Modeling master calendar tables?

[#1]

Hey to everyone,

I have few questions for you.

My today's topic was master calendar table. I've read some articles on this topic.

Because of different analysis that users do should every date from a fact table be extracted to a standalone "master" calendar table.
Regarding user requirements then we there (in a calendar dimension) extract monthname / number, quarter, day, week, .. from this date field.

1. If i have right understanding, than if we have 2 or 3 different dates (shipDate, saleDate, orderDate for example) for each fact (sale) in a fact table, then each date should be extracted into its own calendar table? Please correct me if i'm wrong.

2. In a star schema or transactional DB model should keys be integers. In this examples were keys of type DATE. Is that supposed to be a good practice?    

3. Why would anyone wants to have a date in a fact table? I always thought that date/time goes into separate table, dimension?

Please write down your opinion.

Regards,

Jurij Nemanic 

[ashfaq_haseeb]

Hi

Go through this post.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards

ASHFAQ

[hic]

Yes you should have one master calendar per date in the fact table. See http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles.

But you also want one "common" date for all dates, that you can use as x-axis in charts: See http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date.

Concerning integer keys: It does not matter what type the key is in QlikView. And since the date is the most atomic entity in a calendar dimension, you should use the date as key. Further - dates are integers, but with formatting information so that they are displayed like they should. See http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual.

HIC

[its_anandrjs · Accepted solution]

1.I think you have to go for cononical date because you have different date fields to make it common make single name of that fields.

2. Use single master calendar for all dates with common date field name which connect with your date calendar date fields.

3. Date fields entity is very important for any QV application with common date fields.

[richard_pearce6]

Here is an example of a master calendar

http://community.qlik.com/docs/DOC-6593

regarding to use a calendar or not I believe it depends on your requirements / data size etc. Some small applications will not require them and you can keep simple dimension (Date, Month, Year, Etc) within the FACT table.

If your model has multiple dates then separating the calendar is a good idea rather than multiples but again depends on your performance and complexity.

http://community.qlik.com/docs/DOC-6502

In my opinion, there's no single correct way to have or use a calendar.

Richard

QlikCentral.com

[#6]

Thank you very much!!

[#7]

OK i'll check that canonical form of date.
Thanks!

[#8]

Everything depends on requirements

I think it is still a good practice to have a separate calendar dimension.

Of course this calendar table has to be filled with all the dates formats in some range, depending requirements (granularity).

The reason is that lately user will maybe want some kind of analysis, but we have in our fact table only dates when some measures occur. (Some day we didn't sell anything -> so measure; record in a fact table doesn't exist -> dimension value doesn't exist... and user may be willing to see that days too in a graph when no sale occur, when he drills down to sale by days per month.)

Maybe this is not the best example, but i think it's understandable.

I think i read some article, where the author wrote down that it's advicable to always have a master calendar table...

Any further opinion is welcome