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 Newthen selectIT Resource Managementthen selectAdapter 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.
    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)
    Partial display of columns tab of an aggregation table
    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 Icon of a key 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 signifies that the column is numeric icon to the left of the name indicates that the column is numeric. A pyramid Icon signifies that the column is character data 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
    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.
  • 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
    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
    Extended Attributes tab of an aggregation table
    For items in the preceding display, the following definitions apply.
    New button on Extended Attributes tab
    adds a row to the attribute table. A number and a default name are provided, but you must enter the value and description.
    Delete icon
    deletes a selected attribute from the list.
    Up icon
    If present, enables you to reorder rows by moving a selected row up in the table.
    Down icon
    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.