Transaction fact table and Sql server analysis services calculated measure

View previous topic View next topic Go down

Transaction fact table and Sql server analysis services calculated measure

Post  rahm0277 on Sun Apr 24, 2011 12:39 pm

How do you store facts within which data is related? And how do you configure the measure? For example, I have a data warehouse that tracks the lifecycle of an order, which changes states - ordered, to shipped, to refunded. And for a state like 'refunded', it is not always there. So in my model, I am employing the transaction fact store model, so every time the order changes state, it is another row in the fact table. So, for an order that was placed in april, and refunded in may, there will be two rows - one with a state of 'ordered' and another with a state of 'refunded'. So if the user wanted to see all the orders placed/ordered in april, and wanted to see how many of 'those' orders got refunded, how would he see that? Is this a MDX query that will be run at runtime? Is this is a calculated measure I can store in the cube? How would I do that?

rahm0277

Posts : 6
Join date : 2011-04-24

View user profile

Back to top Go down

Re: Transaction fact table and Sql server analysis services calculated measure

Post  John Simon on Mon Apr 25, 2011 2:02 am

rahm,
Here is a post by Boyan Penev on this particular issue:
http://www.bp-msbi.com/2010/10/avoiding-multiple-role-playing-date-dimensions/

Essentially you want to create an accumulating fact table, or use a PIVOT statement in T-SQL to create a view that does the same thing. Then calculate the lags.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Transaction fact table and Sql server analysis services calculated measure

Post  rahm0277 on Mon Apr 25, 2011 9:44 pm

Thank you, John - that site is very informative. I'm definitely going with the accumulating fact table, and the post you linked is 95% of exactly what I want - just missing one small thing - so in this example, when you have a separate event for orders (shipped, delivered, refunded). The way the post mentioned allows you to see (based on a date filter) all the different types of events for a given date range. But how do you see related facts, such as, for e.g. for the month of april (so my date range is april 1 – april 30), I have 100 orders that were placed. And I want to see how many of those orders were refunded (regardless of the date that was refunded). So, even if the order was refunded in May, I want to see that order XXX was refunded when I see the purchased and refunded counts of April…. is there a different model for this, or an MDX query would get this data for me? Also, can you please explain what you mean by 'lags'? thanks....

rahm0277

Posts : 6
Join date : 2011-04-24

View user profile

Back to top Go down

Re: Transaction fact table and Sql server analysis services calculated measure

Post  Dave Jermy on Tue Apr 26, 2011 7:00 am

'Lag' is simply the elapsed time (usually in days) between two dates. On your accumulated snapshot, if you have dates for ordered, shipped, delivered, refunded, then you can store or calculate the lag between any two of them, giving you 6 lags in all. This is particularly useful if you have delivery time targets/SLAs to monitor.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Transaction fact table and Sql server analysis services calculated measure

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum