topic Re: SCD2 Implementation with 5 Master data time dependent tables in QlikView

SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Wed, 14 Oct 2015 04:10:50 GMT

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.

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Wed, 14 Oct 2015 05:21:53 GMT

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.

Re: SCD2 Implementation with 5 Master data time dependent tables

qlikviewwizard — Wed, 14 Oct 2015 05:39:56 GMT

Hi Vishnu,

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

Incremental Load in Qlikview - Sources

Best Regards,

Arjun

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Wed, 14 Oct 2015 07:25:10 GMT

HI Arjun,

Thanks for the information, But my requirement is different

Regards,

Vishnu.

Re: SCD2 Implementation with 5 Master data time dependent tables

hic — Wed, 14 Oct 2015 09:46:23 GMT

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

See

Slowly Changing Dimensions

IntervalMatch and Slowly Changing Dimensions

HIC

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Wed, 14 Oct 2015 10:10:13 GMT

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.

Re: SCD2 Implementation with 5 Master data time dependent tables

hic — Thu, 15 Oct 2015 13:06:38 GMT

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

Re: SCD2 Implementation with 5 Master data time dependent tables

Thu, 15 Oct 2015 13:19:37 GMT

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

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Thu, 15 Oct 2015 13:47:59 GMT

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;

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

Re: SCD2 Implementation with 5 Master data time dependent tables

Thu, 15 Oct 2015 14:02:07 GMT

The keys must be from your Transactions table.

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Thu, 15 Oct 2015 14:03:40 GMT

HI Henric,

If i try to create a separate bridge table for each combination i am ending up with a circular reference with respect to employee information as it consists i both the bridge tables.

Regards,

Vishnu.

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Thu, 15 Oct 2015 14:05:21 GMT

Henric,

Here is the circular reference what i am getting.

Re: SCD2 Implementation with 5 Master data time dependent tables

vishnumusani — Thu, 15 Oct 2015 15:05:09 GMT

Hi Dathu,

I am placing the common join keys in transaction table after joining transaction table with bridge tables and deleting individual keys from Transaction table in order to avoid multiple joins.

Regards,

Vishnu.