Data Table Maintenance

Perform the following steps to add a new data table with columns.

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. Click the "Add Data Table" icon.

   

3. Enter the Name, Description, and select the Target Folder.

   Data table names can only contain alpha-numeric values and underscores. White space is not accepted.

4. Click OK.

   The new Data Table appears in the folder list beneath the selected parent folder and the right panel now displays the title of the table.
   Before you can add data, you need to set the schema of the table.

5. Select View > Columns to choose which columns to display when you are looking at Data Table schema.

   

6. Click the "Add" icon.

   

   A new row will appear in the schema table.

7. Enter a column Name and Description.

   Column names can only contain alpha-numeric characters and underscores. White space is not allowed.

8. Select the data Type of the column: Integer, Float, String, or Secure.

   • The Secure Data Type is only available for new columns. The Secure Data Type option is not available when modifying existing columns.
   • Refer to Secure Data Table Columns for additional information.

9. (Optional) Select the Index option if you will be using BMQL statements.

   • By selecting an index, performance will be improved when you are using the column in a "where" clause.
   • Refer to Data Table Indexing for additional information.

10. (Optional) Select the Key option to identify which Natural Keys will be unique identifiers.

    • Natural Keys allow administrators to provide only changed records in bulk upload files that modify Data Table data.
    • Refer to Natural Keys for additional information.

11. Repeat steps 6 - 10 to add more columns.
12. Click Save.

Complete the following steps to add a Column to an existing Data Table.

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. Open the applicable 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.

   

3. Click the "Add" icon.

   

   A new row will appear in the schema table.

4. Enter a column Name and Description.

   Column names can only contain alpha-numeric characters and underscores. White space is not allowed.

5. Select the data Type of the column: Integer, Float, String, or Secure.

   • The Secure Data Type is only available for new columns. The Secure Data Type option is not available when modifying existing columns.
   • Refer to Secure Data Table Columns for additional information.

6. (Optional) Select the Index option if you will be using BMQL statements.

   • By selecting an index, performance will be improved when you are using the column in a "where" clause.
   • Refer to Data Table Indexing for additional information.

7. (Optional) Select the Key option to identify which Natural Keys will be unique identifiers.

   • Natural Keys allow administrators to provide only changed records in bulk upload files that modify Data Table data.
   • Refer to Natural Keys for additional information.

8. Repeat steps 3 - 7 to add more columns.
9. Click Save.

Once you have set the schema for your table, you can begin to add data.

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. Click the Data tab in the Edit Data Table panel.

   The columns you created in the Schema tab now appear on the Data Table.

3. Click the "Add Row" icon.

   

   A blank row appears.

4. Enter values for each column.

   

   You can either click into each field or you can use Tab to move quickly through the fields.
5. Repeat Steps 3 and 4 until your table is complete.
6. Click Save to save your changes.

The final step to manually adding a table is to make sure it is deployed.
The names of tables that have been added or modified and are not deployed are proceeded by an orange status icon.

Complete the following steps to deploy a single Data Table.

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. To deploy a single Data Table, select one of the following methods.

   • Click Deploy when you have a Data Table open.

     

     - OR -

   • Right-click on the Data Table name and click Deploy.

     

3. To deploy multiple Data Tables, complete the following:

   1. Select the undeployed Data Tables in the side navigation panel.

      

   2. Select Deploy Selected from the side navigation panel drop-down menu.

      

4. Click Deploy.

   The "Undeployed" status icon is removed from the deployed Data Tables and the table deployments are added to the Status Log.

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 the Admin Toolbar, click on the Add Folder icon.

   

   The New Folder dialog box appears.

3. Enter the folder Name and Variable Name and click OK.

   

   The new folder will now be displayed in the Folder List.

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. Select one 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

   

   The folder name is the only property that can be modified.

3. Click Save to save your changes.

Administrators can add a new Foreign Key column to a Data Table. The procedure below assumes another Data Table has already been created with a single Natural Key column defined to establish a parent/child relationship.

To add a new Foreign Key record to a Data Table, perform the following steps:

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. Open the applicable 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.

   

3. Click the Schema tab.
   

4. Click Add Foreign Key.
   The Foreign Key Column window displays.

   

   1. In the Name field, type the name of the column to establish a Foreign Key relationship.
   2. (Optional) Enter a Description of the Foreign Key.
   3. (Optional) Select the Index option if you will be using BMQL statements.
   4. From the Target Table drop-down, select the parent table to which the Foreign Key relationship is established.
   5. From the Delete Behavior drop-down, select one of the following three options:
      • On Delete No Action – deletion of a referenced record from the parent Data Table is prevented
      • On Delete Set Cascade – deletion of a referenced record from the parent Data Table results in deletion of the referencing record from the child Data Table
      • On Delete Set Null – deletion of a referenced record from the parent Data Table results in removal of the value in the Foreign Key column from the referring record of the child Data Table, leaving a null or empty value

   6. Click OK.

      The foreign key row displays in the Schema tab with the Relationship Name, Target Table, Delete Behavior, and Target Column fields populated.

      

      The Index column defaults to selected and Key column is disabled by default. Administrators cannot change these default selections.

5. Click Save to save your changes. .

Administrators can modify an existing column in a Data Table to support a Foreign Key relationship.

To modify an existing column, perform the following steps:

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. Open the applicable Data Table. This table is considered the child 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.

   

3. Click the Schema tab.
   

4. Click the first column of the corresponding row to select the row to create the Foreign Key relationship, then click Add Relationship.
   

   The Relationship screen displays.

   

5. In the Name field, type the name of the relationship for this Foreign Key.
6. From the Target Table drop-down, select the parent Data Table to which the Foreign Key relationship is established.
7. From the Delete Behavior drop-down, select one of the following three options:
   • On Delete No Action – deletion of a referenced record from the parent Data Table is prevented
   • On Delete Set Cascade – deletion of a referenced record from the parent Data Table results in deletion of the referencing record from the child Data Table
   • On Delete Set Null – deletion of a referenced record from the parent Data Table results in removal of the value in the Foreign Key column from the referring record of the child Data Table, leaving a null or empty value
8. In the Column Mapping drop-down, select the column to establish the Foreign Key relationship.

9. Click Add Relationship.

   The selected row (new foreign key row) displays in the Schema tab with the Relationship Name, Target Table, Delete Behavior, and Target Column fields populated.

   

   The Index column defaults to selected and Key column is disabled by default. Administrators cannot change these default selections.

10. Click Save to save your changes. .

Existing Foreign Key relationships prevent the deletion of parent Data Table schema and modification of its Natural Key definition. To perform such actions, the Foreign Key relationship must first be removed from the child Data Table schema. If deleting an associated record is attempted with a Foreign Key relationship, an error message is posted indicating the deletion is denied.

To remove an existing Foreign Key relationship, perform the following steps:

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. Open the applicable 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.

   

3. Click the Schema tab.

   The existing column schema for the Data Table displays.

4. Click on the Foreign Key row to modify and then click Remove Relationship.

   
   The Confirmation window displays.

   

5. Click OK.
   The selected row displays in the Schema tab with the Foreign Key relationship removed.

6. Click Save to save your changes. .

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, or 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.

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. Open the applicable 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.

   

3. Click the Schema tab.

   

4. Check the checkboxes in the Key column to identify which Natural Keys will be unique identifiers.

5. Click Save.
6. Click the Data tab.

7. Ensure that the added natural keys are valid. Columns with valid Natural Keys look like this:

   

Invalid Data

If a Natural Key is not unique, for example, if two or more rows contain identical value(s) in the column(s) designated as Natural Keys, the record is considered invalid. Invalid cell(s) will be highlighted in red.

• If the existing data in a saved table becomes invalid upon applying Natural Keys a warning will be displayed at the top of the page.

  

• If duplicate data is introduced to a Key column. The cell is highlighted in red.The user can click on the cell to view the warning message.

  

After you have imported or deployed a Data Table, you can check the status and corresponding error logs to ensure that all of your data loaded correctly.

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. Click on the system navigation panel menu and select Status from the drop-down.

   

   The Status Log dialog box appears.

   

3. Check the status of each Data Table import or deployment.

   The Category column indicates which kind of status is displayed.

4. If available, click the Log link to check the error logs.

   

Administrator Group Access Levels for Data Tables

Administrators with Full Access rights can perform the following steps to assign administrator group data table access rights from the data table user interface.

1. Navigate to the Admin Home page.
2. Click on Data Tables under Developer Tools.
3. Open the applicable data table folder.
4. Open the applicable Data Table.
   • Double-click on 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.
5. Click the Access Rights tab.

6. Select the applicable administrator group from the Add a Group drop-down.

   Note: The access level will default to Full Access.

   

7. To change the access level for an administrator group, click on the Access drop-down for the applicable group, and then select the desired access level.

   

8. Click Save.