Optimizing Data Model

[mbsptr]

I am currently rebuilding a data model which is performing poorly and looking for the best approach. As per example below, my current model consists of several fact tables (many more in reality) which are all related by order number.

Currently I have one 'filter' table (and also a few dimensions in the actual model) which filters all fact tables in the model. The filter table is essentially a copy of the orders table with the required columns for filtering, given they are all related by order #.

Some resources suggest combing all related facts into a single table but this seems messy given the number of fact tables I have, and they are all completely different. some orders for example can have 100+ calls per order.

My idea is to ditch the filter table and split it into dimensions, including a junk dimension for all the flags, and have them filtering each fact table individually to create a proper star schema.

The main issue with this is that I will no longer be able to filter the entire report on order number only. Does this mean I am going to need an 'Order Number' dimension? It would essentially be a single column.

Thank you,

Mike

[DOLEARY85]

Hi,

Personally i would try to combine the fact tables at the source or failing that in power query to get a better perfoming model as this will then all be combined prior to any processing within the report.

With your solution i probably would create Order Number dimension for the best performance when filtering the entire report.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍