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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
thescrimpton
Frequent Visitor

Modeling - Orders and Shipments

Good day everyone. Would like to know how to approach the modeling of orders and shipments. These two are both transactional tables. 

 

Should I

- create 2 fact tables for each of them

- Or should I create 1 fact table of them combined

 

Additional information and tips will be much appreciated. Thanks!

3 REPLIES 3
v-yinliw-msft
Community Support
Community Support

Hi @thescrimpton ,

 

Could you please describe your purpose more clearly and what calculations are required? Or is it unclear about what conceptual issues? If calculations are required, provide sample data and expected results, remove private and sensitive data.

 

Best Regards,

Community Support Team _Yinliw

I guess my question is more generic and nothing specific. I am having a hard time grasping the concepts of data modeling regarding the scenario below.


If I have two transaction tables in OLTP side, Orders and Shipment. How should I go about the design?

- Do I need to create one fact for each?

- Or depending on what is needed?

- I am also confused since they are both transactional tables (order is parent of shipment)
Should I only create Shipment FACT? But if this is the case, I will be treating Order as Dimension which is kind of a bit strange (transactional table as dimension???).

- Or should I create a fact table with both Order and Shipment information?

Maybe my question does not make any sense as I am new to this modeling thing, but I do understand the concept between Dimension and Fact. I guess I wanted to know given two related transactional tables (such as order and shipment), what should I consider in designing the corresponding facts related to them.

Hi @thescrimpton ,

 

You can keep two tables, but you need to check your tables, and it is better that they meet some conditions to facilitate calculation and inspection.

First of all, you need to ensure that both tables have a column that can be uniquely identified as a column, which will reduce a lot of trouble when querying.

You mentioned that the Orders table is the parent table of the Shipping table, and you'd better make sure that there is a common column in both tables.

I create a example for you to explain the tables.

vyinliwmsft_0-1667532503382.png

 

vyinliwmsft_1-1667532503384.png

 

 

Then manage a relationship between two tables.

vyinliwmsft_2-1667532503389.png

 

vyinliwmsft_3-1667532503391.png

 

 

Here is my pbix file.

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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