Getting Started with Power Pivot

Modified on Fri, 21 Aug at 5:06 AM

Overview

Microsoft Power Pivot provides several advantages over a standard Excel PivotTable:

Measures can be used to calculate and filter report results.
Millions of rows can be managed efficiently.
Additional data sources, including spreadsheets, can be added to the model.

QQube adds the following capabilities:

Power Query is used as the underlying data engine.
More than 5,700 DAX formulas are included.
The Power Pivot and Power BI data models use the same structure.
The prepared models eliminate hundreds of hours of development work.

How to Begin

Power Pivot does not use the QQube Excel Add-In workflow used by standard Excel PivotTables. Start by opening a prepared Power Pivot example from the QQube Configuration Tool and refreshing the workbook.

Start with a Power Pivot Example

Open the QQube Configuration Tool and select Microsoft Power Pivot under Examples.

The available Power Pivot examples appear in the list. At least one example is provided for each data model.

Grayed-Out Examples

A grayed-out example means the required data model is not loaded or has a synchronization error. Review the synchronization log guide before opening the example.

Microsoft PowerPivot examples listed in the QQube Configuration Tool

Open and Refresh an Example

1. Confirm Successful Synchronization

Review the synchronization logs and confirm that the required data model loaded successfully.

2. Open the Power Pivot Example

Double-click the required example in the QQube Configuration Tool. The workbook opens to an instruction page explaining how to refresh the data, review the underlying Power Query data source, create a new PivotTable, and use the available measure types.

QQube PowerPivot example instruction page

3. Refresh the Workbook

Select one of the report-example tabs, select Data, and then select Refresh All. This refreshes the QQube data used by all report tabs in the workbook. The first refresh may take several minutes, depending on the size of the QQube data set.

Excel Data menu showing Refresh All for a QQube PowerPivot workbook

4. Build or Modify the PivotTable

Select from the available fields and measures in the PivotTable Fields pane. After the workbook is refreshed, all report design is performed with standard Excel and Power Pivot features.

Excel PivotTable Fields pane in a QQube PowerPivot example

Select Assistant Is Not Used

The Select Assistant is grayed out because the QQube Excel Add-In is not used with Power Pivot data models.

Expected Result

The Power Pivot example opens with its Power Query connections, relationships, fields, measures, and DAX formulas already configured. After Refresh All completes, the workbook is ready for Excel reporting and customization.