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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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 

 

 

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

 

 

 

 

 

 

 

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.