skip to Main Content

Thomas

Many years with enterprise operational databases using SQL. Currently a consultant for implementing Power Pivot and developing self-service business intelligence resources. Author of active open source project on CodePlex for extraction of Power Pivot measures (calculated fields) in Excel 2010 data models (http://powerpivotmeasurereader.codeplex.com/). Previously SQL developer for Service Station Systems (6 years) and Manager of Information Services for a manufacturing company (6 years).

SQL Date Tables in Power Pivot

Guest post by Thomas Allan

SQL to Power Pivot

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.

Read the Rest