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]

I mean by that, that when you have fields on your dimenion table that are more detailed than your joining key field (between this dim and the fact), you will not be able to use them to analyze your Measures

[rwunderlich]

I'll second Christoph's question. I don't see any problem with the original model. In fact I think it's a good one to model the one-StatusDimKey  to many-StatusValue.  You can associate any one StatusValue to the Fact or list all the associated StatusValue with concat(StatusValue, ',')

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[YoussefBelloum]

EDITED

rwunderlich Thank you Rob for your time,

would you be able to show us your approach here with some code linking fact table to dim table ?

[rwunderlich]

Nothing special to do here. According to the tables posted by OP, the two tables are linked by StatusDimKey.

-Rob

[YoussefBelloum]

Thats why I was asking.. I thought maybe Im missing something important here.

So the only thing wrong I said here is when I said that " he can't use the dimensions fields that are more detailed than the key field to analyze his measures".. It is because I usually use 1:n relations on the dimensions table starting from the key.