DATASTAGE

PROCESSING STAGE

2008-05-04T08:51:00.000-07:00

EXPAND STAGE

The Expand stage is a processing stage. It can have a single input link and a single output link. Follow this link for a list of steps you must take when deploying an Expand stage in your job.

The Expand stage uses the UNIX uncompress or GZIP utility to expand a data set. It converts a previously compressed data set back into a sequence of records from a stream of raw binary data.

PROPERTIES

OPTION CATEGORY

Command. Specifies whether the stage will use uncompress (the default) or GZIP.

PROCESSING STAGE

2008-05-04T08:31:00.000-07:00

ENCODE STAGE

The Encode stage is a processing stage. It encodes a data set using a UNIX encoding command, such as gzip, that you supply. The stage converts a data set from a sequence of records into a stream of raw binary data. The companion Decode stage reconverts the data stream to a data set.

Follow this link for a list of steps you must take when deploying an Encode stage in your job.
An encoded data set is similar to an ordinary one, and can be written to a data set stage. You cannot use an encoded data set as an input to stages that performs column-based processing or re-orders rows, but you can input it to stages such as Copy. You can view information about the data set in the data set viewer, but not the data itself. You cannot repartition an encoded data set, and you will be warned at runtime if your job attempts to do that.
As the output is always a single stream, you do not have to define meta data for the output link.

PROPERTIES

OPTION CATEGORY
Command Line. Specifies the command line used for encoding the data set. The command line must configure the UNIX command to accept input from standard input and write its results to standard output. The command must be located in your search path and be accessible by every processing node on which the Encode stage executes.

Ways to execute DS Jobs

2008-05-04T07:13:00.000-07:00

>>PREVIOUS>>

1.1. What are the ways to execute datastage jobs?

A job can be run using a few different methods:

from Datastage Director (menu Job -> Run now...)
from command line using a dsjob command
Datastage routine can run a job (DsRunJob command)
by a job sequencer

>>NEXT>>

DIFFERENCE STAGE

2008-03-16T14:07:00.000-07:00

>>PREVIOUS>>

DIFFERENCE STAGE

The Difference stage is a processing stage. It performs a record-by-record comparison of two input data sets, which are different versions of the same data set designated the before and after data sets. An example before and after data set are given in Parallel Job Developer's Guide.. Follow this link for a list of steps you must take when deploying a Difference stage in your job.

The Difference stage outputs a single data set whose records represent the difference between them. The stage assumes that the input data sets have been key-partitioned and sorted in ascending order on the key columns you specify for the Difference stage comparison. You can achieve this by using the Sort stage or by using the built in sorting and partitioning abilities of the Difference stage.

The comparison is performed based on a set of difference key columns. Two records are copies of one another if they have the same value for all difference keys. You can also optionally specify change values. If two records have identical key columns, you can compare the value columns to see if one is an edited copy of the other.

The stage generates an extra column, DiffCode, which indicates the result of each record comparison.

The Difference stage is similar, but not identical, to the Change Capture stage. The Change Capture stage is intended to be used in conjunction with the Change Apply stage; it produces a change data set which contains changes that need to be applied to the before data set to turn it into the after data set. The Difference stage outputs the before and after rows to the output data set, plus a code indicating if there are differences. Usually, the before and after data will have the same column names, in which case the after data set effectively overwrites the before data set and so you only see one set of columns in the output. You are warned that DataStage is doing this. If your before and after data sets have different column names, columns from both data sets are output; note that any key and value columns must have the same name.

The stage generates an extra column, Diff, which indicates the result of each record comparison.

PROPERTIES

DIFFERENCE KEY CATEGORIES

Key. Specifies the name of a difference key input column. This property can be repeated to specify multiple difference key input columns. You can use the Column Selection dialog box to select several columns at once if required.

Key has this dependent property:

Case Sensitive. Use this to property to specify whether each key is case sensitive or not. It is set to True by default; for example, the values “CASE” and “case” would not be judged equivalent. This property is only available if the All non-Key columns are values property is set to True.

Difference Values Category

All non-Key Columns are Values. Set this to True to indicate that any columns not designated as difference key columns are value columns. It is False by default. The property has this dependent property:

Case Sensitive. Use this to property to specify whether each value is case sensitive or not. It is set to True by default; for example, the values “CASE” and “case” would not be judged equivalent.

Options Category

Tolerate Unsorted Inputs. Specifies that the input data sets are not sorted. This property allows you to process groups of records that may be arranged by the difference key columns but not sorted. The stage processed the input records in the order in which they appear on its input. It is False by default.

Log Statistics. This property configures the stage to display result information containing the number of input records and the number of copy, delete, edit, and insert records. It is False by default.

Drop Output for Insert. Specifies to drop (not generate) an output record for an insert result. By default, an output record is always created by the stage.

Drop Output for Delete. Specifies to drop (not generate) the output record for a delete result . By default, an output record is always created by the stage.

Drop Output for Edit. Specifies to drop (not generate) the output record for an edit result . By default, an output record is always created by the stage.

Drop Output for Copy. Specifies to drop (not generate) the output record for a copy result . By default, an output record is always created by the stage.

Copy Code. Allows you to specify an alternative value for the code that indicates the after record is a copy of the before record. By default this code is 0.

Deleted Code. Allows you to specify an alternative value for the code that indicates that a record in the before set has been deleted from the after set. By default this code is 2.

Edit Code. Allows you to specify an alternative value for the code that indicates the after record is an edited version of the before record. By default this code is 3.

Insert Code. Allows you to specify an alternative value for the code that indicates a new record has been inserted in the after set that did not exist in the before set. By default this code is 1.

>>NEXT>>

PROCESSING STAGE

2008-03-16T14:05:00.000-07:00

DECODE STAGE

The Decode stage is an active stage. It decodes a data set using a UNIX decoding command, such as gzip, that you supply. It converts a data stream of raw binary data into a data set. Its companion stage Encode converts a data set from a sequence of records to a stream of raw binary data. Follow this link for a list of steps you must take when deploying a Decode stage in your job.

As the input is always a single stream, you do not have to define meta data for the input link.

PROPERTIES

OPTION CATEGORY

Command Line. Specifies the command line used for decoding the data set. The command line must configure the UNIX command to accept input from standard input and write its results to standard output. The command must be located in the search path of your application and be accessible by every processing node on which the Decode stage executes.

PROCESSING STAGE

2008-03-16T12:34:00.000-07:00

COPY STAGE

The Copy stage is a processing stage. It can have a single input link and any number of output links. Follow this link for a list of steps you must take when deploying a Copy stage in your job.

The Copy stage copies a single input data set to a number of output data sets. Each record of the input data set is copied to every output data set without modification. This lets you make a backup copy of a data set on disk while performing an operation on another copy, for example.

Where you are using a Copy stage with a single input and a single output, you should ensure that you set the Force property in the stage editor TRUE. This prevents DataStage from deciding that the Copy operation is superfluous and optimizing it out of the job.

PROPERTIES

Options Category

Force. Set True to specify that DataStage should not try to optimize the job by removing a Copy operation where there is one input and one output. Set False by default.

PROCESSING STAGE

2008-03-16T08:45:00.000-07:00

COMPRESS STAGE

The Compress stage is a processing stage. It can have a single input link and a single output link. Follow this link for a list of steps you must take when deploying a Compress stage in your job.

The Compress stage uses the UNIX compress or GZIP utility to compress a data set. It converts a data set from a sequence of records into a stream of raw binary data.

A compressed data set is similar to an ordinary data set and can be stored in a persistent form by a Data Set stage. However, a compressed data set cannot be processed by many stages until it is expanded, that is, until its rows are returned to their normal format. Stages that do not perform column-based processing or reorder the rows can operate on compressed data sets. For example, you can use the copy stage to create a copy of the compressed data set.

Because compressing a data set removes its normal record boundaries, the compressed data set must not be repartitioned before it is expanded.

PROPERTERTIES

Options Category

Command. Specifies whether the stage will use compress (the default) or GZIP.

PROCESSING STAGE

2008-03-16T08:36:00.000-07:00

Change Capture Stage

PROPERTIES TAB

Change Keys Category

· Case Sensitive. Use this to property to specify whether each key is case sensitive or not. It is set to True by default; for example, the values “CASE” and “case” would not be judged equivalent.

· Sort Order. Specify ascending or descending sort order.

· Nulls Position. Specify whether null values should be placed first or last.

Change Value category

Value. Specifies the name of a value input column (see page 31-1 for an explanation of how Value columns are used). You can use the Column Selection dialog box to select several columns at once if required. Value has the following dependent property:

· Case Sensitive. Use this to property to specify whether each value is case sensitive or not. It is set to True by default; for example, the values “CASE” and “case” would not be judged equivalent.

Options Category

Change Mode. This mode determines how keys and values are specified. Choose Explicit Keys & Values to specify the keys and values yourself. Choose All keys, Explicit values to specify that value columns must be defined, but all other columns are key columns unless excluded. Choose Explicit Keys, All Values to specify that key columns must be defined but all other columns are value columns unless they are excluded.

Log Statistics. This property configures the stage to display result information containing the number of input records and the number of copy, delete, edit, and insert records.

Drop Output for Insert. Specifies to drop (not generate) an output record for an insert result. By default, an output record is always created by the stage.

Drop Output for Delete. Specifies to drop (not generate) the output record for a delete result. By default, an output record is always created by the stage.

Drop Output for Edit. Specifies to drop (not generate) the output record for an edit result. By default, an output record is always created by the stage.

Drop Output for Copy. Specifies to drop (not generate) the output record for a copy result. By default, an output record is always created by the stage.

Code Column Name. Allows you to specify a different name for the output column carrying the change code generated for each record by the stage. By default the column is called change_code.

Copy Code. Allows you to specify an alternative value for the code that indicates the after record is a copy of the before record. By default this code is 0.

Deleted Code. Allows you to specify an alternative value for the code that indicates that a record in the before set has been deleted from the after set. By default this code is 2.

Edit Code. Allows you to specify an alternative value for the code that indicates the after record is an edited version of the before record. By default this code is 3.

Insert Code. Allows you to specify an alternative value for the code that indicates a new record has been inserted in the after set that did not exist in the before set. By default this code is 1.

PROCESSING STAGE

2008-03-16T08:33:00.000-07:00

Change Capture Stage

The Change Capture Stage is a processing stage. The stage compares two data sets and makes a record of the differences. An example before and after data set are given in Parallel Job Developer's Guide. Follow this link for a list of steps you must take when deploying a Change Capture stage in your job.

The Change Capture stage takes two input data sets, denoted before and after, and outputs a single data set whose records represent the changes made to the before data set to obtain the after data set. The stage produces a change data set, whose table definition is transferred from the after data set’s table definition with the addition of one column: a change code with values encoding the four actions: insert, delete, copy, and edit. The preserve-partitioning flag is set on the change data set.

The compare is based on a set of key columns, rows from the two data sets are assumed to be copies of one another if they have the same values in these key columns. You can also optionally specify change values. If two rows have identical key columns, you can compare the value columns to see if one is an edited copy of the other.

The stage assumes that the incoming data is hash-partitioned and sorted in ascending order (this is done automatically if (auto) is selected on the partitioning tab). The columns the data is hashed on should be the key columns used for the data compare. You can achieve the sorting and partitioning using the Sort stage or by using the built in sorting and partitioning abilities of the Change Capture stage.

You can use the companion Change Apply stage to combine the changes from the Change Capture stage with the original before data set to reproduce the after data set.

The Change Capture stage is very similar to the Difference stage.

The stage editor has three pages:

· Stage page. This is always present and is used to specify general information about the stage.

· Inputs page. This is where you specify details about the data set having its duplicates removed.

· Outputs page. This is where you specify details about the processed data being output from the stage.

The General tab allows you to specify an optional description of the stage. The Properties tab lets you specify what the stage does. The Advanced tab allows you to specify how the stage executes. The Link Ordering tab allows you to specify which input link carries the before data set and which the after data set.

PROCESSING STAGE

2008-03-16T08:29:00.000-07:00

Change Apply Stage

The stage editor has three pages:

· Stage page. This is always present and is used to specify general information about the stage.

· Inputs page. This is where you specify the details about the single input set from which you are selecting records.

· Outputs page. This is where you specify details about the processed data being output from the stage.

PROPERTIES:

Change Keys Category

Key.Specifies the name of a difference key input column. This property can be repeated to specify multiple difference key input columns. You can use the Column Selection dialog box to select several columns at once if required. Key has the following dependent properties:

· Sort Order. Specify ascending or descending sort order.

· Nulls Position. Specify whether null values should be placed first or last.

Change Value category

Value. Specifies the name of a value input column. Value has the following dependent properties:

Options Category

Log Statistics. This property configures the stage to display result information containing the number of input records and the number of copy, delete, edit, and insert records.

Check Value Columns on Delete. Specifies that DataStage should not check value columns on deletes. Normally, Change Apply compares the value columns of delete change records to those in the before record to ensure that it is deleting the correct record.

Code Column Name. Allows you to specify that a different name has been used for the change data set column carrying the change code generated for each record by the stage. By default the column is called change_code.

Copy Code. Allows you to specify an alternative value for the code that indicates a record copy. By default this code is 0.

Delete Code. Allows you to specify an alternative value for the code that indicates a record delete. By default this code is 2.

Edit Code. Allows you to specify an alternative value for the code that indicates a record edit. By default this code is 3.

Insert Code. Allows you to specify an alternative value for the code that indicates a record insert. By default this code is 1.

PROCESSING STAGE

2008-03-16T08:23:00.000-07:00

Change Apply Stage

The Change Apply stage is an active stage. It takes the change data set, that contains the changes in the before and after data sets, from the Change Capture stage and applies the encoded change operations to a before data set to compute an after data set. An example before and change data set are given in Parallel Job Developer's Guide. Follow this link for a list of steps you must take when deploying a Change Apply stage in your job.

The before input to Change Apply must have the same columns as the before input that was input to Change Capture, and an automatic conversion must exist between the types of corresponding columns. In addition, results are only guaranteed if the contents of the before input to Change Apply are identical (in value and record order in each partition) to the before input that was fed to Change Capture, and if the keys are unique.

The change input to Change Apply must have been output from Change Capture without modification. Because preserve-partitioning is set on the change output of Change Capture, you will be warned at run time if the Change Apply stage does not have the same number of partitions as the Change Capture stage. Additionally, both inputs of Change Apply are designated as partitioned using the Same partitioning method.

The Change Apply stage read a record from the change data set and from the before data set, compares their key column values, and acts accordingly:

· If the before keys come before the change keys in the specified sort order, the before record is copied to the output. The change record is retained for the next comparison.

· If the before keys are equal to the change keys, the behavior depends on the code in the change_code column of the change record:

· Insert: The change record is copied to the output; the stage retains the same before record for the next comparison.. If key columns are not unique, and there is more than one consecutive insert with the same key, then Change Apply applies all the consecutive inserts before existing records. This record order may be different from the after data set given to Change Capture.

· Delete: The value columns of the before and change records are compared. If the value columns are the same or if the Check Value Columns on Delete is specified as False, the change and before records are both discarded; no record is transferred to the output. If the value columns are not the same, the before record is copied to the output and the stage retains the same change record for the next comparison.
If key columns are not unique, the value columns ensure that the correct record is deleted. If more than one record with the same keys have matching value columns, the first-encountered record is deleted. This may cause different record ordering than in the after data set given to the Change Capture stage. A warning is issued and both change record and before record are discarded, i.e. no output record results.

· Edit: The change record is copied to the output; the before record is discarded. If key columns are not unique, then the first before record encountered with matching keys will be edited. This may be a different record from the one that was edited in the after data set given to the Change Capture stage. A warning is issued and the change record is copied to the output; but the stage retains the same before record for the next comparison..

· Copy: The change record is discarded. The before record is copied to the output.

· If the before keys come after the change keys, behavior also depends on the change_code column:.

· Insert. The change record is copied to the output, the stage retains the same before record for the next comparison. (The same as when the keys are equal.)

· Delete. A warning is issued and the change record discarded while the before record is retained for the next comparison.

· Edit or Copy. A warning is issued and the change record is copied to the output while the before record is retained for the next comparison.

If the before input of Change Apply is identical to the before input of Change Capture and either the keys are unique or copy records are used, then the output of Change Apply is identical to the after input of Change Capture. However, if the before input of Change Apply is not the same (different record contents or ordering), or the keys are not unique and copy records are not used, this is not detected and the rules described above are applied anyway, producing a result that might or might not be useful.

PROCESSING STAGE

2008-03-16T08:13:00.000-07:00

Aggregator Stage

OUTPUT TAB

The Outputs page allows you to specify details about data output from the Aggregator stage. The Aggregator stage can have only one output link.

The General tab allows you to specify an optional description of the output link. The Columns tab specifies the column definitions of incoming data. The Mapping tab allows you to specify the relationship between the processed data being produced by the Aggregator stage and the Output columns. The Advanced tab you to change the default buffering settings for the output link.

OUTPUT MAPPING TAB

For the Aggregator stage the Mapping tab allows you to specify how the output columns are derived, i.e., what input columns map onto them or how they are generated.

In the example the left pane represents the data after it has been grouped and summarized. The Expression field shows how the column has been derived. The right pane represents the data being output by the stage after the grouping and summarizing. In this example key carries the value of the key field on which the data was grouped (for example, if you were grouping by date it would contain each date grouped on). Column am1mean carries the mean of all the values in the group, am1min the minimum value, and am1sum the sum.

PROCESSING STAGE

2008-03-16T08:00:00.000-07:00

Aggregator Stage

Each of these properties has a dependent property as follows:

· Decimal Output. By default all calculation or recalculation columns have an output type of double. This property allows you to specify that the column has an output type of decimal. You can also specify a precision and scale for they type (by default 8,2).

The Inputs page allows you to specify details about the incoming data set.

The General tab allows you to specify an optional description of the input link. The Partitioning tab allows you to specify how incoming data is partitioned before being grouped and/or summarized. The Columns tab specifies the column definitions of incoming data. The Advanced tab allows you to change the default buffering settings for the input link.

The Partitioning tab allows you to specify details about how the incoming data is partitioned or collected before it is grouped and/or summarized. It also allows you to specify that the data should be sorted before being operated on.

By default the stage partitions in Auto mode. This attempts to work out the best partitioning method depending on execution modes of current and preceding stages and how many nodes are specified in the Configuration file.

If the Aggregator stage is operating in sequential mode, it will first collect the data before writing it to the file using the default round Auto collection method.

The Partitioning tab allows you to override this default behavior.

The exact operation of this tab depends on:

· Whether the Aggregator stage is set to execute in parallel or sequential mode.

· Whether the preceding stage in the job is set to execute in parallel or sequential mode.

If the Aggregator stage is set to execute in parallel, then you can set a partitioning method by selecting from the Partitioning mode drop-down list. This will override any current partitioning (even if the Preserve Partitioning option has been set on the previous stage).

If the Aggregator stage is set to execute in sequential mode, but the preceding stage is executing in parallel, then you can set a collection method from the Collection type drop-down list. This will override the default collection method.

The Partitioning tab also allows you to specify that data arriving on the input link should be sorted before being processed. The sort is always carried out within data partitions. If the stage is partitioning incoming data the sort occurs after the partitioning. If the stage is collecting data, the sort occurs before the collection. The availability of sorting depends on the partitioning or collecting method chosen (it is not available for the default auto modes).

If NLS is enabled an additional button opens a dialog box allowing you to select a locale specifying the collate convention for the sort.

You can also specify sort direction, case sensitivity, whether sorted as ASCII or EBCDIC, and whether null columns will appear first or last for each column. Where you are using a keyed partitioning method, you can also specify whether the column is used as a key for sorting, for partitioning, or for both. Select the column in the Selected list and right-click to invoke the shortcut menu.

PROCESSING STAGE

2008-03-16T07:55:00.000-07:00

Aggregator Stage

Calculation and Recalculation Dependent Properties

The following properties are dependents of both Column for Calculation and Summary Column for Recalculation. These specify the various aggregate functions and the output columns to carry the results.

Corrected Sum of Squares. Produces a corrected sum of squares for data in the aggregate column and outputs it to the specified output column.

Maximum Value. Gives the maximum value in the aggregate column and outputs it to the specified output column.

Mean Value. Gives the mean value in the aggregate column and outputs it to the specified output column.

Minimum Value. Gives the minimum value in the aggregate column and outputs it to the specified output column.

Missing Value. This specifies what constitutes a ‘missing’ values, for example -1 or NULL. Enter the value as a floating point number. Not available for Summary Column to Recalculate.

Missing Values Count. Counts the number of aggregate columns with missing values in them and outputs the count to the specified output column. Not available for Recalculate.

Non-missing Values Count. Counts the number of aggregate columns with values in them and outputs the count to the specified output column.

Percent Coefficient of Variation. Calculates the percent coefficient of variation for the aggregate column and outputs it to the specified output column.

Range. Calculates the range of values in the aggregate column and outputs it to the specified output column.

Standard Deviation. Calculates the standard deviation of values in the aggregate column and outputs it to the specified output column.

Standard Error. Calculates the standard error of values in the aggregate column and outputs it to the specified output column.

Sum of Weights. Calculates the sum of values in the weight column specified by the Weight column property and outputs it to the specified output column.

Sum. Sums the values in the aggregate column and outputs the sum to the specified output column.

Summary. Specifies a subrecord to write the results of the calculate or recalculate operation to.

Uncorrected Sum of Squares. Produces an uncorrected sum of squares for data in the aggregate column and outputs it to the specified output column.

Variance. Calculates the variance for the aggregate column and outputs the sum to the specified output column. This has a dependent property:

Variance divisor. Specifies the variance divisor. By default, uses a value of the number of records in the group minus the number of records with missing values minus 1 to calculate the variance. This corresponds to a vardiv setting of Default If you specify NRecs, the operator uses the number of records in the group minus the number of records with missing values instead.

PROCESSING STAGE

2008-03-16T07:49:00.000-07:00

Aggregator Stage

Options Category

Method. The aggregate stage has two modes of operation: hash and sort. Your choice of mode depends primarily on the number of groupings in the input data set, taking into account the amount of memory available. You typically use hash mode for a relatively small number of groups; generally, fewer than about 1000 groups per megabyte of memory to be used.

When using hash mode, you should hash partition the input data set by one or more of the grouping key columns so that all the records in the same group are in the same partition this happens automatically if (auto) is set in the Partitioning tab). However, hash partitioning is not mandatory, you can use any partitioning method you choose if keeping groups together in a single partition is not important. For example, if you’re summing records in each partition and later you’ll add the sums across all partitions, you don’t need all records in a group to be in the same partition to do this. Note, though, that there will be multiple output records for each group.

If the number of groups is large, which can happen if you specify many grouping keys, or if some grouping keys can take on many values, you would normally use sort mode. However, sort mode requires the input data set to have been partition sorted with all of the grouping keys specified as hashing and sorting keys this happens automatically if (auto) is set in the Partitioning tab). Sorting requires a pregrouping operation: after sorting, all records in a given group in the same partition are consecutive.

The method property is set to hash by default.

You may want to try both modes with your particular data and application to determine which gives the better performance. You may find that when calculating statistics on large numbers of groups, sort mode performs better than hash mode, assuming the input data set can be efficiently sorted before it is passed to group.

Allow Null Outputs. Set this to True to indicate that null is a valid output value when calculating minimum value, maximum value, mean value, standard deviation, standard error, sum, sum of weights, and variance. If False, the null value will have 0 substituted when all input values for the calculation column are null. It is False by default.

PROCESSING STAGE

2008-03-16T07:42:00.000-07:00

Aggregator Stage

Grouping Keys Category

Group. Specifies the input columns you are using as group keys. Repeat the property to select multiple columns as group keys. You can use the Column Selection dialog box to select several group keys at once if required. This property has a dependent property:

· Case Sensitive. Use this to specify whether each group key is case sensitive or not, this is set to True by default, i.e., the values “CASE” and “case” in would end up in different groups.

Aggregations Category

Aggregation Type. This property allows you to specify the type of aggregation operation your stage is performing. Choose from Calculate (the default), Recalculate, and Count Rows.

Column for Calculation. The Calculate aggregate type allows you to summarize the contents of a particular column or columns in your input data set by applying one or more aggregate functions to it.

Select the column to be aggregated, then select dependent properties to specify the operation to perform on it, and the output colum to carry the result. You can use the Column Selection dialog be to select several columns at once if required.

Count Output Column. The Count Rows aggregate type performs a count of the number of records within each group. Specify the column on which the count is output.

Summary Column for Recalculation. This aggregate type allows you to apply aggregate functions to a column that has already been summarized. This is like calculate but performs the specified aggregate operation on a set of data that has already been summarized. In practice this means you should have performed a calculate (or recalculate ) operation in a previous Aggregator stage with the Summary property set to produce a subrecord containing the summary data that is then included with the data set. Select the column to be aggregated, then select dependent properties to specify the operation to perform on it, and the output column to carry the result. You can use the Column Selection dialog box to select several columns at once if required.

Default To Decimal Output. The output type of a calculation or recalculation column is double. Setting this property causes it to default to decimal. You can also set a default precision and scale. (You can also specify that individual columns have decimal output while others retain the default type of double.)

Weighting column. This is a dependent property of Count Output Column or Column for Calculation. Configures the stage to increment the count for the group by the contents of the weight column for each record in the group, instead of by 1. Not available for Summary Column for Recalculation. Setting this option affects only the following options:

· Percent Coefficient of Variation.

· Mean Value

· Sum

· Sum of Weights

Uncorrected Sum of Squares

Processing Stage

2008-03-16T06:27:00.000-07:00

Aggregator Stage

The Aggregator stage is a processing stage. It classifies data rows from a single input link into groups and computes totals or other aggregate functions for each group. The summed totals for each group are output from the stage via an output link. Follow this link for a list of steps you must take when deploying an Aggregator stage in your job.

The stage editor has three pages:

· Stage page. This is always present and is used to specify general information about the stage.

· Inputs page. This is where you specify details about the data being grouped and/or aggregated.

· Outputs page. This is where you specify details about the groups being output from the stage.

The aggregator stage gives you access to grouping and summary operations. One of the easiest ways to expose patterns in a collection of records is to group records with similar characteristics, then compute statistics on all records in the group. You can then use these statistics to compare properties of the different groups. For example, records containing cash register transactions might be grouped by the day of the week to see which day had the largest number of transactions, the largest amount of revenue, etc.

Records can be grouped by one or more characteristics, where record characteristics correspond to column values. In other words, a group is a set of records with the same value for one or more columns. For example, transaction records might be grouped by both day of the week and by month. These groupings might show that the busiest day of the week varies by season.

In addition to revealing patterns in your data, grouping can also reduce the volume of data by summarizing the records in each group, making it easier to manage. If you group a large volume of data on the basis of one or more characteristics of the data, the resulting data set is generally much smaller than the original and is therefore easier to analyze using standard workstation or PC-based tools.

At a practical level, you should be aware that, in a parallel environment, the way that you partition data before grouping and summarizing it can affect the results. For example, if you partitioned using the round robin method records with identical values in the column you are grouping on would end up in different partitions. If you then performed a sum operation within these partitions you would not be operating on all the relevant columns. In such circumstances you may want the hash partition the data on the on one or more of the grouping keys to ensure that your groups are entire.

It is important that you bear these facts in mind and take any steps you need to prepare your data set before presenting it to the aggregator stage. In practice this could mean you use Sort stages or additional Aggregate stages in the job.The Properties tab allows you to specify properties which determine what the stage actually does.

More efficient surrogate key processing

2008-03-16T04:29:00.000-07:00

What I'm basically trying to learn is if you all find the performance of most first tier ETL tools in processing surrogate keys (lookup & generation) acceptable.

Even after fine tuning or re-writing the process to be more like a fact build, do you find that a large percentage of the ETL time is spent on this process? This would imply there are significant performance gains to be made in more efficient surrogate key processing.

We've found that most ETL tools are relatively inefficient in processing surrogate keys simply because they are generalist engines. That surrogate key processing occupies 30-50% of the ETL time.

For example, most ETL tools are only able to process surrogate keys in a multi-pass operation. First pass to update the dimensions, second pass to update the facts. Processing the load in a single pass would significantly reduce complexity and improve performance.

Secondly, ETL tools are generally unable to concurrently process multiple small sessions which update the same surrogate keys. As in the instance of multiple sources (or multiple versions of the same source such as different regions) updating in parallel. Or processing dimension updates for multiple transaction types in parallel. Instead, most ETL tools will handle these as one large job, thus processing each source or transaction type in sequence without fully utilising a machine's multiple processors.

Of course, if most companies can truly live with their current performance, it's not an issue. But I'm wondering if they do so because they feel their only option to accepting the inefficiency is to custom design a high performance solution which would be expensive and difficult to maintain. Or to throw more hardware at the process which is also expensive.

Based on a client's high performance needs, we've designed a specialised engine that streamlines surrogate key processing for updating marts and warehouses. I'm trying to understand how much need there is for our product.

Is the need only amongst those companies with an exceptionally large dimension (like customer) or those experiencing exceptionally high volumes of data or arrival rates?

Or is it that most companies just don't realise that they can significantly improve this portion of the ETL process and radically improve their data mart updating. This means they can maximise their existing infrastructure investment and do more with it. For example, move from a monthly to a weekly load or add new sources or marts or querying tools. What thousands of flowers can bloom once this technical bottleneck is removed?