April 24, 2011 at 10:30 am
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 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? My thought process is that it should be a fact that the user can use in a pivottable, but I'm not sure.....
April 25, 2011 at 6:46 am
This is a double post. Until an editor can fix it, here is the reply I added on the other thread:
Based on what you've provided, I would suggest that you would use an accumulating snapshot fact table. The row is revisited when there is a change. The record has a series of dates which are updated as the record is updated. There is a creation date and whatever other dates you might need. For example, in one of my more complex ones, I have creation date, first contact date, shipped date, completed date, cancelled date, and closed date. I have included the last three because no single work order will have all three. At first they start out 'N/A'. When the order is completed, the complete and closed are set to the same date. If the order is cancelled the cancelled date and closed dates are set. The untouched date remains 'N/A'. In the event an order is completed or cancelled w/o a date, the value is 'UNK'.
Although Ralph Kimball says that the accumulating snapshot fact table is the least common of the three he identifies, it has been the most common for me.
April 25, 2011 at 7:57 pm
isn't this kind of restricting? If I had to add a new status, wouldn't I need to add a new field, and then update it all? Or is that headache much better than having to do traverse through the fact table looking for rows that match the order number?
April 26, 2011 at 3:30 am
I don't know what you mean by restricting in this case. I find that the system works very well. One of the fields I track BTW is status. If I tracked each status change, then the fact table granularity changes from job to job/status change. This is certainly a valid granularity, but then a lot of measures that would apply to job only would not be useable.
As the measure of granularity is THE key aspect of any fact table, you may want to approach it from that end to determine if the "headache" is worth it. Whether it's a headache or not isn't really relevant: measuring at the desired level is the only relevant issue, and then you have to make it work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply