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
|Region||Revenue||Units Sold||Net Income|
|North America||Units Sold||452|
|North America||Net Income||87|
|Asia Pacific||Units Sold||538|
|Asia Pacific||Net Income||92|
|Region||KPI||KPI Value||Units Sold|
|North America||Net Income||87||452|
|Asia Pacific||Net Income||92||538|
Net Incomehave been normalized and
Units Soldhas 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.
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:
|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|
|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:
- 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.
- 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.
- 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.
- 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
- 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
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
- Auto generate is enabled only for the character data type.
- Automatch will create dimension constants for all the original table columns.
- 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.
- 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
- 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.
- Select the value from the Value Column drop-down for each normalized column.
- Select the Suppress Zeroes or Suppress Nulls check boxes to exclude rows from the normalized result set that contains zero or no value.
- 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.
- Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.