Apply a formula to metric sets with compatible time hierarchies

Contents[Hide]

1. Overview

If you have two metric sets, each based on its own data cube, and each data cube utilizes the same or compatible time hierarchies, then you can apply a formula to the two metric sets without having to further join your data. For example, one data cube has a ModifiedDate hierarchy while the other data cube has an OrderDate hierarchy. In this case, the unique names of the hierarchies are different but if both are based on the same underlying time hierarchy (e.g. they were both replaced with the default time dimension), then formulas can be applied to this data without further joins.

This article describes features available in Dundas BI 2.6 or later.

2. Walkthrough

2.1. Create two data cubes

Create a new data cube with 1 measure (LineTotal) and 1 hierarchy (ModifiedDate).

Replace the ModifiedDate hierarchy with the Year > Month > Day default time dimension hierarchy.

Data cube with ModifiedDate hierarchy
Data cube with ModifiedDate hierarchy

Create a second data cube with 1 measure (OrderQty) and 1 hierarchy (OrderDate).

Replace the OrderDate hierarchy with the Year > Month > Day default time dimension hierarchy.

Data cube with OrderDate hierarchy
Data cube with OrderDate hierarchy

2.2. Create two metric sets

Create a new metric set to display the first data cube as a line chart.

Metric Set with LineTotal measure and ModifiedDate hierarchy
Metric Set with LineTotal measure and ModifiedDate hierarchy

Create a second metric set to display the second data cube as a line chart.

Metric Set with OrderQty measure and OrderDate hierarchy
Metric Set with OrderQty measure and OrderDate hierarchy

2.3. Create a dashboard and add a formula visualization

Create a new dashboard using the Blank template. Drag both metric sets to the dashboard canvas.

Dashboard displaying two metric sets
Dashboard displaying two metric sets

Go to the toolbar, click Data Visualization, and then click Formula Visualization. A formula visualization applies a formula to existing data but generates the output result in the form of a new visualization.

Add a formula visualization
Add a formula visualization

In the formula bar, enter a formula for dividing the two chart series. Instead of typing the unique name of each measure, simply click the line series on a chart. The fully-qualified name of the corresponding measure will be entered in the formula bar.

Enter the formula for dividing the two series
Enter the formula for dividing the two series

Click apply. The output result will appear as a table visualization. It will have the two input measures as well but you can remove these from the table (metric set).

Re-visualize the table as a line chart.

Bottom line chart shows the formula result
Bottom line chart shows the formula result

This will also work with formula functions.

3. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri