topic Re: 2 Fact tables share Dimension table at different granularity in QlikView

2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 09:55:22 GMT

Hi All

I have 2 fact tables containing data at different granularity (no option to concatenate). They both use the same hierarchy dimension table but link to it at different points in the hierarchy, one links to the primary key, the other links in the middle of the hierarchy. Do I have any choice but to create 2 dimension tables at different levels?

Thanks

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 10:00:21 GMT

How is your hierarchy stored?

Is there relation between thise 2 fact levels?

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 10:08:56 GMT

in a single denormalised table

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 10:12:32 GMT

Did you considered to transform hierarchy with hierarchybelongsto()?

Re: 2 Fact tables share Dimension table at different granularity

hic — Thu, 12 Jun 2014 10:12:39 GMT

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

HIC

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 11:32:38 GMT

Hi Henric,

So if I have a master link table with a key to a dimension, e.g.

%Dimension %MasterLink

<ANY>|Departmentx aaaaa11111

12345|Departmentx bbbbb2222

And a dimension table with the same key and groupings, e.g.:

%Dimension GroupA GroupB GroupC

<ANY>|Departmentx N/A aGroup bGroup

12345|Departmentx myGroup aGroup bGroup

This would avoid many to many and allow me to have a single dimension table?

Thanks

Dominic

Re: 2 Fact tables share Dimension table at different granularity

hic — Thu, 12 Jun 2014 12:56:41 GMT

First of all: I think you should concatenate. This is possible even though you have different granularity. See Fact Table with Mixed Granularity and Generic keys. So: avoid the link table.

Further, both the solution with Generic Keys and the solution that Dariusz Mielczarek is hinting at above (using HierarchyBelongsTo) involve a bridge table; a table between the facts and the dimension. So this bridge table I don't think you can avoid.

Both solutions will work, and which one you should choose depends on how your hierarchy looks. If it is an unbalanced n-level hierarchy, you should look at HierarchyBelongsTo. If it is a fixed-level hierarchy, it is probably better with Generic Keys. See more on Hierarchies.

HIC

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 13:23:45 GMT

With concatenation the fact tables have zero in common other than a link to the dimension table and even that link is at different levels like:

FactTable 1

Department Fielda Fieldb Fieldc (+more)

A x x x

FactTable2

DepartmentGroup Fieldf Fieldg Fieldh (+more)

AA y y y

FactTable3

DepartmentGroup Fieldk Fieldl Fieldm (+more)

BB z z z

Concatenated

%KEY Fielda Fieldb Fieldc Fieldf Fieldg Fieldh Fieldk Fieldl Fieldm

AA|A x x x

AA| y y y
BB| z z z

So I would end up with one enormous table containing very different data, no metric fields in common. Is this still recommended?

Thanks

Dominic

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 13:56:23 GMT

So concatenated table however enormous I can do, in this concatenated table create a key like:

%Master_Key

<ANY>|Deptx

<ANY>|Depty

CostCentre1|Deptx

CostCentre2|Deptx

CostCentre3|Depty

CostCentre4|Depty

A Bridge table that expands the <ANY> 's to cover all possible Cost Centres like

%Master_Key Hierarchy_ID

<ANY>|Deptx CostCentre1

<ANY>|Deptx CostCentre2

<ANY>|Deptx CostCentre3

<ANY>|Deptx CostCentre4

CostCentre1|Deptx CostCentre1

CostCentre2|Deptx CostCentre2

CostCentre3|Depty CostCentre3

CostCentre4|Depty CostCentre4

Then link this Hierarchy_ID to the Dimension table. Phew I think I might finally have grasped it...?

Thanks

Re: 2 Fact tables share Dimension table at different granularity

hic — Thu, 12 Jun 2014 14:32:38 GMT

Now I get confused... Are you saying that there are no keys between the two/three fact tables, except the hierarchy? If so, you don't need to complicate it. Just load the fact tables as separate tables and use the hierarchy as bridge/link table between them.

Any additional dimensional data can just be linked to the hierarchy table.

HIC

Re: 2 Fact tables share Dimension table at different granularity

Thu, 12 Jun 2014 15:03:23 GMT

Apologies for my bad explanatory skills. There is one other field in common with a similar issue of differing dimensionality. So I would need the link table and key tables as per your Link table and multiple fact tables example, unless I concatenate. Thanks