topic Background behind the Qlikview data model in QlikView

Background behind the Qlikview data model

Fri, 01 Apr 2011 18:35:18 GMT

Tables in the Qlikview data cloud are linked to other tables by a maximum of only 1 field. If more fields have the same name, a synthetic key/table is created. There are several solutions to this, but what I am looking for is some background on why Qlikview can only link on 1 field, as almost every datamodel links tables on more that one key (compound key).

Background behind the Qlikview data model

Sun, 03 Apr 2011 13:35:43 GMT

Hi,

I'm new to QlikView, so I don't know if I'm correct...but I think QlikView doesn't analyze data that we load in...it just creates tables based on our LOADS....were if two fields with same name are considered same and not differentiated....

Basically it cannot understand that in practice most tables are linked using a compound key....

Background behind the Qlikview data model

Tue, 10 May 2011 07:40:35 GMT

Hi Johan,

What I usually do is create compound keys in my script, sometime consisting out of a number of fields. I make sure that the keys exist in both the tables I would like to join.

Hope that this is what you're looking for.

Background behind the Qlikview data model

Tue, 10 May 2011 07:50:09 GMT

Hi Johan,

what Qlikview does is to link the fields in Different tables with samename as commonfield. Now if you wanted to create a compound Key for two fields you can write the syntax in such a way that the compound key is created. If you dont want to create compound Key just name the fields same in the two tables. It will result in a synthetic Key. But this procedure is not recommended, when lot of Synthetic Keys are present in the Datamodel as it results into 1:n connections and the data is not correct.

hope it helps.

Regards

sravan

Background behind the Qlikview data model

Tue, 10 May 2011 10:18:22 GMT

Hi,

Qlikview follows Natural Join and Synthetic Key is not same as compound key.

If you have more than one common columns in tables , qlikview will make synthetic key. If you are doing alias or qualify but you want link based on more than one common column then make compound key.

ie (say)

autonumber( A & '-' & B & '-' & C) as compoundkey.

in qlikview autonumber ( A & B & C) is not same as autonumber ( B & C & A)

but

in case of synthetic key order of columns has no effect.

Regards,

Shubhu

Background behind the Qlikview data model

Anonymous — Tue, 10 May 2011 11:37:42 GMT

Qlikview is different from other datamodels ...

We always create compound keys using autonumber to make sure the keys are correctly taken.

The naming in the tables must be unique per table:

e.g.

table1:

LOAD columnA, columnB, columnX, columnY ... FROM table1;

table2:

LOAD columnA, columnB, colulmnQ, columnR ... FROM table2;

becomes something like:

table1:

LOAD *

, autonumber(table1.columnA+table1.columnB) as compoundkey

;

LOAD columnA as table1.columnA, columnB as table1.columnB ... ;

table2:

LOAD *

, autonumber(table2.columnA+table2.columnB) as compoundkey

;

LOAD columnA as table2.columnA, columnB as table2.columnB ... ;

Always keep in mind that fields with the same name are automatically connected to eachother as key ... if more than one field is connected between 2 tables a syntetic key is created ... then it's better to create compound keys and rename the fields to a unique name.

regards,

Anita

Background behind the Qlikview data model

Tue, 10 May 2011 12:00:07 GMT

The generation of autonumber field is not guaranteed to give the same result when first building qvd files and load applications from these files. We have had a scenario in which the auto generated customer number was different between the orders and customers file. This was the result of building the qvd's in multiple scripts.

This is turned out to be a no-go when using this in combination with storing order data in qvd files per year. No way to be 100% sure that the generated number was similar all the time (perhaps this has changed in QV 10 - haven't tried this yet.)

Background behind the Qlikview data model

Anonymous — Tue, 10 May 2011 12:10:11 GMT

storing data with autonumbers will fail, that's true.

best thing is to save data as is and with loading create the autonumber ...

autonumbers only go corretly when using the within the same script.

Background behind the Qlikview data model

Tue, 10 May 2011 12:14:35 GMT

Have you experienced a significate performance inmprovement using the created autonumbers, compared with the standard compound/regular keys?

Background behind the Qlikview data model

Anonymous — Tue, 10 May 2011 12:47:26 GMT

With large amounts of data, they say keys based on numbers (created with autonumber) search a lot quicker than regular keys.

We don't have that big amount of data to really notice the difference ... but code-technical we have a neater way of connecting and we have a much better overview of how the data relates and in one overview we exactly know which are the keys (we use the '%' character to indicate the key).

e.g.

Customer:

LOAD *

,autonumber(Customernr) as %Customernr

;

LOAD Customernr, ... from CustomerTable;

Orders:

LOAD *

,autonumber(OrderCustomer) as %Customernr

,autonumber(Ordernr) as %Ordernr

;

LOAD Ordernr, OrderCustomer, ... from OrderTable;

etc ...

Re: Background behind the Qlikview data model

quiquehm — Tue, 26 Jul 2011 10:06:25 GMT

Hi Anita

I am having some problems trying to load multiple tables that share several common fields ( 3 to 4 fields are common across most of the tables, and the number of field values for the common fields are also different, sometimes being the field values of one field a subset of the values for other field ). As a result I get multiple synthetic keys ( and I guess the script load takes more time than needed due to poor performance ). In fact I am a bit lost about deciding if I should use left joins, outer joins ..etc to reduce the number of tables and get rid of the sync keys.

Reading another thread on these issues I learnt that possible solutions could be : Use of Concatenation, use of Composite keys ( as you describe here ) or use of a Key Fields Table.

for composite keys, do you know when to use Hash128, Autonumber, Autonumber128 or Autonumber256 ? Any substantial differences ?

About the other methods to avoid synthetic keys, like building a key fields table, any experience or ideas you may suggest ?

Here is the structure of the 3 tables I am using ( it looks simple, but it´s giving me a hard time ).

Thanks for any help !

Best Regards

t_MST://table with billing dates

//dimension fields

DataSet, // this field has the format YYYYMMDD

P_Prj_Nr,

Prj_WBS,

Ntw_Nr,

Activ_Nr,

//measures fields

Plan_Bill_date,

Actual_Bill_date;

t_Activ://table with schedule dates (for Gantt Chart)

//dimensions fields

DataSet,

P_Prj_Nr,

Prj_WBS,

Ntw_Nr,

Activ_Nr,

Activ_Desc,

//measures fields

Late_Start,

Late_Finish,

Actual_Start,

Actual_Finish;

t_Financials://table with costs and revenues per projects WBS

//dimensions fields

DataSet,

Prj_Name,

Prj_WBS,

Activ_Nr,

Activ_Desc,

Prj_Mgr_Name,

//measures fields

Plan_Revenue,

Plan_Cost,

Plan_Margin,

Actual_Cost;