Use Case

How to integrate Bazefield data with Power BI.  Prior to this article it is recommended to learn about the Bazefield API, which is the data source for Power BI. It is highly suggested to learn about the Bazefield API prior to trying to integrate Power BI.  



Background

Why would I want to pull Bazefield data into another reporting solution?

  • Combine Bazefield data with organizational data stored in other databases or files.
  • Take advantage of advanced visual reporting tools like Power BI, Tableau, and Spotfire


Prerequisites

Requires access to the Bazefield API.  See Getting Started with the Bazefield API.  This also assumes the user already has an account and desktop application of Microsoft Power BI.  (https://powerbi.microsoft.com/en-us/ )


Steps


Step 1: Connect Power BI to Bazefield API


Open Power BI Desktop and navigate to Get Data. Choose the "Web" data source under "Other".


Then, setup the API query. In this example, we will use the GetAllocations API call to download raw Availability Allocations. 


You will need:

  • Base url: https://<YourBazefieldDeploymentHere>.bazefield.com/Bazefield.Services/api/
  • Controller component of url: in this case, it is allocations/{AllocationId}
  • Query parameters: in this case, we use the From, To and TurbineIds parameters
  • HTTP Authorization header: Bearer <YourAPIKeyHere>


Step 2: Select which data to import

When no Accept header is provided for the API request, Power BI returns a full HTML document with HTML tables. For your particular use case, you may only need a subset of the data. In this case, we will use the Power BI Data Source Navigator to import only Table0.


Nested columns with subrecords can be expanded in Power BI to denormalize the data into one table. In this example, we expand the Allocations, Alarm and Category columns.



Step 3: Finalize the Data Source


In the query view, select "Detect Data Type" to automatically convert all fields from text to their data types.

All the transformation of the source data from Bazefield is available in the Query Settings sidebar. Finalize any changes and select "Close & Apply".



Step 4: Analyze your data in Power BI!

All the fields from the prepared data source are available in the Power BI dashboard environment.


Additional Example (more complicated)

This example works through how to retrieve and format timeseries data for multiple tags. This nested time-series data has a very different data shape than the fully tabular form required by Power BI, so it is not always obvious which data transformations to use.


Step 1: Bazefield API Query

We will use the measurements/timeseries/aggregated endpoint to return hourly timeaveraged data for year-to-date for multiple tags. Our url looks like https://bazefieldFQDN/Bazefield.Services/api/measurements/timeseries/aggregated?Keys=Tag1,Tag2&Aggregates=4,4&Interval=3600000&From=*y&To=*


This time, we will add an Accept header in order to return data in JSON format instead of HTML tables. This is necessary because we need to have the hierarchical context of the JSON in order to know which timeseries data corresponds to which tag. The header should look like:

  • Accept: application/json


The returned data is nested and not tabular. We will need to transform this into a denormalized tabular form for use in Power BI.


{
    "timeSeriesList": [
        {
            "measurementId": TagIdForTag1,
            "measurementName": Tag1,
            "unit": UnitForTag1,
            "aggregateType": 4,
            "timeSeries": [
                {
                    "t": 1585717200000,
                    "q": 524480,
                    "v": 240.672079626842,
                    "t_local": "2020-04-01T00:00:00.0000000-05:00"
                },
                {
                         more timeseries data
                }
              ]
        },
        {
                 data for Tag 2
        }
    ],
    "startTime": 1585717200000,
    "endTime": 1585761310211.55,
    "replyInfo": {
        "rcvTime": 1585761310211.55,
        "replyTime": 1585761310227.17
    }
}


Step 2: After querying in Power BI, adapt the data source

Start by performing a Navigate down the json object into the timeSeriesList array (we don't need any of the other data).


After the Navigate, we have a List with 2 objects (1 for each tag). We need to turn the list into a table before continuing. Use None as the table delimiter.


This gives a table of 2 records, which correspond to the 2 objects in the TimeSeriesList array in the json. We can then Expand each record, taking the object properties that interest us from the Json (in this case only tag name and the timeseries data).

This gives us a table with the 2 tagnames, and the timeseries data is still in a list that we will need to expand.

We will Expand the timeseries data to New Rows (this denormalizes the data, making a new record for every single timeseries data point).

However, each timeseries record is an object with multiple properties, so we will need to Expand that to new columns as well.

Great! Now we have denormalized all the data and represented it in a tabular format. You may also wish to pivot the data so that each tag value is a column.



Now we have a nicely formatted, pivoted data set to work with in Power BI.





Product Environment and Version


Bazefield API

Bazefield version 8.0.14.4