How to design Star Schema Design for my Data Model

[#1]

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.

[suniljain]

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.

[#3]

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.

[suniljain]

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.

[#5]

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.

[suniljain]

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.

[#7]

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?

[suniljain]

Yes , You are right.

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

[#9]

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?

[pkelly]

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