1. Home
  2. Latest Release Docs
  3. Guides
  4. Concepts
  5. Tools
  6. References
    1. Apache Spark
    2. Built-in Functions
    3. Incorta Component SDK
    4. Incorta Connector SDK
    5. Connectors
      1. AlloyDB
      2. Amazon Web Services (AWS) Athena
      3. Amazon Web Services (AWS) DynamoDB
      4. Amazon Web Services (AWS) RedShift
      5. Amazon Web Services (AWS) S3
      6. Apache Drill
      7. Apache Hadoop HDFS
      8. Apache Hive
      9. Apache Kafka
      10. Apache Kafka V2
      11. Autoline
      12. Box
      13. Cassandra (Simba)
      14. Cassandra (Incorta)
      15. Cisco Meraki
      16. Cosmos DB
      17. Custom CData
      18. Custom SQL
      19. Dropbox
      20. File System (LocalFiles)
      21. Freshservice
      22. FTP
      23. Google BigQuery
      24. Google Cloud Storage (GCS)
      25. Google Drive
      26. Google Sheets
      27. GraphQL
      28. IBM DB2
      29. IBM Netezza
      30. JD Edwards
      31. JIRA
      32. JSON
      33. Kyuubi
      34. Local Files
      35. MariaDB
      36. Microsoft Azure Gen2
      37. Microsoft OneDrive
      38. Microsoft SharePoint
      39. Microsoft SQL Server
      40. Microsoft SQL Server (JTDS)
      41. MongoDB BI
      42. MySQL
      43. NetSuite - Saved Searches
      44. NetSuite - SuiteAnalytics
      45. NetSuite - SuiteQL
      46. NetSuite - Web Service
      47. OneLogin
      48. Oracle
      49. Oracle B2C Service
      50. Oracle BI Publisher
      51. Oracle Cloud Applications (UCM)
      52. Oracle Cloud Applications
      53. Oracle CPQ
      54. Oracle EPM
      55. Oracle Essbase
      56. Oracle OTM/GTM
      57. Oracle Sales Cloud
      58. Oracle WMS
      59. PostgreSQL
      60. Presto
      61. Salesforce v2
      62. SAP ERP
      63. SAP Hana
      64. SAP Sybase IQ
      65. ServiceNow
      66. SFTP
      67. Smartsheet
      68. Snowflake
      69. Splunk
      70. Teradata
      71. Vertica
      72. XML
      73. Zuora
    6. Data Studio
    7. Public API
    8. Security
    9. SQL Interface
    10. Visualizations
    11. Other
  7. Integrations
  8. Data applications
  9. Release Notes
  10. Incorta Premium

Connectors → Oracle BI Publisher

About Oracle Business Intelligence Publisher (BI Publisher)

Oracle Business Intelligence Publisher (BI Publisher) is a powerful enterprise reporting solution designed to streamline report and form generation by decoupling data extraction from report layout design. Users can create and customize report templates using familiar tools such as Microsoft Word and Adobe Acrobat, enabling XML data to be rendered into various formats (PDF, Excel, HTML, etc.) and multiple languages.

About the Oracle BI Publisher Connector

The Oracle BI Publisher connector leverages BI Publisher APIs to provide an SQL-like interface for direct access to the underlying database. This approach effectively overcomes the limitations of Oracle Cloud Applications connector, which provides access to available View Objects (VOs).

It is recommended to use the BI Publisher connector to ingest data from tables that do not have a corresponding VO preconfigured in BICC.

However, this connector is not recommended for handling large datasets. For such scenarios, Oracle Business Intelligence Cloud Connect (BICC) and the Oracle Cloud Applications connector are more suitable for efficient data extraction.

Connector Availability

This connector is available in the Marketplace for Incorta releases 2024.1.7, 2024.7.x, and later.

Supported features

The Oracle BI Publisher connector supports the following Incorta-specific functionalities:

FeatureSupported
Chunking✔
Data Agent
Encryption at Ingest
Incremental Load✔
Multi-Source✔
OAuth
Performance Optimized✔
Remote
Single-Source✔
Spark Extraction
Webhook Callbacks✔

Steps to connect Oracle BI Publisher and Incorta

To connect Oracle BI Publisher and Incorta, here are the high-level steps, tools, and procedures:

  • Create an external data source
    You can create an external data source via the Data Manager. The Oracle BI Publisher connector exists under the Application connector category.
    For the list of properties available when creating an Oracle BI Publisher data source, refer to the Oracle BI Publisher connector properties.

  • Create a schema table with the Schema Wizard or the Schema Designer
    You can create a schema table via the Schema Wizard or the Schema Designer.

    For the list of properties available when creating a dataset based on an Oracle BI Publisher data source, refer to the Oracle BI Publisher dataset properties.

    Note

    When creating a schema table via the Schema Designer, you can find the Oracle BI Publisher data sources when you select Oracle BI Publisher for the data source Type.

  • Load the schema
    You can perform a full or incremental load of the schema with the Oracle BI Publisher dataset. You can also load a specific table.

  • Explore the schema
    After loading data, you can use the Analyzer to explore the physical schema, create your first insight, and save it to a new dashboard tab or an existing dashboard tab.

Oracle BI Publisher connector properties

Here are the properties of the Oracle BI Publisher connector:

PropertyControlDescription
Typedropdown listSelect Oracle BI Publisher as the type of the data source you want to use
Data Sourcedropdown listSelect the Oracle BI Publisher data source you want to use
Name Your Data Sourcetext boxEnter a name for the data source
Oracle Cloud Applications URLtext boxEnter the URL of the Oracle Cloud Application from which you want to extract data. The URL should follow this pattern: https://<instance-name>.oraclecloud.com Example: https://mycompany.fa.ocs.oraclecloud.com
Usernametext boxEnter the username of the BICC admin. The BICC admin user must be assigned the BI Administrator role in the Oracle Cloud ERP. For more details, refer to BI Administrator Permissions.
Passwordtext boxEnter the password of the BICC admin
Connection Poolspin boxEnter the maximum number of allowed parallel connections. The default is 15.
Extra Optionstext boxEnter supported extra options in the form of key=value.

Connector extra options

The following are the supported extra options that you can specify for the data source:

KeyDescription
discovery.sample.sizeThe number of records to be used for sampling results during the schema discovery.
The default is 1000.
extraction.timezoneThe timezone used when parsing timestamps.
The default is UTC.

Oracle BI Publisher dataset properties

For a schema table in Incorta based on an Oracle BI Publisher data source, you can specify the following properties:

PropertyControlDescription
Querytext boxEnter the SQL query used during full loads to retrieve data from the Oracle Cloud Applications database
Chunk Sizedropdown listThis option allows you to split the query's result into smaller chunks, overcoming the size limitations imposed by the BI Publisher.
The default chunk size is 150 MB while the maximum is 450 MB.
Select No Chunking to disable chunking.
Note:
Exercise caution when selecting a value, as larger chunks may increase memory usage, potentially impacting system performance.
Enable parallel extraction of ChunkstoggleThis toggle becomes available when you set a Chunk Size value other than No Chunking.
Enabling this option allows the system to extract table chunks in parallel during full loads, significantly improving data extraction speed for large datasets.
Parallel extraction is particularly beneficial for optimizing performance in scenarios where the source system supports concurrent processing.
IncrementaltoggleEnable incremental load configuration for the schema table
Incremental Querytext boxThis property becomes available when Incremental is enabled.
Enter the SQL query to use during incremental loads.
Incremental Field Typedropdown listThis property becomes available when Incremental is enabled.
Use the Incremental Field Type dropdown to specify the data type of the incremental column, choosing either Timestamp or Date based on your dataset format and requirements.
CallbacktoggleTurn this toggle on to enable post-extraction callback
Callback URLtext boxThis property becomes available when the Callback option is enabled, allowing you to specify the callback URL.

Additional considerations

Column discovery and rejected rows

  • The Oracle BI Publisher Connector relies on sampling a number of records to discover columns and infer their data types.
  • As the Schema Wizard requires at least one record to identify columns, it cannot be used to create a table dataset from an empty data source.
  • Using sampling to infer data types may sometimes result in inaccurate type inference, leading to rows being rejected during data extraction due to mismatched data types.
    • To address the rejected rows issue, update the column data types as required and reload the affected tables and objects to ensure data consistency.

Incremental loads

For incremental queries, enter a query similar to the full load extraction query and specify a condition to fetch the updates only, for example, SELECT * FROM ap_invoices_all WHERE LAST_UPDATE_DATE > ?. The question mark (?) is substituted with a timestamp or a date depending on the selected Incremental Field Type in the dataset definition.

Data chunking

BI Publisher supports queries with result sizes up to 500 MB. To further optimize the connector performance and enable the handling of larger query results, Incorta provides a chunking option at the dataset level. The query results are split into smaller, manageable chunks, facilitating parallel extraction during full loads.