SpotMe/Onomi Analytics API - Visualizing multiple data sources from the SpotMe Data Platform using PowerBI

This document can only serve as an additional reference guide to the SpotMe/Onomi Analytics API - Visualizing a single data source from the SpotMe Data Platform using PowerBI, which you will need to read first before reading this guide. 

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.

This article provides a step-by-step guide on how to load data from multiple sources from the SpotMe Data Platform into PowerBI desktop, and have them linked/merged to provide more advanced visualizations.

In this particular example, we will load both events and persons data types from an existing SpotMe organization, to build some examples of data visualizations.

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.
• A good understanding of how to visualise a single data source - please refer to the SpotMe/Onomi Analytics API - Visualizing a single data source from the SpotMe Data Platform using PowerBI article.

Step-by-step guide

1. Start PowerBI Desktop, and open a new blank report, or an existing report that you want to add a new data source to.
   In this example, we will re-open the report used in the SpotMe/Onomi Analytics API - Visualizing a single data source from the SpotMe Data Platform using PowerBI article.
   
   Click on Home > New Source > Blank Query to add a new data source to the project:
   
   
   
2. Now, click on Home > Advanced Editor, and then copy/paste in the following piece of Power Query M code into the editor, then click on Done:

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

3. Adding this code and clicking on Done will perform the following:
   

   a. Call the https://api.spotme.com/api/v2/customers/YOUR_ORG_ID/persons?ext=csv&region=eu endpoint in order to retrieve the raw data from the SpotMe source. Here you need to replace YOUR_ORG_ID in the URL with the relevant organization ID.
   b. Interpret it as a CSV table with tab delimiter on the PowerBI side.
   c. Interpret the first line of the CSV table as the column headers.

   More advanced users may be interested in introducing a PowerQuery M function to retrieve data from the SpotMe Data Platform API, as retrieving data for the events, persons or any other data type would follow the same exact pattern.
   
   
4. You should now have some persons data loaded. Click on Home > Close & Apply to go back to the Visualization view:
   
   
   
5. At this point, we need to make sure that a relationship does exist in PowerBI to model the fact that our persons may attend some events. Click on the Model view button in the left panel:
   
   
   
6. Here we can see that PowerBI has detected the relationship, as we can see a connection between the events and persons model:
   
   
   
7. By clicking on this connection, it is possible to see its details and edit it if needed.
   Here, we can see that both tables are linked by the eid property (the event ID which will allow us to identify which persons belong to which events. Please note that more complex relationships may have to be explicitly created if needed.
   
   
   
8. By going back to the visualisation view, we'll add a new column in the table, in order to re-count the number of persons potentially attending a given event based on the raw persons data.
   
   Keep in mind that in this example we had previously added a column populated with the _num_pax property of the events table (i.e. a pre-computed value of the number of persons associated with an event) which may be enough information for a variety of use cases.
   
   To recompute this value based on the raw persons table, we will drag & drop the pid property from the persons table into the visualization and aggregate with a Count operator:
   
   
   
9. In this case, we can note that the count of persons that was re-computed from the raw persons table is higher than the _num_pax value provided with the events table. This is because the _num_pax value filters out the persons that were marked as hidden or cancelled.
   
   If in this visualization we use a filter to unselect the persons in the table that have the is_cancelled or is_hidden property set to true, then both counts now match:
   
   
   
10. Now that we were able to re-compute the number of persons potentially attending an event, we will further filter the data by (as an example) removing any attendees that are from SpotMe.
    
    This is done by adding a filter that will remove from the count all the persons with a @spotme.com email address. We can now see a lower count of persons: