Do relationships on numeric columns work faster than those on text columns?

[shubhamrkrock]

Hi Power BI Community,

I have a very troubling question regarding the best way to design a data model I can't seem to find a solution online. It's about the relationships between tables in a Power BI data model. I have always been doing it on numeric columns (Keys), but a stakeholder is challenging that even if it is on long text columns, it's the same in terms of performance. Can you help me here by sharing your thoughts on below?

Ensuring the Primary and Foreign Keys between the DIMs and FACTs in the Power BI relationships are on numeric columns rather than text columns (This would also reduce the volume of data in the Model in terms of MegaBytes, so that also helps Front-end performance)

• Example, Relationship between DIM_ProductCategory and the FACT tables are on the concatenation of Level 1, Level 2…Level 6, that is a not as good as numeric columns
• The FACT table has over 16 million rows which are just repetitions of 187 distinct values of concatenation of Level 1, Level 2…Level 6. This would consume way less space if these were just 1 to 187 number values. This is the same case in 3 such FACT tables (different levels) relationships with the Dimension table of ProductCategory

[RHOU]

Somebody ran a test on this and it looks like there is no difference between the data types. So might as well keep your text keys ... 

https://www.maxwikstrom.se/performance/power-bi-data-types-in-relationships-does-it-matter/?unapprov...

[amitchandak]

@shubhamrkrock , Yes, Int should work better. Refer first point -https://towardsdatascience.com/5-quick-ways-to-speed-up-your-power-bi-dashboard-41af7b46ea25

[amitchandak]

@shubhamrkrock , Numeric would perform better. Always perfer to have a dimesion table. And try to be star schema.

In case you do not have the numeric key, the Cost of creation of that in Power BI can big at the time of loading, in such cases continue with the text/date key.

refer: https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

[shubhamrkrock]

Thanks for the response, @amitchandak!

Yes, that is what my belief is too! We do have a normalised star/snowflake schema with multiple FACT Tables in the data model. 

Also, my way of getting such Key columns would be in the backend, and not in the Power Query of Power BI, because it would cause too much overhead on Power BI while data refreshes.

The question arose when we know that each value in Power BI has a numerical proxy to it (just like Tabular model), so the stakeholder said it is the same performance on the relationship with numeric Keys v/s text Keys. I did say it wasn't true, but wanted to get a second opinion. If my understanding is correct, you agree with me, @amitchandak?