Access your data for external analysis within your BI tool (PowerBi, Tableau…) with ease.

This article is for admin users who have access to the SimpliField Data Access feature.

As a pre-requisite, you should have already installed in your laptop the BI tool you are going to use for your analysis and check it has a BigQuery connector (ex: Microsoft PowerBI).

If you do not already use the data access feature, please contact your account manager.

Setup - Step by step for a connection with PowerBI

Please note that this step by step might change a little bit according to the BI tools you are using.

Open PowerBI and select "Get data" → "More"
Select "Database" → "Google BigQuery"

Select the "Service Account Login" panel and enter the Service Account Email provided. In the Service Account JSON key file contents, copy paste the whole JSON key file (it must be without any carriage return). See the example below.

Service Account Email:

[email protected]

JSON Key file:

{“type”: “service_account”,“project_id”: “simplifield-colombus”,“private_key_id”: “e2eeb902741328aa893f08388603b776218ac15a”,“private_key”: “-----BEGIN PRIVATE KEY-----MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCzBhMDfQD4BNqAM2t8y5hSqMNZoBH5TWWOhsV2xz+YaCthIIwKyBQVBinL0xDae3gVuFlz6TQYG6s1nJk6isjuUFfPZ0RFNPZu0zm7KvnB3SlgprZ3DcsmiAh8aLJKVNskL+a5N6850rEFYgzpBBgqeBKYnVCrK8pHKpRbZgx2v0CNOCcEIbx6CZs-----END PRIVATE KEY-----“,”client_email”: “test-bigquery-powerbi@simplifield-colombus.iam.gserviceaccount.com”,“client_id”: “117407023037305387171”,“auth_uri”: “https://accounts.google.com/o/oauth2/auth”,“token_uri”: “https://oauth2.googleapis.com/token”,“auth_provider_x509_cert_url”: “https://www.googleapis.com/oauth2/v1/certs”,“client_x509_cert_url”: “https://www.googleapis.com/robot/v1/metadata/x509/test-bigquery-powerbi%40simplifield-colombus.iam.gserviceaccount.com”}

Select "Connect". You are now connected to BigQuery and should see your dataset.
You can then select the views you will need for your analysis. The last step of the Power BI BigQuery connector setup is to click “Load” and choose an importing option:
- Import – to bring a copy of the selected dataset into Power BI
- DirectQuery – to set up a live connection to this dataset

Explore your data

Basic analysis:

For basic analysis, you will mainly use the reports tables. They contain all the information of the answers to the checklists you assigned to your stores and users. You will find several versions of the reports table.

REPORTS - Will allow you to perform analysis at the report level. (Ex: You want to have the average score of the reports associated to a specific checklist for a specific store)
REPORTS_SECTIONS - Will allow you to perform analysis at the report section level. (Ex: You want to have the average score for each section of the reports of a specific checklist)
REPORTS_QUESTIONS - Will allow you to perform analysis at the finest level of your reports, the answers. (Ex: You want to create a diagram with the percentage of answers A vs answers B for a specific question of one of your checklists)
REPORTS_QUESTIONS_CLEARED - This is a simplified version of the previous table. You can use it if you do not need to do any join with other tables of your data access.

Advanced analysis:

For advanced analysis, you might need to join several tables. To do that, each table has technical fields (IDs), so you can join them easily. For example, if you need to have the average score of the reports of your checklist "Audit in the morning" for English users only, you will:

Filter your table REPORTS on the CHECKLIST_TITLE attribute with the value "Audit in the morning"
Filter your table USERS on the COUNTRY attribute with the value "English"
Join both tables using the pivot USER_ID
Calculate the average score of all remaining reports

You can find many other tables with data from all your SimpliField favorite features. If you are missing some data for your analysis please let us know !