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.

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.

Wednesday, August 28, 2013

Smart View hangs on Studio Drill through issue

It is amazing at least to me,  that I have two posts in less than a month.

Recently I had both a client and another consultant in our firm with the same (or similar) issues. They had implemented Essbase Studio and were using Rill through reporting. In one case, when a number of users would try to retrieve from Smart View , Smart View would hang for all users. It would last a number of minutes before it would give an error message. If the users tried to retrieve again, they would get another message about “the prior request is still running”. The other message that would appear was “Decompression failed”. A third instance, Smart View would hang for exactly 4 minutes, then free up.  If the Drill through reports were turned off, Smart View performance went back to normal.

A number of things were attempted to try to remedy the situation including:

1. Turning off compression in APS Essbase.properties file

2. Updated the registry on the APS server changing the port timeout from 4 minutes to 30 seconds

Neither of these worked and I was befuddled (This is similar to Elmer fuddled but you don’t try to shoot rabbits). Since I was not working directly on this and the client had turned to Oracle support with no real help, my colleague continued to carve away at it. His email to me speaks better of it than I could, so here is his synopsis of what he tried.

“ So we got something to work… However, the answer makes me think of the chicken dance, where chickens are given food at random intervals and start to develop a pattern of behavior from what they had previously done when the reward arrived. And in solving for this problem, that is exactly what I was doing: dancing like a chicken.

As previously stated we thought it had something to do with the ports. I knew ports were refreshed every 4 minutes, so I thought if it takes 4 minutes after SmartView freezes to come back … the answer must have to do with the ports being used and not available. We timed it … and it took 4 minutes exactly every time. Thus like the chicken, I did a little dance.

So we increased the ports, frequency of port refresh … however it did not work. We then checked the ports that were being used and found only 144 were being used when it froze. I then acquired another little move to my chicken dance. I was starting to move.

We tried the following: •Web logic – EPM Managed Servers Tuning

• APS – Essbase Property File Settings

• APS – Logging.xml

• Essbase – Compact Outline

• Essbase Config File

• Java Heap Size

• IE – Timeout Settings / Registries

Then I realized that if I stopped the application and restarted it, that it would immediately become available. No waiting 4 minutes. I was pretty sure that perhaps if I changed something in the Essbase config file I could get it to work. Now I was really dancing.

I started to look for settings that had a 4 minute time out setting… could not find any. I found a setting called Serverthreads … I decided to try it. So the next morning I asked the administrator to restart the server so we could test it. He made one additional change to increase the logging detail. We went ahead and tried it.

It worked!!! Now all we had to do was verify that this was really the fix.

We removed the serverthreads setting and restarted services, and it still worked. Wow, that was strange. What had caused it to work, since it was still working after removing the change and restarting services? We would need to retrace all of our steps.

So then we removed the detail logging. To our surprise it now failed. Wow … I was really dancing now. We tested this again and found that it only worked if we set Essbase log file to show detail.

My guess is that there is some setting that we can adjust so that we do not always have to have detail logging. However, I have been dancing so hard that I think it is time to pass this dance on to Oracle support.

Like random droppings of a positive stimulus I had danced long into the night finding the right patterns to get my next little dropping of reinforcement.”

As for the solution …

What worked was:

In Provider Services :

D:\Applications\Oracle\Middleware\user_projects\domains\EPMSystem\config\fmwconfig\servers\AnalyticProviderServices0\Logging.xml

Original Entries :

  <logger name='' level='WARNING:1'>

  <logger name='oracle.EPMOHPS' level='WARNING:1' useParentHandlers='false'>

Modified Entries :

  <logger name='' level='TRACE:1'>

  <logger name='oracle.EPMOHPS' level='TRACE:1' useParentHandlers='false'>

“Why changing the logging level should have any impact … that I do not know!!  I wish I was smart enough to answer that.

We only stumbled upon this by dumb luck when the Oracle asked us to change the xml log so that we could send them the more informative log.  Like I said earlier … we were attempting things that made sense only to find that the thing that made no sense worked.”

So while I did not find the solution, one was figured out, If you run into this hopefully, you can benefit from our research.

 

On another un-related note, the second part of the Podcast Edward and I did with Kevin and Stewart is available. take a look, it was fun to do and I think informative. Here it is :

 

Monday, August 19, 2013

Pimping my Ride

The other day, Edward Roske and I participated in a podcast hosted by Kevin McGinley and Stewart Bryson called Real Time BI. We spoke with them on integration of Essbase and OBIEE. It was a really great time. If you want to see what I really look like or now monotone I really am or the witless  banter between Edward and me, take a look at part one  on YouTube (http://youtu.be/wwTIml_b4mE) or iTunes (http://bit.ly/QhwuSq)!

Part 2 will be out soon. In addition to being informational, it is also somewhat entertaining.