Power BI Data Model Architecture

Modified on Sun, 26 Jul at 3:58 AM

Overview

QQube simplifies Microsoft Power BI by providing a complete, prepared data model.

You do not need to build the connections, transformations, relationships, hierarchies, or reporting structure from scratch.

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

The prepared solution contains two primary components:

Power Query
Data Model Management

Power Query

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

Connect to QQube

Power Query establishes the connection to QQube so the data can be refreshed automatically.

Apply Preconfigured Steps

Power Query applies the prepared steps every time the data is refreshed.

These 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.

Power Query connection and transformation steps in a QQube Power BI model

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.

Power Query Editor displaying a prepared QQube query

Review the Query Settings

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

Power Query settings window opened from the cog icon

Do Not Change Prepared Steps

Do not modify the prepared Power Query steps unless you are an advanced user. The most common exception is adding fields that are not included by default.

Data Model Management

Power BI 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.

Examples include:

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.

QQube Power BI model showing relationships, hierarchies, sort columns, and hidden fields

Advanced Materials

Advanced users can review the Power Query Conventions guide for additional technical details.

Expected Result

Power Query connects to QQube and applies the prepared retrieval and transformation steps.

Power BI then manages the relationships, hierarchies, sort columns, and hidden technical fields required for reporting.