topic Re: Intentional Circular Loops in Data Model (Issue) in QlikView

Intentional Circular Loops in Data Model (Issue)

diwakarnahata — Tue, 06 Jan 2015 07:53:18 GMT

Hi All,

I am stuck at a critical data model issue.

Please refer the attached excel and the qvw where I am attempting the data model.

The data model has two fact tables Fact1 and Fact2, one relate table Rel which relates the two fact tables on keys F1Key and F2Key respectively, and it has two common Dimension tables Prod and Geo shared by the two fact tables through a linked table.

Now, there are two scenarios we need to address while reporting as below (please refer the attached qvw):

View 1:

We create a straight table with Desc column from the Relate table, and Vol1 and Vol2 metrics from the respective fact tables.

Now this should give the volumes associated with the descriptions. This scenario is working `fine, but I had to deliberately create a circular loop so that both the fact tables are connected to the relate table.

Note: The Relate, Product and Geo columns are expected to work as filters here.

View 2:

We create a straight table with PDesc column from the Product table, and Vol1 and Vol2 metrics from the respective fact tables.

Now this should give the volumes associated with the product descriptions. This scenario is not working fine for Vol2 metrics where the volume is coming as a result of association with the Rel table instead of the Product volume.This would have worked fine if there was no Rel table or if the circular loop was removed.

Note: The Relate, Product and Geo columns are expected to work as filters here.

Let me know what could be the best solution for the above two scenarios.

Thanks,

Diwakar

Re: Intentional Circular Loops in Data Model (Issue)

marcus_sommer — Tue, 06 Jan 2015 11:10:24 GMT

Without looking in your data and app - avoid circular loops! Most often it's the best (and easiest way) to concatenate the fact-tables to a single fact-table.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

- Marcus

Re: Intentional Circular Loops in Data Model (Issue)

diwakarnahata — Tue, 06 Jan 2015 15:09:38 GMT

Hi Marcus,

I agree to what you are saying. But, I cannot concatenate the fact tables as they are associated to each other from a relate table, and we show desc from the relate table against the Volume from the two fact tables.

Concatenating will give null output.

I tried another approach, to split the Prod and Geo hierarchies for the two fact tables (shown below), but this gives unexpected results in some scenarios, especially when we are showing Prod/Geo columns in the straight table. For e.g. if we want to have Product desc as a dimension, then i have to chose that columns from Prod1 or Prod2 and also the volume will come via the relate table association, which is an issue in this case.

I had thought of another approach (shown below) where i had created a copy of Fact2.

The idea was to use the Vol2 metric from Fact2 Copy if the dimension in the straight tables is coming from the relate table, and if the dimension is coming from the prod/geo table then the Vol2 from the original Fact2 table could be used. But even this approach has a limitation that the Product filters will not apply to Fact2 Copy table.

Let me know if any more info is required.

Any suggestions here will be really helpful.

Regards,

Diwakar

Re: Intentional Circular Loops in Data Model (Issue)

marcus_sommer — Tue, 06 Jan 2015 15:37:16 GMT

I would concatenate the fact-tables and link the dimension against this table and remove the link-table(s). But generally a link-table approach could work if the link-table created properly. The approach to hold a copy from the fact-table is quite awkward and not recommended. For more information see here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/29/clarity-vs-speed

- Marcus

Re: Intentional Circular Loops in Data Model (Issue)

diwakarnahata — Tue, 06 Jan 2015 16:27:15 GMT

I have tried that approach as well. PFA the test application which shows the same.

But since Fact1 and Fact2 are associated, concatenating them is resulting in NULL when the Desc is pulled in a straight table from the Relate table.

Let me know if i am missing anything.

Thanks,

Diwakar

Re: Intentional Circular Loops in Data Model (Issue)

Tue, 06 Jan 2015 16:27:16 GMT

You need to use the Flags while concatenating the fact tables to avoid unexpected results.

Re: Intentional Circular Loops in Data Model (Issue)

Tue, 06 Jan 2015 16:33:33 GMT

In your data model you have Synn Keys. What is the relation b/n Rel table & Fact table ? its both fact keys combination tied to Rel table or Individual Fact Key ?

Re: Intentional Circular Loops in Data Model (Issue)

diwakarnahata — Tue, 06 Jan 2015 18:05:38 GMT

The relate table is actually an affiliation table, which shows what is the affiliation between the two Fact tables. The relate table is connected to the Fact tables Fact1 and Fact2 on different keys namely - F1Key and F2Key respectively.

The synthetic table is coming because the concatenated table is trying to join the relate table on both the keys, and this also results in 0 rows in the straight table.

Let me know if you need any more info.

Thanks,

Diwakar

Re: Intentional Circular Loops in Data Model (Issue)

Tue, 06 Jan 2015 18:20:36 GMT

Please find the below script.

Prod:

LOAD * INLINE [

PKey,PDesc

101,'Prod-A'

102,'Prod-B'

103,'Prod-C'

104,'Prod-D'

];

// Added the RelKey to make assocation with Rel table.

Fact:

LOAD * , F1Key AS RelKey , 'F1' AS Flag INLINE [

PKey,GKey,F1Key,Vol1

101,11,10001,1000

102,11,10002,2000

103,12,10003,3000

104,12,10004,4000

];

Concatenate (Fact)

Fact2:

LOAD * , F2Key AS RelKey , 'F2' as Flag INLINE [

PKey,GKey,F2Key,Vol2

101,11,20001,1000

102,11,20002,2000

103,12,20003,3000

104,12,20004,4000

];

// As per your last comment on the discssion, the Rel table is tied to F1Key on Fact1 & F2Key on Fact2. I am not sure you have 2 separate table or only one Releate table have this.

TempRel:

LOAD * INLINE [

F1Key,F2Key,DESC

10001,20001,'AA'

10002,20001,'BB'

10003,20001,'BB'

10004,20001,'BB'

10001,20002,'BB'

10002,20002,'CC'

10003,20002,'CC'

10004,20002,'CC'

];

// Since the fact have both F1Key & F2Key on same field, we need to create the Rel table like this way.

Rel:

LOAD Distinct F1Key as RelKey , DESC Resident TempRel ;

Concatenate(Rel)

LOAD Distinct F2Key as RelKey , DESC Resident TempRel ;

Drop Table TempRel ;

Please let me know in case of any concerns.

Re: Intentional Circular Loops in Data Model (Issue)

Anonymous — Tue, 06 Jan 2015 19:02:58 GMT

Hi,

Try to concatenate fact tables:

Rel:
LOAD SubField([F1Key,F2Key,DESC],',',+1) as F1Key,
SubField([F1Key,F2Key,DESC],',',+2) as F2Key,
SubField([F1Key,F2Key,DESC],',',-1) as DESC
FROM

(ooxml, embedded labels, header is 1 lines, table is Data);

Fact1:
LOAD SubField([PKey,GKey,F1Key,Vol1],',',+1) as PKey,
SubField([PKey,GKey,F1Key,Vol1],',',+2) as GKey,
SubField([PKey,GKey,F1Key,Vol1],',',+3) as F1Key,
SubField([PKey,GKey,F1Key,Vol1],',',-1) as Vol1,
1 as Fact1
FROM

(ooxml, embedded labels, header is 1 lines, table is Data);

Concatenate
Fact2:
LOAD SubField([PKey,GKey,F2Key,Vol2],',',+1) as PKey,
SubField([PKey,GKey,F2Key,Vol2],',',+2) as GKey,
SubField([PKey,GKey,F2Key,Vol2],',',+3) as F2Key,
SubField([PKey,GKey,F2Key,Vol2],',',-1) as Vol2,
1 as Fact2
FROM

(ooxml, embedded labels, header is 1 lines, table is Data);

Geo:
LOAD SubField([GKey,GDesc],',',+1) as GKey,
SubField([GKey,GDesc],',',-1) as GDesc
FROM

(ooxml, embedded labels, header is 1 lines, table is Data);

Product:
LOAD SubField([PKey,PDesc],',',+1) as PKey,
SubField([PKey,PDesc],',',-1) as PDesc
FROM

(ooxml, embedded labels, header is 1 lines, table is Data);

if you have two rel tables concatenate them and create composite key for F1key and F2key columns with autonumber function,it will save lot of space

Regards

Neetha