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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Doharaquax
Frequent Visitor

Correct data model

Good day,

 

I need advice on how to solve this situation. I have "Job" tables from the ERP system that contain individual jobs. This table has dozens of new items every day. I also have a table "CalcProcessCost" in which there are individual calculation items such as operation 1, operation 2, etc. and their prices. I don't know how to create a data model. CalcProcessCost is definitely a fact table. Is it correct to specify the "Job" table as a dimensional table, even though new items are added to it all the time? Cardinality is 1:N.

 

Thank you in advance for the answers

1 ACCEPTED SOLUTION
AmiraBedh
Most Valuable Professional
Most Valuable Professional

From my point of view, it is appropriate to specify the "Job" table as a dimensional table,
even though you will have new items which can be added to it all the time. Having dimension tables that are updated frequently with new records is known as a Type 2 dimension,
where each change to a dimension attribute creates a new record in the dimension table.
In your case, the "Job" table can be a Type 2 dimension, where each new job added to the table creates a new record with a unique identifier,
and any changes to the attributes of an existing job create a new record with a new version identifier.
Your fact table "CalcProcessCost" would then have a foreign key reference to the dimension table using the unique identifier of the job.

The cardinality of the relationship between the "Job" table and the "CalcProcessCost" table is 1:N, which means that each job can have multiple calculation items associated with it. Therefore, the fact table "CalcProcessCost" would have a foreign key reference to the "Job" dimension table.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

5 REPLIES 5
AmiraBedh
Most Valuable Professional
Most Valuable Professional

From my point of view, it is appropriate to specify the "Job" table as a dimensional table,
even though you will have new items which can be added to it all the time. Having dimension tables that are updated frequently with new records is known as a Type 2 dimension,
where each change to a dimension attribute creates a new record in the dimension table.
In your case, the "Job" table can be a Type 2 dimension, where each new job added to the table creates a new record with a unique identifier,
and any changes to the attributes of an existing job create a new record with a new version identifier.
Your fact table "CalcProcessCost" would then have a foreign key reference to the dimension table using the unique identifier of the job.

The cardinality of the relationship between the "Job" table and the "CalcProcessCost" table is 1:N, which means that each job can have multiple calculation items associated with it. Therefore, the fact table "CalcProcessCost" would have a foreign key reference to the "Job" dimension table.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Thank you for answer.

I would also like to know how to solve the situation when I have the "Customer" table, which is linked to the "Job" table. If I were to connect it like this, it would not be a Star Schema but rather a snowflake.
I read everywhere that star schema is better than Snowflake
I solved it by power querying the CustomerId from the "Job" table to "CalcProcessCost" so that I can directly join the "Customer" table with "CalcProcessCost"
Am I doing this right?

 

thank you 

 

 

AmiraBedh
Most Valuable Professional
Most Valuable Professional

Can you please provide a portion of your data and the structure of your tables so we can help you ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

My point is that the data from the EPR system is structured in such a way that the "CalcProcessCost" table is not basically linked to the "Customer" table.
I completed the link by using merge Queries in POWERQUERY and adding the Calculation.Job.CustomerID link (green in the picture) from the "Jobs" table to the "CalcProcessCost" table. The simplest thing would be to connect the "Customer" table with the "Jobs" table, because they can be related to each other in the beginning, but then it would no longer be StarSchema. I also had to fill in the CreationDate (yellow in the picture) from the "Jobs" table.

The model works and the reports work, but I don't know if I'm making it unnecessarily complicated just because I want StarSchema.

 

 

 

 

My model:

 

Doharaquax_0-1677253935736.png

 

 

model from ERP:

 

Doharaquax_1-1677255749525.png

 

 

 

 

 

 

 

 

AmiraBedh
Most Valuable Professional
Most Valuable Professional

Based on the info you provided, your star schema seems logical to me.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.