Working with Staged Tables

What is a Staged Table?

A staged table contains data that has been extracted from an input data store by a staging transformation and has been rendered into a SAS data set that is suitable for further transformation. The structure and metadata for most staged tables that SAS IT Resource Management provides are based on corresponding template tables for specific adapters and domain categories. For more information about template tables, see Working with Template Tables.

The data in a staged table is accumulated according to rules that are specified by a staging transformation. The IT performance data resides in the staged table after the data is refined and loaded into a SAS data set that is ready for aggregation and reporting. Likewise, the staged table determines how the data appears after it has completed the staging process.

You can create a staged table by using the Adapter Setup wizard, the Staged Table wizard, or a user-written staging transformation. When a staged table is created, it is associated with a staging transformation in a staging job and stored in the same subfolder of the IT Data Marts tree as the staging job by default. This enables you to keep track of the staged tables and their corresponding jobs as they are stored together.

Note: If you manually create your own user-defined tables using the Source Designer or Target Designer, then consider saving these tables in a similar manner to maintain consistency.

When a staging job executes, the staging transformation reads the raw IT performance data from an input data source, processes it, and loads it into the corresponding staged tables. These tables can then be input into Aggregation transformations.

The staged table is generally used as input to an Aggregation transformation. However, a user can change this process flow by using the manual methods that are available in SAS IT Resource Management.

SAS IT Resource Management supplies adapters that provide staging transformations, template tables, and staged tables for many diverse data sources.

Note: The New Table wizard of SAS Data Integration Studio can create other tables that stage raw data from any other input data sources.

For information about the adapters that SAS IT Resource Management supports, see Supported Adapters.

Properties of a Staged Table

The metadata about a staged table, such as how raw data is computed and formed into a SAS data set structure, is available from the Properties dialog box of a staged table.

To view the properties of a staged table, complete the following steps:

Navigate to the folder in the IT Data Marts tree that contains the staging job and staged table.
Open the staging job that includes the staged table.
Right-click the staged table in the process flow diagram and select Properties.

Note: You can also right-click the staged table in the tree view of SAS IT Resource Management to access the staged table properties. However, the set of properties that are available when using this method differs slightly from the properties that are available from the process flow diagram. For best results when viewing and modifying relevant properties, access the properties from the staged table object in the process flow diagram as explained in the preceding steps.

The following list includes all of the staged table properties and indicates whether a property is available only from the staged table in the tree view or the staged table in the process flow diagram:

The General tab displays information that identifies the staged table name, description, location, and associated responsibilities.
The Columns tab displays the metadata for each column in the staged table. For more information about the properties that are available from the Columns tab of a staged table, see Columns in Staged Tables.
The Indexes tab displays any indexes that have been created for this staged table. For each column, the name, description, length, and type are stored.
The Keys tab displays any keys that have been created for this staged table.
The Parameters tab displays any prompts or groups that have been created for this staged table. This tab is available from the tree view only.
The Physical Storage tab displays information about the physical name of the table and the library and DBMS where it is located.
The Options tab displays table options such as compressing observations in a SAS data set, additional security options, and rules for overwriting data sets.
The Notes tab displays any notes or documents that are associated with this staged table.
The Extended Attributes tab displays information about any custom property that is not part of the standard metadata for this staged table. This tab includes any external names for a table, if available.
The Authorization tab, available only from the tree view, displays the settings that define access to the staged table. This tab is available from the tree view only.
The ITRM Options tab displays the adapter, template table, data model version, and predecessor tables that are associated with the staged table. This tab is available from the process flow diagram only.

For information about the contents of these tabs, click Help in the dialog box.

Task List for Staged Tables

When you right-click a staged table, you gain access to several actions that you can perform for that table such as analyze, update metadata, delete, copy, move, export, and so on. The tasks that are available differ depending on whether you right-click the staged table in the tree view or the process flow diagram. For information about these tasks, see the Help that is available for these topics in SAS IT Resource Management.

Create Staged Tables

SAS IT Resource Management does not enable you to create a staged table without associating it to a staging transformation. Thus, you must have a staging job that includes a staging transformation before creating a staged table.

If you want to create staged tables for the BMC Perf Mgr, DT perf Sentry, or SNMP adapters, other than the tables that are included in the staged table templates for those adapters, you can specify and then run macros to do so. These macros create the metadata for the staged table and attach that metadata to the staging transformation in your job.

To create staged tables for BMC Perf Mgr, see %RMMSBMCP.
To create staged tables for DT perf Sentry, see %RMMSDTPS.
To create staged tables for SNMP, see %RMMSSNMP.

Note: For best results, always create or add staged tables to a job from the Staged Table wizard as described in this topic. If you copy an existing table from another IT data mart or job and move it into a different job, or if you attempt to reattach a staged table to its original staging transformation after it was removed, then you might lose valuable components of the table, such as computed columns.

To create the metadata for a staged table, perform the following steps:

Open a staging job that includes a staging transformation. For more information about creating a staging transformation, see Add a Staging Transformation to a Staging Job.
Right-click the staging transformation in the process flow diagram and select Add New Staged Table. The Staged Table wizard opens and prompts you to enter the information that is required to create a staged table.
Specify an IT data mart, storage location, and formulas.

Specify IT Data Mart and Formulas Page of the Staged Table Wizard
1. Confirm and specify the IT data mart and the location where the staged tables will be stored. The IT Data Mart and Location fields default to the IT data mart name and location of the current staging job. You must specify a location for the staged table that is within the IT data mart that is specified in the IT Data Mart field.
  
  You can use the Browse option to select a different location for the staged table or to create subfolders inside the IT data mart for storing the staged tables. However, the Adapter Setup wizard creates and stores staged tables in the same folder as the staging job so you might choose to accept the default values for consistency.
2. Specify the location for any user-written formulas that you want the Staged Table wizard to use in addition to those supplied by SAS IT Resource Management. The default location directs the Staged Table to the supplied formulas. This field does not allow manual entries. Use Browse to navigate to the appropriate folder as needed.
  
  The default location for the formulas folder is the IT Formulas folder, which is located here: Shared Data/SAS IT Resource Management/IT Formulas.
  
  Note: If you select or create a folder location where the formulas that are required for the staged table are not found in the folder location or its subfolders, the column will not be created. The failure to create the column will be noted in the SAS log file.
3. Click Next to continue.
Select template tables. Based on the adapter for the staging transformation, a list of available template tables and their descriptions are displayed within domain categories of table types.

Select Template Tables Page of the Staged Table Wizard
1. Use the check boxes to select the template tables that you want to use to generate staged tables. (You can click + to expand a folder or click – to collapse a folder.) If you select a folder, all of the subfolders and template tables within that folder are automatically selected and the check box displays a check () next to the folder and its template tables. If you select an individual template table within a folder, but not all template tables within the given folder, then the check box for the parent folder shows a darkened box with a check (). This indicates that some, but not all, template tables within the folder are selected.
  
  The Number of selected template tables maintains a real-time count of the template tables that you select.
  
  CAUTION:
  
  The more template tables that you select and staged tables that you add to a job, the longer it takes to create the staged tables and later run the job.
  
  If you select more than 100 template tables, then a warning message explains that creating a large number of staged tables at once can result in long processing times or processing errors.
2. Select Include only recommended columns in the new staged tables if you want only the recommended columns to be included in the staged tables that you are creating. Recommended columns are those that SAS IT Resource Management has predefined as critical or relevant for reporting and analysis in the performance area of the staged table. If this option is not checked, the staged tables will include all available columns.
  
  Note: You can view the recommended columns in a template table from the Columns tab of the Properties dialog box for a given template table. For more information about how to access and view the recommended columns of a template table, see Columns in Template Tables.
3. Click Next to continue.
Specify the SAS library parameters for the staged tables and their staging transformation.

Note: This page appears only if you are creating the first staged tables for a staging transformation. If you have already created staged tables for the staging transformation, then the wizard skips this page. Library parameters are set once for a staging transformation and all staged tables for that transformation use the same library parameters. However, if all staged tables are later removed from the staging transformation and new staged tables are subsequently added, then the Specify SAS Library page is displayed again in the wizard. In this case, the staged tables in the subsequent iteration are placed in a different library from the first set of tables.

Specify SAS Library Page of the Staged Table Wizard
1. Specify the Name and Description for the SAS library. The SAS library name must be unique within its folder in the application server. The Staged Table wizard uses the naming convention <adapter name> + Staging <unique number> to name SAS libraries. You can accept the default name or enter a new name for the library. However, library names must be unique within the application server.
2. Confirm that the Server field shows the server that is associated with the selected IT data mart where this library is to be stored. This field does not enable modifications.
3. Specify the Libref that is associated with the staged table library. The Staged Table wizard uses the naming convention STG + <the same number generated for the library> to name the libref. You can accept the default name or enter a new libref name.
  
  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.
4. Specify the Path Specification for the location of the library and thus where the tables for this library are stored. The default value is based on the combination of the default path that is specified for the IT data mart, and the operating system of the server. The characters stg<unique number> are also appended to the path, where the unique number is the same as that generated for the preceding library.
  
  For example, if the default path for an IT data mart on Windows is C:\SAPJob1, then the default value for a new staging library might be C:\SAPJob1\stg27416, as shown. If the default path for an IT data mart on UNIX is /u/<user ID>/Sample52, then the default value for a new staging library might be /u/<user ID>/Sample52/stg27416.
  
  You can accept the default path, enter a new path, or click Browse to select a path.
  
  Note: Browse is disabled when the selected application server is running on a machine using the z/OS operating system. When entering a z/OS path manually, you can use either a prefix for a traditional z/OS filesystem path (MY.DATAMART) or a root directory in the UFS hierarchical file system (/u/myname/datamart).
5. Click Next to continue. The completed Staged Table wizard will create one SAS library that stores all of the tables and catalogs from the staging transformation. This includes staged tables, control tables for data duplication, and the compiled macro code for the transformation.
Specify the z/OS attributes that allocate file space in the operating environment if the application server is running on z/OS. The Staged Table wizard skips this page for operating environments that are not z/OS. For more information about these parameters, see the Help for SAS IT Resource Management.
Review the details of the staged tables and storage parameters that you entered.

Summary Page of the Staged Table Wizard

If you want to change anything that you entered, click Back to return to the parameter that you want to change.

Note: You cannot change the name of a staged table because it is automatically assigned the same name as the template table on which it is modeled.
Click Finish to create the metadata for the new staged table and SAS library (if applicable). The staged tables appear in the process flow diagram for the staging job.

Process Flow Diagram for Staged Tables in a Staging Job

The staged tables in the process flow diagram show the name and the description of the staged table.
Select FileSave to save the changes to the job.

Note: The metadata for the staged tables is saved in the staging job. However, the physical staged tables are created only after the staging job is run.

Add an Existing Staged Table to a Job

SAS IT Resource Management does not enable you to create a staged table without associating it to a staging transformation. For best results, always create or add staged tables to a job from the Staged Table wizard. If you copy an existing table from another IT data mart or job, copy or move it into a different job, or attempt to reattach a staged table to its original staging transformation, then you might lose valuable components of the table, such as computed columns. For more information about using the Staged Table wizard to add a staged table to an existing job, see Create Staged Tables.

Erase a Staged Table

Erasing a staged table deletes the physical table, its contents, and its metadata.

CAUTION:

If you erase a staged table that serves as a source table in other jobs, then these associated jobs might not execute because they are missing a source table.

Simply erasing a staged table does not affect the metadata of the associated aggregation or information map jobs. However, if you erase a staged table and then re-run the staging job, then the data for the deleted staged table is not created. Thus, the jobs that used that table will fail due to a missing source table.

To erase a staged table, perform the following steps:

In a tree view of SAS IT Resource Management, locate the staged table that you want to erase.
Right-click the staged table that you want to erase and select Erase. The staging job that includes the staged table must be closed and currently not in use before erasing.
In the confirmation dialog box, click Yes to erase the staged table. The staged table disappears from the tree view and the process flow diagram for the job.

Delete a Staged Table from a Tree View

Deleting a staged table from the tree view in SAS IT Resource Management removes the metadata object. This action does not delete the physical data set or the library that is associated with the staged table.

CAUTION:

If you delete a staged table that serves as a source table in other jobs, then these associated jobs might not execute because they are missing a source table.

Simply deleting a staged table does not affect the metadata of the associated aggregation or information map jobs. However, if you delete a staged table and then re-run the staging job, then the data for the deleted staged table is not created. Thus, the jobs that used that deleted table will fail due to a missing source table.

To delete a staged table from a folder in a tree view of SAS IT Resource Management, perform the following steps:

In a tree view of SAS IT Resource Management, locate the staged table that you want to delete.
Right-click the staged table that you want to delete. The staging job that includes the staged table must be closed and currently not in use before deleting.
Select Delete.
In the confirmation dialog box, click Yes to delete the staged table. The staged table disappears from the tree view and the process flow diagram for the job.

Note: If you modify or delete a physical table without using the process described in this topic, the metadata for the table will not be updated.

Delete a Staged Table from a Job

Deleting a staged table from a job removes it from the job so that it is no longer associated with the transformation in the job. This action removes the staged table from the job but does not delete the table's metadata, the physical table, or the library that is associated with the staged table.

CAUTION:

If you delete a staged table that serves as a source table in other jobs, then these associated jobs might not execute because they are missing a source table.

Simply deleting a staged table from a job does not affect the metadata of the associated aggregation or information map jobs. However, if you delete a staged table and then re-run the job, then the data for the deleted staged table is not created. Thus, the jobs that used the deleted staged table (or any existing tables that used the deleted staged table) will fail due to a missing source table.

To delete a staged table from a job, perform the following steps:

In the IT Data Marts tree view of SAS IT Resource Management, locate the job that includes the staged table that you want to delete.
Double-click the job to open it on the Diagram tab of the Job Editor window.
Right-click the staged table object in the process flow diagram and select Delete. The staged table is no longer visible in the process flow diagram of the job.

Purge the Content of a Staged Table

To purge the contents of a staged table while keeping the physical table and its metadata intact, perform the following steps:

In the IT Data Marts tree view of SAS IT Resource Management, locate the job that includes the staged table that you want to purge.
Double-click the job to open it on the Diagram tab of the Job Editor window.
Right-click the staged table object in the process flow diagram and select Purge.
In the confirmation dialog box, click Yes to purge the staged table.

Modify a Staged Table

To modify a staged table, navigate to and open the job that contains the staged table that you want to modify.

CAUTION:

For best results, always modify staged tables from the process flow diagram.

If you access a staged table's Properties dialog box by opening a staged table from a tree view in SAS IT Resource Management, then you might not see all modification options that are available for a staged table.

Perform the following steps:

In the process flow diagram of the job, right-click the staged table that you want to modify.
Select Properties to open the Properties dialog box.
On the General tab, you can change the name and description of the table.
On the Columns tab, you can perform the following tasks:
Add a New Data Column to the Staged Table
1. Select NewData Column. A new line is added to the grid.
2. Click the corresponding cells in the new grid line to enter the name (must be unique), description, type, length, informat, and format for the new data column.
  
  Note: The Expression cell is not available because the new column is a data column and not a computed column.
Add a New Computed Column to the Staged Table
1. Select NewComputed Column. A new line is added to the grid.
2. In the new grid line, enter the name (must be unique), expression, description, type, length, informat, and format for the new data column.
Import a Column from Another Table into the Staged Table
1. Click to open the Import Columns dialog box.
2. In the Available columns list, navigate to the table that contains the column that you want to import.
  
  Note: "User" is a reserved word in SQL and should not be the name of any imported column.
3. Highlight the column that you want to import.
4. Click the right arrow to transfer the column into the Selected columns list.
5. Click OK when you are satisfied with your list of selected columns to import. The new columns appear in the grid.
  
  Note: After you import the column, you must run the staging job before you can view the data in the new column. When the staging job, runs the data is propagated to the physical table and is available for viewing.
Modify the Properties of a Column in the Staged Table
1. Locate the column that you want to modify and double-click the cell that you want to change.
2. Enter the new name (must be unique), expression, description, type, length, informat, or format value for the column. The Expression cell is not available for data columns. The External Name cell is not applicable for all columns.
  
  Note: You can also use the icons in the top row of the Columns tab to manage, move, and modify additional properties of each column of the staged table. For more information about using these features, click Help on the Columns tab.
Delete a Column in the Staged Table
1. Locate the column that you want to delete and right-click the column name.
2. Select Delete. The column is immediately removed from the grid and no confirmation message is displayed.
  
  Note: If you delete an instanced variable from a staged table, then the staging code might not run successfully later.
On the other properties tabs (Indexes, Keys, Physical Storage, Options, Notes, Extended Attributes, and ITRM Options), you can view additional properties for the staged table. For more information about the parameters on these tabs, click Help on each tab.
When you are finished, click OK to save your changes.