Defining Snapshots (2.12)

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 . tablename integer

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 — Specify a unique short name for this snapshot, to be used within the fully qualified name of the generated table.

   • Snapshot group — Specify a group for this snapshot to be added to. Snapshot groups can be added or changed later.

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

   • Tag — 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 the automatically parsed metadata does not match the actual values your snapshot requires, you can adjust the column definitions manually. To do so, simply click the edit icon next to the column you would like to edit. The following properties are editable:

   1. Snapshot Column Name . Note that the new name must not include the pipe (|) character.

   2. Column Data Type . Select data type from the drop down. Selecting a data type different from the one currently selected will trigger schema evolution.

   3. For strings, max length allows you to edit the maximum number of characters

   4. For numeric data types, precision allows you to edit the maximum number of digits and scale allows you to edit the maximum number of decimal digits for numeric data types.

8. 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 .

9. When you are temporarily done with this activity, click Save Draft . Or if you are done defining the activity, click Publish .

   Notice that after you have published 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

• Editing and Managing Snapshots

• Scheduling and Running Tasks

• Handling Task Errors

• Viewing Run History