topic Re: Build Star Schema in QVD in QlikView

Build Star Schema in QVD

Anonymous — Wed, 16 Jul 2014 19:29:05 GMT

Hi,

could you please provide sample script to build Star schema in QVD ?

I have one fact with 5 dimensions and the fields name exactly matches the fact and dimension keys.

Thanks in Advance

Re: Build Star Schema in QVD

VishalWaghole — Thu, 17 Jul 2014 05:17:58 GMT

Hi Sathish,

Please share some dummy data with same format.

so it gives more clarity.

-- Regards,

Vishal Waghole

Re: Build Star Schema in QVD

avinashelite — Thu, 17 Jul 2014 05:24:39 GMT

Hi Sathish,

You want to create a star schema from qvd's as the source rite. If so maintain a one on one relation between the table i.e if their is more then one field is common keep only one as common key for linking and rename the rest of the fields. If you want to combine more than one filed's then use link tables.

Regards,

@vi

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 05:38:13 GMT

I would suggest to have a look on attached data modeling best practices.

Re: Build Star Schema in QVD

ashfaq_haseeb — Thu, 17 Jul 2014 07:44:36 GMT

Hi,

Have a look at attached documents

Regards

ASHFAQ

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 13:47:27 GMT

Thanks for all your responses i deeply appreciate your reply,

May be i should given more information on this (sorry this is my first experience writing in a community disc forum )

Here is the sample data

pls note - I DO NOT want to create 5 separate tables here, only one table with "Star" structure

Fact :

SALE_ID,

PDCR_ID,

POL_ID

PGM_ID

ST_ID

AMT 1

AMT2

DIM 1 :

SALE_ID,

EXEC_NM(SE)

RGN_NM(Region),

DIM 2:

PDCR_ID

PDCR_NM

AGCY_NM

DIM3:

POL_ID

POL_NBR

EFF_DT

DIM4:

PGM_ID

PGM_NM

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 13:56:46 GMT

A Star Schema is a central Fact table surrounded by Dimension tables such that it sort of looks like a star.

What you are requesting is a single flattened table which could do very well for you. But a single table will never be a star.

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 13:58:43 GMT

Think of a QVD as having a one-to-one correspondence to a table. So, you don't save an entire schema...you save individual tables.

For what you're doing, think of loading your data into a QVW, and then doing a binary load off of that QVW for everything else that needs that shared schema.

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 14:03:24 GMT

Thanks Bill,

I can create 5 different tables and create the relationships in the app (qvw file) but i would like to have the join condition in QVD builder itself.

How abt this script

SALE :

SQL select

SALE_ID,

PDCR_ID,

POL_ID

PGM_ID

ST_ID

AMT 1

AMT2

LEFT JOIN

SQL select

SALE_ID,

EXEC_NM(SE)

RGN_NM(Region

LEFT JOIN

PDCR_ID

PDCR_NM

AGCY_NM

LEFT JOIN

POL_ID

POL_NBR

EFF_DT

Store SALE into .....

Do you think it will work as expected with ONE table ?

Thanks in Advance

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 14:10:09 GMT

Yup, that kind of thing should be fine and will create a single flattened table , which I think is perfect for what you have.

However you may wish to investigate mapping loads and the applymap() function.

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 14:45:42 GMT

Mapping Loads, Ok its new to me

can you please give some example using this scenario bill ?

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 14:52:48 GMT

Have a look at this blog re ApplyMap

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Re: Build Star Schema in QVD

Anonymous — Thu, 17 Jul 2014 15:35:47 GMT

Thanks everyone

Have a wonderful day