SQL Server 2005 Analysis Services - Aggregating Two Ways in one table/dimension

[C.J.]

hi

i successfully created my first working cube (!) in ssas 2005. our
"forecast" data is in oracle.

i have a question, maybe you can help (i have the teo lachev book)

the time dimension is on the x axis, going left to right, showing
months and years.
i have, for simplicity sake, an "account" dimension on the y-axis.

the problem is, certain fields (ie- revenue) in my "fact" table,
factForecast, should aggregate with Sum (both of time and over
accounts), while other fileds (ie- AUM) should aggregate with Sum over
accounts, but NOT aggregate with Sum over time.

for example, the AUM for full year 2008 is really just the AUM for
December 2008.
while the REVENUE for full year 2008 is truly Jan + Feb + March etc +
Dec.

any ideas?
thanks
cj

[Moby]

Post by C.J.
hi
i successfully created my first working cube (!) in ssas 2005. our
"forecast" data is in oracle.
i have a question, maybe you can help (i have the teo lachev book)
the time dimension is on the x axis, going left to right, showing
months and years.
i have, for simplicity sake, an "account" dimension on the y-axis.
the problem is, certain fields (ie- revenue) in my "fact" table,
factForecast, should aggregate with Sum (both of time and over
accounts), while other fileds (ie- AUM) should aggregate with Sum over
accounts, but NOT aggregate with Sum over time.
for example, the AUM for full year 2008 is really just the AUM for
December 2008.
while the REVENUE for full year 2008 is truly Jan + Feb + March etc +
Dec.
any ideas?
thanks
cj

hi cj

look for semi-additive members.
you have to define your time dimension by type "time"
create a new measure. change aggregate type to "last non empty"

kr moby