Skip to main content

PowerMetrics: Connecting to Microsoft Azure SQL and creating metrics

Updated

When you sign up for a free PowerMetrics trial, you get access for 30 days to our data warehouse integration for Microsoft Azure SQL. 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 Azure SQL warehouse and create metrics to add to your organization’s metric catalog. From there, business users can securely access metrics from the Azure SQL warehouse that they need for visualization, discovery, and dashboarding.

This article includes:

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 Azure SQL 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 Azure SQL warehouse.

Before connecting to Azure SQL

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

  • Make sure data is configured in your Azure SQL 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.
       
  • For security purposes, we enforce SSL protocol when connecting to your data. As a result, your warehouse must support SSL. If you're using a self-signed SSL certificate or an internal certificate authority, you can either disable hostname and certificate verification or contact our Support Team for assistance. Note: You’ll choose whether to enable or disable certificate validation for SSL mode when connecting to Azure SQL.
     
  • You need at least one user with a role that provides access to the database you want to visualize as metrics.
     
  • If you’re using Network Policies to restrict IP access, add our PowerMetrics IP addresses to your allow list.
     
  • Ensure the “first day of week” setting in PowerMetrics matches the one used in your Azure SQL warehouse. If they don’t match, weekly periodicity data and weekly time range (for example, “last week”) data will not display as expected in PowerMetrics. Learn how to modify the first day of the week in PowerMetrics.
     
  • Ensure the account-wide time zone setting in PowerMetrics matches the time zone used by your Azure SQL 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 user name and password that has access to the Azure SQL database.
  • The host, database and schema names.

Creating Azure SQL metrics

To create Azure SQL metrics for your account you:

Connecting PowerMetrics to Azure SQL

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

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

  1. In the left navigation sidebar click the + button beside Metrics and select See all services.
    see all services
  2. On the Where is your data? page, in the Data Warehouse Services section, click Azure SQL.
    Tip: To quickly find the Azure SQL connector, enter text into the “Search by service” field or select the data warehouse tag tag.
    Azure SQL tile
  3. If you’ve connected to Azure SQL before:
    • We assume you want to use the same account and take you directly to the next step – Creating an Azure SQL 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”.
      account connection
      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 Azure SQL:
    • Click Connect account.
    • Click Add a new account.
    • Under Account settings, fill in the following fields:
      • User name: Enter the Azure SQL user name (one with a role/privileges that gives appropriate access to the database).
      • Password: Enter the password associated with the user name.
      • Host: Enter the Azure SQL server name.
      • Port: 1433 is entered here by default.
      • Database: Enter the name of the database you want to access.
      • Schema: Enter the name of the schema you want to access.
      • At SSL mode, choose whether to use certificate validation.
        Note: If you choose “with certificate validation”, SSL will be used to communicate to the server and to validate the server certificate for domain, expiration, and root certificates. If you choose “without certificate validation”, SSL will be used to communicate to the server but skip validation for domain, expiration, and root certificates.
      • 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 Azure SQL.
    • 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 your Azure SQL 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.
  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.)
    webhook_cache_settings
    Learn more about these settings in this section: “How does PowerMetrics get updated when my Azure SQL data changes?”
  8. Click Continue. You’re ready to create an Azure SQL metric.

Configuring Azure SQL metrics and adding them to the metric catalog

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

To create an Azure SQL metric:

  1. Open the Data panel.
    data tab
  2. Under Table/View, click the drop-down to select the table/view that contains the data for your metric.
    table or view
    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.
  3. Under Table/View, click Filter source data to include or exclude members in the selected table/view.
    table_view_filters
    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 table that contains the values you want to track in your metric.
    measure
  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.)
      dimension_filtering
    • 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 Azure SQL 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.
  6. 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.
    column with dates associated with values
  7. Open the Settings panel.
    settings tab
  8. 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.
    date handling
    If you chose Use most recent values only, select the desired time period. (See below.)
    use most recent values
  9. Under Format, select from Numeric, Currency, Percentage, or Duration.
    format
    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.
  10. 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.
  11. 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.
    default aggregation
  12. 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).
    override aggregation
    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.
  13. Under Blank values, decide how to treat empty values. Tip: For transactional metrics, we recommend you treat blank values as zeros.
    blank values
  14. 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.
    favourable trend
  15. Open the About panel.
    about tab
  16. Optionally, enter a new name and description for the metric.
    Note: The metric was automatically named based on the measure you chose.
  17. Under Metric image, optionally, click Edit image to change the metric’s service icon. Learn more here.
  18. Under Certification, optionally, enable the Certify this metric toggle. Learn more here.
  19. 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 Azure SQL warehouse changes?

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

Queries to Azure SQL are cached in PowerMetrics for 24 hours, by default. Note that table metadata is also cached. If you add a new table to Azure SQL, 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 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 Azure SQL 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 Azure SQL account connection:

  1. In the left navigation sidebar, click the + button beside Metrics > See all services, and select Azure SQL.
  2. Open the account connection dialog by clicking the field under Data source (as shown below).
    account connection
  3. Click the 3-dot menu for the desired account connection and select Edit. (See below.)
    edit
  4. The webhook displays near the bottom of the window. Click the Copy button and paste it into a text document. (See below.)
    copy webhook URL
  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 Azure SQL.
  2. Open the account connection dialog by clicking the field under Data source (as shown below).
    account connection
  3. Click the 3-dot menu for the desired account connection and select Edit. (See below.)
    edit
  4. In the Cache management dialog, under Cache expiry, use the drop-down to select the TTL value. (See below.)
    edit cache settings
    Note: To see updated data after the cache has been cleared, refresh the page.
  5. 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 Azure SQL.

To manually clear the cache:

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

How do I edit the settings for an account connection?

After creating an account connection, you can modify its settings, for example, the user name, password, 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 Azure SQL.
  2. Open the account connection dialog by clicking the field under Data source (as shown below).
    account connection
  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 Azure SQL 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 Azure SQL), however, you can also share it later, as described below.

Note: To share an Azure SQL 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 Azure SQL.
  2. Open the account connection dialog by clicking the field under Data source (as shown below).
    account connection
  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.)
    share account connection
  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 Azure SQL metric. 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 connection
  5. In the Select Azure SQL 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 Azure SQL.
  2. Open the account connection dialog by clicking the field under Data source (as shown below).
    account connection
  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 an Azure SQL 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:

  1. In the left navigation sidebar, click Metrics to open your metric catalog.
  2. Select the desired Azure SQL metric. 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.

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 Azure SQL 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 in the overview and select View queries. (See below.)
    view queries
    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, 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.

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, view the query and, optionally, click the Copy button to assist with further investigation.
    Azure SQL query
    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 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.)
    view queries button
  3. 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.
  4. When you’re done, click Close.

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

First day of week settings for Azure SQL metrics are configured in the Azure SQL warehouse. 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 your Azure SQL warehouse. Learn how here.

Related articles

Related articles