Skip to main content

PowerMetrics: Adding dbt Semantic Layer metrics

Updated

Go here to sign up for a free trial and learn more about our dbt Semantic Layer integration.

Deliver a streamlined metrics catalog to your business users so they can access data from your dbt™ Semantic Layer metrics for visualization, discovery, and dashboarding.

Note: When you sign up for a free PowerMetrics trial, you get access for 30 days to our semantic layer integration. To continue using this feature when your trial period ends, purchase a plan that includes the “semantic layer integration” or one that allows you to buy the “semantic layer integration add-on”. Want to see how PowerMetrics works before connecting it to your own data? Our sample metrics are the perfect, risk-free way to do just that.

This article includes:

Before connecting to PowerMetrics

Confirm you have the right setup for the PowerMetrics – dbt Semantic Layer integration by ensuring:

Have the following information ready. You’ll enter it when connecting to PowerMetrics:

  • The environment ID. To find this information, go to your environment in dbt Cloud and look at the URL. The environment ID is the set of digits at the end. For example, in this case https://cloud.getdbt.com/#/accounts/11111/projects/22222/environments/55555/ the environment ID is 55555.
  • Your dbt Cloud service token. If needed, go here to learn how to generate a service account token. Make sure you assign “Semantic Layer Only” and “Metadata Only” permissions to your service account token. If you already have a token but aren’t sure where to find it, go to the Your Profile section and look under API Access.

Adding dbt Semantic Layer metrics

Connect to your dbt Cloud account and import your dbt metrics into PowerMetrics.

To add dbt Semantic Layer metrics:

  1. In the left navigation sidebar click the + button beside Metrics and select See all services.
  2. On the Where is your data page, select dbtTM Semantic Layer. (See below.)
    dbt Semantic Layer tile
    Tip: To quickly find this service, select the semantic layer service tag tag under the “Search by service” field.
  3. If this is your first time connecting to dbt Semantic Layer:
  4. The Connection successful window displays.
  5. If you want PowerMetrics to automatically update when you successfully deploy your dbt Semantic Layer project, you need to set up a webhook. Click the Copy button to copy the webhook URL that displays under Webhook. You’ll use it when setting up the webhook in dbt.
    Learn more in this section of the article “How does PowerMetrics get updated when my dbt metrics change?”
    copy webhook URL
  6. Click Continue.
    Note: The next time you add dbt Semantic Layer metrics, we assume you want to use the same account as before and skip steps 2 - 5. If you want to use a different account, click the account connection (as shown below). Then, click Add a new account (go back to step 2).
    dbt account connection
  7. Select the metrics you want to add to your list of metrics. You can click individual metric tiles or click Select all metrics to add them to your list of metrics.
    Note: The metric names here are formatted for display purposes (initial uppercase, no underscores). To see the metric names as they’re formatted in the dbt project (lowercase, with underscores) hover over the display name and view the tooltip.
    Important: In future, whenever you add metrics to your dbt Semantic Layer project, remember to come back here and import them into PowerMetrics.
    Tip: If you have a lot of dbt metrics, use the Search field to help locate the ones you’re looking for. (See below.) You can also use the Metric type filter to display specific types of dbt metrics: Simple, ratio, cumulative, or derived.
    find dbt metric by type
    Tip: Click the info button icon for a metric to learn about it, including its description, metric type (simple, derived, ratio, and cumulative), and available periodicities. (See below.)
    get information
  8. When you’re done selecting metrics, click Add metrics.
    Tip: If you return to this page later, you can tell which metrics you’ve already imported. They display with a green checkmark. (See below.)
    indicator a dbt metric was imported
  9. Your list of metrics opens with the newly added ones displayed at the top. To open a metric’s overview page, select it in the list.

Setting metric properties

After adding a dbt Semantic Layer metric to PowerMetrics, you can modify its data format and set a favourable trend. You can also change the metric’s display name, add a description, and change the metric's service icon. This article describes all the editing options for dbt Semantic Layer metrics.

To set properties for a metric:

  1. In the left navigation sidebar, click Metrics to open your list of metrics.
  2. Select the desired metric. The metric’s overview opens in view mode.
  3. Click the Edit button at the top of the page, then click the Data tab.
  4. Under Dimensions, all available dimensions for segmenting and filtering are automatically selected. If there are some dimensions you don’t want to include in the metric, deselect their checkboxes to remove them.
    • By default, selected dimensions will be available for filtering and segmentation in the final metric visualization. To modify this setting for individual dimensions, click All beside the dimension name and deselect the checkbox for as a filter or as a segment.
      dimension_filtering
  5. Click the Settings tab.
  6. At Format, optionally, select an alternate data format from the drop-down menu.
    If you select Currency, the default currency symbol that will display is USD $. You can select an alternate Currency symbol from the drop-down list. Note: This is a display option only. Currency is not converted.
    If you select Duration, optionally, adjust the Maximum and Minimum Granularity settings.
  7. At Decimal places, select the number of decimals to display (from 0 - 9). The Auto setting displays up to 4 decimal places and drops trailing zeros.
    Note: If you change the decimal place setting for a metric and want to see the source data values with their original decimal places, reselect Auto.
  8. At Favourable trend, choose whether you want ascending or descending values to indicate a positive trend.
    For example, if your metric includes sales totals, a higher (ascending) number demonstrates a positive trend but, if your metric includes cost to acquire customers, a lower (descending) number indicates a positive trend. Favourable trends are indicated by a green arrow. Unfavourable changes in metric values are indicated by a red arrow.
  9. Click the About tab.
  10. At Name, optionally, enter a different display name for the metric.
  11. At Description, the metric description from the dbt Semantic Layer project is automatically entered here. You can, optionally, override this description with different text.
  12. Under Metric image, click Edit image if you want to change the metric's service icon. Learn more here.
    Note: You can also certify your metric while setting its properties. See more about certifying metrics below.
  13. Click Save metric.

Certifying metrics

When you’re finished adding and setting properties for a metric you can certify it. This recommends it for use by others in your organization. Learn more about metric certification.

Accessing metric details

You access metric details from the About panel, opened by clicking the info button button in the banner across the top of a metric’s overview page. (To open a metric’s overview, click the Metrics button in the left navigation sidebar, then select a metric from the list to open it.)

In the About panel, you’ll see the following:

  • The metric description. By default, this information comes from the dbt Semantic Layer project, however, in edit mode, you can override it with an alternate description.
  • The metric type (simple, ratio, cumulative, or derived).
  • The metric owner and when it was last edited.
  • The amount of data history that’s available.
  • The available segments, displayed as pink text.
  • The semantic layer metric name (ID).
  • The metric's certification status. Learn more about certifying metrics.
  • The associated data service.

Frequently asked questions

Does PowerMetrics have any pre-built metric definitions?

Yes. We provide dbt metric definition templates for every metric in MetricHQ. Each definition includes its label, description, type, expression, and base metrics.

To access dbt metric definition templates:

  • Select a metric in MetricHQ. Under Metric Toolkit, click dbt Metric Definition, then click Copy.
    After copying the definition, you’ll paste it into your yml file in the dbt Semantic Layer. Then, you’ll populate the placeholder text with the applicable information from your data warehouse.

How does PowerMetrics get updated when my dbt metrics change?

dbt Semantic Layer metrics evolve over time as new data is added to the database or metric definitions in dbt are updated. To ensure these changes are automatically reflected in your PowerMetrics metrics, you can set up a webhook in dbt. The webhook notifies PowerMetrics when you have successfully run a deployment job for the corresponding dbt Semantic Layer project. PowerMetrics then updates the metadata it has for the metric, clears cached queries, and notifies the UI to update its state and get the latest data for the changed metrics. For information on setting up a webhook, see Webhooks for your jobs.

Here are a few tips for creating a webhook in dbt Cloud:

  • Go to the webhook configuration page in dbt Cloud and click the Create webhook button.
  • In the Events section, select the Run completed option.
  • In the Jobs section, select the job that builds and updates the semantic model and metrics.
  • In the Endpoint field, paste the Webhook URL you copied from PowerMetrics (step #5 in the “Adding dbt Semantic Layer metrics” procedure above).
    Tip: To find the Webhook URL:
    1. In the left navigation sidebar, click the + button beside Metrics and select See all services.
    2. On the Where is your data? page, select dbt™ Semantic Layer.
    3. Click the account connection, located in the upper-right corner.
    4. Click the 3-dot menu beside the account connection name and select Edit.

Instead of setting up automatic updates, you can manually clear the cache in PowerMetrics for an account connection. This action also updates your metrics in PowerMetrics to align with changes to your dbt metric data and metric definitions.

To manually clear the cache for an account connection:

  1. In the left navigation sidebar, click Metrics to open your list of metrics.
  2. Select the desired dbt metric from the list. The metric’s overview page opens in view mode.
  3. Click the Edit button at the top of the page, then click the Data tab.
  4. At Data source, click the 3-dot menu, then select Clear cache (as shown below). The cache is cleared for all metrics associated with this account connection.
    manually clear cache
    Note: You can alternatively clear the cache using the following method: Click the + button beside Metrics and select See all services. On the Where is your data? page, select dbt™ Semantic Layer. Next, click the account connection in the upper right corner. Click the 3-dot menu beside the account connection name and then click Clear cache. Click X to close the window.

How do I edit the settings for an account connection?

After creating an account connection, you can modify its settings, for example, the environment ID, service token, and display name.

To edit the settings for an account connection:

  1. In the left navigation sidebar click the + button beside Metrics and select See all services.
  2. On the Where is your data page, select dbtTM Semantic Layer.
  3. Click the account connection, located in the upper-right corner. (See below.)
    dbt account connection
  4. Click the 3-dot menu beside the account connection name and then click Edit. (See below.)
    edit account connection settings
  5. Modify account settings as desired and click Save.

How do I share an account connection?

You can share an account connection to enable other editor users to import and set up metrics from the associated dbt Semantic Layer project.

Note: To share a dbt Semantic Layer metric with others and give them full editing access (including to the data settings), you must share the metric and the account connection that was used to create the metric.

To share an account connection:

  1. In the left navigation sidebar click the + button beside Metrics and select See all services.
  2. On the Where is your data page, select dbtTM Semantic Layer.
  3. Click the account connection, located in the upper-right corner. (See below.)
    dbt account connection
  4. Click the 3-dot menu beside the account connection name and then click Edit. (See below.)
    sharing account connection
  5. Enable the Share account connection with editor users toggle. (See below.)
    sharing account connection
  6. Click Save.

How do I switch to use a different account connection?

You can change the account a metric is associated with by switching it to use a different, existing connection or a new connection.

To switch the account connection for a metric:

  1. In the left navigation sidebar, click Metrics to open your list of metrics.
  2. Select the desired dbt metric from the list. The metric’s overview page opens in view mode.
  3. Click the Edit button at the top of the page, then click the Data tab.
  4. At Data source, click the 3-dot menu and select Change account. (See below.)
    switch account
  5. In the Select dbt™ Semantic Layer account window, either select a different, existing account and click Connect, or click Add a new account, fill in account settings, and click Save.
  6. Click Save metric.

How do I delete an account connection?

To delete an account connection:

  1. In the left navigation sidebar click the + button beside Metrics and select See all services.
  2. On the Where is your data page, select dbtTM Semantic Layer.
  3. Click the account connection, located in the upper-right corner. (See below.)
    dbt account connection
  4. Click the 3-dot menu beside the account connection name and then click Delete. (See below.)
    delete account connection

How do I change a metric’s data source?

PowerMetrics makes transitioning metric data within your stack possible by enabling you to reconfigure metrics to use different data sources.

After connecting to data in a dbt Semantic Layer project and configuring metrics in PowerMetrics, if your data management needs change, you can switch to a different data source for any or all of your metrics – all without affecting existing metric visualizations, dashboards, goals, or sharing rights.

Learn more about changing metric data sources.

To change a metric’s data source:

  1. In the left navigation sidebar, click Metrics to open your list of metrics.
  2. Select the desired dbt metric from the list. The metric’s overview page opens in view mode.
  3. Click the Edit button at the top of the page, then click the Data tab.
  4. At Data source, click the 3-dot menu and select Change data source > See all services. (See below.)
    change data source
    Note: If you already have data feeds in your account, you’ll also see the option to “Select a data feed”. This is a quick way to open your list of data feeds (if you want to change to a data feed data source).
  5. Connect to your data and configure the metric. To access detailed connection and configuration instructions by service, see these sections in our Knowledge Base: Creating data warehouse metrics, Creating data feed metrics, and Creating semantic layer metrics.
    Note: When possible, PowerMetrics refers to the previously-used configuration settings to auto-fill the metric’s properties (measure, dimensions, etc.). Our ability to do so depends on the data source. Sometimes, you’ll need to reselect some or all of the metric’s configuration settings.
    Note: Under Dimensions, if you see the following note, it means one or more dimensions from the pre-existing data source are not available in the new data source. This is often due to the dimensions being named differently. To see a list of the previously used dimensions, click the “See missing dimensions link.” Note: If you click X to close this window or you save the metric, the list of missing dimensions will no longer be available.
    missing dimensions list
    Note: If a dimension's name has changed, in the list under "Dimensions", remap it to point to the previously used column by selecting the new name and typing over it with the column's original name.
  6. When the metric settings have been selected, click Save metric.

Why can’t I load my metrics?

If you see the following message: “The metric <"metric name"> cannot be found in the associated dbt Semantic Layer project”, this means there are no dbt metrics available for import. This happens because metrics weren’t added to the Semantic Layer project or they were removed from it. It can also happen if the metric has been renamed in the dbt project but not updated in PowerMetrics.

If the dbt metric you originally used for this visualization is unchanged, except that it’s been renamed, click the Edit button and, in the Data tab, under dbt project metric, select the renamed metric. Update the name of the metric visualization to match the new dbt metric name and click Save metric.

If you need help adding metrics to your dbt Semantic Layer project, you can refer to these articles: Setting up the dbt Semantic Layer and building and defining metrics.

Tip: If you remove a metric from dbt and want to re-add it, if it uses the same metric name/ID as before, the associated metric in PowerMetrics will start working again. You’ll find the metric name/ID in the About tab.

How do I access the dbt queries for my metric visualizations?

When debugging and troubleshooting it can be helpful to investigate the dbt and SQL queries for your metric visualizations.

You can view (and, optionally, copy) queries for a metric visualization in the metric’s overview page, on a dashboard, or in Explorer.

Note: Only the owner of the associated dbt Semantic Layer account can access these queries.

To view queries for metric visualizations in a metric overview page:

  1. In the left navigation sidebar, click Metrics to open your list of metrics.
  2. Click the metric you want to investigate.
  3. Click the 3-dot menu for a metric visualization in the overview page and select View queries. (See below.)
    viewing queries homepage
    Note: The overview displays a set of visualizations for a single metric. As different views of the same metric, they all use the same queries. As a result, you can view queries by clicking the 3-dot menu for any of the data visualizations on this page.
  4. Under Queries, select the query you want to view and, optionally, click the Copy button to assist with further investigation.
    Note: If an error occurs when the queries are being run, an error message will display here.
  5. When you’re done, click Close.

To view queries for metric visualizations on a dashboard:

  1. In the left navigation sidebar, click Dashboards to open your list of dashboards.
  2. Select the dashboard that contains the metric(s) you want to investigate.
  3. Click the 3-dot menu for the metric visualization on the dashboard and select View queries.
  4. Under Queries, select the query you want to view and, optionally, click the Copy button to assist with further investigation.
    viewing queries dashboard
    Note: If an error occurs when the queries are being run, an error message will display here.
  5. When you’re done, click Close.

To view queries in Explorer:

  1. In the left navigation sidebar, click Explorer.
    If you haven’t added any metrics yet to Explorer, go here to learn how.
  2. Either click the dbt account connectionView queries button or the 3-dot menu in the upper-right corner of the window and select View queries from the drop-down list. (See below.)
    viewing queries explorer
  3. Under Queries, select the query you want to view and, optionally, click the Copy button to assist with further investigation.
    Note: If an error occurs when the queries are being run, an error message will display here.
  4. When you’re done, click Close.

Why am I getting an error when applying a weekly periodicity?

First day of week settings for dbt Semantic Layer metrics are configured in the underlying data source for the Semantic Layer project (for example, in a Snowflake data warehouse). If this setting doesn’t match the one being used in PowerMetrics, and you apply a weekly periodicity to your metric, metric list, or dashboard, you’ll get an error.

To fix this issue, modify the first day of week setting in PowerMetrics to match the one being used by  your dbt Semantic Layer project. Learn how here.

Related articles