Grouping Several Values into Single 'Other' Dimension Value

If necessary, it is possible to define a Dimension that enumerates only a limited set of the available values, with the remaining values grouped under a catch-all 'Other' Dimension Value.

For example, you may have three key products that make up most of your business, while the rest of the products can be collapsed into a single dimension. You can do this in Metric Insights by writing a fetch command for your Dimension that creates this grouping.

This article explains how to create anduse this Dimension when defining Metrics and Reports. In this example, we define a Product Dimension and select three specific products for Dimension values while assigning the rest of the products to an 'Other' Dimension value.

1. Create a New Dimension

Access Content > Dimensions

  1. [+ New Dimension]
  2. Name: Give the Dimension a descriptive name.
  3. Value Source: This example uses SQL data source.
  4. Data Fetch Bind Parameter: Setting this field to match the column name for the data in the source system makes writing fetch commands for Dimensioned element easier.
  5. [Save]

2. Create Fetch Command

  1. CASE statements are used to surface the three desired products and to bucket the rest of the product into the "OTHER" Dimension with a key value of -1.
    • The first CASE statement maps all product_id values to "-1" except for 9, 10, and 50.
    • The second CASE statement selects the product names for the three products with product_id of 9, 10, and 50 and groups the rest of the products under "OTHER".
  2. [Validate]
Expand or collapse content Code Example 
select DISTINCT
CASE
WHEN product_id IN (9,10,50)
THEN product_id ELSE -1
END key_value,
CASE
WHEN product_id IN (9,10,50)
THEN name ELSE 'OTHER'
END display_value
FROM product

2.1. Collect Dimension Values

  1. If your command is valid, the statement box is green; if there are any errors, the box is colored in red and errors are explained in the field below.
  2. Examples of data records are displayed below the SQL statement.
    • Confirm that sample values shown in post-validation are as expected.
  3. [Collect Data]
  4. Dimension Values Grid will be populated.

NOTE: When writing a fetch command for a Metric or Report that uses this Dimension, you can include the same CASE statement logic used to select the key value for the Product Dimension.