Power BI Data Model Architecture

Modified on Sat, 29 Jun at 1:53 AM

Overview

We make it easy to view your data in Microsoft Power BI, 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 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

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



Advanced Materials

Power Query Conventions

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article