April 15, 2015 at 4:24 am
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:
Dimension:
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,
s.num_of_pallets,s.num_of_cartons,s.shipment_value,s.ppd_coll,s.ship_status,
i.invoice_amt,
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_Order
(
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
)
select
dc.[Customer_pk], di.[Item_num_pk], do.[Order_pk], ds.[Ship_pk], di.[Item_pk]
rf.full_order_value,rf.open_order_value,rf.order_date,
rf.num_of_pallets,rf.num_of_cartons,
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]
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply