Data Table Overview

Overview

Data Tables provide single-page administration which includes a spreadsheet-like user interface with easy editing capabilities, powerful search features that allow searches within a single table or across all tables, and enhanced performance and indexing.

Oracle CPQ 20A updates the Data Table administration UI to JET to provide a better administration experience and a foundation to build additional Data Table functionality.

ClosedData Table Side Navigation Panel

ClosedData Table Data Tab

ClosedData Table Schema Tab

ClosedData Table Details Tab


ClosedData Table Indexing

Data table indexing is supported for single column indices on individual tables. Indexing a column has the same effect as putting a standard index on a database column: it will create a sorted list of that column for faster access to the data.

How a data table should be indexed depends upon how it is used. An admin should examine the use cases of BMQL access to the Data Tables to see which columns are used for filtering (which appear in the WHERE clause). These are the columns that are candidates for indexing.

For these indexing candidates, the next step is to examine the selectivity that each column offers. For instance, if a column has a yes or a no value, that is not a selective column since choosing a value will eliminate only half of the table. A column with a unique ID for each record, however, would be highly selective as it will narrow down a search to exactly one record.

Indexes will not work with fuzzy operators. That is, finding all rows with a column containing the letter E will not benefit from indexing that column.

Indexing and Queries

The best use of an index is when it is used with a well thought-out query plan. For each query that is run on the Data Table, for best performance, only the most selective column should be indexed.

When adding indices to queries, it is important to recognize the relative impact that an index will have.

The best candidate for indexing is a large Data Table with a selective column that is queried frequently.

Indexes cannot compensate for poorly designed queries. A query with a dozen conditions in the WHERE clause will only see a small benefit from an index unless one of the indexes is very selective; this is generally not the case with large filter criteria.

Beginning with Release 18C, administrators are warned when deploying a Data Table without an index and with the table contains more than 1,000,000 or more records. This warning is to remind administrators that deploying large tables without an index or indexes can be a performance intensive operation. This warning is provided during deployment, migration, and when processing web services calls.

ClosedData Table Storage

Data Tables are used to store product and commerce data. They can be accessed from any part of the Oracle CPQ application where BML is used. The purpose of storing complex business logic in Data Tables is for ease of maintenance.

Data Stored in Excel (CSV)

Data Stored in CPQ


ClosedForeign Keys

Beginning in Release 18B, Oracle CPQ supports Foreign Key column relationships between Data Tables within a site. Foreign Keys define a relationship between multiple Data Tables ensuring data integrity and consistency, and reducing the cost of data management and complexity when querying. This enhancement establishes a relationship between a defined Natural Key column within a parent Data Table to a defined Foreign Key column within a child Data Table. Administrators can define a maximum of five Foreign Keys per child table, as there is a limitation of five indexed columns per data table.


ClosedLive Data Tables

Live Data Tables immediately reflect saved changes to table queries without a deploy. The saved changes are immediately available for queries from SOAP APIs, REST APIs, BML functions, Export, Bulk Download, and Migration. Live Data Tables support all the features and functionality available for deployable data tables. The "Live" option determines whether a Data Table requires deploy after saving changes or not.

For JET Data Tables, administrators access the "Live" option on the Details tab for data tables.The following image shows the "Live" option enabled for the "Status" data table

Note: Changes to 'Live' Data Tables are not immediately reflected in configuration data for BOM Mapping or System Configurations. Therefore, Oracle CPQ does not recommend using 'Live' Data Tables with BOM Mapping or System Configuration.


ClosedMigration

When migrating Configuration or Commerce, a list of dependent Data Tables is displayed in a pop-up window. If these Data Tables are not selected, migration will fail. This pop-up will only appear when menu attribute validations are mapped to a Data Table, but not when the Data Table is used in BMQL rules.

If you delete an attribute or menu value when migration changes attribute menu values, the link will be removed from the Data Table.

ClosedNatural Keys

Oracle CPQ has included Natural Keys within Data Tables to improve efficiency. As a result of Natural Keys, the bulk upload files that modify Data Table data now only require the table records that are to be changed. Natural Key can be a single column or a combination of several columns that produce a unique identifier for each record (row). The creation of these Natural Keys reduces the maintenance downtime of Data Tables by including only Data Table changes upon upload. Users no longer need to purge and reload all Data Table rows to edit a subset of rows.

Natural Keys are supported in: Data Tables, Web Services API, CSV Upload and Bulk Upload.

For JET Data Tables, the "key" icon denotes designated key columns.

When using the Data Table Natural Keys feature, it is strongly recommended that no more than five string columns be assigned to a part of the key.

ClosedSecure Data Table Columns

Administrators can use the secure data type option for new columns in both new and existing Data Tables. Confidential client credentials are required to connect to other Oracle products and applications. Secure Data Table Columns provide a method for securely storing confidential credentials in CPQ. Secure columns always store the encrypted form of the data in the data table. The only way to access this data in its original, decrypted form is through BMQL.

The following table lists the expected incoming data format to create or update data in secure columns.

Interface Encrypted Decrypted

Admin UI Table Editor

  ü

Import via Admin UI

  ü

*Bulk Upload (see note below)

  ü

Web Services

  ü

Package Upload

ü  

The following table shows the secure column data output for various Oracle CPQ functions.

Interface Masked Encrypted Decrypted

Admin UI Table Editor

ü    

Export via Admin UI

  ü  

*Bulk Download (see note below)

  ü  

Web Services Get API

  ü  

Web Services Add/Update APIs

  ü  

Package Download

  ü  

Jython

  ü  

Resources

  ü  

**bmql() select fields (see note below)

    ü
*If a data table with a secure column is exported or downloaded using a bulk download, then the same content is uploaded using import or a bulk upload, values are double encoded, changing their value.

**For password security, do not include in thowerror message or print the password value within the BML script. Otherwise when the BML print log is enabled, the cleartext password will be visible in the bm.log.

The data entry is masked when entered into a secure data table column.

Masked Data in Secure Data Table Column

Secure column data is not available for the following uses:

If secure columns are referenced, the following BML statements generate errors:

If a BML script contains one of the following BMQL statements with secure column references, errors are generated during a save:

Web services criteria for Get and Delete APIs return a SOAP fault.

While strongly discouraged, accessing Secure Data Table Columns for Dynamic Pick Lists is possible. Querying secure column encrypted data has virtually no value to customers.


Administration

ClosedData Table Limits


ClosedAccessing Data Tables

Information in Data Tables can be accessed from anywhere in your Oracle CPQ application that uses advanced functions.

Complete the following steps to access Data Tables:

  1. Navigate to the Data Table Administration page.
    1. Click Admin to go to the Admin Home Page.

    2. Click Data Tables in the Developer Tools section.

      The  Data Table Administration page opens.

  2. From this page, you can add tables and folders, import and export table files, and perform searches across tables.


Notes

The first column of a Data Table can be populated with an empty string. A row with data must follow.
Empty rows are not allowed on Data Tables with a Primary Key. This does not apply to Data Tables that do not have a key defined.

Related Topics

Related Topics Link IconSee Also