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.

Note

If the ETL is deployed on a remote ETL Engine, custom structure tables are supported only when you apply Cumulative Hotfix 15 on Patch 1 (11.5.01) of TrueSight Capacity Optimization 11.5.

 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 one of the following codes:

((DBConf)conf).prepareBufferTable(<tablename>,<column_names_semic_sep>,
<column_types_semic_sep>,<key_column_names_semic_sep>);

In the preceding code, conf is the DBConf object that is obtained using the extract method via this.getConf().

The parameters used in the code are:

  • <tablename>: Name of the new buffer table; by convention, it starts with the BUF_ prefix.
  • <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:
    • NUMBER
    • CHAR, VARCHAR, VARCHAR2
    • DATE
    • TIMESTAMP
  • <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.

Note

Changing the table structure in a way that removes an existing column may result in data loss.

Example
  • Use the following code to create a new buffer table with the name BUF_MYTABLE
    that contains the following two columns:
    • CATEGORY of type NUMBER (This column is also the primary key)

    • MY_ORIG_COLUMN of type VARCHAR2(50)

      ((DBConf)conf).prepareBufferTable("BUF_MYTABLE","CATEGORY;MY_ORIG_COLUMN","NUMBER;VARCHAR2(50)","CATEGORY");

  • After you create the BUF_MYTABLE buffer table using the preceding code, running the code in the following example will overwrite MY_ORIG_COLUMN column with a new MY_NEW_COLUMN column. MY_NEW_COLUMN will be added to, and MY_ORIG_COLUMN will be dropped from the TrueSight Capacity Optimization Data Warehouse.

    ((DBConf)conf).prepareBufferTable("BUF_MYTABLE","CATEGORY;MY_NEW_COLUMN","NUMBER;VARCHAR2(50)","CATEGORY");

Loading data into a buffer table

The ETL populates a 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:

DataSet buffds = new DataSet("BUF_MYTABLE");
buffds.setCustomStructureDataMode(true);
buffds.setColumns(new String[]{"CATEGORY","VALUE"});
String[] 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.
Example

  • To update the record with a value of 120 to the CATEGORY column, write the following code:

    DataSet buffds = new DataSet("UPDATE:BUF_MYTABLE");
    buffds.setCustomStructureDataMode(true);
    buffds.setColumns(new String[]{"CATEGORY","VALUE"});
    String[] row = buffds.newRow();
    buffds.fillRow("CATEGORY","120",row);
    buffds.fillRow("VALUE","24",row);
    buffds.addRow(row);

  • To delete the record with a value of 120 to the CATEGORY column, write the following code:

    DataSet buffds2 = new DataSet("DELETE:BUF_MYTABLE");
    buffds.setCustomStructureDataMode(true);
    buffds.setColumns(new String[]{"CATEGORY","VALUE"});
    String[] row = buffds.newRow();
    buffds.fillRow("CATEGORY","120",row);
    buffds.addRow(row);

  • To truncate the BUF_TABLE table, write the following code:

    DataSet buffds3 = new DataSet("TRUNCATE:BUF_MYTABLE");

Note

The execution order of the requested dataset operations is specified by the order of datasets in the DataSetList returned by the extractor.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*