Data preparation and Visualization - Advanced Analytics API

Users of AG5 Advanced Analytics API are free to use any tool available to analyze the data.

Data preparation tools should provide the following functionalities:

• Extract and Process JSON data (or for some cases: CSV format)

• Create Parent-Child hierarchies (to aggregate data)

• Detect and change data types

• And more...

Data visualization tools would usually provide the following functionality:

• Create relationships between tables (the data is based on a relational database model)

• Different visualizations and filters

• Option to drill down into hierarchical data.

• And more...

In this article we go into a bit more detail about the use of Microsoft Power BI (in combination with Power Query). We also list some other tools. AG5 Advanced analytics does not include consultancy on the use of these different tools. We do, of course, help you to understand the data structure.

Power Query and Power BI

Connecting to the Data source

REST API

Note: it is possible to connect the API to Power BI directly. However, because of the large number of records, and the inability of Power BI to deal with pagination (see below), we recommend to ingest the API data into your local data storage first.

The direct connection method can be useful for small volumes of data, or for exploration and is described below:

• Choose the option 'Web' followed by 'Advanced'
• Add the url with the correct parts (parameters) (see OpenAPI)
• Add the following to the request header:

  • Authorization: Bearer <The provided token>

  • Accept: text/csv (optional).

• The Entity requests work with pagination. The response header returns info for the pagination. Since Power BI does not give access to the response headers, we advise to use the ‘Accept: text/csv’ request header. The csv option gives the full data set, without pagination.

• After adding the correct URL parts and Header parameters, in the next Window, select ‘Anonymous’ as authentication method to access the Web Content.

Data preparation with Power Query

Depending on the connection method, you will need to do some data preparation. Some common actions to take:

• Fields that contain JSON data will need to be parsed and then expanded.

• Date fields are usually records with day, month, year and will need to be expanded and then merged to create a valid date type field.

• Fields that contain a time period will need to be expanded.

• Some fields (like in the Grouping table: ui_name_json) contain localized data (a different name for each language). These are stored in JSON so they need to be parsed and expanded.

• Create parent-child hierarchies (for example for grouping and parent grouping). The following article details the creation of these hierarchies.

`Other tools

Other tools you can use for data preparation and or visualization are:

• Tableau Prep and Tableau

• Google Looker Studio: Visualizations only

• Alteryx Trifacta

• Microsoft Excel (with Power Query): Data preparation only