none
DW Design - Factless fact table

    Question

  • Hi,
    I'm in the process of designing a data warehouse for one of my clients who runs a online store. The warehouse basically stores all the orders data. It has only one fact table (at present) which has the columns - SalesOrderNumber, SalesOrderLineNumber, OrderDateKey, Productkey, SalesAmt .... The combination of {SalesOrderNumber, SalesOrderLineNumber} is the primary key. There is also a dimension called DimStatus which has different values of an order status like In-Transit, Shipped, Canceled etc. The status of an order can change over a period of time and we want to capture the status history of each order. I'm planning to have a factless fact table for this, the columns in this table would be - SalesOrderNumber, StatusDateKey, SalesStatusKey. The column SalesStatusKey is a FK which references DimStatus.
    We planning to build a SSAS cube over this warehouse. I'm wondering if the above design will work if the user want to see Order details as well as status history in a single report. Is there a better design for the problem ? Please help.

    Thanks,
    RK
    Monday, February 02, 2009 8:00 AM

Answers

  • As long as you are querying along the dimensions that are linked to fact tables from where the measures are coming, that should be fine. UDM does provide that feature of combining measures in a single view (that would be useful only when the measures are relevant in terms of dimensionality of course).

    However, in your case, that dimension linking doesn’t exist i.e. Sales Status doesn’t seem to have been linked to Sales Amount in your data model.  For example, suppose, sales order “sr1” has a sales amount of 500 in your sales fact table, you can’t view this by sales order status because you don’t have a history of at different sales order status what was the amount. In that case, your design should be to store Sales Amount along with Sales Order Id and Status Id as FK in the fact table. Which is, logically, bringing Sales Amount into your current fact less fact table (as I mentioned earlier too, you might bring some measure in this table that has relation to order status) or merging the facts. With fact less fact design, you wouldn’t be storing measures at Sales Order Status level at all and so can’t do that reporting.


    ..hegde
    • Marked as answer by svramakris Tuesday, February 03, 2009 1:59 PM
    Monday, February 02, 2009 11:56 AM

All replies

  •  

    Well, if there was some measurement (fact) in that table (which currently is planned to be fact less), say – NumberOfDaysInStatus (Number of days the sales order was in each status), you would have created a fact table for it and used it as any other fact table – it would have looked like a clean fact table in the dimension model. AT this moment, probably you don’t have a need for such measure but it might come-up in the future (I guess this based on a typical cases).  In my opinion, existence of this fact less fact for the requirement of tracking sales order status history completely makes sense.

    In the cube, for users to be able to browse this info/to build the reporting, create a Count measure from this table (named “SalesOrderStatusCount” or something on that line). This measure will be linked to Date, Sales Order and Status dimension. Further, this measure can be linked to other dimensions using reference relationship (ex: link to customer dimension through order dimension if customer dimension exists with reference from order dimension) – with this your cube should have a rich analytical ability like view order status count by customer, geography, Date (month quarter etc). For example, this cube can answer the queries like – how many instances (sales order status count) of “On Hold” status existed in a given customer group/sector/location in a given quarter etc.

    Hope this helps.


    ..hegde
    Monday, February 02, 2009 10:54 AM
  • Hi,
    Thank you for the answer. I have the requirement where user wants to see status history and normal measures like SalesAmt in a single report. These two come from different fact tables.
    In general DW design, can we have relations between fact tables ?

    Thanks,
    RK

    Monday, February 02, 2009 11:41 AM
  • As long as you are querying along the dimensions that are linked to fact tables from where the measures are coming, that should be fine. UDM does provide that feature of combining measures in a single view (that would be useful only when the measures are relevant in terms of dimensionality of course).

    However, in your case, that dimension linking doesn’t exist i.e. Sales Status doesn’t seem to have been linked to Sales Amount in your data model.  For example, suppose, sales order “sr1” has a sales amount of 500 in your sales fact table, you can’t view this by sales order status because you don’t have a history of at different sales order status what was the amount. In that case, your design should be to store Sales Amount along with Sales Order Id and Status Id as FK in the fact table. Which is, logically, bringing Sales Amount into your current fact less fact table (as I mentioned earlier too, you might bring some measure in this table that has relation to order status) or merging the facts. With fact less fact design, you wouldn’t be storing measures at Sales Order Status level at all and so can’t do that reporting.


    ..hegde
    • Marked as answer by svramakris Tuesday, February 03, 2009 1:59 PM
    Monday, February 02, 2009 11:56 AM
  • Thanks a lot.

    --
    RK
    Tuesday, February 03, 2009 1:59 PM