Data modeling question - multiple degenerate dimensions?


  • Now, for example,Say I had orders spread over several shipments and each shipment could have several transactionsassociated with them.

    And each order is realted to other entities like this:

    |_____Shipping (one to many with Order)
                    |_____Transaction (one to many with Shipping)

    In one report, I simply want to see a count of orders for each customer for the current week and year-to-date.
    The problem is that there is no Order entity in the source DB, I have to derive some sort of unique identifier based on the shipping info.

    So that report would be something like this:


       Current Week Order Count


    Bobs flooring



    Test Account 2



    Test Account3



    In a 2nd report, I might want to drill down from an order, and see the transactions grouped by shipping method for example. 

    Should I have Order, Shipment AND Transaction measure groups?

    Wednesday, September 25, 2013 8:16 PM

All replies

  • It depends...

    You can do well with just one measure group and measures like Order count and shipping count as fractions.

    You can do as well with three measure groups.

    The question is, if you wish to answer questions like "how many orders for blue widgets?" and what should be the answer - distinct count of orders or sum of fractional orders.

    In the three fact tables design you will not have Product dimension related to Order fact.

    Friday, September 27, 2013 2:19 AM