I have some source tables like Customer, Order, ship, item, invoice. Among these source tables, I have to create 5 dimension tables and 1 Fact called orderFact using sql server queries just to test data. So i have created 5 dimensions and pulled dimension keys from each dimension and loaded into fact using join. For measures I have joined those 5 sources created a Rawfact table which have all measures.
Now loading into fact I have joined Rawfact with all dimensions and get keys and for measures i directly pulled from rawfact. Is this process right or we can do it by some other method?
And I want to avoid any Cartesian product for below queries. What I can do to avoid this?
Plz refer below query:
DimCustomer, DimOrder, DimShip,DimItem, DimInvoice and Fact is FactOrder:
select o.ord_id, o.full_order_value,o.open_order_value,o.div_code, o.order_type_code,o.order_status,o.order_date,
from order o
inner join ship s on s.ord_id=o.ord_id
inner join inv i on i.cust_num=o.cust_num
inner join item it on it.item_num=ol.item_num
inner join customer c on c.cust_num=o.cust_num
INSERT into [dbo].Fact_SalesOrder
Customer_fk, Order_fk, Item_num_fk, Ship_fk,inv_id_fk, full_order_value, open_order_value, order_date,
dc.[Customer_pk], di.[Item_num_pk], do.[Order_pk], ds.[Ship_pk], di.[Item_pk]
FROM RawFacts rf
INNER JOIN [dbo].[Dim_Customer] dc ON rf.cust_num=dc.[Cust_num]
INNER JOIN [dbo].[Dim_Item] di on rf.Item_num=di.[Item_num]
INNER JOIN [dbo].[Dim_ship] ds on rf.ship_id=ds.[ship_id]
INNER JOIN [dbo].[Dim_invoice] di on rf.inv_id=di.[inv_id]
INNER JOIN [dbo].[Dim_order] do ON rf.ord_id=do.[ord_id]