Semi-additive issue...

[flurk118]

Semi additive problem...

I have an issue regarding semi additive behaviour which, as a relative newbie, has me stumped.

Take this scenario...

I have a very simple design which consists of a fact table with a single measure (balance), a time dimension and an account dimension. Defined as follows:-



DimAccount (SCD)
AccountKey
AccountType (defines the semi additive behaviour)
Status (can hold the value “Active” or “Inactive”)
StartDate

FactBalances
DateKey
AccountKey
CurrentBalance


The data held is as follows:-

DimAccount (5 accounts)
AccountKey[tab][tab]AccountType[tab][tab]AccountNumber[tab][tab]Status[tab][tab]StartDate
1[tab][tab]Balance[tab][tab]AC01[tab][tab]Active[tab][tab]2009/01/01
2[tab][tab]Balance[tab][tab]AC02[tab][tab]Active[tab][tab]2009/01/01
3[tab][tab]Balance[tab][tab]AC03[tab][tab]Active[tab][tab]2009/01/01
4[tab][tab]Balance[tab][tab]AC04[tab][tab]Active[tab][tab]2009/01/01
5[tab][tab]Balance[tab][tab]AC05[tab][tab]Active[tab][tab]2009/01/01


FactBalance (3 months of data)	
DateKey[tab][tab]AccountKey[tab][tab]CurrentBalance
20090101[tab][tab]1[tab][tab]1000
20090101[tab][tab]2[tab][tab]1000
20090101[tab][tab]3[tab][tab]1000
20090101[tab][tab]4[tab][tab]1000
20090101[tab][tab]5[tab][tab]1000
20090201[tab][tab]1[tab][tab]1000
20090201[tab][tab]2[tab][tab]1000
20090201[tab][tab]3[tab][tab]1000
20090201[tab][tab]4[tab][tab]1000
20090201[tab][tab]5[tab][tab]1000
20090301[tab][tab]1[tab][tab]1000
20090301[tab][tab]2[tab][tab]1000
20090301[tab][tab]3[tab][tab]1000
20090301[tab][tab]4[tab][tab]1000
20090301[tab][tab]5[tab][tab]1000

My design is configured to use semi additive behaviour for my current balance (LastNonEmpty). So if I drag the “CurrentBalance” into my view window I am correctly told 5000.

Now my account dimension is slowly changing and here is where the problem starts. Amending the data above to show a change in account status to AC01 during the 3 month period gives me the following data:-

DimAccount (5 accounts but with one historic change row)
AccountKey[tab][tab]AccountType[tab][tab]AccountNumber[tab][tab]Status[tab][tab]StartDate
1[tab][tab]Balance[tab][tab]AC01[tab][tab]Active[tab][tab]2009/01/01
2[tab][tab]Balance[tab][tab]AC02[tab][tab]Active[tab][tab]2009/01/01
3[tab][tab]Balance[tab][tab]AC03[tab][tab]Active[tab][tab]2009/01/01
4[tab][tab]Balance[tab][tab]AC04[tab][tab]Active[tab][tab]2009/01/01
5[tab][tab]Balance[tab][tab]AC05[tab][tab]Active[tab][tab]2009/01/01
6[tab][tab]Balance[tab][tab]AC01[tab][tab]Inactive[tab][tab]2009/02/01

FactBalance (3 months of data)	
DateKey	AccountKey[tab][tab]CurrentBalance
20090101[tab][tab]1[tab][tab]1000
20090101[tab][tab]2[tab][tab]1000
20090101[tab][tab]3[tab][tab]1000
20090101[tab][tab]4[tab][tab]1000
20090101[tab][tab]5[tab][tab]1000
20090201[tab][tab]6[tab][tab]1000
20090201[tab][tab]2[tab][tab]1000
20090201[tab][tab]3[tab][tab]1000
20090201[tab][tab]4[tab][tab]1000
20090201[tab][tab]5[tab][tab]1000
20090301[tab][tab]6[tab][tab]1000
20090301[tab][tab]2[tab][tab]1000
20090301[tab][tab]3[tab][tab]1000
20090301[tab][tab]4[tab][tab]1000
20090301[tab][tab]5[tab][tab]1000

If I now drag in the current balance I still get 5000 (great) but if I drop the attribute “Status” as a row field I get:-

Active[tab][tab]5000
Inactive[tab][tab]1000
Grand total[tab][tab]5000

Not the behaviour I was seeking. I am after the following result:-

Active[tab][tab]4000
Inactive[tab][tab]1000
Grand total[tab][tab]5000

Can anyone help me to achieve this?

 

[RiverGuy]

Can you post an image or more complete view of your resultset? I'm not sure if you've got months and status on rows, or just status, or something else.

 

[flurk118]

Hi Riverguy, thanks for the response.

sorry about the formating. didn't come out to clear. to explain...

the DimAccount table has five columns:
AccountKey
AccountType
AccountNumber
Status
StartDate


The [Status] field holds either "Active" or "Inactive"
[Startdate] holds the date the row was entered.

Hope that helps.

 

[RiverGuy]

I'm trying to visualize your query output. Does your resultset look like this:

-------------------------------
20090201 |Active       |  5000 |
         ----------------------
         |Inactive     |  1000 |
         ----------------------
          Grand Total  |  5000 |
-------------------------------
20090301 |Active       |  5000 |
         ----------------------
         |Inactive     |  1000 |
         ----------------------
          Grand Total  |  5000 |
-------------------------------

or this

Active       |  5000 |
----------------------
Inactive     |  1000 |
----------------------
Grand Total  |  5000 |
----------------------

or something else?

 

[flurk118]

the second one.


i'm browsing the cube within SSAS and dragging [CurrentBalance] as the measure and DimAccount.Status as my row field.

as the measure is semi additive (byaccount) i was hoping that it would have returned just the most recent entry for the accounts but it has sum'd all entries including both row for the account (AC01).

anyway around this?

 

[RiverGuy]

I couldn't reproduce your results. I built a sample based on your schema above and ended up with 4000/1000/5000. Using LastNonEmpty for the aggregate function.

 

[flurk118]

Did you use the measure to use the "ByAccount" aggregate function?

To use this you need to configure the account table to be of type Accounts, the AccountReference field as type "Account" and the "AccountType" field as type AccountType. You then also need to relate AccountType to AccountReference.

 

[flurk118]

My end goal for this design is to have a cube that, for any question asked of it, will identify data based on the last/current account (DimAccount) – no double counting on the same account please. I thought semi additive functions would be the route to achieve this but, as I hope I have described above, I don’t think it quite achieves this.

If I ask for the balance by status for the month 20090101 I expect to have 5 accounts returned with their statuses.

If I ask for the balance by status regardless of date I expect to have 5 accounts returned with their statuses but “ByAccount” aggregation is picking up the historic change and returns 6 accounts. It seems to return unique data within the context of the question asked and not distinct by the account reference.

I wonder what the correct design route for me to take is. I know very little about the power of MDX but maybe this is the answer. Or should I lock down the design so I has restrictive query capability?