fact table should have measures from 'weak entity' or only parent entity's fields should be entered

Question

i am new to dimension modeling and OLAP. I am trying to create a dimension model for a shop.

"order" table is having columns:

'order_id(auto generated), total_order_cost, date, product_Set_Id'.

"Product_set" table (contains products ordered in each order i.e. multiple rows for each order, tables logically linked by 'product_set_id' column) has columns:

'product_set_id, product_name, quantity,Cost_per_quantity'.

In the ER model "product_set" table is kind of weak entity dependent on "order" table.

My doubt is in the fact table case 1: i should add only 'order_id(fk)' and 'total_order_cost(as measure)' ==>in this case measures from "product_set" won't be there in fact table.

or case 2:i should add 'order_id(fk)','product_set_id(fk)' and 'cost_per_quantity(measure), quantity(measure), total_order_cost(measure)' ==>in this case there will be multiple rows for same 'order_id' and 'total_order_cost'

There are other some tables like "customer" etc but i have doubt in above mentioned.

Thanks in advance!

Answer 1

one suggestion always made is to create a surrogate key on the tables. My fact sales is such that it has the surrogate key which allows me to have the orderline data there and each orderline identified by a surrogate key (which I don't really use - but its not an issue) . That way you can follow case 2.

Does that answer the question?