Data warehousing and loading data by using ssis package

0
votes

I am new to Data warehousing design, I have created a data warehouse design as of my knowledge.

For this database I have created 4 dimensions and 1 fact table.

  1. For all dimensions i didn't kept primary key constraints.
  2. For each dimension having one unique column.
  3. with that unique column only SCD is implemented in SSIS package.
  4. For fact table directly data porting from source database by using query.

Can any one suggest on above mentioned points.
Any modifications have to do or is this correct?

1
ssisdata-warehouse

1 Answers

0
votes

  1. Dimensions have their own auto-incrementing key, so if you mean that you did not copy the key from the source system that is OK.

  2. Not sure what this means. Dimensions do have the business key which uniquely identifies a "dimension object" (customer, store, product), but the column does not have an unique constraint. For SCD type 1 it happens to be unique, but that is a special case.

  3. So, you are using the SCD in SSIS -- fine.

  4. Wrong -- at least the way I understand this. When loading fact tables, you have to look-up primary keys from each dimension based on the business key.

Take a look at this example.