How do I create Google BigQuery metrics? – PowerMetrics Help Center

When you sign up for a free PowerMetrics trial, you get access for 30 days to our data warehouse integration for BigQuery. To continue using this feature when your trial period ends, purchase a plan that includes the “data warehouse integration” or one that allows you to buy the “data warehouse 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.

Connect to data in your BigQuery warehouse and create metrics to add to your organization’s metric catalog. From there, business users can securely access metrics from the BigQuery warehouse that they need for visualization, discovery, and dashboarding.

This article includes:

Why PowerMetrics?
Before connecting to BigQuery
Creating BigQuery metrics
- Connecting PowerMetrics to BigQuery
- Configuring BigQuery metrics and adding them to the metric catalog
Frequently asked questions

Why PowerMetrics?

Here are a few key reasons why PowerMetrics adds value to your existing data stack:

Self-serve analytics for business users: Data consumers can independently access metrics, certified and approved by the data team, in a centralized catalog. The intuitive, approachable PowerMetrics design makes it easy for business users to explore, analyze, and visualize metric data in reports and dashboards, without assistance from the data team.
Secure data storage: Data is stored and managed in your BigQuery warehouse.
Data governance: Built-in users and roles, set for each individual at the account admin level, let you control data access. Learn more about PowerMetrics roles and permissions.
Single source of truth data: Certified metrics in a metric catalog, managed by the data team and accessible to business users, ensures consistent data across reports, dashboards, and departments.
Highly scalable: The amount of data that can be handled by PowerMetrics is only limited by the capacity of the underlying BigQuery warehouse.

Before connecting to BigQuery

Before connecting PowerMetrics to your BigQuery warehouse, ensure you have the right setup:

Make sure data is configured in your BigQuery warehouse to work well with metrics.
- Individual measures (metrics) and their associated dimension(s) must be defined against a single table or view. Note: If your database uses a star schema, with facts (measures) and dimensions in separate tables, we recommend you create an additional view and group your facts and dimensions into a single table for each metric.
- Measures that share a common set of dimensions should be grouped into a single table or view.
- Measures that don’t have common dimension sets should not be grouped, as doing so can result in sparse tables and potentially lead to performance or data integrity issues.
- Tables must include at least one date column and one measure column. Multiple metrics can be grouped into a single view or table if they share a common date and other dimensions.
- See this article for practical tips on improving query performance for data warehouse metrics.
Make sure you have a Google Cloud service account with the right set of permissions. We recommend enabling these predefined IAM roles: "BigQuery Data Viewer", "BigQuery Job User", and "BigQuery Read Session User". Learn more about Identity and Access Management (IAM) roles and permissions for BigQuery.
To allow PowerMetrics to retrieve data from your warehouse, you need to create OAuth certification credentials, stored in a .json or .pfx file, and enter their details into PowerMetrics when connecting. Go here to learn more.
If you’re using Network Policies to restrict IP access, add our PowerMetrics IP addresses to your allow list.
Ensure the account-wide time zone setting in PowerMetrics matches the time zone used by your BigQuery warehouse. Learn how to apply account-wide time zone settings in PowerMetrics.

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

The Project ID.
The OAuth cert file and associated .json or .pfx service account key.
For .pfx files, the OAuth cert file password (if required) and the OAuth issuer email address (the service account email address)

Creating BigQuery metrics

To create BigQuery metrics for your account you:

Connect PowerMetrics to BigQuery
Configure BigQuery metrics and add them to the metric catalog

Connecting PowerMetrics to BigQuery

Connect to data in BigQuery as the first step toward creating BigQuery metrics.

Note: The following procedures describe connecting to BigQuery and creating BigQuery metrics from the left navigation sidebar. You can perform the same actions from the metric catalog (by clicking the +Add metrics button) or from an open dashboard (by clicking Edit > Insert > Metrics +Add metrics).

To connect PowerMetrics to BigQuery:

In the left navigation sidebar click the + button beside Metrics and select See all services.
On the Where is your data? page, in the Data Warehouse Services section, click BigQuery.
Tip: To quickly find the BigQuery connector, enter text into the “Search by service” field or select the tag.
If you’ve connected to BigQuery before:
- We assume you want to use the same account and take you directly to the next step – Creating a BigQuery metric. If you want to connect to a different account, you can do so in the metric creation window by clicking the account connection (as shown below). You can either select an alternate, existing account or click “Add a new account”.
  
  Note: You can edit settings for an existing account by selecting it, clicking its 3-dot menu, and choosing Edit.
If this is your first time connecting to BigQuery:
- Click Connect account.
- Click Add a new account.
- Under Account settings, fill in the following fields:
  - Project ID: Enter the Project ID you want to use to connect to PowerMetrics.
  - Select an OAuth cert file type:
    - If you selected Google JSON, add the OAuth cert file. Note: After adding an OAuth cert file, if you want to replace it, you can remove the existing file by clicking the Remove Cert file button (as shown below).
    - If you selected PFX file:
      - Enter the OAuth issuer email.
      - Add the OAuth cert file.
      - Enter the OAuth cert file password (if required).
  - Dataset ID: Enter the ID for the dataset containing the tables and views you want to use.
  - Display name: Enter the desired display name. We recommend a user-friendly name that makes it easy to determine the source of the data or the identity of the user creating the account connection.
    Note: This name will only display in PowerMetrics, not in BigQuery.
- To share the account connection with other editor users, toggle on Share account connection with editor users (optional).
- Click Save.
The Connection successful window displays.
To automatically update PowerMetrics in alignment with data updates in your BigQuery warehouse, you need to call the webhook URL when the data in your warehouse updates. Click the Copy button to copy the webhook URL that displays under Webhook. Note: Automatically clearing the cache with a webhook is strongly recommended.
To use a cache expiration strategy, under Cache expiry, use the drop-down to select the TTL value. Note: This method is only recommended if you’re not able to set up a webhook. (See below.)

Learn more about these settings in this section: “How does PowerMetrics get updated when my BigQuery data changes?”
Click Continue. You’re ready to create a BigQuery metric.

Configuring BigQuery metrics and adding them to the metric catalog

Now that you’ve connected to data in BigQuery, you’re ready to create a metric.

To create a BigQuery metric:

Open the Data panel.
Under Table/View, click the drop-down to select the table/view that contains the data for your metric.

Note: The table/view must include at least one date column and one non-date column.
Tip: If you don’t see the table/view you’re looking for, check that your account connection properties are correct. If you still don’t see the table/view, try clearing the cache.
Under Table/View, click Filter source data to include or exclude members in the selected table/view. Excluded members will not display or be available as dimensions in the saved metric visualization.

Note: You can filter by member or by condition. By default, the first time you apply filters to a metric, you’ll see the UI for member filtering. To filter by condition, click the “Condition filters” link. Note: You can apply up to 10 filter conditions/dimension.
Under Measure, select the column from the table that contains the values you want to track in your metric.
Under Dimensions, all available dimensions for segmenting and/or filtering the metric value (measure) 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. (See below.)
- To create a metric with no ability to segment or filter, click Deselect all.
- Note: Your measure and dimensions all need to come from the same table or view. If they’re not currently set up that way, you should create new views and group the measure and dimensions you need into a single view.
- Note: When choosing dimensions, you can only select columns that have a text format. If the BigQuery table you selected doesn’t include any text columns there will be no available categories by which to segment.
- Tip: You can optionally change a dimension’s display name by selecting the current name and typing a new one. This changes the display name only (not the name of the dimension in the data source). If you change the display name and want to see the one that’s being used in the data source, hover over the dimension’s name. Note: If you change a name for a dimension that’s also being used by other metric visualizations, associated filters and segmentations will be removed from those visualizations and need to be reapplied.
Under Date and Time, select the column from the table/view that contains the date associated with each value.
Note: Selecting the correct date dimension for your metric is important as it will be used to aggregate your metric for different time granularities, such as daily, monthly, quarterly time periods.
Open the Settings panel.
Under Date handling, select Use all values or Use most recent values only. Your choice determines the data points your metric will use when calculating the metric value for a time period.

If you chose Use most recent values only, select the desired time period. (See below.)
Under Format, select from Numeric, Currency, Percentage, or Duration.

If you selected Currency as the data format, 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 selected Duration as the data format, optionally, adjust the Maximum and Minimum Granularity settings.
Under 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.
Under Aggregation, you’ll see the aggregation type. It’s based on the measure you selected for the metric. For numeric columns, the initial aggregation type is “sum”. For text columns, it's “count”. The aggregation type will be used when the aggregation has not been overridden by the user in a metric visualization and wherever there are no configuration options available. For example, in the metric catalog, metrics will display using an aggregation type based on your selection here.
Enable the Users can override aggregation option if you want to allow personalized changes to the aggregation type for the metric (for example, when users are analyzing metrics in Explorer).

Note: When metrics are created, they’re automatically visualized using the most appropriate aggregation type. As a result, we recommend disabling the ability to override the aggregation type.
Under Blank values, decide how to treat empty values. Tip: For transactional metrics, we recommend you treat blank values as zeros.
Under 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.
Open the About panel.
Optionally, enter a new name and description for the metric.
Note: The metric was automatically named based on the measure you chose.
Under Metric image, optionally, click Edit image to change the metric’s service icon. Learn more here.
Under Certification, optionally, enable the Certify this metric toggle. Learn more here.
When you’re done configuring your metric, click Save metric.
If you added the metric from the navigation sidebar or from the metric catalog, it’s added to the metric catalog. If you added the metric from an open dashboard, it’s added to that dashboard and to the metric catalog.
If, after configuring your metric, you want to edit any of its settings, refer to this article.

Frequently asked questions

How does PowerMetrics get updated when data in my BigQuery warehouse changes?

Data in your BigQuery warehouse naturally evolves over time as new data is added or old data is revised.

Queries to BigQuery are cached in PowerMetrics for 24 hours. Note that table metadata is also cached. If you add a new table to BigQuery, you’ll need to clear the cache to create a metric using the new data.

There are a few options for managing the cache:

Automatically with a webhook (recommended)
Using a cache expiration strategy
Manually at any time

Automatically clearing the cache with a webhook (recommended)

To automatically align data in your warehouse with metric data in PowerMetrics, clear the cache using a webhook. The webhook notifies PowerMetrics when there are changes in the BigQuery data that’s associated with the connected account.

When the webhook is called the cache is cleared, the UI is notified about the changes, and up-to-date data is automatically shown.

Calling the Clear Cache Webhook Endpoint:

Method: POST the webhook url displayed in the account connection dialog to clear the cache for that account connection.
Description: This endpoint enables you to clear the cache used for query responses and metadata. When triggered, this endpoint invalidates all cached data.
Request Body: No request body is required. The endpoint will respond with a success message upon receipt of a valid POST request.

Note: This endpoint is useful for managing cache freshness and ensuring you always receive up-to-date query results, however, excessive use may have performance implications. To clear the cache only when necessary and to keep your data in PowerMetrics current, we recommend adding a step to the data ingestion pipeline for the webhook to be called immediately after data is updated.

Finding the Webhook URL within your BigQuery account connection:

In the left navigation sidebar, click the + button beside Metrics > See all services, and select BigQuery.
Open the account connection dialog by clicking the field under Data source (as shown below).
Click the 3-dot menu for the desired account connection and select Edit. (See below.)
The webhook displays near the bottom of the window. Click the Copy button and paste it into a text document. (See below.)
Click X to close the window.

Using a cache expiration strategy

If you’re not able to set up a webhook, you can manage the cache by selecting a TTL value. Your selection determines the maximum time for individual queries to exist in the cache.

Note:

The PowerMetrics UI will not update automatically when queries expire from the cache. After an item is removed from the cache, the latest data will be obtained and cached only after a user interaction causes that item to be re-queried, for example, by refreshing the browser.
A shorter TTL can provide more up-to-date data but increases the number of queries sent to the data warehouse service and may reduce performance.

You can select the TTL value when creating an account connection or when editing one (as described below).

To select the TTL value:

In the left navigation sidebar, click the + button beside Metrics > See all services, and select BigQuery.
Open the account connection dialog by clicking the field under Data source (as shown below).
Click the 3-dot menu for the desired account connection and select Edit. (See below.)
In the Cache management dialog, under Cache expiry, use the drop-down to select the TTL value. (See below.)

Note: To see updated data after the cache has been cleared, refresh the page.
Click Save.

Manually clearing the cache

At any time, you can manually clear the cache for an account connection. Note: The more often you clear the cache, the more queries that will be sent to BigQuery.

To manually clear the cache:

In the left navigation sidebar, click the + button beside Metrics > See all services, and select BigQuery.
Open the account connection dialog by clicking the field under Data source (as shown below).
Click the 3-dot menu for the desired account connection and select Clear cache (as shown below).

Note: To see updated data after the cache has been cleared, refresh the page.
Click X to close the window.
Note: You can also manually clear the cache from an open metric, in edit mode, by clicking the 3-dot menu beside Data source and selecting Clear cache. This action clears the cache for all metrics associated with the account connection. (See below.)

How do I edit the settings for an account connection?

After creating an account connection, you can modify its settings, for example, the Project ID, OAuth cert file type, and display name.

To edit the settings for an account connection:

In the left navigation sidebar, click the + button beside Metrics > See all services, and select BigQuery.
Open the account connection dialog by clicking the field under Data source (as shown below).
Click the 3-dot menu for the desired account connection and select Edit.
Modify account settings as desired and click Save.

How do I share an account connection?

Sharing an account connection enables other editor users to use the connection to query data in BigQuery and create metrics in PowerMetrics. It does not give sharing, editing, or deleting access to the account connection - only the creator of the account connection can perform these actions.

Usually, you share an account connection while creating it (the first time you connect to BigQuery), however, you can also share it later, as described below.

Note: To share a BigQuery 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:

In the left navigation sidebar, click the + button beside Metrics > See all services, and select BigQuery.
Open the account connection dialog by clicking the field under Data source (as shown below).
Click the 3-dot menu for the account connection you want to share and select Edit.
Enable the Share account connection with editor users toggle. (See below.)
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:

In the left navigation sidebar, click Metrics to open the metric catalog.
Select the desired BigQuery metric. The metric’s overview page opens in view mode.
Click the Edit button at the top of the page, then click the Data tab.
At Data source, click the 3-dot menu and select Change account. (See below.)
In the Select BigQuery account window, either select a different, existing account and click Connect, or click Add a new account, fill in account settings, and click Save.
Click Save metric.

How do I delete an account connection?

Deleting an account connection deletes it for you and for everyone in your account who has shared access to it. When you delete an account connection its associated metrics stop updating. If you want to resume updates, you can edit the metrics and apply the account connection of your choice.

To delete an account connection:

In the left navigation sidebar, click the + button beside Metrics > See all services, and select BigQuery.
Open the account connection dialog by clicking the field under Data source (as shown below).
Click the 3-dot menu for the account connection you want to delete and click Delete.
Click Delete again to confirm.

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 BigQuery warehouse 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:

In the left navigation sidebar, click Metrics to open your metric catalog.
Select the desired BigQuery metric. The metric’s overview page opens in view mode.
Click the Edit button at the top of the page, then click the Data tab.
At Data source, click the 3-dot menu and select Change data source > See all services. (See below.)

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

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.
When the metric settings have been selected, click Save metric.

How do I access the SQL queries generated by PowerMetrics for a metric visualization?

When debugging and troubleshooting it can be helpful to investigate the SQL queries for 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 BigQuery account connection can access these queries.

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

In the left navigation sidebar, click Metrics to open the metric catalog.
Click the metric you want to investigate.
Click the 3-dot menu for a metric visualization in the overview and select View queries. (See below.)

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.
Under Queries, view the query 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.
When you’re done, click Close.

To view queries for metric visualizations on a dashboard:

In the left navigation sidebar, click Dashboards to open the list of dashboards.
Select the dashboard that contains the metric(s) you want to investigate.
Click the 3-dot menu for the metric visualization on the dashboard and select View queries.
Under Queries, view the query 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.
When you’re done, click Close.

To view queries in Explorer:

In the left navigation sidebar, click Explorer.
If you haven’t added any metrics yet to Explorer, go here to learn how.
Either click the View 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.)
Under Queries, view the query 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.
When you’re done, click Close.