Forum Discussion
Help Needed with Power BI Data Modeling
Hello everyone,
I'm new to this forum and to Power BI and trying to understand how to properly model my data to analyze economic inequalities in a country. I have several datasets, and the structure is a bit unusual, so I'm not sure how to connect everything effectively.
Here are the tables I'm working with:
1. Salary Table
This table mixes salary data by job type and by age category, which is confusing. There is no age data in job-type rows and no job-type data in age rows:
Town_Code | Sexe | Job Type | Age in 3 category | Salary |
abcde | Female | Executives | 19.1 | |
abcde | Female | Mid_Managers | 13.2 | |
abcde | Female | 18to25 | 9.7 | |
abcde | Male | 50plus | 18.6 | |
(etc.) |
2. Enterprise Table
Shows number of enterprises by size, per town:
Town_Code | Total_Enterprises | Enterprises_1_to_5 | ... | Enterprises_500_plus |
abcde | 996 | 272 | ... | 0 |
3. Population Table
This one contains population by age group, sex, and cohabitation type:
Town_Code | Sexe | Age in 3 Category | Cohabitation Type (Code) | # Peoples |
abcde | Male | 15 to 25 | 11 | 340 |
abcde | Female | 25 to 50 | 12 | 16 |
4. Geography Table
Contains the town names and geographic hierarchy (Region > Department > Commune, etc.)
My Objectives:
I'd like to analyze and compare:
- Companies based on their location and size.
- Population based on salary and location.
- Focus on a big city.
My Questions:
- How should I model the Salary table? Since it mixes two different dimensions (Job Type and Age Category) in the same columns, I'm not sure how to split or normalize it.
- How can I structure the data into a star schema?
- How canI include the Cohabitation attribute in the model?
- What relationships should I create between these tables?
- Any general best practices for this kind of demographic/economic comparison?
I'm grateful for any guidance, schema suggestions, or resources you can share. I'm a beginner, so simple explanations are really appreciated!
Thanks in advance 🙏
Hi DanieleR , to overcome this, you need to do some modeling.
At first, let's rebuild SalaryData with these columns
Town_Code GenderKey CategoryKey Salary abcde 2 1 19.1 abcde 2 2 13.2 abcde 2 4 9.7 abcde 1 6 18.6 Now, we need to create a bridge table that handles both job type and age group
DimCategory
CategoryKey CategoryType CategoryName SortOrder 1 JobType Executives 1 2 JobType Mid_Managers 2 3 JobType Employees 3 4 AgeGroup 18to25 10 5 AgeGroup 26to49 11 6 AgeGroup 50plus 12
We have location informations so create another table for thatDimTown
Town_Code TownName Department Region abcde Paris Paris ĂŽle-de-France fghij Lyon RhĂ´ne Auvergne-RhĂ´ne klmno Marseille Bouches-du-RhĂ´ne Provence-Alpes-CĂ´te
and To get the gender Create another table
DimGender
GenderKey Gender 1 Male 2 Female
And, finally create this relationship with your other old tables
SalaryData → DimCategory (CategoryKey)
SalaryData → DimTown (Town_Code)
SalaryData → DimGender (Sexe)
Population → DimTown (Town_Code)
Enterprises → DimTown (Town_Code)
Now, you have an organized model to answer your all queries.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
6 Replies
The example tables you have provide are "reports" withj totals.
They contaion no underlying details to build relationships.
If you have access to underlying data then consider dumping it into one simple table with one record per person.
- PersonID
- Gender
- Date Of Birth
- Age (calculated column using DOB)
- Enterprise / Company name|
- Region
- Town
- Department
- Commune
Answers to youy questions:
- Use Age Banding tables to report age bands. Ask if you need more help with age bands.
- Start schema is not needed for this simple model.
- Simple count the number of People by Country, Company, Region, Town, Job Type, Age Band or whatever.
- None are needed for this simple model.
- It is best practice to use geographic data types. Learn about geographic data types here:-https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-mobile-geofilteringhttps://learn.microsoft.com/en-us/power-bi/visuals/power-bi-map-tips-and-trickshttps://www.youtube.com/watch?v=LFMn3wnruNcPlease click thumbs up because I have tried to help.
Then click [accept solution] if it works.
Thank you
Hi DanieleR , to overcome this, you need to do some modeling.
At first, let's rebuild SalaryData with these columns
Town_Code GenderKey CategoryKey Salary abcde 2 1 19.1 abcde 2 2 13.2 abcde 2 4 9.7 abcde 1 6 18.6 Now, we need to create a bridge table that handles both job type and age group
DimCategory
CategoryKey CategoryType CategoryName SortOrder 1 JobType Executives 1 2 JobType Mid_Managers 2 3 JobType Employees 3 4 AgeGroup 18to25 10 5 AgeGroup 26to49 11 6 AgeGroup 50plus 12
We have location informations so create another table for thatDimTown
Town_Code TownName Department Region abcde Paris Paris ĂŽle-de-France fghij Lyon RhĂ´ne Auvergne-RhĂ´ne klmno Marseille Bouches-du-RhĂ´ne Provence-Alpes-CĂ´te
and To get the gender Create another table
DimGender
GenderKey Gender 1 Male 2 Female
And, finally create this relationship with your other old tables
SalaryData → DimCategory (CategoryKey)
SalaryData → DimTown (Town_Code)
SalaryData → DimGender (Sexe)
Population → DimTown (Town_Code)
Enterprises → DimTown (Town_Code)
Now, you have an organized model to answer your all queries.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!Thank you! I think the solution you proposed is very clever and addresses my problem well. I just have one more question: would it make sense to apply the same approach you used for the Salary table to the Population table (because also in this case I have multiple variable combinations I'd like to keep in my data model), using a bridge table to connect and manage relationships?
Hi DanieleR
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Thanks for your help! Just to clarify: my dataset doesn't contain individual-level data : it's already aggregated. What I showed you, it was my data already partially modified. The original format includes several structured tables like the ones below:
Population
- "Town Code", "Town Name", "Cohabitation Type", "Age Category (5-year step)", "Sexe", "#People"
Entreprise
- "Town_Code", "Town_Name", "Region_Code", "Department_Code", "Total_Enterprises", "Enterprises_1_to_5", ... , "Enterprises_500_plus"
Geo_Info
- "Town_Code", ...
Salary
- "Town Code", "Town Name", "Avg_Net_Hourly_Salary", ..., "Avg_Net_Hourly_Salary_Workers", ..., "Avg_Net_Hourly_Salary_Employees_Female", ..., "Avg_Net_Hourly_Salary_50Plus", ..., "Avg_Net_Hourly_Salary_50Plus_Female"
