Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cpalbrecht
Contributor

Qlik Sense unique dimension key

Hi All,

in Qlik View there are "Perfect Keys". Is there a same concept in Qlik Sense? Should I also use "perfect keys" e.g. as dimension keys? From performance point of view, is there any difference if you have unique 1:1 or 1:n dimension keys?

I ask because there is the following problem:

FactTable:

Event TimestampStatusDimKey
2016-01-01 01:00:00ax
2016-01-01 01:00:01ay
2016-01-01 01:00:02bx
2016-01-01 01:00:03ax
2016-01-01 01:00:04by
2016-01-01 01:00:05cz


StatusDimTable:


StatusDimKeyStatusIdStatusValue
axaax1
axaax2
axaax3
ayaay1
bxbbx1
bxbbx2
bybby1
bybby2
czccz1
czccz2
czccz3

The problem here is, each StatusKey can have multiple StatusValues for a specific StatusId. Is it a problem to have not unique dimension keys (StatusDimKey)?

Or should I do the following with the fact table?

NewFactTable1:


Event TimestampStatusDimKey
2016-01-01 01:00:00ax1
2016-01-01 01:00:00ax2
2016-01-01 01:00:00ax3
2016-01-01 01:00:01ay1
2016-01-01 01:00:02bx1
2016-01-01 01:00:02bx2
2016-01-01 01:00:03ax1
2016-01-01 01:00:03ax2
2016-01-01 01:00:03ax3
2016-01-01 01:00:04by1
2016-01-01 01:00:04by2
2016-01-01 01:00:05cz1
2016-01-01 01:00:05cz2
2016-01-01 01:00:05cz3

That causes a lot more rows in the fact table.

14 Replies
YoussefBelloum
Esteemed Contributor

Re: Qlik Sense unique dimension key

Hi,

I don't think it is a problem linking your fact to your dim with that key.. but you will not be able to make analysis using STATUSID and STATUSVALUE.

basically when we join a fact to a dim table, we join using the key field that is the most detailed on the dimension, to be able to make aggregated analysis using the different dimension fields.

maybe a look from one of the experts here stalwar1 will make things clearer

Re: Qlik Sense unique dimension key

Usually the dimension table have unique rows and fact tables have multiple rows for each of the dimension value. OP's data look unique in this way, but in general I would not join them because joining them will repeat the fact table values multiple times which will cause calculation headaches on the front end of the application. I agree with you youssefbelloum‌.

YoussefBelloum
Esteemed Contributor

Re: Qlik Sense unique dimension key

Thanks

cpalbrecht
Contributor

Re: Qlik Sense unique dimension key

Thanks for your answers.

So I have to decide beetween two options:

Option 1: Joining the keys to get unique keys with the problem to repeat the fact table values multiple times --> that can cause calculation problems.

Option 2: Don't join the keys with some analysis limitations.

Is there any solution to have good performance with full analysis possibilities?

YoussefBelloum
Esteemed Contributor

Re: Qlik Sense unique dimension key

It depends on your real data, because here we are talking about 3 fields..

for this exact data, I will not even load the dimension table, I will just create a mapping load to bring the STATUSID field to the fact table.

cpalbrecht
Contributor

Re: Qlik Sense unique dimension key

It is the same problem with Generic keys which hic mentioned.

To get generic keys you have to "duplicate" the fact table values multiple times. And then you have the full possiblities to analyse the data.

YoussefBelloum
Esteemed Contributor

Re: Qlik Sense unique dimension key

Maybe they have time to take a look hic‌ or rwunderlich

cpalbrecht
Contributor

Re: Qlik Sense unique dimension key

The real FactTable has much more (up to 50 columns).

And the structure is like that:

TimestampDimKey1DimKey2DimKey3...Fact1Fact2Fact3...
2016-01-01 01:00:01dimKey1_1--...fact1_1fact2_1-...
2016-01-01 01:00:02dimKey1_1--...fact1_1fact2_2-...
2016-01-01 01:00:03dimKey1_2--...fact1_2fact2_1-...
2016-01-01 01:00:04-dimKey2_1-...fact1_3--...
2016-01-01 01:00:05-dimKey2_2-...fact1_4--...
2016-01-01 01:00:06--dimKey3_1...-fact2_4fact3_1...
2016-01-01 01:00:07--dimKey3_2...-fact2_5fact3_2...
2016-01-01 01:00:08--dimKey3_3...-fact2_6fact3_3...
...........................
cpalbrecht
Contributor

Re: Qlik Sense unique dimension key

Which limitations do I have here? You mentioned I am not able to make analysis using STATUSID and STATUSVALUE. Can you give a short example what do you mean by that?

Community Browser