PowerPivot Data Model in QQube For QuickBooks | CLEARIFY

Architecture of a PowerPivot Data Model

QQube does all of this for you out-of-the-box. 

This guide is for informational purposes only, with additional links for advanced users.

There are two components:

1. Power Query.  Connects to the views in the QQube Database.
2. Data Model Management.  Relationships and Hierarchies.

Power Query

QQube uses the same Power Query Models for both PowerPivot and Power BI, and you can also attach data from other databases to interact with the QQube data.

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 transforming 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