topic Re: convert snowflake schema into star schema in QlikView

convert snowflake schema into star schema

Anonymous — Thu, 27 Aug 2015 11:54:04 GMT

I have the following schema

The fact table is Vente .

the code of loading dimensions

//----------------------Article-----------------------

Article:

LOAD "Code Article",

"Libellé Article ",

"Unité de Stock ";

SQL SELECT "Code Article",

"Libellé Article ",

"Unité de Stock "

FROM QlikDataWarehouseTest.dbo.Article;

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

Assortiment:

LOAD "Code Article",

"Code Fournisseur";

SQL SELECT "Code Article",

"Code Fournisseur"

FROM QlikDataWarehouseTest.dbo.Assortiment;

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

Fournisseur:

LOAD "Code Fournisseur",

"Libellé Fournisseur",

Ville;

SQL SELECT "Code Fournisseur",

"Libellé Fournisseur",

Ville

FROM QlikDataWarehouseTest.dbo.Fournisseurs;

//--------------------Reference---------------------

Reference:

LOAD "Code Reference",

"Code Article";

SQL SELECT "Code Reference",

"Code Article"

FROM QlikDataWarehouseTest.dbo.Reference;

//--------------------OC---------------------

OC:

LOAD [OC1],[Code Article], [INSERTION],[Site] ;

SQL SELECT distinct [OC] as OC1 ,[Code Article], [INSERTION] ,[Site]

FROM [QlikDataWarehouseTest].[dbo].[OC Geant]

WHERE [Code Article] IS NOT NULL

UNION ALL

SELECT distinct [OC] as OC1 ,[Code Article], [INSERTION] ,[Site]

FROM [QlikDataWarehouseTest].[dbo].[OC MPX]

WHERE [Code Article] IS NOT NULL ;

How can I change snowflake schema into star schema to improve performance ?

Re: convert snowflake schema into star schema

quwok — Thu, 27 Aug 2015 12:03:56 GMT

Merging Fournisseur to Assortiment should turn your snowflake into a star schema. But note that only because it is a star schema doesn't make it more performant than the snowflake.

Re: convert snowflake schema into star schema

khasimvali85 — Tue, 19 Apr 2016 11:12:11 GMT

Hi,

I am also facing issue when converting from Snowflake Schema into Star Schema.

can any one explain how to convert?

Regards,

Khasim.

Re: convert snowflake schema into star schema

Tue, 19 Apr 2016 11:38:13 GMT

The main difference between a star and snowflake schema is that a star has only one fact table with many dimensional tables around it. A snowflake schema has more than one fact table

So if you want to convert your snowflake to a star you need to combine your fact tables by concatenating or joining them

Re: convert snowflake schema into star schema

hic — Tue, 19 Apr 2016 12:00:40 GMT

But why do you want to convert to a star schema? In most cases the performance difference is non-existent or negligible.

And having a snowflake scheme is usually a better option when it comes to manageability.

See A Myth about the Number of Hops

HIC

Re: convert snowflake schema into star schema

awhitfield — Tue, 19 Apr 2016 12:02:23 GMT

I really wouldn't bother, you model is fine as it is!

Andy

Re: convert snowflake schema into star schema

awhitfield — Tue, 19 Apr 2016 12:10:20 GMT

Hi Chris,

not necessarily so:

In computing, a snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake shape. The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions.^{[citation needed]}. "Snowflaking" is a method of normalising the dimension tables in a star schema. When it is completely normalised along all the dimension tables, the resultant structure resembles a snowflake with the fact table in the middle. The principle behind snowflaking is normalisation of the dimension tables by removing low cardinality attributes and forming separate tables.^[1]

The snowflake schema is similar to the star schema. However, in the snowflake schema, dimensions are normalized into multiple related tables, whereas the star schema's dimensions are denormalized with each dimension represented by a single table. A complex snowflake shape emerges when the dimensions of a snowflake schema are elaborate, having multiple levels of relationships, and the child tables have multiple parent tables ("forks in the road").

Andy

Re: convert snowflake schema into star schema

Mahamed_Qlik — Tue, 19 Apr 2016 12:11:15 GMT

Hi Jaweher,

As Chris advised, The main difference between a star and snowflake schema is that a star has only one fact table with many dimensional tables around it. A snowflake schema has more than one fact table.

In your scenario, you will need to create single fact table with the dimension surround to it.

You can concatenate or you can use join the different table into the single on basis of Primary key between those table.

Regards,

Mahamed