Getting Started with Power Pivot

Modified on Sun, 26 Jul at 3:52 AM

Overview

Microsoft PowerPivot 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 PowerPivot and Power BI data models use the same structure.
The prepared models eliminate hundreds of hours of development work.

How to Begin

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

Start with a PowerPivot Example

Open the QQube Configuration Tool and select Microsoft PowerPivot under Examples.

The available PowerPivot 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 PowerPivot Example

Double-click the required example in the QQube Configuration Tool.

The workbook opens to an instruction page that explains:

How to refresh the data.
How to review the underlying Power Query data source.
How to create a new PivotTable.
The available measure types.

QQube PowerPivot example instruction page

3. Refresh the Workbook

Select one of the report-example tabs.
Select Data.
Select Refresh All.

This refreshes the QQube data used by all report tabs in the workbook.

Initial Refresh Time

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 PowerPivot 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 PowerPivot data models.

Expected Result

The PowerPivot 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.