Composite key performence issue

[sasikanth]

HI all,

I have 2 fact tables DAILY , SUMMARY (month wise)  andDimensions ,

now i need to create a link table by creating a composite key, i have 7 DIMs so 7 key fields are there,

now i have created a composite key with 7 keys fields ,

Now my question : is there any effect on performence if i create a composite key with 7 key fields?

if then please let me know what are they

Regards

Sasi

[#2]

Hi Sasi

7-column composite key is not an issue per se (obviously you're using autonumber to convert the key to numeric value, don't you?). You can also try to create two separate docs, one on low level (DAILY) and one aggregated (SUMMARY) and use document chaining to go to detailed doc from summary.

Lukasz

[sujeetsingh]

The main thing you need for this is that the data type for this key .

Go to this blog

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

[sujeetsingh]

Avoid using Complex composite keys(when to remove synthetic keys), and instead use the autonumber() function to generate a sequence which uses compact memory.

[sasikanth]

HI Lukasz

Thanks for the reply,

Can u please provide a link or documents which explain about document chaining .

[sasikanth]

HI Sijeet

Can i use like this,

autonumber(key1 &'_'& key2 &'_'& key3 &'_'& key4 &'_'& key5 &'_'& key6 &'_'& key7) as CP_KEY,

if so can please exlain the flow...

[#7]

To setup the document chaining you have to create a trigger with External => Open QlikView Document action and check "Transfer State" checkbox to transfer selections from aggregated document to detail document.

[sujeetsingh]

That can be but do you really need such big composite key

[sasikanth]

HI,

Yes, i have tried to reduce fields but i didn`t , so i need to use that one only.

Here one thing that i can`t understand is the FLOW of searching for the record through compsite key ,

when we made selection.

Thanks

Sasi

[er_mohit]

yes it effects on performance so to make as uniue no. in qlikview to compact memory use

autonumberhash256(Field1,Field2,Field3,Field4,Field5,Field6,Field7) as Key

See it

Autonumber needs one expression as input and autonumberhashXXX can handle more inputs. So you'd use autonumber(Field1&Field2&Field3) which needs to calculate the concatenation of Field1,-2 and -3 first. Autonumberhash can take more inputs: autonumberhash128(Field1, Field2, Field3). More importantly the autonumberhash are likely more efficient in determining if the combined inputs have already been encountered or are encountered for the first time. So they are probably faster in calculating the unique integer.