Calendar Table - Date/Time Analysis

[RobertCotton]

I'm fairly new to powerpivot but learning fast. (Thanks to this board and PowerPivotPro)

I am preparing to do an analysis for my boss that will include several date/time calculations. (number of Invoices per day, week, month, average invoice amount per day, week, month, etc.)

I am using Excel 2010 with PoxerPivot V2 and my data resides in a SQL 2008 R2 database. I do the "heavy lifting" in SQL by creating a "view" that does most of my data mashing. ( I add the day of week, month, week number, month number, etc. from a calendare table in my SQL database) Then I link to the view usinf PowerPivot.

I have read numerous posts that say that PowerPivot REQUIRES a Calendar table to do complex date/time calculations and I am wondering if someone could elaborate on the WHY this is required and the best way to meet the requirements.

It would be no problem for me to add a link to my SQL calendar table in Powerpivot.

Here are just some of my questions.
1. Should I leave the calendar table "disconnected" or establish a relationship to my sales data (Invoice Date)?
2. Should I add calculated columns/measures where day of week, week num, month, etc. are based on the columns from the calendar tabel or are the columns from my initial "view" sufficient?
3. If the answer to question 2 is that the view is sufficient then is the seperate calendar table used primariy for building splicers that contain all possible combinations.

Tis message board has really helped me start to build meaningful reports using PowerPivot. Thanks in advance for your help.

Robert Cotton

 

[Jacob Barnett]

Robert,

First thing to say is that PowerPivot doesn't necessarily REQUIRE a separate dates table but it is absolutely part of good model design, for the following reasons:

- Combining Data: Let's say your Boss's next question is to add some details about Payments to your report which means an additional data table. To pull the two together you are going to need a unique set of dates in order to create your relationships. Once done all you need do to is bring in the relevant date columns to your Pivot and your data will be automatically blended.

- Performance: PowerPivot uses columnar compression and you get much better performance out of 'Fact' tables that are tall and thin. On that basis you want to store as little information as possible about the date (i.e. just the date) in the fact table. My calendar table has 34 different columns and my main fact table has 10m records - storing values for those 34 columns 10m times just wouldn't be practical. I can also add columns to my calendar table at any time.

- Custom Calendars: This may be less relevant to you but 99.9% of us don't have the ability to create a view on the corporate server but we can create a complex table in Excel/Access that reflects the intricacies of the business (e.g. 4-4-5 or specific planning periods/financial years). My PowerPivot calendar is about 70% driven by a static table that is fed from an Access database and the remainder is calculated columns that deliver dynamic stuff like YTD, QTD etc.

- Certain time intelligence functions (apparently) require the column with unique, contiguous dates.

To answer your questions directly:
1. Your calendar would usually be connected to the Fact (invoice) table on invoice date. There are techniques for dealing with a fact table that has 2 date columns that require a disconnected table.
2. You should limit calculated columns where possible for performance reasons - stuff like day, month etc aren't dynamic so need to be calculated in an external excel table or populated from your SQL query on import.
3. See above.

Hope this helps.
Jacob

 

[miguel.escobar]

what Jacob said plus! if you don't have a DATES Table then you won't be able to use the DATES Filters in your pivot tables. Also, as Jacob said, using the fields (columns) from your calendar table you'll save storage space and also note that you should always use the DIM tables as slicers.you should rarely try to slice stuff based on your FACT (trying to filter a 5M table its rather more difficult than filtering a 5 rows table) and the filter propagation helps a lot.

 

[RobertCotton]

Thanks Guys ! I really appreciate your feedback and support.

Robert