PowerPivot Data Model Architecture

Overview

We make it easy to view your data in Microsoft PowerPivot, as everything is already put together for you.  You don't have to deal with the complex underpinnings of creating a data model from scratch.

Additionally, we use the same methodology for both Power BI and PowerPivot.

There are two components:

1. Power Query 
2. Data Model Management

Power Query

Power Query serves the following purposes:

• Establish connection with QQube so the data can be automatically refreshed
• Apply pre-configured steps to the data every time it connects
  • Retrieve the chosen fields
  • Apply field renaming
  • Add certain fields that are not intrinsic to QQube e.g.
    • Calendar calculation fields
    • Sign to Apply for Financial Statements

Microsoft built Power Query to be a "Transform" tool which takes raw data and molding it into usable pieces.  However QQube performs this transformation internally, as Power Query would never be able to handle the transformation complexities intrinsic to "raw QuickBooks data".

If you click on the "COG" you will see this window:

In general, it is wise not to change what is here, unless you are an advanced user.  Exceptions are to add fields that we have not included by default.

Data Model Management

PowerPivot allows you to manage the resulting data from Power Query:

• Create and Manage Relationships There is one FACT table and multiple DIMENSIONS that can be connected only one way.  However temporary relationships might exists for certain types of calculations.
• Create Hierarchies 
  • Multi-level lists such as accounts, items, classes
  • Date and Time periods such as Year-Month or Year-Quarter-Month
• Apply Sort Columns Ensure fields such as Month come out in the proper order
• Hide Fields not used in reports such as "ID" fields used to link tables together