In-Depth
Building Creative PowerPivot Solutions
Creative ways to leverage PowerPivot.
by Alex Porter
For the past year, I have had the pleasure of observing, working with, and learning from early adopters of PowerPivot. I was responsible for passing on the lessons learned and have been involved in implementing several PowerPivot solutions in numerous organizations. Through this experience, I have seen some creative ways to leverage PowerPivot, learned exactly what it takes to build a PowerPivot solution, and gained an understanding of the skills needed to be successful with PowerPivot. In this article, I will share some of those lessons with you.
Extending an Existing Cube with Spreadsheet Data
My first experience with PowerPivot was using it to solve a problem from a previous project. I had just finished a year-long project implementing traditional business intelligence (BI) solutions that integrated two corporate systems and deployed an analysis services (SSAS) cube and reporting services (SSRS) dashboards to 1,000 users. The project was a significant investment and user adoption was important. We wanted to keep an eye on how heavily each dashboard was being used and by which groups and roles.
Unfortunately, the usage tracking provided through an SSAS cube was limited. There was some information about users, including their official title and where they belonged in the organization, but the titles were too specific and organizational hierarchy was not the one we needed. Fortunately, we maintained a spreadsheet that listed users by alias, along with the group they belonged to, what their role was, who trained them, and a wealth of other useful dimensional data. Merging the data maintained in the spreadsheet with the data in the cube seemed like a perfect use case for PowerPivot.
Less than a week after first installing PowerPivot, I successfully imported the two data sources, created a relationship on User Alias (which required a bit of data cleansing), built a compelling dashboard with slicers, and published the solution to SharePoint, where it was scheduled to refresh automatically—making it simple for the BI team to review the latest usage stats by group and role simply by navigating to a SharePoint site. You can view PowerPivot sample solutions on Microsoft Downloads (see http://bit.ly/9AVTps).
Replacing Macro-Driven Excel Reports
One early-adopting group I met helped solve an age-old spreadsheet problem. You’ve no doubt seen a reporting solution that requires users to set filter values and push a button that runs macros that update and compile results from dozens of Pivot Tables. These solutions were commonly built in the past (and are still being built today) out of necessity to find a way to compile data from multiple Pivot Tables into a single report, but PowerPivot gives us a way to make this work much better.
With PowerPivot, there’s an actual Analysis Services cube running in memory on your computer -- a technology called in-memory BI (IMBI). Excel allows you to query any SSAS data source using the Excel cube function in which you specify the data source, the measure you want, and which dimension filtering to apply. The first team to accomplish this imported their base table of data into PowerPivot and built their single-worksheet summary report using cube functions to query for specific values needed. With cube functions, you can reference slicers or filters so that users can modify the report. The old macro-driven version of this report used to take about two minutes to refresh when users updated filters. Leveraging PowerPivot’s IMBI cube, the new version takes just seconds.
Analyzing a Simple Table of Data Using Calculated Measures
One of the biggest surprises I’ve had with PowerPivot is seeing how useful it can be with even the simplest data sets. I chatted with someone who was interested in summarizing survey results using PowerPivot. When I saw that the survey data was just a single SharePoint list with only a dozen or so columns, my initial reaction was, “This is too simple for PowerPivot. You can just use a regular pivot table.” While I was teaching him about Pivot Tables, I realized that even with simple data sets, you can do so much more with PowerPivot.
One of my favorite PowerPivot features is a new function syntax called DAX, short for data analysis expressions. DAX allows users to create complex calculated columns and measures using syntax similar to Excel functions. I’ve spent many an hour dreaming up creative ways to use DAX to solve a complicated ratio or time-based calculation. Using DAX calculations that filtered a number of COUNT measures, I was able to help this user quickly come up with a robust summary of his survey results. The best resource available when learning DAX is a white paper available from Microsoft (see http://bit.ly/b290OE). I carry a printed copy at all times.
When to Use PowerPivot
Perhaps the biggest challenge organizations currently face when looking to leverage PowerPivot is deciding when it makes sense to use. Each of the examples outlined above successfully leveraged PowerPivot to solve a business problem. However, if you’re considering a PowerPivot solution and foresee highly complex data models, messy or difficult to access data sources, high volumes of users or data, complex security needs, or the need for frequent refreshes, you might want to consider other options. PowerPivot can handle some of these requirements, but they are not its strong point. Instead, consider using PowerPivot to prototype your solution quickly before choosing the traditional BI path.
Who Should Use PowerPivot?
Microsoft markets PowerPivot to business analysts and Excel power users -- information workers (IWs for short). In a previous article in this series, PowerPivot's Role in Enterprise BI, John Lauer outlined the need for BI professionals to continue to maintain and focus on traditional architecture such as data warehouses, data marts, and OLAP cubes. In the ideal world, IWs would use self-service BI tools such as PowerPivot, Excel Services and Reporting Services to access traditional BI data sources, extend data models with their own data, add calculated measures, build reports to their own specifications, and publish and schedule a refresh on SharePoint.
This is more of a long-term vision. In the current world, IWs face a bit of a learning curve with PowerPivot. Yes, you can import data and create relationships, but without a good understanding of how data relationships work, you’ll immediately run into strange results. BI professionals can quickly identify and understand the problem and determine a solution, but with limited understanding of data modeling, IWs might give up in frustration.
I’ve learned that PowerPivot is a powerful tool that really does what it promises to do -- build robust BI reporting solutions without any code. To be truly successful with PowerPivot, organizations will need to focus on data and users. Ensure corporate data is available in ways that PowerPivot can easily and directly consume, then train users and hold their hands as they excitedly build their first PowerPivot solution.
Alex Porter has been with Hitachi Consulting’s Business Intelligence Practice in Seattle for five years. He spent the last year implementing PowerPivot solutions and also spoke about his experiences with PowerPivot at Microsoft’s 2010 Business Intelligence convention in New Orleans. Alex spent the last several years as the project manager on data warehouse design and implementations -- from road map through final delivery.