- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@shubhamrkrock , Yes, Int should work better. Refer first point -https://towardsdatascience.com/5-quick-ways-to-speed-up-your-power-bi-dashboard-41af7b46ea25
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
09-19-2024 03:54 AM | |||
08-08-2024 10:34 PM | |||
10-27-2023 12:36 AM | |||
06-06-2024 01:19 AM | |||
05-12-2024 07:50 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
48 |