topic How to design Stat Schema Design for my Data Model in QlikView

How to design Star Schema Design for my Data Model

Wed, 01 Sep 2010 07:41:02 GMT

Hi All,

Many of them recommended me to do Star Schema Design for my data model. Due to lack of experience in that and due to the insufficient experience in data model of the current project I am not able to initiate Star Schema design.

Can any one please help me out to initiate. I have attached the document which I am working. Hope that is good enough to work with.

How to design Stat Schema Design for my Data Model

suniljain — Wed, 01 Sep 2010 08:31:18 GMT

Rikab,

Good Morning!!!

For Star schema,

One Fact table have multiple Dimentional table.

E.g

Fact Table is sales Master

Dimentional table is Customer, Sales office, Region,Material,Billing Type,Etc

But In Your Data modelling . I donot seen any Fact Table.

You can Implement Star schema only if You have Fact table.

Regards

Sunil Jain.

How to design Stat Schema Design for my Data Model

Wed, 01 Sep 2010 12:29:36 GMT

Hi Sunil,

Thanks for response! In my data model I suppose there are 4 fact tables and below mentioned tables are the fact tables as per my understanding!

fsemployee, pooldetail, callindex and fsstockistmap

Can you give some sample document which contains the star schema data model for my understanding. I am not able to understand the same with your explanation.

How to design Stat Schema Design for my Data Model

suniljain — Wed, 01 Sep 2010 15:37:11 GMT

Pls Check Screen Shot of Star Schema.

In Your Case

fsemployee, pooldetail, callindex and fsstockistmap are fact table.

Is it possible to have link between this 4 table.

How to design Stat Schema Design for my Data Model

Thu, 02 Sep 2010 06:10:37 GMT

Hi Sunil,

Which screen you are talking about? Did you attached any or do you want to search the same in google? If that is the case I have already seen that and got little idea about star schema. But don't know what has to be done with my data model for implementing the same.

Is it possible to have link between this 4 table.

I didn't understand the above sentence. Could you please tell me what exactly you wants me to do? Do you mean to say that you want key field which link all these 4 four fact table? Am I right?

Sorry If my doubt is too silly. I am very new to qlikview so I don't have enough experience.

How to design Stat Schema Design for my Data Model

suniljain — Fri, 03 Sep 2010 07:53:20 GMT

Meaning of that statement is that If you can make One dimentional table of that 4 table then only you can built star schema in your application.

How to design Stat Schema Design for my Data Model

Fri, 03 Sep 2010 08:00:41 GMT

Sorry! Correct me if I am wrong!

Do you mean to say that I need to join all these 4 tables in to one? Am I right?

How to design Stat Schema Design for my Data Model

suniljain — Fri, 03 Sep 2010 08:03:19 GMT

Yes , You are right.

Because to build star schemat it is require to have fact table as single table.

How to design Stat Schema Design for my Data Model

Fri, 03 Sep 2010 08:13:33 GMT

Huh! I don't know how I am going to do that. Because in all these fact tables it has from and to date range which is create problem if I make it to one.

If time permits can you please do it! It just something to start with. Can anything can be done for the above said from and to date problem?

How to design Star Schema Design for my Data Model

pkelly — Fri, 03 Sep 2010 14:02:52 GMT

Hi Rikab

Is this a live report or are you just trying to graphically show your star schema design?

The reason I ask is that it looks as if you are pulling information direct from your database as opposed to a QVD table.

I have various fact tables with different time frames and dimensional tables to call upon...

For example...

Fact Tables

SalesOrderIntake.qvd (pulls in last 4 weeks order intake figures)

SalesInvoice.qvd (pulls in last 2 years sales invoice figures)

A common dimensional table which these two tables can link to is the Customer.qvd table which is linked via the %CustKey.

If creating an order intake report, i would utilise the SalesOrderIntake.qvd and Customer.qvd table whilst sales invoice would have SalesInvoice.qvd and Customer.qvd respectively so there would not be a problem.

This is a simplistic example and there are times where I do have both the SalesOrderIntake.qvd and SalesInvoice.qvd but this can be dealt with from within the report rather than your data model.

For example..

For the Sales Invoice section of the report I would leave SalesInvoice.qvd and Customer.qvd alone.

For the Sales Order intake section I would..

SalesOrderIntake.qvd - rename %CustKey (my customer file link) to %CustKeyIntake

Bring the Customer.qvd in a second time with a new name - rename %CustKey to %CustKeyIntake (creating a link to SalesOrderIntake) and change the names of the additional fields so that a join is not created with the original customer table.

The attached file shows the end result.

The process I went through with my data model was to look at each area individually and build from there.

For example I built my SalesOrderIntake.qvd then started adding keys to it as I built the dimesional tables which I thought I would require.

Suppose what I am saying is build your model gradually...

Apologies if I am way off the mark on this....

Regards

Paul

How to design Star Schema Design for my Data Model

suniljain — Mon, 06 Sep 2010 12:18:20 GMT

In your case I am suggesting that you have to use snake flow instead of star schema.

because star schema not suit with whatever data available in your application.

How to design Star Schema Design for my Data Model

Mon, 06 Sep 2010 13:08:32 GMT


Sunil Jain wrote:
In your case I am suggesting that you have to use snake flow instead of star schema.
because star schema not suit with whatever data available in your application. <div></div>

But as per the information given about Snake Flow it also has only one fact table. But whereas in my case there are 4 fact tables. Let me know what can be done for this or else if time permits please work on it and design my data model as snow flake. Just because I never designed any data model till now.

Please do reply...

How to design Star Schema Design for my Data Model

suniljain — Mon, 06 Sep 2010 13:20:52 GMT

Rikab,

pls send me sample data of your apllication with limited record and send me on my person email Id that you have.

I will send you data modeled application.

How to design Star Schema Design for my Data Model

Tue, 07 Sep 2010 13:25:50 GMT

As requested have mailed you documents for you to work with...

How to design Star Schema Design for my Data Model

suniljain — Tue, 07 Sep 2010 13:43:41 GMT

I will send you modelled application up to sunday evening .

How to design Star Schema Design for my Data Model

Wed, 08 Sep 2010 07:28:34 GMT

Hi Sunil,

Thanks for your help. Requesting you to update me progress of work i,e which document you have chosen from the 2 document which I have attached and which model you are planning to design(Star or Snow). Just reply for my mail it will be easy for me to understand what exactly going on!

How to design Star Schema Design for my Data Model

Wed, 08 Sep 2010 08:02:01 GMT

Hi Sunil,

Before you start working on in my document requested you to look in to the below post as well just to get some knowledge on what exactly happening in my case.

http://community.qlik.com/forums/t/33448.aspx?PageIndex=2

How to design Star Schema Design for my Data Model

sunil2288 — Wed, 08 Sep 2010 08:52:54 GMT

HI Rikab

I studied your datastructure.

You don't worry to make Datamodel as it may be star schema or snow flake both of these are too easy to understand

in Star schema you can have only one fact table. and all the dimension table are connected to the fact table...

but in snow flake there are multiple fact tables and the dimension table are connected to multiple fact tables..

Let me know how far you are known to make fact tables..

Regards,

Sunil

How to design Star Schema Design for my Data Model

Wed, 08 Sep 2010 09:27:11 GMT

Hi Sunil Panda,

Many thanks for your explanation. I don't even know what is fact table and how to make that. Could you please design the same for me using the attached document. I also don't know how to design like star schema and snow flake. Hope you could help me out!

How to design Star Schema Design for my Data Model

sunil2288 — Wed, 08 Sep 2010 09:55:40 GMT

Hi Rikab,

There are always two type of data in a database.

1>The data which is constant like.Dimension Tables(Customer_Table,Product_Table...Etc)

2>The varied tables like all type of transactions (Daily_sales_Reports,Daily_Transaction_Maintainance)

Sometimes what happened client has its (Transaction data) in different tables...

At that time we need to make a Fact table by joining or concatenating or using any other method to the Transaction tables..

Hence like this we can make a particular structure to design a DataModel..

Now you need to decide how to categorize...

Hope it will help you to make an idea of Fact Tables..

Regards

Sunil...