PowerPivot Example: Building a Dashboard with PowerPivot
Click below to download complete PowerPivot example in an Excel workbook. The example is explained in the text below.
Download complete Excel example HERE.
Every quarter your team spends countless hours preparing reports for the board meeting. Getting data from different reports, entering data into Excel, preparing exhibits and checking and rechecking formulas. After you get the first set of charts ready then you need even more – to explain this quarters variations. So your team spends more hours slicing and dicing the data, preparing exhibits and checking results.
It is such a hassle to prepare the quarterly board reports and a dozen other key management reports that last year you spent time looking for a better way. You met with salespeople from two different “Business Intelligence” software companies. At first it sounded hopeful but you soon realized that the software solutions were complex and really more suited to a larger company with strong IT resources. There seemed to be no easy solution.
This year the company upgraded to Excel 2010 and the new feature called “Self-Service Business Intelligence” caught your eye. You learned that the theory was that an Excel user could tap into the company’s data files and combine and report on data just using Excel. No programming required. No complex Business Intelligence software required.
After a few hours of research you were able to build a report that looked like this:
The charts combine sales data and product data from the company’s database system with marketing expense and sales budget data from Excel worksheets. The buttons on the right allow you to slice the data by time period, sales territory and product type.
Best of all, to update the report for a new month or quarter all you have to do is press a “refresh” button. No more entering data, rebuilding charts of rechecking formulas. When you need more detail to explain the results you can use the “slicer buttons” on the right to instantly change the analysis.
Too good to be true? That is what your boss said!
To convince him you prepared a walk thru of the four steps you used to prepare the dashboard. Each to the four steps is covered in an appendix to this document.
Step 1: Think about the data you want to measure.
This is the most important step in the process. Whatever you want to improve, start by measuring it. An easy to maintain dashboard will help to focus everyone’s attention on improvement.
After deciding what to measure you need to find the data. This might require meeting with a technical resource. In this example you got the data from six interconnected database tables and two Excel worksheets.
Step 2: Connect Excel to the data
PowerPivot has all you need to connect to databases, Excel, Access, text files or sources of data from the web. Once connected PowerPivot stores a copy of the data in Excel. Millions of rows of data can be effectively stored by PowerPivot using a new technology that compresses the data.
When you are ready to update the data you just click on the PowerPivot “Refresh” button and an updated copy of the data in copied into the PowerPivot database.
Step 3: Prepare Excel charts to show the measures
You can make Excel pivot tables and pivot charts from the data in the PowerPivot database. Add one pivot table to a worksheet. If you need a pivot chart add the chart to the same worksheet. These worksheets will be hidden in the final dashboard. Use them to get your data and measures right.
Step 4: Copy the charts to a single worksheet and add slicers
Copy charts from the pivot table worksheet to your dashboard sheet. Arrange the charts on the dashboard. Then add “Slicers” which are groups of buttons used so filter the data in your charts. Each slicer can be attached to multiple pivot tables. When you click on a year in the slicer on the dashboard then each of the pivot tables attached to that slicer will show data only for that year. In this way slicer buttons on the dashboard worksheet control what data is shown on the dashboard charts.
Your boss is not very fluent in Excel so as you expected his first question was “Why couldn’t you do this with Excel 2003?” You explained that Excel 2010 has three new features that are all part of the new Excel PowerPivot Self-Service BI capability.
Feature 1: Excel PowerPivot allows you to create a simple database within Excel. In the example above you created a database inside of Excel with 9 tables: 6 from the company’s database system and 3 from Excel worksheet data. You were able to specify relationships between the tables so that data could be combined in my charts.
Feature 2: Excel PowerPivot pivot table and charts allowed you to quickly prepare the charts. Unlike the lightweight pivot tables in Excel 2003, these pivot tables can combine data from different tables and come with new formulas that are essential for serious analysis.
At first glance PowerPivot pivot tables and pivot charts are similar to the pivot table and charts that have been in Excel since 1993. But the new PowerPivot pivot table and pivot charts have two very significant differences:
- They can combine data from multiple tables. (The old pivot tables required you to get all the data in one table.)
- The new Data Analysis Expression (DAX) formula language that makes it easy to do calculations within the pivot table and line up comparative measures like “”Prior Year To Date”.
Feature 3: Excel Slicers provide a way of selecting data so that multiple charts can be shown together and controlled by one set of buttons.
You told your boss that this is the level of BI that fits your company’s current needs. You know it wouldn’t meet the needs of a much larger company with more IT resources but it can make a real difference in what your company is able to accomplish with Excel.
Appendix 1 - Think about the data you want to measure.
You want to compare actual sales data with the sales budget and with marketing expenses.
- Actual sales data is in the company’s database.
- The sales budget and marketing expense data are in Excel spreadsheets.
You will want to meet with the database technical support person to identify the tables where the data is stored. If your database is like most you will have to use several tables to get the data you want. This example uses a database sample from Microsoft where the Sales Order Detail contains the sales amount and product code, the Sales Order Header table contains the sales date and territory, the Product table contains the Product Name and Product Sub Category Code, the Product Subcategory contains the Subcategory name and Product Category Code and the Product Category table contains the Category name.
This all sounds complicated but when PowerPivot imports the data from the database it also imports the relationships. There usually is nothing for you to do before you can actually use the data. You don’t need to understand anything about databases!
As in most analyses data you need is not all in the database. In this example the sales budget and marketing expense data are in an Excel spreadsheet. No problem, the PowerPivot database can combine data from multiple sources. Since each row of the Excel data contains the same Territory ID and Product ID codes that are in the database, PowerPivot can combine the data.
Appendix 2 – Connect Excel to the data
Starting from a blank workbook, you first open the PowerPivot Window, connect to the sales database and select the sales order header and detail tables, the sales territory table and the product, product sub category and product category tables:
Opening the PowerPivot Window and connecting to the database.
After connecting to the database, data from the six tables has been imported to the PowerPivot database in the Excel workbook.
Even though PowerPivot has lots of data (121,317 Sales Order Detail records in the above example), workbook size remains manageable.
Pressing the refresh button will reimport an updated copy of the data.
Now you connect to the Excel data. First step is to copy the sales budget into the blank workbook. Make the copied list into a table and create a liked table in PowerPivot.
Sales budget copied to Excel.
Add the Excel table to the PowerPivot database.
And the table is included in the PowerPivot database .
You follow the same steps to copy the Marketing Expenses into Excel and then add to PowerPivot.
One last thing to do in the database is to make a “Date Table”. This is always a good idea for two reasons:
You will want to use a different date measure in your report than exists in the data. In this example you want to report by month and the data is by month, day and year. Also, PowerPivot will sort February before January so it will be helpful for the date table to format the months as yyyy – mm (2011 -12).
You need to filter the actual sales data, budget data and marketing expense data on sales date. Having all three types of data linked to a common date table will speed this process.
You build the date table in Excel and link to PowerPivot.
PowerPivot has already imported these relationships from the database:
You need to add the relationships in yellow below so that you can filter across the data month and year.
The last three relationships relate the sales budget and the marketing expenses to the sales territory and product tables. These relationships would be automatically added by PowerPivot the first time you produced a report of the data. Since you had to add the date relationships you thought it was easier to add them at the same time.