Build Star Schema in QVD

[Anonymous]

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

[VishalWaghole]

Hi Sathish,

Please share some dummy data with same format.

so it gives more clarity.

-- Regards,

Vishal Waghole

[avinashelite]

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

[Anonymous]

Hi

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

[ashfaq_haseeb]

Hi,

Have a look at attached documents

Regards

ASHFAQ

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.