I have the following model:
My dimensions are product, customer, DateTable
My fact tables is:
FactSales (columns: order num, prod_id, cust_id, date, num of items, total amount, cost of pkg, lineitemid, lineitemqty, lineitemprice). The granularity of this table is at the order detail level (so the order header data is repeated).
This is a star schema.
I have a requirement to add FactCharges (columns: order num, prod_id, cust_id, date, chargetype, chargeamt). The granularity is at the OrderHeader level.
And the user must be able to drill through from the Sales to the Charges data.
I am thinking to solve this by either of following approaches:
Create a new dimension table called Orders which will have distinct list of order nums. Then connect this to both FactSales and FactCharges on ord num.
Additionally I can include prodid, custid, date into the Orders dim and make this table sit between the dimensions and both facts.
Another way I'm thinking is to use something like TREATAS to create a virtual relationship on Ord Num between FactSales and FactCharges.
Is there a better pattern for this requirement?