Using custom structure tables
TrueSight Capacity Optimization can host different types of data, and one such type is a custom structure – a type of data that represents generic records with custom attributes.
The custom structure data type in TrueSight Capacity Optimization allows for the creation of the following custom structure tables:
- Buffer table: Contains data that is copied into TrueSight Capacity Optimization for further processing, but is generally not important for direct analysis.
- Item-level detail table: Contains data that represents the details of items that are important for analysis purposes, for example, errors on a specific page.
Although they have a different purpose, the implementation method for both is the same; for this reason, both types are generally referred to as buffer tables.
Custom structure tables are not supported when the ETL is deployed on a remote ETL Engine Server.
Inside an ETL, you can perform the following operations related to custom structure tables:
- Create a buffer table or modify its structure.
- Load data into a buffer table either in append or update mode.
- Truncate (empty) a buffer table.
Creating a buffer table
To create a buffer table, use the following code:
In the preceding code,
$conf is the
ETL::Conf object that is obtained using the
extract method via
The parameters used in the code are:
<tablename>: Name of the new buffer table; by convention, it starts with the
<column_names_semic_sep>: The semicolon-separated list of column names; column names can contain alphanumeric characters and are case insensitive.
<column_types_semic_sep>: The semicolon-separated list of column types; the available types are:
CHAR, VARCHAR, VARCHAR2
<key_column_names_semic_sep>: (Optional) a semicolon-separated list of column names that are part of the primary key of the table, this is, column names that uniquely identify records in the table; they must be a subset of column names listed in
<column_names_semic_sep>. If this parameter is omitted, the table is created without a primary key.
When this method is called, the table is immediately created in the TrueSight Capacity Optimization Data Warehouse, reflecting the structure specified. During the creation process, the method also checks whether or not a table with the same name already exists in the TrueSight Capacity Optimization Data Warehouse. If it does not exist, it is created with the specified structure and primary key; if it does exist, its structure is changed to reflect the new specification, columns are added and removed, and the primary key is modified.
Changing the table structure in a way that removes an existing column may result in data loss.
Loading data into a buffer table
The ETL populates a
ETL::DataSet object to load data into a buffer table. For example, to append all rows of the dataset to the BUF_MYTABLE table created in the preceding example, you can write:
my $buffds = new ETL::DataSet("BUF_MYTABLE"); $buffds->setColumns("CATEGORY","VALUE"); my @row=$buffds->newRow(); $buffds->fillRow("CATEGORY",120,\@row); $buffds->fillRow("VALUE",24,\@row); $buffds->addRow(\@row);
No verification is made against rows that already exist. Specifying a prefix during dataset creation facilitates an update operation; the available prefixes are:
APPEND: Equivalent to the default behavior
UPDATE: Each row in the dataset is verified. If a row with the same primary key exists in the table, it is updated with new values; if it does not exist, a new row is appended.
DELETE: Each row in the dataset is deleted from the buffer table; primary key values are used to determine which rows have to be deleted while other columns are ignored.
TRUNCATE: All rows in the table are deleted; the content of the dataset is ignored.
The execution order of the requested dataset operations is specified by the order of datasets in the
DataSetList returned by the extractor.