Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Star Schema Design Question

3 posters

Go down

Star Schema Design Question Empty Star Schema Design Question

Post  Sameer09021990 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

Back to top Go down

Star Schema Design Question Empty Re: Star Schema Design Question

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

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

http://aginity.com

Back to top Go down

Star Schema Design Question Empty Re: Star Schema Design Question

Post  RM7 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

Back to top Go down

Star Schema Design Question Empty Re: Star Schema Design Question

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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