Star Schema Design Question
3 posters
Page 1 of 1
Star Schema Design Question
Hi all,
I have a fact table which stores transaction between two customers. There is one dimension table customer also.
Customer(Dimension)
Customer_Id, Customer_Name,Customer_Address,Customer_Skey.
Customer_Skey is the primary key in the table.
Purchase (Fact Table)
Buy_Customer_Id, Sell_Customer_Id, Amount, Buy_Customer_Skey,Sell_Customer_Skey.
In the fact table Purchase, Buy_Customer_Skey and Sell_Customer_Skey are the foreign key mapping of column Customer_Skey of Customer table.
Now my question is in Star Schema Design, through which column I need to make the join.
Because if join only by Buy_Customer_Skey, if I want to get a report only of Sell_Customers I wont get it appropriately. And vice-versa if join is by Sell_Customer_Skey.
Kindly recommend what should be done when a fact table has two Surrogate Key mapping columns of the same dimension table.
Thank You
I have a fact table which stores transaction between two customers. There is one dimension table customer also.
Customer(Dimension)
Customer_Id, Customer_Name,Customer_Address,Customer_Skey.
Customer_Skey is the primary key in the table.
Purchase (Fact Table)
Buy_Customer_Id, Sell_Customer_Id, Amount, Buy_Customer_Skey,Sell_Customer_Skey.
In the fact table Purchase, Buy_Customer_Skey and Sell_Customer_Skey are the foreign key mapping of column Customer_Skey of Customer table.
Now my question is in Star Schema Design, through which column I need to make the join.
Because if join only by Buy_Customer_Skey, if I want to get a report only of Sell_Customers I wont get it appropriately. And vice-versa if join is by Sell_Customer_Skey.
Kindly recommend what should be done when a fact table has two Surrogate Key mapping columns of the same dimension table.
Thank You
Sameer09021990- Posts : 1
Join date : 2013-11-12
Re: Star Schema Design Question
You alias the table and use it twice in the query, one for each key.
select ... from fact f join customer a on f.sell_cust = a.cust_key join customer b on f.buy_cust = b.cust_key
select ... from fact f join customer a on f.sell_cust = a.cust_key join customer b on f.buy_cust = b.cust_key
Re: Star Schema Design Question
This is one of the concepts mentioned by Kimball called Role-Playing Dimensions.
Its one physical dimension table in the DB but conceptually there will be multiple (more than 1) roles being played by the single dim in the warehouse.
As mentioned by ngalemmo, you need to use an alias in the reporting layer.
Its one physical dimension table in the DB but conceptually there will be multiple (more than 1) roles being played by the single dim in the warehouse.
As mentioned by ngalemmo, you need to use an alias in the reporting layer.
RM7- Posts : 3
Join date : 2012-10-10
Similar topics
» Question on symmetry in Star schema
» Simple Star schema question
» Design of Star Schema?
» makeing a stand for star schema design
» Star schema design / Reporting in Excel/PowerPivot
» Simple Star schema question
» Design of Star Schema?
» makeing a stand for star schema design
» Star schema design / Reporting in Excel/PowerPivot
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum