Modeling with multiple facts table

[XY_Z]

Hi everyone, I need some guidance/best practice on improving the following data model. There are multiple hierarchies from the least granular at level 1 to the most granular at level 3.  There is a dimension table and fact table for each level. The fact tables for level 1 & 2 contains threshold values for different categories. The level 3 fact table contains data which need to be aggregated to compare to the threshold values. The users need to be able to see the level 3 granularity so grouping the table is not possible. Technically a combined table can be created from level 1/2/3 fact tables, but it will increase the model size and will repeat values. 

The model is working, however I would like to know if there is a better way of structuring the fact tables (may be level 1 & 2 are not true fact tables).

[XY_Z]

Thank you @v-nuoc-msft for your input! The tables are already in import mode. Some additional concern for combining dimension tables is that the relationship to level 1/2 fact table will become many-many which may not be ideal.

[v-nuoc-msft]

Hi @XY_Z 

For your question, you may want to consider data cleansing to reduce redundant fields in your tables.

Reduce the size of the data model and remove duplicate values through data cleansing.

We recommend that you design your model with the required columns based on your reporting requirements.

Remove unnecessary rows by removing duplicate values.

Grouped aggregation operations on fact tables, which can be used to increase the granularity of fact type tables.

You can view the link below for more details:

Data reduction techniques for Import modeling - Power BI | Microsoft Learn

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-nuoc-msft]

Hi @XY_Z 

Here are some suggestions for your questions:

You might consider using a star model to merge the dimension tables for each level into a single Dimension table.

Then create a one-to-many relationship with the fact tables.

Alternatively, you can aggregate the fact tables of Level 1 and Level 2. The aggregation process reduces the table sizes in the semantic model, allowing you to focus on important data and helping to improve the query performance.

And you can set the storage mode to import, which can avoid the performance load caused by data queries.

Optimize DirectQuery models with table level storage - Training | Microsoft Learn

Introduction to performance optimization - Training | Microsoft Learn [76e3-6b80-ade-1019]

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.