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

Hi All,

Since Power BI does not natively support relationships using multiple columns,

How do you create single field generenrated keys ? Specifically if you work with Lakehouse?

Which solution do you use?
How do you maintain it in the code?

Hey YevgenyM ,

I/we lean toward this:
No matter of the data architectecture, meaning if there is a lakehouse or not we are replacing business keys witha surrogate key, meaning integer values. We do not do that if the resulting semantic model is small, less than 1M rows in the largest table(s),
We are doing this using Power Query, if we are using Dataflows (Gen 1or Gen 2) we are making use of linked entities as we are on dedicated capacities. We load source data (..._raw), we transform the raw data, this includes creating the surrogate key (... transform), and finally we build the tables that are consumed by applications further downstream, e.g., semantic models (..._model).

As soon a lakehouse gets involved we are shifting moving the layered dataflows to the metal layers of the lakehouse. We have a tendency to replace dataflows with notebooks, but this is also because we have more data engineers that are familiar with Python than with SQL.

Hopefully this helps to tackle your challenge.

Regards,
Tom

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!

Hi YevgenyM,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

Hi YevgenyM,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you