Working with Template Tables

What are Template Tables?

Template tables are representations of adapter-specific tables that are supplied by SAS IT Resource Management for specific IT data sources. Template tables are models that are used to create staged tables. These staged tables are, in turn, populated by staging transformations when loading and transforming raw data.

Each template table provides the metadata for all regular columns, computed columns, and other attributes (formats, formulas, source code for computed columns, and so on) that a staged table can include when it is created from a template table. The resulting staged table can include all or a subset of this metadata that is provided by the template table.

A template table includes regular columns and computed columns that indicate how raw data is computed and populated into the staged table. This predefined metadata is based on data classifications and metrics that SAS IT Resource Management considers most important for aggregation and reporting in various performance areas. Thus, template tables are the starting point where SAS IT Resource Management begins to add intelligence to raw data. For more information about the metrics that SAS IT Resource Management uses for the various adapters, see the metrics documentation that is located on the SAS IT Resource Management documentation page. To locate the SAS IT Resource Management documentation, use the Products Index at http://support.sas.com/documentation/index.html.

Locate Template Tables

Template tables are available from the Folders tree view. Expand Products

SAS IT Resource Management

Adapters. Then, expand an adapter subfolder, the IT Template Tables subfolder, and one or more of the subfolders for domain category. SAS IT Resource Management groups the tables by adapter and domain category (such as Disk or Network).

Note: You cannot delete template tables unless you have the appropriate permission to do so.

Domain categories are categories of template tables (and aggregation tables) that are organized logically to represent subsets of the IT resource measurements that are available from an adapter. This enables you to identify the template tables that correspond with the performance data that you want to use when creating staged tables.

Template Tables for Disk Performance of the HP Reporter Adapter

In the previous image from the Folders tree view, the template table PCSDSK is in the Disk subfolder and is therefore related to the disk performance data from the HP Reporter adapter. Using this information, you can determine that this template table provides metadata for staging the data that relates to PCS disk metrics such as disk performance and disk space usage. Other template tables that relate to other facets of disk performance are also in the Disk subfolder.

Note: Domain categories also surface in the Staged Table wizard and the Adapter Setup wizard for creating staged tables.

Differentiate between Template Tables and Staged Tables

Staged tables have the same name as the template tables on which they are modeled. For this reason, identifying an object as a template table or a staged table when working within SAS Data Integration Studio can be confusing.

The following table shows the differentiating factors that enable you to distinguish a template table from a staged table.

Distinguishing Template Tables from Staged Tables

Differentiator	Template Tables	Staged Tables
Icon	Template tables are indicated by the icon in the tree views and the Staged Table wizard.	Staged tables are indicated by the icon in the tree views.
Location	In the Folders tree view, template tables are in the ProductsSAS IT Resource ManagementAdapters<adapter name>IT Template Tables<domain category> subfolders.	In the Inventory tree view, staged tables are in the `Table` folder. In the IT Data Marts tree view, staged tables are in the same folder as the corresponding staging job (unless you designated otherwise when creating the staged tables). Staged tables are also stored in the same IT data mart subfolders as their respective staging jobs.
Job	A template table never appears in a folder of the IT Data Marts tree view or in the process flow diagram of a job.	Each staged table appears as an object in the process flow diagram of a staging job. The following image is an example of how a staged table looks in a process flow diagram.

Template Table Properties

About Template Table Properties

Template tables are distinct tables that are defined to the metadata for SAS IT Resource Management. You cannot modify the properties of template tables because these predefined tables include values and attributes that make up the intelligence for processing raw IT data based on industry research. You can, however, view the properties of template tables to determine the template tables from which you want to create staged tables for a given adapter.

To view the properties of a template table, perform the following steps:

In the Folders tree view of SAS IT Resource Management, expand ProductsSAS IT Resource ManagementAdapters. Then expand an adapter subfolder, the IT Template Tables subfolder, and one or more of the domain category subfolders to see the corresponding template tables.
Right-click a template table and select Properties. A Properties dialog box appears and enables you to view the template table's metadata, such as table name, column metadata, and external properties.

Properties Dialog Box for the PCSDSK Template Table

The Properties dialog box contains the following tabs:

The General tab displays information that identifies the template table, such as name, description, folder location, and responsibilities.
The Notes tab displays notes or documents that are associated with the template table.
The Advanced tab displays additional metadata attributes about the template table, such as metadata ID and filename.
The Authorization tab displays users and groups that have various permissions related to template table.
The Columns tab displays the metadata for each column in the template table. This tab includes information such as the name and description for each column, the expressions used for any computed columns in the template table, and the type classification for each column in the template table. For more information about the Columns tab for template tables, see Columns in Template Tables.
The ITRM Options tab identifies the adapter that is associated with the template table as well as any predecessor tables, external names, and data model versions that correspond with the template table. For more information about ITRM Options tab for template tables, see ITRM Options for Template Tables.

Note: You can click Help in the Properties dialog box for more information about each tab.

Columns in Template Tables

The Columns tab of a template table's Properties dialog box enables you to view the specific column metadata for the table. You can use this information to view the types of data that the template table includes, understand how computed columns are formulated, and determine whether you want to use the template table for creating a staged table.

Sample Columns Tab from the Properties Dialog Box of the PCSDSK Template Table

Sample Columns Tab for a Template Table's Properties Dialog Box

The following list describes the information that you can view for each column in a template table. Each row on the Columns tab shows the metadata for a single column of the template table. You cannot modify this information because the predefined metadata includes values and attributes that make up the intelligence for processing raw IT data based on industry research.

specifies the column's ordinal position in the grid on the Columns tab. Note that the ordinal position is not necessarily the same as the alphabetic sequence of variable names.

Name

specifies the name of the column in the template table.

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. For more information about computed columns in template tables, see Computed Columns in Template Tables.

Description

provides a description of the data that populates the column. The description provides more information about the type of data that the column includes.

The column descriptions in template tables are propagated to staged tables and used as labels in information maps and aggregations. Therefore, column descriptions for template tables are standardized to be concise, easily understood, and efficiently integrated into other objects and SAS products such as SAS Enterprise Guide and SAS Information Map Studio. Standardizing column descriptions and label names ensures that equivalent data is labeled the same across all adapters. Standardization also reduces the need for editing label content in reporting clients.

Here are some of the ways that column descriptions in template tables have been standardized to be effective labels:

Character length is reduced by removing spaces between words and capitalizing the first letter of each word or term within the description (such as ManagementClassLength).
Character length is typically reduced by removing special characters, punctuation, and redundant words that do not enhance the meaning of the data.
Words that indicate performance area or domain category (such as CPU, processor, disk, memory, and network) are typically at the beginning of the description.
Words that indicate data interpretation and normalization (such as rate, bytes, and seconds) are at the end of the description.
Words are abbreviated if a clear alternative is available (such as Average to Avg).

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 Numeric icon

icon to the left of the name indicates that the data in the column is numeric. A pyramid Character icon

icon to the left of the name indicates that the column contains character data.

Length

specifies the maximum length of the data in the column.

Informat

specifies the SAS informat that is used (if needed) to read values for the selected column. Informats are required for reading fixed-width data.

Format

specifies the SAS format that is used to write values for the selected column. Template table columns are in NLS format when applicable. NLS formats are indicated by NL as the first two characters in the format column. For more information about NLS formatting, see National Language Support (NLS).

External Name

specifies the name by which the staging code maps columns in a staged table to their corresponding variables in the raw data. External names are used by both staged tables and template tables. However, not all columns in a staged or template table have a predetermined external name.

Note: For MXG adapters, the external name is usually the name of the MXG variable. For the DT Perf Sentry adapter, the external name is the name of the DT Perf Sentry metric.

Recommended

specifies whether the column provides data that SAS IT Resource Management has predefined as critical for reporting and analysis in the performance area of the template table. The Staged Table wizard enables you to use only the recommended columns from a template table when creating a corresponding staged table. This enables you to create staged tables that include only the columns that are recommended and thus not spend resources staging and analyzing data that is likely not relevant. For more information about using the Staged Table wizard, see Create Staged Tables.

The Adapter Setup wizard also uses the recommended columns of a template table to create various levels of output. For example, when using the Adapter Setup wizard, you can choose staging only, minimal, typical, or full output. Then the wizard creates the appropriate level of output based on your selection and the columns recommended for the corresponding tables. For more information about the Adapter Setup wizard, see About the Adapter Setup Wizard.

ITRM Options for Template Tables

The ITRM Options tab of the Properties dialog box for a template table includes information such as what tables are staged before the template table and how the original data collector identifies the data that is used for the template table.

Sample ITRM Options Tab from the Properties of the PCSDSK Template Table

The ITRM Options tab includes the following parameters:

Adapter Key

specifies the adapter that is associated with the template table. For adapter names, SAS IT Resource Management uses the latest names that are approved by respective third parties. However, due to the constraints of character limitations in software code and organizations changing the names of their products and software, SAS IT Resource Management uses an abbreviation called an adapter key to represent an adapter in the software if necessary. The adapter key is for reference by SAS Technical Support and does not require any action from the SAS IT Resource Management user.

Note: In some cases, the adapter key does not match the current name of the adapter but instead reflects the former name of the adapter. For example, the adapter key for the BMC Perf Mgr adapter is PATROL, the former name of this adapter.

Predecessor Tables

specifies other tables that are required to be staged in order to produce this table. For example, one of the staged tables (named SAR) for the SAR adapter is a compilation of many SAR tables (predecessor tables). When the SAR table is staged, SAS IT Resource Management programmatically stages the various predecessor tables that make up the resulting SAR staged table so that you do not have to stage each predecessor table individually. If a template table does not require staging predecessor tables, this parameter is blank.

External Names

specifies the names that the original data collector (or other software) uses to identify the data that serves this template table. External names are the means by which the staging code maps a SAS IT Resource Management template table to a table in the raw data. For example, a template table that is named “NTPHDSK” in SAS IT Resource Management might have an external name such as “Physical Disk” in the raw data file. The staging code uses this information to determine that the Physical Disk data in the raw data file belongs in the staged table that is modeled from the NTPHDSK template table.

Note: The staging code for some adapters does not use external names. For example, the table name in the raw data might be the same as the table name in SAS IT Resource Management.

The external names for template tables are similar in concept to the external names for staged tables. For more information about using external names for staged tables and columns in staged tables, see Understanding External Names of Staged Tables and Columns.

Data Model Version

specifies the version of the template table. This is the version from which the staged table is created or updated through the Maintain Staged Tables wizard. For more information about the Maintain Staged Tables wizard, see About the Maintain Staged Tables Wizard.

Computed Columns in Template Tables

Computed columns in a table contain data values that are derived from other columns in the table. Template tables contain computed columns for data that is not directly provided by the raw data source but the data might be useful for reporting or promoting consistency across various data sources.

The following table demonstrates how template tables might use computed columns for a data source for consistency across various data sources. In this example, Data Source 1 includes three columns: Column A, Column B, and Column C, which is the total of Column A and B. Data Source 2 provides only two columns: Column A and Column B. For Data Source 2, a template table might compute the total of Column A and Column B so that the data for Data Source 2 is as comprehensive as Data Source 1.

Example of a Computed Column in a Template Table

Data Source 1	Data Source 2	Template Table for Data Source 2
Column A	Column A	Column A
Column B	Column B	Column B
Column C (equal to Column A + Column B)		Column C (a computed column equal to Column A + Column B)

Note: A computed column should not be used as a source of calculations for other computed columns. Using a computed column as a source for a computation can be problematic due to the order of computations that are necessary to derive the final value.

To find a computed column in a template table and view the formula that it uses, click the Columns tab of a template table's Properties dialog box. All computed columns contain an expression in the Expression column of the grid.

Here are some of the reasons for which SAS IT Resource Management might use computed columns in a template table:

Create datetime derivations such as changing week date and shift values to your local environment.
Provide normalizations such as converting bytes to kilobytes, seconds to milliseconds, and percentages between 0 and 1 to percentages between 0 and 100.
Total paired columns to provide a summed value that includes input and output values, or received and sent values.
Derive opposite values by using a percentage-based computation that typically subtracts one value from another. For example, a computed column might subtract the number of used components from the number of allocated components to determine the number of components that are still available.
Standardize performance event count to total events across various classifications such as batch jobs, TSO sessions, and started tasks.
Create class columns when a table contains an instance column that can contain either a global or an individual instance of a performance metric. In this case, a computed column such as CpuId or DiskId is created and set to an individual instance value, or it is set to a blank value for global instances. These new class columns can then be used as targets of filters to divide global and individual instances into appropriate aggregations.
Convert raw counts to rates.
Calculate response time counts and percentages.

Use Template Tables to Create Staged Tables

The Staged Table wizard and Adapter Setup wizard enable you to create staging jobs that use template tables to create corresponding staged tables. When executed, the staging jobs load the source data into the staged tables. The staged tables can be used as input to an Aggregation transformation or other SAS Data Integration Studio transformations that are used for analysis and reporting.

SAS IT Resource Management enables you to select the template tables that you want to use or accept the template tables that are selected programmatically for a given adapter.

To select the template tables that you want to use, create a staging job manually. For more information about how to create a staging job, see Add a Staging Transformation to a Staging Job. For more information about how to create a staged table, see Create Staged Tables.
To have template tables preselected programmatically for a staging job, run the Adapter Setup wizard for a supported adapter. The template tables that the Adapter Setup wizard uses are predetermined by SAS IT Resource Management to include the data that is most relevant and appropriate for the level of analysis and reporting that you select. For more information about the template tables that are preselected by the Adapter Setup wizard for each adapter and domain category, see Overview of the SAS IT Resource Management Data Model .