How to Maintain the Latest Raw Value in an Aggregation Table :: SAS(R) IT Resource Management 3.7: Administrator's Guide

Scenario: To Keep Only the Data of the Last Day That Was Processed of Each Month

SAS IT Resource Management provides a technique that enables you to generate an aggregation table that contains the data from the last day of every month. This functionality is best used under the following conditions:

You have data that is relatively stable (for example, configuration type data).
You want to create a data source that can be used to compare the value of that data to what has been historically typical for that measurement.

The following example shows how to generate an aggregation table that captures these values from the last day of each month:

the value of the day, from the column called DAYDATE
the value of the amount of allocated storage, from the column called DCVALLO

To accomplish this task, start with an aggregation table that contains mainframe storage data that was captured by the DCOLLECT adapter.

Daily Aggregation Table That Contains All of the Data That Has Been Processed
DAYDATE	TotalMegabytesUsedByAllSystems (from DCVALLO)
01MAY2011	20000M
02MAY2011	21000M
...	...
31MAY2011	29000M
01JUN2011	30000M
20JUN2011	31000M
21JUN2011	32000M

Proposed Outcome of the Technique That Maintains the Latest Data of Each Month

The desired result is an aggregation table that contains for each month the information that was processed from the last day of that month. The following display shows what that aggregation table should contain:

New Aggregation Table That Contains Data from the Last Day of Each Month That Was Processed
DAYDATE	TotalMegabytesUsedByAllSystems
31MAY2011	29000M
21JUN2011	32000M

As each day’s data is read into the aggregation, the value of DAYDATE in each class combination should show the latest date. Similarly, TotalMegabytesUsedByAllSystems (a label that is used for the DCOLLECT DCVALLO measurement of allocated space) should show the latest value of allocated space in the class combination.

Note: After reading in the data from the next day (June 22, 2011), the new aggregation table should contain DAYDATE and DCVALLO values for these two dates:

31MAY2011
22JUN2011

Technique: Using the ID Column to Maintain Data from the Last Day of Every Month

An ID column is populated with the last value that is introduced to a class combination. Most commonly, this is used to populate something that is determined by the class list (for example, VOLSER).

A common usage pattern is to have an aging column (such as DAYDATE) in the class list and a value such as MONTHDATE as an ID column. (MONTHDATE can be determined from DAYDATE.)

To accomplish the goal of generating an aggregation table that contains only the data from the last day that was processed in every month, perform the following steps:

Use the Aggregation wizard to specify an aggregation table that reads a source table that can supply the values that you want to work with.

Note: In the scenario described previously, the source for the TotalMegabytesUsedByAllSystems values is the column called DCVALLO, which is in the XDCVOLS table of the IBM DCOLLECT adapter.
Specify MONTHDATE as the aging column of the new aggregation table.
Specify DAYDATE as an ID column of the new aggregation table.
Specify the column that contains the values that you want to work with as an ID column of the new aggregation table. In this scenario, the column DCVALLO would be specified as an ID column.
Complete the specification of the new aggregation table.
Save and then run the job that generate the table.

The following display shows the page of the Aggregation wizard that accomplishes the goals of this scenario:

Specify Class and ID Variables Page of the Aggregation Wizard