topic Re: Star model in QlikView

Star model

Anonymous — Wed, 28 Oct 2015 15:28:44 GMT

I have the following model

//--------------------Assortiment---------------------

Assortiment:

LOAD "Code Article",

"Code Fournisseur";

SQL SELECT "Code Article",

"Code Fournisseur"

FROM QlikDataWarehouse.dbo.Assortiment;

//--------------------Fournisseur---------------------

Fournisseur:

LOAD "Code Fournisseur",

"Libellé Fournisseur",

Ville;

SQL SELECT "Code Fournisseur",

"Libellé Fournisseur",

Ville

FROM QlikDataWarehouse.dbo.Fournisseurs;

How could I modify my script in order to get star model ?

Re: Star model

Anonymous — Wed, 28 Oct 2015 15:56:33 GMT

You can join the tables that are related to form one or you could rename the names of your fields and create new keys

Re: Star model

marcus_malinow — Wed, 28 Oct 2015 16:11:59 GMT

As Jannet said, a simple solution would be to join Assortiment and Fournisser like so:

//--------------------Assortiment---------------------

Assortiment:

LOAD "Code Article",

"Code Fournisseur";

SQL SELECT "Code Article",

"Code Fournisseur"

FROM QlikDataWarehouse.dbo.Assortiment;

//--------------------Fournisseur---------------------

//Fournisseur:

LEFT JOIN (Assortiment)

LOAD "Code Fournisseur",

"Libellé Fournisseur",

Ville;

SQL SELECT "Code Fournisseur",

"Libellé Fournisseur",

Ville

FROM QlikDataWarehouse.dbo.Fournisseurs;

Re: Star model

rubenmarin — Wed, 28 Oct 2015 16:14:37 GMT

If Fournisseur has unique values you can do a (left/inner/outer) join with Assortiment:

//--------------------Assortiment---------------------

Assortiment:

LOAD "Code Article",

"Code Fournisseur";

SQL SELECT "Code Article",

"Code Fournisseur"

FROM QlikDataWarehouse.dbo.Assortiment;

Left Join(Assortiment)

LOAD "Code Fournisseur",

"Libellé Fournisseur",

Ville;

SQL SELECT "Code Fournisseur",

"Libellé Fournisseur",

Ville

FROM QlikDataWarehouse.dbo.Fournisseurs;

Re: Star model

Wed, 28 Oct 2015 16:15:25 GMT

Jawehar89,

Try to do a left join in script which will drop your physical table structure & keep your data based on join result.

like,

Assortiment:

Load * from Assortiment;

left join (Assortiment)

Load * from Fournisseur;

which should fix your issue.

Thanks

Brad.

Re: Star model

marcus_sommer — Wed, 28 Oct 2015 16:22:31 GMT

There is no special need to create a datamodel in a star-scheme if you haven't any performance issues. If it's worked it's fine.

- Marcus

Re: Star model

prabhu0505 — Wed, 28 Oct 2015 16:33:52 GMT

I completely agree with Marcus.

Result 1: Note down the size and response time / performance of application in its current form (snow flake model).

Result 2: Convert Snow flake to Star with the guidance's from above post, measure again the size and response time of application.

Compare Result 1 & 2 to come up with best model for your data.

Re: Star model

Anonymous — Wed, 28 Oct 2015 16:41:26 GMT

you try somthing like that

//--------------------Assortiment---------------------

Assortiment:

LOAD

"Code Article" AS % KeyArticle_Assortiment ,

"Code Fournisseur";

SQL SELECT "Code Article",

"Code Fournisseur"

FROM QlikDataWarehouse.dbo.Assortiment;

Left Join(Assortiment)

LOAD "Code Fournisseur",

"Libellé Fournisseur",

Ville;

SQL SELECT "Code Fournisseur",

"Libellé Fournisseur",

Ville

FROM QlikDataWarehouse.dbo.Fournisseurs;

oc:

LOAD

[Code Article] AS % KeyArticle_Oc,

[OC1],

[INSERTION],

[sITE]

FROM..

Article:

load

[Code Article] AS % KeyArticle_Oc,

[Code Article] AS % KeyArticle_Assortiment,

[Libelle Article],

[Unité de Stock]

From

....

Left join(Article:)

REFERENCE:

[Code Article],

[code Refer]

From