Category: sql server analysisservices
aetern on Mon, 06 Oct 2014 19:34:42
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?
aetern on Tue, 07 Oct 2014 11:51:42
RichardLees on Wed, 08 Oct 2014 01:44:03
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