DataCube Export Integration

Oracle CPQ has a maximum (CLOB) property that limits the number of DataCube columns that administrators can enable for a CLOB per Commerce process.

Enable or Disable CLOB for DataCube Columns

Administrators can use a CLOB attribute in the mapping XML file to enable or disable CLOB on a per column basis. To enable CLOB for a column, set the CLOB attribute to "true". To disable CLOB for a column, set the attribute to "false". Administrators can also omit the CLOB XML attribute. In doing so, the column is not made into a CLOB.

Oracle CPQ creates a data file per CLOB column. The data file contains all data entries for a column, delimited by "<ER>". As shown in the following figure, the data file name follows the following format: <table name>_<attribute name>.dat.

Administrators can place a filter on the mapping XML file for a process data column, which can have no filter, one filter, or multiple filters. Each filter contains the attributes shown in the following table.

In the following Mapping XML File with Filter Attributes sample, all Transactions with "_ship_To_country" set to "USA" and "_bill_to_company_name" set to "Oracle" are included in the DataCube report.

The DataCube mapping XML file contains an "export_subpath" attribute under the reporting root directory. This attribute allows administrators to name the folder where DataCube reports are stored. As shown in the below example, the folder is a sub directory of the "reporting_root" folder. The folder also specifies the sub directory that a reporting system sends a report during export.

For example: Assume an administrator creates a DataCube integration in the Integration Center, sets the "Remote Directory" to '/my/reports', and uses the mapping XML file for a particular report to set export_subpath = "NorthAmerica". After generating the report, it is zipped and sent to a remote server in the /my/reports/NorthAmerica/ directory.

Beginning in Oracle CPQ 23D, CPQ will support exporting transaction arrays in DataCube exports. Main and sub-document transaction arrays and array set attributes can be added to the DataCube XML mapping file. Likewise, the default generated sample mapping.xml file now includes all array sets and array set attributes for the specified process. DataCube exports also support filtering of transactions based on the criteria provided for the array set attributes.

For example, the following DataCube XML mapping file includes a main document array set (services_c) and a sub-document array set (shippingOptions_c). The <array_set> tag is used to define the transaction arrays. The example filter (highlighted in green) will only export records where the “region_c” attribute values are “North America”.

Exported zip files will include .ctl and .dat files for each transaction array listed in the XML mapping file. The following example highlights the services_c (bm_rt_3023066262.ctl and bm_rt_3023066262.dat) and shippingOptions_c (bm_rt_3023068615.ctl and bm_rt_3023068615.dat) transaction array files included in the DataCube export.

The Integration Center contains a DataCube integration type, which allows administrators to control their own SFTP and database credentials. DataCube output files are sent to a remote server using the following information entered in the Integration Center when configuring a DataCube integration.

1. Navigate to the Integration Center.
   1. Click Admin to go to the Admin Home Page.

   2. Click Integration Center in the Integration Platform section.

      The Integration Center page opens.

2. Select DataCube Integration from the Type menu.

   The DataCube Reporting Setup appears

   

   2. Set the following SFTP parameters:

      1. Server: The name of the SSH server to which DataCube reports are sent.

      2. Username: The login name to use when connecting to the SSH server.

      3. Password: The login password to use when connecting to the SSH server.

      4. Remote Directory: The directory under which all DataCube reports are sent.

         • A blank value will default to the user's login directory.
      5. Known Host Key: The upload file with the Known Host Key.
         The Known Host Key format is the truncated OpenSSH format (as produced by ssh-keygen)
         <key-type>(SPACE)<key-data>
         where <key-type> is ssh-rsa or one of the supported EC formats (e.g. ecdsa-sha2-nistp256, ecdsa-sha2-nistp384, or ecdsa-sha2-nistp521) and <key-data> is Base64 encoded of raw public key data from ssh-keygen tool.
         For example:
         ssh-rsa AAAAB3NzaC1yc2EAA... and ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNo...
         
         If you don't have the Known Host Key, log a Service Request on My Oracle Support.

      6. Port: The port number to use when connecting to the SSH server.

         • A blank value will use the SSH default port 22.
3. Set the following Database parameters:

   1. Server: The database server that the DataCube report's SQL will run on.

   2. Username: The login name to use in the DataCube report's SQL.

   3. Password: The login password to use in the DataCube report's SQL.

      The database parameter fields must be filled in. This information indicates the database to import the DataCube into. If you do not have a specific database for the DataCube import, you can enter dummy data into the fields as this information is irrelevant for your use case.
      The DataCube extraction process will create a reports.bat batch file. This .bat file will format a sqlplus import command using the information entered in the Database parameters section so the file can be processed directly by the database server. Depending on the database type (SQL/ No SQL), additional steps may be required to process the data and insert to the database server. The database information is not used to create a connection to the database server.
4. Click Save.

5. Click Test to verify your connection.

6. Click Test Upload to test file upload. A successful file upload test displays a Test Connection Passed message. The remote server is sent an SFTP test file named CPQ-SFTP-TEST.TXT and contains a UTC timestamp (i.e. 2022-05-06T16:02:59.170Z).

1. Navigate to the Processes page.
   1. Click Admin to go to the Admin Home Page.

   2. Click Process Definition in the Commerce and Documents section.

      The Processes page opens.

If DataCube Export is enabled, a DataCube Reporting button appears on the Processes page.

If you do not see the DataCube Reporting button, contact your Oracle CPQ application sales representative to evaluate your access to the DataCube Export feature.

• You will need to specify where (which third party system) to send the files. The files are usually transmitted via SFTP.
• You will also have to obtain encryption and decryption keys.

2. Click DataCube Reporting.

   The Reporting Setup page opens.

2. Click New Report.

   Administrators can schedule multiple DataCube reports. Each report can have its own mapping XML file and its own scheduled time, reporting period, export location, CLOB attributes, and defined filter. Filtering Transaction data from a DataCube allows administrators to schedule multiple DataCube reports with each report containing different data.
3. Enter a Name for the report.

4. Enter the time in the Generate Reports At field.

   The system time displays in the upper right of the page. The time zone displays in the upper left of the page. A Recurrence Interval field also displays on the DataCube Reporting Setup page. Use this field to specify the number of days between each report generation cycle.

5. Enter a number in the Recurrence Interval field.

   Use this field to specify the number of days between each report generation cycle.

6. Enter a Number in the Number of days of data to export field.

7. Click Create a New Mapping File to download the default mapping XML.

   The XML will look something like this:

   

8. Edit the mapping file in an XML editor.

   • The XML will tell you which data is included and what files it's placed in to, as well as the variable name of the columns.

9. Update the variable names for the database ID fields.

   • There should be no additional changes.
   • See below, in the sample XML, for an example of the relationship between Commerce Attributes and the 3rd party database field IDs.
10. Save the completed mapping file.
11. Click Choose File next to the Upload Mapping File button to upload the file into CPQ.
12. Click Apply.

Example XML (Upload)

1. Navigate to the Processes page.
   1. Click Admin to go to the Admin Home Page.

   2. Click Process Definition in the Commerce and Documents section.

      The Processes page opens.

2. Click DataCube Reporting.

3. Select the Logs link next to a specific report to open.

   The DataCube Reporting Logs window appears.

   • This page summarizes the generation and export of the report over the past seven days.
   • In the Description column, "Commerce Reporting" indicates report generation and "DataCube Export" indicates the export of the report.
   • Each log entry contains a Status: Running, Completed, or Failed.
   • A short message details the start time, end time, and duration of completed tasks. For failed tasks, the main cause for failure displays. For additional details, administrators can refer to the error logs (bm.log).