Data Applications → Install Oracle EBS for Incorta On-Premises

About the Oracle EBS Data Application for Incorta On-Premises

The Incorta Oracle E-Business Suite (EBS) data application provides a new, faster, more effective option for analyzing and understanding your data, with no data modeling or extract, transform, load (ETL) required. You will get instant access to up-to-date, consolidated enterprise resource planning (ERP) data and can build all of the high-performance reports you need on your own.

With an extensive library of prebuilt content, Oracle EBS users gain immediate access to rich content areas and sample dashboards that are easily customizable and logically grouped by department and persona to help answer the most-pressing business questions quickly.

In addition, the Oracle EBS data applications expedite your organization’s migration from legacy reporting tools, and drastically speed your Oracle EBS-based reporting and analytics project’s time to value. They also let you more quickly add new data sources to your Oracle EBS analysis.

The following are the Oracle EBS modules for which data applications are available in the on-premises version of the Incorta Direct Data Platform™:

Financial Modules

Supply Chain Planning Modules

Business Flows

  • Order to Cash
  • Procure to Pay

Other Modules

  • Discrete Manufacturing
  • EAM (Enterprise Asset Management)
  • EBS HR (Human Resources)
  • Projects
Note

The Supply Chain Planning Modules apply only to customers using Oracle Advanced Supply Chain Planning (ASCP) or Value Chain Planning (VCP).

Each data application module represents a 1:1 mapping of the corresponding tables and relationships in Oracle EBS. Also included is prebuilt metadata that can be used to load data for each module from the Oracle database and view it in prebuilt dashboards.

Note

The Incorta on-premises EBS data application supports Oracle EBS version R12.x.

Install the Oracle EBS Data Application

The Oracle EBS data application requires the following ZIP file:

  • FullInstallEBSVer<VERSION_NUMBER>.zip

The Oracle EBS data application requires the import of a tenant file to an Incorta Cluster. A CMC Administrator can perform the tenant import. A systems administrator with root access to the host can copy the tenant data files, configure Spark, and configure the necessary datasources.

Here are the steps to install the Oracle EBS data application:

  • Contact Incorta Support to obtain the Oracle EBS data application ZIP file. Download and unzip the file.
  • Within the unzipped Oracle EBS data application file, locate the ebsVer<VERSION_NUMBER>.zip file.

Import the EBS Data Application Tenant

  • Import the ebsVer<VERSION_NUMBER>.zip tenant:
    • Sign in to the CMC.
    • In the Navigation bar, select Clusters.
    • In the cluster list, select a Cluster name.
    • In the canvas tabs, select Tenants.
    • Select +Import Tenant.
    • Drag and drop the ebsVer_<VERSION_NUMBER>.zip to the Click or drag a file here to upload panel in the Import a tenant to the cluster dialog.
    • Select Next.
    • Verify or enter the tenant properties.
    • Verify or enter the tenant email properties.
    • Select Create.

Tenant Properties

PropertyControlDescription
Nametext boxEnter the tenant name. Select Check to determine if a Tenant already exists with the name entered.
Usernametext boxEnter the username for the Super User
Passwordtext boxEnter the password for the Super User
Emailtext boxEnter the email address for the Super User
Pathtext boxEnter the shared storage path for tenant related data
Pause scheduled jobstoggleEnable this property if the imported tenant will have all scheduled jobs paused on import

Tenant Email Properties

PropertyControlDescription
Sender’s Username AuthtoggleEnable this property if the email requires username authentication
System Email Usernametext boxEnable Sender’s Username Auth to configure this property. Enter the username for the system email.
System Email Addresstext boxEnter the email address for the system email
System Email Passwordtext boxEnter the password for the system email address
SMTP Hosttext boxEnter the Simple Mail Transfer Protocol (SMTP) host for the system email
SMTP Porttext boxEnter the SMTP port number
Share NotificationstoggleEnable this property to share notifications

Copy the EBS Data Application Tenant Data Files

The EBS data application package contains the following CSV data files that you must copy to the Tenant data folder:

  • contentTypeLookup.csv
  • Date_US.csv
  • Month_US.csv
  • Period.csv
  • Quarter.csv

Here are the steps to perform a secure copy of the CSV files using scp:

INCORTA_NODE_HOST=100.101.102.103
cd ~/Downloads/FullInstallEBSVer5/Data
scp -i ~/.ssh/host_pemkey.pem contentTypeLookup.csv Date_US.csv Month_US.csv Period.csv Quarter.csv incorta@${INCORTA_NODE_HOST}:/tmp/

Secure shell into the host.

ssh -i ~/.ssh/host_pemkey.pem incorta@${INCORTA_NODE_HOST}

Copy the CSV data files to the /<INCORTA_INSTALLATION_PATH>/Tenants/<TENANT_NAME>/data directory in bash shell.

sudo su incorta
INCORTA_INSTALLATION_PATH=/home/incorta/IncortaAnalytics
cp /tmp/contentTypeLookup.csv /tmp/Date_US.csv /tmp/Month_US.csv /tmp/Period.csv /tmp/Quarter.csv $INCORTA_INSTALLATION_PATH/Tenants/bp_oracle_ebs/data

Configure Spark for the EBS Data Application

Allocate 10 GB or more to the Spark Worker process, and restart Spark:

  • Edit the <INCORTA__INSTALLATION_PATH>/IncortaNode/spark/conf/spark-env.sh file with vim.
vim /home/incorta/IncortaAnalytics/IncortaNode/spark/conf/spark-env.sh
  • Set the SPARK_WORKER_MEMORY value to 10g (or greater)

    SPARK_WORKER_MEMORY = 10g
  • Restart Spark

    cd /home/incorta/IncortaAnalytics/IncortaNode
    ./stopSpark.sh
    ./startSpark.sh
  • Check the Spark configuration using the Spark Master UI: http://<HOST_NAME>:9091.

Configure the EBS Data Application Datasources

  • Sign in to the Incorta Direct Data Platform™.
  • In the Navigation bar, select Data.
  • In the Context bar, select the External Data Source tab.

Edit the EBS Data Source

  • Select Edit (pen icon) to the right of the EBS datasource.
  • Enter the EBS connection properties for your instance:
    • Username
    • Password
    • Connection Pool: 10
    • Connection String: jdbc:oracle:thin:@<HOST>:<PORT>/<DATABASE>

Edit the VCP Data Source (for ASCP customers only)

  • Select Edit (pen icon) to the right of the VCP datasource.
  • Enter the VCP connection properties for your instance:
    • Username
    • Password
    • Connection Pool: 10
    • Connection String: jdbc:oracle:thin:@<HOST>:<PORT>/<DATABASE>

Edit the Incorta (Metadata) Data Source

  • Select Edit (pen icon) to the right of the Incorta datasource.
  • Enter the EBS connection properties for your instance:
    • Username
    • Password
    • Connection Pool: 10
    • Connection String: jdbc:mysql://<HOST>:<PORT>/<DATABASE>

Edit the SparkSQL Data Source

  • Select Edit (pen icon) to the right of the SparkSql datasource.

  • Enter the SparkSQL connection properties for your instance:

    • Username
    • Password
    • Connection Pool: 10
    • Connection String: jdbc:postgresql://<HOST>:5442/<TENANT_NAME>
    Note

    The port is the Data Store (DS) port defined in the CMC.

Configure the Oracle EBS Data Application

To configure the Oracle EBS data application, update the corresponding Session Variables and perform a full load of the EBS COMMON Schemas.

Update EBS Data Application Session Variables

  • Sign in to the Incorta Direct Data Platform™.
  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Session Variables tab.
  • Edit the query for the following variables with information for your EBS instance, by selecting Edit (pen icon) to the right of each variable:
    • Default_Book_Type_code
    • Default_Fiscal_Year
    • Default_Ledger
    • Default_Period
    • Default_Period_2
    • Default_Start_Date

Perform a Full Load of the EBS COMMON Schemas

  • Sign in to the Incorta Direct Data Platform™.
  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • Select each of the schemas below. In the Action bar, select LoadFull Load.
    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON

To configure a specific EBS module, refer to the list of Financial Modules or Supply Chain Planning Modules and select the module for configuration details.

Important

Monitor your Incorta host disk space as you load the EBS schemas. For large tables, to limit full data loads, add a SQL clause WHERE CREATION_DATE > <DATE>.

EBS Data Application Security Groups

The EBS data application tenant includes the following security groups:

FeatureSupported
ReportAdminsAnalyze User, Schema Manager, User, User Manager
ReportUsersIndividual Analyzer, User

Additional Information

Flexfields

EBS has two types of flexfields, descriptive flexfields (DFF) and key flexfields (KFF). To include flexfields, you will need to either create custom tables or customize the SQL of existing tables.

For example, the out of the box EBS_FIN_COMMON schema for Chart of Accounts consists of three segment tables: GL_ACCOUNT_SEG, GL_COST_CENTER_SEG, and GL_BALANCING_SEG. To include KFF, open these tables in the Schema Designer and customize the SQL to support your implementation. You can also create additional segment tables as required.

Table Placeholders

Some tables have 1=0 in the SQL because they are not used in the data applications, and as a result, data is not loaded into the table. If these tables are needed for your implementation, you can modify the SQL to load the table.

FND Lookups

You can support FND_LOOKUPS by using a lookup() function in a formula field so that you do not need to join to the table. For example:

lookup(EBS_FND_COMMON.FND_LOOKUP_VALUES.MEANING, EBS_FND_COMMON.FND_LOOKUP_VALUES.LOOKUP_TYPE,'TRANSFER STATUS', EBS_FND_COMMON.FND_LOOKUP_VALUES.LOOKUP_CODE,EBS_PA.PA_COST_DISTRIBUTION_LINES_ALL.TRANSFER_STATUS_CODE , EBS_FND_COMMON.FND_LOOKUP_VALUES.LANGUAGE,'US')

EBS Hierarchies

For EBS hierarchies like Account or Cost Center, use EBS_GL.GL_SEG_VAL_NORM_HIERARCHY. For segments without multiple parents, use one of the following two approaches:

  • Incorta self join hierarchy
  • Flattened data

For segments that have multiple parents, further analyze the top nodes for the given segment. You can create a limited number of top nodes as separate attributes or hierarchies within Incorta.

Calendars

The Gregorian calendar is supported with a join to the EBS_CAL_COMMON.GREGORIAN_CALENDAR table. This table is loaded from the Date_US.csv file.

The fiscal calendar is supported with a join to the EBS_CAL_COMMON.GL_DATE table.

Multi-Currency

Out of the box, amounts in Entered and Accounted currency are supported. To support other global currencies, such as USD, for the multi-ledger use case, use the EBS_FIN_COMMON.GL_DAILY_RATES table and customize it as required. Perform a lookup to this table to get the exchange rate and multiply it with the base amount. For an example, refer to one of the following fields in the CollectionsManager.Payments schema:

  • Amount Due Original (AR Global)
  • Amount Due Original (Revenue Global)
  • Amount Due Remaining (AR Global)
  • Amount Due Remaining (Revenue Global)

Multiple Child Tables with a Common Parent

The Accounts Payable module has an example use case of multiple child tables with a common parent. AP_INVOICE_PAYMENTS_ALL is a child of AP_INVOICES_ALL. There is no direct path from AP_INVOICE_DISTRIBUTIONS_ALL to AP_INVOICE_PAYMENTS_ALL. As a result, it is not possible to report Payment Amount with Invoice Number and PO ID in one Incorta report. To resolve this issue, create a Materialized View that has the INVOICE_ID (key) and the aggregated payment amounts from the payments table, and make it the parent table of AP_INVOICES_ALL.