Guest post by Thomas Allan
Intro by Avi: As in Excel, in Power Pivot there are often many ways to accomplish the same thing. For example you can source a Date/Calendar table from Excel, Azure (here and here), Power Query, and SQL (this post). That is usually a sign of strength of the tool. Although it also makes it more challenging/fun to be able to weigh the options and decide which one works best for you given your situation. Thomas, shows us a cool way to pull Date table from SQL.
Stepping back a bit, there is some good interplay between Power Pivot and SQL. In terms of feeding Power Pivot using SQL – see Why PowerPivot is Better Fed From a Database Part 1 and Part 2. And also Power Pivot being a great addition for SQL savvy folks – see I Know SQL Queries, So Why Do I Need Power Pivot?. Goodness all around, I say 🙂
Take it away Thomas…
Benefits
In addition to often mentioned benefits of using SQL servers as data stores (flexibility, reliability, scalability and security), the benefit of linking to a centralized source that delivers results quickly, consistently to practically any client, anywhere, adds a powerful dimension of “portability“ from the outside of the Excel workbook as Power Pivot and DAX formulas offer on the inside.
Date Table
Four types of resources are often used to create date tables within Power Pivot: 1) Excel itself, using formulas or VBA, 2) data feeds, which you can find an example of following this [link removed due to 404] hyperlink, 3) Power Query, which you can find an example of following this hyperlink, and 4) relational databases.
The example that follows was developed using the relational database SQL Server 2012 and uses only table-valued functions. Although the code was developed on SQL Server 2012, it was also tested on a 2008 release of Microsoft’s flagship database product.
For demonstration purposes, the solution offered here is based on a calendar fiscal year (quarters start January 1, April 1, July 1 and October 1). For other types of calendars, such as 4-4-5 or school semesters, the code can modified by a SQL developer (also, for other types of calendars, some architectural issues may also apply inside Power Pivot, which are fully explained in Rob Collie’s comprehensive Power Pivot course).
This post assumes that the reader has basic familiarity with SQL Server Management Studio, sufficient to install table-valued functions, or has access to someone who knows how to install table-valued functions. This post also assumes familiarity with connecting to a SQL Server database from within Power Pivot (similar to connecting to an Access database).
Download SQL code below.
Discussion
The solution described in this post uses three functions, of which I am the author only of the top-level function (GetCalendar).
After the functions have been installed on your target SQL database and you have created a connection to that database within Power Pivot and have advanced to the Table Import Wizard, you will chose the option to “Write a query that will specify the data to import”, instead of “Select from a list of tables and views to choose the data to import.”
After choosing “Next”, you will then be prompted for a “Friendly Query Name” which will become the table name in Power Pivot (I typically just use “Calendar”) and then in the edit box under “SQL Statement” you could hard code a range like this:
or you could code something like this that will automatically adjust start and end dates depending on when the data is refreshed:
After validating the code and choosing Finish, you should see a sheet-tab in the Power Pivot window that looks like this (I am using Excel 2013 and have formatted the date columns):
During testing, in addition to date data types, I also passed in datetime and datetime2 arguments without raising any errors (SQL Server handled the implicit conversion to date type and truncated the time component).
Performance I found satisfactory: a ten year calendar table was produced on my testing platform within a quarter of a second (223 ms).
Giving Credit to the form of the Calendar Table
The calendar table was created following calendar patterns I observed in Kasper de Jonge’s book, Dashboarding and Reporting with Power Pivot and Excel and on his website.
Giving Credit to the Author of Two of the Three Functions
The power of the SQL code comes from two functions (GetDates and GetNums) written by Itzak ben-Gan, one of the great SQL developers and mentors. GetCalendar calls GetDates, which in turn calls GetNums. Both functions were taken from Itzak’s book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (code for GetDates will be found on page 137 and code for GetNums will be found on pages 103, 135 and 146).
The function GetNums is a sequence generator that can create continuous number sequences that far exceed the requirements of PowerPivot calendar tables and the SQL Server date type. For example, I had no problem creating a calendar table that ended on December of the year 9999. The function GetNums uses geometric progression to create sequences of integers that can exceed 4 billion (from all of about 10 lines of code).
The function GetDates, applies dates to the sequence of integers generated in GetNums.
The function GetCalendar manipulates each date returned by GetDates with built-in SQL Server functions such as “datepart” and “datename”.
Saving You Grief
Note: If I have any claim to fame, it is that it seems I have an uncanny ability to make every mistake possible before getting something right. Hopefully, these personal issues can be made to benefit others who might also accidentally get cross-wise on a point or two of Power Pivot and Excel functionality.
Since SQL Server does not use Excel’s serialized date format, knowing the following may save you a couple minutes or spare you from some temporary grief:
Never try to format a date retrieved from SQL Server inside of an Excel pivot table or pivot chart; always format the date in the Power Pivot window, in the Formatting options of the Home tab. Since SQL Server defines dates differently than Excel, you can only format a SQL Server date within the Power Pivot window.
Never format the “Date” column of a date table using the Format function (it will be converted to text). If you use the Format function on a different column, you will probably need to specify a Sort By column.
Source Code
Source code for the three functions used to create SQL Server calendar tables, in addition to both example “select” statements used to query the database can be obtained by following the hyperlinks below:
