There has been quite a lot of interest in Microsoft PowerPivot for Excel ever since it was launched in Excel 2010. In fact, out of all the “Power” tools, it is apparently the most popular, as evidenced by the # of google searches performed on it (see Figure 1 below)

Figure 1 - Avg. Monthly searches for various “Power” tools in Google as of 12/3/2014.

Although quite a lot has been written about PowerPivot and its features, there are certain aspects about PowerPivot that may not be very obvious, especially to someone who is new to the tool. Since it is the most popular Microsoft “Power” tool out there, I decided to devote an article discussing the top 5 things that you MUST know about PowerPivot. Additionally, in order to make it actionable, I discuss how these five facts can be used by end users and organizations planning to implement PowerPivot.

So, let’s get started.

1. It is a Self-Service BI tool for Power Users

Amongst all the hype about PowerPivot, one thing that may not be apparent is the fact that it is a tool that is intended to be used by Power Users as opposed to BI Architects and developers. The idea behind introducing a new tool was to solve the age old BI problem: BI projects are simply not designed to be completed in a matter of months, let alone weeks or days. Any enterprise data warehouse no matter how large, will always be missing some key data elements that are needed by users. It takes IT a significant amount of time to add new data elements to the data warehouse because of the various necessary ETL/Integration steps. PowerPivot when used by Power users who understand the tool and the data within their organization, can be the perfect complement in such a situation.

Power users can use it to add or create key data elements or metrics that are not present in the data warehouse in a matter of days. This can be a win-win situation provided the proper data governance protocols are followed (a topic of a separate article). These new data elements are added to their PowerPivot model, of course, and not the enterprise Data Warehouse. However, with a proper process in place, PowerPivot in the hands of capable power users can serve as the staging ground for the Data Warehouse.

2.  It is the perfect destination for Data Mashups that combine a variety of data sources

PowerPivot can virtually bring in data from any number of data sources. This includes relational databases, multidimensional sources, cloud services, data feeds, Excel files, text files, and data from the Web. Once data is brought into the PowerPivot model, it all acts as one seamless model regardless of its source. Earlier, we discussed the use-case of PowerPivot as a self-service tool used alongside an existing Data Warehouse. Another great use-case for PowerPivot is when you are trying to sell the value of an enterprise Data Warehouse. Rather than spend months building the ETL, cleaning the data, creating conformed dimensions and star schemas, PowerPivot can be used to quickly pull data from a variety of sources and create a workable data model in a matter of weeks. Power View can then be used to build graphs and charts on this combined data and management can see the value in types of analysis that can be performed on the combined data.

Here is a short 2 min video showing another application of the data mashup feature where PowerPivot is used to bring external data from an Atom data feed from a website.  If you combine PowerPivot with Power Query, Microsoft’s self-service ETL tool, you can mashup data in many previously unthinkable ways to get the perfect data set for analysis.

3. Although it is within Excel, PowerPivot uses the powerful Analysis Services Tabular Engine

PowerPivot is accessed from within Excel. Hence that gives the impression that the data sits inside Excel and PowerPivot operates on top of that data. This is not true. PowerPivot uses the same engine that MS Analysis Services (SSAS) Tabular uses and PowerPivot data sits inside that engine. This is the reason why PowerPivot does not have many of the limitations of Excel. For e.g. you can store approx. 2 billion rows and your model can have 2 billion columns, which is obviously not the case with just Excel.

View the PowerPivot Capacity Specification to see if you will hit any of PowerPivot’s limits in your project. Please note that these indicate the limits of PowerPivot. You will need to provide the machine enough resources (memory, 64-bit OS, etc.) in order to get close to those limits.

4. You do NOT need SharePoint in order to leverage PowerPivot

Whilst SharePoint provides a large number of benefits in terms of sharing, securing and refreshing PowerPivot workbooks (and I would encourage you to look at it), it is not a necessity when using PowerPivot. Microsoft has designed PowerPivot to be used as a stand-alone product with Excel. This works great when your shop does not already have SharePoint. Implementing SharePoint can offer the organization a number of capabilities, however, it is a huge investment. The good news is that you can start to explore and use PowerPivot without SharePoint. It can be a perfect Personal BI solution in the absence of SharePoint.

5. You will surely need to learn DAX in order to fully utilize the power of PowerPivot

If you have used PowerPivot for some time, you will soon realize that in order to utilize it for meaningful analysis, you will have to start building calculated measures and fields. The good news is that DAX is based on Excel formulas, and it is meant to be much less intimidating than MDX (the language used by SSAS Multi-dimensional). However, that being said, once you have used DAX for some time, you will realize that it starts to get complex pretty soon. Like they say, everything comes with a price and at some point, power users who want to utilize PowerPivot will have to pay the price and learn DAX.

The stairway to PowerPivot and DAX is an excellent place to start if you want to start learning DAX. PowerPivotPro also has an entire section dedicated to DAX. If you are looking for some tools when learning DAX you will find them at Marco Russo’s blog here.

If you are an astute reader, you may have observed a contradiction or anomaly at this point. If PowerPivot is meant to be a tool for end users, albeit power users, why does it require them to learn DAX especially when DAX itself can get complex? Shouldn’t an end-user tool be simpler? Well, I would love to hear your thoughts on this issue before I share mine. Please use the comments section below to share your thoughts.

Well, those were areas I thought you ought to know about PowerPivot. It is a great tool with huge potential and I am certainly excited about it!