SpotMe/Onomi Analytics API - Visualizing a single data source from the SpotMe Data Platform using PowerBI – SpotMe knowledge base

In this article you will find a step-by-step guide on how to build a basic data visualisation on PowerBI desktop, using data from a single source, via the SpotMe Data Platform.

The data used in this example is of the events type, and pulled from an existing SpotMe organization.

To find out how you can load data from multiple sources from the SpotMe Data Platform into PowerBI desktop, and have them linked/merged to provide more advanced visualizations, please refer to the SpotMe/Onomi Analytics API - Visualizing multiple data sources from the SpotMe Data Platform using PowerBI.

To see a sample dashboard of how you can combine data into more a more sophisticated dashboard, please refer to the Example of data visualization using Power BI article.

What you will need

A SpotMe Backstage user, with the following necessary roles:
- An API developer role for the given organization ("account_api_developer"), if cross event reporting for a single organization is needed. More information
- An API developer role for the given customer ("customer_api_developer") role for the given customer, if cross organization/event reporting is needed. Please contact your SpotMe Account Manager to enable this role.
A valid API token (more information on how to obtain an API token).
The organization ID for cross workspace reporting within the same organization, that you can find in the Organization > Details section in Backstage, under Account ID)
OR
The customer ID, for cross organization/event reporting. Please contact your SpotMe Account Manager to get this ID.
PowerBI Desktop installed.
(Optionally) A M365 account with PowerBI access to publish the report if needed.

Step-by-step guide

Start PowerBI Desktop and open a new blank report:
Click on Home > Get data > Blank query:
Click on Home > Advanced Editor:
Copy/paste the following piece of Power Query M code in the editor, then click on Done:
```
let

events = Table.PromoteHeaders(
Csv.Document(
Web.Contents(
"https://api.spotme.com",
[
RelativePath = "/api/v2/orgs/YOUR_ORG_ID/data/events",
Query = [ext = "csv"],
ApiKeyName = "access_token"
]
), [Delimiter="#(tab)", Encoding=1252, QuoteStyle=QuoteStyle.None]
), [PromoteAllScalars=true]
)

in

events
```
This code does the following:
a. Calls the https://api.spotme.com/api/v2/customers/YOUR_ORG_ID/events?ext=csv endpoint, in order to retrieve the raw data from the SpotMe source. Here you need to replace the "YOUR_ORG_ID" in the URL with the relevant organization ID.
b. Interprets it as a CSV table with tab delimiter on the PowerBI side.
c. Interprets the first line of the CSV table as the column headers.
Next, click on Done.
You will see a warning message asking you to specify how to connect. Click on Edit credentials:
In the Access web content screen, click on Web API.
Paste your API token in the Key field, and then click on Connect:
Your events data should now be visible as a raw data table:
To better label and identify the data, you may wish to rename your data, as it will be named "Query1".
This can be done by editing the Name field that is located in the PROPERTIES, located in the panel on the right of the screen. Here we have renamed it as "events":
You can then click on Home > Close & Apply.
You will be redirected to your report view. Here you can add a pie chart visualisation, by clicking on the Pie chart icon on the right side of the screen, in the Visualizations panel:
In the Data panel on the right side of the screen, drag and drop the data you wish to add to the pie chart.
Here we have added the eid, as well as an event property (category_2) into the Legend box, in order to create a simple pie chart visualization of the number of events per category:

Enforcing data types and introducing custom data columns

By default, when loading CSV data into PowerBI, its columns will be interpreted as text data, even if they contain integer or floating-point numbers.

Similarly, you will by default get UNIX timestamp values for dates, when it is often more convenient to have the PowerBI date type (in particular for displaying or grouping based on dates).

Finally in some cases, you may want to introduce extra columns to your data set, by processing/combining other existing columns.

These types of operations can easily be performed as part of your data query in the Advanced editor.

Let's look at this by re-using the previous example. Click on Home > Advanced Editor, and select the events query. Now, click on Advanced Editor and copy and paste in the following code snippet:

let
events = Table.PromoteHeaders(
Csv.Document(
Web.Contents(
"https://api.spotme.com",
[
RelativePath = "/api/v2/orgs/SpotMe%20Marketing/data/events",
Query = [ext = "csv"],
ApiKeyName = "access_token"
]
), [Delimiter="#(tab)", Encoding=1252, QuoteStyle=QuoteStyle.None]
), [PromoteAllScalars=true]
),

// Enrich data / convert needed columns to PowerBI types
events1 = Table.AddColumn(events, "URL", each Text.Combine({"https://backstage.spotme.com/event/", [eid]}), type text),
events2 = Table.AddColumn(events1, "start_date", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Number.FromText([start_at])), type datetime),
events3 = Table.AddColumn(events2, "end_date", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Number.FromText([end_at])), type datetime),
events4 = Table.AddColumn(events3, "onomi_tag", each Text.Replace([category_2], "onomi-tag_", ""), type text),
events5 = Table.TransformColumnTypes(
events4, {
{"_num_pax", Int64.Type},
{"_num_pax_activated", Int64.Type}
})

in 
events5

This will look as follows in the Advanced editor:

The extra code added to the snippet, beneath the "// Enrich data / convert needed columns to PowerBI types" text, will do the following:

Add a new URL column, giving you a direct link to your events on Backstage.
Populate the start and end dates columns in the PowerBI data format, instead of using UNIX timestamps.
Add a new onomi_tag column, that is created by performing some basic string processing on the category_2 column.
Ensure that some columns will be considered as integer numbers, so that they can be conveniently aggregated in visualizations.

Clicking on Done in the Advanced editor will perform these changes. The new columns and new data types will now appear in the events raw data:

Once added to the raw data, the new columns can now be used to create visualizations.

In the below example you can see a simple table view. To note, because the number of persons is now rightfully interpreted as a number (integer), it is now possible to get a total number of persons ("Sum of num pax") in the table below:

In this article

What you will need

Step-by-step guide

Enforcing data types and introducing custom data columns

Related articles