How to connect a Matomo database to Microsoft Power BI

To analyse Matomo data in Microsoft Power BI, Matomo On-Premise users can connect directly to their Matomo database through Power BI’s built-in MySQL connector. This method mirrors the data that you see in Matomo.

An alternative method for Matomo Cloud and On-Premise users is to import data using the Reporting API. This returns pre-formatted datasets whereas a direct database connection lets you build custom queries, combine Matomo data with other sources, and create advanced visualisations in Power BI.

Note: Direct database access is only available with Matomo On-Premise. If you are using Matomo Cloud (or On-Premise), follow the guide on How to import data into Microsoft Power BI Desktop.

Before you start

Make sure you have the following in place before connecting Power BI to Matomo:

• Database credentials: You will need the hostname or IP address, database name, username, and password for your Matomo MySQL/MariaDB database. Use read-only permissions where possible for security.
• Remote access permissions: Matomo servers typically restrict database access for security, so your computer’s IP address must be whitelisted on the database server to allow external connections. To connect successfully:
  • Determine your public IP address (go to What is my IP Address to check).
  • Add this IP to the list of allowed IPs on your Matomo server or firewall configuration.
• Power BI Desktop: Install the desktop application with the MySQL connector enabled. If this is the first time connecting to a MySQL database, Power BI may prompt you to install additional components when trying to connect to the database. Follow the Microsoft instructions, install the required drivers, and restart Power BI if necessary.

Connect to Power BI

1. Launch Power BI and select Get data in the Home ribbon.
2. Search for and choose MySQL database from the list of data sources.
3. Click Connect.
4. Enter your MySQL database connection details: the Server address and Database name.
5. If required, use the Advanced options to enter a custom SQL query (e.g. to return daily visit counts).
6. After connecting, Power BI will execute your SQL query and display a preview of the results.
7. Select Load to import the data into your Power BI workspace.
8. Choose the columns to be included from the imported dataset (1), select the type of Visualisation and customise the Columns display order (2), setup data Filters (3), and view the customised format of data on the page canvas (4).
   

Time zone differences between your server and Power BI may cause small discrepancies (e.g. a visit appearing on the following day). This is typically due to differing local times and does not indicate a data issue.

Once connected, Matomo data is available in Power BI like any other data source. You can use Power BI’s visualisation tools to build charts, reports, and dashboards that combine Matomo metrics with data from other systems.