topic Re: Composite key performence issue in QlikView

Composite key performence issue

sasikanth — Wed, 26 Feb 2014 08:11:27 GMT

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

Re: Composite key performence issue

Wed, 26 Feb 2014 08:20:01 GMT

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

Re: Composite key performence issue

sujeetsingh — Wed, 26 Feb 2014 08:22:24 GMT

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

Re: Composite key performence issue

sujeetsingh — Wed, 26 Feb 2014 08:23:36 GMT

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

Re: Composite key performence issue

sasikanth — Wed, 26 Feb 2014 08:32:29 GMT

HI Lukasz

Thanks for the reply,

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

Re: Composite key performence issue

sasikanth — Wed, 26 Feb 2014 08:35:12 GMT

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...

Re: Composite key performence issue

Wed, 26 Feb 2014 08:44:04 GMT

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.

Re: Composite key performence issue

sujeetsingh — Wed, 26 Feb 2014 10:43:37 GMT

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

Re: Composite key performence issue

sasikanth — Wed, 26 Feb 2014 10:53:30 GMT

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

Re: Composite key performence issue

er_mohit — Wed, 26 Feb 2014 10:57:40 GMT

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.

Re: Composite key performence issue

sasikanth — Wed, 26 Feb 2014 12:02:23 GMT

HI mohit,

is there any way to reduce number of keys in composite key,

in my scenario i have 7 common keys between Dim and both FACT tables,

and

if i use autonumberXXX() function At what extent it reduce the effect on the performence ?

Please provide sample example if u have.

Thanks

sasi

Re: Composite key performence issue

Wed, 26 Feb 2014 12:37:27 GMT

LINK tables are known to have poor performance when there are many rows. If you have 7 dimensions, I will expect that you have many rows.

You may think another way to create your model: by concatenating your tables for example. If possible, of course.

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

Fabrice

Re: Composite key performence issue

sasikanth — Thu, 27 Feb 2014 03:51:59 GMT

HI ANUEZ,

Concatenating is not possible in my scenario ,

Other than Link table and Concatenating ,is there any way to solve the problem ?

so that i can make my Data model perfect.

Thanks for the reply

Regards

Sasi

Re: Composite key performence issue

Thu, 27 Feb 2014 08:15:07 GMT

I don't know.

Did you read the post by HIC about mixed granularity ? That is your scenario : some daily data, some monthly data. You have therefore a field named %Time to be generic that will contain months and days. Each %Time will point to day and month (3 fields in the link table) to perform the link and let the user do the choice. For the days that is evident, for the months the day part is null.

You concatenate the tables. You create some LINK dimension tables (one for each dimension where there is different granularity). HIC's solution works perfectly.

If your current LINK table is small, you will get no problem. It it contains several millions lines, I am not so sure.

Fabrice