Working with normalize transformations

You use normalize transformations to combine values from multiple columns in a single column by creating more rows.

About this task

Each distinct original column value is placed within a new, single data column in the new result set, one row per column value. A second column is used to indicate which original column value a given row includes.
Table 1. Original result set data
Region Revenue Units Sold Net Income
North America 250 452 87
Asia Pacific 290 538 92
Europe 320 675 120
Normalization allows us to transform the data into the following two forms:
Table 2. Normalized query results
Region KPI KPI Value
North America Revenue 250
North America Units Sold 452
North America Net Income 87
Asia Pacific Revenue 290
Asia Pacific Units Sold 538
Asia Pacific Net Income 92
Europe Revenue 320
Europe Units Sold 675
Europe Net Income 120
Table 3. Alternate normalized query results
Region KPI KPI Value Units Sold
North America Revenue 250 452
North America Net Income 87 452
Asia Pacific Revenue 290 538
Asia Pacific Net Income 92 538
Europe Revenue 320 675
Europe Net Income 120 675
In the first transformation, all three numeric columns have been normalized into a column pair. In the second sample, Revenue and Net Income have been normalized and Units Sold has been retained as an independent column. Note that the names of both the column that carries the value (KPI Value in this sample) and the column that indicates the original column (KPI) are arbitrary and can be defined by the user.

Normalized queries are often used to reverse an aggregation, rotating the pivot of the table and displaying individual rows for aggregated data. However, it is important to note that normalization can be applied to any result set data, not merely aggregated values.

Queries containing aggregated data can be normalized, in effect reversing the cross-tabulation and enabling the query results data to be displayed in a normalized format. For example, the Furniture Sales table is made up of the following columns Order Date, Category and Gross Profit, to display the gross profit of sales by the order date. The table is then crosstabbed to display a Gross Profit Column for each Category and an Order Date Column. The crosstabbed query results appear like the example shown below:
Table 4. Crosstabbed query results for furniture sales
Order Date Gross Profit (CHAIRS) Gross Profit (SOFAS) Gross Profit (MISC)
19 November 2008 160 452 87
23 November 2008 0 680 181
26 November 2008 120 642 0
Normalizing the query reverses the crosstab result set to produce query results that look like the original query results before the crosstab was applied. The normalized query results appear like the example shown below:
Table 5. Normalized query results for furniture sales
Order Date Category Gross Profit
19 November 2008 CHAIRS 160
19 November 2008 MISC 87
19 November 2008 SOFAS 452
23 November 2008 MISC 181
23 November 2008 SOFAS 680
26 November 2008 CHAIRS 120
26 November 2008 SOFAS 642

To configure a normalize transformation:

Procedure

  1. In the Analytical query structure, select a normalize transformation node.
    In the Item editor pane, you can view the items of the normalize transformation. The original table columns are initially displayed in the No Transform section. The columns that need to be retained without any transformation, in the result set, should remain in No Transform section.

    Transform: Columns which needs to be normalized must be moved to the Transform section.

    Discard: Columns which needs not to be present in the result set must be moved to the Discard section.

  2. Optional: If the node under normalize transformation is shown as none, add a query or table into the <none> node. For more information, see Populating analytical query transformations.
  3. To move the table columns from one section to another section, right click Column Name > Move to Section.
    For example, if you want to move a column from No Transform section to Transform section, select the column and right-click > select Move to Transform.
    Alternately, you can drag and drop the columns from one section to another section.
    Note: You can select multiple columns by using the Ctrl + Click.
  4. Click Add above the Dimensions list box.
    The Dimension Column dialog opens. In Dimension Column dialog, specify the name and type of the column in the Name field and Type drop-down list respectively. This is the new column that will display the names of the normalized columns. In the example above, this is the Category column.
  5. Optionally, you can check Auto generate check box to automatically create all the Constants for the dimension column.
    In the example above, Constants are the different product type columns, such as CHAIRS, MISC, and SOFAS. These are labels for the normalized column names and as such can be anything. For example, if you wanted to make the query results more readable, you could specify lowercase names like chairs, misc, and sofas.
    Note:
    • Auto generate is enabled only for the character data type.
    • Automatch will create dimension constants for all the original table columns.
  6. Specify the data values for the dimension column under Constants section. If you have chosen to auto generate the data values of dimension columns, the auto generated data values will be displayed in Constants section.
  7. Click Add above the Values list box.
    The Value Column dialog opens. Specify the name and type of the column in the Name field and Type drop-down list respectively. This is the new column that will display the values of the normalized columns. In the example above, this is the Gross Profit column.
  8. In the Transform section, to map each normalized column to its corresponding dimensions constant, click the Value Identifiers cell.
    The Dimension Mapping dialog opens. In the Dimension Mapping dialog, for each Dimension, map the corresponding Constants for the particular normalized column.
  9. Select the value from the Value Column drop-down for each normalized column.
  10. Select the Suppress Zeroes or Suppress Nulls check boxes to exclude rows from the normalized result set that contains zero or no value.
  11. Optional: If you want to generate dimensions, constants, value columns, and fill the dimension mapping of normalized columns automatically, click Automatch in the upper right corner of the Item editor and click Yes in the Automatic filling the structure of normalize dialog.
    Note: Automatch will override all your existing query settings.
  12. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.