Unused Surrogate Keys required or not? Eg. FactSalesSK

[Anonymous]

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?

[Anonymous]

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]

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.

[Anonymous]

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

[lbendlin]

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.