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.

Using EPM Reporting Books with Smart View - Part 1

One of the Oracle EPM product features that has been requested for decades - yes, decades - is to bring a report book into Microsoft Excel. With the April 2022 update this can FINALLY be done. Granted, the report book has to be the new Reporting book and not the old Financial Reporting book. But think of it as one more reason to migrate from Financial Reporting to Reporting.

This new feature works for FCC, Planning, Tax Reporting, and Narrative Reporting.

Using the feature requires enabling a new Smart View extension that is being released with the April 2022 update. This blog post will cover how to install the extension. A future blog post will cover how to use it.

To install the extension, go into Microsoft Excel and go to the Smart View ribbon. Click Options and then go to the Extensions panel.

The screen above shows EPM Books already listed but your screen will not. Click the blue "Check for Updates, New Installs, and Uninstalls" link at the bottom. When prompted, select the appropriate environment. Here, I'm using a FCC test environment.

Once the update is done, an item for EPM Books will be shown. Click Install on the right to install the extension. Note that administrator level access to the computer should not be needed for this. 

Once the installation is done, quit Microsoft Excel (the installation process will remind you) and restart. Now, use the Smart View panel to connect to the application. Once the application ID is clicked upon, a new link will show for launching EPM Books. Click More>> if the link does not show.

The next blog post will go through how to use the Smart View extension to bring books into Microsoft Excel.

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!

EPM Automate - System Status Website

As companies implement Oracle EPM Cloud components (FCC, Planning, ARC, etc.), EPM Automate is almost always a part of the process. Typically with data loads and backups, which all of the components need, but also restructures, database refreshes, and more. Along with the growth in these scripts is the need to communicate to users and admins the status of everything. Each script can send an email or a text with the status of each run. But with hourly data loads, backups, restructures, regression tests, etc. the number of emails can easily become overwhelming. An email or text should be sent if there are problems, but if things are going well, is a deluge of emails really the right solution?

As an alternative, a simple webpage can do the trick. Since EPM Automate typically runs on a server, the server can also serve up a webpage with the status of each job. 

The basic steps are to have each script write the status to a web page. Then use an overall web page that incorporates all of the individual status pages. Users can access the one overall page to see where everything is in real time.

For more detailed information and step by step instructions, please refer to the Amazon e-book linked here and to the right if viewing this on a computer. 

https://amzn.to/3wHsNAf      Clickable version: https://amzn.to/3wHsNAf

Kscope22 - June 19-23

ODTUG's widely well-regarded conference Kscope is back! Scheduled for June 19-23 in Dallas, TX, it promises to be informative, with sessions on tips and tricks, customer success stories, hands-on training sessions, and more. And the Oracle session on Sunday is always helpful and provides great info to start the week off.

The conference is being held at the Gaylord which is really, really close to the airport (DFW). Looks like a great property.

Go to https://kscope22.odtug.com for more information and to register. The early bird pricing ends on March 31, so register soon!

Pro tip #1 - there are two prices - one for ODTUG members and one for non-ODTUG members. Become a member for $99 and save $150 on the registration. Not only are you saving some money but you also get the full benefits of being an ODTUG member. 

Pro tip #2 - to save ANOTHER $100, use discount code KEYTCH22. The registration process will prompt for this at the beginning so don't miss it. Disclosure: I do not benefit in any way from this code - just passing it along. 

Hope to see everyone there!

Reporting - Book POVs and How Different than Financial Reporting

The replacement to Financial Reporting (FR), called Reporting, has been in the Oracle EPM cloud environments for a while now. For the most part, everything I've known in FR has worked about the same. Different navigation or UI, maybe, but all of the same concepts. I'm also glad there are improvements.

With books, one of the common things to do in FR is to set a book POV. So if all of the reports should run for the children of a specific entity, you could simply manage that at the book level. So when I went to Reporting to create a similar book, I tried the same thing. As you might guess, it didn't work. After I got some guidance from Oracle, this blog post shows what it does do, how to do the FR equivalent, and why what to do now is better than FR.

When you create a book in Reporting, there is a book level POV. If you leave it at Default (which means the user's POV) or just select a single member, it works just like the book POV in FR. But if you use a function or select multiple members, like the Entity dimension below, it's different. Instead of running the report(s) for all selected members, it just limits the POV to the selected items. Users view one at a time and do not get a PDF with all of the members.

You can use the options on the flyout menu to control the display but that's about it.

So, what do we do? The key is to use a section. Sections are grouping of reports, other docs, and/or other sections. If you think about an outline, they are the first (or more) top level(s) before you get to the report/doc at the bottom level. They do help with managing the groupings better than FR, which only allowed for collating by member or report. You can see the improved management of it all in a table of contents. Speaking of the table of contents, sections allow for better control over the book layout. Want a cover page before the table of contents? In FR, you couldn't do that unless you had a hardcoded table of contents PDF or Word doc where the page numbers were not dynamic. In Reporting, with sections, it's no problem.

When adding a section, a name field allows for a description to be placed and the dimensions that need a section POV can be selected. These should match what is needed for the report(s).

With the section in place, add the report(s) and then edit the section POV with the desired multiple members. Now when the book runs, the report will run for all of the entities in the section POV, essentially meeting the FR book POV functionality.

At the bottom of the screen there is a section for the TOC headings. For both the reports/documents and the sections, the headings can be adjusted between the artifact (report or document) name, the dimension label, or the dimension alias.

If you have more than one section, then yes, you'll need to manage each section's POV if there is a change. But this is a small and easy trade for the improved control over the book page order, table of contents format, etc.

EPM Automate - DOS Commands for Checking Available Disk Space

I recently answered this question on Oracle Cloud Customer Connect [you are signed up for CCC, yes?] and wanted to share the answer here.

A user is setting up EPM Automate to do snapshot downloads. There is a need to check the available disk space and then delete prior backups as necessary. The question was how do you check available disk space. Here are the steps.

The easiest way to find the available disk space is with the DIR command. The output can look something like this.

The output starts with the word Volume and can go for any number of lines based on the contents. The number to capture is 25,998,811,136, but without the separators and as a variable so that a comparison can be made. 

First, use the DIR command with a redirect to write the output above to a file. Use the /-C parameter to remove the separators.

DIR /-C > file.txt

The output of this will look like above but without the separators. Next, isolate the bytes free line vs all of the others with a FIND command. Search for bytes free. Redirect to another file.

FIND "bytes free" file.txt > line.txt

The output of this will look like the last line above. Next, use the FOR command to find the right part of the line. The parameters allow specifying a delimiter, but if nothing is specified then a space will be used as a delimiter. Note, if the below command is used in a batch file, use %%I as shown. If typing this in manually to a command window, use %I instead.

FOR /F "TOKENS=3" %%I IN (LINE.TXT) DO (SET FREESPACE=%%I)

So now we have a variable with a number in it. Is this useful? Maybe, maybe not. The next step would be to subtract a predetermined target amount from the available space to see if the available space is above or below the target. The SET command with the /A parameter will do this. But, it is limited to 32-bit numbers, meaning it doesn't work for numbers larger than 2,147,483,647.

So, a little more work needs to be done. Assuming the target is 10GB (10,000,000,000) we can reduce everything to GB instead of working with the full number. Use the SET command to pull out just the wanted part of the number (GBs only) into a new variable.

SET FREESPACEGB=%FREESPACE:~0,-9%

Now all that is left is a variable with just the GB value: in this case, 25. Then it would be an easy comparison to compare to our 10GB target. In the case that available disk space is under 1GB, then the above truncation may cause an issue. Doing the comparison in MB instead would get around that. Change the -9 above to -6 and then compare the output to 10000 instead of 10.