SCD2 Implementation with 5 Master data time dependent tables

[vishnumusani]

Hi

I do have total 5 Master data tables and all those are time dependent. In Employee table i have all other MD tables unique key and also all unique keys of these are available in my Transaction table.

MD Tables: Employee, Organization, Person, Position, Job.

Transaction : One Table

I am able to establish a bridge table using 2 master tables along with a transaction,but when i am trying the same approach to add another dimension table to script it is causing issues.

Could you please let me know what could be the approach to build the model by considering the time dependencies.

Regards,

Vishnu.

[vishnumusani]

Hi,

Forgot to mention that my Employee table is interdependent with remaining dimension tables. So I followed below approach to build model

1) Join Employee & Organization Table using "Organization Unit with inner join and stored that data into a temporary table

2) used Range max & Range Min along with Where clause to identify overlapping dates for Both Employee & Organization

3) Tried to integrate intermediate table with transactions table as per SCD process.

When i try to follow similar approach with Employee and Job and try to integrate the model it is coming incorrectly by creating circular references.

Could you please let me know where exactly i am going wrong.

Regards,

Vishnu.

[qlikviewwizard]

Hi Vishnu,

Please check this thread for detailed information. Hope this will help you.

Incremental Load in Qlikview - Sources

Best Regards,

Arjun

[vishnumusani]

HI Arjun,

Thanks for the information, But my requirement is different

Regards,

Vishnu.

[hic]

That should be straightforward - although a lot of coding...

See

Slowly Changing Dimensions

IntervalMatch and Slowly Changing Dimensions

HIC

[vishnumusani]

Hi Henric,

Thanks for responding,

After going thru the mentioned documents only i am able to create attached models but both of them are not giving the desired results.

Let me know where exactly i am going wrong.

Regards,

Vishnu.

[hic]

You have 5 dimensions, each with different interval definitions. Then you should have 5 bridge tables. Now it looks as if you have created one common bridge table for all dimensions EmpOrgJobFinal.

HIC

[#8]

Can you please share your script. I believe you data model looks correct but did you make proper joins ?

[vishnumusani]

Hi Dathu,

Thanks For Responding below is the code

////3 Basic lods for each master data after that below is the code

*** Employee Organization Join******************

EmpOrgTemp:

Load distinct

EmpIntervalID,

[SPID],

[SPID] as TmpSPID,

[M_EMP_Organizational_Unit] as TmpOrgUnit,

[EMP_Valid_From],

[EMP_Valid_To]

resident Employees;

Inner Join

load distinct

OrgIntervalID,

[OPID],

[OPID] as TmpOrgUnit,

[Org_Valid_From],

[Org_Valid_To]

resident Organizations;

*********************************************

************* Employee Job Join ****************

EmpJobTemp:

Load

EmpIntervalID,

[SPID] as TmpSPID,

[SPID],

[M_EMP_Job] as TmpJob,

[EMP_Valid_From],

[EMP_Valid_To]

resident Employees;

Inner join

Load

JobIntervalID,

[JPID],

[JPID] as TmpJob,   

[Job_Valid_From],

[Job_Valid_To]

resident Jobs;

********************************************************

*********Concatinating Above Two *************************

EmpOrgJob:

Load

*,

  autonumber(TmpSPID & '|' & EO_From_Date & '|' & EO_To_Date) as EmpOrgIntervalID;

Load

EmpIntervalID,

OrgIntervalID,

TmpSPID,

date(rangemax([EMP_Valid_From],[Org_Valid_From]),'DD.MM.YYYY') as EO_From_Date,

date(rangemin([EMP_Valid_To],[Org_Valid_To]),'DD.MM.YYYY') as EO_To_Date

resident EmpOrgTemp

where [EMP_Valid_From] <=[Org_Valid_To] and [Org_Valid_From] <=[EMP_Valid_To] ;

concatenate

Load

*,

  autonumber(TmpSPID & '|' & EJ_From_Date & '|' & EJ_To_Date) as EmpJobIntervalID;

Load

EmpIntervalID,

JobIntervalID,

TmpSPID,

date(rangemax([EMP_Valid_From],[Job_Valid_From]),'DD.MM.YYYY') as EJ_From_Date,

date(rangemin([EMP_Valid_To],[Job_Valid_To]),'DD.MM.YYYY') as EJ_To_Date

resident EmpJobTemp

where [EMP_Valid_From] <=[Job_Valid_To] and [Job_Valid_From] <=[EMP_Valid_To] ;

*******************************************************************

********** Creating A UniqueKeyin the common Table*******************

EmpOrgJobFinal:

Load *,

(EmpOrgIntervalID & '|' & EmpJobIntervalID) as Key

resident EmpOrgJob;

***********************************************************************

******************Transactions************************************

Transactions:

LOAD Employee as TmpSPID,

     Territory,

     Country,

     Region,

          Job as TmpJPID,

     [Organization Unit] as TmpOPID,

     (Date#(DAY,'DD.MM.YYYY')) as DAY1

     From

hrpa_c01.xls

(txt, unicode, embedded labels, delimiter is '\t', msq);

*******************************************************************

***********Integrating Between Transaction & Concatenated Table***************

EmpOrgTran:

Intervalmatch(DAY1,TmpSPID)

Load Distinct EO_From_Date,EO_To_Date,TmpSPID Resident EmpOrgJobFinal;

Left Join (Transactions)

Load

TmpSPID,

  DAY1,

  Autonumber(TmpSPID & '|' & EO_From_Date & '|' & EO_To_Date) As EmpOrgIntervalID_T

Resident EmpOrgTran;

EmpJobTran:

Intervalmatch(DAY1,TmpSPID)

Load Distinct EJ_From_Date,EJ_To_Date,TmpSPID Resident EmpOrgJobFinal;

Left Join (Transactions)

Load

TmpSPID,

  DAY1,

  Autonumber(TmpSPID & '|' & EJ_From_Date & '|' & EJ_To_Date) As EmpJobIntervalID_T

Resident EmpJobTran;

******************************************************************************

******* Creating Unique Key in Transaction Table*********************

Inner Join (Transactions)

Load * , (EmpOrgIntervalID_T & '|' & EmpJobIntervalID_T) as Key resident Transactions;

Drop Field  TmpSPID,TmpOrgUnit,TmpJob;

Drop Table EmpOrgTemp,EmpJobTemp,EmpOrgTran,EmpJobTran,EmpOrgJob;

**********************************************************************

[#10]

The keys must be from your Transactions table.