Click here to Skip to main content
15,867,568 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:

Dimension:
DimCustomer, DimOrder, DimShip,DimItem, DimInvoice and Fact is FactOrder:

Loading Rawfact:

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

1 solution

make sure to add column store on the fact table
CREATE CLUSTERED COLUMNSTORE INDEX CCSI_journal_date  
    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