PowerMetrics: Connecting to Cube and creating metrics

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.

Connect to data using a Cube semantic layer and create metrics to add to your organization’s metric catalog. From there, business users can securely access metrics from the Cube semantic layer that they need for visualization, discovery, and dashboarding.

This article includes:

• Why PowerMetrics?
• Before connecting to Cube
• Creating Cube metrics
  • Connecting PowerMetrics to Cube
  • Configuring Cube metrics and adding them to the metric catalog
• Frequently asked questions
  • How does PowerMetrics get updated when data in my Cube warehouse changes?
  • How do I edit the settings for an account connection?
  • How do I share an account connection?
  • How do I switch to use a different account connection?
  • How do I delete an account connection?
  • How do I change a metric’s data source?
  • How do I access Cube and SQL queries for a metric visualization?
  • Why are users getting an error when applying weekly periodicity to Cube metric visualizations?

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.
   
• Defined metrics: Cube does not contain a metrics layer. As a result, it doesn’t include individual, atomic metric definitions. Connecting to PowerMetrics enables you to define metrics according to the specific needs of your organization.
   
• Secure data storage: Data is stored and managed in your data source, for example, a data warehouse. Cube provides a query abstraction layer (semantic layer) to simplify access to and querying of the data.
   
• 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 Cube and the underlying data warehouse.

Before connecting to Cube

Before connecting PowerMetrics to Cube, ensure you have the right setup:

• Make sure data is configured to work well with metrics. Note that each metric in PowerMetrics is defined against a single cube. Cubes must include at least one date column and one non-date column. If your data is spread across multiple cubes and related by a single join, you should create a single view for each metric that combines the measure and dimensions you require for that metric.
   
• If you’re using Network Policies to restrict IP access, add our PowerMetrics IP addresses to your allow list.
   
• The “first day of week” setting in PowerMetrics matches the one used by the underlying data source (for example, a Snowflake data warehouse). If they don’t match, weekly periodicity data will not display as expected in PowerMetrics. Learn how to modify the first day of the week in PowerMetrics.
   
• The account-wide time zone setting in PowerMetrics matches the time zone used in your data stack. 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 Cube host URL: To find your Cube host URL, click Overview in Cube Cloud. Your Cube host URL is listed under REST API.
• The API key: To find your CUBEJS_API_SECRET key, follow this path in Cube Cloud: Settings > Configuration > Cube environment variables.
   

Creating Cube metrics

To create Cube metrics for your account you:

• Connect PowerMetrics to Cube
• Configure Cube metrics and add them to the metric catalog

Connecting PowerMetrics to Cube

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

Note: The following procedures describe connecting to Cube and creating Cube 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 Cube:

1. In the left navigation sidebar click the + button beside Metrics and select See all services.
   
2. On the Where is your data? page, click Cube.
   Tip: To quickly find the Cube connector, enter text into the “Search by service” field or select the tag.
   
3. If you’ve connected to Cube before:
   • We assume you want to use the same account and take you directly to the next step – Creating a Cube 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.
4. If this is your first time connecting to Cube:
   • Click Connect account.
   • Click Add a new account.
   • Under Account settings, fill in the following fields:
     • Cube host URL.
     • API key.
     • 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 Cube.
   • To share the account connection with other editor users, toggle on Share account connection with editor users (optional).
   • Click Save.
5. The Connection successful window displays.
6. To automatically update PowerMetrics in alignment with data updates in Cube, you need to call the webhook URL when the data in Cube 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.
7. 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 Cube data changes?”
8. Click Continue. You’re ready to create a Cube metric.

Configuring Cube metrics and adding them to the metric catalog

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

To create a Cube metric:

1. Open the Data panel.
   
2. Under Cube/View, click the drop-down to select the cube/view that contains the data for your metric.
   
   Note: The cube/view must include at least one date column and one non-date column.
   Tip: If you don’t see the cube/view you’re looking for, check that your account connection properties are correct. If you still don’t see the cube/view, try clearing the cache.
3. Under Cube/View, click Filter source data to include or exclude members in the selected cube/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.
4. Under Measure, select the column from the cube that contains the values you want to track in your metric.
   
5. 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 cube 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 cube/view 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.
6. Under Date and Time, select the column from the cube/view that contains the date/time 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.
   
7. Open the Settings panel.
   
8. 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.
9. 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.
10. 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.
    
11. Open the About panel.
    
12. Optionally, enter a new name and description for the metric.
    Note: The metric was automatically named based on the measure you chose.
13. Under Metric image, optionally, click Edit image to change the metric’s service icon. Learn more here.
14. Under Certification, optionally, enable the Certify this metric toggle. Learn more here.
15. 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 Cube warehouse changes?

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

Queries to Cube are cached in PowerMetrics for 24 hours. Note that cube metadata is also cached. If you add a new cube/view, 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 Cube with metric data in PowerMetrics, clear the cache using a webhook. The webhook notifies PowerMetrics when there are changes in Cube 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.

Note: To ensure the cache in PowerMetrics is immediately cleared when source data is updated, we recommend using this method. It’s not built into Cube but can be added to a custom script./p

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 Cube account connection:

1. In the left navigation sidebar, click the + button beside Metrics > See all services, and select Cube.
2. Open the account connection dialog by clicking the field under Data source (as shown below).
   
3. Click the 3-dot menu for the desired account connection and select Edit. (See below.)
   
4. The webhook displays near the bottom of the window. Click the Copy button and paste it into a text document. (See below.)
   
5. 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:

1. In the left navigation sidebar, click the + button beside Metrics > See all services, and select Cube.
2. Open the account connection dialog by clicking the field under Data source (as shown below).
   
3. Click the 3-dot menu for the desired account connection and select Edit. (See below.)
   
4. 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.
5. Click Save.

Manually clearing the cache for an account connection

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

To manually clear the cache for an account connection:

1. In the left navigation sidebar, click the + button beside Metrics > See all services, and select Cube.
2. Open the account connection dialog by clicking the field under Data source (as shown below).
   
3. 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.
4. 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 (as shown below). This action clears the cache for all metrics associated with the account connection.
   

How do I edit the settings for an account connection?

After creating an account connection, you can modify its settings, for example, the Cube host URL, API key, and display name.

To edit the settings for an account connection:

1. In the left navigation sidebar, click the + button beside Metrics > See all services, and select Cube.
2. Open the account connection dialog by clicking the field under Data source (as shown below).
   
3. Click the 3-dot menu for the desired account connection and select Edit.
4. 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 Cube 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 Cube), however, you can also share it later, as described below.

Note: To share a Cube 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 > See all services, and select Cube.
2. Open the account connection dialog by clicking the field under Data source (as shown below).
   
3. Click the 3-dot menu for the account connection you want to share and select Edit.
4. Enable the Share account connection with editor users toggle. (See below.)
   
5. 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 the metric catalog.
2. Select the desired Cube 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.)
   
5. In the Select Cube 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?

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:

1. In the left navigation sidebar, click the + button beside Metrics > See all services, and select Cube.
2. Open the account connection dialog by clicking the field under Data source (as shown below).
   
3. Click the 3-dot menu for the account connection you want to delete and click Delete.
4. 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 Cube 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 metric catalog.
2. Select the desired Cube 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.)
   
   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.
   
   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.

How do I access Cube and SQL queries for a metric visualization?

When debugging and troubleshooting it can be helpful to investigate the Cube and 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 Cube account connection 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 the metric catalog.
2. Click the metric you want to investigate.
3. Click the 3-dot menu for a metric visualization on 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.
4. Under Queries, select Cube or SQL.
5. 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.
6. 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 the 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, click Cube or SQL.
5. 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.
6. 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 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.)
   
3. Under Queries, click Cube or SQL.
4. 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.
5. When you’re done, click Close.

Why are users getting an error when applying weekly periodicity to Cube metric visualizations?

First day of week settings for Cube metrics are configured in the associated data source. If this setting doesn’t match the one being used in PowerMetrics, and users apply a weekly periodicity or weekly time range (for example, “last week”) to their metric visualizations in the metric overview page, metric catalog, or dashboard, they’ll get an error.

To fix this issue, modify the first day of week settings in PowerMetrics to match the one being used in the data source. Learn how here.