Create SatrSchema from one Fact Table

[Anonymous]

Hi all, I am new to QlikView as a tool and I am working through a task I have been given. I have a Fact Table with transactional history and have been asked to create a Star Schema. How do I go about using the Fact Table to create the Reference Tables?

Regards

Martin

[migueldelval]

Hi Martin,

I can´t understand your question so well.

But if you have a fact table, you only need to add new tables using only 1 filed to connect it. If you use more than 1 field you are going to make sinthetic keys.

Regards

Miguel del Valle

[narendiran]

Hi Martin,

Not sure you want star schema if you have one perfect fact table because sometimes flat table structure might work better than star schema in QlikView.

Any ways please see below

After loading in the Fact table, you can create a lookup by loading resident from it, and using DISTINCT to get the distinct values:

Like the below example

LookupTable: Load distinct QlikID,QlikName resident Fact table;

[mohamed_ahid]

Hi

try to do this :

let suppose that your fact table have location data (city ,country ,Zip code ....) .to create a location table :

location table :

load

city ,country ,Zip code,

AutoNumberHash128(city ,country ,Zip code) as Location_ID

from  fact table ;

fact table :

load

AutoNumberHash128(city ,country ,Zip code) as Location_ID

// city ,country ,Zip code,  you do not have to load this data anymore

......

from ......

[jyothish8807]

Hi Martin,

If you have only fact table and nothing else, the you can splits the fields from your fact in to dimension tables.

For ex:

FACT

Product

Product Name

Shipment

Shipment Address

Sale

Then you can create two dimension tables "Product" and "shipment" and create a star schema.

Fact:

Load *

from Fact;

Noconcatenate

Product:

Load

Product,

Product Name

resident fact;

Noconcatenate

Shipment:

Load

Shipment,

Shipment Address

resident Fact;

Drop field Product Name,Shipment Address from fact;

Regards

KC

[danansell42]

Hi Martin

All the above answers are correct.

I was going to ask how large is your fact table?

If its not that big then you are probably just as well keeping a singular table as the data model.

Dan

[Anonymous]

Thanks Daniel,

Yes it is not big and my feeling was exactly the same, but the task wants a Star Schema created.

[Anonymous]

Thanks Miquel,

The task I was given wants me to create a Star Schema from a single Fact Table, although it is small enough to keep as one table.

Regards

[arethaking]

Can you provide the sample data with the tables?

[Anonymous]

Hi,

Here is the data. The task I must complete is to create a Star Schema from this.

Regards