FDMEE and Essbase ASO clears

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.

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.

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.

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.

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.

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).

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.

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.

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.

Getting Started with FDMEE (for on-premises and hybrid cloud implementations)

Ever since Hyperion Application Link (HAL) died, people have looked for ways to help them load data and metadata into their Oracle EPM applications. ODI became a favorite tool of several developers due to its flexibility and ability to load to Essbase, Planning, and HFM. However, a complicated and in some cases very expensive licensing structure makes ODI less appealing today as it has been in the past for EPM practitioners.

You may be thinking, “But I get to use ODI for free with my Planning Plus/Financial Management Plus licenses.” That is true; however, according to the EPM Licensing Guide, the Restricted Use License for ODI only allows the use of the embedded agent within the ODI Studio. It does not allow for the use of standalone agents. Standalone agents are the key to running the ODI scheduler, so you are effectively limited to running your ODI scenarios manually from ODI Studio.

As you may know, FDMEE was released in the 11.1.2.3 version and has really blossomed in the 11.1.2.4 release.  As Oracle has stated on their road map, FDMEE will become the favored solution for loading data into and synchronizing data between EPM applications. That transition is well on its way.

So, let’s pretend that you are a customer who has “seen the light” and purchased FDMEE licenses (and at least one adapter license) and are now ready to implement. Where do you begin after installation?

Generating the System Folders

Setting up the FDMEE file structure should be one of the first things that you do in a new implementation of FDMEE.  This allows the importing of files into FDMEE and sets up all of the folders needed for FDMEE to work properly.

From inside FDMEE, click the Setup tab.

Next, click the System Settings link on the left side.

Enter your application root folder directory. C:\FDMEE is my directory on this sandbox VM as all of my products are deployed to one server. You may use a UNC path for the application root folder instead of an absolute path. Click on Create Application Folders.

A confirmation message is displayed:

Period Mapping

Before we get too excited about setting up source systems and target applications, I like to make sure that my POV has all of the required elements first. It’s pretty easy to forget the period or category mapping when we are first setting up FDMEE and then we’ll get all sorts of strange messages when trying to run our first integration.

Before setting up your period mapping, we need to take a look at the applications that we are going to be integrating with. What is the most granular period of time for all of your applications? Is it Monthly, Weekly, Daily, or something else entirely? The most granular period level determines how your Global Period Map is set up.

At a client of mine, they have one daily application, several monthly applications, and one yearly application.  That forces us to create a period mapping for every day in the Global Period Map. We then use an application mapping to choose the first of each month as a valid period for our monthly applications. The yearly application similarly has the first day in their fiscal calendar for each year in the application map.

In FDMEE 11.1.2.4, Oracle added the Excel Interface which allows us to download data from the FDMEE tables into Excel. This is a great way to fill out your Period Mapping data without painfully entering each day into the FDMEE interface. I will typically start by adding a couple of periods into the Period Map manually to seed it with some data before exporting it to Excel.

To seed the Period Map, select the Period Mapping link on the Setup tab in FDMEE.

Click the Add button to add the period maps.

Enter the appropriate data for the Target Period and Year Target columns. Click Save once complete.

Notice the Period Name field is Month-Year. This is due to a not very well documented “feature” that requires Period Names to not include spaces if we are going to use the FDMEE batch script utilities to kick off data load rules.

To fill out the rest of FY16, let’s use the Excel Interface. Click on the Excel Interface link on the Setup tab of FDMEE.

Open the Entity Type drop-down box and select Period Mapping.

Enter a file name and click the Download button. FDMEE will append the .xls extension for you.

The file will be saved in your FDMEE application folder location.

Next, I will open the Period.xls file that I saved and drag the PERIODKEY column down to auto-fill the rest of FY16.

I do the same for the other columns with data, but we ran into a problem with the text field PERIODDESC.

To fix that, I write a quick formula using the TEXT() function to build the correct PERIODDESC based on the PERIODKEY field. These functions come in very handy if you are working with a daily Period Mapping table.

Next, copy the formulas and paste the values over the incorrect PERIODDESC entries.

The Excel Interface files have a named range in them that corresponds to the FDMEE repository table name. Any time that I have inserted records into a table like this, the range always seems to be thrown off. The last step is to fix the named range and make sure that it includes your newly added records.

In Excel, click on the FORMULAS ribbon and click the Name Manager button to edit the named range.

Edit the range to include all of the data cells and the header records.

Save your file back to the FDMEE application folder location. Now, we need to upload the file to FDMEE to insert our Period Mapping records.

On the Excel Interface page, under the “Upload From Excel” box, click the Select button to browse to our Period.xls file and click OK.

Click the Upload button to upload the file. There is a log displayed on the Excel Interface screen that shows the status of your actions.

Now, browsing back to the Period Mapping task, we can see that our periods were imported properly.

Adding additional periods is an ongoing maintenance item that an FDMEE administrator will need to do periodically. The Excel interface only allows adding new data to the TPOVPERIOD table, it does not allow you to update existing data unfortunately.

Category Mapping

The final bit of setup for this post is the Category Mapping. The category maps are much less dynamic than the Period Mapping and usually will not require as much maintenance.

For the categories (or scenarios), I typically add them manually through the FDMEE user interface. Simply click on the Category Mapping task in the FDMEE Setup tab and add the categories (or scenarios) as needed. Here I created a Budget category that will map to the “Plan” dimension in my application.

These are some of the first steps that I would typically take when getting started in FDMEE. For now, this is where I will leave you. There are many more things to do such as set up your source and target applications and create import formats, locations, data load rules, and data maps. The FDMEE administration guide is an excellent resource for the other tasks as well as scripting in Jython. I recommend bookmarking the EPM Documentation site it as you will use it often.

FDMEE 11.1.2.3.520 connections – ODI trickery for Oracle databases

I recently wrapped up an infrastructure project at a client that needed to prove the connection between Peoplesoft and FDMEE would work. The client will then use this connection going forward as he builds out his brand new implementation.

We kept getting stuck with the FDMEE connection “initialize” step. The DBA granted us read-only access to the Peoplesoft tables through a separate schema than the owner of the tables. For security reasons, they didn’t want us logging directly into the Oracle database as the owner of the Peoplesoft tables and for that I cannot blame them. I setup the connection in ODI as I have a few times before and expected that everything would be just fine. Unfortunately, we kept getting an Oracle error in ODI: ORA-00942: table or view does not exist.

I connected to the schema using SQL Developer and when I tried to view the tables, I could see that no tables existed under my service account schema. I was able to query the Peoplesoft tables through synonyms, though, so we assumed that everything would work fine.

As we looked at the errors in the ODI logs, we could see that it was attempting to access the tables using a “Schema.Table” naming convention. The schema that it was using was our service account schema “SVC_HYPERIONSQL”. Since that schema didn’t contain any tables, it was clear that was the cause behind the errors, but how to get the integration to point to the correct schema where the tables exist?

I went back into ODI and looked at the Data Server connection. That’s when I realized that the “Schema” that it was asking for needed to be the owner of the Peoplesoft tables. We could use our other service account schema as the Work Schema, but in order for the integrations to run properly, they needed to access the tables using the correct account, which was SYSADM for this environment.

Must select the proper schema that owns the ERP tables when using Oracle repositories.

After selecting the proper schema, the “Initialize” step in FDMEE worked without a hitch.