-
semi additive issue
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:-
Code:
DimAccount (5 accounts)
AccountKey AccountType AccountNumber Status StartDate
1 Balance AC01 Active 2009/01/01
2 Balance AC02 Active 2009/01/01
3 Balance AC03 Active 2009/01/01
4 Balance AC04 Active 2009/01/01
5 Balance AC05 Active 2009/01/01
FactBalance (3 months of data)
DateKey AccountKey CurrentBalance
20090101 1 1000
20090101 2 1000
20090101 3 1000
20090101 4 1000
20090101 5 1000
20090201 1 1000
20090201 2 1000
20090201 3 1000
20090201 4 1000
20090201 5 1000
20090301 1 1000
20090301 2 1000
20090301 3 1000
20090301 4 1000
20090301 5 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:-
Code:
DimAccount (5 accounts but with one historic change row)
AccountKey AccountType AccountNumber Status StartDate
1 Balance AC01 Active 2009/01/01
2 Balance AC02 Active 2009/01/01
3 Balance AC03 Active 2009/01/01
4 Balance AC04 Active 2009/01/01
5 Balance AC05 Active 2009/01/01
6 Balance AC01 Inactive 2009/02/01
FactBalance (3 months of data)
DateKey AccountKey CurrentBalance
20090101 1 1000
20090101 2 1000
20090101 3 1000
20090101 4 1000
20090101 5 1000
20090201 6 1000
20090201 2 1000
20090201 3 1000
20090201 4 1000
20090201 5 1000
20090301 6 1000
20090301 2 1000
20090301 3 1000
20090301 4 1000
20090301 5 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 5000
Inactive 1000
Grand total 5000
Not the behaviour I was seeking. I am after the following result:-
Active 4000
Inactive 1000
Grand total 5000
Can anyone help me to achieve this?