EPM Automate: Using GetSubstVar with DOS Batch File

Summary: this post is about using the EPM Automate GetSubstVar command within a DOS batch script and then transforming the output into a usable variable.

With most every FCC implementation there are multiple EPM Automate processes. One of these is typically to load and consolidate the close period. A discussion that typically comes up is how to control the period and year for which the process is running.

Over the years I've preferred using a file on the EPM Automate server. The admin would set the period and year there (either within a file or just read the name of a file). There are other controls I like putting here, like to skip the process, to load or not to load exchange rates, etc.

There is a way to use the substitution variables within the application if this approach isn't desired. There is an EPM Automate command that gets substitution variables: GetSubstVar. 

First, log into the environment and use the getsubstvar command to get the variable. The parameters let you get variables from the application level or the cube level. You can also get all of the variables at those levels or just a specific one. Here, these commands are getting specific variables from the Consol cube and writing the output to text files.

call "c:\oracle\epm automate\bin\epmautomate" login 

call "c:\oracle\epm automate\bin\epmautomate" getsubstvar Consol name=CurrMonth >> currmonth.txt

call "c:\oracle\epm automate\bin\epmautomate" getsubstvar Consol name=CurrYear >> curryear.txt

call "c:\oracle\epm automate\bin\epmautomate" logout

The three line output of the two text files looks like this (excluding the filenames):

CURRMONTH.TXT

Processing...

 Consol.CurrMonth=Dec

getsubstvar completed successfully

CURRYEAR.TXT

Processing...

 Consol.CurrYear=FY19

getsubstvar completed successfully

The next step is to get the info that's needed from the files, namely the period and the year. Fortunately, the DOS batch command FOR can take care of that. FOR has many different options. Here are the two commands to run to pick out the month and year from the above.

for /f "tokens=2 delims==" %%i in (currmonth.txt) do (set currmonth=%%i)

for /f "tokens=2 delims==" %%i in (curryear.txt) do (set curryear=%%i)

NOTE: if running these commands within a batch file, the variable i is preceded with TWO % and if running manually from a command line use ONE % instead. Tokens=2 instructs FOR to read the second occurrence of what is found. Delims== instructs FOR to use = [the second one] as the delimiter. 

Last, if the script needs to pass the year to Data Management to run a data load rule, create a new variable to combine the month, a dash, and then the two digit year (strip off the FY).

set dmperiod=%currmonth%-%curryear:~2,2%

So now there are three variables. To verify, type:

echo %currmonth%

echo %curryear%

echo %dmperiod%

Jan 2022 Oracle Cloud EPM Update - new EPM Automate SkipUpdate command

I want to point out a new EPM Automate command that's being released in the January 2022 updates. SkipUpdate. What this does is it allows you to skip a monthly update. Before, you had to open an SR to skip an update. Now (or soon), you'll be able to do this yourself, which is awesome. Here's how it works.

The command has three parameters: add, remove, and list.

Add means you want to add an update to skip. So you would say epmautomate skipUpdate add version=22.04 to skip the April 2022 release. You can also optionally add a comment.

Delete means you want to clear ALL previous adds you have submitted. So if you had a typo or whatever, you can clear them all and start over.

List is exactly what is says: list the active skip requests. You would want to do this before the delete. The list also shows the user that did the request, date/time, and any comment.

There are a few things to remember. First, each environment MUST update at least quarterly. So you can't lock in the code as of now and use that forever. And when an update is applied, all features/changes/etc. that have occurred since the last update get applied. There is no skipping of features, etc.

Second, the setting is by environment. You may be thinking that you could let the test environments do the normal monthly update and focus just on the production environments. But then there's a problem. The snapshots only migrate between environments if they are the same version or one version apart, but not two. You could find yourself where test is two versions ahead and now no longer to accept a snapshot from production.

If the environment is on the current release then weekly and emergency patches will be applied. But if the environment has a one off patch (like applied by development to fix an issue only for your company) then the next update cannot be skipped.

The release notes have a link to a powershell and Linux scripts to run this command. You can also run it manually, with a DOS batch file, or however you run EPM Automate. 

This command will be great to ensure that updates do not happen during quarter ends. Some environments are not calendar fiscal year based, so this would be a huge help for them since the Oracle updates favor a calendar fiscal. 

Here's the link to the EPM Automate help for this new command: skipUpdate.

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

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 Smart View - showing both label and alias/description

I haven't done a blog post in a while so it's past time for one. And after reading this and seeing my previous post, I am glad there is continuity.

Let me start by saying I'm liking FCC. It's the next evolutionary step in Oracle consolidation just like HFM was an evolutionary step above and beyond Hyperion Enterprise. But sometimes with these processes there is a step back. And yes, there are bits of Enterprise that were never replicated in HFM and are still missed to this day. This post describes one area like this, offers a workaround, and asks for a favor.

For HFM users, and this was true for Enterprise, when you used the spreadsheet add-in of the day to do ad hoc analysis you had a choice something like this. Ignore Indentation and Ancestor Position - they are headings for other fields that are hidden by the expanded dropdown.

So when you're in an ad hoc grid you can choose to see the member labels, member descriptions, or both. Both would look like 100 - Petty Cash.

When using Smart View with FCC or Planning, the same dropdown has different selections.

Both of these are versions of the member label. To see the alias or description, there is a second button that appears on the ribbon called Change Alias.

With these options, choosing None will show the labels and choosing one of the aliases (this application only has one called Default) would show the alias instead.

THERE IS NOT AN OPTION TO SHOW BOTH. And this is the departure from HFM, etc.

So, here's the solution. Note this wasn't my idea but was proferred and confirmed by people smarter than me in these areas. Create another alias and populate it with the combination of label and the regular alias. Users can choose that as they desire. You can also use it for reporting if you want. The downside is you have two fields to maintain instead of just one. If you are using EDM, DRM, or other metadata management tool, then you can use a formula to calculate this field. Regardless of how you do it, just watch the overall length though.

Now, the favor. There is something you can do. If you read the previous post on this blog, it describes how to go to Oracle Cloud Customer Connect and submit and like ideas. Someone already created an idea for this topic. So here's the favor: if you agree, please log into CCC and like this idea. Here's the link. I have seen ideas that get liked in this fashion make it to the product, so it is not a pointless exercise. Guaranteed, no, but not pointless.

https://cloudcustomerconnect.oracle.com/posts/c676227152

I think FCC is great, but I do miss the simple things in life sometimes.

Cloud Customer Connect - Liking Ideas

Hopefully all Oracle cloud customers know about this topic, but maybe not. There is an Oracle website, cloudcustomerconnect.oracle.com, where users can post forum questions and submit enhancement requests (they're called ideas). Oracle staff regularly review the items on CCC and comment. Also, there are webcasts announced via CCC on new product features and other items and the replays of these webcasts are also available.

All of that is good, but there is a feature that doesn't seem to get used all that often. Submitting an idea is good, but it is helpful if others review the idea and like/dislike it. If something is important to all users, then all users should weigh in which would raise the importance of the idea in Oracle's eyes.

For example, I searched this morning for an idea and didn't find it, so I created the idea.

In case you're wondering, yes, you can drill down on shared members in forms but not via Smart View ad hoc or Financial Reporting expansions, both of which are used much more than forms.

If this idea (or any idea that's out there) is important to you, click the green circle with the thumbs up. You're presented with a comment box so you can easily explain why this is important to you. You can also just cancel out of the comment box if you don't wish to leave a comment.

Note you can only like/dislike three ideas within a minute, so go slow and don't get click happy.


If ideas are not supported, then there is a perception that the idea has little importance to the users at large. Oracle reviews the ideas and categorizes them. For example, this idea from 2018 has been tagged as planned. This is also one of the most liked ideas in the FCCS arena, so if only 25 people are weighing in there is a lot of room for improvement in participation.

So, in summary, go to cloudcustomerconnect.oracle.com, use the forum and the webcasts, but also do two things: (a) submit ideas and (b) weigh in on other ideas. Or, to use another term that's popular in the USA periodically, go vote!


P.S. While you're in CCC, if you want to support my idea at the top about drilling down on shared members, it would be much appreciated!!!! Here's the link:

https://cloudcustomerconnect.oracle.com/posts/f5e1dd2fed

HFM 11.2 - New Features!

The on-premise Oracle EPM product suite was upgraded today to release 11.2. This long awaited release is primarily a technical upgrade for a lot of back end technology. That all said, there are two new HFM features.

First, in Task Audit, there are additional tasks that are now logged.

• Journal Period Opened
• Journal Period Closed
• Data Locked
• Data Unlocked

The data lock/unlock tasks also include the point of view being acted upon. It's not clear but I'm hoping the journal scenario/year/period is also captured.

Second, there are two new configuration settings related to calculations.

• MaxNumConcurrentCalculations - sets the number of concurrent calcs allowed per server per application. The default is 8.
• ConcurrentCalculationWaitTimeInSecond - If the max above is reached and someone then starts another calc, this setting controls how long they wait before the calc times out. If a calc times out, then a message is entered in the system log with the point of view details. The default is 30 seconds. By setting the value to -1, the system will wait indefinitely until another calc finishes.

I've lived without these two features but I can understand and appreciate their usefulness. Looking forward to seeing what else is included with version 11.2.

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!