Upcoming Enhancement to Clear Data Profiles

There is a need, sometimes, to clear data. Whether something got loaded wrong or whatever, there is a need. The clear data screen, below, is almost perfect: enter dimension members and then run the clear. There is one thing lacking. If you look closely at this screenshot, you'll notice the period and year dimensions are trying to use substitution variables. Currently, this does not work. You have to specify the period(s) and year(s) being cleared. This requirement makes clearing data somewhat useless in an automated process. 

In case you're wondering, where this ability will be the most helpful with automation is the ability to clear a period before loading new data. Even if the data is loaded in replace mode, if an entity was loaded to the period in the past and is no longer in the data load, then the system does not know to clear the data. By clearing the period first, then this would no longer become an issue.

There is good news. The idea of allowing the use of substitution variables was created on Cloud Customer Connect back in September 2020 and then a duplicate submission was done in March 2022. On April 14, 2022, there was an update to the March idea. "Should be coming in the next few months" was the update. I assume the normal safe harbor statement applies.

This is welcome news and I look forward to using it. And don't think that the substitution variables have to be manually updated. EPM Automate can populate the substitution variables and then run the clear data profile, so this will be a great addition.

FCCS - Parent currency intercompany matching without running translation

So, there is a question that comes up with the intercompany matching report. We load data and consolidate. We have entities with multiple currencies and all of the parent entities are a single currency. Why then is there a need to also run a translation step in order to compare all of the multiple currency entities in the same common currency as all of the parents?

Looking at the intercompany matching report, the solution looks easy. Change the Currency dimension to Parent Currency. It works for regular reports, right? Here's what happens when the report is executed.

You can change the Currency dimension to a reporting currency, but then the translation process is required. If the Currency dimension is left at Entity Currency, then each entity will be compared in its local currency, which is not helpful at all.

So, what can be done?

Create a member in one of the custom dimensions but not under any hierarchy, like a sibling of Total Custom or whatever. Tag it as dynamic calc. Then, create a member formula as shown below.

This formula pulls the parent currency data of the top level custom dimension member into entity currency for this calculated member. Change "Total Custom" to be the top of the hierarchy.

Last, in the intercompany matching report setup, use this calculated member for that custom dimension and set the Currency dimension in the POV to Entity Currency. Running the report with this configuration, the parent currency data will be shown, which is perfect and the translation rule process can be skipped. 

The report header will still say "Entity Currency" although the header will also list the new custom member as well. A small price to pay to skip the translation step!

Did the FCC consolidation work?

You run a consolidation in FCC (or FCCS as originally known), wait, maybe wait some more, and then you go to the jobs list and see the status is complete. Great! But then you look and see data isn't there or the data status is unchanged. What gives?

A consolidation job status of "complete" DOES NOT mean success or fail. It means simply that the process has finished running. But what you really want to know is the success or fail status. Where do you find this?

In the job listing click on Consolidate to see the details.

Here, you will see some details about the POV and how long the job took. 

But you STILL WON'T know whether it succeeded or not (but if the time duration is really quick then failed is probably the answer). Click Completed at the top left by Job Status.

There, now you get to see whether the consolidation succeeded or failed. Whew!

If you think there should be a better way, can you do anything about it! YES! On Cloud Customer Connect there is an idea posted already. What you can do is to "upvote," meaning click the thumbs-up icon. After clicking, either add a comment if you want or click Skip. The more companies that like an idea, the more likely it will be considered. You can also lobby your Oracle contacts when you see them on calls, etc. Let your voice be heard! Here is the link - both as a link and as raw text for those who don't like clicking links.

https://cloudcustomerconnect.oracle.com/posts/8d62090b81

https://cloudcustomerconnect.oracle.com/posts/8d62090b81

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!

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.

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/

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

Here is the link to the release notes: https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/fccs/releases/Mar/19mar-fccs-wn.htm

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!

FCCS December 2018 Update

Follow this link for the details on the last FCCS update for the year.

https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/fccs/2018-fccs-wn.htm#December_2018_Update

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

FCCS - Housekeeping Tasks

As more and more customers migrate to or license and implement Oracle FCCS, people are asking what they need to do with regard to housekeeping or care and feeding of the application. There are several tasks that should be done to keep FCCS in good shape. Nothing is hard, some can be automated, and those not currently "automatable" will be. These are listed below in no particular order.

1. Validate Metadata - within the metadata manager run the process to validate metadata. Issues occur when metadata settings are not correct and the validation process will tell you what is wrong.
2. Download application snapshots - the application snapshot should be downloaded for backup purposes. This is easy to automate with EPM Automate.
3. Download and truncate the audit logs. As the audit logs grow, they utilize space. Every now and then (the frequency will vary from customer to customer) these logs should be downloaded and then truncated.
4. Clear Empty Blocks - run this business rule to remove empty blocks, as they are using space when they're not needed. Running monthly or weekly is about right.
5. Dense Restructure - this is like defragmenting the application. The statistics will show you whether needed or not. If the value is close to 1, then running is not needed. If something like 0.001024, then definitely needed. Again, checking weekly is about right.

Doing these things should keep FCCS running quickly and smoothly.

FCCS - Calculations, FIX, and Restricted Dimensions

As FCCS now has configurable calculations, many people with HFM backgrounds, like myself, are learning Essbase calc script; the language used for these calculations. I haven't written an Essbase calc script before this month since 2002, so it's been a while. I'm not going to go through all of the details in one blog post (veteran Essbase consultants describe writing calc script as an art not a science) but I do want to point out one parallel for those with HFM backgrounds.

In HFM there are five dimensions that cannot be on the left side of a HS.Exp function, which is used to calculate almost everything. These dimensions are scenario, year, period, entity, and value. So we use If/Then statements to control when rules run for these dimensions.

In FCCS, one of the main functions in Essbase calc script is FIX/ENDFIX. FIX basically limits the members of dimensions to those members on which a calculation should run. The online help (link provided below) uses an example of fixing on a product and then doing a units sold calculation for that product.

There are five dimensions that cannot be used in FIX statements, however. These are scenario, year, period, view, and entity. Note that four of these are exactly the same as HFM. When a user starts a FCCS consolidation, they are using these dimensions as parameters for the consolidation and so the software is automatically fixing on these dimensions. And when you get past the FIX statement and into the actual calculation, these same five dimensions cannot be on the left hand side of the calculation, just like HS.Exp. So, just like you would do in HFM, us a IF/ENDIF to control when calculations run for these dimensions.

Here is the link to the FCCS help to learn more.

https://docs.oracle.com/en/cloud/saas/financial-consolidation-cloud/agfcc/configurable_calc_working_with_essbase.html

Have fun!

FCCS - Autonomous Consolidations

One of the newer additions to FCCS is autonomous consolidations. The typical user pattern since MicroControl days has been to input data and then run a consolidation. With this feature, the second part of that just happens. So, here's how to turn it on.

From the Navigator (hamburger) menu icon select Consolidation under the Application heading. From the vertical tabs on the left, click the second one for Autonomous Consolidation.

Above is the resulting screen. Use the POV to select the year, period, and scenario and then on the right click Turn On. When doing so, the following prompt will appear.

Click Confirm. Now the process is running, based on data input.

What to do for the next period? Come back to the screen and change the POV to the next period. After clicking Go, this prompt appears.

It would not surprise me if incrementing the period shows up as a close task or an EPM automate command in the future.

FCCS - First Look at Advanced Consolidations

The December 2017 update to FCCS will include advanced consolidation, meaning support for multiple types of ownership in the entity hierarchy. This is a first look at the function. For users of HFM and Enterprise, this will look very familiar.

First, when the update rolls out, it has to be enabled in Enable Features. This can be done without rebuilding the application.

There are two main screens that show the feature. The first is Manage Ownerships.

First, use the point of view to select the entity hierarchy, scenario, year, and period. Then you can adjust the ownership percentage, control (yes/no), and consolidation method (more on this below). This does allow organization by period (where an entity may roll into a parent one period but not the next).

The next relevant screen is Manage Methods (selected from the Actions dropdown above). Methods define different types of consolidations.

For each method, ownership ranges can be assigned to determine when a method should be assigned to a relationship. The methods can also be selected manually in the Manage Ownership screen above.

The calculations, target accounts, etc. for each method are seeded, meaning not changeable. This is expected to change soon, where customers can configure what happens.


Looks solid!

FCCS - Using a company logo

As I spend more and more time with FCCS I'm seeing where there have been lessons learned from the prior products. One of the simple sounding things to do that wasn't all that easy before is customizing the interface to use a company's logo. In the past with HFM, depending on the version, it usually involved replacing the system delivered files with custom ones and then remembering to replace them after each patch or upgrade. Now, with FCCS, it couldn't be easier.

After logging in, click the Tools card (yes, those icons or tiles or buttons are called cards) and then select Appearance.

The appearance window will appear. Specify a URL for the logo and/or background. The Theme refers to the color used for title bars and the like and Shape refers to the button style.

Here, we're just changing the logo image. There is a reset button if you want to restore to the defaults. After clicking OK, the image is brought in and you're done!

Member Selection in FCCS

For those who are switching from HFM (or Enterprise for that matter) to FCCS there are a lot of things that will seem familiar. You have journals, consolidation, hierarchies, Financial Reporting, FDMEE (lite), intercompany matching (and thank goodness it's with the Enterprise side by side format), and more.

One simple, more practical area where things are different is with member selection. This may seem trivial, but it is learning small things like this that make using the product easier to pick up. You focus more on what you're doing vs. wasting time on how to do it.

In Enterprise, member selection was a simple dialog box with a list or hierarchy of members, you select one, and click OK. Sorry, I don't have Enterprise up and running to show a screenshot.

In HFM, member selection is typically a dual sided dialog box with available choices on the left and the selected item(s) on the right and clicking the chevrons (not shown below) in the middle to add/delete or select a relationship.

In FCCS, and I'm pretty sure the same goes for EPBCS/PBCS as well, member selection works a little different. There are two main flavors.

This first example is from journals. There are tabs across the top for each dimension instead of the dropdown of HFM. Start from the left and click on parent members to drill down to the children in the next column - if you keep clicking, like on USA, the screen scrolls to the right to keep drilling down. But the question comes: how do you select a member? In the screenshot, there are three entities selected (gray boxes) - which one will be in the point of view when OK is clicked?

The answer is none of them! The selected entity is Latin America in the center column - note the checkmark to the left of the label. So, to select an entity, you must click the whitespace to the left of the member to place the checkmark. Clicking the entity itself does not place the checkmark. Think Smart View member selection, where you have to click the box and then click the chevron.

The other primary member selection box is seen in intercompany matching reports, where a relationship (think list in HFM and Enterprise) is needed. This screen has the same columns (in the middle and right) for the drill down as above and the same blue checkmark (see North America in the right column), but the additional bit shows in the LEFT column. 

This column shows a relationship selection. To do this, hover over a specific "anchor" member and the fx icon will appear. When clicked, the various relationships will appear. The equivalent of "base" members is "Level 0 Descendants," which highlights the Essbase underpinnings of the product.

Note that multiple items can be added to the left, whereas HFM typically would only allow one list/relationship to be added to the point of view.

Also, as seen in the bottom of the second screenshot above, this panel can be toggled from Members to other choices depending on the design of the application.

A huge benefit to all of this is you can do this from your phone/tablet. This screenshot was taken from an Apple iPhone using the standard Safari web browser.

Member selection is a little different, just like Enterprise to HFM was a little different but in the end you get what you need.