Oracle Analytics Cloud

Oracle Analytics Cloud (OAC) has made great strides in becoming a top tier analytics tool by providing a quick-to-deployment solution that can consume data from a broad variety of sources. This data can then be stored within a multidimensional cube within the Essbase component of OAC and analyzed via Oracle's Data Visualization tool. This creates a fantastic, flexible solution for self-service or published analytics.

Typically, an organization utilizing OAC also has a variety of transactional systems that are ripe for integrating and analyzing via OAC.

Workday is a well-established solution for cloud-based financial and human capital management. This means it will have a deep well of data related to the business that can provide insights and understanding to every level of your organization. Integrating OAC with Workday can provide that analysis and make it available across your organization.

OAC can be integrated with Workday in one of two ways:

• 1. Through direct connect capabilities that come standard in OAC
• 2. Through the use of flat files that are generated by Workday and consumed by OAC

This blog will focus on the second bullet - Integrating metadata and data through the use of flat files. Keep in mind, this whole process can be automated and scheduled based on your business requirements.

Metadata Loads - Flat File

The first step in integrating metadata with OAC using Workday is to have Workday provide the file extracts needed with all information called out in the file. The file should be provided in a comma or tab delimited .txt file with all parent-child relationships residing within the file. OAC will use the parent-child relationship to build the hierarchies needed in the outline.

For example, if trying to automate the build of an account hierarchy in OAC, the file might look something like this:

Parent,Child,Alias

Assets,10000,Accounts Receivable

Liabilities,200000,Accounts Payables

Net_Income,40000,Revenues

Net_Income,60000,Operating Expenses

By using dimension load rules within OAC, this file can be loaded directly into the OAC file repository and then ran through the load rule created specifically for the account dimension. What you would typically see is two metadata files for each dimension being sourced from Workday. The first file would contain all level 0 accounts and their respective parents (level 0 referring to the lowest level members in the hierarchy). The second file would contain the parent member build. Using the example above, the second file would have the detail needed to map Assets to Balance Sheet and Net Income to Income Statement. It is ok if the files being provided have extra columns with details not needed, these columns can be ignored within the load rule.

Metadata load rules also give us the ability to add prefixes or suffixes to the members coming over from Workday. A best practice would be to give a prefix before all level 0 members. For example, we might want to add "A_" before all the account member names so they would look something like "A_40000". The purpose of this is to eliminate the chance of any duplicate member names or aliases within the application.

Data Loads - Flat File

Separately from metadata loads, data loads can be built within the OAC application. Again, starting from the Workday side, we would need to extract a format friendly file with all the required information needed to be called out in the file. To provide an example of how this file might look, let's assume we have 7 dimensions in our database: Account, Company, State, Period, Year, Scenario, and Version. Based on this, the file might look something like:

Account,Company,State,Period,Year,Data

Revenue,ABC_Corp,New_York,Mar,2019,"10,000"

Expense,ABC_Corp,New_York,Mar,2019,"5,000"

Through the load rule, we would be able to map each column to a specific dimension to tell the data where to go. Notice how Scenario and Version are not called out in the file. This is okay since these are typical descriptors that are specific to OAC. Through the load rule, we would be able to prepopulate a field needed to load our data into the "Actual" scenario and the "Final" version.

Overview of Automation

This entire process can be fully automated from the extract of Workday data to the import of that data into OAC. Here are the required components to automate the process:

• A SFTP utility - needed to pull the files that are being extracted from Workday and place them on the server
• A small server - needed to hold the extracted Workday files, any backups of the application, and a scheduler that can kick off the command line jobs
• OAC Command Line Utility - this is the automation utility that comes standard with OAC. Command line scripts can be created that will pick up the updated Workday files on the server, replace the current files in OAC, and then execute the metadata and data load rules.

Assuming the automation process runs overnight, users will have the ability to access the most up to date actuals for the current year to provide for better and faster analysis. This process could be scheduled to run on more frequent intervals as required by the business.