About Aggregation Tables

What Is an Aggregation Table?

Aggregation tables contain data that is collected and accumulated according to rules that are specified by an Aggregation transformation. These rules are based on columns from the source table and are used to classify, organize, and calculate statistics about IT data. The target table that is produced by an Aggregation transformation is used for further data management, analysis, or report processes.

With SAS IT Resource Management, you can generate two types of aggregation tables: summarized aggregation tables and simple aggregation tables.

Summarized aggregation tables:

Summarized aggregation tables consist of data from the source table that is summarized in accordance with the rules that are specified in the Aggregation transformation. Summarized aggregation tables also include statistics that are calculated and stored for the categories of data that you specify.
Simple aggregation tables:

Simple aggregation tables consist of data from the source table that is simply appended to the target table. Like summarized aggregations, simple aggregations support join columns, computed columns, and aging. Simple aggregations are analogous to the detail tables of the performance data warehouse (PDB) in SAS IT Resource Management 2.7.

How Is the Metadata for an Aggregation Table Created?

Methods for Creating Metadata for an Aggregation Table

The metadata for an aggregation table can be created in two ways:

automatically, by means of the Adapter Setup wizard
directly, by invoking an aggregation wizard from an Aggregation transformation

Creating Metadata with the Adapter Setup Wizard

Using the Adapter Setup wizard, you can specify the adapter, domain category, and time periods for which you want to generate reports. The Adapter Setup wizard creates the metadata for the appropriate Aggregation transformations, according to your specifications.

To invoke the wizard, from the menu bar, select New

IT Resource Management

Adapter Setup.

Note: The Adapter Setup wizard prompts for other input, such as the IT data mart where the job is to be stored, the location of the formulas that should be used when creating computed columns, and information about where the reports are to be stored. For more information about the Adapter Setup wizard and the other objects that it creates, see About the Adapter Setup Wizard.

The following display shows the Disk job that was generated by the Adapter Setup wizard for the SAR adapter. It contains a SAR Disk Aggregation transformation that specifies the creation of five aggregation tables.

SAR Disk Job That Generates Multiple Aggregation Tables

SAR Disk Job in the Process Flow Diagram

Note: The Adapter Setup wizard can generate multiple aggregation tables from a single Aggregation transformation.

Creating Metadata with an Aggregation Wizard

To create an aggregation table with the aggregation wizard from an Aggregation transformation, you must first perform the preliminary steps. For information about how to perform these steps, see Set up the Aggregation Transformation in the Process Flow Diagram.

To invoke the wizard, right-click the Aggregation transformation and select Add Aggregation Table from the drop-down list. You can then choose to create a Summarized Aggregation Table, a Simple Aggregation Table, or Aggregations through Templates.

You must respond to the prompts of the selected wizard in order to specify how the aggregation table is to be generated. When the wizard is successfully completed, the metadata for the aggregation table is generated and stored in the folder of the IT data mart that you specified. (If you do not select an IT data mart, the wizard defaults to the IT data mart in which the job resides.) The Diagram tab of the Job Editor window is updated to display the job that, when run, generates the aggregation table that you specified.

The following display shows a job called MyJob that was generated by directly invoking the Summarized Aggregation wizard. It contains an Aggregation transformation that is specified to create a single aggregation table.

Table Generated by the Summarized Aggregation Wizard

MyJob that contains MySummarizedAggregation Table

Note: An Aggregation wizard that is directly invoked can generate only one aggregation table for each invocation of the wizard. However, you can invoke the wizard multiple times on the same Aggregation transformation, each time generating another aggregation table

How Is the Physical Aggregation Table Generated?

Aggregation transformations are contained in jobs that generate physical aggregation tables when the jobs are successfully run. For information about how to run a job immediately, see Running Jobs. For information about how to deploy a job for batch scheduling, see Run a Job Immediately.

What Are the Properties of an Aggregation Table?

To open the Properties dialog box of an aggregation table, right-click the aggregation table in the process flow diagram. From the drop-down menu that is displayed, select Properties.

The following display shows the Properties dialog box for the DayDisk aggregation table, which was displayed by right-clicking the DayDisk aggregation table image on the process flow diagram. (The metadata for this table is one of many tables that are generated by the Disk Aggregation transformation of the SAR adapter.)

Properties for the DayDisk Aggregation Table

Properties for DayDisk Aggregation Table

Note: Unlike other SAS Data Integration Studio transformations, there is no Mapping tab. The aggregation wizard manages this function.

The Properties dialog box consists of the following tabs:

The General tab contains the identifying information about the table. The fields on this tab can be modified.

The following display shows the General tab of an aggregation table.

For items in the preceding display, the following definitions apply.

Name

identifies the name of the aggregation table.

Description

describes the aggregation table.

Location

specifies the location of the aggregation table in the metadata folder hierarchy. Click Browse to select a path to the table from the Select a Location dialog box that displays the metadata directory structure.
The Columns tab contains a grid that provides the following information about each column of the table. The Aggregation wizards supply the following columns:
- ContribCount is a numeric column in summarized aggregation tables. The value of ContribCount is the number of input observations (rows of data) that were used in computing this summarized row of data
- LastUpdated is the system datetime of when this row of data was last updated. This is in simple and summarized aggregation tables.
- CompleteDay is a single character, set to 'Y', 'N', or blank. If the Completed Day processing is not enabled, it is always set to blank. If the Completed Day processing is enabled, this column is set to 'Y' if the value of DATETIME indicates that this row of data represents a completed day. Otherwise, this column is set to 'N', which indicates an incomplete day. A day is considered complete if any input row of data has a time portion of the datetime value that is greater than the specified cutoff time.
The following display shows the Columns tab of an aggregation table.

Columns Tab of an Aggregation Table (partial listing)

For items in the preceding display, the following definitions apply.

#

specifies the column's ordinal position in the grid on the Columns tab.

Name

specifies the name of the column.

If the Key icon appears next to the name, then that column is used as a primary, unique, or foreign key.

Expression

specifies the combination of functions and mathematical operations that are used to derive a value for the column. This field is blank unless the column is a computed column.

Description

describes the column.

Type

specifies the data type of the column. Valid types are character and numeric. You can also identify column types by looking at the Name variable. A round icon to the left of the name indicates that the column is numeric. A pyramid icon to the left of the name indicates that the column contains character data.

Length

specifies the length of the column. The length of numeric columns is from 2 through 8. The length of character columns is from 1 through 32,767.

Informat

specifies the SAS informat that is used (if needed) to read values for the selected column. Informats are not used by the Aggregation transformation.

Format

specifies the SAS format that is used to write or display values for the selected column.

Summary Role

specifies the type of the column. It is not used by SAS IT Resource Management.

CAUTION:

The New button at the top of the grid should not be used.

The preferred method of adding, deleting, or modifying a column is to do so from the appropriate aggregation wizard. Using the wizard ensures that the metadata is properly updated.
The Indexes tab contains a list of the columns (and their descriptions, types, and lengths) that can be selected to use as indexes. Indexes can improve queries that use the aggregation table.

The following display shows the Indexes tab of an aggregation table.

Indexes Tab of an Aggregation Table

For more information about indexes, see Indexing an Aggregation Table.
The Keys tab is not used by SAS IT Resource Management, and its values are ignored.
The Physical Storage tab contains information about where the table is stored.

The following display shows the Physical Storage tab of an aggregation table.

Physical Storage Tab of an Aggregation Table
The Notes tab contains areas where you can annotate the table.
The Extended Attributes tab contains a custom property that is not part of the metadata for the table.

The following display shows the Extended Attributes tab of an aggregation table.

Extended Attributes Tab of an Aggregation Table

For items in the preceding display, the following definitions apply.

adds a row to the attribute table. A number and a default name are provided, but you must enter the value and description.

deletes a selected attribute from the list.

If present, enables you to reorder rows by moving a selected row up in the table.

if present, enables you to reorder rows by moving a selected row down in the table.

#

specifies the number of the extended attribute.

Field Name

specifies the attribute name to be defined.

Value

specifies the value for the attribute that is specified in the Field Name column.

Description

describes the attribute and value.

CAUTION:

Do not delete or modify the AgeLimit field from the Extended Attributes tab. In addition, do not delete the Filter from the Extended Attributes tab.

Doing so can cause processing errors.