Visualizing Home Assistant data in Power BI

15 Sep 2021

Ahhh, Power BI and Home Assistant. Two of my favorite tools. So, why not try to bring them together? I like the Home Assistant data visualizations, but they are limited - that's where Power BI can really help. But how to bring data from Home Assistant to Power BI? Well, I figured it out, so here's is how.

I chose to use DirectQuery mode in Power BI to avoid having to import a sizable dataset in to Power BI and suffer from delays. I want Power BI to show real-time information from Home Assistant, so DirectQuery is the way forward.

The problem is that the way Home Assistant stores that is not entirely great for Power BI. So, you would have to introduce a layer between Home Assistant and Power BI to present that in a slightly different structure to Power BI compared to how Home Assistant stores it. Alternatively, you could use Power Query to try to transform the data there, but that would not work. A SQL view is the solution here. That leads to the first ingredient: the database. Once you have the database set up you can add views and have Power BI connect to the views.

Step 1: use MariaDB as your Home Assistant Database

By default, Home Assistant leverages a SQLite database. I was unable to connect this to Power BI reliably. Also, I was unable to add a view to the database that provides the data in the structure that Power BI handle. Hence, my advice is: switch to another database. I picked MariaDB. So, first off: run MariaDB as your Home Assistant database. Install the MariaDB add-on following these instructions.

On the configuration page of the MariaDB addon enable port 3306 as the host port and hit save. Then restart the addon.

Restart your Home Assistant environment and make sure you don't get any recorder errors.

Step 2: install PhpMyAdmin addon

For us to create the view, we need to access the database. I chose to install the PhpMyAdmin addon using these instructions.

Step 3: create views

Run the below query on your homeassistant database using PhpMyAdmin. You can also download the query here.

DROP FUNCTION IF EXISTS `IsNumeric`;
DROP VIEW IF EXISTS `states_powerbi`;
DROP VIEW IF EXISTS `entities_powerbi`;

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE VIEW states_powerbi as
select
	a.state_id
    ,a.entity_id
    ,a.state
    ,a.attributes
    ,JSON_EXTRACT(a.attributes, '$.unit_of_measurement') as unit_of_measurement
    ,a.last_changed
    ,a.last_updated
    ,a.created
    ,CASE WHEN a.isnumeric = 1 THEN a.state_numeric else NULL END as state_numeric
from (
    select
        state_id,
        entity_id,
        state,
        attributes,
        last_changed,
        last_updated,
        created,
        IsNumeric(state) as isnumeric,
        CAST(state as float) as state_numeric
    from states
) a;

CREATE VIEW entities_powerbi as
SELECT distinct entity_id, domain FROM states;

Step 4: connect Power BI to your Home Assistant installation

First of all, you will need to make sure you have the MariaDB ODBC driver installed for Power BI to be able to load data from the MariaDB database. Be sure to select the ODBC driver and the appropriate version (64 or 32 bits). Here are direct links, although the version of the driver could be outdated: 64-bit or 32-bit. After installing the driver, open this Power BI file in Power BI Desktop.

Select 'OK' in the security risk pop-up:

You should get a request to enter the local IP of your Home Assistant installation. If not, open the Power Query Editor by choosing Transform Data and change the IPAddress parameter value there.

After entering the IP Address you should be prompted for credentials to connect to the MariaDB. The default username is homeassistant and the password is whatever you set up in step 1.

After this, select Close & Apply to apply these changes.

Step 5: Enjoy

The report provided is just an example report and allows you to search entities and show their values. The top right is an area chart which is great for numerical values such as temperatures. The bottom right is a custom visual built by Jan Pieter Posthuma which is great for entities that are not numerical (such as open/closed or home/away) which you can find here. For more info on this visual please read the documentation available here or look at the issues reported on GitHub.

I hope you enjoy exploring your Home Assistant data using Power BI! Let me know what you think and please share what you produce! I am sure there is way more to do and cool things to build, so curious to hear from you!