SK and ID columns in a Fact table

[umutiscan]

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
2012	01	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..

[ngalemmo]

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.

[umutiscan]

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.

[ngalemmo]

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

[Sponsored content]