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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

C.J. - 25 Jun 2008 19:57 GMT

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

Reply to this Message

Moby - 27 Jun 2008 08:15 GMT

> hi
>
[quoted text clipped - 20 lines]
> 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

Reply to this Message