Why I created a blog

Its been many 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.

Friday, August 18, 2017

Fast and Furious - OAC Updates

It took a little while for the first Essbase OAC patch to appear. It did  on July 29th and I blogged about all the new features. Well a mere two weeks later and we have the next big release (what I'll call 106). When I saw the pace it reminded me of the following quote (watch the video link for the full effect)
Round the world and home again
That’s the sailor’s way
Faster faster, faster faster
There’s no earthly way of knowing
Which direction we are going
There’s no knowing where we’re rowing
Or which way the river’s flowing
Is it raining, is it snowing
Is a hurricane a-blowing
Not a speck of light is showing
So the danger must be growing
Are the fires of Hell a-glowing
Is the grisly reaper mowing
Yes, the danger must be growing
For the rowers keep on rowing
And they’re certainly not showing
Any signs that they are slowing
‘Wondrous Boat Ride’, from Willy Wonka and the Chocolate Factory (1971)

Yes the OAC dev team is delivering features and functionality at a tremendous rate. If you are looking for what is included with bug fixes and features, it is a bit difficult as the readmes still don't seem to work and after you install the parch, you can't even get to the (broken) link.  So I won't talk about bug fixes here although I understand there were a bunch. I'll talk about the new features.  I have to thank my friends at OAC dev for providing me with this info. Most of the screen shots are theirs.

Calc Trace

The first thing I want to talk about is a new feature called Calc Trace. It is amazing that once Product Management has to do their own testing we get features we have wanted for a long time. That is the case here. It was difficult for them to figure out what a calculation script was doing. So out of it came this. You set a application config setting CalcTrace On and from Smart View you can see the numbers that make up any calculation. In your script, you provide the intersection you want to trace

then when you run it you get a pop-up in Smart View with what contributed to the calculation
There are a lot more details, but this should be enough to get you excited.

Audit Trails

Next,Audit trails have been added to track data changes. To enable it there is a config setting "Audittrail Data" The audit trail is captured in a .csv file on the server. This will track Smart View data changes, LRO , URL File and cell notes. Access to the results is based on user security , You can see the results either in Smart View for a selected intersection or by exporting the file and openin (in excel preferably).  UPDATE, it has been confirmed that this only works for BSO and Hybrid cubes.

Partitions and XREf/XWrite

Want more? How about a UI editor for replicated partitions. In the last release we got transparent partitions, now to complete it we get the replicated as well. Remember for partitions, if you go across OAC instances you have to be licensed for Enterprise edition, but within an instance is ok.
Talking about partitions, there are enhancements to partitions in general

  • Validation/Save errors have been made clearer
  • There is ample scope for partition definition becoming stale. For ex:
    • cube deleted/renamed,
    • Essbase host/port changes,
    • user credential changes etc.
  • Consequences of such stale partitions could manifest in multiple ways.
    • Incorrect query/calculation results for slices with partition overlapping .
    • Presence of orphan partition between a pair of cubes prevents creation of fresh partition of same type.
    • There was no cleanup/recovery mechanism from such stale metadata in partition definitions without manually deleting the ddb files.
  • Solution
    • Invalidation & Periodic auto cleanup on servers.
    • Invalidate orphan partitions at first discovery.
    • Cleanup all orphan partitions on the cube at startup.

Similar to Partitions, there have been a lot of changes to Xref and location aliases mostly to allow cross instanceand  on-preXREf . XRef now hastwo flavors:

@XREF(LocationAlias [, mbrList])

Recommended to be used to access remote cube from a different essbase instance. For ex: Local cube on cloud & remote cube on-prem
@XREF(App,Db [, mbrList])
Meant to be used only in single essbase instance models.
Facilitates easy usability through simple DBX import of multiple dependent cubes
For location Aliases to define a connection to another server, you define the connection then use that connection in the location alias
Scenario Management has been improved. In prior versions if you added sandboxing to an existing cube you lost all data. I guess you still do, but now if you export the data prior to creating the sandbox, you can re-import it and it will automatically be saved to the base member. No need to build a load rule.

Hybrid cube automated solve order.

This is enabled by setting the config setting “HYBRIDOPTIMIZEDSOLVEORDER TRUE”

Doing so, sets up  bunch of automatic solve order settings
  • If the formula contains even one ratio operator (/, %), then set the solve order to 51
  • If the formula contains even one “*”, then set the solve order to 5  
  • Rest operator or combinations between operators of different types will get their default solve orders (10-50)
  • If consolidation type of formula member is ‘^’ (never aggregate) then set the solve order to 51
  • If dynamic member doesn’t have formula (aggregation) , then no changes to solver order are currently performed
Note this is based on the operators within the formula not the unary operator on the member


The next item is something I requested for a long time. It is encryption for EssCLI. In the early versions, the ID and password were clear text in the file. Now an encrypted file is created that stores the password. The first time you log in you are prompted for the password and it is saved in a file, the next time, you just specify the user and the password is used from the file
EssCLI has also added support for remote data sources. This means you can load on-prem data relational data sources from within the companies firewall without opening ports. To use it you create a connection.properties file the EssCLI uses. It uses JDBC drivers for the connection.
you can then use it in the CLI

Dimension build

esscs dimbuild -application Sample -db Basic -rule Dim_Product.rul -stream -properties connection.properties -restructureoption ALL_DATA

Data load

esscs dataload -application Sample -db Basic -rule Basic.rul –stream -properties connection.properties

Drill Through Reporting

I have to say I'm not thrilled about this feature as I think this first generation is very limited, but at least it is a start. In Enterprise edition, you can drill back to a source. The reason I'm not thrilled is the dril through definition is tied to the generation names you would assign in the outline. I think it limits the functionality and I've expressed this to development. It is a multiple step process to set this up and would take too much space in this post to go through it. I'll create a separate post to walk you through the steps.

Cube Designer

 This Smart View extension gets better with each release and the new features include:
  • Improved Server Error Messages handling
  • Allows Export of Application from within Cube Designer
    • Include Data
    • Includes Calc Scripts
  • Data Export supports size up to 400 MB
  • Text Lists Support in the Cube Designer Wizard
  • Text Lists supports up to 64k entries


There are also enhancements for formulas in unstructured data loads

  • Allow for Hints for Measures
  • Can refer to specific column names as formulae
  • Provide Excel based Column References as formulae
  • Mix of both
  • Support for new implementation of XREF/XWRITE
  • Allows for migration of applications from one instance to another without the need for location aliases

LCM Improvements

I've saved one of the big changes for last. You can now use an LCM export from PBCS and FCCS to build an Essbase cube

  • Support for LCM Extracts from EPM using EPMAutomate
    • The OAC Essbase LCM Import using CLI extracts the Essbase Cube contents from the Export done using EPMAutomate
      • PBCS
      • FCCS…
Other general LCM Enhancements include:
  • Support for Partitions (Use Trusted Connections, within the same instance) – Earlier required manual intervention of changing partitions
  • Calc Associations – Users are manually added (Cloud to Cloud)
  • Support Audit Trail – preserved and imported (Cloud to Cloud)
  • Roles are migrated (Cloud to Cloud)
  • LRO (Not Supported) – Can be done manually using MAXL
There are some other minor things like a MaxL script library and template support.

It is amazing how far OAC/Essbase has come in such a short time. I look forward to the next round of enhancements.

Wednesday, August 9, 2017

OAC - The first patch

The honeymoon is over. Well not for my Boss, Edward Roske, he got married in Paris last week. So for him it is just starting. Congratulations Edward. For us using OAC, it is over because the first patch has arrived. It is not a virgin version any more.  That is a good thing for us.  While a lot of things in the patch were talked about at KScope as “coming soon” and was put in to the “Look Smarter than you are with Oracle Analytics Cloud” book, I feel it is good to go over what was released in the patch on 7/29/2017. The version is 17. Interestingly enough, one thing that did not work was getting the readme for the update.

There are a number of enhancements in this release. First, we can now see the logs generated without having to FTP onto the server and search for them. If you are at the Server level (having not selected an application or database) Select Administration and you will see the logs structure.

A couple of things to note.
1. You can only download the logs, they re not interactive on the screen
2. It is best to use something like Notepad ++ to view the output. Since this comes from a Linux box, notepad does not put display it well.
Second, A Rules file editor has been added so you can maintain and create rules files. When you select a database, you can go into the scripts card and click on the second 
Once there, we can create a new rule or edit an existing one.
Currently, you can't see the data like you can in EAS, but this is much better than the JSON editor shipped in the initial release (Although, I wish they kept that as an option as in some cases it would be nice)
Next, a UI has been added for creating transparent partitions.  I believe they are working on replicated partitions, but don't quote me on that.
In addition, you can now export Data and calc scripts from the UI
And here is what it looks like in the exported DBX file in excel. Notice if the file is over 1 meg, it saves it to a file instead.
another change to the UI is you can now set Location Aliases for using XRef (which also means you can use XRef)
Non-UI changes include:
  • The ability to use MaxL files in EssCLI (there will be a whole blog post on this)
  • Application timeout settings. If an application is idle for 15 minutes it will stop. In the future this is supposed to be configurable. (for 3 minutes to 2 weeks)
  • A new Sub-select for MDX in BSO (yes I said BSO) cubes.  The information I got is that it can be sued as a filter mechanism to reduce processing that requires partial aggregation. I've not had a change to delve into this further or see the benefits or ramifications. Once I do I'll talk about it.
  • Finally CalcLockBlock has been deprecated in this release. 
BUT wait, it you buy into this now, you also get at no additional charge, updates to the Cube Designer extension in Smart View. Alas, I don't have screen shots as I haven't had time to download the latest version but the improvements include:

  • Admin capabilities -> Delete App/Cube
  • Search Capability in Hierarchy viewer
  • Support for Generation Names
  • Support for asynchronous jobs
  • File Options - Setting the file delimiter, Skip Rows and Ignore Columns
  • Flip the sign for members on load (selecting the dimension and UDA)
  • Support for Text Lists 
While I'm sure there are bug fixes as well, (I know the corrected a problem with CDFs) until I get the actual readme, I'm not sure what fixed..
I expect the next release will occur soon, and when it does I'll blog about some other cool features that are supposed to be in it.

Friday, July 28, 2017

The Hidden Dimension

Let me digress, Oh wait, I have to tell you something before I digress from it. The other day on OTN (or was it Network 54?) there was a question posted on how a user could add commentary to Essbase in OAC.  I had mentioned Linked Reporting Objects(LROs) and verified that it worked in OAC.

When the user tried, they got an error. I was able to recreate it:

In my original test, I was using a non-sandbox cube. When getting the error, I had switched to a Sandbox enabled cube. it was not making sense to me, then I remembered to look at the dimensions. Here is what I saw:
You will notice in addition to the Sandbox dimension another dimension was added "CellProperties".  This is not an attribute dimension but a real "Hidden" dimension in that it acts like an attribute and does not appear as part of a standard retrieval.
This dimension is only added when Sandboxing is enabled. Looking at the member I saw there were three children:
When I added the member EssValue to my sheet LROs could be added. So what is this dimension and what is used for? I put all three members on my retrieval sheet and did a refresh.
EssValue returned the data value that is part of a normal retrieval. This means to be that "CellProperties" must default to this value. EssStatus gave me a the number 1. Hmm what does that mean? If I cleared out the member both the EssStatus and EssID went to #Missing. Entering a number and I got :

It changed to a 2. I remember in the beta talking about this. If I remember correctly, 1 means it was loaded from a dataload. 2 means user input (Submit). I believe a 3 means it is the result of a calculation.
The EssID, is really meant for relationships in sandboxing and approvals and really has no value to an enduser.
So you get two tips in this post.
1. How to get LROs to work in a Sandbox enabled cube
2. What that mysterious hidden dimension is and what it is used for.
OK, now I can digress

Thursday, June 29, 2017

Smart View and Office 2016

I'm just returning from KScope 17 and while sitting in the airport, decided to talk about something I was asked multiple times during the week. There are multiple instances where people have issues with Smart View and Office 16 (or 13 or 10). while I am no expert in this, Oracle has a number of  white papers and tips and tricks for improving performance. I take no credit for them but list them below to hopefully help you out. As a note, The Smart View team really is interested in issues you have so if you run into something, submit an SR or enhancement request (If you are stuck, respond to this post and I'll try to help you navigate Oracle to get your issue resolved, no promises on results)

Flickering issues

How to disable Toobar transitions in 2013 and 2016:


Disable Animations:


Memory and Performance


Http Session Timeout:


If these issues do help, please post as well to let others know of your success.

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