BI Tools

​

Supported BI Tools

​

Connection Parameters

​

Metabase

​

Setup

1. In Metabase, click Add a database
2. Select PostgreSQL as the database type
3. Configure the connection:

   Field	Value
   Display name	Materialize
   Host	Your Materialize hostname
   Port	6875
   Database name	materialize
   Username	Your Materialize user email
   Password	Your app password (mzp_...)
   SSL	On (require)

4. Click Save

​

Connecting to a Specific Cluster

1. After creating the connection, go to Connection details
2. Navigate to Connection initialization settings
3. Under Bootstrap queries, add:
   SET cluster = your_cluster_name;
   

ALTER ROLE "[email protected]" SET cluster = analytics_cluster;

​

Refresh Rate

1. Add #refresh=5 to the dashboard URL for 5-second refresh
2. Or use #refresh=1 for 1-second refresh

https://metabase.example.com/dashboard/123#refresh=5

​

Tableau

​

Tableau Cloud

1. In Tableau Cloud, create a new data source
2. Select PostgreSQL as the connector
3. Enter connection details:

   Field	Value
   Server	Your Materialize hostname
   Port	6875
   Database	materialize
   Username	Your Materialize user email
   Password	Your app password
   Require SSL	✓ Checked

4. Click Sign In

​

Tableau Desktop

​

Setup

​

Connection

1. In Tableau Desktop, go to Connect to a Server
2. Select More → PostgreSQL
3. Enter connection details:

   Field	Value
   Server	Your Materialize hostname
   Port	6875
   Database	materialize
   Authentication	Username and Password
   Username	Your Materialize user email
   Password	Your app password
   Require SSL	✓ Checked

4. Click Sign In

​

Troubleshooting

ERROR: Expected FOR, found WITH
ERROR: WITH HOLD is unsupported for cursors

​

Configure Cluster

ALTER ROLE "[email protected]" SET cluster = analytics_cluster;

​

Looker

​

Setup

1. In Looker, go to Admin → Connections
2. Click Add Connection
3. Configure:

   Field	Value
   Dialect	PostgreSQL 9.5+
   Host	Your Materialize hostname
   Port	6875
   Database	materialize
   Schema	public
   Username	Your Materialize user email
   Password	Your app password

4. Click Test then Connect

​

Known Limitations

​

1. Connection Test Warning

Test kill: Cannot cancel queries: Query could not be found in database.

-- Find the connection ID
SELECT * FROM mz_sessions;

-- Cancel the query
SELECT pg_cancel_backend(connection_id) 
FROM mz_sessions 
WHERE id = 'session_id';

​

2. Symmetric Aggregates

• Disable symmetric aggregates in your Looker project (documentation)
• Use non-symmetric aggregations (Looker remains fully functional)
• Contact Materialize support for optimization guidance

​

Configure Cluster

ALTER ROLE "[email protected]" SET cluster = analytics_cluster;

​

Power BI

​

Setup

1. In Power BI Desktop, click Get Data
2. Search for and select PostgreSQL database
3. Enter connection details:

   Field	Value
   Server	instance.region.aws.materialize.cloud:6875
   Database	materialize
   Data Connectivity mode	DirectQuery

4. Click OK
5. Enter credentials:
   • Username: Your Materialize user email
   • Password: Your app password
6. Click Connect

​

Materialized Views Limitation

1. Create a regular view:
   CREATE VIEW my_view_bi AS SELECT * FROM my_materialized_view;
   
   Then use my_view_bi in Power BI.
2. Use an indexed view instead:
   CREATE VIEW my_view AS SELECT * FROM my_source;
   CREATE INDEX idx ON my_view(column);
   
3. Use Native Query Folding:
   = Value.NativeQuery(Source, "SELECT * FROM my_materialized_view;")
   

​

Troubleshooting

• Check the server name format (should be host:6875)
• Don’t include protocol or trailing slash

1. Go to File → Options and settings → Data source settings
2. Delete any Materialize entries
3. Try connecting again

​

Configure Cluster

ALTER ROLE "[email protected]" SET cluster = analytics_cluster;

​

Hex

​

Setup

1. In Hex, go to Workspace Settings → Integrations
2. Click Add → PostgreSQL
3. Configure connection:
   • Host: Your Materialize hostname
   • Port: 6875
   • Database: materialize
   • Username: Your Materialize user email
   • Password: Your app password
   • SSL: Required
4. Click Test Connection → Save

​

Using in Notebooks

import hex

# Query Materialize
df = hex.query("SELECT * FROM my_view")

# Use in visualizations
hex.viz.plot(df, x='timestamp', y='value')

​

Deepnote

​

Setup

1. In your Deepnote project, click Integrations
2. Select PostgreSQL
3. Enter connection details:
   • Hostname: Your Materialize hostname
   • Port: 6875
   • Database: materialize
   • Username: Your Materialize user email
   • Password: Your app password
4. Click Create Integration

​

Using in Notebooks

import deepnote

# Query Materialize
df = deepnote.query("SELECT * FROM my_view")

# Display results
df.head()

​

Excel

1. Install the PostgreSQL ODBC driver
2. In Excel, go to Data → Get Data → From Other Sources → From ODBC
3. Configure the DSN with Materialize connection parameters
4. Import data from tables and views

​

Best Practices

​

1. Use Clusters for Isolation

CREATE CLUSTER bi_cluster (SIZE = 'medium');
ALTER ROLE "[email protected]" SET cluster = bi_cluster;

​

2. Add Indexes

CREATE INDEX idx_user_id ON user_events(user_id);

​

3. Use Materialized Views

CREATE MATERIALIZED VIEW daily_metrics AS
SELECT 
    DATE(timestamp) as date,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM events
GROUP BY DATE(timestamp);

​

4. Monitor Query Performance

SELECT * FROM mz_internal.mz_recent_activity_log
WHERE execution_time_ms > 1000
ORDER BY execution_time_ms DESC;

​

5. Set Connection Limits

ALTER ROLE "[email protected]" CONNECTION LIMIT 10;

​

Refresh Rates

• Metabase: 1-5 seconds
• Tableau: Live connection mode
• Looker: Real-time queries
• Power BI: DirectQuery mode

​

Troubleshooting

​

Cannot Connect

• Verify hostname and port (6875)
• Check SSL is enabled/required
• Confirm app password is correct
• Test with psql first

​

Materialized Views Not Visible

• Create regular views that SELECT from materialized views
• Use native SQL queries
• Add indexes to regular views

​

Slow Queries

• Add indexes on filtered/joined columns
• Use appropriate cluster size
• Check query execution plan
• Monitor resource usage in console

​

Feature Not Supported

• Check SQL compatibility
• Use alternative SQL patterns
• Submit feature requests

​

System Requirements

• Outbound network access to port 6875
• SSL/TLS support
• PostgreSQL driver/connector
• Materialize app password

​

Next Steps

​

Request Support for New Tools