Forum Discussion

YevgenyM's avatar
YevgenyM
Icon for Advocate IV rankAdvocate IV
2 months ago

Power BI Composite Keys: Performance vs. Maintenance tradeoffs in a Lakehouse architecture

Hi All, Since Power BI doesn't natively support relationships using multiple columns, creating a single-column surrogate/composite key is a standard requirement. However, when working within a Lake...
Need Help
Tutorial Requests
tharunkumarRTK's avatar
tharunkumarRTK
Icon for Super User rankSuper User
2 months ago

YevgenyM 

 

I would recommend 

 

Create a numeric surrogate key inside a fabric notebook in silver layer of your medallion architecture. You can use Spark SQL functions to do so.

Please find the samepl code which would take first 15 characters from the column and conversion it into a numeric key of 16 digits.

SELECT 
    
    CAST(CONV(SUBSTRING(MD5(business_key), 1, 15), 16, 10) AS BIGINT) AS numeric_key
FROM my_table

 

This way you should be able to use them in Power BI to relate two tables. Since numberic keys would not a large memory footprint it will not cause issues in power bi. 

 

 

 

 

Connect on LinkedIn

You can read my blogs here: https://www.techietips.co.in/

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!