FCC - Loading data as admin not working (issue, workaround, and fix)

Some FCC applications have an issue where data loads done with an admin ID are not calculating correctly after the data load. What is happening is that the data status isn't correctly being set to impacted and so the subsequent calculation/consolidation doesn't bring forward opening balances, calculate movement, etc.

The workaround for this is simply to do the data loads with a non-admin ID. Or do something that impacts all status, like a database refresh.

Sources tell me that Oracle is planning (safe harbor statement applies) to do an off-cycle update to the test environments this week, probably on Jan 14. Once that happens, admins can test the fix in the test environment and then file an SR to request that their production environment gets an early update. Both the test and production updates would happen during the daily maintenance cycle, which can either be run on the normal schedule or on-demand using EPM Automate. Or, if the early update is not desired, then continue with either workaround until the regularly scheduled production update.

Update: Jan 14: The test pods were updated to 25.01.31 overnight from 25.01.30. Early testing is showing the fix to be in place.

Kscope24 Speakers' Awards

Kscope24 was a great conference this year. The content was good and the organization was almost perfect. There was even vegan food which was a huge bonus for me. After the conference the evaluations were reviewed and the annual speaker awards were announced. I'm honored to have made the list for the top close and consolidation speaker this year. Without getting into all of the details, I had a choice of two topics to present: FCC constant currency and EPM Automate job monitoring with a web page. I chose the FCC constant currency and that topic was well received by the attendees. But I must have talked fast as I finished and we had 10 minutes left in the session, so I gave everyone a crash course in the EPM Automate job monitoring. A few people enjoyed that topic more than the constant currency. I'm glad I was able to provide some value to everyone one way or the other.

Congratulations to all of the Kscope24 award winners. To see the full list, go to https://www.odtug.com/awards

And also put Kscope25 on the calendar: June 15-19, 2025. More details are available at https://www.odtug.com/odtug-kscope25

Happening Now: ODTUG Board of Directors Election

The ODTUG (Oracle Development & Technology User Group) FY2025 board of directors election opened up today. Members with paid memberships as of May 31, 2024 are eligible to vote. This year there are nine candidates running for two three-year board positions. The voting window ends on July 1, 2024 at 2:59AM US eastern daylight time. All eligible voters are encouraged to go to the site (link below) and vote.

This year I did something I haven't done before: I chose to run for the board. ODTUG started including the Hyperion products at Kscope about 12 years ago. Through this time I have benefited tremendously from the knowledge, contacts, and experiences from my interactions with ODTUG: Kscopes, webinars, etc. My primary goal as a board member would be to cultivate this sense of community learning and knowledge sharing throughout ODTUG. I believe that everyone can contribute to others, whether they are an experienced community leader or new to their profession. So I plan to meet my goal by guiding others to replenish positions of leadership and expertise and by attracting new members to share in and benefit from the same traditions.

Here's the link to vote for those eligible or if you just want to see who else is running. I encourage everyone who can to vote, and I ask that one of those votes be for me. Thanks!

Jun 19: This link was updated to match the new ODTUG website.

https://www.odtug.com/vote-for-the-2025-board

FCC - Showing Calc Status on a Report

With HFM and Financial Reporting, there is a function that pulls the calculation status into a report. This is useful for those who want to ensure the data has been consolidated when looking at the results. I've seen conditional formatting used in conjunction with this to alert the users that the data is suspect if the consolidation hasn't been ran.

With FCC and Reporting/Narrative Reporting, there isn't such a function. But there's still the same need. I filed an idea lab submission to request the function. I thought I (and my clients) was out of luck, but then I thankfully ran across a simple solution, and it's built in!

When building the report, look at account FCCS_CSTATUS FILTER. This account just doesn't give you some number that you have to convert - you get the proper text status. Along with specifying the account, you have to set the point of view to FCCS_No Data Source (which isn't used often) and FCCS_No Movement. The rest of the point of view should be normal for other data retrieves, but the intercompany and custom dimensions should be set to FCCS_No Intercompany or No custom.

When the report runs, it looks like this. Note this report is also showing the out of balance amount. The column headers for the data source and the movement are hidden. Not shown, the rows for the report are level 0 entities.

And being an account, this means that the calculation status can be pulled into Smart View with either ad hoc or functions, which is a big bonus.

When was the last data load?

I had a request recently for a simple way for users to know when the last data load occurred. They could go into process details or jobs, maybe, but was there a simple way to check from within a report, data grid, or even Smart View. There may be something better, but here's what I did.

I created an account called LL_DateTime. I originally was going to put the year in one account, month in another, etc. but I ended just using the one account.

Within data management (this part isn't in the new interface yet) I created a logic group to duplicate a record. I picked a retained earnings account for one of the primary legal entities so there would always be a data point to duplicate.

After assigning the logic group to the location, I used a SQL map to change the amount. The two SQL functions in use here are SUBSTR (get a subset of a string) and SYSTIMESTAMP (date and time on the server).

The SYSTIMESTAMP returns a format of 28-MAY-24 02.34.56.654321 PM -00:00  which isn't something we would be able to load as an amount. So SUBSTR is used to pull out pieces of this and to combine them into something useful. There are a few components that need mapping. The month of May needs to be converted to a number. The AM/PM indicator needs to be converted to a 24 hour clock (again, trying to load the date and time as one amount. There are a couple of different WHENs in use to direct the calc (if May, then 05; if PM, then add .12 [12 hours]). Admittedly there is probably a more elegant way of converting the month to a number without having to enumerate all of the options.

So, the format of the output looks like this: 20240528.1434. This is an easy number to load and report.

The last part of the output (00:00) indicates the timezone. In this case, it is UTC. This could be converted to a specific timezone but in the case of worldwide users, most people would have to convert anyway. It's better to just use UTC and let each user deal with summer time, etc.

The last part of the puzzle here was to create a simple data grid with all of the point of view members locked except for the year and period. This way users can check and see when the data was last loaded. They can also replicate the point of view and use Smart View, etc.

EPM Cloud Data Integration - Dealing with [Parent].[Child]

Another day, another solution to remember for later.

When moving data from one application to another, shared members can sometimes flow through the integration with [Parent].[Child] notation. If loading the child member to the target application and the target does not have the same hierarchy, then this won't work. Or, like I did today, when loading entity elimination data from FCC to Planning, then only the parent entity is needed.

A #FORMAT map can do some of this (and maybe all). #FORMAT can ignore specific positions, replace text, etc. Here's the doc link. But there are limitations to #FORMAT and the need for SQL scripting comes into play.

https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/erpia/format_mask_mapping_for_target_values.html 

With SQL scripting, here's what to do. There are two main SQL functions to use: SUBSTR and REGEXP_INSTR. SUBSTR returns a subset of a string. REGEXP_INSTR searches for a character and can return different things, mainly the position of the character.

In the particular use case shown here, only the Parent portion was needed, so omit the first [ and then omit everything after the first ]. Here's the SQL using the two functions. With SUBSTR, we start with the string in the field ENTITY, start with position 2, and then use REGEXP_INSTR to know how far to go. This function is searching for the first ] and returning its position within the string. That result is the last parameter for the SUBSTR function (how far to go) but we need to subtract 2 from the result. Subtracting 1 since the first character is being omitted and subtracting another 1 as the ]  - the character being searched - isn't needed.

SUBSTR(ENTITY,2,REGEXP_INSTR(ENTITY,'[]]+', 1,1,0)-2)

Since I'm using a wildcard map to process all incoming records, I also wanted to flag members that come through without the [parent].[child] format, so I used SUBSTR again to check the first character. If [ then use the above; otherwise, flag that there is a new entity without [parent].[child] (which would mean the member is not shared) and should be dealt with differently, which could be an explicit map, passthrough, etc.

The ending result looks like this (I also added a prefix to the parent so the result looks like ELIM_parent). The lines starting with -- are comments.

This use case is focusing on isolating the parent. If just the child is needed, change the parameters for SUBSTR and REGEXP_INSTR, respectively, to focus on the end of the record without the last ] on the end and search for the position of the second [ in the string.

FCC - Using Entities that are Shared in Insertion Rules

When working with insertion rules, sometimes there is a need to calculate something only for specific entities. But, if that entity is shared - in other words, exists in multiple hierarchies or has multiple owners - there can be problems. Here's how to deal with this common problem.

The need most arises with IF statements. The code usually looks something like this:

IF(@ISMBR("331"))

   do some calcs

ENDIF;

This code will pass validation and deploy successfully, but when trying to consolidate, an error is generated.

Clicking the link and drilling into the error message will show this:

Unable to resolve duplicate member name ["331"] on line ____

The problem is the rule doesn't know whether to run for [ParentA].[331], [ParentB].[331], etc. One workaround would be to write the IF statement like this:

IF(@ISMBR("[ParentA].[331]"))

But if the entity gets moved, then that creates another problem.

The solution is to use the @EQUAL function. @EQUAL searches the outline for the specified string within a specified dimension. The result looks like this:

IF(@ISMBR(@EQUAL("331","Entity")))

If an additional entity existed like 331abc that should not be included, then add an additional condition to the IF statement to exclude that entity.