Search code examples
powerbidata-warehousestar-schemafact-table

Power BI star (constellation) schema: 2 fact tables with relation between each other

I need to create a data warehouse for analyzing data in an order process but I am unsure of how to proceed since my research led to conflicting information. For simplicity I will only use the most important tables to describe my issue.

TblOrder (Fact):

  • Once a customer makes an order it creates an entry in TblOrder.

  • Every entry is unique an has an OrderNoand it's own timestamp of creation

TblOrderSubProcess (Fact):

  • An order consists of at least 1 sub process.

  • Each sub process of an order is a unique entry in TblOrderSubProcess and has it's own timestamp of creation.

  • Each sub process contains has a column with the OrderNo

  • An entry will only be made if enough material is in the warehouse

  • Moving material between warehouses (for storage management) is also a sub process but has OrderNo = null

TblOrderInformation (Dimension):

  • This table contains information about how a sub process is supposed to be executed (e.g.: locationA → locationB, material, expected time, etc.)

enter image description here

Star schema being the best practise in Power BI, I am not supposed to create a relationship between TblOrder and TblOrderSubProcess but there will be visuals which need the relation between both tables.

My solution to this was to create a flat table where I TblOrder LEFT JOIN TblOrderSubProcess. This solution works but has it's flaws:

  • If I create a date slicer it will always be using the date of Order or OrderSubProcess. This would mean that my Power BI Reports can only contain information of either Order or OrderSubProcess

  • Joining the tables creates redundancies

    • I would still need a seperate Table for all the OrderSubProcesses which were created because of storage management since I would lose these entries in a LEFT JOIN (because OrderNo = null)

  • The created flat table would be very large with initially 30 columns. The columns will grow over time.

My goal is a datamodel which solves the mentioned issues and follows best practises

Solution

  • This sounds very similar to the order header - order line data modeling problem. The kimball group wrote a blog post about this below.

    https://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/

    In summary, TblOrderSubProcess will be your fact table. TblOrder would be split into logical dimensions.