Understanding Dimensional Modeling

[jswartz_09]

Hi everyone, 

I'm beginning my journey on trying to create a dimensional model for our Power BI environment. I come from an SQL background in reporting of just writing SQL queries for the end user to share via an excel file, but finally we are working towards integrating Power BI as a BI tool for our division. 

However I am finding this to be a bit different than just simply writing an SQL query. 

I am learning that in Power BI you can't simply import the tables you have in SQL server and just join like you normally would in the Power BI Desktop modeling view and expect the same results. I'm finding that one to many relationships is the norm and they have to connect via the many side. Which is where I have come to this design of Dimensional Modeling. 

Most books or examples I find deal with sales or numerical data but we run a case management system so sometimes the examples don't always apply or are hard to replicate using our data. 

I'm really just looking for expert advice on how I should be taking something that I would normally write an SQL query for and create a dimensional model out of it, so that it can be used in Power BI. 

So getting down to an example: 

Lets say a requestor is asking me: For a List of cases that were completed in years 2024 and 2025 by region. The requestor would also like to know of those cases completed, what were the education information and employment information of each of the participants. 

I would simply write my SQL as the following:

select  a2.region, a1.pk_Participant_Id, a1.last_name, a1.first_name, a2.pk_Case_Id,  a3.completion_date, a3.completion_reason, a4.education_level, a5.employment_status
from participant a1
left join case a2
on a1.pk_Participant_Id = a2.fk_Participant_Id
left join completion a3
on a2.pk_Case_Id = a3.fk_Case_Id
left join education a4
on a1.pk_Participant_Id = a4.fk_Participant_Id
left join employment a5
on a1.pk_Participant_Id= a5.fk_Participant_Id 
where a3.completion_date between 2024-01-01 and 2025-12-31             

How would you go about creating a Dimensional model just using the tables from this query?
Participant Table Columns:

• pk_Participant_Id
• last_name
• first_name
• date_of_birth
  Case Table Columns: 

• pk_Case_Id
• fk_Participant_Id
• region
• created_date
  Completion Table Columns:
  

• pk_completion_Id
• fk_Case_Id
• completion_date
• completion_reason

Education Table Columns:

• pk_Education_Id
• fk_Participant_Id
• education_level
• created_date

Employment Table Columns:

• pk_Employment_Id
• fk_Participant_Id
• employment_status
• created_date

Appreciate any help you can all give me. 

[v-achippa]

Hi @jswartz_09,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for the data. Based on the requirement here the Completion Table should be the fact table since the analysis is based on completed cases and completion date. Case and Participant Tables should be dimension tables. Education and Employment Tables must be reduced to one row per participant (latest record) before loading, otherwise power bi cannot determine which value to use.
With this model, filtering completion date for 2024–2025 and grouping by region will return the same result as the sql query.

Thanks and regards,

Anjan Kumar Chippa

[jswartz_09]

I need a little more information. How would the fact table look? What are the columns in the fact table? How would you model it with Dimensions?

[v-achippa]

Hi @jswartz_09,

Since your requirement is based on completed cases, the grain should be like one row per completed case. So the Completion table should be the fact table, by this we make sure that there is no duplication of cases.

• The Case and Participant tables are dimension tables that describe that completed case.
• The important part is Education and Employment. In sql, if a participant has multiple education or employment records, the query just duplicates rows. Power bi cannot do that cleanly because relationships need to return a single value, that is why education and employment need to be reduced to one row per participant like for example the latest record.

So instead of flattening the data like sql, we create a clean fact table and connect the respective tables around it. With this model it gives the correct results.

Thanks and regards,

Anjan Kumar Chippa

[jswartz_09]

What are the columns of the fact table?

[v-achippa]

Hi @jswartz_09,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[GeraldGEmerick]

@jswartz_09 Would it be possible to provide sample data for these tables? That would make it much quicker to mock up and give you an answer.

[jswartz_09]

Of course. 
Here is the Participant Table: 

pk_Participant_Id	last_name	first_name	date_of_birth
1	Jones	George	1/1/1955
2	Bradshaw	Terry	3/1/1975
3	Smith	Bob	10/1/2001
4	Depp	Johnny	7/1/1990
5	Durham	Bull	2/1/1980
6	Lincoln	Abe	11/15/1994
7	Bass	Billy	12/2/1988
8	Khan	Shaka	9/15/1964
9	Streisand	Barb	6/10/1989
10	Connely	Jennifer	4/24/1990

 Case Table: 

pk_Case_ID	fk_Participant_Id	region	created_date
1	3	North	1/1/2023
2	6	South	1/15/2023
3	7	East	1/16/2023
4	1	West	2/5/2023
5	4	North	4/1/2023
6	3	North	5/25/2023
7	6	South	7/29/2023
8	8	West	1/1/2024
9	2	West	2/10/2024
10	10	East	4/10/2024
11	5	South	5/1/2024
12	9	North	7/25/2024
13	5	West	8/1/2024
14	8	North	9/20/2024
15	1	East	11/15/2024
16	7	East	1/2/2025
17	2	South	1/15/2025
18	3	North	5/11/2025
19	10	North	8/12/2025
20	7	West	12/20/2025

Completion Table: 

pk_completion_id	fk_case_id	completion_date	completion_reason
1	1	3/27/2023	Unsuccessful
2	2	6/15/2023	Unsuccessful
3	4	8/5/2023	Successful
4	5	10/1/2023	Successful
5	7	12/1/2023	Unsuccessful
6	3	1/16/2024	Successful
7	6	5/25/2024	Successful
8	8	7/1/2024	Unsuccessful
9	11	7/1/2024	Unsuccessful
10	9	8/10/2024	Successful
11	10	10/10/2024	Successful
12	12	1/1/2025	Successful
13	13	3/1/2025	Successful
14	15	5/15/2025	Unsuccessful
15	16	7/2/2025	Successful
16	14	9/1/2025	Successful
17	17	12/31/2025	Successful

Education Table:

pk_Education_Id	fk_participant_id	education_level	created_date
1	3	High School	1/1/2023
2	6	High School	1/15/2023
3	7	Some College	1/16/2023
4	1	Bachelors	2/5/2023
5	4	High School	4/1/2023
6	8	Some College	1/1/2024
7	2	Bachelors	2/10/2024
8	10	Doctorate	4/10/2024
9	5	High School	5/1/2024
10	9	Some College	7/25/2024
11	8	Bachelors	9/20/2024
12	1	Masters	11/15/2024
13	7	Bachelors	1/2/2025
14	3	Some College	5/11/2025

Employment Table:

pk_Employment_Id	fk_participant_id	employment_status	created_date
1	3	unemployed	1/1/2023
2	6	part time	1/15/2023
3	7	part time	1/16/2023
4	1	full time	2/5/2023
5	4	self employed	4/1/2023
6	6	full time	7/29/2023
7	8	full time	1/1/2024
8	2	unemployed	2/10/2024
9	10	full time	4/10/2024
10	5	part time	5/1/2024
11	9	unemployed	7/25/2024
12	7	full time	1/2/2025
13	2	full time	1/15/2025
14	3	part time	5/11/2025
15	10	part time	8/12/2025

In case those are hard to read here is some photos: 

Participant:

Case:

Completion: 

Employment: 

Education: