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.

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.  

Monday, December 12, 2016

An undocumented Update in the FCCS Dec-16 release

The other day, I blogged about the changes in the Dec-16 release for FCCS.  As it turns out there is a bug that was fixed (well partially fixed) that people might want to know about. It relates to reporting currencies. First a little history. Back in August, I submitted an SR which turned into a bug.  The issue was for the currency dimension, specifically when you create a reporting currency.

To do that, I went into the currency dimension and simply checked the reporting currency box for the currency I wanted.

The next time the Essbase database was refreshed, I had a new reporting currency. Nifty. The member name for the new reporting currency is CAD_Reporting, no problem, The Alias for the member is "CAD"  Hmmm the alias is the same as the member name of the input currency. If you tried to retrieve data in Smart View with Aliases turned on, an error popped up about an ambiguous member name can't be resolved.  
I know what you are saying (I can read your mind). Just remove the alias from Cad_Reporting and all will be good.  Nice idea, but unfortunately, we can't edit the aliases for reporting currencies.
In the Dec-16 release although it is not documented, the issue is partially resolved. Now when you retrieve from Smart View you will not get an error about the name. if I retrieve the following sheet (Of course my POV is set to retrieve data properly) I get:

Looks good. If I switch to aliases (or just type in Cad_Reporting while Aliases are turned on) I get:
No error, but now which is which? Both say CAD, but the one on the right is really the alias for CAD_Reporting and the one on the left is the member name for the CAD input currency.   So at least now we don't get the error retrieving the data, but it will be a bit confusing for the users. They really have to put in "CAD_Reporting" and not the Alias CAD even when they have aliases turned on.  It should not be too bad until they completely fix it as the input currencies are not really used much, but expect to get questions about why what appears to be the same intersection provides different results.

Thursday, December 1, 2016

FCCS Update - Dec 16

In my blog post A Glimpse into FCCS I talked about haw alternate rollups are not allowed in the Entity dimension because of how Oracle is doing the elimination logic. Well, one of the big improvements with the latest patch is the ability to have alternate rollups.
From the read me
"One entity can have multiple parents, and contribute differently to each parent. These members are called “Partial Shared” entities, where only a part of input data is shared across all instances of the entities".
This patch/upgrade release also includes  a number of other enhancements including:
Currency translation for supplemental data. This data can now be entered in local currency and be translated
Move of rate override members from the Data source dimension to the Consolidation dimension. Although not spelled out, this was a bug we encountered where historical rates were not being applied properly. This change allows different override rates to be applied to different source members. Three new members have been added to the consolidation dimension FCCS_Overrides, FCCS_Rate Override, and FCCS_Amount Override.

In Data Management, FCCS is now a source system- With this change FCCS can be used as a source for other cloud systems like PBCS( Planning Cloud Service,) EPBCS(Enterprise Planning Cloud), ARCS(Account Reconciliation Cloud Service)  and PCMCS (Profitability and Cost Management Cloud Service). In addition, you can push data to a flat file for use in other systems.

Data Management now also supports multi-period loads. This allows you to have periods on the columns in your data file.

There were a couple of bug fixes as well. dealing with member tagged as saved assumptions and Flow not accumulating over time and Data entry intersections not always clearing for parent members.

As you can see the FCCS team has been very busy adding needed enhancements.  It just gets better and better.

Wednesday, November 23, 2016

Essbase patch is here

Since the great pumpkin has gone to bed and we are ready for Thanksgiving gluttony in the US, I thought I would give you a little treat.
Oracle released the latest patch for Essbase and Essbase Studio These patches have no real new features but a number of important bug fixes. In Essbase, the two that caught my eye were:
"24675006 - In some cases, a Dynamic Calc member can return different values on subsequent refreshes"
"24439234 - In some cases, incorrect results will be returned when retrieving results from SmartView Client on an Aggregate Storage database."

Based on that alone, I would patch my system.

I've heard from a number of users of Essbase studio how Drill through reports that used to work, no longer do. They will be happy with this patch as the big bug fix is:
"24321974, 23222585 - Unable to run Drill Through reports when a member has more than 999 descendants"

If you want to see all of the fixes for Essbase, Studio, APS and the client then you can look at Oracle Blogs. Note to actually read the readme files you will need an Oracle ID.

Hopefully Oracle has a nice Christmas present for us with a new release that has additional features. The irony of a Jewish person wanting Christmas presents for Oracle. Right up there with believing in the Easter bunny.