Star Schema Design Question

View previous topic View next topic Go down

Star Schema Design Question

Post  Sameer09021990 on Tue Nov 12, 2013 10:02 am

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

Sameer09021990

Posts : 1
Join date : 2013-11-12

View user profile

Back to top Go down

Re: Star Schema Design Question

Post  ngalemmo on Tue Nov 12, 2013 11:57 am

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
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Star Schema Design Question

Post  RM7 on Tue Jun 03, 2014 11:10 am

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.

RM7

Posts : 3
Join date : 2012-10-10

View user profile

Back to top Go down

Re: Star Schema Design Question

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum