Performance question

[zenisekd]

Hi, 
I have a fact table, which has a couple millions of rows. Among other columns, there is "Product Code", which only contains product code and does not have any relationship to another table. 

My colleague claims it would be better to create a dimension table of this column (so a table that only contains one column - product code), create a relation and use it in a slicer instead of the product code column from the fact table. He thinks that if there are only a few distinct rows, the slicer visual in the report will work faster/ better. 

I think not. As far as I remember, it does not significantly improve any speed. What is the right answer? thanks

[freginier]

Hey there!

In Power BI, the best option would be using a dimension table for slicers instead of filtering directly from the fact table. However, the actual performance improvement depends on cardinality and model structure.

- If the "Product Code" is in the fact table, Power BI must scan through all rows to apply filters, which is inefficient.

- If "Product Code" is moved to a separate dimension table, Power BI processes fewer unique values, leading to better performance.

Also Power BI works best with a star schema rather than a flat table. A separate dimension table reduces memory usage and improves DAX calculations. 

Your colleague is correct in my opinion —creating a dimension table for "Product Code" is the better approach in Power BI, especially when there are many unique product codes. It allows for better compression, filtering efficiency, and slicer performance.

Hope this helps!

😁😁

[zenisekd]

Creating a separate product code table wont create fewer unique values of product codes, it only introduces additional relation which comes with some overheads.. 

[MattiaFratello · Accepted solution]

Hi @zenisekd, unless you are using DirectQuery, having a separate table won’t improve slicer speed.

The performance of a slicer is mainly affected by the cardinality (the number of unique values) in the column, not the total number of rows in the fact table. 

If there were additional attributes related to the "Product Code" (e.g., product name, category, price, etc.), then a dimension table would be beneficial for data modeling and easier filtering.

[powerbiexpert22]

Hi @zenisekd ,

your colleage is right, in general this is what we follow 

[zenisekd]

I dont think so. Can you explain why? Even chat gpt agrees with me, saying "creating a separate dimension table for "Product Code" in this scenario is unlikely to provide any significant performance improvement in Power BI".

[powerbiexpert22]

Hi @zenisekd ,

I agree with @MattiaFratello 

[zenisekd]

So in this scenario, you agree with me 🙂 

[powerbiexpert22]

Hi @zenisekd 

yes