Excel PowerPivot: The poor man’s data warehouse

Recently, one of my employees was working to automate an excel based report.  The old school was to Excel automation was involving heavy use of macros and VBA.  This would execute queries or copy data around between sheets spread out on network shares and accomplish some data task.  From my experience this was very brittle and prone to failures.  This time around, I told by employee to use Power Pivot to accomplish the automation.  There is no VBA and only SQL statements that get executed.  The visualization is accomplished by using Pivot Tables and Pivot Charts.  Power Pivot is awesome in that it can pull data from many different types of data sources, SQL, Oracle, SSRS, SSAS, MySQL and Excel Sheets.   The trick is that in order to make your data model work well for reporting you have to think a little like a data warehouse architect.  THis will enable a power user to create self refreshing data sets that can be analyzed in Pivot Tables and Pivot Charts.  Let me walk you through a simple Power Pivot model that I created to track some of my personal fitness goals and health metrics.
Let’s start with the all important time table.  In Data Warehousing, almost all measurable data ties to a point in time, and we model that with a Time Dimension.  In my example, I have a sheet with one column a date and time.  I increment each row by one hour and copied it down about 10,000 times.  You then click “Add to Data Model” and you created a Power Pivot Table.  To make this more useful and interesting for slicing data you need to add some columns.  For Example, add the day of week with this expression: FORMAT([DateTime], “ddd”).  Finally, create a key column.  I learned this trick from a former colleague, you can create a easy to read key for dates and times with a little addition and multiplication.  You could easily exclude that or expand in out to minutes or seconds even: (YEAR ([DateTime]) *10000) + (Month ([DateTime]) * 100) + DAY ([DateTime]).
Adding a Sheet to Power Pivot
add to pivot
Adding Columns
adding columns
Once you have your time situated, you are ready to start creating some tables with data to measure, fact tables in the Data Warehouse lingo.  I wanted to start with three measures: calories, exercise, and weight.  All of these tables started out very simple, there was a column for the date and column for the value.  In the Power Pivot designer I would then repeat the creation of the Date Key on each table.  Final step is to create an association between the fact tables and the time dimension using the calculated DateKey column in Power Pivot; the easiest way to do that is by clicking and dragging in the Power Pivot designer.  Once that is complete you are ready to start adding Pivot Tables or Pivot Charts in your spread sheet.
A Pivot Chart
pivot charts
The Completed Model