3

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.....

M.R.
  • 4,737
  • 3
  • 37
  • 81

1 Answers1

1

One way to model this would be to create a factless fact table to model events. Your ORDERS fact table models the transaction amount, customer information etc, while the factless fact table (perhaps called ORDER_STATUS) models any events that occur in relation to a specific order.

With this model, it's easy to count or add all transactions based on their order status by checking for existence of records in the factless fact table.

Datajam
  • 4,141
  • 2
  • 23
  • 25
  • can you add a little more detail? I'm not too familiar with factless fact tables... – M.R. Apr 26 '11 at 03:53
  • Factless Fact tables just model dimensional foreign keys without any measured fact, i.e. it models the relationship between dimensional keys. Read more [here, with a relevant example](http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT50FactlessFact.pdf). – Datajam Apr 26 '11 at 08:00
  • There are other lines of thought on how this can be approached such as http://stackoverflow.com/questions/11114988/how-to-model-process-and-status-history-in-a-data-warehouse . I am still confused as to which direction to take. – Franklin Nov 24 '12 at 11:26
  • @Franklin, Did you get the best practice to share during the passed years? – mingchau Apr 19 '19 at 02:12
  • @Datajam, so the `ORDER_STATUS` fact is a table that records order status change? How could it join with the `ORDERS` fact table if so? – mingchau Apr 19 '19 at 03:06