Aggregation strategy

Aggregation lets you represent your supply chain without making the model overly complicated. For example, it is often useful to aggregate your products at a product family level rather than at the individual SKU basis. In addition to a basic aggregation strategy, you can use a more advanced method in which data is first segmented, then aggregated within each of the segments. This method can be helpful when you want to use different aggregation levels for different segments.

By default, model data is not aggregated. You must select Specify aggregation for each Model element included in the Model Recipe on the Aggregation node to enable the aggregation features.

For each of the elements you included on the Core Elements node, you can define aggregation levels.

When you apply aggregation to the elements, this affects the results populated in all tables that use those elements. For example, aggregating Customers determines the records that are populated in a number of tables, such as Customers, Customer Orders, Customer Sourcing Policies, Transportation Policies and Flow Constraints. In each of these tables, the customer value will be the result of the aggregation you applied.

There are three types of aggregation you can define per element:

  • No aggregation – Rows from the DDM are not aggregated.

  • Basic aggregation – Select one or more aggregation levels by which to group the DDM data.

  • Advanced aggregation – Select a segment value, then define aggregation levels within each segment. The aggregation levels can vary between segments. The segment value and aggregation levels are the same as supported for aggregation levels in Basic Aggregation.

Editing aggregation settings

With Basic Aggregation and Advanced Aggregation, you can make changes to the aggregation levels, name and aggregation operators as needed.

If you select a different aggregation method, such as changing from Basic Aggregation to Advanced Aggregation, all current settings for the element's aggregation will be discarded. You are prompted to confirm this action. Click Continue to discard the current settings, or Cancel if you want to keep the current aggregation method and settings.

No aggregation

  • No aggregation – Rows from the DDM are not aggregated. Each row included in the scope filters becomes a row in the model.

Basic aggregation

Basic aggregation enables you to group data from the model elements tables by one or more columns. You can select one or more aggregation levels by which to group the DDM data. The name applied to the aggregated row is based on the columns you select for aggregation. You can control the order of the name components using Aggregation Rules.

Numeric columns are aggregated based on the aggregation operator you select using Aggregation Rules.

When aggregating data, keep the following in mind:

  • Column values that vary across the aggregated records are not populated. For example, assume the following source data:

location_name location_primary_country location_primary_city primary_lat primary_long
Boise DC United States Boise 43.6187 -116.215
Laredo DC United States Laredo 27.5604 -99.4892
Calgary DC Canada Calgary 51.0829 -114.08
Ottawa DC Canada Ottawa 45.4166 -75.7
Pittsburgh DC United States Pittsburgh 40.4406 -79.9958

If you aggregate based on location_primary_country, and use "AVG" as the aggregation operator for the numeric columns, the result will be as follows, with no value populated for City:

Name Country City Latitude Longitude
Canada Canada   48.2498 -94.89
United States United States   37.2066 -98.5667
When aggregating, it is possible to create an aggregated value that exceeds the length of the target column in the model table. This condition will cause a failure when trying to run the model recipe.

Aggregate using basic aggregation

  1. On the Aggregation node, select Specify aggregation for each Model element included in the Model Recipe to enable the aggregation features.

  2. Click Next to move to the element you want to aggregate, such as Sites, or click the node in the navigation tree.

  3. Select Basic Aggregation as the aggregation method.

  4. Using the dropdown list, select one or more columns that will be used as aggregation levels for the data. The order in which the aggregation levels are listed is the order of data aggregation. You can remove a selected aggregation level by unchecking it in the dropdown list or by clicking the x next to the name of the column. To include the record key as part of the aggregated value, you can select the *Ckey column for Customers, Sites and Products.

    When aggregating Customers, be aware that the aggregated name for the records will be prepended with "CZ_". This is to prevent duplicate names in the Sites and Customers table. For example, if you aggregate both Sites and Customers using primary_country, Sites will have values such as "Mexico" and Customers will have "CZ_Mexico".

  5. Click Aggregation Rules. You see the Aggregation Rules flyout.

  6. For Name Aggregation, you see the columns you selected as aggregation levels in the order in which they were selected. This order determines the unique name applied to the resulting aggregated record. If you want to change the order of the columns for creating the name, you can drag and drop them to the required positions in the Group By list displayed.

  7. For Numerical Field Aggregation, you see numerical columns from the source table that will be included in the model. By default, the values for these columns will be averaged in the aggregated records. You can select the Aggregation Operator to be applied to each column:

    • SUM - the sum of the values for the column across the records included in the aggregation.

    • MIN - the minimum value for the column across the records included in the aggregation.

    • MAX - the maximum value for the column across the records included in the aggregation.

    • AVG - the average value for the column across the records included in the aggregation.

    • AVG (Weighted by Orders Full Quantity) - the average value for the column across the records included in the aggregation, weighted by the total quantity for those records from the Orders Full table. This applies to Customers, Products and Sites.

    • AVG (Weighted by Shipment Full Quantity) - the average value for the column across the records included in the aggregation, weighted by the total quantity for those records from the Shipment Full table. This applies to Customers, Products and Sites.

    • AVG (Weighted by Production History Quantity) - the average value for the column across the records included in the aggregation, weighted by the total quantity for those records from the Production History table. This applies to Products.

      For example, you can use the "AVG (Weighted by Orders Full Quantity)" operator to locate the customer latitude an longitude values based on the quantity of product they order. The location will be closer to those customers with larger order quantities.

  8. For Aggregation Separator, select the character you want to use to separate the aggregation value levels in the name structure. The default is an underscore, but you can change this to a hyphen, a dot, or a vertical line. The character you select applies to all aggregated elements. For example, if you select the vertical line for Customers aggregation, aggregated Sites, Products and Modes will also use the vertical line. If you select Do not aggregate model data on the Aggregate node and clear the aggregation settings, the separator returns to the default underscore when you next apply aggregations.

  9. Click Save. The flyout closes. If you have changed the order of the aggregation levels using Aggregation Rules, this is reflected in the aggregation levels field.

  10. If your recipe definition is complete, click Save and Close. You can click Save to save the current definition and continue editing.

When rows are aggregated, the resulting record will be assigned a unique name based on the order of aggregation columns. For example, for Customers with primary_country and primary_state_province as the aggregation levels, customer names in the resulting model would be similar to the following:

CZ_Canada_AB

CZ_Canada_ON

CZ_United States_NY

CZ_United States_TX

 

Advanced aggregation

Basic aggregation enables you first segment your model element data. For example, you may want to segment the data by country. Once the data is segmented, you can group the data by one or more columns. The set of aggregation levels can vary between different segments. The segmentation and aggregation features in advanced aggregation provide you with a great deal of control over the elements that become the basis of your model structure. Specific use cases that can make use of advanced aggregation include:

  • Applying a different customer aggregation strategy by country. You can segment by country, then apply a different aggregation strategy for each country. For example, once you have segmented by country you can aggregate customers in Mexico by state/province and aggregate customers in the United States and Canada by business unit first, and then by state/province.

  • Applying a different product aggregation strategy by sales volume. For example, you can label products as "High volume", "Medium volume", or "Low volume" using one of the product family level columns in the DDM. You then segment the products by that product family column, and apply a different aggregation strategy for each segment.

The name applied to the aggregated row is based on the columns you select for aggregation. You can control the order of the name components using Aggregation Rules.

Numeric columns are aggregated based on the aggregation operator you select using Aggregation Rules.

When aggregating data, keep the following in mind:

  • Column values that vary across the aggregated records are not populated. For example, assume the following source data:

      • location_name location_region location_primary_country location_primary_city primary_lat primary_long
      Boise DC NA United States Boise 43.62 -116.22
      Laredo DC NA United States Laredo 27.56 -99.49
      Bremen DC EMEA Germany Bremen 53.08 8.8
      Leipzig DC EMEA Germany Leipzig 51.34 12.41
      Ganzhou DC APAC China Ganzhou 25.92 114.95
      Quanzhou DC APAC China Quanzhou 24.90 118.58

      If you segment based on location_region, then aggregate based on location_primary_country, and use "AVG" as the aggregation operator for the numeric columns, the result will be as follows, with no value populated for City:

      Name Country City Latitude Longitude
      APAC_China China   25.41

      116.76
      EMEA_Germany Germany   52.21

      10.60
      NA_United States United States   37.2066 -98.5667
When aggregating, it is possible to create an aggregated value that exceeds the length of the target column in the model table. This condition will cause a failure when trying to run the model recipe.

Aggregate using advanced aggregation

  1. On the Aggregation node, select Specify aggregation for each Model element included in the Model Recipe to enable the aggregation features.

  2. Click Next to move to the element you want to aggregate, such as Sites, or click the node in the navigation tree.

  3. Select Advanced Aggregation as the aggregation method.

  4. Use the dropdown list to select the column that will be used to segment the data. When the column is selected, you see the Segmentation Aggregation Levels. There is one row for each unique value of the segment column. For example, if you segment by primary_country, one row is displayed per country.

    When aggregating Customers, be aware that the aggregated name for the records will be prepended with "CZ_". This is to prevent duplicate names in the Sites and Customers table. For example, if you aggregate both Sites and Customers using primary_country, Sites will have values such as "Mexico" and Customers will have "CZ_Mexico".

  5. Using the dropdown list in each row, select one or more columns that will be used as aggregation levels for the data. The order in which the aggregation levels are listed is the order of data aggregation. You can remove a selected aggregation level by unchecking it in the dropdown list or by clicking the x next to the name of the column. To include the record key as part of the aggregated value, you can select the *Ckey column for Customers, Sites and Products. The set of aggregations levels and the order in which they are selected can vary between the rows. Tools are available to help with the process:

    • Clear Row - Within each row, click Clear Row to remove all aggregation levels from the current row.

    • Copy To All Rows - Within a row, click Copy To All Rows to copy the set of aggregation levels from the current row to all rows in the table. This overwrites any aggregation levels already selected in the other rows.

    • Clear All Segment Aggregations - Click this option to clear the aggregation levels from all rows in the table.

  6. Click Aggregation Rules. You see the Aggregation Rules flyout.

  7. For Name Aggregation, you see the columns you selected as aggregation levels in the order in which they were selected. This order determines the unique name applied to the resulting aggregated record. If you want to change the order of the columns for creating the name, you can drag and drop them to the required positions in the Group By list displayed. Keep in mind that these are the levels across the various segment rows. The name aggregation structure is the same for all segments.

  8. For Numerical Field Aggregation, you see numerical columns from the source table that will be included in the model. By default, the values for these columns will be averaged in the aggregated records. The numerical field aggregation is the same for all segments. You can select the Aggregation Operator to be applied to each column:

    • SUM - the sum of the values for the column across the records included in the aggregation.

    • MIN - the minimum value for the column across the records included in the aggregation.

    • MAX - the maximum value for the column across the records included in the aggregation.

    • AVG - the average value for the column across the records included in the aggregation.

    • AVG (Weighted by Orders Full Quantity) - the average value for the column across the records included in the aggregation, weighted by the total quantity for those records from the Orders Full table. This applies to Customers, Products and Sites.

    • AVG (Weighted by Shipment Full Quantity) - the average value for the column across the records included in the aggregation, weighted by the total quantity for those records from the Shipment Full table. This applies to Customers, Products and Sites.

    • AVG (Weighted by Production History Quantity) - the average value for the column across the records included in the aggregation, weighted by the total quantity for those records from the Production History table. This applies to Products.

      For example, you can use the "AVG (Weighted by Orders Full Quantity)" operator to locate the customer latitude an longitude values based on the quantity of product they order. The location will be closer to those customers with larger order quantities.

  9. For Aggregation Separator, select the character you want to use to separate the aggregation value levels in the name structure. The default is an underscore, but you can change this to a hyphen, a dot, or a vertical line. The character you select applies to all aggregated elements. For example, if you select the vertical line for Customers aggregation, aggregated Sites, Products and Modes will also use the vertical line. If you select Do not aggregate model data on the Aggregate node and clear the aggregation settings, the separator returns to the default underscore when you next apply aggregations.

  10. Click Save. The flyout closes. If you have changed the order of the aggregation levels using Aggregation Rules, this is reflected in the aggregation levels fields for the segments.

  11. If your recipe definition is complete, click Save and Close. You can click Save to save the current definition and continue editing.

When rows are aggregated, the resulting record will be assigned a unique name based on the segmentation level and the order of aggregation columns. For example, for Customers with location_region as the segmentation level and primary_country and primary_state_province as the aggregation levels, customer names in the resulting model would be similar to the following:

CZ_NA_Canada_ON

CZ_NA_United States_NY

CZ_NA_United States_TX

CZ_SA_Argentina_AR-M

CZ_SA_Brazil_DF

Sort the segment level values

  1. Click the Segment Levels column header.

  2. Select A to Z to sort in ascending order or Z to A to sort in descending order.

Edit the segment value

While defining the advanced aggregation, you can make changes to the aggregation levels, name and aggregation operators as needed.

If you select a different column to segment the data, such as changing from primary_country to business_unit, all current settings for the aggregation levels will be discarded. You are prompted to confirm this action. Click Continue to discard the current aggregation settings, or Cancel if you want to keep the current segment and aggregation settings.