At times, interacting with inspection data using iAuditor Analytics might not be enough for your reporting. For such cases, business intelligence tools such as Power BI can enhance the experience greatly through the creation of rich, interactive reports with visual analytics. This article outlines how you can export your iAuditor data and import it to Power BI Desktop, as well as guidance on how you can model, manage, and visualize your data on the platform.

Requirements:

  1. iAuditor Premium subscription
  2. iAuditor API Token
  3. Microsoft Power BI account

Export iAuditor data and import into Power BI

  1. Use the iAuditor Exporter to export your inspection data to CSV format
  2. Launch, and sign in to Power BI Desktop or Power BI online
  3. Click “Get data” on the lower-left corner of the screen
  4. Under “Create new content,” select “Files”
  5. Choose “Local File” from the row of options
  6. Navigate in your file directory to “Open” the exported CSV file
  7. Powe BI begins to import your inspection data and notifies you when complete

Model the data

By creating a relationship between multiple inspection fields and their unique audit IDs, you can dynamically filter data and even visualize them. To create this relationship, you first need to create a new calculated column that contains the unique audit IDs. Microsoft offers a helpful learning module if you wish to learn more about modeling data in Power BI.

IMPORTANT NOTE

The following instructions require data to have been imported into Power BI already, please follow the instructions above to export iAuditor data and import into Power BI if you have not yet done so.

  1. Launch Power BI Desktop app
  2. Open the dataset you wish to model
  3. Click the “Data view” icon from the left-hand side panel
  4. Select “Modeling” ribbon from the top panel. This enables the formula bar for calculations using Data Analysis Expressions (DAX) language
  5. As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula. The Power BI formula bar will suggest specific DAX functions and related data columns as you enter your expression.
  6. Copy and Paste the following expression into the formula bar:
    auditIdTable = DISTINCT(TEMPLATE_ID[Audit ID])
    replacing TEMPLATE_ID with the template ID associated with the CSV file you've imported.
  7. Next, you will create another table for the field you want to associate with a specific Audit ID
  8. Follow step 1. above to create a new table
  9. Copy and Paste the following expression into the formula bar:
    TABLE_NAME = SELECTCOLUMNS(FILTER(TEMPLATE_ID, FIND("ITEM_ID", TEMPLATE_ID[Item ID], 1, 0) > 0), "Audit ID", [Audit ID], "COLUMN NAME", [Response])
    Replace TABLE_NAME with the name you would like to name this table
    Replace TEMPLATE_ID with the template ID associated with the CSV file you've imported
    Replace ITEM_ID with the Item ID of the field you are want to be mapped. Yin the Item ID column of the CSV export.
IMPORTANT NOTE

The [Response] near the end of the query can refer to any column from the initial CSV table imported.

Manage data relationships

  1. Set the relationship between the field and its unique audit ID. Power BI allows you to set the relationship between tables or elements visually
  2. To see a diagrammatic view of your data, use the "Relationship view," found on the far left side of the screen next to the Report canvas
  3. From the Relationships view, you can see a block that represents each table and its columns, and lines between them to represent relationships
  4. Click and drag the audit ID in theauditIdTable and drop it on the Audit ID in the table created for the field you mapped
  5. Follow this same process for any number of fields, which will allow the fields to interact dynamically when you create visualization filters

Here is an example of a working Relationship view, using iAuditor inspection data. You can see the raw data table on the left, and the Modeled data on the right, all connecting back to the unique audit ID table.

 

Visualize iAuditor data in Power BI

Here are some guides to help inspire your Power BI visualizations.

  • Create a line graph of audit scores over time, then slice the data by time frame:
  • Dynamically visualize the number of audits completed by each user. Then slice the data by date completed, region manager, audit author, and more!
  • This map shows the location of various audits conducted in Sydney, Australia. The size of the circle indicates the audit score; the circle's color shows which team conducted the audit. Below, the data cycles through the data for each business team:
  • Create a heat map of your organization's auditing frequency:

Was this article helpful?

We love your feedback. Please tell us what you think.


Yes No
Care to share a bit more so we can continue to make improvements for you? Care to share a bit more so we can make this article even better for you? Thanks for your feedback!