0

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?

closed as primarily opinion-based by LowlyDBA, Marcello Miorelli, Paul White Jun 17 at 10:04

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

1

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.

  • In fact, I am currently using an Accumulating Snapshot for the table at the Header-Detail level to update the states of the order as it moves from quoting through to delivery (line states can change as well). I just didn't want to belabor that aspect of the design here with respect to the Charges part of the business process. However, your point about not being able to answer every question through transformation strikes me as the most important part of your answer. – codedawg82 May 20 at 13:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.