Oracle AI World Hack: Security

Oracle AI World is a huge conference with several thousand attendees or more. I’ve been to several and notice people making the same mistakes in navigating the conference. Sharing what makes the conference work for me with the hope that others can make the conference work for them.

Keep in mind the conference is set up to manage the attendees as a whole. You have to do what you need to make it work for you and get the most out of it.

One of the easiest things to make your life easier is getting in. Security scans bags and people walk through a scanner. And rightly so in these days. Sometimes the mass of people going through security can be overwhelming, particularly in the morning when everyone is showing up. People usually go to the main level security entrance by the registration area because that’s what they’ve seen and it’s where the mass of people go.

But what people fail to realize is there is more than one security entrance. Go up a level to the canal shops area and there is a second security entrance. Here’s a picture (taken Sunday afternoon). If you’re on the main level take the escalator up that is on top of Starbucks and you’re there.

I went in this way Monday morning and there were security people there but NO ONE else was going in!

It gets you to the same area - after passing through without the throngs of people around you, walk down the hall and simply take the escalator down and you’re on the main level.

Oracle AI World is large and full of waits, cramped spaces, etc but at least you can start the day off relaxed.

Oracle Cloud EPM - Financial Reporting finally being terminated

After multiple years of notice, Oracle has announced the end of the Financial Reporting report module in the Oracle Cloud EPM products.

Dave Roberts (Oracle product manager) shared the announcement to remove Financial Reporting. All of the Financial Reporting references, user interfaces, and content will be removed in the 27.02 (February 2027) release. This will include the repository (where the reports are stored) and FR studio (the report editor/builder). So these Navigator links and the Financial Reports tab in the Reports screen will be gone.

The worthy successor to Financial Reporting, simply called Reports, has been in the Cloud EPM suite since 2021 and has built on and exceeded the capabilities of Financial Reporting in many ways. When I worked at Hyperion I saw beta versions of Financial Reporting starting a year before it was released and saw how it out-performed the previous report writer (Hyperion Enterprise Reporting). Now it's time for Financial Reporting to move on as well.

There were a few clarifications in the announcement where Financial Reporting is not quite disappearing yet. This sunsetting does not impact Fusion Cloud ERP, legacy Profitability and Cost Management, and the Hyperion on-premise solutions. There are no plans to move Reports to on-premise and my guess is that Fusion and PCMCS will eventually get Reports.

The Reports module has been in the suite since 2021 and is a great successor. If EPM customers haven't been using it and are still using Financial Reporting, this announcement should be enough of a reason to finally migrate. For details on how to do the migration, refer to my blog post from February 2025 - link provided here: https://ericerikson.blogspot.com/2025/02/financial-reporting-another-nail-in.html

And for Oracle documentation on the differences between Reports and Financial Reporting and the migration, go to this link: https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/dmepr/appendix_b_migration_of_reports_from_financial_reporting.html

Oracle Smart View - When and How to Update

Oracle Smart View is an add-in for Microsoft Office and is used across EPM and with Cloud ERP. There are also several extensions that add features and functions to the add-in. Smart View gets updated usually twice a year and the extensions less frequently. This post talks about how EPM admins can check if users have older Smart View versions and how users can check for and then update the extensions themselves.

You might be wondering about why not talk about updating Smart View itself. In many organizations users do not have security access to install or update Smart View and/or installations are centrally managed and pushed out as needed but users can usually do the extensions. But basically to update Smart View itself, download and unzip the new file and then run the executable within.

Note this process is not as seamless as the Oracle Update Advisor for Oracle AI Database (which sounds pretty awesome). Then again, OUA is geared for a maximum availability database architecture, so way beyond the tech level of Smart View. Check out these posts from Oracle for more information.

https://blogs.oracle.com/maa/announcing-oracle-update-advisor

https://blogs.oracle.com/maa/from-green-lights-to-gold-tiers-maa-oracleupdateadvisor

Admins

Admins can use the Activity Report within each application to monitor the Smart View versions in use. Specifically in FCC, use the Navigator icon to go to Overview (in the Application section) and then click Activity Report. Click one of the available options and then scroll down to the Usage section at the bottom. The report shows which versions are in use for browsers, Smart View, Excel, and EPM Automate. There is also a section not shown below that will report specific users that are on much older versions. Version 25.100 shown below is only one version behind the current version (25.200) so no specific user report was warranted.

Note that the activity report does not report on the extensions and the versions of the extensions in use. This will need to be done by the users.

Users

Cloud ERP and EPM users themselves can check their own Smart View version by using the Help > About menu item on the Smart View ribbon.

The extensions can initially be downloaded by users directly from within Oracle Cloud EPM. Click the user ID at the right and select Downloads... The available downloads will be listed. Other then Smart View itself, click the Download button and then double-click the downloaded file in order to install the extension. Users are typically able to install extensions but not Smart View itself. And note that downloading Smart View itself must be done from the Oracle Technology Network which requires an Oracle ID that users typically do not have.

To check for updates for installed extensions, users can select the Options button on the Smart View ribbon and then click Extensions on the left side of the resulting window. Click the blue "Check for Updates, New Installs, and Uninstalls" link at the bottom. After selecting the desired server, the software will provide the status for each installed extension (below, the top three have updates available). Click "Update Available" in order to update the extension.

When to Update

The main Smart View add-in is usually updated twice a year. For the extensions, there is an option to check for updates every time that Office starts (see above screenshot at the bottom) but the extensions are not updated frequently enough to warrant checking that frequently. Also, the extension updates will be reported in the monthly update summary.

Oracle FCC: Which Metadata Editor is Best for Catching Errors?

Those who have worked with Oracle EPM for several years know there is usually more than one way to do things (although we consultants sometimes think that "our" way of doing something is the correct way!). With Oracle FCC and Planning there are at least five different ways of editing metadata (entities, accounts, etc.). These options have varying levels of edit checks available, at least for FCC. This post will talk about the five with a particular focus on edit checks. There is no one "right" way here, but there are "better" and "not as good" options. 

Option 1 - "simplified" or "new" metadata editor

This option is built into the software and is accessed by selecting Navigator icon > Overview (from Application menu section) > Dimensions > select dimension. This option presents the metadata in a grid format where parents can be expanded/collapsed, columns moved around, valid choices presented as dropdowns, etc. Attributes can be copied and pasted across multiple members which is helpful.

There are pros and cons related to edit checks. If you try entering an alias with more than 80 characters, you are allowed to do so but you can't save [thanks to Jake Turrell for the info]. Kinda good (can't save) but kinda bad (allowed to type the extra characters). But where this editor shines with FCC are the visual attribute edits. Here, Average is not a valid exchange rate type for FCC and the software is adding the red border to indicate that. This change was made in the old editor (option 2) and in the new editor, Average isn't even a choice. But it still shows the field as something that needs to be corrected.

Disclosure: this is my default choice out of the options presented but will use another option below when needed, so keep reading.

Option 2 - "classic" or "old" metadata editor

The classic/old Essbase metadata editor is accessed by selecting Navigator icon > Dimensions (from Create and Manage menu section) > select dimension. This option was the original metadata editor in FCC and has been available for several years after Oracle said they were going to remove it (when the "new" one came out). I think there are larger priorities and if people are used to using this editor with earlier versions or with Essbase or with Planning then why not. 

The metadata is presented as a hierarchy but each member must be edited individually: no copying/pasting to multiple cells like in option 1.

However, as far as editing goes, it does lack some features. It will correctly not allow an alias to be entered that's greater than 80 characters [again, thanks to Jake]. But it will allow for invalid attribute choices to be selected. They may be valid for Essbase and/or Planning, but not everything is valid for FCC. Here, the Average option is not valid for FCC but can be selected and saved and there is no notification that this will be a problem.

Option 3 - Oracle Smart View

Smart View can also be used to edit metadata directly in Excel. This option does have some ease of use: built in extract/submit buttons, cut/copy/paste, Excel features like AutoFilter and use of functions to build metadata components, etc. But it lacks the edit checks as shown above.

I do use this one frequently when doing initial metadata building. It's commonplace to begin a project where someone gives me an Excel file with the chart of accounts, legal entity hierarchy, etc. 

Option 4 - Export to text file, make changes, upload

This option is probably the simplest in theory, but the hardest to make work successfully. Export the metadata to a text or CSV file, manually make the changes, and load back in. With this option there are no edit checks and lots of room to create errors (like leave out a comma [delimiter]) but technically it does work.

Option 5 - Oracle Enterprise Data Management

Why worry about edit checks if you can eliminate the errors altogether? Enterprise Data Management is a part of the Oracle Cloud EPM suite that adds governance, rules, workflow, and other features around metadata management. Each target application can have the edit checks around what's valid and not valid set up. This is usually an additional cost (unless you already have Enterprise licensing for Oracle Cloud EPM and can work with less than 5,000 members) and does involve a project to implement but there are a lot of benefits that none of the other options can match.

These are five of the common ways to work with FCC metadata. All of these are valid approaches although the manual editing with the text file is probably the least likely to be used. If administrators are still working with option 2 (the old editor) then some serious consideration should be given to switch to option 1 (the new editor). The grid format allows editing multiple members at the same time and the visual attribute edits (the red borders) are two features that the old editor simply can't match.

Oracle EPM Reporting - Trick for Period Reporting

A fellow consultant reached out for help on solving a problem with building a report with a specific requirement regarding periods in the columns. Like many things here on the blog, I'm adding the solution here for everyone (including me) to reference and find in the future.

The problem involves reporting a range of periods. The customer wanted to select a period in the point of view and then show the remaining periods in the quarter (not the year, which would be a simple RANGE function). So (assuming a calendar year setup) if the user selects January then show February and March and if the user selects February then only show March. With Essbase calculation scripting there are functions called LSIBLINGS and RSIBLINGS that can do this (show siblings before and after the current member). But in reporting, either within Narrative Reporting or within Reporting within the applications, there are no such functions.

My initial thought was to specify all the periods in the columns and then apply a bunch of conditional suppresses. It would work but I knew there was a better way, so I reached out to super star Opal Alapat for help. 

So, here's what to do: use the INTERSECT function. The function returns all members that meet all of a list of specified criteria. The issue is figuring out which criteria to use where the combination yields the desired result.

For this particular problem, there are two criteria to specify. First, use the SIBLINGS function to return all of the siblings of the current point of view period (which will be user specified). If a user selects January, this function will return February and March (which coincidentally meets the requirement). But if the user selects February, the function returns January and March (which doesn't work).

The second criteria filters out the prior periods. Use the RANGE function to show periods from the current point of view to December (or the last period if a non-calendar application). So if the user point of view is February, then this function will return all months February to December.

This is where the magic of the INTERSECT function comes in. The function looks at the two criteria and returns only the members that are in all the criteria sets. Here's what it looks like in the member selector and after returning to the grid.

And after clicking OK, the function shows this way in the report grid.

The solution works well: both easy to set up and understand. And avoids the hassle of creating a bunch of conditional suppresses.

Bonus: If there is a need to report months before the current point of view in the quarter, then change the RANGE to go from January (or the first period) to the current point of view.

Change to EPM Software Release Cycle

 Oracle recently made an important announcement for all EPM environments that all administrators need to know.

First, some history, then what's happening, and then the consequences.

In the fall of 2025 the Essbase version in some products was upgraded to 21c. There were some product issues in the October 2025 and there was some communication from a group within Oracle advising customers to skip the updates. Probably more for the confusion as much as product issues, Oracle skipped the November and December 2025 releases and the January 2026 release (which usually doesn't have much anyway as it happens during year end close for the majority of customers).

Now, after further review and I'm guessing out of an abundance of caution, Oracle is skipping the February and March 2026 updates as well. The next scheduled release will be the April 2026 release. If following the normal cadence, the test environments will be updated on April 3 and the production environments on April 17.

So, consequences. The EPM Automate skipupdate command doesn't need to be used to skip these periods like was required for the October 2025 release. But, once April comes the environments will not have been updated in more than three months, so the skipupdate command won't work then either. To skip the April update (quarter end close for a majority of customers) an SR is required and Oracle will have to do it. 

Customers will need to make a choice regarding the test environments. Currently, the test environment is most likely either the same version as production or one version ahead, meaning that an artifact snapshot (application backup) can be moved back and forth. If both production and test are not updated in April, then that will continue but the new version won't be available for testing. If the normal update cadence described above is followed, then the test environment won't be able to import the production snapshot during the two week testing window (more than one version different).

Recommendations. If you want to test the new version in the test environment, do a fresh clone from production to test right before the update gets applied to test (like on April 2). Also consider creating an extra environment so the current test environment can skip the update (if needed to support production and/or migration policies) and the new test environment can get updated (best of both worlds).

Oracle really doesn't love acronyms

This post is a little off topic and definitely not technical. There is something that bugs (software pun intended) me about how many people refer to Oracle's products, including Oracle employees. And this issue isn't related to just Oracle; I see this everywhere, both in tech and non-tech.

Particularly with the Oracle Enterprise Performance Management products with which I work, there are a lot of abbreviations: EPM, ARCS, FCC, etc. And elsewhere in Oracle there are terms like AI, OCI, ERP, and many, many others. You're probably thinking "Yes, yes, we know all these acronyms and use them all the time. So what?"

Well, here's the issue: most of these are NOT acronyms. Really. They are actually initialisms. The difference between an acronym and an initialism is how one pronounces the grouping of letters. If the letters are spelled out individually, like EPM, AI, OCI, etc, then it's an initialism. If the letters are pronounced all together as a word, like ARCS (pronounced as "arcs") then it's an acronym.

If you don't believe me, look it up: here's one link.

https://www.grammarly.com/commonly-confused-words/acronym-vs-initialism

Epilogue: out of curiosity, I checked a free version of ChatGPT to see if their AI had any self-awareness. Looks like not yet.

Update: same happens with Google Gemini.

Oracle Cloud EPM - Renaming Stuff

Like most people I'm learning new things in Oracle Cloud EPM all the time. Many items (probably like this one) are most likely things that others have known for years and take for granted. Anyway, here is something I recently ran across that's a followup to my February 2025 post about migrating away from Financial Reporting.

Once all of the migration work is done for the reports, users should go to the Reports tab within the Reports card and not the Financial Reporting tab which unfortunately still shows.

The Financial Reports tab will be there until Oracle takes it away. Having it there may be a temptation for users to keep going there out of habit. We could delete the reports or change security to them all, but if they're needed during the first cutover close, either of those options would slow things down. So, here's an idea.

There's a menu item under the Tools cluster called Artifact Labels. This screen allows renaming practically anything. Well, at least within the web interface as the renaming occurs by browser language. The idea is for language translations or making something user friendly if the real label or name is cryptic.

So, go to Tools > Artifact Labels and click the Filter icon. From the extensive list (and you'll see you can rename just about everything), select Tab. Use the Add Language dropdown to select the desired browser language. Then scroll down to the Financial Reports line and enter the desired text and click Save.

When renaming a navigation flow item like this, closing the browser and logging back in may be necessary. But now, when a user goes into the Reports card, they see this.

The tab still works and the reports are all still there and work but hopefully the users get the hint.

For more extensive changes, create a line item like above and then export to CSV, make more changes, and then import as shown above with the Actions dropdown.

I'm thinking all of these renames could get out of hand but when used thoughtfully this is a great hidden gem of a feature.

FCC Consolidation Status - "Completed" does NOT mean Successful

UPDATE - September 2025 - this is looking to be fixed. A consolidation with this error now shows Error on the main jobs screen.

FCC has a similarity to Microsoft Excel on manual calc. Change data, then press F9 for the calculations/formulas to run. In FCC, the similar process is consolidation, just like it was in its predecessors MicroControl, Hyperion/Enterprise, and HFM. This process is central to how all four products work. Unlike those great products of the past, however, FCC consolidations have one big deficiency: the status reporting. Did the consolidation work or not? Without going to multiple screens, you don't always know.

Look at these two consolidation results from the Jobs listing. One of them failed and one succeeded. Can you tell which is which?

Looking at the details of one of the consolidations, can you tell now? No, you can't.

Actually, this consolidation failed. Clicking the word "Completed" shows that the consolidation didn't even run.

It could be that "Completed," internally, means someone started the process, the process started running the software code that Oracle told it to do, and the code reached the end of the process. Whether the process actually encountered an issue during that; well, that's up to you the user/admin to figure out. In my mind, this is like saying whether I have eaten. I start the process of eating: something goes in mouth, I chew, I swallow. Ok, I ate something. Did I like it, did I hate it, did I get sick, did I eat something poisonous and die, am I still hungry: who knows.

I posted an enhancement idea on Oracle Cloud Customer Connect on this over four years ago. Maybe everyone is okay with this level (I want to use other words here but am trying really hard not to) of status reporting. I'm thinking that Oracle can do better. If you want to upvote the idea - please do -  here's the link:

https://community.oracle.com/customerconnect/discussion/558521/consolidation-jobs-should-indicate-error-not-just-complete

Since FCC has been reporting consolidation status this way for so long, you'd think everyone would know about it. That's not true - I just had this conversation with a client today. The issue appeared in downstream processes: reporting was weird, data wasn't correct, etc. So if your consolidations run but you don't get the results you expect, unfortunately a little digging is required.

Financial Reporting - Another Nail in the Coffin

 Logging into a test EPM environment after the 25.02 release (February 2025), I saw this message.

Oracle announced in the release notes that support for Financial Reporting in EPM is ending in June 2025 and this message is a confirmation. As noted above, this transition has been planned for some time now.

Fortunately, it's not a hard process to convert existing Financial Reporting reports to Reporting. But before trying to migrate reports, check first that they run and have no errors (if a report doesn't work now, it's not going to work later). Also check with the users about which reports need migrating.

Click the Navigator and go to Reports. On the Financial Reports tab where the FR reports are listed, there is an icon on the far right for each report for migration. Click the icon to begin the migration.

When prompted, click on the desired folder for the new report. If the folder isn't there, then cancel, create the folder on the Reports tab, and then run the migration.

After the process runs, a confirmation message is shown.

There is also a way to migrate all of the reports at once. Click the Migrate All button at the top right. Choose the top level destination folder and optionally select whether to recreate the existing folder structure.

The status of the process can be seen in the Jobs screen.

A common sense approach would be to migrate everything, deal with any issues (see below), and then migrate individual reports again if they require updating before the conversion is complete.

This process seems too easy, right? The documentation - link below - has two pages on (a) dealing with migration errors and (b) the differences between the two reporting tools. Depending on the report design and build, there may significant work still to do.

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

So the next step, even if no errors are reported, is to edit each report and check for validation errors (open the report in edit mode and see if the red bar shows at the top of the screen) and verify the output with three objectives: does the report run, is the content correct, and is the formatting correct. 

For example, open a report in edit mode and you may see something like this:

When I've done the migration, I've noticed that the report/folder security within Financial Reporting is not copied over. I can't say whether this is normal, but also plan to verify and apply security as needed.

Last, the Financial Reporting books do not migrate. These will need to be recreated.

Admins should start on this migration soon if they haven't already. When Oracle refers to the "June 2025 (25.06) update" above, remember that does not mean the end of June - it's the third Friday in June. 

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.

FCC Constant Currency

An occasional requirement for an FCC implementation is to translate data at multiple currency rates. There's always actuals at the actual rates, but then you can translate actuals at last year's rate, actuals at the forecast rate, actuals at next year's budget rate, etc. These different translations provide the data to show whether operations performed well against the comparative data or did the exchange rate just work in their favor.

There are a couple of different ways to set this up. The first question is whether to use a scenario or a custom dimension to hold the translation versions. If the requirement includes the balance sheet, is used occasionally, or the customs are already used, then one or more scenarios is the recommended direction. In the case I'm showing here none of those apply, so this example will use a custom dimension. The requirement is for income statement only, the output is used extensively during close and subsequent reporting, and a custom dimension is available.

After creating additional members in the custom dimension to hold the results, the next step is to create a custom translation rule (as shown below). The top half of the rule shows where to source the exchange rates. In this case, the rates are sourced from scenario Actual for the prior year. The "current period" is specified, so Dec 23 actuals will translate at the Dec 22 exchange rates, Nov 23 at Nov 22, etc. The second half of the screen controls the source and destination members. The left side shows the source data: no constant rate (custom), descendant accounts of net income, and only applies to the actual scenario. On the right hand side is any redirection (target) members. In this case, the results are stored in LastYrRate (so No ConstantRate is translated and then stored in LastYrRate). The account and scenario dimensions do not need redirection, so those fields are left blank so that those members simply pass through.

Once the rule is created it just needs to be deployed to be in effect.

So that will take care of the translations. But if the analysis will include operations from the total company's currency, what to do there? Example: if total company is in US Dollars and there are US Dollar entities that roll up to the total with only US Dollar parents, then there is no translation. As there is no translation, the above translation rule will not include that data. This can be addressed by adding an insertion rule to simply copy the data over from No ConstantRate to LastYrRate. Note that the FIX statement should specify level 0 members of the intercompany and other custom dimensions.

One note on the @ISUDA function used. IsUSD is a UDA that is applied to the level 0 entities that have the "home" currency. Apparently there is a function that can read the entity currency directly but it isn't visible as of now in Calculation Manager. Oracle has filed an internal SR to expose the function which will remove the requirement to tag the entities with a separate UDA like this.

The custom translation and insertion rule will run with each consolidation. After consolidating, the users can simply switch their POV in reports, Smart View, etc. between the custom members No ConstantRate (for actual numbers) and LastYrRate (for actual numbers at last year's rates).

Data Exchange - DRCRSplit for Delimited Files

Recently had to solve a problem during an implementation and as I've done before, I'm adding the solution here so I can find it again.

An incoming trial balance file is delimited and has two columns for the amount: a debit column and a credit column. Each record will have a value in one of these but not both. If the file was fixed format instead of delimited, there is a built-in import format expression called DRCRSplit that will deal with the two columns. With the function, you specify the midpoint of the overall character count but with delimited files you don't have a consistent midpoint character count, so it doesn't work with delimited files.

Like maybe some of you reading this, I did a Google search on this topic and found multiple Oracle forum posts, CloudCustomerConnect posts, etc. asking the same question over several years but didn't find an easy solution. There was a posted answer that combined the DRCRSplit function with the Column function, but as pointed out by someone else that solution causes data problems. So, below is what I did. There may be a more elegant way of solving this problem, but the solution worked for me.

I mapped the debit and credit balance columns to separate attribute fields and for the regular amount field, I mapped the account field to it. Each record needs a numeric amount; otherwise, it will get dropped on the import. I'm thinking the year could be used as the placeholder amount if it were available on each record. I'm guessing that one of the amount fields would probably work in conjunction with the no zero suppress expression.

[Editor note: if the right sidebar is covering part of the picture, click it to see the full detail]

Next, I used a SQL map on the entity to (a) pass the entity through and (b) to populate the amount. Below, the entity portion isn't shown. The AMOUNTX field (the target amount) is populated from the debit column if it is not equal to zero and from the credit column if the debit is equal to zero. Anything placed in an attribute field is stored as a string, so the TO_NUMBER function converts the string back to a number. If there was a need to deal with quotes, spaces, commas, etc. in the number as thousands separators or decimals, that could be addressed with either a format on TO_NUMBER or using one of the REGEXP functions.

I attached this SQL to the entity dimension mapping. If the entity dimension needs more mapping and the SQL gets in the way of using the other mapping types (explicits, etc.) then attach the SQL to another dimension, like Data Source. Any of them will work, so the SQL should be placed somewhere inconspicuous.

Cloud Customer Connect - the Importance of Upvoting Ideas

The #1 best place to go for Oracle Cloud EPM info (yes, better than this blog) is Cloud Customer Connect. It is part community forum, Oracle training, announcements and guidance from Oracle product managers, and the ONE way to submit enhancements or customer requests for new features, etc. This last component is the focus for this post.

Idea Lab is where everyone can go in and submit an enhancement request and explain why, the business case, etc. Others can review and upvote to indicate their interest in the same thing or downvote if they don't see the need. When I say "others can review," that includes Oracle product managers, who review each idea and, once a direction has been decided by Oracle, will tag for future inclusion, deny, or say the idea is already in the product.

The upvoting portion of the process is the most important for the community and is probably the least underutilized portion of the process. This is the mechanism for the community to review and indicate their interest in the ideas. Ideas that receive a lot of upvotes are considered more seriously by Oracle as they have more demonstrated appeal.

Here's an example. In a navigation flow, you can create a new card to Narrative Reporting report, package, snapshot, or book (see below). All that is great. But what would be much more useful would be to create a card that links to a Narrative Reporting folder. That way, for instance, with one click on a card in FCC, users would be able to access all of the FCC reports. With the current ability, the admin would have to create a card for each one. If there are 50, then that would be 50 cards. Add a new report, add a new card. This does not make sense!

In Idea Lab, there is an idea to add a folder to the above list. Here's the link (login with free Cloud Customer Connect account required):

https://community.oracle.com/customerconnect/discussion/652748/add-narrative-reporting-folder-to-navigation-flow

Down near the bottom is the place to upvote/downvote. To do so, click the "thumbs up" or "thumbs down" icons. Maybe this navigation, which isn't exactly obvious, is the reason there isn't much up/down voting. Once the choice is clicked, it will fill in (here, I have already upvoted this idea). Users can also add comments to explain their support, add additional business cases, add workarounds, etc.

I think that upvoting ideas is highly underutilized by the community. By upvoting ideas that make sense to them, users can add their influence to the idea which will increase the appeal of the idea to Oracle.

So, get out and vote!