Skip to main content

Working with (and combining) data warehouse, semantic layer, and data feed metrics

Updated

PowerMetrics is a hybrid BI solution. Business users can visualize and analyze metrics from:

Your account can focus on one source of data and metric type or combine any or all of them!

This article describes working with data warehouse, dbt Semantic Layer, and Cube metrics in combination with PowerMetrics' built-in metric types (data feed and calculated metrics) and includes:

Combining metric types

All metrics, regardless of their data source and type, can be combined as calculated metrics or multi-metrics and displayed alongside each other on dashboards.

Combining metric types in calculated metrics. Calculated metrics combine metric values using an equation (with simple math - addition, subtraction, multiplication, and division) to create a metric that can be expressed as a number, a percentage, or a ratio. For example, you can combine instant or custom data feed metrics with dbt Semantic Layer metrics to create the equivalent of a ratio or derived dbt metric. As a bonus, unlike dbt ratio and derived metrics, calculated metrics can be made up of metrics from any data source.

Combining metric types in multi-metric charts (in the Explorer and on dashboards). Multi-metric charts enable business users to visualize and compare data for multiple metrics in a single chart. For instance, they could add a Snowflake “Revenue” metric to Explorer and analyze it alongside one or more of our many Facebook Ads instant data feed metrics to see the impact of their Facebook Ads campaign. In Explorer, or in a multi-metric visualization on a dashboard, up to 5 unique metrics can be combined for analysis and free-form exploration.

Adding any and all metric types as visualizations to a dashboard. All metric types behave the same way for consumers, so the decision of which type to use depends entirely on which PowerMetrics solution works better for your data scenario.

The similarities

Metrics are metrics, after all. Here’s a list of PowerMetrics features that work the same way regardless of the metric type and data source:

The differences

Because of fundamental differences in the way data warehouses, dbt Semantic Layer, Cube, and PowerMetrics are designed, there’s bound to be some differences in working with metrics that come from each system. The table below summarizes these differences.

Data Warehouse Metrics

Semantic Layer Metrics
(dbt Semantic Layer and Cube)
Metrics built into PowerMetrics
(Data Feed Metrics and Calculated Metrics)

METRIC TYPES

In PowerMetrics, these metrics are referred to as “data warehouse metrics”.

METRIC TYPES

dbt Semantic Layer metric types are defined in dbt Semantic Layer and include:

  • Simple
  • Ratio
  • Cumulative
  • Derived

In PowerMetrics, they’re referred to as “dbt Semantic Layer metrics”.

Cube does not contain a metrics layer. As a result, it doesn’t include individual, atomic metric definitions. Connecting Cube to PowerMetrics enables you to define Cube metrics according to the specific needs of your organization.

In PowerMetrics, Cube metrics are referred to as “Cube semantic layer metrics”.

METRIC TYPES

  • Instant data feed metrics are created and managed by Klipfolio and give fast access to essential metrics for popular services.
  • Instant calculated metrics combine instant metrics (that are created and managed by Klipfolio) using calculations. For example, the Google Analytics 4 “Engagement Rate” instant metric combines two instant metrics using this formula: “Engaged Sessions” / “Sessions”.
  • Custom data feed metrics are configured by you based on data feeds you create and manage. These metrics can connect to data from almost any data source you can think of.
  • Calculated metrics combine data from one or more instant data feed, custom data feed, dbt, Cube, or data warehouse metrics with a formula to create a single metric. Using calculated metrics, dbt users can create the equivalent of a ratio or derived dbt metric.

CONNECTING TO A DATA SOURCE

PowerMetrics connects directly to the warehouse service using an account connection you create.

The authorization method used to allow PowerMetrics to access your data depends on the warehouse service but typically requires you to enter a username and password that’s associated with the correct role/permissions.

Find service-specific articles in this section of our Knowledge Base.

CONNECTING TO A DATA SOURCE

For dbt Semantic Layer users, metric definitions are imported into PowerMetrics from your dbt Semantic Layer projects.

Authorization for PowerMetrics to access your data is granted using your dbt Cloud Service token and Environment ID.

Learn more about connecting to dbt Semantic Layer.

For Cube users, metrics are defined in PowerMetrics by referencing columns in cubes.

Authorization for PowerMetrics to access your data is granted using an API key.

Learn more about connecting to Cube.

CONNECTING TO A DATA SOURCE

Data feeds, either created by Klipfolio (for instant data feed metrics) or by you (for custom data feed metrics) connect to the data source and channel the data into your team’s metrics.

OAuth Token authentication is typically used to authorize PowerMetrics to access your data.

Learn more about data feeds and OAuth Token authentication in PowerMetrics.

RETRIEVING DATA FOR METRIC VISUALIZATIONS

PowerMetrics connects to the warehouse and retrieves data using an SQL query.

RETRIEVING DATA FOR METRIC VISUALIZATIONS

For dbt Semantic Layer users, PowerMetrics creates a dbt semantic layer query that the dbt Semantic Layer translates into direct queries against the data warehouse.

For Cube users, PowerMetrics creates a Cube API query. Cube translates this query into an SQL query, which is then executed against your data warehouse.

RETRIEVING DATA FOR METRIC VISUALIZATIONS

To retrieve data for instant data feed and custom data feed metrics, we send queries to our metrics database (where the data is stored).

To retrieve data for calculated metrics, we query the individual operands and apply the formula and calculation based on the query result. We don’t store data for the calculated metric itself, but, instead, for each metric within it. The only exception to this is if a calculated metric includes a dbt, Cube, or data warehouse metric as we don’t store data for that metric type.

STORING DATA

PowerMetrics does not store your data.

Data is stored in the data warehouse.

When the data is queried, it’s cached for a short time in memory and removed when the cache is cleared (automatically, with a webhook setup, with a cache expiration strategy, or manually).

STORING DATA

PowerMetrics does not store your data.

Data is stored in the underlying data warehouse, for example, Google BigQuery or Snowflake.

When creating semantic layer metrics, PowerMetrics directly queries your data in real time from the semantic layer. This query is cached for a short time in memory and removed when the cache is cleared (automatically with a webhook setup or manually (dbt Semantic Layer and Cube), or by using a cache expiration strategy (Cube)).

STORING DATA

For instant data feed and custom data feed metrics, the data we retrieve from external data sources and APIs is stored in our proprietary metrics database, which is optimized for metric-based ingestion, updates, and queries.

UPDATING DATA

Data is automatically updated in PowerMetrics, to align with the data in your warehouse via a webhook. Users can also manually align data by clearing the cache or using a cache expiration strategy.

Find service-specific articles in this section of our Knowledge Base.

UPDATING DATA

For dbt Semantic Layer users, data is automatically updated in PowerMetrics whenever a deployment job is successfully run (requires webhook setup) or manually by clearing the cache for the connected dbt Semantic Layer account in PowerMetrics. Learn more.

For Cube users, Cube has its own cache strategy.

In PowerMetrics, when configuring the cache expiration, we recommend choosing the shortest available TTL value. Learn more.

UPDATING DATA

Data is queried automatically based on a schedule. Your pricing plan determines the auto-refresh rate but users can also manually queue a data feed to be refreshed. Learn more about data feed refresh.

Every time your data refreshes, the associated data feed gets updated. The resulting data is then added to the existing data for the metrics that use the feed. The metrics then incorporate the new data into their history.

EDITING METRICS

As previously mentioned, data is stored in the database.

To guarantee SSOT data, in PowerMetrics, users can edit a metric’s display properties only. They cannot edit a metric’s underlying data.

Learn more about editing options for data warehouse metrics.

EDITING METRICS

As previously mentioned, data is stored in your chosen database.

To guarantee SSOT data, in PowerMetrics, users can edit a metric’s display properties only. They cannot edit a metric’s underlying data.

Learn more about editing options for:

EDITING METRICS

Editing options depend on the type of metric. For example, instant data feed metrics, which are created and managed by Klipfolio, have fewer editing options than custom data feed metrics that you create and manage yourself.

Learn more about editing options for:

Related articles

Related articles