CODEWITHSUNDEEP
sql-server-2008ssas
nickrobison
nickrobison

Reputation: 150

Slowly Changing Dimension in SSAS

I'm trying to design a cube that will let me look at monthly revenue over the past several years using SSAS. I've built the revenue fact table using a Type 2 SCD that results in the following table:

Account Number     MonthlyPayment     Start Date     End Date
102939              115               01/01/2001     03/31/2001
102939              75                04/01/2001     NULL
102940              107               02/01/2001     NULL

Now, I'm trying to figure out what my monthly revenue is for each month e.g. 

01/01: 115 
02/01: 222 
05/01: 182

I've built a cube but it's only reporting revenue on specific months because it's linked on Start Date e.g.

01/01: 115
02/01: 107
02/01: 0 
04/01: 75

Apparently I'm missing something because all the links I've been able to find stop after building the fact table in SSIS and seem to imply that it's a piece of cake once the data is formatted correctly. Do I need to write an MDX calculated measure and if so how? Or is there something much simpler that I should be looking at.

Any advice would be greatly appreciated!

Upvotes: 1

Views: 1497

Answers (2)

Nat
Nat

Reputation: 14305

You add a Time dimension that includes all the dates, even if they are not used by your fact table. SSAS allows you to generate a Time dimension that includes a month column you can use in MDX queries.

I am sorry, I did not completely comprehend the problem you were defining. I think you are going to have to denormalise the table into something like

Account Number     PaymentAmount     Payment Date     
102939              115               01/01/2001     
102939              115               02/01/2001     
102939              115               03/01/2001     
102939              75                04/01/2001     
102940              107               02/01/2001

That will end up simplifying that calculations, at a cost of table size.

Upvotes: 0

ic3
ic3

Reputation: 7690

That's not an easy call.

You're looking to define at cell level (aka row level) a new type that is Payment(amount, start, end) that when aggregated is taking a date to calculate the amount.

OLAP cubes are not designed to solve this kind of problems out of the box as they aggregate only basic types (double, int, booleans...). I don't know of any vendor doing this at this point in time.

The standard way would be to add for each month the amount :

Account Number     MonthlyPayment     Date     
102939              115               01/01/2001
102939              115               02/01/2001
102939              115               03/01/2001
102939              75                04/01/2001
...
102940              107               02/01/2001
102940              107               03/01/2001
102940              107               04/01/2001
...

The question of this solution is the number of original rows versus new rows (memory) and how to generate the 'new' table.

Another is finding a vendor that can partnership with you to better address this solution, if SSAS is not a must.

Upvotes: 1

Related Questions