Friday, May 18, 2012

Hyperion Planning 11.1.2.2 – User Interface



I’ve been trying to figure out a good topic to write about with the release of Planning 11.1.2.2 and I figured the infrastructure piece would have been covered by John Goodwin within 15 minutes so that would be old news.  I realize the installation and upgrade to this version is a very important topic and there a ton of new features and major changes that need to be discussed.  (Yes, Business Rules is gone and only Calc Mgr remains.)  However, one thing that I think does need to be discussed is the new user interface for Planning.  It’s actually a lot more different than the old one than people think.  Oracle says it’s now much more Web 2.0 like and therefore better.  (I thought they said this about the last UI redo but it’s all a bit foggy.)

The bottom line is that if you present your users with the new UI I’d be very prepared for what I would think would be major backlash.  I’ve used the new UI to enter a forecast and it’s very different acting and departs from the old mantra of “Excel on the Web”.  Why, you ask?

It would appear at first pass that Oracle missed the mark completely, however, you really need to dig deeper and poke around in the UI to understand it’s not as bad as you might initially think.  However, the actually selection of a cell and updating of data in it are counter to the way Excel works.

Let’s look at a few examples.

1.  Speed.  I can’t really demonstrate speed with screen shots.  You’ll just have to trust me or try it yourself and let me know what you think.  I can’t really spin this one to be a positive and lack of speed is a big problem when you roll out to the end users.  The one thing I will say is that while the speed is an issue there is a benefit here and that is the bandwidth usage of each form.  Oracle estimates that web forms that were about 2.5 – 3 MB are now around .5 MB.  Not sure if those numbers are exactly correct but it’s close to what I’ve seen.

2.  New pop-up messages.  Every time you save data you get a pop-up window.  While it’s nice to be told that everything worked as it should have, but a pop-up?


The good news is that if you simply click on the form the pop-up will go away but that is counter-intuitive to most users who will believe they need to click the “OK” button before continuing.  Definitely something that should and will be called out during training sessions on the new version.  (I’d be shocked if no users complained about this during training sessions on the new version.)

3.  Lack of appropriate Scroll boxes.  This is actually very interesting and a change in the way most people think about navigation.  There are no scroll bars on the left hand panel of the application.  (They do remain on the right hand panel.)  All we need to do here to demonstrate the difference is to shrink your IE window.  Don’t ask me why the abandoned it on one side of the form and kept it on the other.  Probably something that will change with the next release. 

Here IE is maximized to take up my whole screen.  Notice the Business Rules area. 





Now if you see the same form in an IE window that is not Maximized but instead has to crunch things together a little bit, you’ll notice that the Business Rules section is gone and there is instead a very small double arrow that is a link. 



If you click on the arrows you’ll see a link to open the Business Rules section of that web form.


If you click on the link for this you’ll open the web form’s business rules.



A little different than the old UI and the other side of this UI, but not completely unusable.  The one thing that Oracle doesn’t seem to mind doing is adding more clicks to applications.  Take this example for instance.  You used to be able to launch the rule from the web form with 1 click.  Now you are using 3 clicks.

4.  Date Entry and Updates.  This idea of Oracle being OK with adding more clicks is magnified when you look at the data entry on a web form.  Since this is probably the single most used functionality within Planning the ability to do it easily and efficiently should probably be a driving factor of the design of the application.

In version 11.1.2.2 when you want to update a data cell you need to double click instead of the past versions accepting a single click to activate the cell.  In this version a single click seems to activate the cell because it changes it’s color, however, it doesn’t allow anything else unless you are using a menu driven action.  By double clicking the cell is activated for data entry.  Ok, I’m with Oracle so far to an extent.  To be honest the way the forms worked in the last version as far as data entry seemed to be pretty good and worked well for me.  However, if we have to live with a double click I can live it.  The next piece is where things depart from what I’d call “standard usability”.

When you double click on a cell containing data the cell is activated for data entry but the cursor is on the left hand side of the cell. This is certainly outside the realm of consistency with Excel.  One of the beauties of the old UI was that if you double clicked the form it highlighted the entire value and you could type over it.  Now, not only must you double click to activate the cell for input but you must double click again to highlight the numbers which incidentally doesn’t work because now the commas stay in the cell while you are editing them and the double click to highlight the contents stops at the commas.  This is obviously a nit picky item for Oracle, however, they should understand that this function of entering and updating data make up the vast majority of what users of the system do.  I would further point out to Oracle that Essbase became famous for a lot of reasons but one of them was undoubtedly because of its smooth integration (extension of, if you will) with Excel.

Sunday, March 18, 2012

Essbase SQL Connection Options


It has amazed me for years that people don’t utilize the function within Essbase to connect it directly to relational databases.  Sure, people for years talked about Essbase Integration Studio and now Essbase Studio is supposedly the best thing since sliced bread.  Don’t get me wrong, Essbase Studio is a very good tool and it does enable the functionality of drill through which is very powerful.  The problem is that Essbase Studio can be overkill for some situations and to be honest it can’t really do everything it claims.  One example of this is updating text lists.  Oracle claims that it can be done via Essbase Studio but even Oracle Support admits there is a bug that precludes this functionality from working. 

So, if you just have some relational data and want it in Essbase easily with functionality you already know how to use and drill through isn’t a requirement than maybe the Load Rules SQL connectivity is for you.  The big problem with the SQL Connectivity for Load Rules is that the documentation is less than optimal.  Essentially there are three ways to connect to a relational database via Load Rules and they are all pretty easy to use.  They are via and ODBC connection, a Substitution Variable or an Oracle Call Interface (OCI).

In order to utilize any of these you’ll need to create a new rules file, and then click File and “Open SQL”.


You’ll then be prompted for a database, which is your Essbase database.  I’m not sure why this is, but if you are creating a rules file from a database it should already be populated for you.

You will then be presented with the Open SQL Data Sources window.  This is where you will enter everything you need to retrieve relational data for Essbase. 


In the upper left you’ll see the three connection options. The ODBC connection is easy enough if you have access to the Essbase server and can define the ODBC connection on it.  Even if you are running the Essbase Administration Console client on your machine it will still read the ODBC connections from the server only.  I almost always define the ODBC connection as a System DSN and have never had any problems with it.

To be honest I haven’t tested the Substitution Variable method enough, however, I can tell you that putting an OCI call in a Sub Var and utilizing that doesn’t seem to work. 

The OCI option is the trickiest to use if you haven’t used it before because finding the correct syntax is difficult.  Which is the real reason I decided to write this particular blog entry.  The Oracle documentation for an OCI call states that the syntax is: “jdbc:oracle:oci:@<servername>:<port number>:<SID>”.  (The use of <> is to illustrate an example, you don’t need them in your syntax anywhere I’ve used them.)  However, that doesn’t work in Essbase.  This makes sense since by clicking the radio button in Essbase that you are going to use an OCI call you’ve already told Essbase the beginning of that syntax.  Incidentally, I found that syntax by looking up Oracle Database documentation, as I could not find a single example or reference to syntax anywhere in the Essbase documentation.

What I’ve found works within the Essbase Load Rules is: “<servername>:<port number>/<SID>”.  One very important thing to realize there is that all Oracle documentation seems to say that a “:” should be used to separate the Port number from the SID.  However, I have not had any luck with that syntax and have always had to resort to using a “/”.  I haven’t tested this on an Oracle RAC and it may be different in that scenario.

For those with SQL experience the rest of the window should be pretty self-explanatory.  You can put anything in the “Select” window that you usually would and anything in the “From” window that you would write into a sql statement, including PL-SQL commands.  In the “Where” box you enter any additional statements, including things like, “ORDER BY” or even “UNION” if you’d like.

The entry area at the top right can be ignored for the most part but if you need to fill it out it’s essentially all of your Essbase info.

Once you have everything, hit the “OK/Retrieve” button and you should see data in your new Rule file just as if you’d opened a text file.  One difference is that when you open a text file, Essbase doesn’t automatically assume the first row has column heading whereas the SQL interface will automatically take the columns from your “Select” window and use them as the column headers.

When you save the rul file you will automatically save the SQL you’ve entered.

One note about this is that while you are developing you’ll need access to the app logs for the application you are building the rul for.  The reason is that any problem at all with the connection string or the query will result in the same error message from EAS.  Essentially it’ll tell you to check the log.  It means the Application log and there you will find pretty good messages to help you debug any issues.

Friday, October 14, 2011

Oracle Open World 2011

Well, if you've been following the blogging and news from Open World you've probably already heard about the Exalytics appliance. This has just gone on-sale and features an in-memory Essbase instance as well as the rest of the BI Foundation. It's an incredible offering and I'm sure this will become a consideration for many company's BI needs. The most interesting thing I heard about the Exalytics system however, was not what it can do right now, but what it will include in the future.

As it was explained to me, Oracle has put a piece of software on the appliance that allows for products like Essbase to be moved to an in-memory solution without having to rewrite the software. This adds a lot of potential flexibility to what will be offered on the Exalytics appliance. John O'Rourke from Oracle showed the Hyperion SIG meeting a roadmap of the future EPM products and one thing he noted was that the next release of Planning was going to be available on the Exalytics platform. This goes a long way to addressing the short comings of the BSO architecture underneath planning in a couple of different ways. First of all, if everything is in-memory you should be able to build bigger Planning models without performance degradation. Secondly, if your Planning model is still too big, the functionality to produce ASO models from within Planning will become more viable because that process will obviously become faster. Either way it should make Hyperion Planning able to handle significantly larger metadata models.

That's the big news from Open World as I see it and don't forget to attend Connection Point in November in Atlanta! You can get details from our website, http://wwww.mindstreamanalytics.com

Friday, August 26, 2011

Hyperion Planning: Performance Settings Dimension order - not all dims move

For those of you using Planning 11.1.2 and have tried to migrate pieces of a Planning application from one environment to another you have probably encountered this bug. What happens is that LCM doesn't keep the dimension order properly and when you open Planning in the target environment post migration you can't re-order the dimensions the way you want them.
The ISSUE: The issue is that after the migration using LCM in a classic Planning app in 11.1.2 we were not able to utilize the "Performance Settings" funtionality in Planning to reorder the dimensions for Essbase. The underlying problem is in the Planning relational repository.
SOLUTION: First, you need to query the Planning application's relational database or schema. This is the one for the Planning application, not the Planning system relational database. (If they are one in the same you need to fix that immediately.)
The query you want is below:
SELECT A. DIM_ID, B.OBJECT_ID, B.OBJECT_NAME, A.POSITION1, A.POSITION2, A.POSITION3, A.POSITION4, A.POSITION5
FROM PRD_HYP_PLANAPP1.HSP_DIMENSION A, PRD_HYP_PLANAPP1.HSP_OBJECT B
WHERE A.DIM_ID=B.OBJECT_ID
ORDER BY POSITION5;

This statement should return something similar to the following results:

DIM_ID OBJECT_ID OBJECT_NAME POSITION1 POSITION2 POSITION3 POSITION4 POSITION5
30 30 HSP_Rates 0 0 0 0 0
34 34 Period 2 2 2 2 1
32 32 Account 1 1 1 1 2
89731 89731 Asset Class 10 10 10 10 3
120689 120689 Project 15 15 15 15 4
38 38 Year 5 3 3 5 4
89730 89730 Line Item 9 9 9 9 5
37 37 HSP_XCRNCY 6 6 6 6 6
33 33 Entity 4 7 5 3 7
31 31 Scenario 12 4 4 7 7
89471 89471 Employee 8 8 8 4 8
35 35 Version 7 5 7 8 9
101836 101836 Product 11 11 11 11 11
91033 91033 Data_Type 3 12 12 12 12
103461 103461 Channel 13 13 13 13 13
103467 103467 Tier 14 14 14 14 14
90128 90128 JobTitle 100 100 100 100 100
120690 120690 Program 101 101 101 101 101
120727 120727 Classification 102 102 102 102 102

This represents your Planning dimensions as well as some of the other things that Planning keeps track of when it interacts with Essbase. In this example we are using all 5 Plantypes, 3 standard, Workforce and CapEx. The dimensions for the standard 3 and Workforce are listed here in POSITION1, POSITION2, POSITION3 and POSITION4. These plan types all work fine and can be reordered within the standard Planning web application with no problems. It's the CAPEX cube that we are having problems with and which is represented here in POSITION5. (In order to figure out what POSITIONX column relates to which cube you can move the dimensions in the Planning application and re-query the relational database to see which values changed.) You will notice that in POSITION5 there are two rows with values of 4 and two rows with values of 7. This is the problem and why Planning cannot move the dimensions.
To fix this particular case we ran the following update statements. These update statements re-ordered the dimensions in the order we wanted them, but also fixed the problem of not being able to use the Planning web "Performance Settings" funtionality.
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 4
WHERE A.DIM_ID = '89731'

UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 7
WHERE A.DIM_ID = '120689'

UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 3
WHERE A.DIM_ID = '38'

UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 9
WHERE A.DIM_ID = '89730'

UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 5
WHERE A.DIM_ID = '33'

UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 10
WHERE A.DIM_ID = '31'

UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 15
WHERE A.DIM_ID = '35'

Let me know if this works for you. If you get stuck somewhere along the way, give me a shout and I’ll help you get un-stuck.

Monday, January 24, 2011

OAUG Essbase Today


I just finished giving a presentation for the OAUG on Essbase and it was probably way to technical for the audience, but I had a great time putting it together.  It had a lot of great examples so if you are interested in downloading it you can find it here, http://www.mindstreamanalytics.com/presentations.html

The most interesting topic in my mind is the capability of Essbase ASO to perform allocations and custom calculations.  It's a big leap forward for ASO and definitely sets the stage for ASO to take over more of what BSO is currently used for.  However, the big downside right now is that the ASO calculations can't utilize member functions for the allocations and are limited on the custom calculation side.  Think about having to have a different formula or allocation for every account instead of being able to group like allocation methodologies together with an @UDA() or @ATTRIBUTE() function, or even an @RELATIVE() function.  Once ASO can leverage those types of powerful functions it will really be ready to take over for BSO.  While some of the more advanced BSO calculations like @MOD() probably won't be in ASO for some time they aren't heavily used and to some degree I wonder if it's even worth Oracle's time to recreate them in ASO.  The people that are using those types of functions probably don't need to move to ASO because the data set size is probably smaller.

Well, the NCOAUG conference is coming up along with the FP&A innovation conference in San Diego.  Hope to see everyone at one of them!

Tuesday, October 26, 2010

Welcome to MindStreaming

Welcome to the MindStreaming Blog!  I've started this blog to enhance the dialog around analytics, with a target at what used to be Hyperion Analytic tools and Oracle's Business Intelligence Enterprise Edition.  I also wanted to keep everyone apprised of what is going on in the world of Oracle EPM.  Thank you in advance for reading!