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 18, 2014

KScope Deep Dive input needed

As the time for KScope draws near, I’ve been busy revising my presentations and getting ready to go. Ill be there starting Saturday afternoon. I doubt I’ll get any sleep until next Thursday.

Speaking of Thursday. This year at KScope, something new is being tried. The different tracks will have “Deep Dive” sessions Thursday morning that are 2 hours long. These will be well worth sticking around for. For the Essbase track, a panel like no other will happen. Why do I say like no other, well, Some of the greatest minds in the Essbase world will be on the panel including

  • Carol Crider – Senior Technical Support Specialist, Oracle
  • Steve Liebermensch – Essbase Product Manager, Oracle
  • Mark Rittman – Ace Director, Rittman Mead
  • Tim Tow – Ace Director, Applied OLAP
  • Sarah Zumbrum – Ace Associate, Finit Solutions

Either Edward Roske or I will also be on the panel. Edward my have to sit in instead of me because whenever I get with Carol Crider I get tongue tied. She is the internals support guru that we all go to when we need real help (No Cameron, she dies not have a degree in psychology). Steve Liebermensch knows more about Essbase features and functionality than anyone else I know. Tim is an expert on the Java API and Mark know more about OBIEE than anyone lese I know.  Why I was included on the panel, I have no idea with this esteemed group.

That said, without questions, the group will be sitting there with nothing to say. WE need you to submit questions for the group to answer. Please don’t ask. things like “In my cube I have 29 dimensions and it takes 12 minutes longer to calculate than it did two years ago. How do I optimize it” That is way to specific. But if you asked about the reasons calculations might take longer than they used to. that might be able to be answered.

Your questions can be submitted in a couple of ways.  First, by tweeting with the hashtag #EssbaseDeepDive, or at @CubeCoderDotCom (throwing in the #Kscope14 hashtag will help others to see your question, too).  Alternatively, by email to EssbaseDeepDive@gmail.com.

I look forward to your questions and to meeting many of you at KScope 

(Note, even if you are not going and have a thought provoking question, go ahead and submit it. )

Tuesday, May 13, 2014

Don’t always believe what you read

I was helping another consultant with a calc script as they were getting incorrect results. They wanted to do a @sumrange for a particular intersection of data..  They had coded the statement like :

@sumrange(actual,"no product","Final",.... 

I  asked why they didn't use a cross dimensional operator. They referred me to the tech reference:

SNAGHTML2a2beef8

 

For those of you who can’t read it, the note says

“Member name cannot be a cross-dimensional member combination.”

  Having heard that, I looked for another solution and offered a couple of ideas but I kept thinking about cross dimensional operators. I could swear I had done that before. I asked the consultant to humor me and set the @sumrange to use a cross dim instead of how they had coded it. Amazingly the code validated and ran and actually gave the correct set of numbers.

The moral of the story,  the tech reference is not always right. Even  when things are in print, question them and experiment. This typo cost the consultant hours trying to figure out why the calc didn't work. Think outside the box and experiment

Monday, March 24, 2014

Another post on 11.1.2.3.500

It seems like everyone and their brother (and Cameron Lackpour, the younger brother I NEVER had and NEVER wanted) has jumped in on relating the cool things the latest Essbase patch has to offer. So as not to be left out in the cold, I thought I make a few comments as well.

I think this new patch is a real game changer with a lot of cool features. It will have many of us throwing out old optimization techniques and coming up with a whole new set. We will really have to think out side the box to figure out what is best.

That said, There are a few things you might want to consider. First, although this is listed as 11.1.2.3.500, it has significant changes in it. Why Oracle has such significant changes in something that is just a patch, I don’t know, but it certainly better than waiting for a full release of the product. One supposes you could install the patch to get the features like enhanced aggregation for MDX functions and bug fixes, I would test very carefully before using some of the other new functionality without extensive testing.

Next, I would like to go into a little detail on a couple of the new features. In general, the word of caution I would give is to test extensively if you are using any of the new features. While they can give you significant gains in performance, they can cause you some issues.

Fix Parallel

The idea behind fix parallel, is there are many situations where calc parallel goes into serial mode and we as developers know better. We can use Fix parallel to force the calculations into a parallel mode. This implies that we actually know what we are doing and that there will be no conflicts if we go into this mode. During the beta testing, it was determined that Fix Parallel is not as fast as calc parallel in most cases, but is faster than not calculating in parallel mode at all.

Hybrid Mode

We have all been drooling over this idea since it was revealed at Kscope last summer. The poser of a BSO cube with the aggregation speed of an ASO cube. How this is implemented is you take your sparse dimensions and make them dynamic . In addition, you add a parameter to the Essbase.cfg file to urn this feature on. During the beta, TimG tested hundreds of queries against hybrid mode and most performed very well.

This is a huge game changer, or at least it will be. I say that because this initial implementation is limited. There are a lot of things that will cause the cube to revert to BSO mode. Using Dynamic Time series (DTS), cross dimensional operators in formulas, some very common functions in formulas (a list too long to list here), and attribute dimensions forces the cube into BSO mode.  Frankly, while I think this feature is fantastic, currently it has a limited use case and until some of limitations are removed, tread lightly. Of course if your cube is doing simple aggregation, then go for it an gain the benefits.

Exalytics – writing blocks back to the same location

As Cameron mentions in his blog post, many though this already occurred, and to a certain extent it does. While blocks are written to a new location, Essbase will look at the free spaces to see if a block can fit into a spot vacated by another block, my guess it seldom happens.  Having blocks rewritten to the same location can reduce fragmentation a lot. I’m guessing this is Exalytics only right now because most data in Exalytics is actually in Flash memory or on solid state disk. This is just a guess on my part, but from testing the effect of fragmentation on BSO cubes in the past, I can say  heavily calculated cube (like planning) will have vast improvement in speed without having the constant maintenance of defragging the DB.

 

As I said in the beginning of this post, this release is a real game changer. Oracle development should be commended and applauded for thinking outside the box and leapfrogging to this level of functionality.  I can’t wait to see what improvements are on the horizon. See I’m never happy with what I get, I always want more. Gaby Ruben told me at Kscope, his job is to make us all rewrite or optimization presentations every year or two.  I think he is keeping his word. We all need to re-examine how we optimize given these fantastic changes to the product.

Thursday, February 27, 2014

Undocumented Change to Smart View 11.1.2.5

As we all know (or at least the cool kids know) the changes to Smart View have been coming quickly. So fast that the wise at Oracle decided to decouple its releases from the rest of the EPM stack. That is how we are at 11.1.2.5.200 while the rest of the stack is 11.1.2.3. I think this is a good thing as it allows Smart View to be more proactive in introducing changes to help us do more and better reporting.  I applaud Oracle for doing this.

However, because the changes are coming so quickly, not everything gets documented very well. From the 11.1.2.5 new features documents we see this.

“Change to Display of Duplicate Variable Names

With this release, Smart View added functionality to display fully qualified variable names when variable names are duplicated. This helps to identify variables defined at the global, application, and database levels. “

But what does it mean? One of the cool features of Smart View is the ability to use substitution variables. You are thinking, what is so cool about that, the add-in could always do it and Smart View could do it since version 9X. Well yes, in both cases you could enter your substitution variable like &CurrMonth and when you retrieved, you would get the value of the substitution variable returned when you refresh the data. With this comes the limitation that if you save the spreadsheet, you save the actual value of the substitution variable and not the substitution variable itself. Huh? What I mean is suppose the substitution variable is set to Mar. It saves Mar and not &CurrMonth. 

Starting in Smart View 11.1.2.1.102 a new way to use variables was introduced. HSGETVARIABLE. When used in a worksheet, it would retrieve the value of the variable but keep it as a formulaic member so next month when you changed the variable, your report would update with the new information. Pretty cool!

So lets get on to the change. Suppose I have the following substitution variables:

image

Notice I have both a global and database specific variable with the same name.

In 11.1.2.3.X and below, I could enter

=HSGetVariable(“HSActive”,”CurrMonth”) and when refreshed, get the variable. Note, HSActive means the current active connection for the sheet, I could also put in a private connection name.

image

Starting in 11.1.2.5, the command allows you to put in a qualified name

=HSGetVariable(“HSACTIVE”,”Sample.CurrMonth”) to determine the scope of where to get the variable from. If left blank, it seems to get the global variable. If qualified, it picks up the variable from where you specify

image

The screenshot above shows what the formulas look like, but in reality when you enter them you get is

image

Once you refresh you get

image

The difference between rows 1 and 2 is Row one has been physically been changed to Mar while row 2 is still a formula.  The interesting thing is unless you qualify the application, you will get the global variable. 

Further, I’m using HSACTIVE for the connection name, you can actually use a different connection instead. For example if I created a private connection for Demo.Basic called DEMO, I could use it and it would pull from the Demo.Basic version of the variable even if I’m connected to Sample.Basic.

So here is where it is getting more interesting. In 11.1.2.5.200 I tried the same thing. When trying to use a global variable

=HsGetVariable("HSActive","currmth")

I get an error message

image

and what did not work before supplying both the application and database now does work

=HsGetVariable("HSActive","sample.basic.currmth")

As a test, I deleted the application and database level variables and then tried:

=HsGetVariable("HSActive","currmth")

and now it returns the global variable.

By the way &Currmth stopped working as well. 

Between the two versions Oracle development has apparently been refining how this functionality works. So what worked in 11.1.2.5 does not necessarily work the same way in 11.1.2.5.200 and of course different than prior versions.

Anyway this is a good enhancement to substitution variables and I urge you to give it a try.

Tuesday, February 11, 2014

Formatted columns in Essbase

Am I crazy(Yes)! Formatted columns in Essbase? What am I talking about, we know you format your data in the front end, why would I do it in Essbase.  That is a good question and brings up the topic of this post.  This is one of those items I put into the category of “Little used features of Essbase” an ever evolving presentation I give at various events.  What I am talking about is Format strings that became available with Essbase 11.1. Lots of people jumped on the Text and Date measures bandwagon and that are in use a lot now but few if any have implemented format strings and they can be very useful.

How about taking a date stored in Essbase and returning it as a formatted date in the format you want, or taking a numerical value and returning it as text. Wait you say, I can do that in a text list. Well you can sort of, but formatted text gives you more flexibility. For example , I can tell the format string that if the value of a column is between 0 and 28.5 then return the text “Bad”, if the value is greater than 28.5 and less than 80.3 return “Good” and if it is greater than that return “Great”. Text lists have distinct integer values and can’t do that without some manipulation.

Using one of the examples in the Tech reference

 http://docs.oracle.com/cd/E12825_01/epm.111/esb_techref/frameset.htm?mdx_cellvalue.htm

I took Sample.Basic and enabled Typed Measures

image

I then went into the Variance % measure and added the following format string:

MdxFormat(
CASE
    WHEN CellValue() <= 5 THEN  "Low"
    WHEN CellValue() <= 10 THEN "Medium"
    WHEN CellValue() <= 15 THEN  "High"
    ELSE  "Very High"
END
)

Note, The example in the web page has the quotes as intelligent quotes and you have to change them back to regular quotes or you will get an error something like “Error on line 3 unknown name ?”

So what does the output look like? For help in checking the values, I added a member named Variance % Unformatted.  You can see I now have text in my report that will change as the data does and does not require the results to be integer values.

image

There are a lot of possible uses for this to create more customized reporting.  I should note that this is only possible with Smart View as the Add-in does not support the text output.

Now that I have expanded your horizons, explore the possibilities

Tuesday, January 21, 2014

Smart View Member Misnomer

Believe it or not, I  will actually be updating my blog more frequently in the future. I’ve gotten 3 articles each half written and will be finishing them soon (I hope)! Two of them are on Formatted columns and an undocumented change to Smart view behavior. look for them soon.

But in the meantime, just to prove I’m not dead, Here is  a little tidbit that I have been asked too many times.  In Smart View connecting to Essbase, you have the options for member display of “Member Name Only” and “Member Name and Alias”  (For now I’m going to ignore qualified member names)

This confuses people as when they select Member Name Only, they see the Alias. What this setting really means is “Member Name OR Alias depending what is selected in Alias”. If you select None, you get the member name if you select an Alias table you get an Alias. Simple isn’t it.

During the beta long ago, I tried to get them to change the wording, but Oracle could not come up with anything meaningful that fit into the selection box so we are stuck with what it is.

Wednesday, October 9, 2013

Exalytics T5-8 is here

While I fully expected my boss (Edward Roske) to blog about the new Exalytics box on his blog Looksmarter.blogspot.com, he has been silent about it. Rather than leave you in the dark about this, I decided I can’t wait for him spew the details so I’ll do my best to give you the info.

Prior to Oracle Open World (Sep 12th to be exact) a new price list was available and in the Exalytics section was this entry that we had not heard of before Exalyytics T5-8. There was no press about it, but at Oracle Open World a few weeks later, they talked about the box.

Here is what I found out.  Prior versions were labeled X2-4 and X3-4. Apparently the X stands for Intel , the 2 or 3 for the Chip generation and the last 3 or 4 for the number of sockets.  As Edward mentioned when the X3-4 came out earlier this year, there is an upgrade kit available for the X2-3 to really turn it into a X3-4.

So what is the new machine? It is listed as a T5-8. So T instead of X. Yep it is not intel chips but Sparc T5 processors. This machine runs on Solaris operating system instead of Linus and includes 4 TB of DRAM, 3.2 TB of Flash Storage and 7.2 TB of hard disk. This box comes with up to 128 CPUs, much more than the 40 you can get with the X3-4.

image

I’ve not had a chance to play with this box but have been told the main reason for is is scalability. It is meant for a large number of concurrent users. What I’ve not heard (officially) is how it performs vs. the X3-4.  Historically intel chips have been faster for Essbase than Sparc chips, and the paperwork says nothing about a performance comparison. I’m guessing it is a little slower, but with the ability to consolidate 3-4 X3-4 machines into one, the user scalability should be really good.

So how much will this box set you back? According to the price list, the box itself is $330K, pretty cheap. You do have a cost per CPU and user, which makes it much more, but that is not all that different from the older models.  It sounds worth it to me.  If/when I have a chance to test it out, I’ll let you know more.

If you like my brief summary here, I’ll be talking about Exalytics in more depth at the OAUG Connection Point Conference in our beautiful Capital , Washington D.C. on Oct 23rd. If the spending limit isn’t fixed by then, traffic in the city should be light! (This is not a political statement, just an observation).

Edward or I will also be talking about it at the Hyperion Solutions Road Show in So Cal on Thursday Oct 17th. It is downtown LA, so there will be traffic. If you want more info on that event email Danielle White at dwhite@interrel.com or register at So Cal Road Show registration  This event is limited to current and potential Oracle clients and not to partners, Sorry.  I hope to see you at one of the events.