A Guest Post Reviewing Developing Essbase Applications Hybrid Techniques

OK, I’m just the middleman here. Tracy McMullen asked me to post her review of Developing Essbase Application: Hybrid Techniques and Practices. These are all of her words. I’ve not changed anything in this review except to put quotes around it.

“Truly Taking Your Essbase Knowledge to the Next Level
You know how sometimes when a sequel to a movie or good book, comes out… it just stinks compared to the first release? For instance, Dumb and Dumber To or Speed 2… Other movies and books know that releasing a sequel to its classic first release just wouldn’t be the same and they smartly do not follow up their product with a sequel (like Old School or E.T.). Cameron and team’s first edition of Developing Essbase Applications: Advanced Techniques for IT and Finance Professionals was valuable addition to all Essbase administrator’s libraries. So how does their second edition (or “sequel”), Developing Essbase Applications: Hybrid Techniques and Practices fair? This new book follows in the footsteps of sequels like Empire Strikes Back, The Godfather II or The Dark Knight, taking the great things about the first edition and then improving upon them in the following release. They’ve hit another homerun, providing an invaluable tool to the Essbase community.
This Essbase book has something for every type of Essbase consumer, from the super techy Essbase administrator to the IT / infrastructure team supporting Essbase to the brand new Essbase administrator and finally the end user. So buy at least one (or more) for your organization and share with your Essbase stakeholders. The Developing Essbase Applications Hybrid Techniques and Practices writing team (John, Martin, Tim, Cameron, Glenn, Mike, William) has given us this handy toolkit with code examples and reusable code and detailed explanations of simple to complex topics like design best practices, how some of the new features work under the covers, and detailed “how to” steps so that you can follow along. When it comes to testing new features, they’ve done the testing for us.
A few of my favorite parts of the book: John provides general guidance on Essbase server configuration with Exalytics environments and seeing the testing results of just how powerful Exalytics can be. If you are looking to upgrade and purchase new hardware, read the Exalytics chapter! This might give you some of the ammunition to make your case for Exalytics in the purchase debate. I loved Martin’s Magical Number 7 and how it applies to Essbase dimension design. Even experienced admins can benefit from this design best practices chapter. All of the new buzz around hybrid cubes is really exciting! But what is it exactly? Tim and Cameron dissect the new hybrid option for Essbase and share actual performance results. You’ll find some interesting (and surprising) results. Glenn’s chapter on SQL for Essbase is a must read for every Essbase administrator. It helps both the IT developer and the business user understand how SQL can be utilized for loading data into Essbase, extracting data from Essbase back to relational targets. If you’ve ever wanted to load test your Essbase environment, Tim’s chapter will show you the way to accomplishing this tricky task. As of mom of two sibling girls, I completely appreciated the analogy OBIEE and Essbase provided Mike’s chapter, Copernicus was Right. Essbase isn’t the center of the universe? Why symmetry matters? Mike rocks the boat a little in this part of the book but shows how to really address challenges of Essbase and OBIEE integration. If you aren’t familiar with Dodeca, check out Cameron’s chapter on this alternative tool for end users to interact with both Essbase and relational sources. William’s Smart View chapter breaks down all of the different query options available within Smart View (did you even know these options existed?). He provides a super helpful comparison chart then deep dives into the content with examples on the different ways to use Smart View to interact with data.
Developing Essbase Applications Hybrid Techniques and Practices is not just a high level book. This is a roll up your sleeves and jump in the weeds kind of book. There is a LOT of information which can be overwhelming at times (but really that is a good thing). Reread if you need to because the all of the details are there to learn about Essbase. I concur with Steve Liebermensch who wrote the Afterword, add this book to your shopping cart and pay for it already! I’m certain you will learn something new (likely a lot of something new’s) that will help you in your journey with Essbase. “

Essbase 12C is here or is it?

A few weeks ago when I was at Oracle Open World, there was a big to do, partially started my me. Someone had posted that Essbase 12C had been released.  Thinking quickly, I immediately found the readme and saw that EAS and Essbase Studio were not supported. I couldn’t fathom that, going back to command line to  build cubes? and how would load rules get created.
I was hoping that perhaps, it had the new simplified interface that Gabby Rubin has been hinting about (and I actually got to see at OOW  -- It is awesome), but alas no.
It turns out this 12C release is for use with Oracle Business Intelligence Enterprise Edition (OBIEE) 12c that was released the Friday before.  It does have cool things we can expect later in our own EPM version of Essbase.

First it is the new JAVA kernel, so I’m happy to let the OBIEE people debug it for us. This Java is supposed to be first released as EssCloud service. so don’t expect it any time soon in-house. I would say on premise, but apparently Larry is rebranding on premise to be some kind of private cloud, or so the story goes.
Second, It supports all of the functions including cross-dim in hybrid mode. That is huge. It means they figured out cross dim issue performance. I can’t wait to try it.

Finally, They are putting Essbase in memory. The real use case for Essbase with OBIEE is for OBIEE to spin off Essbase cubes for caching data to make OBIEE reports faster. we won’t be able to get at these cubes, but OBIEE reports will be.
So for OBIEE (at least) a REALLY cool version of Essbase is available, but for us EPM-ers we will have to wait. may uncle Gabby, will give me a version for Hanukkah to play with. or a version for each night. I love new toys. 

Exalytics X5-4 Fast and Furious

I love going to KScope because I learn about new features and products. This event was no different. In the Sunday symposiums with Oracle there was a discussion on the new Exalytics X5-4. It was only Sept last year at Open World when the X4-4  was announced . Edward Roske talks about it in his Blog. It was a big deal then. With the introduction of the X5-4 only 9 months later it becomes even bigger, better and “Badder”. With the X5-4 we go to a max of 72 cores, up from 60 and more memory. In addition to more cores, the X5-4 supports a new NvMe High bandwidth flash technology that improves throughput by 2.5 times. I won’t bore you with the details if you want to read about them , here are the specs

To me the most remarkable thing about this is you get more and the price has not increased. All of the way back to the X3-4 the price has remained the same. With a list price of $175K it is what I consider cheap.

As John Booth mentions in his Blog, you can get this as an X5-2 configuration as well offering additional flexibility. Note I had a correction from John. The X5-2 was more a wish from him than a reality. While you could create a X5-2 using sub-capacity licensing, you are still paying for the physical cores (Thanks Steve Libermensch for that clarification)

For us in EPM it keeps getting better and better.

Essbase Studio 11.1.2.4.002 patch

Well, I survived KScope. It was a very good event with participants getting over 175 sessions related to EPM/BI.  I sat in a number of sessions and was impressed with the quality of the speakers and presentations.  I also had the opportunity to speak in 4 sessions and I think they went pretty well, at least from the questions people asked.

Patch 11.1.2.4.002 came out the other day and I read through the readme file. There were only two changes and one document change. 

The first bug fix relates to a problem with stored dimensions (I assume ASO) where it would not let you use external consolidation operators. 

The documentation change fixes the statement that you can drill through on any level of a hierarchy including the top level. That is incorrect, you can’t drill through from the top member of the hierarchy (The dimension name).

The most intersting bug fix is the second one and I’m surprised they are calling it a bug as it used to be described as a limitation. When doing a drill through report on a recursive hierarchy, the drill through would fail with an error message if there were more than 1000 level 0 members returned in the query.  For recursive queries, Essbase Studio created an IN clause with the list of level zero members under the selected member. The 1000 member list was a limitation for Oracle as that is the maximum number of members allowed in an In clause. I’ve not been able to test this yet and wonder how development got around that limitation.

I guess the moral of the story is , even if something is listed as a product limitation, still submit bug and enhancement requests and it is very possible what you need will be changed.

Don’t believe everything you read (again)

I got an email from my boss Edward Roske about an entry in the Tech Reference. He is working on a cool super secret project (all will be Reveled and revealed  at KScope) and he asked me about something he saw in the Tech reference on the AGGMISSG command.

For those of you who don’t like to read the tech reference I’ll save you the time going to it.

SET AGGMISSG

Specifies whether Essbase consolidates #MISSING values in the database.

The default behavior of SET AGGMISSG is determined by the global setting for the database, as described in the Oracle Essbase Database Administrator's Guide.

Syntax

SET AGGMISSG ON | OFF ;

Notes

SET AGGMISSG commands apply to calculating sparse dimensions.

Example

SET AGGMISSG OFF;
CALC ALL;
CALC PERCENTS;

See Also

SET Commands

 

What struck him as funny and me as well was the statement

SET AGGMISSG commands apply to calculating sparse dimensions. (my highlighting).

Neither he nor I could remember it acting that way. I reached out to MMIP Cameron Lackpour and he opened his System 9.3.1 tech reference and it said the same thing.

Thinking this can’t be right, think Planning with upper level periods allowing input and being dense, I decided to test it.

Using Cameron’s FDITHWW sample Basic, I cleared all the data and set the upper levels of year to be stored.

I used Smart View to populate the following intersection

(Note Profit shows up because Measures is dynamic)

I then ran the following calculation script:

SET AGGMISSG  OFF;

SET UPDATECALC  OFF;

Calc dim(Measures,Year);

Agg (Product,market);

Here are my results:

As you can see, my dense dimension acted like Edward and I expected, with it ignoring the #MISSING children and keeping Q1 and AGGing it up to Year. This means  the Tech reference is slightly askew.  

As a side, there is something else in the  Tech ref example, If you look there is a statement:

CALC PERCENTS;

I’d never heard of it and a search of the Tech reference has the only reference in the Aggmissg example. Trying to run it gives an invalid syntax so this is inaccurate as well.

I will be submitting both of these opportunities to the Documentation group as they actually do fix these type of errors when they are found.

Moral of the story, Even if you read it in the Documentation, try it yourself and you might be surprised at the results.

ASO calculation bug

UPDATE

Note, It funny how things work out. While I’ve not tested it out yet, a patch set update (PSU) 20859535 appeared today  after I created this post.

Defect Number	Defect Fixed
20806331	MDX formulas are not calculating correctly for parent members of the accounts dimension, which are tagged with time balance properties and compression, in an ASO cube where the parent has more than one child.

This is the bug I reported last month.So it appears it might be fixed. I just have to now test it.

Glenn 5/11/15

I am a creature of habit. I have done the same calculation to put YTD net income into Retained earnings in too many cubes to count. In My ASO cubes, I know that I have to set the solve order higher  than normal for the ancestors of my calculated retained earning  member to get it to roll up properly. and it has always worked.That is until now. I’m working on 11.1.2.3.502 and have run into an interesting issue.

My retained earnings calculation works if I am at individual periods, but does not work if I’m at total Periods. In addition, it works if I am at the single stored member of my View dimension  but not if I expand the View dimension. The stored member value actually changes.  In tracing through the issue, It appears the formula for retained earnings is not firing when I’m at Total Year or when I have multiple member of my dynamic View dimension.

. I was able to find a work around. Instead of allowing the parent of my retained earnings calculation be a natural rollup, I forced it to be a calculated formulaic member that adds up its children. That apparently is enough to force the calculation to occur and it properly rolls up to all of the ancestors.

I don’t particularly like this solution as it means that If the users add a new account then the formula has to be changed as opposed to the hierarchy rolling up correctly.

This is also part of a bigger issue. During my testing of formulas in a “View” dimension, I had issues where the formula would not work at a parent account level, but would at the child level. Oracle has confirmed this bug and I was able to get around it by setting the Accounts dimension as a default higher solver order.

Again while this works, IT is different from every other earlier version. My advice is if you upgrade check your calculations very carefully across all of your dynamically calculated dimensions. Don’t assume things will work hunky Dory.

A quick tip for Dataexport

I love the dataexport function in calc scripts. I tend to use it a lot for both writing data to flat files and to relational databases. I’ve written multiple blog posts on it.

Today, I got an email from a fellow consultant who was having problems with it and needed help.  It took me a few emails back and forth, but I was able to help them. I decided to post it so we all don’t run into the same issue.

The original email was

“There is a sparse dimension that is dynamically calculated in an app.

I want to export a parent which is dynamically calculated, but even when setting the data export with DataExportDynamicCalc ON;  it still exports the level 0 for that dimension. “ If I change that dimension to Dense, then it exports what I want, the parent.  I even fix on that parent member but it still exports level 0 of that parent.”

I first responded asking if the member name was explicitly in the Fix Statement and if SET DataExportLevel ALL; was set.  I was assured it was. I was sent the whole calc and it looked good.

SET DATAEXPORTOPTIONS

{   DATAEXPORTCOLFORMAT ON;
  DataExportDynamicCalc ON;
  DataExportLevel ALL;
  DataExportColHeader "Period";
  DataExportOverwriteFile ON; };

/*EBIT*/

FIX ("1st Pass","Final","Budget", "Actual","FY15", @relative("YearTotal",0),

  "EBIT",
     "ALY","SAP CC 1000","760","U-ctID","NZU72200",@RELATIVE("Cost Category",0))

     DATAEXPORT "File" "," "TESTENABLE.TXT";

ENDFIX;

I was about to write back that that I was stumped and remembered something they said that I skimmed over the first time.  “If I change that dimension to Dense, then it exports what I want”

Hmmmm. I started to think about the difference between dense and spares dimensions and how Essbase works. It worked on a dense dimension. OK, So it pulls in the block and can calculate the dynamic members. OK, that is reasonable.

A sparse dimension. Wait a second, there is no block for a dynamically calculated member. In this case, the block is calculated upon retrieval. By default, the fix would bypass empty blocks.  I looked at the set statement again and it hit me. There was no statement for emptyblocks. I remember it because I always include it and turn it off in my scripts so I know it is off for sure.   I knew there was on and looked it up in the tech reference. SET  DataExportNonExistingBlocks ON|OFF

The tech reference describes this function as:

Specifies whether to export data from all possible data blocks. For large outlines with a large number of members in sparse dimensions, the number of potential data blocks can be very high. Exporting Dynamic Calc members from all possible blocks can significantly impact performance.

again hmmmm. All possible blocks.  I had the consultant add this to their extract set it to ON and try it. I did warn them this could be slow, but they told me it was a small outline.

Lo and behold it worked like a champ. IT is a valuable lesson. With us typically wanting to improve performance we turn things on and off without even thinking about it. Sometimes we have to go back and reevaluate our options.

We all need to thank Applied OLAP

I typically don’t single out a person or company in my blog, but am doing so today. Tim Tow, Oracle Ace Director, Owner of Applied OLAP, Essbase friend and evangelist, on his blog announce the release of newest version of the Next generation Outline extractor .

Why the big deal? Why am I praising him? First, Tim maintains the code for his love of Essbase, he makes no money from it. Second, it cost him money. Time taken from billable work to make changes is a cost, plus, he has his help desk support people assist anyone with a problem again at no cost.

That is all well and good, but the final thing is his responsiveness in improving the product. I emailed Tim on a Wednesday asking about missing features of the relational extract. Tim and I exchanged a few emails about what I would like to see and how I thought it should work.  By Sunday, I had a beta version of extractor with all I asked for and more.  I know from Tim’s questions that I was not getting work he had already planned, but that he had modified the product for me.  After my testing the changes (I found no bugs), he has released it to the Essbase world.

We all need to tank Tim and Applied OLAP for their continued support of the Hyperion community. I don’t work for Tim, but do think his products are awesome. It is nice that he puts as much care into the free products he supports as he does for his fantastic Dodeca product.

FixParallel–How fast is fast?

Addendum to this blog post.

I lied, I lied, but not intentionally. When I wrote this post, I thought it was true and the timings were as I saw them, but upon further investigation, It turns out I had encountered a bug with FixParallel where including the set DataExportRelationalFile  on causes some problem with data exports using FixParallel.  The export only returned data for one node of the hierarchy not the entire hierarchy as it should have.  Unfortunately, I don’t have access to rerun the tests again. This issue was fixed in 11.1.2.4 and I think a patch for 11.1.2.3.5X that had not been installed on the system I was using. I’m guessing the performance will still be faster than without it, but can’t give you correct numbers

Sorry if I misled you

*****************************************************************************

I have finally been able to use FixParallel introduced in 11.1.2.3.500 on an Exalytics server. I’ve used it for for calculations and dataexports, so how fast is it and does it really make a difference?

For my allocations calculations, I really can’t tell you how much of a difference it made, but I know it was a lot faster to do my allocations with FixParalled than without it. I just didn’t capture the times.

For my DataExport, I was able to measure the difference.  I was exporting 1083702 level 0 blocks in column format with a block size of 9984b. I created a Dataexport calc script and set CalcParallel to16 in the script. Running it took 336.95 seconds. I thought that was reasonable, but I wanted better.

I changed the script to use FixParallel using 16 threads across my location dimension which has abut 800 members. The calculation took 9.94 seconds. If I multiply out that number by 16 I come up with 159.04 seconds so it it telling me the FixParrallel calculation is improving performance more than just the parallelization of the script.

What I did not expect is; just like ParallelExport, the FixParallel dataexport created a file for each thread, so instead of one file I ended up with 15. They were named with a suffix of _T? where ? was a number between 1 and 15.  (not sure why I didn’t have 16 files).  I also don’t know what would happen f the file size spanned 2 gig. Would it append a _1 to the file name? I tried reducing the number of threads  to 3 and reran the script. Alas, I ended up with only three files so I can’t give you an answer. But interestingly the script took 690.63 seconds, much longer that the script without FixParallel, so apparently there it tuning we can do to the script.  I could try including another dimension in my FixParallel, but am happy with my less than 10 second export. Perhaps a test for another day.

So is FixParallel worth it, my testing says YES! FixParallel for me was an awesome new feature and one I will use often.

Everything must come to an end

I finally got my new laptop after having a loaner for over a month so I can now resume blogging (at least more easily).  I’ve been excited about all of the new features and functionality of Essbase 11.1.2.4 and there has bee a flurry of blogs that have described that functionality. Today, Tim Tow posted one feature going away the VB API.   There are a number of other features that you might not be aware of as either completely gone or depreciated so they won’t be supported in the future.

So first what is already gone?

1. Essbase Native Security. Shared services should be used to manage and maintain users.
2. Essbase Integration Services. The writing has been on the wall for this for a while with Essbase Studio taking its place, there is little need to duplicate functionality. Of course along with this goes Hybrid analysis.
3. VB API (Time talks about that in his blog)
4. A number of Configuration file (Essbase.cfg) settings. I believe the functionality has been built into the base product so these are no longer needed.

• PRELOADALIASNAMESPACE

• PRELOADMEMBERNAMESPACE

• PRELOADUDANAMESPACE

• MAXACTIVEUPDATETRANSACTIONS

• MAXTOTALACTIVETRANSACTIONS

So what is depreciated (This means not recommend for use and will most likely go away completely in the next release)

1. Direct I/O Can’t say I’m sorry to see this go. I’ve never had an implementation where it was advantageous to use it and it always seemed to be buggy.
2. Outline change log. While not widely used I do have some clients that use this for SOX compliance so the auditors can track who made outline changes and when. I’m hoping a new method for tracking this will be forthcoming.
3. Currency Conversion applications and Currency partitions. I don’t know anyone who used currency applications. Most consultants I know build it themselves within the primary cube. That said, Oracle announced at Open World last September that they are working on incorporating more HFM like financial functionality into Essbase, so I would expect to see a new method for currency conversion to appear at some point.
4. Data compression types ZLIB and None. Don’t know anyone who used them. I guess it means a certification test question will need to be updated. Really since Essbase already figures out what compression a block should use, these are outdated.
5. Linked Partitions. While a great idea, no one uses them (ok I have one client that implemented them, but doesn’t really use them)
6. the EAS API. again a nice idea to customize EAS, who really needed to do so,
7. Network File System (NFS) protocol on Network Attached Storage (NAS) devices. Who really cares. I think this was for Windows NT which is long gone.
8. MaxL statements relating to security. I believe this goes along with all of the other security changes. getting rid of the Essbase.sec file. I will miss this one as I used it as do some clients to automate some security processes. It means I’ll finally have to learn JAVA and use the JAVA Shared Services API to replicate what I did in the past.  These are the commands going away.

• alter user statement—only the add [to group] and remove [from group] grammar is deprecated

• create group statement—the entire statement is deprecated

• create user statement—the entire statement is deprecated

• display group statement—only the all grammar is deprecated

• display user statement—only the all grammar is deprecated

• drop group statement—all grammar is deprecated except for the from security_file grammar

• drop user statement—all grammar is deprecated except for the from security_file grammar

9. XOLAP is being limited to Teradata, Netezza and IBM DB2. Really these are where it was being used on higher powered machines so it makes sense.

With all of the great changes occurring in Essbase, I am sure Oracle is getting rid of code that makes the changes difficult or had to be modified or maintained with the changes. As can be seen from the list, Most will have minimal impact on users. If there are any of the depreciated features that are critical to your organization, let Oracle know, They do listen. (If you like you can communicate through me). While they may not keep a feature, they may have other ways coming out to accomplish what you need to do.

 

In closing I say Rest in peace to those items already removed 

 

and for those processes on life support get ready for the plug to be pulled