Data Table Side Navigation Panel
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.
Data Table Side Navigation Panel
The Data Table side navigation panel displays folders and tables in a hierarchical structure. The navigation panel toolbar provides additional menu items, simple and advanced filtering, and action icons for folder and table maintenance functions. The JET data table UI utilizes the full browser page and administrators can close the side navigation panel to allow a larger table editing space. Status icons displayed next to data tables indicate live and undeployed data tables.
The drop-down menu provides access to import data tables, select/deselect all tables, expand/collapse all tables, and see the Status Log. The export, deploy, and clear data options are available when data tables are selected.
You must use the drop-down menu to perform export, deploy, or clear data actions for multiple tables. When you select multiple tables and perform an action from the right-click menu, the operation will only be performed on the single table highlighted with dotted lines.
The simple filter searches the data table names and filters the list of tables using the provided text. The advanced filter, which behaves the same as the Legacy filter, searches the contents of the specified tables and filters the list of tables. If tables are not specified in the Advanced Filter, all data tables are searched. After an Advanced Filter is applied, click "Edit Filter" to modify the search criteria.
To add a new data table click the "Add Data Table" icon, and then provide the name, description, and select the target folder.
Administrators can use either of the following methods to open a data table:
To add a new folder click the "Add Folder" icon, and then provide the name and variable name.
Administrators can use either of the following methods to edit a data table folder:
Note: The folder name is the only property that can be modified.
The Data tab displays the data content of the table that is currently being edited. The Data tab is the default tab, as it is in Legacy. Administrators can view and modify the table data on the Data tab. The JET UI provides several usability enhancements for data tables:
The table toolbar provides quick access to view options, filtering, add, delete, and undo actions. The simple and advanced filters in combination with the column and pagination view options improve the user experience especially for data tables containing a large number of columns and/or lines. Additionally, the undo icon now allows administrators to undo all unsaved changes for the selected row.
The View menu provides options to show/hide columns and set the number of rows displayed per page.
To use the simple filter click on table toolbar Simple Filter icon, type the desired keyword or phrase, and then press <Enter> or <Return>.
If the keyword is present in any column, the resulting lines are returned. To deactivate the filter, click on the Simple Filter icon. 
To use the advanced filter click on the table toolbar Advanced Filter icon.
The Advanced Filter dialog opens. Define the filter criteria. In the first column, select the desired column names. In the second column, select the applicable operator. In the third column, define the search criteria. Select "Match Any" or "Match All". Click Apply to filter results.
If the keyword is present in any column, the resulting lines are returned. To refine the advanced filter, click Edit Filter.
To deactivate the filter, click on the Advanced Filter icon.
The Schema tab defines the columns and data types that make up the data table. This tab supports add and delete actions as well as index, key, and validation functions. It also provides access to Foreign Key and Relationship management.
The JET column view and filters are also available on the schema tab. The Schema tab also provides an undo icon which allows administrators to undo the most recent unsaved action.
The new Details tab shows the date when the table was added, last modified and deployed. It provides the "Live" Data Table option, and allows administrators to add a description for the table.
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.
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.
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
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.
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.
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.
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.
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) |
ü |
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.
| Item | Limits | ||||
|---|---|---|---|---|---|
| Total Columns in a Table |
100 Columns (50 String, 25 Integer, 25 Float) * Secure Data Table Columns count against the 50-column limit for String columns in a single table. |
||||
| Total Rows in a Table | 1,000,000 + Rows | ||||
| String Characters in a Cell |
255 Characters For data with secure columns, the character limit is more restricted due to the encryption process.
|
||||
| Integer Characters in a Cell | 9 Characters | ||||
| Float Characters in a Cell | 16 Characters | ||||
| Table Names |
20 Characters Unique (case insensitive) Must look like variable names:
|
||||
| Column Names |
20 Characters Unique within the Table (case insensitive) Must look like variable names:
|
||||
|
Invalid keywords for Table and Column Names |
Many of these keywords are used in BML. They cannot be used in Table or column names.
|
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:
Click Admin to go to the Admin Home Page.
Click Data Tables in the Developer Tools section.
The Data Table Administration page opens.
From this page, you can add tables and folders, import and export table files, and perform searches across tables.
|
21A What's New | Customer Support | Contact Us |
      |