0

I have a couple of modelling questions using several star schemas. There are different entities which I consider facts (they all share similar dimensions, dimcustomer, dimdate, dimshop, etc.), for example, orders, payments, inventory, credit cards movements and inventory. All of them have thousands of entries every day and each one has metrics which I would like to report from. If all of these are independent facts though, and thinking about facts shouldn’t be joined between them, a few problems present:

  1. Actually, payments and orders are related because payments are (sometimes) made for the orders. Both have a different granularity so I think they can’t be together in the same fact table.
  2. For reporting purposes, I am going to need to join all facts together to get the full revenue. I assume this is not something strange to do, but again I am joining facts together, is that ok?

Below there’s a rough schema of this example. I hope it helps. enter image description here

Thank you so much in advance.

Maria
  • 363
  • 4
  • 13
  • 1. _payments are (sometimes) made for the orders_ I guess there must be a key that allows you to associate payments to an order. This is often a "Purchase Order". Sometimes people create a purchase order dimension but that seems a little over the top to me 2. You wouldn't join them you would `UNION ALL` them. More likely you'd have a pre claculated summary fact that summarises these detail facts into a "Proft and Loss", but you can drill from the P&L back into the transactions. – Nick.Mc Sep 26 '22 at 11:13
  • thanks for your reply. 1.yes, I have the order_id as a FK to fact_orders which is what I am questioning, as there's a FK in a fact to another fact. I would prefer to keep all type of payments in the same fact payment table so creating an alternative table is tricky. 2. great point, it would definitely be a union. – Maria Sep 26 '22 at 11:23
  • In general, you don't link Fact tables. If you need to combine data from multiple facts then you query each one and then combine the resultsets using common dimensions. If you have business questions that need data from multiple fact tables then it may be worth considering building a new fact table that provides the information to answer these questions – NickW Sep 26 '22 at 13:17
  • To join across facts, you use conformed dimensions. The "star schema modelling" approach is to create a special "Order" dimension which sits between the two facts. It's a dimension table who's key is order_id. It's kind of redundant but it's the star schema way – Nick.Mc Sep 26 '22 at 23:10
  • Thank you both, I will make sure which kind of questions they make and I might add further order information to the payments facts (even if redundant), create another fact for this or an extra dimension as suggested. – Maria Sep 27 '22 at 07:40

0 Answers0