Azure: SQL Database and EPBCS Integration
By: Ivan Casarrubias - Principal Consultant
Published: November 2, 2020
Oracle EPM Cloud has limited source system connection options when setting up source systems via Data Management. By leveraging EPM Integration Agent, data can be imported from non-Oracle systems such as an Azure SQL Database.
In this tutorial, we have created a database in Azure for data related to bank accounts. We have created a .Net web app that mimics an ERP data entry system for new bank accounts. Data is taken from a web form and inserted into a table in the Azure SQL Database that holds records for bank customers. We will then run EPM Integration Agent to pull the newly added records into Oracle EPBCS using a direct connection to Azure.
SQL SERVER MANAGEMENT STUDIO
In SQL Server Management Studio, we have connected to the Azure SQL Database to run a Query that pulls in the 8 sample records which currently exist in our database.
The same SQL Query will be used in Oracle EPBCS to create a Query in Data Exchange.
ADDING A NEW BANK ACCOUNT WEB APP
From the .Net web app, we will add a new record for a fictional customer named Thomas Craven. Using the Account number 111111111 and Customer Information File (CIF) Number 1111111, we have successfully created a new bank account with a $1,111 deposit.
CONFIRMING DATA ENTRY TO AZURE SQL DATABASE
Refreshing our .Net web app with Azure (which is connected to our Azure SQL Database) we see the new record has been added for a total record count of 9 records.
CREATE THE TARGET APPLICATION
To successfully integrate the Azure SQL Database to EPBCS, we must create a Target Application and set up the Application Filters.
The “BankAccounts” Query is similar to the SQL Query we ran in SQL Server Management Studio. Microsoft SQL Server is the JDBC Driver, and the JDBC URL is linked to the Azure SQL Database which contains the Bank Account Data.
CREATE THE INTEGRATION
We select our Source and Target Systems, then specify the Location, Cube and Category. After we complete mapping the dimensions and members, we are ready to fire up EPM Integration Agent from the command line. The EPM Integration Agent initialization can be automated to integrate EPM Automate.
Once the EPM Integration Agent has established a secure connection to Oracle EPBCS, we can run the Data Rule that will pull in the 9 records from Azure SQL Database and stage them in EPBCS.
For this example, we are running the data load rule styled “AZURE_EPMAGENT_BANK” for October 2020. After EPM Automate completes the data load, we can confirm that the 9 records were successfully pulled from Azure SQL Database and mapped correctly in Data Management.
Using only a Header File, we can extract the records from Azure SQL Database via a .dat file. For those of you with experience pulling in data from Oracle ERP Cloud Systems, you will recognize these three green checkmarks which verify that the .dat file has been extracted from the source system.
From the Data Load Workbench, we can confirm that the data has been transformed, staged, and is ready for validation and export. The newly created record for Thomas Craven is highlighted and contains the Account Number 111111111 and CIF Number 1111111.
For those not well versed in SQL or manipulating data within it, you can start with resources here; and anyone interested in following our process for SQL Servers and data can find more information here.
Schedule your Free Consultation, to see it in action!
We offer Autonomous Systems for EPM Cloud Applications such as Oracle Planning & Budgeting Cloud Service (PBCS/EPBCS), Financial Close & Consolidations Cloud Service (FCCS), Enterprise Data Management Cloud Services (EDMCS), Account Reconciliation Cloud Service (ARCS), Profitability & Cost Management (PCM) and more.