You can use Power BI to create dashboards based on the data maintained in Materialize.
Database connection details
To set up a connection from Power BI to Materialize, use the native PostgreSQL database driver with the following parameters:
|Database type||PostgreSQL database|
|Server||Your Materialize host name followed by
|Data Connectivity mode||DirectQuery|
|Database username||Materialize user.|
|Database password||App-specific password.|
Errors like the following indicate that there is a problem with the connection settings:
A non-recoverable error happened during a database lookup.
If you see this error, check that the server name is correct and that you are using port
6875. Note that the server name should not include the protocol (
https://), and should not include a trailing slash (
/) or the database name.
PostgreSQL: No password has been provided but the backend requires one (in cleartext)
If you see this error, check that you have entered the correct password. If you are using an app-specific password, make sure that you have not included any spaces or other characters that are not part of the password. If the issue persists, try the following:
- Go to File
- Options and settings
- Data source settings
- Delete any references for the Materialize host from the data source list
- Try to connect again
For more details and troubleshooting, check the Power BI documentation.
When you connect to Materialize from Power BI, you will get a list of your tables and views.
However, Power BI does not display materialized views in that list.
To work around this Power BI limitation, you can use one of the following options:
Create a view that selects from the materialized view, and then use the view in Power BI.
For example, if you have a materialized view called
my_view, you can create a view called
my_view_biwith the following SQL:
CREATE VIEW my_view_bi AS SELECT * FROM my_view;
Then, in Power BI, you can use the
If applicable, instead of using a materialized view, create a view with an index instead.
Use the Power BI Native query folding to write your own query rather than using the Power BI UI. For example:
= Value.NativeQuery(Source, "select * from my_view;")