Power BI

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:

Field	Value
Database type	PostgreSQL database
Server	Your Materialize host name followed by :6875 For example: id.us-east-1.aws.materialize.cloud:6875
Database	materialize
Data Connectivity mode	DirectQuery
Database username	Materialize user.
Database password	App-specific password.

Troubleshooting

Errors like the following indicate that there is a problem with the connection settings:

1. 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 (http:// or https://), and should not include a trailing slash (/) or the database name.

2. 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.

Known limitations

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:

1. 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_bi with the following SQL:

   CREATE VIEW my_view_bi AS SELECT * FROM my_view;
   

   Then, in Power BI, you can use the my_view_bi view.

2. If applicable, instead of using a materialized view, create a view with an index instead.

3. 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;")