Sunday, March 27, 2022

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



Wednesday, March 23, 2022

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!



Friday, March 18, 2022

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.



Tuesday, March 15, 2022

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.




Friday, March 4, 2022

Reporting - Managing the POV - Grid/Global and Hide/Not Hide

Just spent five minutes figuring out how to manage the point of view in a Reporting (not Financial Reporting) report. One would think this would be simple. For the most part, it is, but not always.

When a grid is open, the point of view displays along the top. The word Default (as shown below for the Period dimension) means to prompt the user for the member selection at run-time. Choosing something else will lock the dimension to that member. Clicking the vertical ellipse icon on the right side of the dimension opens a menu. Reset to Default is straightforward - the member gets cleared and the below choices get reset. Hide Dimension suppresses or doesn't show the dimension and the selected member. In case you're wondering, if the dimension is set to Default AND the dimension is hidden, the first member in the dimension (likely the dimension name) gets used.



The last selection is whether the dimension is Global or not. If Global, then the dimension and the selected member will apply to all grids in the report. If Global is not selected, then the dimension setting is specific to that grid. If not global and not hid, then the dimension and the current selection will show at the top of the grid within the report when the report is run and users can change the member.

So far so good. The problem comes when you want to change things. The order of the selections should not matter - set the member, hide/unhide, or global/not global. But that is not the case. Here, the member is selected and the dimension is global, but the option to hide or not hide is greyed out. If you turn off Global, then Hide is still greyed out.



The solution for this is to reset to default and start over. After the reset, verify Global is selected, set the member, and then select Hide.


In the end, the initial build is easy enough, but if a change is needed, it isn't really a change: it's a start over.