Many of you used the Essbase ribbon I built 10+ years ago. I have been asked by many to build a similar ribbon for Smart View. Today I am introducing the Power Adhoc Ribbon. It has all the bells and whistles of the old ribbon completely re-engineered for Smart View. There is a nominal fee, but you can try it for free. Some of the awesome features are summarized below.

The Power Adhoc Ribbon has it’s own website where you can get updates, download the most recent version, and read more about what the options and future enhancements. Head over to the Power Adhoc website for all the details.

• Refreshing worksheets is the cornerstone of any adhoc reporting. We put refresh on steroids. You can refresh the worksheet or all the worksheets in the workbook, but we added even more. The ribbon has two other options that let you refresh with suppression on or off, without changing your Smart View settings.
• We know you change your aliases all the time. Now it sits on the ribbon to toggle it on the fly. Change between no aliases and the default aliases whenever you want, quickly and efficiently.
• The ribbon also includes quick options to change your aliases between default and none. It doesn’t stop there. You can also toggle your formatting between Excel and Cell Styles.
• Now, all your most commonly edited worksheet settings are on the ribbon, including zoom, suppression, and retention. When you change worksheets, the ribbon automatically updates to reflect the current worksheet settings.
• What do you do when you want to distribute those workbooks with Smart View functions? Zap will remove all the functions in your worksheet or workbook with the values they return and leave any Excel formulas.
• How many times have you wanted to duplicate a worksheet and been frustrated because it doesn’t copy your settings and connection? That is now a thing of the past. The worksheet duplicator will copy any worksheet and include the connection and worksheet settings.
• The Power Adhoc ribbon adds a complete template management vehicle. Commonly used worksheets can be saved in groupings and reused at a later time by opening them right from the ribbon. Group them by application, purpose, or however you want. Templates can be starter adhocs or standard reports. You manage your templates and how they are organized. Your templates can be renamed, reorganized, or removed at any time.
• Power Options allows you to show/hide the options you want. You no longer need to see the options that aren’t relevant to you.
• Because you maintain your subscription, we can easily let you know when updates are released.  As soon as any improvements are available, you will be the first to know.
• Not everything on the ribbon will be new to you. To reduce the need to toggle between ribbons, the most commonly used options from Smart View are included.
• To reduce the learning curve, every option has a power tooltip that explains its purpose.
• By having a subscription, you get all the updates and you decide what enhancements are prioritized. If there is an enhancement you need, ask for it.
• Many operations, like refreshing and submitting, can sometimes take longer than you expect. Just to make sure you can monitor those requests, we notify you how long it took by updating Excel Status Bar.

For years, people have asked me to build a new ribbon to supplement Smart View that replicates the functionality I have in my Essbase Ribbon.  Multiple people have hacked into it and made it their own.  One company is selling it.  For that reason, I had zero interest in doing it again.  Time has passed, and here I am, building a ribbon.  It will again have common functions in one place, as well as some additional ones, like

• Copy a worksheet that includes all the Smart View settings and connection information
• HS Zapper function that will replace any cell with one of the HS functions with the value it returns for all you folks that use HSGet and its sister and brother functions.
• Template manager that will allow you to create/manage/update templates, whether they be complicated workbooks, or just starting points for a new adhoc
• A connection manager that will automatically log you in to an environment
  • Maybe a Shared Connection switcher to easily toggle environments
• A bulk copy/paste option that to put in the POVs
• Functions like retrieve with suppression, retrieve without suppression, so you don’t have to constantly change the options

This will not be written as a VBA addin.  It will be a full-blown DLL written in C# to optimize security and performance.  It will come with an installation process that will allow easy installation updates.

If you would like to be a beta tester, I am looking for 20-30 people.  If you are interested, send me a message through the contact option at In2hyperion.com.  I will include people up to the a limit to make it somewhat reasonable to manage feedback.  If you want to ensure inclusion, let me know ASAP.  It will be first come, first serve. It will be ready in weeks.

This is what you can expect as a starting point.

Nobody will argue that Oracle Support is a word class experience.  Like it or not, the service is here to support thousands and individuals can get lost in the process.  If you navigate the process correctly, you can get results.

What Do Do First

Your first step should be to troubleshoot the issue.  Do everything you can for support.  The more you provide, the less “did you plug in the computer” type questions that delay you getting help  you will get.  Be clear and concise.  The first few levels of support are likely less experienced than you and will only be able to provide novice level help most of the time.

Next, make sure the content of your support ticket is complete.  Don’t leave any questions.  What I have found to be extremely helpful is to video the issue.  Make sure you show the problem and walk through the calculation or script or whatever is the source of the issue.  This doesn’t have to be a professional video.  There are free screen recording applications.  There are also some pretty inexpensive ones.  If you use a Mac, ScreenFlick is 35 bucks and well work it.  It gives you the basics.  If you use SnagIt, it also works just fine.

If you are a Windows user, there is a nice free option, but you probably haven’t noticed it, and for good reasons.  If you look through the Start menu, you won’t find any reference to a screen recorder. You’re not missing it… it simply isn’t there. That’s because rather than making the screen recorder a standalone app, Microsoft included it as a feature of the Xbox Game Bar in Windows 10.

Your Second Step

After you open a ticket and provide your video of the issue, proving there is an issue without a doubt, follow these steps

• Make sure you get a duty manager assigned and push for escalation
• Call 1-800-223-1711
• Press 1 and enter your SR number
• Press 2 to speak with a manager
• Do not press 1 to speak with Engineer, this will likely result in going straight to Voicemail
• Tell the Support Hub the critical impact you are facing
• Request a Duty Manager callback ASAP
• Repeat request level 2, then level 3, to level n, until you get what you want
• Call hourly until you get what you want – the squeaky wheel gets the grease

During this process, here are some things make sure you get the most out of the experience.

• Speak with the support hub manager
• Describe the impact, include key project milestones at risk and communicate what you need for progress
• If your issue is CRITICAL you should ALWAYS make a request for a duty manager call back.
• If you ask for a duty manager call back, wait 30-60 minutes until you receive the call and reiterate the problem
• Be strategic with your request
• Don’t just say you need to escalate – describe the impact and communicate what you need for progress
• Keep in mind, your SR will NOT appear in an “escalated” state unless you negotiate this with a duty manager

That’s A Wrap

Should you have to do all this?  No.  The experience should be better, but yelling about it and banging your fists on the table won’t change anything.  You have to play the game and go through the process.  If you use these tactics, I guarantee you will improve the response.

Having the best of both worlds, ASO and BSO, doesn’t come without some gotchas.  Before you jump in with both feet, beware of some things that are not supported in hybrid.  As of Friday, May 22, 2020, @ISMBR in planning does NOT work. I don’t know if this is a bug, but it is not documented as a function that doesn’t exist.  What is documented is the following.  There isn’t a ton in this post, but I thought it would be beneficial to share this as a warning, as well as an easy way to find the list. If you find more things that don’t work, please share with the community.

Oracle is providing free access to Oracle Financial Statement Planning, including Strategic Modeling, to all existing Oracle Planning Cloud customers for the next 12 months.  This includes the use of Groovy.

You would be able to purchase Financial Statement Planning and Scenario Modeling if you choose to continue using it. Alternatively, your right to use Financial Statement Planning and Scenario Modeling will expire after April 30th, 2021.

And yes, Hackett Consulting is a preferred provider to help you take advantage of this.

The Oracle FAQ will provide answers to your most of your questions.  If you would like to take advantage of this, let’s get the ball rolling!  If you are a do-it yourselfer, the following classes will kickstart your ability to implement Groovy.

• Mastering The EPM Planning Javadocs
• Groovy for EPM Cloud Planning
• Groovy for EPM Cloud Planning (Grid Builders Only)
• A REST API offering will be available next month

Try it out for free getting a few samples.

Importing data directly into PBCS can be done in two ways.  There is a planning format and an Essbase format.  Yes, it can be loaded through Data Management, but this is focused on explaining the direct import process, the formats, and the benefits to each.

Planning File Format

What Is The Planning File Format

Per Oracle, if data is exported to a file in the Planning format, you can use Notepad to change the database name in the file; for example, ASOCube, and then import the data file into the aggregate storage outline provided all dimensions exist.

This is fine and dandy if you are importing data exported from Planning.  Normally, there is a source that is giving the data to PBCS and a format has to be provided to that source.  The format is basically a delimited file with some quotes around a group of the fields.  Technically, there are 3 pieces of the file.

1. One dimension has to be in the rows.
2. One dimension has to be in the columns.
3. The third section is the POV, or the remaining dimensions.

It looks like this.

Account,Jan,Feb,Mar,"POV",Plan Type Name
10020,40.10,50.60, 70.20,"Actual,Final,Company_10,FY18",Finance

Why Use The Planning Format?

There are some benefits and drawbacks of using this format.

• Data loaded to smart list accounts can be loaded as the actual smart list label. This is great for applications where the data is provided in that format.  WFP data often provides data in the smart list value, like Part Time/Full Time.
• When there are errors, the load continues through the end of the file.
• The job console shows the number of records processed, the number of records that couldn’t be loaded, and the first x lines of errors with line numbers.  I will warn you that I have seen the console show all records loaded and nothing loaded.  I have also seen no rejected records when the file didn’t load completely.  So, when you are developing loads, I would recommend validating carefully and looking at the block counts to ensure the messaging in the console is correct.
• The biggest drawback to loading the planning format is that it loads through Planning and because of some of the benefits, it is slower to load. This normally isn’t a huge issue because the loads are finished within a few minutes.
• The format is a little odd and can sometimes be problematic to produce.  Certain dimensions are required in the row and column headers.  This is often challenging.  For instance, most applications require the months to be in the row or column areas.  Since they can’t be put in the POV, more than two months have to have multiple columns.  If the source can’t produce this easily, a file for each month has to be created.

Essbase File Format

Why Use The Essbase Format?

Loading directly to Essbase is faster and often times easier to produce the needed format.  It loads directly to Essbase so none of the mapping done when loaded through Planning (like smart lists) happens.  This is often a huge headache because the mapping has to be done in the source or in some middle ETL layer (assuming DM is not being used).  Another issue with using this method is that when a record rejects, nothing more is loaded.  The job console also does not show the records processed.

What Is The Essbase File Format

Per Oracle, (exported by an on-premises product). These files can be formatted in either Cell list or Column list format. Cell list format specifies a single cell value for each record, whereas Column list format specifies multiple cell values for each record. For example, if you specify Time on the column, you might have the values Jan, Feb, Mar, and so forth on the column, with 12 values for each record (that is, one for each month). Conversely, the Cell list equivalent would contain 12 records, one for each month. For information on exporting files from Essbase, see the on-premises Oracle Essbase Administration Services Online Help.

That doesn’t tell us much.  The format of this loading methodology is almost identical with the Planning format.  The thought process is a little different.  This file will hold a column for every dimension but has no header line.  Each dimension is preceded and terminated by quotes and delimited by a tab.  All the dimensions are listed first, followed by the value of that intersection.  The planning format shown above will look like this

"10020"       "Actual"      "Final"       "Company_10" "FY18" "Jan"  40.10
"10020"       "Actual"      "Final"       "Company_10" "FY18" "Feb"  50.60
"10020"       "Actual"      "Final"       "Company_10" "FY18" "Mar"  70.20

Last Call

Neither of these formats/methods are complicated, but the documentation is lacking when explaining exactly what they are.  There is little mention to the pros/cons.  Hopefully sharing this will fill in the gaps that exist in the documentation.

I don’t normally write up monthly updated, but this month there are a number of intriguing changes/updates/enhancements that are important to know.  Some may change existing processes.  This is not an exhaustive list, but these are things I think all of us should take note of.

The changes that you will see in the UI may not be visible unless you clear your cache.

EPMAutomate

• You MAC users are going to like this, it can now be installed and work on a MAC. By the way, I am one of them.  I can’t wait until the beta is finished for the Smart View to work with office360, meaning MAC, Ipad, etc.
• You can now run multiple instances of EPMAutomate. So, logging out of one will not log you out of the other.  This will help those that want to run test and prod on one VM.  It will also remove any issue of one process stepping on another.
• Admin mode can be enabled with EPMAutomate, which was a huge missing piece, in my opinion.
• A sortMember command to order members in dimensions is now available.

Forms

• No more composite forms, sorry guys!

Smart Push

• There is a new Smart Push option called Use Database Suppression that enables you to apply row suppression at the database level instead of at the application level, thus eliminating the impact on query thresholds and improving Smart Push wait times.

Settings

• There is a new setting to set the accounts that are linked between plan types to false. This will use the  a new attribute of HSP_LINK (not HSP_NOLINK).

Groovy

• We now have the ability to use the new suppression option on the data map / smart push. This will improve performance and reduce the need to break up pushes in groups of members.
• Groovy can now run jobs. This means a rule can execute other jobs within that rule.  They show as tasks in the job console.  This is limited to the execution of rules, rulesets, and templates.
• All RTPs are now supported in ASO.

Smart View (AMEN on some of these as us old timers will love) – You will want to download the updated version to take advantage of these.

• In-grid, cell-based POV—POV members are placed on the grid instead of in the POV toolbar.
• Submit without refresh—Using the default Submit Data button in the Smart View ribbon, all data cells in a single grid (modified cells and unchanged cells) are marked dirty and submitted. Once the submit operation is complete, the entire grid will be refreshed.
• Enhanced free-form—Users can insert empty columns and rows anywhere in a grid and change the alias table. Additionally, supports member auto-refresh where deleted members are returned to the grid upon refresh.
• Multiple-grid ad hoc—Multiple ad hoc grids can be placed on the same Excel worksheet. With multiple-grid ad hoc, users can submit data from any grid on the sheet. Grids based on aggregate storage cubes and block storage cubes are supported on the same sheet. Each grid is independent; for example, if required, you can change the alias table for only one grid on the sheet.
• Users will now get a warning when the version installed is out of date. Hopefully this can be turned off!

General

• Jobs can now have a new return, success with warnings. An example of this might be loading data.  The job finished successfully but there were rejected records.  Make sure your automation accounts for this!

DM

• There is now an option to not run the validation when data is loaded. For those of you that despise the performance challenges of using DM, this might be a game changer.  The longest part of the process is the actual validation, not the import or export to Planning.

Modules in ePBCS

• Rolling forecast can now be configured for length.
• There were some critical bugs that were fixed in WFP (I know first-hand).
• Some CapX forms were improved as well.

There is a new, and often requested, option added to Smart View.  If you use drill through to Data Management (PBCS) or FDMEE (On Prem), download the most recent release of Smart View.  We, as users, now have the option to change where the result of our drill through queries is returned.  Users can either be asked where the result should be displayed, have it displayed in your browser, or (drum roll) have another tab created that holds the results in Excel.

Change Your Option

This isn’t a complicated or drawn out explanation, but it is sexy!  To change where the results are displayed, go to your Smart View ribbon and click Options to open the dialogue.  Select the Advanced tab on the left and scroll down just a tad.  You will see an option for Drill-Through Launch.  The 3 options previously mentioned are available.  This removes one of the biggest user frustrations regarding drill-through reporting and will surely make a lot of people happy.

The Proof Is In The Pudding

It works just as you would expect, but here is the proof.  When you open a retrieve and connect to the application, right click on a cell.  Click on Drill-through as you always have.

If the data has more detailed data available, a new worksheet will be created in your workbook with the results if you have chosen the In New Sheet option.

Not Much Else To Cover

That is it.  There isn’t much else to say, but this is a great and frequently requested feature.  We can finally provide a good answer.

Absolutely you can drill through to the detail and have it returned in Excel.

As always, post a comment if you have something to share with the community or have additional questions about this topic.

The good news is migrating to the cloud doesn’t change a lot when it comes to backing up your Essbase applications.  Conceptually, it is the same.  The utilities used are slightly different.

Enter CLI

If you are new to Essbase on the cloud, the CLI, or command line interface, is something you will want to download and configure.  It is a pretty useful utility and easy to use.  I will say that it is new and missing a lot of functionality you may want.  I was just as frustrated using EPMAutomate with PBCS when it came out.  Three years later, however, EPMAutomate is pretty complete.  I am hoping for the same progression with CLI.  For backing up your apps, the CLI will give you everything you need.

Running An LCM Backup

There is really only one command that is a must.  I will get into why I say this in a second.  LcmExport will run an LCM and store it locally, which is a nice bonus.  There is no need for any other commands to download and rename it.

LcmExport has the following parameters.

• -verbose (or -v) will provide a more complete response description, especially if there is an error
• -application (or -a) requires an additional parameter that identifies your application name
• -localdirectory (or -ld) requires an additional parameter that tell the command where to store the backup file
• -zipfilename (or -z) requires an additional parameter and is the name of the LCM file that everything will be stored locally
• -threads (or -T) requires an additional parameter equal to the number of threads you want to use to run the backup
• -skipdata (or -skip) will tell the LCM to ignore the data in the application
• -overwrite (or -o) is used will tell the process to overwrite the zip file if it exists
• -password (or -p) requires an additional parameter to send the password to the command

At minimum, the application and zipfilename parameters are required.  The localdirectory and overwrite parameters will likely be used in every call you make as well.

C:\[cli foldername]\esscs.bat lcmExport -a Sample -z Sample.zip -ld c:/temp -o

Why This Isn’t Enough

I have always felt very strongly that data exports should be done because corruption will remain in the pag files until it is fixed, and often times, it isn’t found for days, weeks, or months.  At that point, you can’t export your data and you are in real trouble.  So, I don’t rely solely on the LCM backup strategy.

There are a couple ways to export the data.  With an on-premise implementation you might use Maxl to export the data.  The other option is to write a calculation that does the exports.  The calculations route will provide more option with formatting, the delimiter, and what data is included.  It might be a little slower, but since the inclusion of this option, I have relied on it ever since.

You could integrate Maxl at this point to do the same thing, but the CLI also provides you with the tools to do it if you use a calculation.  At this point, assume a calculation exists named FullExport that exports the data to application and database path on the server with a name of FullExport.txt.

At this point, there are two additional commands that will bulletproof your backup strategy.

C:\[cli foldername]\esscs calc -a Sample -d Basic -s FullExport.csc
C:\[cli foldername]\esscs download -f FullExport.txt -a Sample -d Basic -ld c:/backup -o

Completing The Circle

Normally this would be executed through DOS, or my favorite, PowerShell.  This would allow the dynamic generation of the scripts so they could be reused.  Things like the application name, database name, local path, calc script, and possibly some others, would all be variables.  The result would be something produced similar to this.

C:\cli_utility\esscs login -url https://myEssbase-test-myDomain.analytics.us2.oraclecloud.com/essbase -u kylegoodfriend 
C:\cli_utility\esscs lcmExport  -a Sample -z Sample.zip -ld c:/Backups -o
C:\cli_utility\esscs calc -a Sample -d Basic -s FullExport.csc
C:\cli_utility\esscs download -f FullBackup.txt -a Sample -d Basic -ld c:/Backups -o

I would add a step in my shell to rename this LCM and the data export downloaded with a date and time in the name.

REM Rename the LCM Zip file
ren c:\Backups\Sample.zip Sample_%date:~10,4%%date:~4,2%%date:~7,2%.zip 
REM Rename the data export
ren c:\Backups\FullExport.txt Sample_Backup_%date:~10,4%%date:~4,2%%date:~7,2%.txt 
REM Delete all files older than 30 days
forfiles /p c:\Backups /s /m *.* /D -30 /C "cmd /c del @path"

A Final Note

The calculation script referenced above would look something like this.  There are many options that can be set.  If you aren’t familiar with this, a quick google will get you what you need.

SET DATAEXPORTOPTIONS
{
  DataExportLevel "LEVEL0";
  DataExportDynamicCalc OFF;
  DataExportNonExistingBlocks OFF;
  DataExportRelationalFile ON;
  DataExportOverwriteFile ON;
}
DATAEXPORT "File" "," "/Sample/Basic/FullExport.txt";

As always, post and share. If you have a question, do the same.

I have been swamped with project work and webinars, so I haven’t been able to put a ton of time into finalizing new posts.  I have a lot of thoughts and new topics started.  So, expect some new Groovy examples and possibly some Essbase Cloud content.

Unti9l then, I came across this really nice little tidbit when trying to replace special characters in a file and thought those of you who are using PowerShell would benefit by having it.  The beauty of this is that you don’t need to know which characters need escaped.  For those of you like me that aren’t well versed in this topic, not having to know which characters need escaped to work will speed up the development of similar functions.

The following snippet is a simple function that accepts 3 parameters.

1. The string to be searched with the characters to replace
2. The string that will replace the characters
3. The string of characters that need replaced

This function has defaults.  the first is required, but the second and third are not.  If the second and third parameters are now supplied, the defaults – #, ?, (, ), [, ], {, and }  – will be replaced with a blank string, effectively removing them.  These parameters can be passed, and the defaults will be ignored.

function Replace-SpecialChars {
  param(
    [string]$InputString,
    [string]$Replacement = "",
    [string]$SpecialChars = "#?()[]{}"
  )

  $rePattern = ($SpecialChars.ToCharArray() |ForEach-Object { [regex]::Escape($_) }) -join "|"
  $InputString -replace $rePattern,$Replacement
}

Replace-SpecialChars (Get-Content c:\test\replace.txt) | Set-Content c:\test\replaceNEW.txt

The above will create a new file named replaceNEW.txt with the contents of replace.txt, but will exclude the characters supplied to the function.

If this doesn’t make sense, take a look at this.  Let’s say we want to replace every x with xray.  The following command would accomplish this.

Replace-SpecialChars (Get-Content c:\test\replace.txt) "x" "xray" | Set-Content c:\test\replaceNEW.txt

So, this isn’t just for special characters!

If you have a cool script, share it by commenting!