CODEWITHSUNDEEP
etldimensioncognos-10
mistafl0
mistafl0

Reputation: 1

Cognos data manager: incomplete dimension delivery

I'm a beginner with ETL tools and my wish is to create a dimension table that will be used as a history table.

I need a surrogate key but i do not necessarily need to track changes (slowly changing dimension) because my data sources table is already designed like: MyTable(ID,DIM,START_DATE,END_DATE).  

The query normally retrieves 500+ rows, but after the build execution, my  delivered table has only 150- rows. I get only one row by business key (ID) which is normal...  but does how to create a history table in that case ? Sorry if it is a silly question.  

I am using: -Cognos data manager version 10.2.1 -Oracle version 12.1.0.2.0   Thank you in advance for your responses !

Upvotes: 0

Views: 122

Answers (1)

RajBedi
RajBedi

Reputation: 11

This looks like a Type 2 SCD. In order to maintain the history the ETL should be able to end date the current record with ( Current date -1 ) when a new record for existing ID comes to the source system. So, the new record will have Start_date as current date and End_date as some future date (eg 9999-12-31). Again, when another record for the same ID comes this record will be ended and the new record will be inserted. This way you will be able to maintain the history of all the records as well as when a particular record was active. Also, you need to make sure that no two records for the same ID are active on a particular date

Upvotes: 1

Related Questions