Power BI

The IoT platform's Power BI Custom Connector enables a user, in a fast and easy way, to get data directly from an account on the IoT platform into Power BI Desktop for advanced analysis. Creating reports like the ones below takes just minutes after a little training.

After exporting the reports to the Power BI cloud and installing a Power BI Enterprise gateway with access to the IoT platform's connector, they can also be automatically refreshed and shared. However, using the Power BI cloud is not a requirement - Power BI Desktop alone works well.

---

1. Get started

1.1 Installation

To install, you must use a Windows computer (Power BI is not supported on Mac or Linux) and be a local administrator (or have an IT policy that allows the needed components to be installed by users).

1. Download and install Power BI Desktop from Microsoft: https://docs.microsoft.com/en-us/power-bi/fundamentals/desktop-get-the-desktop

2. Download the IoT platform's Power BI Custom Connector zip from the platform's Docs / Power BI (or wherever IT instructs) and extract the two files: YggioConnector.pqx and Yggio_regkey.reg.

   1. Place YggioConnector.pqx in this folder (create it if it does not exist - use exactly this spelling): [Documents] > Power BI Desktop > Custom Connectors (on English Windows: C:\Users\<you>\Documents\Power BI Desktop\Custom Connectors)

   2. Double-click Yggio_regkey.reg and accept that it modifies the computer. This installs Sensative's certificate thumbprint in the registry and lets Power BI use the connector without changing Power BI's security settings.

      If the thumbprint is not installed, Power BI will complain that it cannot validate the IoT platform's connector. To use it anyway, lower the security setting via File → Options and Settings → Global → Security → Data Extensions → (Not recommended) Allow any extension to load without validation or warning.

3. Restart Power BI Desktop. To confirm the install worked, open Get Data → More… and search for Yggio - the connector should appear.

1.2 Limitations

This document covers the functionality of the IoT platform's Power BI Custom Connector. It does not (beyond a few hints) describe general Power BI usage, which is documented at https://docs.microsoft.com/en-us/power-bi/.

---

Quick start: your first chart

Once the connector is installed, this is the shortest path to a working chart:

1. Get Data → More… → Yggio → Connect.
2. Enter the URL of your IoT platform server and sign in (username + password, or an API key token).
3. Choose the Chart query, pick up to 8 devices and up to 4 measurements (e.g. temperature), and click Apply to preview.
4. Set the three parameters that decide what you get: Time period (e.g. last 7 days), Resolution (e.g. every hour), and Value function (e.g. mean).
5. Tick the query and click Load.
6. In the report view, change the time column's type from datetime to time (see the tip below) - this is the most common thing people miss.
7. Add a Line chart, drag the time column to the axis and your measurement to values. Done.

Everything below explains each of these steps and the more advanced options in detail.

---

2. Overview of functionality

2.1 Fundamentals - three ways to connect

The connector offers three ways to connect to the IoT platform and extract data, each with a different balance of ease-of-use versus how much data you can retrieve:

1. Navigator - Get Data → search "Yggio" → connect to a server. The most guided, easiest way to start. Time-series charts are limited to 8 nodes × 4 time-series fields, chosen from a predefined list or entered manually. Works well for most use cases.
2. Formula bar (guided) - Get Data → Blank query → type =Yggio and pick a function from the drop-down. Opens a 2–3 step guide that removes some Navigator limits. For charts, the 3 first time-series fields come from the selected nodes and the 4th is from the predefined list.
3. Free query in formula bar - Get Data → Blank query → type =Yggio and write the full query yourself. Using lists you can go beyond 8 nodes × 4 fields - all constraints are removed. You need to know the query syntax, node names, options and field names (all documented inline in Power BI). Use this to (a) retrieve time series from more than 8 nodes, or (b) use PowerQuery M-language for complex data sets.

A good way to learn the free-query syntax is to first build a query with the Navigator or the guided formula bar, then modify the resulting query.

For very advanced users, the IoT platform's queries can be used from Power BI's advanced editor (PowerQuery) to build complex queries aggregating data from different sources.

2.2 Features (connector functions)

Pick a function by goal:

I want to…	Use
Chart measurements over time	Yggio.Chart
Build a building energy-efficiency KPI	Yggio.ChartEnergy
See which measurements a device has	Yggio.ChartFields
Compare current values of several devices	Yggio.IotNode (columns) or Yggio.IotNodes (rows)
List devices and when they last reported	Yggio.Overview
See which translators (decoders) exist	Device Models

Full list:

Function	Returns
Yggio.Chart	A cube of general time-series data (X nodes × Y fields × Z time). Optional extra statistical columns and value functions (mean, max, min, first, last, count, sum, difference) for fast creation of advanced charts and reports (e.g. billing reports).
Yggio.ChartEnergy	Time-series data for a building's energy-efficiency KPI, based on energy consumption, outdoor temperature, average indoor temperature and building area.
Yggio.ChartFields	The available time-series fields for the selected nodes.
Device Models	The available device decoders - "translators" in the IoT platform's terminology (they usually also normalize the data format to the FIWARE standard, not just decode).
Yggio.IotNode	Current data for several IoT nodes formatted in columns, to make them easy to compare.
Yggio.IotNodes	Current data for several IoT nodes formatted in rows, to increase visibility of available data.
Yggio.Main	Opens the Navigator main menu in table format. Not normally used from the formula bar.
Yggio.Overview	An overview of the devices in an account and when they last reported.

Field names follow the IoT platform's canonical data model (translators normalize vendor data to a FIWARE-aligned model), so the same field means the same thing across every device - see the field table in the developer Translator API reference.

2.3 Connecting to the IoT platform

After choosing "Get Data" (or starting a query for the IoT platform from the formula bar), select the Yggio connector, then enter the URL of the required IoT platform server.

Next, enter credentials to log in - either your username and password, or an API key token. These are stored in Power BI's credential list and reused the next time you connect to the same server. The IoT platform's API keys expire after a while, so update them periodically. To log into a different account, clear the server's stored credentials via File → Options & Settings → Data source settings.

Use an API key token on shared or reporting machines so no personal password is stored (remember it will expire); username/password is convenient on your own machine.

To make switching between servers and accounts easy, the connector uses a list of predefined server short names plus a user-defined tag. For example, BY resolves to https://beta.yggio.net, and BY+demo stores credentials under the data-source path BY+demo - so BY+test can be used as a different path to log into another account on the same server. Some reference short names:

• PY = https://public.yggio.net
• EU = https://yggio.eu
• BY = https://beta.yggio.net
• Y3B = https://yggio3-beta.sensative.net
• YG = https://ygg.io

The short name plus tags let a data scientist / report professional who manages several accounts on a server quickly switch between them. If you get an error when Power BI tries to fetch data, the server URL entered could not be found - enter a valid server URL or short name.

2.4 Ease of use

The connector is forgiving in its syntax: it tries to interpret what you want. Exact names are not required - e.g. Temp retrieves the first time-series field that (lower-cased) starts with temp; a time period 2 m retrieves the last two months. Inside Power BI the full syntax and options are documented in detail, so even writing queries for the IoT platform in the advanced editor is relatively easy.

2.5 Multi-language support

The connector is multilingual and follows the language of the Power BI installation. It currently supports English and Swedish.

---

3. Use the connector to extract data

3.1 Navigator interface and parameters

In Power BI, click Get Data and search for Yggio.

After Connect, enter your credentials in the connector's Navigator UI, select the desired query, and follow the options.

By default, the preview window (bottom right) populates with available IoT nodes and time-series fields after clicking Apply - very helpful for confirming you get the right data before loading it into Power BI.

The Chart query has important optional parameters:

• Time period - the range of data to extract.
• Resolution - the data resolution. The IoT platform returns at most 1000 data points per time-series field.
  • Every hour / day / week / month / quarter - one data point per bucket.
  • Full resolution - every data point (the 1000-point limit caps long ranges on high-frequency devices).
  • High resolution - once a minimum distance between points is exceeded, tries to retrieve the maximum 1000 points for the period.
  • Low resolution - like high, but with a longer minimum distance between points.
• Enable calculation of utilization via extra date columns - adds columns grouping data by hour of day, day of week, week number, etc., which greatly simplifies grouped reports.
• Value function - the calculation applied per resolution bucket: mean, min, max, first, last, count, sum, difference. For example, Resolution = every day with Value function = max gives the daily maximum; difference on a cumulative meter gives the consumption per day.

Billing / IMD report (Individuell mätning och debitering) tip: Time period last 3 months, Resolution every day, Extra date columns true, Value function difference - then sum the delta values per month with Power BI's standard transform and report functions.

Chart Energy Efficiency

The most advanced feature, Yggio.ChartEnergy, calculates an energy-efficiency KPI:

Energy used per time unit ÷ (Area × (1 + |avg. indoor − outdoor temp|))

You supply the inputs the KPI needs: the energy series (consumption), the outdoor temperature series, the indoor temperature series (averaged), and the building area. The connector returns the KPI as a time series you can chart.

This is the same calculation as the analytics-building-energy-efficiency translator. Use Yggio.ChartEnergy to compute the KPI on the fly in Power BI; use the translator instead when you want it computed inside the platform and stored on the device (so it's available to alarms, dashboards and other tools).

If you improve the building (new windows, more efficient heating, etc.), the energy-efficiency KPI bars should become lower.

Mark the checkbox next to a query and click Load to load the data into Power BI for analysis and reporting.

3.2 The formula bar

Open the formula bar via the small down-arrow below Get Data → Blank query. A Power Query editor opens with the cursor in the formula bar. Type =Yggio to see the drop-down of queries; select one and press return to reveal its UI, documentation and full syntax. The queries differ in syntax and required parameters but share the same style, so they become familiar quickly. (Yggio.Main is the Navigator specification - no purpose from the formula bar.)

The first parameter in every query is the IoT platform server; you can enter more parameters in the first step, but it's always fine to just enter the server plus credential tag and click Invoke.

After Invoke and credentials, Power BI logs in and shows a UI to fill the remaining parameters, with all available nodes pre-populated in the dropdowns.

In step 2 (depending on the query) you may choose to expand nested JSON structures such as the raw data and the Context Map (metadata). Click Invoke again to load the data into the Power Query editor, where you can transform it before Close & Apply.

The benefit of the formula bar over the Navigator is that for Yggio.Chart / Yggio.ChartEnergy the available time-series fields per node name get pre-populated for the first 3 of 8 nodes (the 4th field remains the predefined list for the last 5 nodes).

3.3 Directly enter the full query in the formula bar

Besides automation from PowerQuery, the main reason to write the full query directly is to avoid the node/field-count limits. The node-name and field-name parameters accept lists - {"par1", "par2", "par3", ...} - letting you go beyond the UI limits. Power BI provides syntax help while writing the parameters.

---

4. Power BI hints and sample reports

Once a data set is retrieved, click Close & Apply in the Power Query editor to load it, then switch to the report editor (top-left graph icon).

The most important first step: change the datetime column's type from datetime to time. Otherwise Power BI groups all the data and most graph widgets won't work as expected.

Tip - shape data before loading. Choose Transform Data (instead of Load) to open Power Query first, where you set the time column's type, rename fields and filter rows, then Close & Apply. Load just brings the data in with the connector's defaults - you can still adjust it afterwards.

Tip - grouping over time. For reports that group by hour / day / week / month, enable the Chart query's extra date columns option (Section 3.1); it adds ready-made grouping columns, so you don't have to build date logic in Power BI yourself.

Then start with basic widgets - Line chart, Column chart, Pie chart - and experiment by dragging time and data fields to their fields. Each widget usually lets you choose how to combine values in the same group (sum, average, etc.), depending on what you want to visualize.

---

Refresh and sharing

• Power BI Desktop refreshes on demand - click Refresh, or re-invoke the query.

• Scheduled refresh and sharing (publishing to the Power BI Service) require an on-premises data gateway in standard (enterprise) mode - the personal gateway does not support custom connectors. Set it up once:

  1. On the gateway machine, install the connector the same way as on the desktop - put YggioConnector.pqx in that machine's Custom Connectors folder and run Yggio_regkey.reg.
  2. In the gateway configuration, allow custom data connectors.
  3. Store the IoT platform credentials (username/password or API key) on the gateway's data source.

  Using the Power BI cloud is optional - Power BI Desktop on its own works well.

---

Downloads

• Yggio Power BI Connector (zip, v1.1.1) - contains YggioConnector.pqx + Yggio_regkey.reg.