Verarbeitung von SoSafe-Daten in PowerBI via Power Query M

Dieser Artikel wird bald auch auf Deutsch verfügbar sein!

Introduction

SoSafe customers are able to retrieve analytical/raw data directly from us through the Power BI Integration. However, to provide higher reliability and flexibility, the SoSafe API 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?

Download here: SoSafe API Power Query Dataflow.pqt

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.

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

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.
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.
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:

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:

Users: https://support.sosafe.de/ADOC/sosafe-api#SoSafeAPI-Usersendpoint
Campaigns: https://support.sosafe.de/ADOC/sosafe-api#SoSafeAPI-Campaignsendpoint
Campaign Analytics: https://support.sosafe.de/ADOC/sosafe-api#SoSafeAPI-Analyticsendpoint

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.

Right-click the Users table card and start editing the script.
Based on the documentation found in the SoSafe Open API spec, add the corresponding parameters as such and save the script
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.

Create a new Blank Query and open the script editor.
Inside the let block, make use of the performGetRequest function to retrieve JSON data
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
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:

CODE

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:
- CODE
```
{
  { "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:

CODE

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"
)