2 Fact tables share Dimension table at different granularity

[#1]

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

[#2]

How is your hierarchy stored?

Is there relation between thise 2 fact levels?

[#3]

in a single denormalised table

[#4]

Did you considered to transform hierarchy with hierarchybelongsto()?

[hic · Accepted solution]

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

HIC

[#6]

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

[hic]

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

[#8]

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

[#9]

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

[hic]

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