why dimension table

[Anonymous]

Hi, 

I have studied Power BI for some time,but I don't have much practical experience. I'm a bit confused about the use of dimension table. in my work sometimes all the HR data (employee data, compensaiton data, job/organization info) are stored in one big flat table. Since I can use these fields in slicers and filter functions already, why should  create seperate dimension table (e.g., employee table , job table)?

Can someone give me a few examples to show the advantage of reference table?

[richbenmintz]

Hi @Anonymous,

Please have a read of this excellent article by @AlbertoFerrari, https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/ , He and Marco Russo are two of the best when it comes to all things DAX and Power BI.

Richard

[Anonymous]

Many thanks! So I understand if I need to use multiple filters from the same table, the best practice is to create  seperate dimension tables.

the next question is how many dimension tables I should create? I have seen people create lots of dimenstion tables, each table corresponds to one filter. The problem is that will generate a complicated relationship schema. The other solution, I guess is to create only a few dimension table  (e.g., put country, job code, company name in one reference table), however, the reference table should contain all the logic combinations of country-job-company. Is that correct?

[richbenmintz · Accepted solution]

Hi @Anonymous,

Each dimension should relate to a specific Domain, eg all customer attributes would belong in the Customer Dimension, all Vendor attributes would belong in the Vendor Dimension, all date attributes would belong in the Date Dimensions .... Each Dimension would then join back to the fact on the unique key per dimension member. If you find that you are creating many single attribute dimensions you should try to find logical groups and derive a dimension key from the unique combinations.

Hope that Helps,

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[amitchandak]

@Anonymous , refer

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

refer Best Practices
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://www.c-sharpcorner.com/article/power-bi-best-practices-part-1/
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Relationships/td-p/712566
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567

[jdbuchanan71]

If your model consists of only one data table then you do necessarily need separate dimension tables.  However, consider if you wanted to have payroll data and sales data in the same report and show both by employee.  This is where having an employee table attached to both the sales data and payroll data is needed.