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.
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.
- Data tables that have not been deployed are preceded by a orange status icon.
- Live data tables, which immediately reflect saved changes without deploying, are preceded by a green status icon.
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:
- Double-click on the data table folder or the data table
- Select the data table and then click the "Edit" icon, or
- Right-click on the data table and the select "Edit" from the pop-up menu.
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:
- Select the data table folder and then click the "Edit" icon, or
- Right-click on the data table folder and the select "Edit" from the pop-up menu.
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 navigation panel can be collapsed to provide a larger viewing area for the data table.
- Administrators can sort by columns by clicking on the column header
- Column resize allows users to enter a precise column width.
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.
Oracle CPQ 22B introduced the ability to export or delete filtered data table records. The new options for filtered records allow customers to export or delete specific sets of records from data tables that contain a large number of records.
The Filtered Data drop-down menu appears when a user filters data using a simple or advanced search.
The new Filtered Data drop-down menu provides Export and Delete options.
When there are filtered records, users can also access Export Filtered and Clear Filtered actions by right-clicking on the applicable data table name.
When users select the Filtered Data > Delete option or the Clear Filtered option from the table drop-down menu a Confirmation dialog will appear to make sure the user wants to delete the filtered records.
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.
The data table Access Rights tab allows administrators to assign administrator group data table access rights directly from the data table user interface.
Administrator Group Access Levels for Data Tables
Administrator group functionality provides administrator group access rights at the data table level. In addition to providing access at the data table level, administrators can now employ administrator groups to assign View, Edit, and Full Access permissions. The following administrator group access levels are now available for data tables:
-
No Access: Members of this group have no access to the data table.
When a new administrator group is added, the group is assigned No Access to all existing data tables.
- View Access: Members of this group are only able to view the contents of the table.
- They don't have the ability to edit, delete, or deploy the data table.
- They can export data records, but can't import data records or make any changes to the schema.
- Edit Access: Members of this group can view, add rows, and edit the contents of the data table.
- They can add new rows to the data table and modify content.
- They can deploy the data table changes.
- They can import new rows if there are no schema changes
- They can't modify data table schema or delete an existing data table.
- Full Access: Members can read, edit, and delete existing records and data tables. They can also make changes to the data table schema.
- They can add new rows into the data table.
- They can view, modify, and delete records of an existing data table.
- They can make changes to the data table schema.
- They can deploy the data table changes, including data table schema changes.
- They can import new rows, including row with schema changes.
- They can delete existing data tables.
- This is the default access level when assigning access rights to an existing administrator group.
Data Table User Interface with Access Rights Applied
When access rights are applied to an administrator group, unavailable actions will be grayed out for members of the group. The View icon 
is displayed when the user only has view access.
View Access Rights
The following image shows the data table user interface for View Access group members. The edit, delete, and deploy actions for the data table are not accessible.
Note: Even though the Import action is shown as an available action, group members with View Access will not be able to import data tables.
Edit Access Rights
Group members with Edit Access have more actions available, but are not able to delete tables or make schema changes. The following image shows the Data and Schema tabs for Edit Access group members.
Full Access Rights
All actions are available for group members with Full Access. The Access Rights tab is displayed for group members that have Group Administration privileges. If the user does not have Access Administrator privileges, they will only be able to view access rights. The following image shows the Schema tab for Full Access group members.
Notes:
- When assigning or modifying data table access rights for an administrator group, changes must be saved but the affected data table does not need to be deployed.
- Deleting a group from the Access Rights tab in the data table UI does not remove access to the data table folder. To remove access to a data table folder, you must go to Group Administration UI.
- Refer to Assign Data Table Access Rights to assign administrator group data table access rights from the data table user interface.
- Refer to Create and Edit Administrator Groups for administrator access group procedures.
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.
- Tables that are rarely queried will not benefit from indexing.
- Tables that are small (on the order of tens to thousands of records) will not benefit from indexing.
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) |
ü |
**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:
- Formulas: query() conditions
- Formulas: query() return column
- Table-based rules: filter column
- Table-based rules: Constraint message
- Table-based rules: Constraint value
- Table-based rules: Recommendation message
- Table-based rules: Recommendation value
- Table-based rules: Recommended item properties
If secure columns are referenced, the following BML statements generate errors:
- gettabledata() select fields
- gettabledata() where fields
- bmql() order by fields (dynamic)
- bmql() where fields (dynamic)
If a BML script contains one of the following BMQL statements with secure column references, errors are generated during a save:
- bmql() order by fields (static)
- bmql() where fields (static)
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
The following table provides the data table limits. These limits cannot be increased.
| 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:
- Navigate to the Data Table Administration page.
-
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.
Notes
Performing advanced filter or REST API services on multiple large data tables can cause performance issues. Beginning in Oracle CPQ 22B, the number of data tables that can be selected in the Global Menu Advanced Filter is limited to five data tables to minimize potential performance issues. Customers are still encouraged to use the table-level filters to search for specific values.
If you would like to modify the multiple data table selection limit for your site, submit a Service Request (SR) on My Oracle Support. Changing this limit is strongly discouraged because increasing this limit can potentially lead to performance issues on your site.
- 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.
- Make a local back-up of a Data Table by exporting it before making any changes. This is a quick and easy way to recover the Data Table in the event of errors.
- Make all changes to a test site first.
