Skip to main content

Processing SoSafe data in PowerBI via Power Query M

Updated

Introduction

SoSafe customers are able to retrieve analytical/raw data directly through the SoSafe API, which can also be used through PowerBI.

This guide demonstrates how to use Microsoft Fabric’s Gen 2 Dataflows to retrieve and display data in PowerBI, with examples using the browser version information. It shows how to set up queries for efficient data visualization and future storing through a provided template.

SoSafe PowerBI Template

What is it?

The download is available at the bottom of this article.

SoSafe has built a template that can be easily imported into PowerBI to retrieve and visualize data from the SoSafe API. This template includes basic functionality on how to retrieve data from the API, process it and create Table structures.

Power Query dataflow diagram showing the APIKey, KeyID, and SoSafeHost parameters feeding into the performGetRequest and convertToTable functions, which produce the Users, Campaigns, and CampaignAnalytics tables.

The template only retrieves data within a Gen 2 Dataflow and does not consider further usage into PowerBI reports or data storages.

Using the template

  1. Load it directly into PowerBI by creating a new dataflow and using the file provided above as a model. More information on how to do so in PowerBI can be found here and here.

  2. Add the API Key and Key ID provided through the SoSafe Manager in the two parameters provided by the template: APIKey and KeyID. If you haven’t gotten any of these values, check this guide.

    Power Query parameter panel for entering the API key value, with a tooltip noting it is provided by the SoSafe Manager. Below, an arrow points to the current value field, labelled Valor atual, next to the Gerir parametro button.

  3. Once both values are added, you can then click on the respective tables on the right side of the dataflow to load them one at a time and visualize data:

Power Query dataflow with a tooltip on the Campaigns table describing it as campaign information and analytics. Below, the convertToTable script and a preview table with columns for id, name, description, status, created, and modulesplit.

  1. Done! The data is available for you to visualize and process!

Modifying base requests

The template includes three tables for three pieces of data that the SoSafe API provides:

These endpoints however, contain different kinds of filters for data which can be modified to retrieve different results from the API. Using the Users table as an example, we can modify the query.

  1. Right-click the Users table card and start editing the script.

  2. Based on the documentation found in the SoSafe Open API spec, add the corresponding parameters as such and save the script

    Power Query script for the Users table, with an annotation pointing to the params record where a limit parameter is added to the performGetRequest call. Below, convertToTable defines firstname, lastname, email, and userGroup fields.

  3. Done!

Adding new requests

Adding a new request/table is also quite simple! Before you create your own queries, don’t forget to consult the SoSafe Open API Spec to understand what's possible to retrieve from the API.

  1. Create a new Blank Query and open the script editor.
  2. Inside the let block, make use of the performGetRequest function to retrieve JSON data
  3. Still inside the let block, use the result of performGetRequest to retrieve the list of records and pass it as an argument to the convertToTable function to convert the JSON data to a Power Query Table

  4. Done! You will now be able to see your new query next to all the others!

Custom Functions

The template provides two custom functions that can be used throughout the pipeline to make things easier for you.

performGetRequest

This function will call the SoSafe API directly and retrieve data in the JSON format (example responses can be found on the SoSafe Open API Spec). It will also already handle authentication automatically, based on the credentials provided in the first step, as well as add Accept and Content-Type as default headers.

Additionally, performGetRequest also supports retrieving data in chunks, using multiple requests to retrieve the entirety of the data set in smaller pieces. This will improve performance and allow more flexibility and control towards your needs. For example, if chunkBy and chunkSize are both passed with example values id and 5 respectively, and there are 15 values in the id query parameter, this function will perform 3 requests and return an array of 3 elements containing each response.

This function takes two arguments:

  • path (text): The path of the GET request, for example `/v1/users`
  • options (nullable record): The options to pass down this request, they include:
    • headers (record): A key/value record of all the headers to pass, excluding default ones
    • params (record): A key/value record of parameters to add to this request. Takes values of type text, number and list
    • chunkBy (text): The text field to which chunk values by, provide together with chunkSize and only if the parameter to chunk by is present in params
    • chunkSize (number): The amount of records to request at a time through the API, provide together with chunkBy

Example request with chunked requests:

responses = performGetRequest(
  "/v1/analytics/elearning",
  [
    params = [ campaignId = Table.Column(Campaigns, "id"),
    chunkBy = "campaignId",
    chunkSize = 5
  ]
),

convertToTable

This is a simple function that will convert a given list of records to a Power Query Table. Within the SoSafe API, this means the JSON data of a response retrieved with performGetRequest can be used as argument here. Additionally, it takes an argument to determine what the primary key in the data set to be used in the table is, as well as optional foreign keys. The arguments are as follows:

  • records (list of records): The list of records to convert to a table
  • types (list of lists): A list with the name of the fields and their respective Power Query types. This will convert the types from the response into proper table types. For example:
    • {
  { "id", Int64.Type },
  { "name", Text.Type }
}
  • primaryKey(text): The name of the field that holds the primary key for this table
  • foreignKey (nullable list): An optional list that can be passed with all the names of all the fields that are supposed to be foreign keys

Example conversion dealing with chunked requests:

table = convertToTable(
  // convert all responses to a list of records containing the correct data
  List.Accumulate(
    responses,
    {},
    (acc, current) => List.Combine({ acc, current[data] })
  ),
  // assign types
  {
    { "customerId", Int64.Type },
    { "campaignId", Int64.Type },
    { "campaignName", Text.Type },
    { "firstname", Text.Type },
    { "lastname", Text.Type },
    { "email", Text.Type },
    { "language", Text.Type },
    { "userGroup", Text.Type },
    { "userCreated", Date.Type },
    { "registrationDate", Date.Type },
    { "isActive", Logical.Type },
    { "progress", Percentage.Type },
    { "achievedLevel", Percentage.Type },
    { "allPassed", Text.Type },
    { "passedModules", Int64.Type },
    { "overdueMandatoryModules", Int64.Type },
    { "passedMandatoryModules", Int64.Type },
    { "passedOptionalModules", Int64.Type },
    { "recentlyFinishedModule", Date.Type }
  },
  // assign primary key
  "email"
)

Related articles