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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Snowlock
Frequent Visitor

Should I denormalise narrow, repeititve dimension tables

I have read that narrow dimension tables containing repeated data (e.g. order status, that kind of thing) should be denormalised onto the relevant fact table - that Vertipaq will optimise this using dictionaries. However, the article is about five years old, and I'd like to ask what the current best practice is. Particularly if you then filter on or write a measure against a denormalised column, will that then do a string comparison on each row in that column? Or will it find a key from the dictionary, and use that?

I guess I'm asking - is it better to add a denormalised column to the fact table, or a completely new, normalised dimension table?

 

EDIT - hmmm it's from Jan 11th, I guess it's not as old as I thought:
https://towardsdatascience.com/how-to-reduce-your-power-bi-model-size-by-90-76d7c4377f2d

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Snowlock , It is always better to be in Star schema. If a dimension has levels in different tables merge those.  to make sure only 1 level join with fact.

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

 

A dimension can be role-playing or sometime you need more than one copy of dimension as per need

 

If a dimension does not add any value separately, means it belongs to only one fact and use only as a filter or slicer it can be merged with the fact

 

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Snowlock , It is always better to be in Star schema. If a dimension has levels in different tables merge those.  to make sure only 1 level join with fact.

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

 

A dimension can be role-playing or sometime you need more than one copy of dimension as per need

 

If a dimension does not add any value separately, means it belongs to only one fact and use only as a filter or slicer it can be merged with the fact

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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