Query metric views from BI tools
This feature is in Beta.
BI compatibility mode lets you query Unity Catalog metric views from external BI tools. When enabled, Databricks rewrites the queries generated by the BI tool to correctly evaluate metric view measures.
This page covers how to enable BI compatibility mode, how it works, supported scenarios, and known limitations.
Requirements
Microsoft has removed the BI compatibility mode option from the Power BI connector to Databricks. Reports that use this connector option no longer function. To request restored support for Databricks metric views in Power BI, vote and provide feedback on the Fabric Ideas forum. As an alternative to Power BI, consider Databricks AI/BI dashboards, which work natively with metric views.
- A cluster that runs Databricks Runtime 18.0 or above.
- A BI tool that supports pass-through SQL or DirectQuery connections to Databricks.
- The ability to run session-level SQL configuration in the BI tool (for example, through an initial SQL script or startup command).
Enable BI compatibility mode
Enable BI compatibility mode by running the following SQL configuration command at the start of your session:
SET spark.databricks.sql.metricView.bi.compatibilityMode.enabled = true;
How you set the configuration depends on your BI tool. For example, in Tableau you can use the Initial SQL field in the connection dialog.
BI compatibility mode applies only to the session where you set it. Each new connection must set the configuration again.
DirectQuery mode
BI compatibility mode requires that queries run on the Databricks SQL engine. If your BI tool offers both import and direct query modes, use direct query (or live connection) so that queries pass through to Databricks where the rewrite mechanism can apply.
How BI compatibility mode works
Metric views appear as regular tables to BI tools. When BI compatibility mode is enabled, Databricks rewrites the queries generated by the BI tool to correctly query the metric view.
BI compatibility mode handles two types of queries automatically:
- Aggregation queries: When a BI tool generates a query with standard aggregation functions (such as
SUM) on measures, BI compatibility mode rewrites these aggregations to adhere to the measure definitions in your metric view. Always useSUMas the aggregation type for measure columns. The SQL engine always applies the correct underlying measure logic. - Data preview and schema discovery: When the BI tool requests non-aggregated data (for example, column previews or data samples), measure columns return null values instead of an error. Dimension columns return their values normally.
Supported scenarios
The following BI tool features work for most BI tools when you enable BI compatibility mode.
Scenario | Description |
|---|---|
Basic measure visualization | Use a measure in a chart or table value field to display aggregated results. |
Filters | Apply filters to dimension or measure columns in your visuals. |
Dimension slicers | Use dimension columns as slicer or filter controls. |
Cross-filtering | Click a value in one visual to filter related visuals on the same page. |
Drill-through | Drill into a detail page filtered by a specific value. |
TopN filtering | Show the top or bottom N values ranked by a column. |
Data preview | Use data preview and schema discovery. Measures display as null in previews. |
Visual calculations | Client-side calculations applied to already-aggregated results (for example, running totals and ranking). |
Dimensions vs. measures
Metric views contain two types of columns: measures and dimensions. Understanding the difference is important when building reports.
- Measures: The aggregation logic of a measure is defined in the metric view (for example,
SUM(price * quantity)orCOUNT(DISTINCT customer_id)). In your BI tool, always leave the aggregation set toSUMfor measure columns. The SQL engine automatically applies the correct measure logic. If you need a different aggregation, modify the measure definition in the metric view itself. Don't change the aggregation on the BI tool side. - Dimensions: Dimensions behave like regular table columns. You can apply any standard BI operation to dimensions, including aggregations, grouping, filtering, sorting, and bucketing. If a numeric field acts as a dimension (not a measure), all standard aggregation types work normally on that field.
Best practices
- Always include a single metric view in your data set. The metric view is your semantic definition.
- Create folders to organize dimension columns (for example, a "Date" folder for every dimension column from the Date dimension).
- Rename dimensions to have user-friendly names.
- Change numeric dimension columns to a non-aggregate summarization type.
- Create wrapper measures using
SUM()for each measure column and hide the original measure columns (for example,Total Sales = SUM('Store Sales'[total_sales])). - Organize the measures into a dedicated folder.
- Use only wrapper measures in your visuals.
Limitations
BI compatibility mode has limited control over how BI tools generate and process queries. The following limitations apply.
Only use SUM for measure aggregations
Always leave the aggregation type set to SUM for measure columns. All aggregation functions (SUM, COUNT, MIN, MAX) are rewritten to the underlying measure definition, so they all return the same result. Selecting a different aggregation type can lead to unexpected behavior:
AVGshows1.0because some BI tools computeAVGasSUM / COUNTinternally, and both return the same measure value.- Count (Distinct), Standard Deviation, Variance, and Median generate query patterns that are incompatible with the rewrite mechanism and produce errors or incorrect results.
If you need a different aggregation, modify the measure definition in the metric view. All aggregation types are fully supported within metric view definitions.
Grand totals for non-additive measures
Some BI tools calculate grand totals by reaggregating per-group values on the client side rather than issuing a separate query. This produces correct results for additive measures (for example, SUM(revenue)) because reaggregating locally gives the correct answer.
However, for non-additive measures (for example, SUM(revenue) / COUNT(DISTINCT customer), or any ratio, percentage, or expression involving DISTINCT), grand totals might display incorrect values because summing pre-grouped ratios is not mathematically equivalent to computing the ratio over the full data set.
Quantitative slicers on measure columns
A quantitative (range) slicer on a measure column may not work as expected. Some BI tools may query for the MIN and MAX of the measure to determine the slider range, but both are rewritten to the same underlying measure value, collapsing the range to a single point. Filters on measures still work. Only range slicers are affected.
Measures can't be used as categorical or dimensional values
If you use a measure column as a categorical value (for example, as an axis, legend, or slicer), the query fails and returns the following error:
Measure columns cannot be used in GROUP BY clause or as categorical values. We recommend wrapping them with an aggregate function such as SUM() for the expected behavior.
Calculated fields with multiple measures
Calculated fields that reference a single measure work correctly. Some BI tools fetch the aggregated result first and then perform the calculation on the client side (for example, bucketing revenue into low, medium, and high categories).
However, expressions that combine multiple measure columns inside a single aggregate (for example, SUM(m1 + m2)) are not rewritten by BI compatibility mode and produce errors or unexpected results.
Joining metric views with other tables
You can't join metric views with other tables in the BI tool. If you must combine data from a metric view with another table, incorporate the join into the metric view definition itself.
Materialized metric views
BI compatibility mode doesn't support metric views with materializations, and they might produce unexpected results.