My design considerations when developing Power BI datasets for smart buildings
To connect #PowerBI to the smart building platform from Siemens Infrastructure , we developed a Power BI template that contains all the necessary logic to interact with APIs and populate a set of standard tables used to create dashboards. This post tells you more about our development approach.
Why Power BI?
With 微软 Power BI, you can create and publish highly customizable and interactive dashboards with rich data integration capabilities. Power BI's flexibility makes it ideal for organizations with diverse reporting needs, and it offers advanced features such as machine learning integration and granular security. Power BI allows you to combine data sources to provide cross-data insights for informed decision making. Building X serves as one such data source.
Power Query M - Power BI's code language
Since I'm quite new to the Power Query M language, it's been a great journey to learn this language and define an architecture that is maintainable and provides reusable building blocks (e.g. custom queries). I decided to write custom queries as functions, as if I were using a regular programming language. Just write small and reusable function blocks that are easy to maintain, document, understand, and test.
Because Building X adheres to the JSON:API standard (link), it requires support for pagination to retrieve a full list of entries such as locations, devices, point and time series. To achieve this, I implemented a pagination mechanism that uses recursive calls. This recursive approach keeps calling the endpoint until no "JSON:API links { next }" reference is found in the HTTP response. An essential aspect of recursive calls is the use of the @ prefix before calling the function, which allows Power Query to execute functions recursively.
The following code gets the entire content of a JSON:API endpoint.
//////////////////////////////////////////////////////////////////////////
// Summary:
// This custom function retrieves data from an Openness API endpoint,
// making multiple API requests to obtain all pages of data.
// Input parameters:
// relativeApiPath: The relative API path to the endpoint.
// apiToken: The API token for authentication.
// query (optional): Additional query parameters to include in the API request.
//////////////////////////////////////////////////////////////////////////
JsonApi_GetPagesByPath = (relativeApiPath as text, apiToken as text, query as record) as list =>
let
// Define a function to retrieve a page of data with headers
GetJsonPage = (relativePath as text, query as record) =>
let
// Define custom headers, including the Authorization header for token authentication
headers = [
#"Authorization" = "Bearer " & apiToken,
#"Accept" = "application/vnd.api+json"
],
// Define request options
requestOptions = [
Headers = headers,
RelativePath = relativePath,
Query = query,
Timeout = #duration(0, 2, 0, 0),
ManualStatusHandling = {400, 401, 403},
IsRetry = false
],
// Make the API request with headers
response = Web.Contents(BuildingXBaseUrl, requestOptions),
response_metadata = Value.Metadata(response),
status_code = response_metadata[Response.Status]
in
if status_code = 200 then Json.Document(response) else null,
// Define a recursive function to retrieve all pages of data
GetJsonPages = (relativePath as text, dataRowsList as list, query as record) as list =>
let
// Make the API request for the current page
jsonResponse = GetJsonPage(relativePath, query),
// Extract the data from the source
newDataRows = jsonResponse[data]? ?? {},
// Check if there is a next page in the API response
nextPage = jsonResponse[links]?[next]?,
// Concatenate the current page's data to the accumulated data
combinedRows = dataRowsList & newDataRows
in
// If there is a next page, recursively call the function for the next page
if nextPage <> null and Text.Lower(BuildingXBaseUrl) = Text.Lower(Text.Start(nextPage, Text.Length(BuildingXBaseUrl))) then
// Remove the first part to convert the absolute URL to a relative URL
@GetJsonPages(Text.RemoveRange(nextPage, 0, Text.Length(BuildingXBaseUrl)), combinedRows, [])
else
// If there is no next page, return the accumulated data
combinedRows,
// Call the recursive function to retrieve all data, starting at page 1
dataRows = GetJsonPages(relativeApiPath, {}, query)
in
List.Buffer(dataRows);
Throughout my experience, I have discovered that Power BI excels at processing extensive lists but encounters challenges when tasked with building tables that necessitate a substantial number of API calls for population. Unfortunately, Power BI lacks the ability to parallel process API calls, but luckily Web.Contents() does cache responses for the same URI.
From Power BI list to table
We've established a standardized approach by creating both "Get<entity>List" and "Get<entity>Table" functions for each endpoint. This strategy allows for efficient reusability of "Get<entity>List" functions whenever the API endpoint needs to be called based on the owner entity.
For example, consider the scenario where we need to retrieve point values. The points/{pointid}/values endpoint needs to be called for each specific point. In this context, the GetPointValuesList function first uses the GetPointList function to call the points/{pointid}/values endpoint individually for each point. Then, the "GetPointValuesTable" function seamlessly converts the resulting list into a predefined tabel structure.
?? The good news for you is that we have already implemented all of these features, so you can start using the pre-built tables to create the dashboards your organization needs to gain insight.
The dataset hides the complexity of API interaction and provides standardized tables for use in your dashboards.
领英推荐
Optimize for building data point timeseries
Within Building X, tags serve as identifiers for locations and points. To significantly reduce dashboard refresh times, it is important to filter locations and points at the API level to ensure that only the data relevant to the dashboards is retrieved. To accomplish this, I introduced Power BI parameters that allow multiple tags and tag values to be specified for server-side filtering. This enhancement streamlines data retrieval and improves overall performance.
Future of (PowerBI & BuildingX)
Our next step is to optimize our Openness APIs for BI purposes and I would love to learn from your use cases. We will also provide energy tables so you can use energy consumption and aggregations in your dashboards.
We are also working on a Building X Connector for Power BI to make integration even easier.
?? If you're interested in using Power BI to gain insight into your building performance, contact me so we can work together to achieve your goals.
Learn more about Operations Dataset for Power BI
The Power BI Operations Dataset is available through the API Manager app and the Operations API subscription.
?? Learn more about the Power BI Operations Dataset for Building X on our Developer Portal.
Learn more about Building X
Building X is a scalable digital building platform to digitalize, manage and optimize building operations, allowing for enhanced user experience, increased performance and improved sustainability.
?? Learn more about Building X on our website.