Home » business intelligence » Understanding how Power Pivot and Power Query contribute to a successful Business Intelligence project built with Power BI

Understanding how Power Pivot and Power Query contribute to a successful Business Intelligence project built with Power BI

For readers familiar with Microsoft’s Power BI suite of tools for Excel, but not clear as to how Power Pivot, and Power Query interact within a Power BI project, a webcast from TechEd Europe 2014, titled Overview of Business Intelligence in Office and Office 265 may be worth watching. The webcast is led by Peter Myers, a subject matter expert on the topic, and runs about 1.25 hours.

Myers identifies the target user for the Power BI suite: a business analyst from a Line of Business (LoB) unit within an organization. He explains how research has shown analysts are the biggest users of Excel for data analysis. He also explains how a central IT support organization usually interacts with this target user, including a mention of why it makes sense for LoBs to sponsor an effort to produce a data governance plan, which a central IT organization can use to sign off on a business intelligence (BI) project, which an analyst, as the target user, will be likely to produce.

But what I found to be really useful within the first 10 minutes of the webcast, was how Myers presented the first of his three components of a successful BI project:

  1. “Explore”
  2. “Visualize”
  3. “Control”

The “Explore” phase, as Myers presents it in the webcast, is characterized by a need to collect data directly related to business process and to provide it with a form suitable for analytics manipulation. The steps in this phase amount to “[c]ombin[ing] and analyz[ing] large datasets; [s]ummarize data, and discover trends” along with a need to “[I]nstantly preview charts and pivot tables” (quoted from one of the slides included in this webcast).

As he describes the “large datasets” typically required for the type of analytics he is about to demonstrate, he segues into a presentation of Power Pivot as a tool developed to overcome Excel’s row limitation (he explains this limit amounts to 2 to the power 20 rows). He defines Power Pivot as “the ability to load data into the workbook, but not into the work sheet. It is loaded into a data model”. So Power Pivot is used to store large amounts of data for the overall Power BI process.

When he turns to discuss the process required “to access data”, which he explains typically includes a need to “access, filter, cleanse, and to transform before it is added to the workbook”, he introduces the Power Query add-in. He goes onto explain how Power Query can be looked at as the Data Warehousing tool for the business analyst to implement an ETL (Extract, Transform and Load) process in Excel.

To reiterate: stakeholders in a Power BI implementation for SharePoint 2013 on-premises, or for SharePoint Online, Office 365 should free up sometime to watch this webcast.

©2014, Ira Michael Blonder & Rehmani Consulting, Inc. All Rights Reserved