Display 'Top n + Other' measures using Data cube transforms

Contents[Hide]

1. Overview

This article shows how to display top/bottom n records and the sum of the remaining records as a single value in a visualization. This can be done by using the grouping and filtering transforms in the Data cube.

2. Set Up the Data Cube

This example uses the Adventure Works database to display top 4 Product Id’s with the maximum LineTotal price and the rest of the Product Id’s LineTotal measure summed up as a single value. 

Under the data connector, find the [Sales].[SalesOrderDetail] table.

Drag and drop the [Sales].[SalesOrderDetail] table on the Data cube designer 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.

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 another [Sales].[SalesOrderDetail] table on the Data cube designer canvas.

Connect both the SQL selects 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.

Insert a Sort Transform and right click on it to configure it. In the Configuration dialog, drag the LineTotal from the Sorting Transform Input to the Sorting elements and sort it in the 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.

Insert a Rank transform from the Insert Other components in the toolbar.

Same as before, right click on the rank transform and configure it. Drag 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.

Insert a Filter Transform from the Insert common transforms in the toolbar. Right click to configure it.

Click on the edit icon beside the Rank element. Choose Greater than from Element Operator dropdown and set the value as 4. This will give all the records that have a rank greater than 4 i.e. all the records except 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.

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 click on Data Preview.

You can see the ProductId’s with the top 4 LineTotals and an additional row with the summed up LineTotal of the rest of the ProductId’s.

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

Insert a Data conversion transform and configure it.

Change the ProductID from Int32 to String.

Configure the Data Conversion Transform to change the ProductId from Int to String
Configure the Data Conversion Transform to change the ProductId from Int to String

3.1.2. Calculated Element Transform

Click on the connection link between the Data conversion Transform and the Process Result node.

Insert a calculated element transform. Right click on the transform and click on configure.

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:

Configure the Calculated Element Transform to replace the empty string
Configure the Calculated Element Transform to replace the empty string

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 from the dockable windows at the bottom.

The Resulting data with Top 4 measures and sum of Others
The Resulting data with Top 4 measures and sum of Others

Use this data cube on the dashboard and revisualize it to any chart type.

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