Microsoft Excel preview

The dbt Semantic Layer offers a seamless integration with Excel Online and Desktop through a custom menu. This add-on allows you to build dbt Semantic Layer queries and return data on your metrics directly within Excel.

Prerequisites

• You have configured the dbt Semantic Layer and are using dbt v1.6 or higher.
• You need a Microsoft Excel account with access to install add-ons.
• You have a dbt Cloud Environment ID and a service token to authenticate with from a dbt Cloud account.
• You must have a dbt Cloud Team or Enterprise account. Suitable for both Multi-tenant and Single-tenant deployment.
  • Single-tenant accounts should contact their account representative for necessary setup and enablement.

📹 Learn about the dbt Semantic Layer with on-demand video courses!

Explore our dbt Semantic Layer on-demand course to learn how to define and query metrics in your dbt project.

Additionally, dive into mini-courses for querying the dbt Semantic Layer in your favorite tools: Tableau, Hex, and Mode.

Installing the add-on

The dbt Semantic Layer Microsoft Excel integration is available to download directly on Microsoft AppSource. You can choose to download this add in for both Excel Desktop and Excel Online

1. In Excel, authenticate with your host, dbt Cloud environment ID, and service token.

   • Access your Environment ID, Host, and URLs in your dbt Cloud Semantic Layer settings. Generate a service token in the Semantic Layer settings or API tokens settings
   Access your Environment ID, Host, and URLs in your dbt Cloud Semantic Layer settings. Generate a service token in the Semantic Layer settings or API tokens settings

2. Start querying your metrics using the Query Builder. For more info on the menu functions, refer to Query Builder functions. To cancel a query while running, press the Cancel button.

When querying your data with Microsoft Excel:

• It returns the data to the cell you have clicked on, and each cell where data is requested will have a note attached to it, indicating what has been queried and the timestamp.
• There's no timeout limit.
• If you're using this extension, make sure you're signed into Microsoft with the same Excel profile you used to set up the Add-In. Log in with one profile at a time as using multiple profiles at once might cause issues.

Query Builder functions

The Microsoft Excel Query Builder custom menu has the following capabilities:

Menu items	Description
Metrics	Search and select metrics.
Group By	Search and select dimensions or entities to group by. Dimensions are grouped by the entity of the semantic model they come from. You may choose dimensions on their own without metrics.
Time Range	Quickly select time ranges to look at the data, which applies to the main time series for the metrics (metric time), or do more advanced filter using the "Custom" selection.
Where	Filter your data. This includes categorical and time filters.
Order By	Return your data order.
Limit	Set a limit for the rows of your output.

Note: Click the info button next to any metric or dimension to see its defined description from your dbt project.

Modifying time granularity

When you select time dimensions in the Group By menu, you'll see a list of available time granularities. The lowest granularity is selected by default. Metric time is the default time dimension for grouping your metrics.

Filtering data

To use the filter functionality, choose the dimension you want to filter by and select the operation you want to filter on.

• For categorical dimensions, you can type a value into search or select from a populated list.
• For entities, you must type the value you are looking for as we do not load all of them given the large number of values.
• Continue adding additional filters as needed with AND and OR.
• For time dimensions, you can use the time range selector to filter on presets or custom options. The time range selector applies only to the primary time dimension (metric_time). For all other time dimensions that aren't metric_time, you can use the "Where" option to apply filters.

Querying without headers or columns

If you would like to just query the data values without the headers, you can optionally select the Exclude Column Names box.

FAQs

I'm receiving an `Failed ALPN` error when trying to connect to the dbt Semantic Layer.

If you're receiving a Failed ALPN error when trying to connect the dbt Semantic Layer with the various data integration tools (such as Tableau, DBeaver, Datagrip, ADBC, or JDBC), it typically happens when connecting from a computer behind a corporate VPN or Proxy (like Zscaler or Check Point).

The root cause is typically the proxy interfering with the TLS handshake as the dbt Semantic Layer uses gRPC/HTTP2 for connectivity. To resolve this:

• If your proxy supports gRPC/HTTP2 but isn't configured to allow ALPN, adjust its settings accordingly to allow ALPN. Or create an exception for the dbt Cloud domain.
• If your proxy does not support gRPC/HTTP2, add an SSL interception exception for the dbt Cloud domain in your proxy settings

This should help in successfully establishing the connection without the Failed ALPN error.