Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
83 | |
79 | |
53 | |
39 | |
37 |
User | Count |
---|---|
104 | |
85 | |
47 | |
44 | |
42 |