PowerPivot Data Model Architecture

Overview

QQube simplifies Microsoft PowerPivot by providing a complete, prepared data model. You do not need to build the connections, transformations, relationships, or reporting structure from scratch.

QQube uses the same model design for both PowerPivot and Power BI.

The prepared solution contains two primary components:

1. Power Query
2. Data Model Management

Power Query

Power Query performs the following functions in a QQube PowerPivot model:

• Establishes the connection to QQube so the data can be refreshed automatically.
• Applies preconfigured steps whenever the connection is refreshed.

The preconfigured steps include:

• Retrieving the selected fields.
• Renaming fields for easier reporting.
• Adding fields that are not intrinsic to QQube, including:
  • Calendar calculation fields.
  • Sign to Apply for Financial Statements.

How QQube Uses Power Query

Microsoft designed Power Query as a transformation tool that converts raw data into usable reporting structures.

QQube performs the complex QuickBooks data transformation before the information reaches Power Query. Power Query then handles the prepared QQube data and applies the remaining connection and model-specific steps.

Review the Query Settings

Select the cog icon beside a Power Query step to review its settings.

Data Model Management

PowerPivot manages the data returned by Power Query.

Relationships

Each QQube data model contains one FACT table and multiple DIMENSION tables.

The permanent relationships are already created according to the QQube data warehouse structure. Temporary relationships may also be used for specific calculations.

Hierarchies

Prepared hierarchies make it easier to drill through multi-level data, including:

• Accounts.
• Items.
• Classes.
• Year and Month.
• Year, Quarter, and Month.

Sort Columns

Sort columns ensure that fields appear in the correct order. For example, Month names are sorted by Month number instead of alphabetically.

Hidden Fields

Technical fields that are not used directly in reports are hidden. These include ID fields used to create relationships between tables.

Expected Result

Power Query connects to QQube and applies the prepared retrieval and transformation steps. PowerPivot then manages the relationships, hierarchies, sort columns, and hidden technical fields required for reporting.