Modeling - Orders and Shipments

[thescrimpton]

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!

[v-yinliw-msft]

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

[thescrimpton]

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.

[v-yinliw-msft]

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.

Then manage a relationship between two tables.

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.