topic Re: Create SatrSchema from one Fact Table in QlikView

Create SatrSchema from one Fact Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Create SatrSchema from one Fact Table

migueldelval — Thu, 21 Jul 2016 13:12:36 GMT

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

Re: Create SatrSchema from one Fact Table

narendiran — Thu, 21 Jul 2016 13:26:54 GMT

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;

Re: Create SatrSchema from one Fact Table

mohamed_ahid — Thu, 21 Jul 2016 13:33:27 GMT

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 ......

Re: Create SatrSchema from one Fact Table

jyothish8807 — Thu, 21 Jul 2016 13:37:37 GMT

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

Re: Create SatrSchema from one Fact Table

danansell42 — Thu, 21 Jul 2016 13:51:27 GMT

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

Re: Create SatrSchema from one Fact Table

Anonymous — Thu, 21 Jul 2016 13:57:49 GMT

Thanks Daniel,

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

Re: Create SatrSchema from one Fact Table

Anonymous — Thu, 21 Jul 2016 14:00:13 GMT

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

Re: Create SatrSchema from one Fact Table

arethaking — Thu, 21 Jul 2016 14:09:02 GMT

Can you provide the sample data with the tables?

Re: Create SatrSchema from one Fact Table

Anonymous — Thu, 21 Jul 2016 14:28:41 GMT

Hi,

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

Regards

Re: Create SatrSchema from one Fact Table

effinty2112 — Thu, 21 Jul 2016 14:37:09 GMT

Hi Martin,

Does your fact table have a lot of repetition of data related to dimensions? For example if your fact table has CustomerID and a repeated Customer Name then you could make a simple customer dimensional table.

Customers:

Load

Distinct

CustomerID,

[Customer Name]

Resident Facts;

Drop Field [Customer Name] from Facts;

This will remove the repeating customer names from the fact table and you will have a Customers table holding the ID and the Name. Repeat for other dimensions enriching the dimensional tables with more fields where desirable.

Essentially we're doing a little bit of gentle normalisation of your single big fat fact table but in QlikView we don't need to push this process far.

Good luck

Andrew

Re: Create SatrSchema from one Fact Table

Anonymous — Thu, 21 Jul 2016 17:06:00 GMT

Thank you,

I agree that a flat table would be better, but the task given to me wants to see a Star Schema.