Datasets

What are Datasets?

In the simplest terms, a dataset is any named group of records. Like variables, datasets are used to represent data in a task that may be different each time a task runs. But unlike standard variables, datasets can contain multiple rows and columns. This is useful when retrieving a collection of data, such as a database or spreadsheet, or retrieving information that describes one or more objects or items. Datasets can hold information such as medical or financial records. Datasets are also used to store information returned by applications or the operating system itself. For example, Advanced Workflows returns information about task errors (e.g., error name, number, description) via an AMError dataset. Datasets can be cataloged, which permits them to be referred to by name without specifying where they are stored.

The data in a dataset is laid out like a database table, which has a unique name and consists of columns and rows. The columns consist of pre-defined units of data, such as one item in a database or personnel data about one member of a customer list. The rows contain the actual data for the columns. An example of a simple dataset containing customer data is illustrated below. The name of the dataset is Customers. The first row (in bold) contains the unique names of the dataset columns, which in this case, describes the data type. All other rows include the actual data as described by each column.  

Table: Customers

Using Datasets

Datasets are accessed in the same way that one would access information in a database, by specifying the column and row where the data resides. Every dataset created and used must have a unique name, much like variables. But because datasets are more like tables (as opposed to variables, which can be thought of more like containers that hold one value), they are referenced differently than other objects. When using datasets, the unique name of the dataset must be referenced followed by the column name enclosed in percentage signs. For instance:

%DatasetName.ColumnName%

When a dataset is created, the current row is automatically set to 1 (assuming that the dataset has any data, since it is possible for a dataset to have 0 rows, such as when a SQL Query returns no data). A dataset is of minimal use, however, unless one can access the other rows. Typically, this is accomplished by using the Loop Dataset action which takes a dataset name as a parameter and automatically increments the current row with each iteration. The loop continues until all the rows have been accessed. In this way, one could make a task that performs operations on each row of the dataset while using the same expression. Using the table above as an example, one can retrieve the email address of each customer using the following expression inside a Loop Dataset action:  

%Customers.Email%

Using a Loop Dataset step is not the only way to access dataset rows. It is possible to directly access a particular row of a dataset by supplying the row number within the expression. For example, if the dataset contains five rows and you need to get the data in row 2, simply enter the row number enclosed in parenthesis directly after the dataset name. For example:

%DatasetName(2).ColumnName%

Again, using the table illustrated above, if you want to retrieve the phone number specified in row 3 (in this case, Paula Smith's phone number), the following expression will do the trick:

%Customers(3).Phone%

Common Actions that use Datasets

Several actions and activities create and populate datasets. The table below describes some of those actions.

Pre-Named Datasets

In addition to datasets that are created and populated during execution of an action or activity, a collection of pre-named (or fixed field) datasets are also available which can provide more insight about the behavior of a running task, determine system states or examine other elements, such as triggers used to fire off a task or errors that were generated by a task. The table below describes some of these types of datasets.

Example using SQL Query action

The SQL Query action is an example of an action that creates and populates a dataset. The fields contained within that dataset are determined by the query that was executed. For example if the following query is executed:

SELECT firstname, lastname, company from customer where city='Los Angeles';

Then the following dataset would be generated:

datasetname
|--firstname
|--lastname
|--company

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset action to loop through the records. Inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

%mydatasetname.firstname%

or you could combine two fields together like this:

%mydatasetname.firstname + ' ' + mydatasetname.lastname%

Expressions such as these can be used in any parameter in any action. The AML code to display the data in a message box would look like this:

<AMMESSAGEBOX MESSAGETEXT="%mydatasetname.firstname%" 
WINDOWTITLE="The firstname of the current record is">

At runtime the text %mydatasetname.firstname% is replaced by the contents of the subject of the current record.

The percent signs (%) at the beginning and end of the variable name indicates that the text in-between the percent signs is an expression and should not be taken literally. Instead, it is replaced with the current contents of that column in the current row at runtime.

Common Dataset Fields

Most of the fields (columns) that are returned in a dataset are dictated by the action creating and populating the dataset. For example, when using the SQL Query action, the field names are controlled by the columns returned by the query. In the SNMP Get action, the field names are dependant on the SNMP query data being returned by the agent. Nonetheless, a common set of fields are available which can be used to retrieve information about any created dataset. Most of these fields are global, which means they can be used on any dataset, regardless of which action or activity the dataset originated from. These fields can be accessed in the same manner as other fields, though some of their values are read only. Also, the values of these fields are the same regardless of the row being accessed.

The table below describes the common set of fields (columns) that a dataset creates (assuming the dataset name assigned is theDataset).