Reply
shubhamrkrock
New Member
Partially syndicated - Outbound

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

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
5 REPLIES 5
RHOU
Advocate III
Advocate III

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
amitchandak
Super User
Super User

Syndicated - Outbound

@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/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Don't think I'm crazy for writing in this old post, but I was looking into this topic and, as already mentioned, it seems like there's no difference between using integers, text, or date data types to build the relationships
Look at these great articles:

https://www.sqlbi.com/articles/choosing-between-date-or-integer-to-represent-dates-in-power-bi-and-t...
https://www.maxwikstrom.se/performance/power-bi-data-types-in-relationships-does-it-matter/

 

Syndicated - Outbound

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?

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)