How to calculate balance at specific date depending on historical (scd 2 type) attibute value

Question

aetern on Mon, 06 Oct 2014 19:34:42

Hi,

i need some help to calculate balance amount at specific date using attibute value filter. This attribute is stored using scd type-2 method in customer dimension table and customer dimension has relationship with fact table (with fully additive amounts) using surrogate keys.

there is my example DW and OLAP architecture:

What i need is to answer the question - what whas the customers balance with status A (attribute "att1 value" value)  at the end of 2014 february.

So i need to sum all values from the begining to end of february 2014 of all customers, who has A status at the end of february.

the right answer would be 20.

Is any effective way to calculate this using mdx?

Or simply i should transform my fact table to semi additive fact table?

Replies

aetern on Tue, 07 Oct 2014 11:51:42

Any suggestions?

RichardLees on Wed, 08 Oct 2014 01:44:03

Hi PD,

I am not sure what the difficulty is.  You can filter on Customer.cust_no.C2 and Customer.attr1Value.A.  This will only give you the fact with a value of 20.  If there were many facts for this filter, you would simply take the last one.

Is that along the lines of what you are trying to do?

aetern on Wed, 08 Oct 2014 07:11:43

Sorry, my example is not informative as it should be :)

for example, if we choose customer.att1Value.A and select to calc balance at specific date 2014-05-28

OLAP cube should return rezult 1820

In other words - calculation must sum all customers (who has active attr1value.B at selected date) rolling total from the begining up to selected date

```DECLARE @dt as date	='2014-05-28'
declare @av as varchar(1) ='A'
SELECT
cust_code
,(SELECT SUM(value)
from fact_Changes
left join dim_Customers fc on fc.cust_sk=fact_Changes.cust_sk
where date <= @dt and fc.cust_code=dim_Customers.cust_code
) as TotalBalance
FROM dim_Customers
where
valid_from<= @dt and (valid_to>@dt or valid_to is null) AND
att1Value=@av```

```Valid result:
c2	20
c1	1800```

Latest posts in the category

sharepoint general_ja

windows metro apps tools