Data Warehouse Modeling: Sales Order Charges [closed]

Posted on

Question :

I have two databases systems that I’m trying to dimensional model a sales order business. Both operational data stores have a Sales Order header and detail table. However, one of them has an additional table for Additional Charges that are charges that can be adjusted a either at the sales order header level OR at the line level. The relationships between the tables are:

  • Header to Detail = 1 to Many
  • Header to Charges = 1 to Many
  • Detail to Charges = 1 to Many

I have a few competing dimensional model design thoughts:

  1. Create a Sales Order header + line level granularity fact table and then a separate fact table for adjustments. This keeps the granularity clean, but might repeat many dimension key columns between the two tables.

  2. Flatten all three tables into one single fact table (with allocated measures) for one of the sources and then leave the Charges related set of columns blank for the other source. This technically contains different granularities in a single fact table, a big Kimball no-no.

  3. Another option might be to create some sort of bridge table, maybe? Thought this would make the Charges act as a dimension than a fact which might be not very efficient…

Can someone guide me as to which of these options is the most apt design?

Answer :

Can someone guide me as to which of these options is the most apt design?

Funilly enough, that question does have an answer: No. No one can.

Dimensional modeling is always about the questions you want to answer, and typically requires eliminating some information in the transformations. There’s lots of modeling decisions you could make, but the only way to choose between them is comparing the ease and performance of answering questions about your business.

Note that you didn’t consider simply using Periodic Snapshot fact, where you would calculate the interesting measures at a grain of something like (day, org, product). This will give you a very simple dimensional model that probably won’t answer every last question, but you can combine that with storing the full transaction details for scenarios that need it.

Leave a Reply

Your email address will not be published. Required fields are marked *