Working with Custom Tables

IBM SMF Custom Tables

SAS IT Resource Management provides support for more than 1,100 tables in the IBM SMF adapter. However, it does not support every table that MXG can produce from IBM SMF data. For those instances, you can extend the functionality of the IBM SMF staging transformation to load data into user-defined tables for which SAS IT Resource Management does not provide a template. These are called IBM SMF custom tables.
To create an IBM SMF custom table, you must create (or register) metadata about the physical MXG data set or table. You can use the New Table wizard to do so. The physical table must exist first and it must contain a column (variable) called DATETIME to enable it to be added to a user-defined table. If the default definition for the MXG data set does not contain DATETIME, it can be added simply by using MXG exits. This process is documented in the first step of the following task. Once the MXG data set is registered, it can be added as an output table to the IBM SMF staging transformation. It can also subsequently be used as input to an Aggregation transformation.

How to Load IBM SMF Data into User-Defined Tables

To load IBM SMF data into user-defined tables for which SAS IT Resource Management does not provide a template, perform the following steps:
  1. If your MXG data set (with the DATETIME variable present) already exists as a physical table, skip this first step.
    Otherwise, create it now using regular MXG coding techniques. If the MXG data set does not have a DATETIME variable defined for it, you must identify the variable that contains the datetime stamp for each event or interval that the data represents. Then, modify the EX exit member that contains the OUTPUT statement for that table to also contain an assignment statement of the form:
    DATETIME=mxg_variable;
    You must also add the DATETIME variable to the output data set's keep list. To do so, add it to the _Kdddddd. For example:
     %MACRO _KTY1415 DATETIME
    %
    The preceding example adds the DATETIME variable to the keep list for the TYPE1415 table. Look up the correct names for the EX member and the _Kdddddd macro for your data source in the MXG documentation.
  2. Because you do not want the SAS IT Resource Management staging transformation to write to this copy of the MXG data set, make a copy of the physical MXG data set. You can put the copy in any writable library, such as the Staged library of your IT data mart.
  3. Modify the MXG_PROPS_SMF format that maps the name of the tables to the MXG code that has to be executed. Locate the source for this format in your SASHELP.ITRM.FORMATS catalog. In the format, the value to be formatted is the MXG data set name, and the resulting label is a string of MXG code members and macro names. Add a new line to the format containing your MXG data set name and the name of the MXG code and macros using what is there as a template. Make sure that the text strings start in the same columns. When you execute PROC FORMAT, revise the LIBRARY= parameter in the PROC statement to ensure that the output catalog in the PROC FORMAT statement is pointing to ADMIN.ITRM. This revision of the LIBRARY= parameter creates the format in your ADMIN library that precedes the SASHELP.ITRM.FORMATS in your FMTSEARCH SAS option. Therefore, this format will be used instead of the one supplied at installation time.
    The task of allocating the ADMIN library requires you to have Update access to it. The allocation can be achieved only when no other SAS IT Resource Management ETL jobs are running against this IT data mart. You must execute the following statement in a SAS session:
    libname admin "path_of_ADMIN_library";  
    where "path_of_ADMIN_library" can be found in your SAS IT Resource Management or SAS logs, or by querying the ADMIN library of your IT data mart within SAS IT Resource Management.
  4. Start SAS Data Integration Studio and use the Register Tables wizard from the File menu to register your MXG data set as a user-defined table.
    To register your MXG data set as a user-defined table, perform the following steps:
    1. On the first page of the Register Tables wizard, select SAS as the source type.
    2. In the Library field, select the library that contains your MXG data set. If you copied your MXG data set to a library that has already been defined in metadata, select the appropriate library from the SAS Library list. If you copied it to a library that has not yet been defined in metadata, and the library is not in the list, then you must define a new SAS Library in metadata now by selecting New.
    3. On the following screen, select the MXG data sets that you want to import metadata from.
    4. Choose a folder to store the metadata. Select the Other Tables folder in the appropriate IT data mart. It should be the same IT data mart that contains the job to which you want to add this table.
    5. Select Finish on the confirmation screen to import the metadata. The MXG data sets should be listed within the Other Tables folder of your IT data mart in the SAS Data Integration Studio Custom tab.
      Note: The table has a slightly different icon associated with it because it is not backed by a SAS IT Resource Management staged table template.
  5. Add the new user-defined table as an extra output of the IBM SMF staging transformation in your existing jobs. To do so, perform the following steps:
    1. Double-click the appropriate job. It appears on the Diagram tab of the Job Editor window.
    2. In the Transformations tree of SAS IT Resource Management, expand the ITRM Transformations folder and locate the IBM SMF staging transformation. Drop that staging transformation onto the Job Editor window. The staging transformation appears in the process flow diagram for the job.
    3. Right-click the IBM SMF staging transformation and select Add output.
    4. Drag the new user-defined table from the Custom tab to the drop zone that now appears on the process flow diagram.
    5. Save the job.
  6. Run the job or deploy it for subsequent batch execution, as you would any other SAS IT Resource Management job.