Data Applications → Install and Configure Oracle Cloud ERP for Incorta On-Premises
About the Oracle Cloud ERP Data Application for Incorta On-Premises
The Incorta Oracle Cloud Enterprise Resource Planning (ERP) 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 ERP data and can build all of the high-performance reports you need on your own.
With an extensive library of prebuilt content, Oracle Cloud ERP 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 Cloud ERP data applications expedite your organization’s migration from legacy reporting tools, and drastically speed your Oracle Cloud ERP-based reporting and analytics project’s time to value. They also let you more quickly add new data sources to your Oracle Cloud ERP analysis.
Following are the Oracle Cloud ERP modules for which data applications are available in Incorta On-Premises:
Financial Modules
- Receivables
- Fixed Assets
- General Ledger
- Payables
- Purchasing
Supply Chain Planning Modules
- Inventory
- Order Management
Business Flows
- Order to Cash
- Procure to Pay
Other Modules
- Human Resources
- Projects
Each data application module represents a 1:1 mapping of the corresponding tables and relationships in Oracle Cloud ERP. Also included is prebuilt metadata that can be used to load data for each module from the Oracle database and view this data in prebuilt dashboards.
Install the Oracle Cloud ERP Data Application for Incorta On-Premises
The Oracle Cloud ERP data application requires you to download and unzip the following file:
OracleFusion_<VERSION_NUMBER>.zip
You can request this file from Incorta Support.
Import the Oracle Cloud ERP Data Application Tenant
The Oracle Cloud ERP data application requires the import of a tenant file to an Incorta Cluster. A CMC Administrator can perform the tenant import. The tenant import uses the following file, available in OracleFusion_<VERSION_NUMBER>.zip
:
TenantOracleCloudERP_<VERSION_NUMBER>.zip
Here are the steps to import the 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
TenantOracleCloudERP_<VERSION_NUMBER>.zip
file 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
Property | Control | Description |
---|---|---|
Name | text box | Enter the tenant name. Select Check to determine if a Tenant already exists with the name entered. |
Username | text box | Enter the username for the Super User |
Password | text box | Enter the password for the Super User |
text box | Enter the email address for the Super User | |
Path | text box | Enter the shared storage path for tenant related data |
Pause scheduled jobs | toggle | Enable this property if the imported tenant will have all scheduled jobs paused on import |
Tenant Email Properties
Property | Control | Description |
---|---|---|
Sender’s Username Auth | toggle | Enable this property if the email requires username authentication |
System Email Username | text box | Enable Sender’s Username Auth to configure this property. Enter the username for the system email. |
System Email Address | text box | Enter the system email address |
System Email Password | text box | Enter the password for the system email address |
SMTP Host | text box | Enter the Simple Mail Transfer Protocol (SMTP) host for the system email |
SMTP Port | text box | Enter the SMTP port number |
Share Notifications | toggle | Enable this property to share notifications |
Copy the Oracle Cloud ERP Data Application Tenant Data Files
The mydata
folder in OracleFusion_<VERSION_NUMBER>.zip
contains the following CSV data files that you must upload to Incorta:
contentTypeLookup.csv
currencyTypes.csv
Date_US.csv
Month_US.csv
Period.csv
Quarter.csv
Here are the steps to upload the data files:
- In the Navigation bar, select Data.
- In the Action bar, select + New → Add Data Source.
- In the Choose a Data Source dialog, in Data Files, select Upload Data File.
- In the Upload Data File dialog, in Upload Options, select Overwrite existing file.
- Drag and drop the CSV files to the Upload Data File dialog.
The mydata
folder also contains a datafiles
folder that you must upload to Incorta using the following steps:
- In the Navigation bar, select Data.
- In the Action bar, select + New → Add Data Source.
- In the Choose a Data Source dialog, in Data Files, select Upload Data Folder.
- In the Upload Data Folder dialog, in Upload Options, select Overwrite existing file.
- Drag and drop
datafiles
to the Upload Data Folder dialog.
Update CMC Configurations for the Oracle Cloud ERP Data Application
Update On Heap Memory
The On Heap Memory for the Analytics and Loader Services should be set to at least 6GB. Here are the steps to update the On Heap Memory:
- Sign in to the CMC.
- In the Navigation bar, select Clusters.
- In the cluster list, select a Cluster name.
- In the canvas tabs, select Nodes.
- Select Edit (pen icon) for each service (Analytics Service and Loader Service).
- Enter 6 for On Heap Memory (GB).
- Select Update.
- Select Restart for each service.
Review Spark Configurations
- Sign in to the CMC.
- In the Navigation bar, select Clusters.
- In the cluster list, select a Cluster name.
- In the canvas tabs, select Cluster Configurations.
- In the panel tabs, select Server Configurations.
- In the left pane, select Spark Integration.
Configure the Oracle Cloud ERP Data Application
Configuration Prerequisites
Before you configure the Oracle Cloud ERP data application, your Oracle Cloud ERP administrator will need to create a single user with the correct roles to access the Oracle Cloud ERP Business Intelligence Cloud Connector (BICC) and Universal Content Management (UCM). Select the following links for instructions:
The Oracle Cloud ERP BICC URL must be accessible by Incorta.
Configure the Oracle BICC and UCM
Import the Incorta Offerings
- Sign in to the Oracle BICC with the following URL and your host name:
https://<YOUR_HOST_NAME>.oracledemos.com/biacm/
- Select the Manage Offerings and Data Stores option from the toolbar, and then select Import Modifications. In the Import Modifications dialog, select Choose File.
- Select the
exportCustomization_<DATE>_<VERSION>.zip
file fromOracleFusion_<VERSION_NUMBER>.zip
, and select Open. - Select Import.
- Select the
- Select the Offering drop down list to view the Incorta offerings, such as Incorta AP.
Schedule the Offering Extracts
You will need to schedule an extract for each offering that corresponds to the Oracle Module of interest. Refer to the Offerings Matrix for details. For example, if you are interested in Advanced Payables (AP), you will need to schedule extracts for Incorta AP, Incorta PO, Incorta Common, and Incorta Finance Common.
If you have run extracts in the past, you may need to reset the last extract date. Refer to Creating a Business Intelligence Cloud Extract for more details.
- Select the Manage Extract Schedules option from the toolbar to run the Incorta offerings extracts of interest.
- In Schedules, select + to add a schedule.
- In Schedule Details,
- For Job Type, select Application Data Extract.
- Enter a Name for the schedule.
- Select Next.
- In Data Store List,
- From the Offering drop down list, select an Incorta offering.
- Select the Enabled for Extract checkbox in the view header to select all of the objects.
- Select Next.
- In External Storage,
- For External Storage, select UCM.
- For Notification, select None.
- Select Save.
Verify the Extract is in the UCM
When the extract is complete, sign in to the UCM with the following URL:
https://<YOUR_HOST_NAME>/cs/idcplg?IdcService=GET_DOC_PAGE&Action=GetTemplatePage&Page=HOME_PAGESelect Search, and then select Advanced…
In Search: Expanded Form,
- In Security Group, enter OBIA%.
- Select Search to see the list of .zip extract files..
Offerings Matrix
Number | Module | BICC Offerings | Incorta Schemas and Load Order |
---|---|---|---|
1 | Advanced Payables (AP) | Incorta Common Incorta Finance Common Incorta AP Incorta PO | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_AP Fusion_PO |
2 | Accounts Receivables (AR) | Incorta Common Incorta Finance Common Incorta AR | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_AR Fusion_AR_SNP Fusion_AR_MV |
3 | Fixed Assets (FA) | Incorta Common Incorta Finance Common Incorta FA | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_FA |
4 | General Ledger and Sub ledgers (GL & XLA) | Incorta Common Incorta Finance Common Incorta GL | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_GL Fusion_XLA Fusion_XLA_MV |
5 | Inventory (INV) | Incorta Common Incorta INV | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_INV |
6 | Order Management(ONT) | Incorta Common Incorta ONT | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_ONT |
7 | Procurement (PO) | Incorta Common Incorta PO | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_PO |
8 | Projects (PRJ) | Incorta Common Incorta Projects | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_PRJ |
9 | GL Budgetary Control | Incorta Budgetary Control | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_FND_Common Fusion_FIN_Common Fusion_PARTY_COMMON Fusion_Budgetary_Control |
10 | HCM (Core) | Incorta Common Incorta HR | Fusion_CAL_COMMON Fusion_HCM |
11 | HCM (Recruiting) | Incorta Common Incorta HCM Recruiting | Fusion_CAL_COMMON Fusion_HCM Fusion_HCM_REC |
12 | HCM (Payroll) | Incorta Common Incorta HR Incorta HCM Payroll | Fusion_CAL_COMMON Fusion_HCM Fusion_HCM_Payroll |
13 | HCM (Learning) | Incorta Common Incorta HR Incorta HCM Learning | Fusion_CAL_COMMON Fusion_HCM Fusion_HCM_Learning |
14 | Procurement(Sourcing) | Incorta Common Incorta Sourcing | Fusion_CAL_COMMON Fusion_Item_Common Fusion_HR_Common Fusion_PARTY_COMMON Fusion_Sourcing |
Apply Required Customizations to the Oracle Cloud ERP Data Application
To connect the data application to your Oracle Cloud ERP instance, the following steps are required:
- Setup the Incorta Cloud Applications UCM connection
- Perform a full load of the physical schemas
- Configure the seeded session variables
- Configure the General Ledger (GL) Account Key Flexfields (KFFs)
You can apply optional Oracle Cloud ERP customizations to the data application. These steps are covered in detail below.
Setup the Incorta Cloud Applications UCM connection
- Sign in to the Incorta Cloud Admin Portal (CAP).
- In the Navigation bar, select Clusters.
- Select the Cluster you installed the Oracle Cloud ERP data application on.
- Select Incorta Analytics.
- In the Navigation bar, select Data.
- In the Data Manager, in the Context bar, select the External Data Source tab.
- In the List View of external data sources, for the Fusion data source, select Edit (pen icon).
- In the New Data Source dialog,
- Enter your Oracle Cloud Applications Username.
- Enter your Oracle Cloud Applications Password.
- Enter your Oracle Cloud Applications URL for UCM storage:
https://<YOUR\_HOST\_NAME>/idcws/GenericSoapPort
- For Root Query Text, the default is
dSecurityGroup <matches> OBIAImport
. You can add the following to further filter the data as required:<AND> dDocTitle <matches> file_crmanalyticsam%
- For Data Type Discovery Policy, leave the default value of Use Both Sample data and Metadata Definition Files selected.
- For Metadata directory, leave the default value of
/datafiles
. - For File Name Pattern, leave the default value of
file_<catalog>_<schema>_<table>-batch*.zip
. - For File Criteria - Last Modified Timestamp, you can enter a timestamp to load only files created or modified after that date. Here is the default value:
>= '2021-01-01 00:00'
- Select Ok.
If you would like to get your data from object storage instead of UCM storage, refer to Oracle Cloud Infrastructure (OCI) Object Storage.
Perform a Full Load of the Physical Schemas
Perform a full load of the data for each physical schema that corresponds to the Oracle Module of interest. Refer to the Offerings Matrix for details. For example, if you are interested in Advanced Payables (AP), you will need to perform a full load of Fusion_CAL_COMMON, Fusion_ITEM_COMMON, Fusion_HR_COMMON , Fusion_FND_COMMON, Fusion_FIN_COMMON, Fusion_PARTY_COMMON, Fusion_AP, and Fusion_PO in the order listed. Here are the steps to perform a full load.
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the Schema Designer, open each required physical schema. In the Action bar, select Load → Load now → Full.
Monitor your Incorta cluster disk space as you load the Fusion schemas. To limit full data loads for large tables, add a SQL clause to the table query: WHERE CREATION_DATE > <DATE>
.
Configure the Seeded Session Variables
Configure the following seeded session variables that are used to set default values for dashboard prompts:
Variable Name | Description | Note |
---|---|---|
Default_Ledger | This variable is used to provide a default ledger value in the financial dashboards. | You can view the Ledger Name values in Fusion_FIN_COMMON.Ledger by creating an insight using the table as a data set or previewing the data in the schema table. |
Default_Period | This variable is used to provide a default fiscal period in the financial dashboards. | You can view the Period Name values in Fusion_FIN_COMMON.GLFiscalPeriod by creating an insight using the table as a data set or previewing the data in the schema table. |
Here are the steps to configure the variables:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Session Variables tab.
- Select Edit (pen icon) for the session variable you would like to configure.
- In the Internal Variable dialog,
- Select Query to open the Formula Builder.
- Update the query as necessary.
- Select Done.
- Select Save.
Configure the General Ledger Account Key Flexfields
By default, the Oracle Cloud ERP data applications use three segments in the chart of accounts. If you use only three segments, follow the minimal configuration steps in this section. If you have additional segments, follow the steps under optional configurations to add additional segments.
Determine the Chart of Accounts to Use for Reporting
You can query Fusion_FIN_COMMON.Ledger.LEDGERCHARTOFACCOUNTSID by creating an insight to determine which chart of account to use.
Get the List of Segments by Chart of Accounts ID
- Sign in to Oracle Cloud Applications.
- Select My Enterprise → Setup and Maintenance.
- In Functional Areas, select Financial Reporting Structures.
- In Task, select Manage Chart of Accounts Structure Instances.
- In Manage Chart of Accounts Structure Instances,
- In Key Flexfield Code, enter GL.
- Select Search.
- For the General Ledger Accounting Flexfield, select Actions → Manage Structure Instances.
- In Manage Key Flexfield Structure Instances, select Operations Accounting Flex for Structure Name.
- Select Search.
- For Operations Accounting Flex, select Actions → Edit.
- In Edit Key Flexfield Structure Instance: Operations_Accounting_Flex, you can view the mapping of account segments.
Update the Business View Labels for the Segments
Here are the steps to update the segments in the CodeCombination business view of the FusionGeneralLedger business schema:
- In the Navigation bar, select Business Schema.
- In the Business Schema Manager, in the list view of business schemas, select the FusionGeneralLedger business schema to open it.
- In the Business Schema Designer, select the CodeCombination business view to expand it.
- Verify the segment labels correspond to your requirements. Update the labels as necessary:
- Select More Options (vertical ellipsis icon) → Edit.
- Enter the new values in Label for SEGMENT1, SEGMENT2, or SEGMENT3. For example, the Label for SEGMENT1 may be Company, the Label for SEGMENT2 may be Department, and the Label for SEGMENT3 may be Account, depending on your implementation.
- In the Action bar, select Done.
Update the Join for the GL Account Segment
- Open the Fusion_XLA.XLA Accounts table, which contains the accounts for each module used in the GL reconciliation dashboards, such as Accounts Payable.
- Select the correct segment field for the Account.
- Select Done.
- Perform a full load of the Fusion_XLA.XLA Accounts table.
- Update the join to the Fusion_FIN_Common.CodeCombination table for the GL Account Segment in your implementation. For example, if segment 3 is the Account segment, update the join between Fusion_XLA.XLA_Accounts.ACCOUNT and Fusion_FIN_COMMON.CodeCombination to CODECOMBINATIONSEGMENT3.
- Perform a full load of Fusion_FIN_COMMON.CodeCombination.
Update the Chart of Accounts in the Materialized View
Here are the questions you need to answer to determine the parameters used in the Incorta Fusion_FIN_COMMON.GLAccountHierFlat materialized view:
- What is the name of the account tree that you want to use?
- What is the name of the account tree version that you want to use?
- What is the Oracle Foundation (FND) value set category name for the GL account hierarchy nodes?
You will use the answers above to update the materialized view. Refer to Set up the General Ledger Key Flexfields for Oracle Cloud ERP to navigate your environment for values specific to your implementation.
If you have other GL segment hierarchies you want to optionally include, for example, cost center hierarchy, here are the steps:
- Create similar materialized views using the same SQL, but replace the parameters with values specific to those hierarchies.
- Create joins to the CodeCombination PVO in this new materialized view, similar to the GL account segment hierarchy.
- Create one alias per hierarchy if you have multiple hierarchies for the same segment, using the hierarchy name as a filter in the join. The hierarchy name cannot be dynamically picked from the dashboard, as Incorta joins are computed at load time.
To update the chart of accounts materialized view, Fusion_FIN_COMMON.GLAccountHierFlat:
- Change the specific values of the following for your implementation:
- treeversionname
- segment
- valuecategory
- Perform a full load.
For example, with segment 3 as the Account segment, make the following updates as indicated below:
SELECTgsh.dep31pk1value || ' - ' || lvl1.DESCRIPTION AS account_level1,gsh.dep30pk1value || ' - ' || lvl2.DESCRIPTION AS account_level2,gsh.dep29pk1value || ' - ' || lvl3.DESCRIPTION AS account_level3,gsh.dep28pk1value || ' - ' || lvl4.DESCRIPTION AS account_level4,gsh.dep27pk1value || ' - ' || lvl5.DESCRIPTION AS account_level5,gsh.dep26pk1value || ' - ' || lvl6.DESCRIPTION AS account_level6,gsh.dep25pk1value || ' - ' || lvl7.DESCRIPTION AS account_level7,gsh.dep24pk1value || ' - ' || lvl8.DESCRIPTION AS account_level8,gsh.dep0pk1value || ' - ' || lvl9.DESCRIPTION AS account_level9,gcc.CODECOMBINATIONSEGMENT1,gcc.CODECOMBINATIONSEGMENT2,gcc.CODECOMBINATIONSEGMENT3,gcc.CODECOMBINATIONSEGMENT4,gcc.CODECOMBINATIONSEGMENT5,gcc.CODECOMBINATIONSEGMENT6,gcc.codecombinationid,lvl2.FLEXVALUE lvl2_flexvalue,lvl3.FLEXVALUE lvl3_flexvalue,lvl4.FLEXVALUE lvl4_flexvalueFROMFusion_FIN_COMMON.flex_tree_vs_gl_account_vi gsh,(SELECTgcf.treestructurecode,gcf.treecode,gcf.treeversionid,gcf.dep0pk1value,MAX(gcf.distance) AS maxdistanceFROMFusion_FIN_COMMON.flex_tree_vs_gl_account_vi gcf,Fusion_FIN_COMMON.fndtreeversiontlp1 tvtWHEREgcf.treestructurecode = tvt.treestructurecodeAND gcf.treecode = tvt.treecodeAND gcf.treeversionid = tvt.treeversionidAND tvt.treeversionname = 'Account_Tree_Current1' /* <--- Update */GROUP BYgcf.treestructurecode,gcf.treecode,gcf.treeversionid,gcf.dep0pk1value) hier,Fusion_FIN_COMMON.fndflexvaluespvo lvl1,Fusion_FIN_COMMON.fndflexvaluespvo lvl2,Fusion_FIN_COMMON.fndflexvaluespvo lvl3,Fusion_FIN_COMMON.fndflexvaluespvo lvl4,Fusion_FIN_COMMON.fndflexvaluespvo lvl5,Fusion_FIN_COMMON.fndflexvaluespvo lvl6,Fusion_FIN_COMMON.fndflexvaluespvo lvl7,Fusion_FIN_COMMON.fndflexvaluespvo lvl8,Fusion_FIN_COMMON.fndflexvaluespvo lvl9,Fusion_FIN_COMMON.CodeCombination gccWHEREgsh.dep0pk1value = hier.dep0pk1valueAND gsh.distance = hier.maxdistanceAND gsh.treestructurecode = hier.treestructurecodeAND gsh.treecode = hier.treecodeAND gsh.treeversionid = hier.treeversionidAND gsh.dep31pk1value = lvl1.FLEXVALUEAND gsh.dep30pk1value = lvl2.FLEXVALUEAND gsh.dep29pk1value = lvl3.FLEXVALUEAND gsh.dep28pk1value = lvl4.FLEXVALUEAND gsh.dep27pk1value = lvl5.FLEXVALUEAND gsh.dep26pk1value = lvl6.FLEXVALUEAND gsh.dep25pk1value = lvl7.FLEXVALUEAND gsh.dep24pk1value = lvl8.FLEXVALUEAND gsh.dep0pk1value = lvl9.FLEXVALUEAND gsh.dep0pk1value = gcc.CODECOMBINATIONSEGMENT3 /* <--- Update */AND lvl1.valuecategory = 'Account' /* <--- Update */AND lvl2.valuecategory = 'Account' /* <--- Update */AND lvl3.valuecategory = 'Account' /* <--- Update */AND lvl4.valuecategory = 'Account' /* <--- Update */AND lvl5.valuecategory = 'Account' /* <--- Update */AND lvl6.valuecategory = 'Account' /* <--- Update */AND lvl7.valuecategory = 'Account' /* <--- Update */AND lvl8.valuecategory = 'Account' /* <--- Update */AND lvl9.valuecategory = 'Account' /* <--- Update */
Update the Incorta Analyzer Table with the GL Account Segment
The Oracle Cloud ERP data application uses Segment 2 for the GL Account Segment. If you use a different segment column, update the Fusion_XLA.XLA_Accounts Incorta Analyzer table with the correct segment number.
- In the Schema Manager, open the XLA_Accounts table.
- In the Schema Designer, select the Incorta icon.
- In the Data Source dialog, select Edit (pencil icon).
- For ACCOUNT, select the right arrow (>) to open the pill properties.
- In Formula, update the segment number. For example, with segment 3 as the Account segment, change
Fusion_FIN_COMMON.CodeCombination.CODECOMBINATIONSEGMENT2
toFusion_FIN_COMMON.CodeCombination.CODECOMBINATIONSEGMENT3
.
Apply Optional Customizations to the Oracle Cloud ERP Data Applications
Following are common Oracle Cloud ERP customizations that you can optionally apply to the data application:
- Configure Descriptive Flexfields (DFFs)
- Configure additional segments for the General Ledger (GL) account Key Flexfields (KFFs)
- Drill to Oracle Cloud ERP from an Incorta insight
Configure Descriptive Flexfields
A flexfield is a set of placeholder fields, or segments that are associated with a business object. Oracle Cloud ERP provides three types of flexfields: descriptive, extensible, and key. Here are the steps to support a DFF for a table in Incorta:
- Determine the flex VO. In Oracle Cloud ERP, there is a separate VO object for a table’s flexfields. For example, for ContractHeaderP, the flex VO is
FscmTopModelAM.ContractsCoreAM.HdrDFBIAM.FLEX_BI_ContractHeaderFlexfield_V
. - Import the flex VO into Incorta. Refer to Add New VOs to Incorta for the detailed steps.
- Join the flex VO as a parent table to the base VO. Use the key columns of the base VO to join to the flex VO key columns, which will have a column name similar to S_K_5000.
- Add the DESC_ field from the flex VO to a business schema with the appropriate label. For example, in contractHeader the end user DFF column name is ProjectMarket. Therefore, the flexfield value field in the VO would be DESC_PROJECTMARKET_, and the label would be Project Market.
- Load the VO.
Here are the DFF VOs included in the Fusion_FLX_COMMON schema:
Descriptive Flexfield Name | Flex VO Name | Base VO | Application Table Name |
---|---|---|---|
CE_EXTERNAL_TRANSACTIONS | FscmTopModelAM.ExternalTransactionBIAM.FLEX_BI_ExternalTransaction_VI | FscmTopModelAM.FinCeCashTransactionsAM.ExternalTransactionsPVO | CE_EXTERNAL_TRANSACTIONS |
ContractHeaderFlexfield | FscmTopModelAM.ContractsCoreAM.HdrDFBIAM.FLEX_BI_ContractHeaderFlexfield_VI | FscmTopModelAM.ContractsCoreAM.ContractHeaderP | |
OKC_K_HEADERS_ALL_B | ContractLineFlexfield | fscmtopmodelam.contractscoream.lndfbiam_flex_bi_contractlinedescflexfield_VI | FscmTopModelAM.ContractsCoreAM.ContractHeaderLinesP |
OKC_K_LINES_ALL_B | EGP_SYSTEM_ITEMS_DF | FscmTopModelAM.EgpItemsPublicModelAM.ItemBaseDFFBIAM.FLEX_BI_ItemBaseDFF_VI | FscmTopModelAM.EgpItemsPublicModelAM.Item |
INV_MATERIAL_TXNS | FscmTopModelAM.InxTxnDFFBIAM.FLEX_BI_InxTxnDFF_VI | FscmTopModelAM.InventoryAM.InventoryTransactionDetailPVO | INV_MATERIAL_TXNS |
PJC_EXP_ITEMS_DESC_FLEX | FscmTopModelAM.PjcEiBIAM.FLEX_BI_PjcEi_VI | FscmTopModelAM.PjctransactionstAM.expenditureitemPVO | PJC_EXP_ITEMS_ALL |
PJC_TXN_XFACE_DESC_FLEX | FscmTopModelAM.PjcEiBIAM.FLEX_BI_PjcEi_VI | FscmTopModelAM.PjcTransactionsAM.ProjectUnprocessedCostTransactionPVO | PJC_TXN_XFACE_ALL |
PJF_PROJECTS_DESC_FLEX | FscmTopModelAM.PJF_PROJECTS_ALLBIAM.FLEX_BI_PJF_PROJECTS_ALL_VI | FscmTopModelAM.PjfProjectAM.Project | PJF_PROJECTS_ALL_B |
PJF_TASK_STRUCTURE_DFF | FscmTopModelAM.TaskDffBIAM.FLEX_BI_TaskDff_VI | FscmTopModelAM.PjfProjectDefinitionAM.TaskVLPVO | PJF_PROJ_ELEMENTS_B |
POR_REQUISITION_HEADERS | FscmTopModelAM.RequisitionHeaderPublicBIAM.FLEX_BI_RequisitionHeaderPublic_VI | FscmTopModelAM.PrcPorPublicViewAM.RequisitionHeaderP1 | POR_REQUISITION_HEADERS_ALL |
POR_REQUISITION_LINES | FscmTopModelAM.RequisitionLinePublicBIAM.FLEX_BI_RequisitionLinePublic_VI | FscmTopModelAM.PrcPorPublicViewAM.RequisitionLineP1 | POR_REQUISITION_LINES_ALL |
POZ_SUPPLIER_SITES_ALL_M | FscmTopModelAM.PozSupplierSitesDFFPublicBIAM.FLEX_BI_PozSupplierSitesDFFPublic_VI | FscmTopModelAM.PrcPozPublicViewAM.SupplierSitePVO | POZ_SUPPLIER_SITES_ALL |
PO_HEADERS | FscmTopModelAM.POHeaderDFFPublicBIAM.FLEX_BI_POHeaderDFFPublic_VI | FscmTopModelAM.PrcPoPublicViewAM.StandardHeaderPVO | PO_HEADERS_ALL |
PO_LINES | FscmTopModelAM.POLineDFFPublicBIAM.FLEX_BI_POLineDFFPublic_\VI | FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO | PO_LINES_ALL |
RA_CUSTOMER_TRX | FscmTopModelAM.TransactionHeaderFLEXBIAM.FLEX_BI_TransactionHeaderFLEX_VI | FscmTopModelAM.FinArTopPublicModelAM.TransactionHeaderPVO | RA_CUSTOMER_TRX_ALL |
RCV_TRANSACTIONS | FscmTopModelAM.RcvTxnDFBIAM.FLEX_BI_RcvTxnDF_VI | FscmTopModelAM.RcvReceiptsAM.ReceivingReceiptTransactionPVO | RCV_TRANSACTIONS |
Since flex VOs are deployment-specific, you need to perform the following steps to get the updated fields:
- Export the existing flex VO in the BICC.
- Download the metadata CSV (MDCSV) file from the BICC.
- Import the MDCSV file to Incorta.
- Save the flex VO.
If no fields are selected in the flex VO, edit the VO in the BICC:
- Select all fields.
- Select Save.
If you do not see some flex VOs in the BICC, deploy the DFFs from the Fusion Application with the following steps:
- Navigate to the Manage Descriptive FlexFields window.
- Query the DFF CODE.
- Select Edit (pen icon).
- If the BI Enabled check box is cleared, select it.
- Select Save.
- Deploy the flex code.
- Run the Import Oracle Fusion Data Extensions for Transactional Business Intelligence job.
Configure Additional Segments for the General Ledger Account Key Flexfields
The required customizations section above outlined the steps to configure the GL account to reflect your implementation of the GL# KFFs when you use only three segments in your Oracle Cloud ERP instance. This section outlines the steps to configure the GL account to reflect your implementation of the GL# KFFs when you use more than three segments.
Add new business views for additional qualifying segments. For example, create a new business view called product and add all the columns for that segment.
If the segment has no hierarchy, add the <segment name>_c
and DESC_<segment_name>
columns into the business view. If the segment has a hierarchy, the hierarchy is already flattened in the VO. Add all the columns for each level in the hierarchy to the business view.
Drill to Oracle Cloud ERP from an Incorta Insight
You can drill to Oracle Cloud ERP from an Incorta insight if a URL exists to pass the ID in. Here are some examples of URLs you can enter in the column URL field of an insight:
- Purchase Requisitions:
https://<YOUR_HOST_NAME>/fscmUI/faces/FndOverview?fnd=%2FWEB-INF%2Foracle%2Fapps%2Fprc%2Fpor%2FmanageReq%2Fui%2Fflow%2FManageReqMainAreaFlow.xml%23ManageReqMainAreaFlow;paramInvokedApp%3DNOTIFICATION%3BparamReqHeaderId%{EBS_PO.PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID};;;false;256;;;&fndGlobalItemNodeId=itemNode_my_information_purchase_requisitions
- AP Invoices:
https://<YOUR_HOST_NAME>/fscmUI/faces/deeplink?objType=AP_VIEWINVOICE&action=VIEW&objKey=InvoiceId={FusionPayablesTransactions.InvoiceHeaders.INVOICE_ID}
Add New Fields to Existing Tables in Incorta
Here are the steps to add new fields into existing tables in Incorta:
- In the BICC, open a Data Store for a particular Offering.
- Select Select Columns.
- Select the Include checkbox for the field you want to add.
- Select Save.
- In the Data Stores for Offering list view, select the gear icon and then Reset to Full Extract.
- In the UCM, select Search.
- In Title, enter the data store name in lowercase.
- Select Search.
- Delete all files with a date before today.
In the BICC, schedule and run an extract for the new field.
In Incorta, open the schema that contains the table.
- Open the table in the Table Editor.
- Select the checkbox for the new column.
- Select Done.
Perform a full load of the table.
Select Preview Data to validate the data in the new column.
Add New VOs to Incorta
There may be situations in which you want to bring in additional data from Oracle Cloud ERP. The primary method to bring Oracle Cloud ERP data into Incorta is through VOs. The data from VOs can be extracted from the Oracle BICC console. There are a lot of VOs pre-seeded in the BICC console that are already assigned to specific offerings and enabled for extract. Not all VOs are pre-seeded. However, any VO that is in Oracle Cloud ERP can be extracted from BICC as a custom VO extract.
Here are the high level steps to add a new VO:
- Identify the VO that contains the data needed for reporting in Incorta
- Find or add the VO in the BICC
- Download the VO MDCSV from the BICC and upload it to Incorta
- Extract the VO data from the BICC into the UCM
- Create the table corresponding to the new VO in Incorta
Identify the VO that contains the data needed for reporting in Incorta
- In Oracle Transactional Business Intelligence (OTBI), either create or run a report for the subject area of interest with the following URL and your host name:
https://<YOUR_HOST_NAME>/analytics
- Select Administration → Manage Sessions.
- From the Cursor Cache list, select View Log for the report. The log contains a list of VO objects, as well as details on how a VO joins to other VOs.
- Copy the name of the VO of interest from the log.
Find or Add the VO in the BICC
- In the Oracle BICC, search for the VO.
- If the VO is not found, you can add the VO:
- Select Manage Offerings and Data Stores from the toolbar.
- Select an Offering from the list.
- Select +.
- In Data Store Key, enter the name of the VO.
- In Associate Offerings, make sure the name of the offering you would like to add the VO to is in Selected Offerings.
- Select Save.
- Select Select Columns.
- Select the Include checkbox for the columns you would like to add to the VO. You can select all columns with Select → Select All.
Download the VO MDCSV from the BICC and upload it to Incorta
- In the BICC, download the VO MDCSV file:
- Select the gear icon, and then Extract Metadata Definition (MDCSV).
- Unzip the MDCSV file.
- Change the file extension from .MDCSV to .csv.
- In Incorta, upload the file to the datafiles folder. For more information, refer to the Upload one or more data files and folders, including subfolders and files section of the Connectors → File System document.
Extract the VO Data from the BICC into the UCM
- In the BICC, select Manage Extract Schedules to extract the VO data into the UCM.
You can also add the VO to a custom offering and extract the offering.
Create the table corresponding to the new VO in Incorta
- In Incorta, use the Schema Wizard to create a physical schema table for the VO definitions.
- Create the joins from child to parent.
- Create any formula columns needed.
- Perform a full load of the physical schema.
- Create or update a business schema.
- Create new dashboards from the business schema, and validate the data and joins.
Sizing
To determine the data sizing for Oracle Cloud ERP, create a BI Publisher report using the following SQL:
SELECTowner,table_name,num_rows,round(blocks * 8 / 1024) size_mbFROMall_tablesWHEREowner not in ('SYS', 'SYSTEM')ORDER BY1,2
FAQs
Q: Are Incremental extracts supported for Oracle Cloud ERP?
A: Yes
Q: How are VOs kept up to date with Oracle releases?
A: If Oracle adds new columns to the VOs, you can perform a full re-extract of the VO from the BICC and save the table in Incorta.
Q: How do I dynamically load data for only the last 90 days to a table?
A: You can use the value 90d in the date filter field Load Only Files Created Since to load only the files created in the last 90 days before the loading starts.
Q: How do I add a query filter to a VO in the BICC?
A: Edit the VO and in the Query Filter section add a filter like __DATASTORE__.<COLUMN> = <VALUE>
Q: How do I query a VO from OTBI?
A: In OTBI, select Administration → Issue SQL, and run a query. Following are example queries:
SELECT_PHYSICAL count(*)FROM"HcmTopModelAnalyticsGlobalAM.PersonAM.GlobalPersonPVO"
SELECT count(*)FROMEXTERNAL('ADF','"oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."Connection Pool"').'FscmTopModelAM.ReceiptAccountingAM.ReceiptAccountingTxnP1'
Troubleshoot
Issue | Resolution |
---|---|
Not able to connect to the Oracle Cloud ERP data source from Incorta due to a system error | ● Verify that the Incorta server has network access to the Oracle Cloud ERP URL. ● Update the Java Development Kit (JDK) to version 8. |
Not able to see files in the UCM | Verify that the user roles are set up correctly in the BICC and UCM. Refer to the Configuration Prerequisites for more detail. |
No sample file is found for data store fscmtopmodelam.finglcalaccam.fiscaldaypvo | Upload at least one sample file to WCC to be used for schema discovery |
The connector did not find any extract file for the datastore fscmtopmodelam.finglcalaccam.fiscaldaypvo , or a file with a title like file_fscmtopmodelam_finglcalaccam_fiscaldaypvo-batch*.zip | There must be at least one file in the UCM so that the connector can discover which columns are selected for extraction. Sign in to the UCM and search for the file. If it exists, re-extract the file from the BICC. |
No schema named [catalogam] found under catalog [fscmtopmodelam] | This means that the connector did not find any files whose names start with the prefix file_fscmtopmodelam_catalogam* . Sign in to the UCM and search for that file. If it exists, re-extract the file from the BICC. |
Failed to load data from [Fusion] due to [Column PURCHASEORDERDISTRIBUTIONPOHEADERID] not found in source schema | Source columns were either dropped or renamed in the Oracle Fusion source VO. Open the table in the schema, select Files, Save, then Done. |
Mismatch of columns in various files of a VO in the UCM | New columns were added to the fields when the offering was uploaded and the full load files were extracted because data has been previously extracted. Read only the files in the UCM from a certain date by adding a date filter in the File Criteria - Last Modified Timestamp connector property. For example:: >= '2021-01-01 00:00' . The other option is to delete older files from the UCM. |
Disk full error | Contact Incorta Cloud Support to change the cache clean period for the UCM ZIP files to something less than the default of one hour. |
Table load hangs | Open the loaded log. The load may have been interrupted while the tables were still reading from the data files, which can cause an java.io.IOException: Premature EOF IO error and the file lock to not be properly released. Restart the loader. |
You can use BI Publisher to debug in the Fusion Application. Refer to the Troubleshoot section of the Cloud Applications documentation for further information.
Support for Source System Row Deletion
- Create a new Oracle Cloud Applications data source.
- Select .pecsv for File Extension.
- In the BICC, perform a Primary Key Extract. Select Manage Extract Schedules, and for Job Type, select Active Primary Key Extract.
- Create a new schema, or use an existing one, and add the PK tables using the Schema Wizard.
- Perform a full load of the PK table.
- Create a join from the base table to the PK table, with the PK table as the parent.
- Create a table security filter on the base table that specifies a field of the parent table is not null. Here is an example:
not(isNull(PK_Schema.PKTable.id)).
The deleted rows from the base table will always be filtered from the dashboards. If required, you can also schedule a periodic full load, such as every week, to filter the deleted records from the base table.
Add non-Oracle Cloud ERP tables in Incorta
There are many Fusion modules like Oracle Trade Management or Oracle Configure Price Quote (CPQ) that do not integrate with BICC. Here are the steps to add data from these modules:
- Create a CSV report using a tool like BI publisher, zip it, and name it with the following format:
file_<catalog>_<schema>_<table>-batch*.zip
. For more information onn how to define a destination file name dynamically using a date expression, refer to Fusion Middleware User's Guide for Oracle Business Intelligence Publisher. - Upload the file to the UCM or OCI Object Storage.
- In Incorta, use the Schema Wizard or directly create a table in a schema using the Fusion data source. The connector will import the object from the .zip file .
- Sign in to to the Oracle Webcenter console and select New Check-In.
- In the Content Check-In Form,
- For Type, select a document type.
- For Title, enter a name for the file.
- For Author, select the owner from the list.
- For Security Group, select a security group.
- For Primary File, select the CSV report to upload.
- For Revision, enter the revision number. By default, the value will be 1.
- For Release Date, select the release date. By default it will be set to the current date.
- Select Check In.
- Once the document is uploaded successfully, select Search and then select Advanced Search.
- Enter the Security Group in the Content Check-In Form to filter the results.
- Select Search. The CSV file will appear in the search results.
Oracle Cloud Infrastructure Object Storage
You can access BICC content in OCI Object Storage. Here are the steps:
- Sign in to your Oracle cloud account with the following URL:
https://www.oracle.com/cloud/sign-in.html
- Enter your username and password.
- Navigate to the Object Storage tab, which lists the existing buckets associated with your region.
- Select Profile.
- Select API Keys in Resources.
- In the Add API Key dialog,
- Select Download Private Key.
- Select Download Public Key. Download the public key to the same directory as the private key.
- Select Add.
- In the Configuration File Preview dialog, select Copy.
Create the OCI Configuration File
- Open a terminal session to the directory where you downloaded the keys.
- Enter
Code <CONFIG_FILE_NAME>
, to open a text file. - Paste the Configuration File Preview content.
- Add a path to the private key file. For example:
key_file=/Users/mac/Desktop/oci_test/<KEY_FILE_NAME>.
Create an Oracle Cloud Applications OCI Data Source in Incorta
- In the Navigation bar, select Data.
- In the Action bar, select + New → Add Data Source.
- In the Choose a Data Source dialog, in Application, select Oracle Cloud Applications.
- In the New Data Source dialog,
- For Storage Type, select OCI Object Storage.
- For OCI Configuration FIle Path, enter the path to the configuration file for OCI Object Storage that contains information about the user, profile, tenancy ID and region.
- For OCI Configuration Profile, enter the profile you wish to access your data from. The configuration file can contain multiple profiles, each of which has its own authentication credentials, so you should select a specific profile to use.
- For Bucket, ener the name of the bucket that contains the target data files. A bucket is a logical container for storing objects, such as a folder in a file system.
- For Data Type Discovery Policy, leave Use Both Sample data and Metadata Definition Files selected.
- For File Name Pattern, leave the default value of
file_<catalog>_<schema>_<table>-batch*.zip
. - For Keep file(s) for, select how long you would like to keep the files.
- Select Ok.
Refer to the Set up Oracle Fusion SaaS Business Intelligence Cloud Connector (BICC) to use Oracle Cloud Infrastructure (OCI) Object Storage article for more information.