Qlik Sense unique dimension key

[cpalbrecht]

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 Timestamp	StatusDimKey
2016-01-01 01:00:00	ax
2016-01-01 01:00:01	ay
2016-01-01 01:00:02	bx
2016-01-01 01:00:03	ax
2016-01-01 01:00:04	by
2016-01-01 01:00:05	cz

StatusDimTable:

StatusDimKey	StatusId	StatusValue
ax	a	ax1
ax	a	ax2
ax	a	ax3
ay	a	ay1
bx	b	bx1
bx	b	bx2
by	b	by1
by	b	by2
cz	c	cz1
cz	c	cz2
cz	c	cz3

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 Timestamp	StatusDimKey
2016-01-01 01:00:00	ax1
2016-01-01 01:00:00	ax2
2016-01-01 01:00:00	ax3
2016-01-01 01:00:01	ay1
2016-01-01 01:00:02	bx1
2016-01-01 01:00:02	bx2
2016-01-01 01:00:03	ax1
2016-01-01 01:00:03	ax2
2016-01-01 01:00:03	ax3
2016-01-01 01:00:04	by1
2016-01-01 01:00:04	by2
2016-01-01 01:00:05	cz1
2016-01-01 01:00:05	cz2
2016-01-01 01:00:05	cz3

That causes a lot more rows in the fact table.

[YoussefBelloum]

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

[sunny_talwar]

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]

Thanks

[cpalbrecht]

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]

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]

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]

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

[cpalbrecht]

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

And the structure is like that:

Timestamp	DimKey1	DimKey2	DimKey3	...	Fact1	Fact2	Fact3	...
2016-01-01 01:00:01	dimKey1_1	-	-	...	fact1_1	fact2_1	-	...
2016-01-01 01:00:02	dimKey1_1	-	-	...	fact1_1	fact2_2	-	...
2016-01-01 01:00:03	dimKey1_2	-	-	...	fact1_2	fact2_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_4	fact3_1	...
2016-01-01 01:00:07	-	-	dimKey3_2	...	-	fact2_5	fact3_2	...
2016-01-01 01:00:08	-	-	dimKey3_3	...	-	fact2_6	fact3_3	...
...	...	...	...	...	...	...	...	...

[cpalbrecht]

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?