Data Model: Dimensions to fact table columns

[rpiboy_1]

As is well established, best practice is to have a Fact table with columns that are your 'facts' for each row. What is the suggested approach to when those columns may have dimensions that are unique to them? For example certain groups of columns may always 'go together' and in visualizations it may be desirable to filter or organize the data based on these dimensions.

• should the single large fact table be split into multiple fact tables based on these dimensional relationships?
• I can imagine a scenario where you pivot the facts, so that you end up with a large table of Key:Value pairs, and then you could have additional columns to define the relationship(s) to the dimensions.
  • This however would be very in-efficient, and could cause issues in 'large' datasets.
• Is there another approach I'm missing?

[rpiboy_1]

Thanks,

Love GiC and have also used eDNA. I'll take a look, good chance I've watched the GiC video. 🙂

Sounds like my intution on splitting the Fact table is the 'right' path to go down.

[foodd · Accepted solution]

Regarding your specific question about columns that may have dimensions that are unique to them, it is recommended to split the single large fact table into multiple fact tables based on these dimensional relationships.

Patrick from Guy in a Cube in his classic 2019 video addressed 

best practices in Power BI Data Modeling:

Data modeling best practices - Part 1 - in Power BI and Analysis Services

As well EnterpriseDNA from their Blog (they have learning paths and courses, and many Forum Posts on Data Modeling best practices):

Data Modeling In Power BI: Tips & Best Practices (enterprisedna.co)