Click here to Skip to main content
15,667,422 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,
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:

Loading Rawfact:

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,
it.net_weight, it.gross_weight,it.warranty_days,it.item_type,it.item_num,
c.terr_code, c.largest_bal,c.last_amt_pay,c.last_inv_amt,c.num_invoice_paid,c.cust_num,
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
where o.order_date>'2012-12-31'

Loading FactOrder:
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,
 num_of_pallets, num_of_cartons,
 net_weight,  gross_weight

dc.[Customer_pk], di.[Item_num_pk], do.[Order_pk], ds.[Ship_pk], di.[Item_pk]

rf.net_weight, rf.gross_weight

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]
Updated 10-Jun-22 18:40pm

1 solution

make sure to add column store on the fact table
    ON [dbo].[DimJournal_dest]
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ] 
[ ; ]  
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900