Power Pivot, the self-service BI tool

Microsoft Power Pivot is a new technology included in Excel 2013 and available as separate add-in for Excel 2010 aimed at providing self-service Business Intelligence (BI).

Power Pivot is a real revolution inside the world of data analysis, because it gives you all the power you need to perform complex analysis of data without requiring the intervention of BI technicians.

This tool, an Excel add-in, implements a powerful in-memory database that can organize data, detect interesting relationships, and offer you a swift way to browse information.

These are some of the most interesting features of Power Pivot:

  • The ability to organize tables for the PivotTable tool in a relational way, freeing the analyst from the need to import data as Excel worksheets before analyzing the data.
  • The availability of a fast, space-saving columnar database that can handle huge amounts of data without the limitations of Excel worksheets.
  • DAX, a powerful programming language that defines complex expressions on top of the relational database. DAX allows you to define surprisingly rich expressions, compared to those that are standard in Excel.
  • Amazingly fast in-memory processing of complex queries over the whole database.
  • The ability to integrate different sources and almost any kind of data, such as information from databases, Excel worksheets, and sources available on the Internet.

Some people might think of Power Pivot as a simple replacement for the PivotTable, some might use it as a rapid development tool for complex BI solutions, and others might believe it is a real replacement for a complex BI solution.

Power Pivot is a great tool for exploring the BI world and implementing BI solutions. It is not a replacement for a complex BI solution, such as those built on top of Microsoft Analysis Services, but it is the first tool available from Microsoft to handle self-service BI, a technology that lets you build complex data structures and analyze them without contact IT staff!

Microsoft Power Pivot

Articles on Power Pivot

Here's a collection of recent articles on Power Pivot from SQLBI experts:

  • Duplicated names in DAX

    This article describes how DAX resolves column and measure names in DAX, providing best practices to avoid conflicting names.

  • Lookup multiple values in DAX

    This article describes different techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance.


  • Context Transition and Expanded Tables

    This article describes how table expansion and filter context propagation are important DAX concepts to understand and fix small glitches in DAX expressions.


    This article explains how to use SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance.


  • Compare equivalent periods in DAX

    If you do not have a full month with data, comparisons such as year-over-year (YOY) might require particular filters in order to do a like for like comparison in DAX. This article describes how to write DAX expressions comparing equivalent periods, keeping corresponding number of days in the comparison.

See more articles

Recommend this course

Do you think that a colleague or a friend can be interested
in Power Pivot Workshop? Recommend it!