Low Code Your Financial Close with KNIME and ExoInsight

The office of the CFO depends on financial and operational data to facilitate the creation of budgets and forecasts and to close the accounting books every month. All of these processes are of critical importance to ensure financial targets are hit and business performance can be measured. Of course, these processes often contain many steps, involve multiple groups, and pull data in from multiple sources.

Obtaining the necessary data and metadata from the financial and consolidation systems is often one of the most cumbersome tasks involved with the financial close. The data stored in Oracle Essbase, PBCS, FCCS, and HFM is some of the most critical for analysis and month end close reporting packages, but it can also be some of the most difficult to access.

KNIME has partnered with Casabase Software to enable you to easily access these important financial systems directly from your KNIME workflows. ExoInsight by Casabase Software provides KNIME nodes specifically for pulling data from Oracle Essbase, Oracle PBCS, Oracle FCCS, and Oracle HFM. This allows you to use the power of KNIME Analytics Platform to low-code processes that manage and streamline your financial close and budgeting/forecasting processes, ultimately saving time and money, reducing risk, and automating manual tasks. Let’s look at some examples!

The most common model for enabling reporting, forecasting, and budgeting of project-related capital expenses with Oracle Essbase is to have a dedicated capital expense cube that consolidates all the project costs and then feeds this data into a primary reporting cube to combine it with the P&L and balance sheet. Often the capital expense cube will contain the project-level detail but the reporting cube will only contain a higher-level category rollup, or just a single capital expense number. This necessitates the need for reconciling between the capital and reporting cubes to make sure the numbers tie throughout the close process, typically a very manual process that can introduce significant risk.

By taking advantage of the low-code and automation abilities of KNIME and combining them with the data access capabilities of ExoInsight, you can create a process that assures that not only the data always ties between the capital and reporting cubes, but that the data feeding the capital cubes from the GL source is accurate as well.

There will be three primary components to our validation process:

• Data Access
• Validation
• Notification

For Data Access, we’ll need to obtain data from the source GL which will tell us which projects need to be included in the validation, data and metadata from the capital expense cube, and data from the reporting cube. First, let’s query the source GL for the projects that need to be included in the validation. We’ll use the DB Query Reader node with a SQL Statement that is structured to bring back the project number and associated costs.

This will return a table that will be used to validate that the capital expense cube has both the appropriate projects loaded and the costs associated with those projects are correct. Note that a third column concatenating the PROJECT_NUMBER and PROJECT_COST values is created; this will be used for the project cost validation step.

Next, we need to pull in the equivalent information from our capital expense Essbase cube using the ExoInsight for Essbase node.

Now that we have both sets of data, we can use the Reference Row Filter to easily capture which projects appear in the source system but not in the capital expenditure cube.

A similar process could be used for the project costs, but this time the concatenated column from the source GL will be used.

To validate that the capital cube values match the reporting cube capital expense costs, assuring that the integration between the capital cube and the reporting cube is running properly, all that is required is a simple four-node flow.

The two ExoInsight for Essbase nodes will pull the capital values from the capital and reporting cubes. The Concatenate node will combine these two results, and the Rule Engine node will compare the values. A true value can lead to a success email, and a false value can lead to a notification to the appropriate people that the integration process needs to be investigated for errors.

Now the workflow can be published to KNIME Server and run on a schedule throughout the monthly close process. Depending on the results of the validation, any number of steps can be taken: email projects that have not made it to the capital cube to the appropriate people, create a load file to resolve any project cost issues, or create a notification that everything is appropriately validated.

Another common FP&A function that could easily be automated with a KNIME and ExoInsight low-code solution is the validation of budget and forecast submittals. The ability to have visibility into which entities and departments have submitted their data is critical to monitoring the progress of the submittals and enables a proactive approach to meeting submission deadlines.

Only two primary nodes are needed to create the logic for a submittal validation workflow: an ExoInsight for Essbase node and a Rule Engine node, pictured in the graphic below.

Creating the Essbase query in the ExoInsight for Essbase node per department or entity and testing whether the entity has provided the requisite data is a quick and easy way to validate submittals of the budget or forecast. Additional nodes can be added to the workflow to produce a red/green light chart of whether entities had submitted their data or not, or generate emails at various times to remind those who have not yet submitted that the deadline is near.

Metadata audits are an extremely important and critical process for FP&A teams, especially those under strict accounting controls related to the publishing of financial results to investors. Making sure that dimensions, members, and formulas have not changed, or only approved changes have taken place, can easily be done in a KNIME workflow with the ability of the ExoInsight for Essbase node to pull metadata information.

Pulling the metadata information from an Essbase cube with the ExoInsight for Essbase node is as easy as putting the dimension name in the query box and running the query, as shown below.

All the relevant information is returned, including member names, parents, generations, levels, formulas, UDAs, attribute dimension values, etc. This information can be compared to a previous extract to ensure that only expected changes are taken place, and action can be taken when an unexpected change is flagged.

The primary goal of the financial close and budgeting and forecasting processes is to monitor the health of the business, adjust strategies based on these findings, and make a plan for the future. To do this, the data that is curated and validated must be presented in an easily understandable and digestible way. Using the KNIME reporting and charting functionality with your Oracle Essbase, PBCS, FCCS, and HFM data can provide a convenient and powerful way to generate your reporting packages.

KNIME Analytics Platform gives you a way to present your data in any way you like by easily manipulating the result sets that ExoInsight gives you. Dynamic and referenceable visuals can easily be updated, modified, and presented to the management team on a scheduled basis, eliminating the need for manual work.

Of course, this is just a small sample of the normal accounting close or budgeting/forecasting processes that can be automated using KNIME and ExoInsight. Additionally, ExoInsight enables you to create all the same functionality described in this article for Oracle PBCS, Oracle FCCS, and Oracle HFM as well. KNIME Analytics Platform provides an easy way to automate many of the manual processes that exist today in your FP&A department, and the easy data access provided by ExoInsight enables you to work with data that traditionally has been difficult to access.

Combining workflows such as these with the automation and scheduling ability of KNIME Server is where amazing gains in productivity and reduction in risk and duplicate efforts can be realized.

The ExoInsight KNIME extensions are free of charge, however you will need the commercial ExoInsight for the extensions to work. You can contact Casabase Software to download the ExoInsight KNIME extension to get started. User guides can be found here. Reach out to Casabase Software for a free demo and to learn how easy it can be to start working with your Oracle Essbase, Oracle PBCS, Oracle FCCS, and Oracle HFM data and hierarchies in your KNIME workflows!