On-Premises

Do Oracle’s OOW cloud announcements signal the death of on-premises EPM?

This week at Oracle Open World, Oracle has announced more details around a few new EPM Cloud products (Essbase Cloud Services, PCMCS, and DMCS) in addition to their already existing stack of SaaS cloud services (PBCS, EPRCS, EPBCS, ARCS, and FCCS).
axeWith these new offerings added to their stable, is this the death of on-premises EPM as we know it?

Oracle’s stated direction of product strategy for the EPM products is to tap into unserved business users. EPM has been predominantly used by corporate finance departments from the beginning. At one point, Hyperion was marketed to CFOs and not CIOs because it could be run on an administrator’s computer under a desk without IT involvement. The evolution of EPM cloud is a return to the golden age of Essbase – easily created departmental applications that provide better analytic ability than Excel alone.

The EPM cloud products are really all about allowing easy adoption for non-traditional EPM users and providing rapid value to their customers. Spreadsheets still dominate at small to medium companies. The cloud offerings are really simplify life for those companies who struggle with maintaining servers and have a lack of technical skill at designing an optimized solution. With the EPM cloud products, it’s very easy to roll out a Workforce or CapEx application in EPBCS by sending out the URL and paying the monthly subscription fees. The cloud also allows the business users to be in the driver’s seat by not needing IT resources to get them up and running.

As we know, there is a long way to go yet on the EPM Cloud roadmap to get all of these products working well together. For instance, how exactly do we get data from our EPBCS application into ESSCS for additional reporting? How about my BICS dashboards using data from my ESSCS departmental cube or my PBCS budget data? It’s clear to see that with Oracle’s growth in the cloud and continued development of additional features and functions on the cloud products that these drawbacks will be remedied in time.

This whole cloud thing is just a fad, it will pass, right?

Even Mark Hurd stated during his keynote on Monday that the cloud is no fad, it’s a generational shift that is here to stay. Oracle has stated publicly that they fully intend to continue to support and develop EPM on-premises solutions. Matt Bradley, SVP for EPM and BI Development at Oracle, has said that Oracle expects most companies will enter into a hybrid cloud implementation if and/or when they decide to move their investments into the cloud. They have developed tools in DRM and FDMEE to support these hybrid cloud implementations. The shift to cloud computing is happening, but it doesn’t signal the immediate end of the line EPM on-premises. Once the cloud products have fully matured, there may continue to be valid use cases for on-premises EPM products going forward.

So, what is the future of my on-premises investment?

The market indicates that there is a healthy appetite for cloud solutions and all indications are that Oracle expects even large customers to eventually move their EPM investments to the cloud. While the on-premises products are still being developed, the availability of new on-premises versions has slowed down. For the last few years, we were blessed with several major releases of EPM software from 9.3.1 to 11.1.2.4. Oracle noted that the software release adoption cycle was about every two to three years, so we expect that the new software releases for on-premises will be more in line with those adoption cycles. We should expect to see some new features and functionality through Patch Set Updates to the latest code line in between major upgrades. Future on-premises releases will begin to showcase a simpler architecture to the components and focus on usability.

What should we do with our on-premises EPM environments now?

If you haven’t already upgraded to version 11.1.2.4, it is highly recommended. The 11.1.2.4 code line has some great features like better support for Essbase Hybrid Aggregation, improvements in HFM consolidations, FDMEE data synchronization between EPM applications, and the new Financial Reporting Web Studio. I have been on several calls with customers who are still working in old releases and the Classic Essbase add-in. It is time to move on and update those environments. If you have upgraded to version 11.1.2.4, it’s highly recommended to keep up with the latest Patch Set Updates on at least a quarterly basis. Sometimes applying the latest patches may cause some issues, so thorough testing of new patches is always recommended before implementing into production.

Staying on the latest release also allows companies to bridge from on-premises to cloud much easier. For example, as mentioned earlier FDMEE and DRM already support hybrid cloud implementations. Oracle has doubled-down at OOW 2016 on their assertion that cloud computing is the future. While on-premises EPM software isn’t going away any time soon, the cloud products are going to continue to mature rapidly. As the cloud products develop and integrations between them become more defined, more and more companies are going to see the benefits of moving their EPM investments into the cloud.

Advertisements

FDMEE and Essbase ASO clears

FDMEE to Essbase

Last month we covered FDMEE and Essbase (BSO) calculations. This month, let’s take a look at FDMEE integration with ASO. With BSO, we set up calculation scripts to do a clear before the data load and an aggregation after the load. With ASO, there are no calculation scripts so we can’t use the same functionality.

Partial clears in ASO can be done using MaxL, the Essbase scripting language. Those of you familiar with Essbase already know, an aggregation is not needed after the a data load to ASO as those cubes dynamically aggregate.

There are several ways to accomplish these clears, most of them revolve around using Event Scripts in FDMEE. Event Scripts are written in Jython or Visual Basic. Jython is an implementation of the Python programming language, designed to run on the Java platform. Jython is a more powerful language than VBA and it’s fairly easy to learn and work with, so that is what I use when writing Event Scripts.

We have many intervals in the FDMEE integration process where we can insert custom code into an Event Script. Each FDMEE process has a before and after script that can be customized. Since we want data to remain in our Essbase ASO application for as long as possible, we will use the BefLoad script to run our clear process.

It’s possible to call a batch file that will execute your MaxL script to run the clear, but I like to call MaxL directly from Jython. This method requires that the Essbase Client is installed on your FDMEE server so that the startMaxl.cmd script is available. Of course, we should be using encrypted MaxL scripts so that our passwords are not visible in either the Jython BefLoad.py script or in our MaxL script.

In this hypothetical situation, our Club Electronics stores in Delaware have submitted their ATM Sales. Lets say that Club Electronics submits a new file each day to update our ASOSamp application with the month-to-date sales numbers. To make sure that we are loading the correct data each time, we need to clear the existing Delaware ATM sales for Club Electronics for the current month and current year.

This scenario could be accomplished by hard coding values in for Delaware in MaxL, but we have other states that submit similar files using different locations in FDMEE. In order to make our clear script usable by multiple stores and entities, we can pass variables using Jython to MaxL to dynamically clear portions of our ASO cube based on the location (or data load rule, or integration option set in FDMEE, or many other variables).

So, let’s begin in FDMEE with our file integration using ASOSamp as our Target Application. I have already set up ASOSamp as a Target Application, created our Import Format for a comma delimited file, created our Location for Club Electronics ATM Sales (CE_ATM_Sales), and created the Data Load Rule to load this data.

MaxL Script

Our MaxL script accepts three parameters: Geography, Month, and Stores. We have our MaxL encrypted so that no passwords are stored in plain text. The trick to getting this to work, I have found, is using double quotes around the MDX expression in the MaxL statement. This allows MaxL to properly evaluate the variables. You can hard code some or all of the MDX tuple, I did a little of both here.

ASOSamp MaxL Clear.csv

Jython BefLoad.py Script

In the BefLoad script, we need to test and make sure that which FDMEE Location is being loaded to ensure we are running the proper code. This can test can also be done at the Load Rule level, if you have multiple rules in the same location. Next, the script calls startMaxl.cmd which is installed as part of the Essbase Client installation and passes the variables to the MaxL script.

ASOSamp BefLoad

Passing Variables

The trick to getting all of this to work is the ability to pass variables; either dynamic variables that come from FDMEE (Location name, POV month, etc.) or static variables that we have coded into the Jython script. In the example above, I show how to pass a variable with a space from Jython to MaxL. By escaping the double quote (“) with a backslash (\), we are able to pass the variable from Jython to the Windows Command prompt surrounded in double quotes (“).  Without the escape character, the variable will not get passed correctly.

Logs

In our FDMEE process logs, we can see that the code is running properly thanks to the fdmAPI.logInfo lines we added to the BefLoad script:

2016-07-22 17:53:11,871 INFO [AIF]: Executing the following script: C:\FDMEE/data/scripts/event/BefLoad.py
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:11,923 INFO [AIF]: BefLoad Script: Begin
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:11,923 INFO [AIF]: Submitting MaxL to selectively clear ASOSamp
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: MaxL commands to ASOSamp were successful
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: BefLoad Script: END
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:14,825 INFO [AIF]: EssbaseService.loadData - START

In Essbase, we can also verify that the MaxL code is executing properly by checking the ASOSamp application log:

[Fri Jul 22 17:53:12 2016]Local/ASOsamp///6600/Info(1013210)
User [admin@Native Directory] set active on database [Sample]

[Fri Jul 22 17:53:12 2016]Local/ASOsamp///6544/Info(1042059)
Connected from [::1]

[Fri Jul 22 17:53:12 2016]Local/ASOsamp/Sample/admin@Native Directory/6544/Info(1013091)
Received Command [AsoClearRegion] from user [admin@Native Directory]

[Fri Jul 22 17:53:13 2016]Local/ASOsamp/Sample/admin@Native Directory/6544/Info(1270602)
Removed [25] cells from input view. Partial Data Clear Elapsed Time [0.258334] sec

[Fri Jul 22 17:53:13 2016]Local/ASOsamp/Sample/admin@Native Directory/6544/Info(1013273)
Database ASOSamp.Sample altered

[Fri Jul 22 17:53:13 2016]Local/ASOsamp///7080/Info(1013214)
Clear Active on User [admin@Native Directory] Instance [1]

With the ability to call MaxL directly as part of FDMEE scripts, your integration is only limited by your imagination. To take this post another step further, you might decide to update substitution variables in Essbase based on the FDMEE POV that is being loaded or maybe even build aggregate views using MaxL in the AftLoad.py script without much additional effort.

FDMEE and Essbase calculations

Since January, I have been on a project using FDMEE against Essbase and Planning applications. There are certainly many more companies using FDMEE with HFM, so I wanted to share some things I have learned about FDMEE and Essbase.

The topic of this post is about the process of running calculations in Essbase. Typically with Essbase data loads, especially repetitive loading of data during a close cycle, we will want to clear out the data first and then load in the new data. Of course, after that data loads to Essbase (BSO) we will likely need to run an aggregation after the load.

Lucky for us, Oracle baked in the ability to run calculations on your Essbase Target Applications at certain points during the load process. In this post, we will cover how to set up a clear script before a data load, but an aggregation script after the load is the same process with a different script.

First, we need an Essbase calculation script to run a CLEARDATA command. For this occasion, I wrote a quick little CLEARDATA script to clear out Sales in California for whatever period I happen to be running in FDMEE. I have saved the script in the Sample.Basic application as “fdmClear”.

SET CALCTASKDIMS 4;
SET CALCPARALLEL 4;
SET UPDATECALC OFF;

SET RUNTIMESUBVARS
{
POVPer = "";
};

FIX(&POVPer, "Sales", "California")

CLEARDATA Actual;

ENDFIX

Notice the use of RUNTIMESUBVARS for my POVPer variable. By defining these in our calculation script, FDMEE will be able to pick up the variable and pass a value when it runs the calc script.

To set up these calculations, we need to be on the Setup tab of FDMEE and have the Target Applications window up. If we switch to the Calculation Scripts tab, we can add our scripts.

EsbCalcs1

We need to click the Add button to set up our clear script. I have entered in the “fdmClear” name for the script and clicked on the pencil icon to add my calc script parameter. In the “Set Calculation Script Parameters” window, I clicked the magnifying glass icon. This forces FDMEE to look back at the calc script and pick up the POVPer variable.

EsbCalcs2

Now we have a choice as to what value to assign to our POVPer variable.  In our simple use case, we will use the POV Period option and click OK.

EsbCalcs3

Next, we can select the appropriate scope for the calculation. For instance, if we have a clear for this application and it only affects a particular slice of data, we would want to use a more granular scope like data rule or location. Script scopes with higher granularity override those with lower granularity. So, if we have multiple scripts with different scopes, the most granular one will take effect (data rule scope overrides a location scope, location scope overrides category scope, and category overrides application).

EsbCalcs4

For any selections other than Application, a Scope Entity box will appear and allow you to specify what rule, location, or category to use. I chose Location and selected my Loc1 sample location.

The next choice is to decide when to execute the calculation: before the data load, after the data load, before the check process or after the check process.  In my case, I selected Before Data Load.

EsbCalcs5

Finally, if we end up with multiple calculations with the same script scope, scope entity, and event, we can specify a Sequence value to order the calculations appropriately. I only have one calculation, so this is left null.

Calculation Script Parameters

I was curious if a regular substitution variable would work, so I created a Market subvar (mkt) on the Sample.Basic application and a Measures subvar (acct) as a global variable to test.

EsbCalcs6

EsbCalcs7

No dice. When we attempt to add our substitution variables in the parameter names window, FDMEE is only able to retrieve the RUNTIMESUBVARS defined within the script.

EsbCalcs8