Wednesday, November 27, 2019

FCCS - calculating rolling x number of periods

A common type of financial calculation involves a rolling x number of month calculation, like a rolling 12 month net income. In HFM, there were functions and keywords we could use. There are different ways of writing the calculation, but most likely one of these:

HS.Exp "A#R12_NetIncome = A#Net Income.W#Periodic.P#CUR + A#Net Income.W#Periodic.P#CUR-1 + A#Net Income.W#Periodic.P#CUR-2 + A#Net Income.W#Periodic.P#CUR-3 + A#Net Income.W#Periodic.P#CUR-4 + A#Net Income.W#Periodic.P#CUR-5 + A#Net Income.W#Periodic.P#CUR-6 + A#Net Income.W#Periodic.P#CUR-7 + A#Net Income.W#Periodic.P#CUR-8 + A#Net Income.W#Periodic.P#CUR-9 + A#Net Income.W#Periodic.P#CUR-10 + A#Net Income.W#Periodic.P#CUR-11"

OR (assuming a December year end):

HS.Exp "A#R12_NetIncome = A#Net Income.W#YTD + A#Net Income.W#YTD.Y#PRIOR.P#Dec - A#Net Income.W#YTD.Y#PRIOR"


If only a three month rolling amount was needed, use the first approach and just stop after P#CUR-2.

In FCCS, a member formula is a good way to write the calculation (as compared to an insertion rule), but for us HFM converts, how? Essbase has a long list of functions, many of which work just fine with FCCS. The functions we want to look at now are @PRIOR, @RELATIVE, and @CURRMBR. Here are the definitions from Oracle's help website.

@PRIOR - "Returns the nth previous cell member from mbrName, in the sequence XrangeList. All other dimensions assume the same members as the current member. @PRIOR works only within the designated range, and with level 0 members."

@RELATIVE - "Returns all members at the specified generation or level that are above or below the specified member in the database outline."

@CURRMBR - "Returns the member that is currently being calculated in the specified dimension (dimName). This function can be used as a parameter of another function, where that parameter is a single member or a list of members."

So, how do we use these? I don't claim to be the best calc script writer (far from it), but these approaches work for me. For the rolling three month calc, I use this (again, assuming December year end):

IF(@ISMBR("Jan"))

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Dec",1,@RELATIVE("Years",0)) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Nov",1,@RELATIVE("Years",0));

ELSEIF(@ISMBR("Feb"))

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 1) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Dec",1,@RELATIVE("Years",0));

ELSE

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 1) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 2);

ENDIF


Starting with the ELSE part first, the formula gets the current periodic amount and then uses the @PRIOR function to get the first prior and then the second prior month data. This will only work for March to December, though, as the @PRIOR function doesn't wrap around the year like HFM does. So, use the IF or ELSEIF to check for Jan and Feb and for the prior year data points, use the @RELATIVE function within the @PRIOR function to pull from the prior year.


For the rolling 12 month calculation, the above technique could be extended to cover 12 months, but as with the HFM formulas above there is a more elegant way.

"FCCS_Net Income"->"FCCS_YTD" + @PRIOR("FCCS_Net Income"->"FCCS_YTD"->"Dec",1,@RELATIVE("Years",0)) - @PRIOR("FCCS_Net Income"->"FCCS_YTD"->@CURRMBR(Period),1,@RELATIVE("Years",0));


This formula pulls the current month YTD value, adds the prior year December YTD value, and then subtracts the current month, prior year YTD value. The @CURRMBR function is inserting the current month being calculated into the dimension reference for the @PRIOR function. As I type this I'm not sure if this is needed, but as seems to be working I'm leaving it alone.



In closing, Essbase has been around since the early 1990s, so there has been plenty of time to figure out the functions that are needed - us HFM converts just need to learn!








Monday, October 28, 2019

FCCS - Metadata out of sync

It's really rare that this happens, but it happened to me so I wanted to share it with everyone in case someone else runs into it and you'll know what's going on. First, check out this screenshot from the data status grid.


The first two rows are level zero / base level members. For this post ignore row two. The third row is the parent. The fourth row is the parent's parent (in this case it is FCCS_Total Geography). Note that the first row shows a data status of impacted, the third row shows no data, and the fourth row shows OK. With the first row being impacted, the third and fourth rows should be impacted as well, but they're not. If the fourth row is OK, then the first and third rows should be OK too, but they're not.

Talked with someone at Oracle and it turns out this happens if the metadata in Essbase gets out of sync with the metadata in the relational database. Again, really rare. There isn't anything that an administrator can do to correct this: force consolidate, refresh database, reset service, etc. do not address this (I tried). The only options are to (a) get Oracle involved or (b) rebuild the app.

Fortunately, it sounds like this problem will be addressed soon (I make no guarantees on the if and when). In the meantime, should you see this situation, now you'll know.




Saturday, June 1, 2019

Archiving FCCS Environment Versions

Hi. It's been a while since I've last posted, but I'm back on track talking about some great changes with the June 2019 update.

First, the product gets a rename to just Financial Consolidation and Close. I'll still use FCCS below, at least for now.

Along with the usual updates, a huge area has been addressed. One of the questions that has repeatedly come up with FCCS is how to archive and access different versions of an application. In HFM, it was simple - just make a copy of the application. As long as you had enough database space, you could make as many copies as you wanted. And some customers did: 2016 year end, 2017 year end, before major acquisition, after major acquisition, etc. So, in FCCS, what can you do?

Up until June, the choice was pretty limited. You save your artifact snapshot (basically the whole environment in a zip file). But, you can't import the snapshot from a year ago as they have to be kept "fresh," meaning you can go back one software version but not further. You could keep the different snapshots and each month load them into test and re-extract them, but that's a big pain, particularly as you start storing more and more snapshots. And if disk space is a worry, then you would have to upload, import, extract, download, and delete; again, more pain. Although in fairness EPM Automate would be helpful here. And all of this would have to happen in coordination with the monthly update cadence, testing for the updates, testing for any development work, etc.

You could always license more environments, but with the 10 user minimum for a three year commitment that would get pricey for the occasional day of use when it's needed.

So, what's changing? There are two changes coming as discussed in the June 2019 update documentation located at: https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/fccs/releases/Jun/19jun-fccs-wn.htm

The first change is the availability of a script that updates the stored snapshots to the current release. The script will take the stored snapshots and update them to the current software version. The script does the update without having to import and then recreate the snapshot: much more elegant. The script should be run monthly in production after the monthly update. Find the details on this page: https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/sample_script_10_snapsht_library.html

The second change revolves around environment licensing changes. With the licensing change, Oracle is making it easier to get more environments, even with low user counts. Users need the current production environment and last year's ending environment both available at all times? No problem! At a very high level summary, you pay for each user once and then set up FCCS, Planning, Account Reconciliation, etc. There are three licensing levels with some differences. If you have the Standard license level, you basically create as many FCCS applications as you want for an additional fee for each one. If you have the Enterprise license level, there is no additional charge. Go to https://cloud.oracle.com/en_US/epm-cloud for more information. Also see the bottom of this post for a link to Opal Alapat's excellent description.

Beyond archiving, the June update has more fun stuff. Near and dear to me, as I use an Apple computer, is Smart View for Mac! Right now it's forms and ad hoc with functions to come later. There's also a new feature in Data Management to export data directly to a file without creating a custom application.

Also want to say that I hope everyone is going to Kscope19 in Seattle (https://kscope19.odtug.com). Lots of cool presentations going on this year, including yours truly talking about intercompany eliminations and hands on sessions with FCCS and Narrative Reporting (formerly EPRCS).

JUNE 2019 ORACLE EPM UPDATE BLOG HOP:

Continue your educational journey by "hopping" over to the following posts to find out more about specific product updates, as well as gain different partner perspectives on the licensing, naming, and consolidation changes. Thanks to Oracle Ace Director Opal Alapat for setting this up. And if you came to my blog from someone else's blog, thanks for stopping by and hope you come back!

Opal Alapat gives an overview of the June 2019 Oracle EPM Cloud blog hop, as well as details on the product name changes, product consolidations, and new licensing:
https://womaninepm.com/2019/06/04/orclepm-cloud-update-big-june-2019-changes/

Jun Zhang does a comprehensive summary of the June updates by product: https://theepmlab.com/epm-cloud-updates-june-2019-epbcs-fccs-pcmcs-arcs-edmcs-narrative-reporting/

Jay Chapman covers the notable EPRCS (now called Narrative Reporting) June updates: https://functionallytechnical.blog/2019/05/31/new-name-who-dis/

Pete Nitschke discuses the Planning Cloud June updates in more detail: http://essbasedownunder.com/2019/06/pbcs-epbcs-what-is-old-is-new-again/

Kate Helmer summarizes important EDMCS updates:
https://hyperionbarbie.wordpress.com/2019/06/04/19-06-edmcs-update-new-epm-cloud-announcements/

Tony Scalese covers changes to the REST API and EPM Automate:
http://www.fdmeeguru.com/1906-rest-api-epm-automate-changes/




























Friday, February 22, 2019

FCCS - March 2019 Update and New User Interface

Two topics today. The 19.03 update contains nothing new, just bug fixes for existing issues. The list of issues addressed is available on support at https://support.oracle.com/epmos/faces/DocumentDisplay?id=2055579.1 (Oracle login required).

The release notes also mention that starting with 19.05, a new user interface will be introduced. "Sky Blue" includes various improvements and is more consistent with other Oracle cloud applications. New customers will get Sky Blue and existing customers will keep their existing theme, but it can be switched. Here's a preview video: Preview EPM Cloud's New Look and Feel











Friday, January 18, 2019

FCCS - Converting HFM Financial Reports to FCCS

Oracle just released a tool to migrate on-premise Financial Reporting reports for HFM to Financial Reporting reports for FCCS. The tool maps the dimensions, preserves the report formatting and layouts, can convert reports in bulk, and provides ready to use reports. The utility can be enhanced via Python scripting.

Here is the link to the Oracle Cloud Marketplace for the tool:

https://cloudmarketplace.oracle.com/marketplace/en_US/listing/50393071

Looking forward to trying it out!




Tuesday, October 16, 2018

FCCS - Blocks

Those who are coming to FCCS from Essbase or Planning know all about blocks. But for those coming from HFM or Enterprise or MicroControl, blocks are a new concept.

BRIEFLY, blocks are how data is stored in FCCS. A block is a combination of any stored sparse and dense member. Each combination of stored sparse dimensions is a separate block. But all members in a dense dimension is stored in one block. In FCCS, the Account dimension is the only dense dimension, so all accounts are in the block for a given combination of sparse dimensions.

Blocks get automatically created when loading data but not with calculations. You write a perfect calc, deploy, consolidate, and get no result. It looks like the calc either didn't run or didn't work. What actually happened is the calc worked but since there was no block there was no place to store the result.

So, what to do? On the rule insertion points, there is an option to enable automatic block creation.



On the bottom right of the above screenshot, you'll see an option to auto create blocks. Clicking No will change it to Yes with the following warning.


So now when you consolidate, the calc will run as before but now there will be a place to store the data.

There are other ways to create blocks, but this is the simplest (other than loading data).