Display 'top N + other' measures using data cube transforms

Contents[Hide]

1. Overview

This article shows how to display the top/bottom 'N' records and the sum of the remaining records using data cube transforms.

Note
Metric sets have settings that can perform the same task: see Displaying top/bottom records.

2. Set up the data cube

This example uses the Adventure Works database to display the top 4 Product IDs by LineTotal price, and the sum of the rest of the LineTotal values. 

Under the data connector, find the [Sales].[SalesOrderDetail] table, and drag it onto the data cube canvas.

An automatic ETL process consisting of SQL Select transform and the Process Result is created.

2.1. Top/Bottom transform

Select the connection link and insert a Top/Bottom transform from the toolbar.

Insert a Top/Bottom transform
Insert a Top/Bottom transform

Right-click on the Top/Bottom Transform and select Configure.

In the configuration dialog, insert the number of records you want to see in the Record Count. From the Sorting Transform Input, drag the measure being sorted for the Top/bottom records (LineTotal).

Configure the Top/Bottom transform
Configure the Top/Bottom transform

2.2. Union transform

Drag and drop the [Sales].[SalesOrderDetail] table again onto the canvas to create another SQL Select transform.

Combine the outputs of the Top/Bottom transform and the new SQL Select using a Union transform.

Insert a Union transform
Insert a Union transform

2.3. Sort transform

Select the connection link between the second SQL Select and the Union transform, and insert a Sort transform.

Right-click the transform and configure it. In the configuration dialog, drag LineTotal from the Sorting Transform Input to the Sorting Elements and click its arrow button to sort it in Descending order.

Configure the Sort transform
Configure the Sort transform

2.4. Rank transform

Select the connection link between the Sort transform and the Union transform, and insert a Rank transform.

Right-click on the transform and configure it. In the configuration dialog, Linetotal from Rank Transform Input to Ranking Elements.

Configure the Rank transform
Configure the Rank transform

2.5. Filter transform

Select the connection link between Rank transform and the Union transform and insert a Filter transform.

Right-click on the transform and configure it. In the configuration dialog, click the Edit icon beside the Rank element. Choose Greater Than from the Element Operator dropdown and set the value as 4. This will give us all the records that have a rank greater than 4 (i.e., all but the top 4 records).

Configure the Filter transform
Configure the Filter transform

2.6. Aggregate transform

Next, click on the connection link between the Filter transform and the Union transform and insert the Aggregate transform.

In its configuration dialog, uncheck all the measures or dimensions that are not needed. Set the Aggregate function for LineTotal as Sum and uncheck the ProductID.

Configure the Aggregate transform
Configure the Aggregate transform

3. Preview the data

Select the Union transform and open the Data Preview window.

You can see the ProductIDs with the top 4 LineTotal values and an additional row with the sum of the rest of the LineTotal values.

Preview the data from the Union transform
Preview the data from the Union transform

Notice that the sum of the rest of the LineTotals has an empty string for the ProductID.

3.1. Replace the empty string

Click on the connection link between Union transform and the Process Result node.

3.1.1. Data Conversion transform

In our case, ProductID contains numbers and not yet strings (text), so we will insert a Data Conversion transform and configure it.

Change the type for ProductID from Int32 to String.

Convert the column to String
Convert the column to String

3.1.2. Calculated Element transform

Click on the connection link between the Data Conversion transform and the Process Result node, and insert a Calculated Element transform.

In its configuration dialog, uncheck ProductID and add a new calculated element.

Name this calculated element as ProductID. Change the Data Type to String and add the following code in the expression editor:

Add a Calculated Element transform for the text 'Others'.
Add a Calculated Element transform for the text 'Others'.

Tip
A Null Replacement transform can also be used instead.

Submit the changes.

The final data cube looks like this:

The resulting data cube
The resulting data cube

Click on Process Result and preview the data in the Data Preview window.

The output with the 'Others' category name.
The output with the 'Others' category name.

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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