powerbidaxpowerbi-desktopdata-modelingstar-schema

How to model when header table has values?


I have following tables:

order header table: ordnum, orddate, customerID, cost_of_packing, cost_of_delivery

Order line table: ordnum, ordlinenum, productid, qty, price

Ordnum column is the common field between the above 2 tables.

Customer: customerID, customername

Product: productID, productname

How to model this data?

If I join order header and order lines tables, therefore make this into one fact table, then the costofpackaging and costofdelivery will duplicate for each row.

If I keep them as 2 separate fact tables, then I cannot connect them on ordnum column as this breaks the star schema design. Thus I can't report on order header and details.


Solution

  • Get rid of order header table entirely. Bring order dates and customer Ids to the order detail table, and allocate costs to the lines.

    If you need to do line-level calculations, you will have to define cost allocation rules. For example, express costs as % of the order total value, and then multiply by the line amounts. That will make costs fully additive.

    If costs are never considered at the line level, then simply park them at the first line of each order, and keep blanks at all other lines.

    Edit:

    I mocked up your data and model to show how it will look in practice.

    Data model:

    enter image description here

    Mocked up Orders table with top-level numbers allocated to the first order line:

    enter image description here

    Top-line report looks like this:

    enter image description here

    Order detail report with drill-down to products:

    enter image description here

    DAX Measures:

    line-level:

    Order Qty = SUM(Orders[Qty])
    
    Order Amount = SUMX(Orders, Orders[Price]*Orders[Qty])
    

    top-level:

    Order Delivery Cost =
    CALCULATE (
        SUM ( Orders[Delivery Cost] ),
        REMOVEFILTERS ( Orders[Product ID] )
    )
    

    Combined top and line level calculations:

    Order Total  = [Order Amount] + [Order Delivery Cost]
    

    As you can see, the model is simple, and the calculations are intuitive and performant. The only thing that looks a bit strange is the blank in delivery costs, but that's the price you'll have to pay for not allocating.