topic Re: Data Modelling in QlikView

Data Modelling

agni_gold — Thu, 14 Nov 2013 08:25:53 GMT

Hi,

I have 10 dimension tables ,

and i already make fact table using these dimension tables ,

but these table are not cross referenced to each other through fact table . how can i do this ,

Please help .

Re: Data Modelling

datanibbler — Thu, 14 Nov 2013 08:31:09 GMT

Hi,

can you make your question a it more precise, please? There are a lot of people here able and perfectly willing to help, but no one possesses a crystal ball to my knowledge...

As a first shot:

- Are (and how are) the tables linked? What are the keys?

Can you maybe post your qvw?

Best regards,

DataNibbler

Re: Data Modelling

DavidFoster1 — Thu, 14 Nov 2013 08:38:10 GMT

This sounds like an almost perfect star schema (The rule of thumb proposed by Ralph Kimball is to keep to 7 dimensions or less) the dimension tables should only link to the fact table at the centre of the star.

Re: Data Modelling

Thu, 14 Nov 2013 09:03:55 GMT

Hi,

We need the schema of tables to help you we need also the number of fact tables if exist more than one .

Re: Data Modelling

jonathandienst — Thu, 14 Nov 2013 09:04:06 GMT

QV does the associations using the file names. When you load the fact table, you need to load the keys to the dimension tables using the same field name (in the fact table and in the dimension table). Like this:

DimCustomer:

LOAD

CustomerKey,

CustomerName,

....

Fact:

LOAD ....

CustomerCode As CustomerKey,

...

HTH

Jonathan

Re: Data Modelling

agni_gold — Thu, 14 Nov 2013 09:46:23 GMT

Suppose i have 3 Tables

T1 T2 T3

ID T2_ID T3_ID

Name ID T2_ID

Address T2_Name T3_Cost

thses are 3 tables T1,T2,and T3 and we can see these tables are linked each other

after this i have loaded thses tables in qlikview like this :

load ID as %ID

Name

Address

from T1;

load T2_ID as %T2_ID

T2_Name

from T2;

load T3_ID as %T3_ID

T3_Cost

from T3;

now i make fact table

Fact_Table:

%ID

%T2_ID

%T3_ID

#T3_Cost

now these are connected with dimension table but they are not cross referenced (Means association is not making ).

Re: Data Modelling

Thu, 14 Nov 2013 13:05:18 GMT

Just join the tables with KEY only

FACT:

LOAD DISTINCT ID AS %ID Resident T1;

join LOAD DISTINCT ID AS %ID, T2_ID AS %T2_ID Resident T2;

Join LOAD DISTINCT T2_ID AS %T2_ID , T3_ID AS %T3_ID, T3_Cost AS #T3_Cost Resident T3;

Re: Data Modelling

agni_gold — Thu, 14 Nov 2013 16:30:56 GMT

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

this msg appears when i reload script

///$tab T1

[T1]:

LOAD ID as %ID,

Name,

Address

FROM

C:\Users\agnivesh\Desktop\Question\Data\T1.xlsx

(ooxml, embedded labels, table is Sheet1);

///$tab T2

[T2]:

LOAD T2_ID as %T2_ID,

ID,

T2_Name

FROM

C:\Users\agnivesh\Desktop\Question\Data\T2.xlsx

(ooxml, embedded labels, table is Sheet1);

///$tab T3

[T3]:

LOAD T3_ID as %T3_ID,

T2_ID,

T3_Cost,

FROM

C:\Users\agnivesh\Desktop\Question\Data\T3.xlsx

(ooxml, embedded labels, table is Sheet1);

///$tab Fact

[Fact]:

LOAD DISTINCT %ID AS %ID

Resident T1;

join

LOAD DISTINCT ID AS %ID,

%T2_ID AS %T2_ID

Resident T2;

Join

LOAD DISTINCT T2_ID AS %T2_ID ,

%T3_ID AS %T3_ID,

T3_Cost AS #T3_Cost

//ID as %ID

Resident T3;

Re: Data Modelling

Clever_Anjos — Thu, 14 Nov 2013 16:33:14 GMT

Into Qlikview, if two tables share a field with same name, QV will create a key between the tables.

Just make sure your fact table has a field (CodProduct, for example) with same same from dimension table and QV will do the rest to you

Re: Data Modelling

Thu, 14 Nov 2013 16:36:04 GMT

i'm not sure to understand really your goal but another point of view could be to load your 3 tables using CONCATENATE in a unique master table.

It will keep the link thru the same fields as your 3 distinct tables. It is not join but you can check the effect.

Best regards

Chris

Re: Data Modelling

agni_gold — Thu, 14 Nov 2013 16:42:05 GMT

Please tell me when i reply this msg how can i attach files in this ?

Re: Data Modelling

agni_gold — Thu, 14 Nov 2013 16:45:36 GMT

Yes thats i am asking , If same fields are present then QV makes internally synthetic table which contains all common fields and generate a synthetic key ,

my question is that if number of common fields are more then , is these is any way to remove this synthetic table (generated by QV).

Re: Data Modelling

Clever_Anjos — Thu, 14 Nov 2013 16:47:27 GMT

You can concatenate your fields, generating a unique key

autonumberhash128(Dim1,Dim2,...Dim10) as my_key