Tuesday, March 27, 2012

Basic Key Field question

I'm not sure if this is an SSAS question or an SSIS question.....I'll start here with basic theory/SSAS question.....

Simple design:

dimPriceBasis

PriceBasisKey (PK,int,not null)

PriceBasisCode (int,null)

PriceBasis (varchar(50),null)

dimTime

TimeKey (PK,int,not null)

AltDateKey (datetime,null)

factOrders

OrderDateKey (FK,int,not null)

ShipDateKey (FK,int, not null)

PriceBasisKey (FK,int, not null)

OrderID(bigint,not null)?

Amount (float, null)

Price (money,null)

I am having trouble conceptualizing key field set up. I had this cube up and running yesterday but then I started thinking about it and now I'm lost. Foreign keys in the fact table are supposed to be integer (counters) that point to integer primary keys in the dim tables. If you then combine the FK fields to serve as the primary key of the fact table I am confused when I go to load data as to how I'll generate integer counters based on the data in my OLTP Order table that will provide unique links to my dim tables and provide a unique key for the fact table. (OrderID is the OLTP table primary key.) All the data is basically coming out of one table for now.

My next question would be how to generate surrogate keys but that would be an SSIS question.

"If you then combine the FK fields to serve as the primary key of the fact table " - not sure why - it's not always applicable. For example, in your case, couldn't there be multiple orders with the same PriceBasis/OrderDate/ShipDate combination?

No comments:

Post a Comment