SK and ID columns in a Fact table

View previous topic View next topic Go down

SK and ID columns in a Fact table

Post  umutiscan on Thu May 10, 2012 9:51 am

Hi,
Let's say I have a simple "customer purchase" table. It is a daily snaphot and it has product purchase amounts of customers.
And I have customer and product dimensions.

F_CUST_PURCHASE
---------------
SNP_DATE_SK
CUSTOMER_SK
PRODUCT_SK
PURCHASE_AMOUNT

D_CUST
---------------
CUST_SK
CUST_ID
VALID_FROM
VALID_TO
CUST_TYPE

D_PRODUCT
---------------
PRODUCT_SK
PRODUCT_ID
VALID_FROM
VALID_TO
PRODUCT_NAME
PRICE


Suppose that business users are trying to get a report like below, where product price is greater than 1000.


YEAR MONTH TOTAL_SALE_AMOUNT CUSTOMER_TYPE PRODUCT_NAME
201201 1200000 B PRODUCT_1
2012 01 130000 C PRODUCT_2
2012 01 130000 B PRODUCT_2

Product price and customer type may change at any time, and if the business users are interested in the price and the customer type at snapshot time I can join fact and dimensions using SK columns. There is no problem.

There may be a difference between actual price and purchase price of a product. If they are interested in the actual prices and actual customer types there is a problem because SK columns are mapping to an older version of the dimensions. How can I resolve this kind of problem? May I also hold CUSTOMER_ID and PRODUCT_ID columns in the fact table?

Thanks in advance..

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: SK and ID columns in a Fact table

Post  ngalemmo on Thu May 10, 2012 10:36 am

You do not store natural keys in a fact table (unless it is a degenerate dimension). Perform a self-join on the dimension using the natural key to locate the current version of the dimension row.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SK and ID columns in a Fact table

Post  umutiscan on Fri May 11, 2012 4:44 am

Thanks for your response.

I can easly write a query thet performs a self join on the dimension. But I wonder if it can be easily implemented in BO. We are using BO as reporting tool and I don't have too much info about it.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: SK and ID columns in a Fact table

Post  ngalemmo on Fri May 11, 2012 9:41 am

You define a derived table in BO that contains the self join.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SK and ID columns in a Fact table

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