Verarbeitung von SoSafe-Daten in PowerBI via Power Query M

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?

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.

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.

   

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:

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

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

   

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.

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

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?

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.

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.

   

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:

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

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

   

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.

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