I have a header-detail type of a requirement.
For example:
My dimensions are product, customer, DateTable
My fact table is FactOrders (columns: order num, prod_id, cust_id, date, num of items, total amount, cost of pkg).
This is a star schema.
There is a new requirement to add/show data for order line items, and to make a drill through such that when user clicks on an order they should be able to drill through to a new page showing the order lines.
Now I'm thinking about this requirement in terms of data modelling. Getting rid of the FactOrders, and just have FactOrderLines doesn't look like a solution due to the presence of cost of pkg because this is an order level value and cannot be apportioned to order lines.
2 solutions come to my mind:
Create FactOrderLines and connect it to FactOrders. But this will make the model a snowflake.
Another option is to create a new dimension that has the distinct order numbers, and treat this as a dimension table, then create FactOrderLines. Then, connect this dim table to FactOrderLines and FactOrders. Thus I retain the star schema by not having relation between FactOrders and FactOrderLines, yet I can achieve the drill through behaviour in OrderNum.
What is the recommended pattern for such a requirement?
According to the Italians, merging the header into your existing fact table and keeping just a single fact table is the way to go.
https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/
Regarding the apportionment of cost of pkg, don't apportion. Just make sure that when you do calculations on it, you do it at the header level (distinct counts etc).