Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Surm
Helper III
Helper III

Unused Surrogate Keys required or not? Eg. FactSalesSK

Greetings!

 

Keeping Power BI Semantic models as thin as possible by not loading unnecesssary columns helps get the best performance and is also said to the best practice.

 

Consider the below Fact table.

 

FactSales

  1. FactSaleSK: Incremental surrogate key. Makes each fact row unique.
  2. DimProductSK: FK to DimProduct
  3. DimCustomerSK: FK to DimCustomer
  4. etc...

Should FactSaleSK be included in the Power BI semantic model? Or, should it not?

I tend to think that it should be omitted because I can't see a use of it.

Has this been explicitly discussed in the current best practices?



 

4 REPLIES 4
Surm
Helper III
Helper III

Greetings!

 

Keeping Power BI Semantic models as thin as possible by not loading unnecesssary columns helps get the best performance and is also said to the best practice.

 

Consider the below Fact table.

 

FactSales

  1. FactSaleSK: Incremental surrogate key. Makes each fact row unique.
  2. DimProductSK: FK to DimProduct
  3. DimCustomerSK: FK to DimCustomer
  4. etc...

Should FactSaleSK be included in the Power BI semantic model? Or, should it not?

I tend to think that it should be omitted because I can't see a use of it.

I am raising this question because it is the best practice in SQL Server for every table to have a clustered index.
Similarly, is there a best practice for Power BI semantic models to either to contain a unique SK or to remove it?

 

lbendlin
Super User
Super User

If it makes each fact row unique then it is a primary key.

 

Foreign Keys on a fact table should definitely be hidden, as they make no sense for reporting. Similarly the integer primary keys on all tables can be hidden as they have no business value.  The story changes when your primary key has a business meaning (a date, or a product code). These should stay visible.

 

General guidance is that if you have multiple columns with the same name and meaning across downstream tables they should all be hidden in favor of the controlling dimension column.

@lbendlin ,

Hiding the columns reduces the clutter but it does not reduce the model size, because the hidden columns are still in the model taking up space.

The specific question I am asking is whether we should remove the Surrogate Key from the Fact Table, leaving the Fact table without a unique key. Surrogate Keys do not have business meanings, so this column will not be required by the business users.

I am raising this question because it is the best practice in SQL Server for every table to have a clustered index.

Similarly, is there a best practice for Power BI semantic models?


whether we should remove the Surrogate Key from the Fact Table, leaving the Fact table without a unique key

That would be a question for Vertipaq, but I would think that the answer is yes, as that column cannot be meaningfully compressed.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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