Why I created a blog

Its been four years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Wednesday, June 7, 2017

Using Cube Designer with OAC to convert to Hybrid

Recently on OTN (or was it network 54) someone asked about changing all of parents to be dynamic for a Hybrid cube in Essbase.  There were a number of suggestions from getting utilities to downloading the Next generation Outline extractor from Applied OLAP, to using Excel.  All good suggestions, But if you are using Essbase in OAC, there is even an easier way, wait for it - Cube Designer.

Huh? Cube designer is the Smart View extension for OAC that lets you build your outline in excel and uploads it to the cloud. It is pretty slick. One of the items they figured is people will want to convert cubes to the Advanced Analytic Engine (a fancy term for Hybrid).  So they added an Easy button. 

First you have to have imported your cube into the DBX format.(I’m guessing DBX stands for Database extract, but it could mean anything).  This can be done a few ways:

1.       If the cube is already in the OAC instance, in Cube Designer, select private->Export cube to application workbook


2.       If it is not in OAC, then get your hands on the DBXtool utility (ask a cloud friend for it). With it you can export your on-prem cubes into excel (and who wouldn’t want to do that)

Once you have it in Excel, go to the dimension you want to change. In my example it is Customer. For this example it is a nice small dimension

Next click on View Hierarchy


The hierarchy will appear in a dialog box. Expand “Edit Parents” and select “Set Storage to dynamic”

Click refresh and all of the parents in your worksheet are changed to dynamic. Close the editor


I know what you are saying. Glenn it was only two members. Yes in this case it was, but in most instances it will be hundreds of members scattered throughout the hierarchy. This will handle them all at once and it is super-fast.

After that you can use the Build cube icon to update your existing OAC cube or build a new one.  What could be easier than that?  (well at least for OAC).

Look for more of these little tidbits about Cube Designer and OAC in general in future posts



Tuesday, May 30, 2017

New old settings (and graceful stopping applications)

I am not immune to plagiarizing sharing information I have gotten. In this case, I received a private message from one of the top support people for Essbase Carol Crider. She actually said I could/should share this to make the information more well known.  See I really do give credit where credit is due.
Thanks Carol.

"We have a OLD setting with a NEW feature and the documentation is a bit lacking in clarity. The setting is:


 This used to be ONLY UNIX, now it ALL operating systems.  It provides a lot more detail to support and development on the cause of a crash. Before you had the XCP and the stack it provided. A crash dump on Windows gives a mini dump of memory. For lack of a better term they are calling the Windows mini dump a core file.

 Also another change that was undocumented is the unload of applications. We are seeing several SR's because we get unable to unload application xyz while user GLENN is running a transaction... (editor note - Wait, How did I get in her description. I swear I didn't run anything)

 Here is a definition provided by development: ++++++ * Some operations cannot be stopped a lot of time even if user killing appropriated session or it just stuck. As workaround, you can unload the application to kill all running sessions. To avoid a situation when user cannot unload an application because of such "stuck" sessions - Essbase developed recently ( an  internal mechanism that soft kill Essbase application process (ESSSVR) when "alter system unload application" command is received, but some session on the application cannot be stopped. Essbase waits 3-5 minutes before soft kill the application. When this mechanism is activated - the following should appear in <app>.log file: 
RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING ... At least one request is still active; a core file may be generated for diagnostic purposes, if core file generation is enabled ... Exception error log [<APP>/log000xx.xcp] is being created... A core file may get generated in [<APP>/ESSSVR.Fri_Oct_xx_xx_xx_xx_xxxx] Exception error log completed <APP>/log000xx.xcp please contact technical support and provide them with this file RECEIVED ABNORMAL SHUTDOWN COMMAND - APPLICATION TERMINATING  "

So there you have it. upgrade or patch to (or higher) and you get the soft kill. You also get better diagnostics. Thanks again Carol for sharing.   

Friday, April 14, 2017

Yeah, Its Back!!!

This will be a short post about the newly release Smart View version that was released a few days ago. It seems what is old is new again. What am I talking about? The ability to pull in calc scripts(For Essbase)  into Excel, and run them directly from Excel.

Let me give you a little background. With the Essbase Add-in through the API (not the toolkit), I would use VBA code to pull in a calc script from the server. In the script I would have tokens like ~Entity. In Excel, I had a routine to change ~Entity to whatever entity the user selected. I would then execute the calc script directly from Excel. Nice custom focused calculations just like we now do in Business Rules. When Smart View came along and the API was removed, we lost that functionality.

Well now it is back! There are two new functions, HypGetCalcScript and HypExecuteCalcScriptString that allows me to do this again. It is funny, the use case I described is actually written into the description of HypExecuteCalcScriptString – From the readme “executes a calculation script along with substitution variables. This function allows you to customize the script string, or use portions of strings from different calculation scripts.”

That is not all that is included in this patch. There are a lot of other new features including a new HSGetAlias, changes to HSGetDescription, New features for PBCS, FCCS, OAC HFM, Chart support for Power Point, and a whole lot more. To see it all, you can look at the readme a
t https://updates.oracle.com/Orion/Services/download?type=readme&aru=21178353#smart_view_11125700_readme_174 (Yes Virginia, you do need a support identifier to get to this doc).

I’m excited to play with this new/Old functionality. Until Essbase 12c comes out with excel support for runtime prompts, the will be allow us to make our Essbase environments even better.

Friday, March 17, 2017

Essbase Cloud Service - Is it here or isn't it?

For over a year now, I’ve been beta testing Essbase Cloud service and it has gone through a lot of change in that time. When I first looked at it, it was to be a SaaS offering like PBCS. That changed multiple times to become PaaS.  At Oracle Open World, when I did a session with Gabby Rubin, things started to change even more. We were talking about the integration of Data Visualization and Essbase. So now we don’t have EssCS but finally the release of OAC or Oracle Analytics Cloud (notice no service at the end). Will the Essbase part be called EssCS or just Essbase in OAC? Only time and Oracle will tell.

View my Play it forward video “What is OAC?” at Play it forward videos http://bit.ly/WhatIsOAC.

OAC is a suite of products built to work together.  I think this is the direction Oracle is going. Rather than release individual products for the Cloud, release suites and let the user decide what to enable and use.

What does OAC have in it? Well my beloved Essbase, Data Visualization, BICS, and a couple of other products.

As I said, this is a PaaS offering. This means a lot of things. For the User Admin:

1.       Responsibility for determining what patches to apply and when. No forced patches like PBCS.

2.       The need to set up backups (again no forced down time)

3.       Responsibility to schedule operating system patches


A set of users will have access to the server (designated by the client). If really large files or special files need to be put on the server, FTP is available.


As I write this, it is pre-GA (by the time you read it, the product will be GA) so I don’t have the final pricing. I do know it will be priced by OCPU (Oracle CPU). Each OCPU is accompanied by a corresponding number of OCPUs you want and for how long. What is nice is this makes the environment elastic. You can get 4 OCPUs for everyday work, but at quarter end, bump it to 8 for 2 weeks because of extra processing. Then at the end, bring it back down. There are different rates based on hourly usage or monthly usage you can choose. From what I understand, again subject to change, there will be two setups. A Standard environment and an Enterprise environment. The difference is additional functionality in the Enterprise version for (wait for it)…an additional cost.  I’m not going to comment on what the features are as they have been changing frequently. When the price list is released, we will know more. 

So let’s talk about what OAC has to offer. I’m going to concentrate on Essbase since it is my first love and others are talking about the other products in the suite. This is Essbase 12c which means it is a fully functional hybrid model. No more work arounds for functions that turn the database into BSO mode or cross dimensional calculations. All of the cubes are Unicode enabled by default. Others are talking about features like unstructured data loads and tying it to DV so I won’t mention them here. For those of you who want to migrate your on-prem Essbase cubes to the Cloud, how is it done?

There is a new stand-alone version of LCM that can be downloaded and used to create a backup of the on-prem cube for upload. It is different from LCM in Shared Services, as it actually exports everything needed for an Essbase cube. There are a couple of gotchas in migration. All substitution variables now have to be application specific and configuration file settings are application specific as well. There is another new tool EssCLI (Essbase Call Level interface) similar to EPMAutomate for uploading the LCM extract, moving files around, running load rules and scripts, and downloading files back to on-prem.

Next, the way you build cubes is different. In this release, you will install a Cube Designer extension to Smart View in Excel.  You can export and import DBX files which are excel files with the settings of a cube and all of the dimension information. The dimensions can be built in either a parent-child or generation format. This is not the same interface that Planning and PBCS use. It is richer and has graphical components.  There is a web based UI as well where changes can be made.

Essbase now has sandboxing and Scenario management built in. While we have talked for years about how you can do “what Iffing” in Essbase, you can truly now do it as it is built into the product. What is nice about the sandboxes is they are lightweight, not in functionality, but in data storage.  The sandboxes hold overrides from the base scenario so little additional data is stored keeping cube sizes small. In on-prem, we had to copy data to new scenarios, replicating all of it, and it could be painful. What is also impressive is that you can run calculations against the sandboxes and it behaves as if all of the data is there. Access to sandboxes is controlled through the new scenario management feature. The owner of the sandbox decides who can participate in the sandbox either to collaborate or be an approver of the data. The data can be merged back into the base data in multiple ways.

I don’t want to set unrealistic expectations as everything is not rainbows and unicorns, as this is the first release. There are things that will not be there initially. First, with load rules, you will be able to edit them using JSON in a text editor, but the UI is not there yet.  Second, I hope you have a good understanding of calculation scripts and formula syntax. Currently the editor (in both the UI and Cube Designer) is nothing more than a text editor with a validation to say that the script is good. The bells and whistles are not there yet for autocomplete, inserting from a function list, and color coding. The ability to upload or run your MaxL scripts is in the EssCLI documentation but is not enabled yet. If you want to upload MaxL scripts, you will have to use FTP to get it on the server and log onto the server to execute it or schedule it through CRON. This is a Linux server so anything done in a batch script will have to be converted to shell scripting and in Linux, things are case sensitive. In testing, I was bitten multiple times in Cube Designer and EssCLI where I had the wrong case and got errors.

So, as usual, I have been honest and told you the good and what needs to be improved. So what is my take on Essbase in the Cloud - would I migrate to it?  I think there are specific use cases where I would immediately go to the Cloud:

1.       If I don’t have Essbase already and I want to don’t have resources for on-prem Essbase (IT, servers, or other)

2.       I want to allow my users to take the data dumps they have now and are turning into pivot tables, to upload them into Essbase and do almost immediate analysis on the (collaborate, shared source of data)

3.       I have on-prem Essbase with a few cubes. The data for these should be flat file based as this version does not support SQL loads (yet).

4.       I want to gain the ability to use DV and BICS to analyze my data more graphically.

I am excited for the future of OAC and expect really good things to be added after the initial release. I’ll be blogging on more specific items as they get enhanced and/or I’ve had more chance to work in the GA version. I’m hesitant to say anything now as we all know what is in a beta version is not always in the GA version, and sometimes things show up in GA that we never saw in testing


If you want to learn more about OAC, check out the blog hop participant posts below! What is a blog hop? A blog hop is a group of bloggers who all get together to blog on a particular topic. We share each others blog posts in an attempt to share a lot of great information in one place. Enjoy!

Friday, March 3, 2017

Three Little Words

Others and I answer a lot of questions on the Oracle Technical Network and other forums and there is a ubiquitous little phrase that sends chills down our spines when someone is asking a question. What could be so bad that it sends terror down our spines?  “Nothing has changed”. Well that was the start, but the chilling words? “Just one thing” Why do I start this post with this? 

I was at a client doing a technical review of their environment this week.  (Names, PAG files sizes, dimension hierarchies are all fake to protect the innocent) We were doing some performance testing and somewhat related to the testing, but in production, they were having an issue with a BSO cube.  Their process is to export the level zero data, Clear the cube, “Deploy” the cube, reload the data and aggregate the cube.  

During this process, the cube grew from ~60 gig to ~180 gig. Of course I asked the question, what changed? The initial response was “nothing…” then a few minutes later “well just one little thing.” What was that one little thing? They explained to me most of the data is loaded into one member in the hierarchy (let’s call it No_ZZZZ). They continued that No_ZZZZ was a level zero child of the dimension ZZZZ. So the hierarchy looked like:

“All we did was to move No_ZZZZ to be a sibling of F2”

From this we had a deep discussion about how blocks are created.  Let’s make an assumption that all other dimensions have only one member in them (wouldn’t that simple design be nice?). In the first example, if I aggregate and all of the data exists in No_ZZZZ I will create one new block - ZZZZZ

In the second case, 4 additional blocks are created – d1, aaaa, Total_zzzz and ZZZZ.  In my simple case, going from 2 blocks to 5 is huge. Imagine when you consider real dimensions where you are aggregating a large number of members in the other dimensions. It makes it very easy for a cube to grow tremendously with just a simple change.  

The moral of the story? Well there are two First you have to understand the impact of any change you make no matter how small it may seem. You need to test and not just assume the change is minimal. Second, if an issue occurs, it is typically because of some change somewhere. Before asking, research to try to figure out what might be causing the issue. It might be “Just one little thing”         

Wednesday, January 4, 2017

FCCS- What you see is not always what you get

I was contacted by another consultant who is working on an FCCS implementation and he was having issues with not getting data for certain accounts. He looked at his mappings and they should have been loaded. From an Essbase perspective, I know that the typical reason data won't load is because it is trying to load to a dynamic calc member.
I mentioned this to him and he showed me the properties of one account:

So much for that, I thought. I then asked him to use the dimension editor in Smart View for FCCS and Long story short, Although the Data storage showed never share, the Data Storage for the cube (and the rates cube showed dynamic calc.

(Note, I hid the extra columns that are not applicable to this issue.)

It turns out h had created this and other members by copying a parent member and just changing the one data storage property. Why there are multiple that don't show up in the editor, I don't know.

As a side note, this consultant did later in the day meet with the Oracle "Seal" team that helps with issues. They have the ability to look at the created cube with EAS and lo and behold, the members were tagged as dynamic calc. He is fixing the entries using the dimension editor now and that should solve his problem.