Defining Snapshots (2.9)

Uses of Snapshots

With snapshots, you can easily save data for later inspection by regulators; the snapshot can pull data from multiple tables as needed, and the system applies a tag to the records.

When you have run a snapshot multiple times, applying a different tag each time, you can examine how that data changes over time. In particular, you can build a cube on the snapshot data, using the tag values as a dimension.

Snapshot Tables

For each snapshot, the system will generate a table. The table name is based on the snapshot definition, and the columns are based on the query used in the snapshot definition. The table name format is as follows:

halp_snapshot_snapshot.tablenameinteger

Where snapshot is the Short Name of the snapshot and tablename is the short table name, both of which you provide when defining the snapshot. At the end of the name, integer indicates the version number. (Note that halp_snapshot is a default part of this name but is configurable.)

If you change a snapshot definition so that it defines a different set of columns, the system automatically increments the version number and generates a new table that has the required columns. For example, when you define a snapshot, the generated table name may be halp_snapshot_Sample.SampleTable1, but if you redefine the query, the new generated table name becomes halp_snapshot_Sample.SampleTable2, and so on.

Defining a Snapshot

To define a snapshot:

1. Click the Snapshot icon in the application menu.

2. Click New Snapshot.

   The system displays a dialog box where you specify initial information.

3. Specify the following information:

   • Name—Required. Specify a unique name for this snapshot.

   • Short Name—Required. Specify a unique short name for this snapshot, to be used within the fully qualified name of the generated table.

   • Table Name—Required. Specify a unique short table name (also to be used within the fully qualified name of the generated table).

   • Tag—Required. Specify a short string that becomes a tag applied to the snapshot when the snapshot is executed. As an example, a tag could indicate the state of the data, such as Preliminary or Final.

   • Description—Type a description of this snapshot and its purpose.

   Except for Short Name and Table Name, you can edit these values later as well.

4. Click Submit.

   Now you can define the rest of the snapshot.

5. To specify the query that returns the data for the snapshot:

   1. For SQL Statement, specify an SQL SELECT statement that retrieves the values you want in the snapshot. The query can refer to multiple tables. The query can use * where that is syntactically valid.

   2. Click Parse SQL Statement.

   The system then tries to parse the SQL statement and determine the structure of the table it will generate and populate. If the system can parse the SQL, the Columns section then displays the fields of the new table, and the Indices section displays the indices it will generate for the new table.

   If the system cannot parse the SQL, a warning is displayed, and you can edit the query.

6. Optionally add custom indices to this table. To add an index:

   1. Click New Index.

   2. For Index Name, type a unique index name.

   3. For Property 1, select the field to index.

      To create an index on multiple fields, repeat with Property 2 and so on.

7. If there is a table that you want to lock while performing this snapshot (in addition to the table or tables used in SQL Statement):

   1. Click Add Table.

   2. For Table Name, type the fully qualified table name (not just the short name).

   3. Click Submit.

8. Click Save.

   Notice that after you have saved a snapshot for the first time, the Tables To Lock For Snapshot section lists the table or tables to which SQL Statement refers.

See Also

• Using the SQL Explorer

• Editing and Managing Snapshots

• Scheduling and Running Tasks

• Handling Task Errors

• Viewing Run History