Creating Aggregation Tables with the Summarized Aggregation Table Wizard :: SAS(R) IT Resource Management 3.7: Administrator's Guide

How to Enter General Information about the Aggregation Table

To specify general information about the aggregation table, perform the following steps:

Enter the name of the table in the Name field. This field can contain up to 60 characters. It must be unique within the metadata folder. It must also be unique among all other table names within the application server. A value is required in this field.

Note: The name of this aggregation table is displayed on subsequent pages of the wizard.
(Optional) Enter the identifying information in the Description field. This field can contain up to 200 characters.
(Optional) In the SAS table name field, you can accept the default value, or enter the physical name of the aggregation table.
The IT data mart contains the job in which the Aggregation transformation is located. (It cannot be changed.)
(Optional) In the Location field, you can accept the default value, or enter the metadata path to the metadata folder where the job that contains the Aggregation transformation is stored.

CAUTION:

Use only English characters when entering textual information in the fields of the wizard.

Non-English characters can cause processing errors.

How to Specify Library Information

If this aggregation table is the first target table that is specified for this Aggregation transformation, an additional page of the wizard opens. This page prompts you for the physical target location and for information about the SAS library where the data is to be stored. When this page opens, the following fields are already specified with default values. If you want to modify these fields, perform the following steps:

In the Name field, enter the name of the library that contains all aggregation tables that are associated with the Aggregation transformation that you are working with. This field can contain up to 60 characters. A value is required in this field.
In the Description field, enter the information that describes the library. It can contain no more than 200 characters. It is an optional field.
In the Server field, enter the application server that you specified for the IT data mart that you are working with. The default application server is the SASITRM server. A value is required in this field.
In the Libref field, enter the name that is temporarily associated with the library. The libref or an aggregation library is a concatenation of AGG and a random number (for example, AGG27040). A value is required in this field. The libref name must be unique within the application server.
Note: The following naming rules apply to the libref:
- The libref can consist of no more than eight characters.
- The libref must begin with a letter (A through Z) or an underscore (_).
- The remaining characters of the libref must be letters (A through Z), numerals (0 through 9), or an underscore (_). The libref cannot contain spaces.
In the Path specification field, enter the location where the tables for this library are to be stored. You must specify a location for the tables that is within the IT data mart that is specified in the IT Data Mart field.

Enter this field manually or click Browse to select a location from the Select a Directory dialog box that displays the directory structure on the server's file system. A value is required in this field.

Note: On UNIX and on z/OS using zFS locations, you can use paths that contain symbolic links. Using symbolic links would be useful if you want to retain flexibility for changing the real physical location of libraries. See the UNIX ‘ln’ command for additional details.

How to Select a Filter and Enable Data Duplication Checking

Filters can limit the amount of data that is input to an aggregation table. To select a filter and a column for data-duplication checking, perform the following steps:

From the drop-down list in the Select filter field, select the filter that you want to use. The selected filter is displayed in the Select filter field and its corresponding expression is displayed in the Expression field.
The Expression field displays the formula that defines the filter. It cannot be changed from this location. For information about how to modify the expression field of a filter, see Modify a Filter of an Aggregation Transformation.

If you want to create a new filter to use for your aggregation table, perform the following steps:

Click the Add New button to open the Add Filter dialog box.
In the Name field, enter the name of the new filter. The name field can contain no more than 60 characters.
In the Expression field, enter the formula.
Click OK.

Note: For more information about how to work with filters, see Working with Aggregation Transformation Filters .

Also available on this page is the box to select the Enable data duplication checking for the aggregation table. Select this box to indicate that you want to aggregate only data items from the source table that have a value for LSTPDATE that is newer (greater in value) than the most recent (greatest) value of the LastUpdated column in the aggregation table. If selected, this option prevents duplicate data items from being aggregated into the target table.

Note: The LSTPDATE column represents the last date and time that data was staged. This option is enabled only if LSTPDATE is found in the source table. (By default, this option is deselected.)

Specify Aging Columns

Managing Your Data: Aging and Purging Options

SAS IT Resource Management provides several ways of managing the amount of data that is kept in the aggregation tables of your IT data mart. You can apply the aging and purging functions to both simple and summarized aggregations. The selection of the aging and purging attributes for a simple aggregation table or a summarized aggregation table can be performed on the Specify aging page of the wizard.

Purge target table before loading new data

Purging the data from the target table before the new data is loaded.
Perform aging for target table

You can specify whether and how you want to age your data when you create the Aggregation table.

By entering a value for the Age limit in days, you are specifying the age range that data is to be present in the table. The minimum number of days is 1; the maximum number of days is 9999.

Note: Columns that are used for aging are DATETIME columns. For these columns, one day of aging extends over 24 hours of data collection, which might extend over one or more days.

You are not specifying that the data is to be aged out after that number of days. Instead, the value that you specify is the number of days of data that you want to keep. Calculate what is aged out by subtracting the value of the Age limit in days from the latest date of the new or existing data. The resulting date is the date before which data will age out. For example, in the following figure, Day 9 is the latest date of the data. The Age limit in days is seven.

Example of How Data Is Aged Out

All data from Day 2 and earlier is aged out. All data from Day 3 through Day 9 is kept. However, no data was loaded on Day 4 and Day 8. Therefore, only five days of data are kept, namely the data for Day 3, Day 5, Day 6, Day 7, and Day 9.

Note: If you reduce the aging value, the change takes effect the next time the job is run, even if there is no new data.

How to Specify Purging Criteria

To purge the target table before you load new data, check the corresponding check box. This action removes the data that was processed in previous executions of the job.

How to Specify Aging Criteria

You can specify whether and how you want to age your data when you define the aggregation target table. Based on these specifications, data is kept in the aggregation table or aged out of it. To specify aging criteria for the target table, perform the following steps:

To perform aging for the target table, check the corresponding check box. This action enables you to select the column to be used for aging and to specify the format of that column.
- The Column field displays a list of the columns from the source table that are available to use for aging the data. From the drop-down list, select the column that you want to use. It is displayed in the Column field. The format of the selected field is displayed.
  
  Note: If you want to specify percent change or moving statistic columns, you must select an aging column. SAS IT Resource Management uses that aging column in order to calculate the period-to-period changes for that column. The selected aging column is automatically added to the list of class columns for a summarized aggregation table.
- The Date or Datetime option is automatically selected, based on the contents of the column that you selected for aging. Changing this option is permissible, but it is not recommended.
  
  Note: Specify the option that reflects the type of column that you chose. For DATE values, the aging counts by days. For DATETIME values, the aging counts by the number of seconds in a day.
Use the spinner to select the number of days age limit.
To specify the completed day, check the corresponding check box. This action enables you to select the column to use to determine whether a day is complete. (This option is enabled if aging has been specified for this aggregation table and the source table has a column called DATETIME.)

This feature is useful only for tables where the aging column represents a DAY or less. For example, this feature is not useful for WEEKDATE aggregations.

Note: If the Apply completed day option is deselected on an existing aggregation table that previously selected that option, the rank columns of that table are analyzed. If they depend on Completed Day being selected, a message is displayed that lists the rank columns that would be affected. The deselection of the Completed Day option is not permitted.
Use the Cut off time field to specify when a day is completed. Use the spinners to specify the hours, minutes, and whether the time is a.m. or p.m.

Specify Class and ID Columns

About Specifying Class and ID Columns

On the Specify class and ID columns page, you can specify the columns from the source table that are to be included in the target aggregation table as class columns and as ID columns.

A class column is used to group or classify data. For example, the value of a class column could be a device address.

At least one column must be selected as a class column.

Note: When designing aggregation tables, select class columns carefully. For each unique value of each class column, an observation is created in that table. To ensure that aggregation tables have meaningful observations, select class columns for which the values are not highly variable.

Note: If aging is specified for this target aggregation table, then the aging column is automatically added to the list of class columns. The aging column cannot be deleted from the list of class columns. If aging is turned off, the column can be removed from the list of class columns.
An identification (ID) column contains an alternate identifier for the data in a class column. For example, the value of the corresponding ID column could be the name of a particular device.

The value that is assigned to the ID column is the last value that is read into the group that is defined by the unique combination of class columns.

ID columns are not required.

Note: If an input column is given a role as a class column, ID column, or a statistic, it cannot simultaneously be assigned a different role. Therefore, it is not available for selection. If all available columns are assigned as class or ID columns, then no columns are available to be used as statistic, percentile, percent change, moving statistics, or rank columns. In that case, the wizard does not display the pages that enable you to specify those columns.

How to Specify Class and ID Columns

To specify class or ID columns, click New to open the Add New Class and ID Columns window.

To select class columns for the target aggregation, highlight the columns in the Available columns panel that you want to select as class columns. Then click the right arrow to move those columns into the Selected class columns.
To select ID columns for the target table, highlight the columns in the Available columns panel that you want to select as ID columns. Then click the right arrow to move those columns into the Selected ID columns.

You can select the entire list of available columns by clicking the double right arrow. Back arrows can be used to deselect one, several, or all columns.

Tip

After your data has been aggregated, you can change the class list. If you remove columns from the class list, the existing data in the summarized aggregation table is merged again so that it matches the new class list. If you add new columns to the class list, then these new columns in the table will have a missing value (for numeric) or blank value (for character) in the existing data.

Note: If you add a column to a class list that is associated with a source table that is used in a join operation, then you must also ensure that the column is also included in the class or ID list of the destination table. If not, an error message is displayed during code generation that identifies the columns that are not present in the destination table. You cannot remove a class or ID column if it is used to satisfy a join column in another aggregation table. Similarly, you cannot remove a class or ID column if it is used as the source column of a rank column in this aggregation table.

How to Modify the Fields of a Class or ID Column

After the class and ID columns have been selected, you can modify the name, label, and format for any of the input columns that you are including in the target aggregation table.

To modify the Target Column Nameand Target Column Label, enter the new value in the field that you want to change. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.
To modify the Target Column Format, double-click in the field and select the new format from the drop-down list.

Specify Statistics Columns

About Specifying Statistics

On the Specify statistics page, you can calculate or delete one or more statistics on analysis columns that you selected on the previous page. These columns are displayed in a grid format, which contains a row for each analysis column. The grid contains the following columns: Analysis Column and Statistics, Weight By, Target Column Name, Target Column Description, and Target Column Format. Except for the Analysis Column and Statistics column, the columns of the grid are blank until the statistic is specified. (The Analysis Column and Statistics column displays the names of the selected analysis and statistics columns.)

Note: For the list of statistics that SAS IT Resource Management enables you to create, see Calculations Performed by SAS IT Resource Management.

How to Create Statistics for an Analysis Column

To create statistics for an analysis column, perform the following steps:

Click New, which opens the Add New Statistics window.
In the Select analysis variables panel, check the box next to the analysis column for which you want to specify statistics. (Checking multiple analysis columns enables you to specify the same statistics for all the analysis columns that you selected. Otherwise, check an analysis column and specify the statistics for it individually.)
To specify unweighted statistics, in the Statistic Types panel, check the statistics that you want to create. The counter that is next to All Selected Statistics displays the number of statistics that you selected.
To specify weighted statistics, in the Weighted Statistic Types panel, select the column that you want to use from the Weighted by drop-down list. Then check the weighted statistics that you want to create.
In the following display, Count, Geometric Mean, Weighted Geometric Mean, and Weighted Mean statistics for the DATETIMEONEMIN analysis column are to be created.

Add NewStatistics Window
Click OK to add the requested and any underlying statistics to the grid under the analysis column. The row displays these statistics along with its attributes, such as Weight By, Target Column Name, Target Column Description, and Target Column Format. These fields contain the default values that are associated with the statistics. To modify a field on this grid, highlight it, and change it as needed by entering the revision in the field.
The following display shows the newly added Count, Geometric Mean, Weighted Geometric Mean, and Weighted Mean statistics that are generated for the DATETIMEONEMIN analysis column.

Specify Statistics Window with Newly Added Statistics

How to Modify a Statistic Column

You can modify only the Target Column Name, Target Column Description, and Target Column Format fields on the grid. (The Weight By column cannot be modified.)

To modify a field, enter the revision into the field. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.

Note: For best results, do not change the Target Column Format column. If you do change it, make sure that the value that you enter in the Target Column Format is a valid SAS format.

How to Delete a Statistic Column

To delete a statistic column from the list of statistic columns that are to be created for an analysis column, perform the following steps:

Select the statistic. ( Deleting a statistic column that uses another (underlying) statistic column does not delete that underlying statistic column.)
Click Delete.

Note: If the column that you want to delete is used to calculate another column in this table or a join column in another table, a dialog box is displayed showing where the column is used. You must remove those columns before you can delete this column.

How to Delete Multiple Statistics Columns

In some instances, you might want to delete multiple columns at the same time. For example, you might want to delete all of the statistic columns for a particular analysis column, or only some of them. You can delete all of the statistic columns for a particular analysis column by either selecting all the statistic columns individually, or by selecting the analysis column itself. Either action accomplishes the same purpose.

However, if your selection of columns to delete includes an analysis column and some, but not all, of the statistic columns for that analysis column, a message is displayed. The message states that you selected conflicting choices and that no columns are to be deleted. The message advises you to select either an analysis column or one or more statistics columns.

Specify Percentiles Columns

About Specifying Percentiles

Percentile measurements in SAS IT Resource Management enable IT organizations to quantify and analyze utilization, availability, performance, and capacity characteristics of IT infrastructure components. These measurements can be compared with other components or other time periods in the infrastructure so that IT organizations can prioritize and resolve current day and potential problems.

Note: Jobs that calculate percentile columns might require longer processing times because the data might have to be read multiple times. For best results, minimize the number of percentile columns that you specify.

On the Specify percentiles page, you can request that multiple percentiles be generated for an input metric. Percentile information is displayed in a grid format, which contains a row for each percentile that is being calculated. The grid contains the following columns: Input Column and Percentiles, Round To, Target Column Name, Target Column Description, and Target Column Format.

Note: Each requested percentile for a given analysis column must use the same precision of granularity, that is, it must specify the same rounding. If you specify a different precision of granularity for the same analysis column, a message is displayed indicating that the granularity will be changed to that of the first analysis column.

Percentiles are calculated from the rounded values using the same guidelines as PROC UNIVARIATE with PCTLDEF=5, which is the default for UNIVARIATE. The calculated percentile value is one of the discrete rounded values, unless the requested percentile position falls between two values. In that case, the calculated value is the average of the two points.

How to Create Percentiles for an Analysis Column

To create percentiles for an analysis column, perform the following steps:

Click New, which opens the Add New Percentiles window.
In the Select analysis columns panel, check the box next to the analysis column for which you want to specify percentiles. (Checking multiple analysis columns enables you to specify the same percentiles for all the analysis columns that you selected. Otherwise, check an analysis column and specify the percentile characteristics for it individually.)
In the Requested percentile field, enter the numeric value of the percentile that you want to calculate. This value applies to all the columns that you checked. The percentile must be greater than or equal to zero and less than or equal to 100.

Note: The zero percentile would be the same as the MINIMUM statistic, rounded to the granularity that is requested for the percentile. The 100th percentile would be the same as the MAXIMUM statistic, rounded to the granularity that is requested for the percentile.
In the Round to nearest field, enter the numeric value of the precision of granularity that you want to calculate. This value applies to all the columns that you checked.
Click OK to add the requested percentiles to the grid. The row displays these percentiles along with their attributes, such as Round To, Target Column Name, Target Column Description, and Target Column Format.

How to Modify a Percentile Column

You can modify only the Target Column Name, Target Column Description, and Target Column Format fields on the grid. (The Round To column cannot be modified.)

To modify a field, enter the revision into the field. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.

Note: For best results, do not change the Target Column Format column. If you do change it, make sure that the value that you enter in the Target Column Format is a valid SAS format.

How to Delete a Percentile Column

To delete a percentile column from the list of columns that is to be created for an analysis column, perform the following steps:

On the grid, select the percentile.
Click Delete.

Note: If the column that you want to delete is used to calculate another column in this table or a join column in another table, a dialog box is displayed showing where the column is used. You must remove those columns before you can delete this column.

How to Delete Multiple Percentile Columns

In some instances, you might want to delete multiple columns at the same time. For example, you might want to delete all of the percentile columns for a particular analysis column, or only some of them. You can delete all of the columns for a particular analysis column by either selecting all the columns individually, or by selecting the analysis column itself. Either action accomplishes the same purpose.

However, if your selection of columns to delete includes an analysis column and some, but not all, of the percentile columns for that analysis column, a message is displayed. The message states that you selected conflicting choices and that no columns will be deleted. The message advises you to select either an analysis column or one or more percentile columns.

Specify Percent Change Columns

About Specifying Percent Change

You can calculate the percent change of a resource’s given statistic or percentile in specified time periods and compare the results from one time period to another. This enables you to see the percent change of the statistic or percentile column for a resource in one time period. You can then compare it to the same statistic or percentile for this same resource in another time period. The percent change metric is useful for capacity planning.

Percent change information is displayed in a grid format, which contains a row for each statistic that is being calculated. The grid contains the following columns: Statistic and Percentile, Weight By, Input Column, Target Column Name, Target Column Description, and Target Column Format.

Tip

If you want to specify percent change for a column, you must have selected an aging column. SAS IT Resource Management uses that aging column in order to calculate the period-to-period changes for that column.

All statistics, percentiles, percent change, moving statistics, ranks, and computed columns are recomputed each time the aggregation transformation is executed.

How to Specify Percent Change for a Statistics Column or a Percentile Column

To calculate the percent change for a statistic or percentile, perform the following steps:

Click New, which opens the Add New Percent Change Columns window.
From the list under Select columns, select the statistics and percentile columns for which you want to calculate the percentage of change. Use the arrow to transfer those statistics to the Selected columns.
Click OK. The selected columns are added to the Percent Change grid, and you are returned to the Specify Percent Change page.

How to Modify a Percent Change Column

You can modify only the Target Column Name, Target Column Description, and Target Column Format fields on the grid. (The Weight By cannot be modified.)

To modify a field, enter the revision into the field. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.

Note: For best results, do not change the Target Column Format column. If you do change it, make sure that the value that you enter in the Target Column Format is a valid SAS format.

How to Delete a Percent Change Column

To remove the calculation of percent change for a statistics or percentile column, perform the following steps:

On the percent change grid, select the statistics or percentile column.
Click Delete.

Note: If the column is used to calculate another column in this table or a join column in another table, a dialog box is opened that is displayed showing where the column is used. You must remove those columns before you can delete the percent change column.

How to Delete Multiple Percent Change Columns

In some instances, you might want to delete multiple columns at the same time. For example, you might want to delete all of the percent change columns for a particular analysis column, or only some of them. You can delete all of the columns for a particular analysis column by either selecting all the columns individually, or by selecting the analysis column itself. Either action accomplishes the same purpose.

However, if your selection of columns to delete includes an analysis column and some, but not all, of the percent change columns for that analysis column, a message is displayed. The message states that you selected conflicting choices and that no columns will be deleted. The message advises you to select either an analysis column or one or more percent change columns.

Specify Moving Statistic Columns

About Specifying Moving Statistics

Moving statistics enable IT organizations to identify and establish baseline and threshold measurements for the many performance measurements that they want to measure. This statistic can also be used to monitor characteristics of the SAS IT Resource Management system. For example, it can help monitor the growth in the number of systems for which data is analyzed. Similarly, it can help monitor the volume of reports that are created by each SAS IT Resource Management report job (if measures on those items are retained and managed using SAS IT Resource Management).

On the Specify moving statistic page, you can request that multiple moving statistics be generated for a statistic or a percentile. (Moving statistics cannot be created directly on an input column.) The following types of moving statistics can be created:

Moving Average

an arithmetic mean computed on a subset (typically the N-most recent points) of data instead of the entire population of data. Moving averages are used to smooth out short-term fluctuations, and highlight longer-term trends.

Moving Standard Deviation

a standard deviation computed on a subset (typically the N-most recent points) of data instead of the entire population of data.

All moving statistics are recomputed each time the aggregation transformation is executed.

Moving statistic information is displayed in a grid format, which contains a row for each moving statistic that is being calculated. The grid contains the following columns: Statistic and Percentile, Input Column, Number of Periods, Moving Statistic Type, Target Column Name, Target Column Description, and Target Column Format.

How to Create Moving Statistics for a Statistic or Percentile Column

To create moving statistics for a statistic or percentile column, perform the following steps:

Click New, which opens the Add New Moving Statistic Columns window.
In the Select columns panel, check the box next to the statistic or percentile column for which you want to specify moving statistics. (Checking multiple statistic or percentile columns enables you to specify the same moving statistics for all the columns that you selected. Otherwise, check a statistic or percentile column and specify the moving statistics and its characteristics individually.)
Check the Moving Average box if you want to create a moving average for each of the selected columns. Moving averages are created for all the columns that you checked.
Check the Moving Standard Deviation box if you want to create a moving standard deviation for each of the selected columns. Moving standard deviations are created for all the columns that you checked.
In the Number of periods field, enter the number of periods for which you want to calculate the selected moving statistic. This value applies to all the columns that you checked.
Click OK to add the requested moving statistics to the grid. The row displays these moving statistics along with their attributes, such as Number of Periods, Moving Statistic Type, Target Column NameTarget Column Description, and Target Column Format.

Note: If more than one moving statistic is specified for the same column, a number is appended to the Target Column Name. This number is incremented by one for every new moving average that is specified for a column. For example, if you specified a moving average column of name CPUBusyPct_MA, then if another moving average column is requested for CPUBusyPct, then by default it should be called CPUBusyPct_MA1.

How to Modify a Moving Statistics Column

You can modify only the Target Column Name, Target Column Description, and Target Column Format fields on the grid. (The other columns cannot be modified.)

To modify a field, enter the revision into the field. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.

Note: For best results, do not change the Target Column Format column. If you do change it, make sure that the value that you enter in the Target Column Format is a valid SAS format.

How to Delete a Moving Statistics Column

To delete a moving statistics column from the list of columns that are created for an analysis column, perform the following steps:

On the grid, select the moving statistics column.
Click Delete.

Note: If the column that you want to delete is used to calculate another column in this table or a join column in another table, a dialog box is displayed showing where the column is used. You must remove those columns before you can delete this column.

How to Delete Multiple Moving Statistics Columns

In some instances, you might want to delete multiple columns at the same time. For example, you might want to delete all of the moving statistics columns for a particular analysis column, or only some of them. You can delete all of the columns for a particular analysis column by either selecting all the columns individually, or by selecting the analysis column itself. Either action accomplishes the same purpose.

However, if your selection of columns to delete includes an analysis column and some, but not all, of the moving statistics columns for that analysis column, a message is displayed. The message states that you selected conflicting choices and that no columns will be deleted. The message advises you to select either an analysis column or one or more moving statistics columns.

Specify Ranking Columns

About Ranking

You can specify ranking for class, ID, statistics, percentiles, percent change, and moving statistics columns. The ranking of data determines the position of the value of a column or a value within a selected group of class columns. You can specify that ranks be computed on class columns, ID columns, or statistical columns. Only numeric columns can be ranked.

SAS IT Resource Management computes a dense rank, which means that the rank numbers are sequentially numbered, without gaps. The ranks start with 1 and are incremented by 1. Ties receive the same rank. For more information about ranking, see “The RANK Procedure” chapter in the Base SAS Procedures Guide.

Ranks are computed before the calculation of computed columns so that ranks can be used in the calculation of a computed column.

On the Specify ranking page, you can specify the columns that you want to rank and how these columns should be ranked. You can also delete a rank column. Rank columns are displayed in a grid format, which contains a row for each defined rank column. The grid contains the following columns: Input Column, Target Column Name, Target Column Description, Rank Order, Rank Grouping, and Completed Days, which are all initially blank until you add a ranking specification for a column.

Note: Jobs that calculate rank columns might require longer processing times because the data might have to be read multiple times. For best results, minimize the number of rank columns that you specify.

How to Specify Ranking for a Column

To specify ranking for a column, perform the following steps:

Click New to open the Add New Rank window.
To rank columns over all the data, click the corresponding option. As shown in the following display, the list of columns with this option includes numeric class and ID columns that you selected from the source table. The list of columns also includes the statistics, percentile, percent change, and moving statistic columns that you specified on the previous page.

Rank over All the Data

Then, underSelect columns to rank, check the columns that you want to rank.
Alternatively, to rank columns over specific class columns, click the corresponding option. As shown in the following display, the list of columns with this option includes only the statistics columns that you specified on the previous page.

Rank over Specific Columns

In the Rank over the following class columns box, check the class columns over which you want to rank selected columns.

Then, in the Select columns to rank column, check the columns that you want to rank.

Note: You can select any class columns over which to rank the selected statistic. However, you should leave at least one class column deselected. The class columns that are not selected are the columns over which the statistic is to be ranked. For example, you might have an aggregation table of average CPU Utilization, and the class columns are DAYDATE, DOMAIN, and MACHINE. If you request a descending rank of Average CPU Utilization using a Rank Grouping of DAYDATE and DOMAIN, then that request would result in a ranking of Average CPU utilization for all machines within the DOMAIN for the given DAYDATE. Thus, the observation where the rank value is 1 would be the machine with the highest average CPU utilization, ranked separately for each unique DOMAIN and DAYDATE combination. Alternatively, if you request a descending rank of Average CPU Utilization using a Rank Grouping of DAYDATE, DOMAIN, and MACHINE, then that request would result in a ranking of Average CPU utilization for a single row of data. That ranking would not be useful.
In the Rank Order box, click Ascending or Descending.

Note: In a descending rank, the largest value gets a rank of 1. The next largest value gets a rank of 2, and so on. In an ascending rank, the smallest value gets a rank of 1, and the next smallest value gets a rank of 2, and so on.
To rank only completed days, check the corresponding box.

Note: This option is available only if the Apply completed days option was selected on the Specify aging page of this wizard. Furthermore, if a rank is defined as having completed days, then you cannot disable the Apply completed days option, and you cannot disable aging.
Click OK to add the rank to the list of rank columns.

How to Modify a Rank Column

You can modify only the Target Column Name and Target Column Description fields on the grid. (The Rank Column, Rank Grouping, and Completed Day columns cannot be modified.)

To modify a field, enter the revision into the field. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.

How to Delete a Rank Column

To delete a rank column, perform the following steps:

Select the column that you want to delete.
Click Delete.

You cannot delete a rank column if it is used as the source for a join column in another aggregation table. You must first delete the join column from the other table before deleting the rank column.

CAUTION:

Rank columns are often used as filters in information maps. If you remove a rank column that is used as a filter in an information map, then the information map, the information map job, and the report jobs that depend on that filter might fail to run.

To avoid processing errors, delete any reference to a deleted rank column in any information map, report, or transformation that uses that column.

How to Delete Multiple Rank Columns

In some instances, you might want to delete multiple columns at the same time. For example, you might want to delete all of the rank columns for a particular analysis column, or only some of them. You can delete all of the columns for a particular analysis column by either selecting all the columns individually, or by selecting the analysis column itself. Either action accomplishes the same purpose.

However, if your selection of columns to delete includes an analysis column and some, but not all, of the rank columns for that analysis column, a message is displayed. The message states that you selected conflicting choices and that no columns will be deleted. The message advises you to select either an analysis column or one or more moving statistics columns.

Specify Join Columns

About Join Columns

The following rules govern the join function:

Simple aggregations cannot serve as the source of a join column.
Only ID, statistic, percentile, percent change, moving statistics, and rank columns can be joined from a summarized aggregation.
A simple aggregation (the destination) can join columns from a summarized aggregation (the source) if and only if every class column in the source table exists as a column in the destination table.
A summarized aggregation can join columns from another summarized aggregation if and only if the complete class list of the source table is a proper subset of the union of class and ID list of the destination table.

Note: Jobs that include joined columns might require lengthy processing time. For best results, minimize the number of joined columns that you specify.

Information about join columns is displayed in a grid format, which contains a row for each column that is to be joined. The grid contains the following columns: Source Columns, Target Column Name, Target Column Description, and Target Column Format. The entries in the grid are initially blank.

On the Specify join columns page, you can specify columns from other aggregation tables in this Aggregation transformation to join with columns in this target table.

CAUTION:

If you changed the list of class columns for an aggregation table, you might have made the join invalid.

This case would break the fourth rule of the rules that govern a join.

Note: The Specify join columns page is available only if there are columns from separate target tables that are eligible for joining.

If you use the Next button to traverse the pages in the Summarized Aggregation wizard, the Join Columns page does not appear if there are no tables eligible to join.
If you use the Edit feature to jump directly to the Join Columns page, an error message appears if there are no tables eligible to join.

How to Specify Join Columns

To add a join column from an existing table to the table that you are specifying, perform the following steps:

Click New. The Add New Join Columns dialog box appears.
Select columns from the list in the Available Columns. Use the arrow to transfer those columns to the Selected Columns panel and click OK. The selected columns are entered automatically into the appropriate cells of the grid, along with their corresponding default values for Target Column Name, Target Column Description, and Target Column Format.

Note: By default, the first character of the name of the resulting join column (the Target Column Name) is the first letter of the aging column of the source table. So the join column default name would be: <first character of aging column of source table>_<column name from source table>.

How to Modify a Join Column

You can modify only the Target Column Name, Target Column Description, and Target Column Format fields on the grid.

To modify a field, enter the revision into the field. The Target Column Name can be any legal SAS column name. However, it must be unique within its table.

Note: For best results, do not change the Target Column Format column. If you do change it, make sure that the value that you enter in the Target Column Format is a valid SAS format.

How to Remove Join Columns

To delete join columns, perform the following steps:

Highlight the join column in the grid of columns to be joined.
Click Delete.

How to Delete Multiple Join Columns

In some instances, you might want to delete multiple columns at the same time. For example, you might want to delete all of the join columns from a particular table, or only some of them. You can delete all of the columns for a particular table by either selecting all the columns individually, or by selecting the table itself. Either action accomplishes the same purpose.

However, if your selection of columns to delete includes a source table and some, but not all, of the join columns for that table, a message is displayed. The message states that you selected conflicting choices and that no columns will be deleted. The message advises you to select either a source table or one or more join columns.

Specify Computed Columns

About Computed Columns

A computed column stores the results of computational tasks. For example, a computed column can be used to convert unit measurements so that all the data uses the same standardized units of measurement. You can add, modify, or delete computed columns on the Add or modify computed columns page. This page is available in the Summarized Aggregation Table wizard or the Simple Aggregation Table wizard. By default, a computed column is numeric.

Note: If you change a computed column, the values for the computed column are recalculated for that column in all rows of the data when the job is executed. (This recalculation is performed even if no new data is added to the aggregation table.)

The Specify computed columns page displays a grid that shows the following fields for each computed column: Name, Expression, Description, Length, Type, Informat, and Format.

Tip

A computed column called TimePeriod is automatically added to every simple and summarized aggregation table. Its value is always the same as the aging column, and you can refer to TimePeriod instead of referring to the aging column directly. This technique simplifies reporting, especially in those instances where you do not know the name of the aging column. (If no aging is specified for the target table, then the field is set to missing.) The expression for this column is defined when the aggregation wizard is completed. For example, if the aging column of an aggregation table is DAYDATE, the expression for the TimePeriod computed column is DAYDATE and its expression is TIMEPERIOD=DAYDATE;.

On the Specify computed columns page, you can create, modify, or delete computed columns.

How to Add a Computed Column

To add a computed column to the aggregation table, you must use the Edit function. (The New and Delete buttons are not available on the Specify computed columns page of the wizard when the aggregation table is being created.)

Right-click the existing aggregation table to which you want to add a computed column. On the Properties dialog box, click Edit. From the list of pages, select the Computed Columns entry and perform the following steps:

Click New. A new line is displayed on the grid that contains default values for the following attributes: name, length, type, informat, and format. (The default value for the name of the column is “Untitledn”, where n is the nth computed column for this aggregation.)

Specify Computed Columns
Enter the values that you want to use for the Name, Expression, and Description fields. You can enter values for the Length, Type, Informat, and Format fields, or you can accept the default values for these fields.

Note: You can use a SAS macro in a formula expression or in the expression for a computed column. The SAS macro definition needs to be available to the SAS session that executes the staging or aggregation job that populates the computed column. For example, you might need to add a new autocall library using the SASAUTOS SAS option in the SAS configuration.

How to Modify a Computed Column

To modify a computed column, perform the following steps:

Select the column from the drop-down list. You can change the values of this column as needed.
In the Name field, you can change the name of your computed column. The name of the computed column can be any legal SAS name. It must be unique within the target table.
In the Expression field, you can specify a modified computation for this computed column by entering it in this field or by using the Expression Builder function. Double-click in the field and click ... to open the Expression Builder window.

Expression Builder Dialog Box with IT Formulas Tab
Many functions are available from the Functions and Data Sources tabs of this window. In addition, the Expression Builder window provides another tab to assist you in defining the computation for this column—the IT Formulas tab.
- The Functions tab of the Expression Builder window lists a variety of functions, conversions, and other actions that you can specify in your expression. Select the function that you want to use in the expression and click Insert.
- The Data Sources tab of the Expression Builder window lists all the columns that are generated in the aggregation table output. Select the column that you want to use in the expression and click Insert.
  
  For simple aggregations, the Data Source tab displays all the output columns that you selected for the simple aggregation table. For the summarized aggregation table, the Data Source tab displays all the class, ID, statistic, percent change, rank, and join columns that you selected for the summarized aggregation table. In addition, it displays the system-generated LastUpdated, CompletedDay, and ContribCount columns.
  
  All column names that are used in the computation are the output column names. For example, if you choose to use an input column named SYSTEM as a class column, you might rename it MACHINE. Then, if you want to use that column when defining a computed column, you should refer to that column as MACHINE, not SYSTEM.
- The IT Formulas tab lists the formulas that you have defined as well as the formulas that are supplied with SAS IT Resource Management. Select the formula that you want to use and click Insert.
An rValue expression is an expression that consists of code that is appropriate only for the right-hand side of an assignment statement. In addition to the conventional rValue expression, SAS IT Resource Management also supports more complex expressions. For example, your expression can use SAS code that might include loops, IF statements, and so on. This code must be written in valid SAS DATA step syntax.

Note: You can use a SAS macro in a formula expression or in the expression for a computed column. The SAS macro definition needs to be available to the SAS session that executes the staging or aggregation job that populates the computed column. For example, you might need to add a new autocall library using the SASAUTOS SAS option in the SAS configuration.

When you are satisfied with the expression that you defined, click OK in the Expression Builder window. This action closes that window and places the expression in the appropriate field of the computed column.

Note: If invoked from the Filters tab of an Aggregation transformation, the Expression Builder window does not contain the IT Formulas tab. This tab is available only when the window is invoked from the Add or modify a computed column page of the wizards for summarized and simple aggregations.

For more information about the Expression Builder window, click the F1 key or Help from within that window.
In the Description field, you can change the descriptive information about the computed column.
In the Type field, you can change the type of the computed column. Double-click in the field and use the arrow to display the drop-down list of valid types (either character or numeric). From this list, you can select the appropriate type for this computed column. Fields that are the result of a calculation should be specified as numeric. By default, the computed column is numeric.
In the Length field, you can change the length of the computed column. For numeric type columns (not character type columns), this numeric value must be from 2 through 8. (Reducing the length of a numeric computed column might introduce precision errors.) For character computed columns, the length can be from 1 through 32767.
The Informat field is not used by the Aggregation transformation.
In the Format field, you can change the SAS format of the computed column. Double-click in the field and from the drop-down list of valid formats, select the format for this computed column.

You can also enter a format name if it will be available when you execute the job or when you view the data in SAS.

How to Delete a Computed Column

Deleting a computed column removes it from the metadata for a table. The column is no longer to be generated when the Aggregation transformation job is run. However, deleting a computed column does not remove the column from the physical tables of aggregated data that have already been generated.

Note: You must use the Edit function to add or delete a computed column. (The New and Delete buttons are not available on the Specify computed columns page of the wizard when the aggregation table is being created.)

To delete a computed column from an aggregation table, right-click the aggregation table. On the Properties dialog box, click Edit. Select the Computed Columns entry and perform the following steps:

Select the computed column that you want to delete.
Click Delete.

When you have finished working with your computed columns, click Next to continue to the final page of the wizard.

Note: After the job is redeployed and executed, changes to the computed columns will be reflected in the physical table. For information about redeploying jobs, see Redeploy All Jobs on the Server.

How to Complete the Specification of the Summarized Aggregation Table

The final page of the wizard displays the details for the summarized aggregation table that you specified. If you are satisfied with your choices, click Finish. Click OK to return to the process flow diagram.

In the metadata, aggregation tables are stored in sorted order, with an asserted SORTEDBY= data set option. The SORTEDBY assertion reflects that the data is stored in the following order:

alphabetical list of the remaining class columns
aging column

BY-group processing can be performed using the class list, if the BY-group list matches the same SORTEDBY assertion. With SAS IT Resource Management, you can leverage SAS Data Integration Studio to define additional indexes for use where BY-group processing is required. If you require a sorted aggregation table, you can input that table to a SORT transformation and store the resulting table wherever you want.

For information about indexes, see Indexing an Aggregation Table.