Essbase 11.1.2.2 Documentation

While I am not the first to notice this, the 11.1.2.2 documentation is now on the web. For how long I don’t know as it was pulled once before. You can get to it from http://docs.oracle.com/cd/E26232_01/index.htm

With the documentation out, the release can’t be far behind. I’ll be looking through it and highlighting some of the cool new items soon.

Update, I found out yesterday the software for Essbase is available as a patch. Go to Support.Oracle.com, log in and go to patches and updates.

Search on the product family Hyperion Essbase and version 11.1.2.1 and you will see

I almost have the changes for the Essbase Studio book done, so it will be available for 11.1.2.2 soon as well.

I find it interesting that they just released the Essbase/Essbase Studio and supporting items (shared Services, EAS) but not Planning, FHM or other products all together. I think that This was holding up the release of Exalytics so they released this now. OBI was released last week so everything is in place for Exalogic.

Stay tuned

A success story

I just got back from the first ODTUG Hyperion SIG User Group Conference and am happy to report it was a roaring success.  There was great attendance and aside from mine fantastic sessions. Floyd Conrad  from Oracle gave a great keynote, I won’t repeat what he said here for two reasons. First there was a safe harbor statement in it and second, I actually missed it. I was doing a webcast at the time.

The conference got great support from Applied OLAP, US Analytics, Linium, and interRel. 4 different Oracle Ace directors, Tim Tow, Eric Helmer, Edward Roske were there presenting as well as others. On top of that the refreshments were really yummy  and give-a-ways were good. Unfortunately, I didn’t win the free pass to KScope12.

Below is Alice Lawrence Hyperion SIG president and conference chair beaming about the successful birth of this baby. She should be very proud of this first SIG sponsored event.

I wish I took more pictures, but completely forgot until it was too late.

I look forward to the next event. Unfortunately, the March meeting in Atlanta had to be postponed till a later time. I’ll keep you posted when I know more about when it will be. 

Hyperion Sig Mini-conference reminder

I don’t want you to miss out as there is still time to register for the ODTUG Hyperion SIG mini-conference in Arlington, Tx  on Thursday, February 23th. It will be at Ranger stadium and promises to be a great event.  The agenda and registration is available at HERE,  The keynote  is being given by Floyd Conrad  from Oracle and is entitled The Future of Oracle EPM and Beyond. There are at least 4 Oracle Ace directors that will be there giving sessions. Where else can you get a keynote and sessions like that for FREE!

I’ve heard serious rumor that there will be a similar event in Atlanta on March 20th, but it is not confirmed yet. I’ll get you details once I know more.

Upcoming Opportunities

There are a lot of things going on and I thought I would let you know about them. First, I’ll be speaking at a Hyperion Solutions Roadshow  with the ever amusing Edward Roske on Jan 24th in Denver. I’m also excited that Toufic Wakim will be giving the keynote. Aside from being a heck of a nice guy and a great speaker, he actually knows what he is talking about and can give great insight into the Hyperion products.  It is at the Hyatt Regency downtown. To see the sessions click here To register for it click here (note: you must register with a company email address)

Second, In on February 14th at 12 noon (Easter time), I will be in a loving mood and will be presenting a webcast for both interRel and ODTUG.It is titled is MDX Practical Examples.  Note, it will be repeated as an interRel webcast on Thursday Feb 16th. The abstract for it is:  

MDX is the direction of the future, but how do you actually code in it? Join Oracle Ace Director Glenn Schwartzberg as he walks through some of the basics of using  MDX in Essbase. This session is peppered with real world examples of how you do multiple cross joins (and why), the syntax for getting descendants, the first child and what you can do in MDX that you can’t do  in Calc scripts. The list goes on and on. This session is a must attend for those who are getting started with ASO cubes and MDX and provides tips and tricks for those who have already been using MDX. To register for the webcast, go here.

Next, If you are in the Dallas Area, or plan to be there on Thursday February 23rd, the ODTUG Hyperion SIG is putting on a half day mini-conference at the ballpark in Arlington. While the agenda is not set yet, you can reserve a spot by going here. There is supposed to be a sister event in Atlanta. I believe the date will be Friday, February 17th, but I don’t have information on it to share with you yet. When I do, I‘ll post another blog entry.

I’m not sure where I’ll be or what I’ll be presenting in March, but in April, I am doing a couple of sessions at the Collaborate conference. in Las Vegas and of course, in June, I’ll be presenting at the Kscope 12 conference. I’ll put more information out on those later.

Of course, while I’m not doing most of them, the interRel webcast series is in full swing with a series on HFM, FDM and the related components. To get a list of them, contact Danielle White at dwhite@interrel.com

Smart View Patch to a patch

In September, Smart View patch 11.1.2.1.102 was released which gave us parity with the Excel Add-in. While it is great, I was informed by a colleague that there is a newer version of the patch available. I checked on Oracle support and sure enough, there is now 11.1.2.1.103 available. It is only for Smart View, there don’t appear to be equivalent patches for Essbase, APS or any other  products. Below are the fixes in this patch. If you go to the support site and look up the patch, there are also known issues that are worth looking at. 

• 6928106

General: Smart View cannot display member names and their aliases at the same time.

• 12828079

General: When Smart View is enabled in PowerPoint, images are displayed as blanks during Slide Shows.

• 12858015

General: When you perform web launch from Financial Reporting with a report that has multiple members in the POV, the POV selection is lost in Smart View.

• 12896500

General: If Smart View is located in a different directory from that specified in the server, Excel may terminate abruptly with this runtime error: "Microsoft Visual C++ Runtime Library…"

• 12957863

General: When you to open or save a Smart View Excel file, you may get this message: "Microsoft Office Excel experienced a serious problem with the 'oracle hyperion smart view for office, fusion edition' add-in. If you have seen this message multiple times, you should disable this add-in and check to see if an update is available. Do you want to disable this add-in?"

• 11779755

Essbase: When you change the POV, you may get this error: “Invalid start location.”

• 12918727

Essbase: If you are connected to a non-unique Essbase outline from a multiple-grid worksheet, you may get this error: "Invalid spread sheet data. An application protocol error might exist between server and client processes."

• 13242869

Essbase: When the Use Excel Formatting option is selected, cells that contain formulas may display the formulas rather than data after you refresh. In addition, cell formatting may change from general to text.

• 12854000

Planning: In the Smart View Panel, Planning folders and forms are not displayed the same order that they are in Planning. Instead, they are displayed in alphabetical order.

• 12902911

Planning: In ad hoc grids, cross-worksheet references in formulas do not work; the formula itself is displayed in the cell rather than the contents of the cell referenced.

• 12908185

Planning: When you search for members using the drop-down menus in the Page section of a Planning data form, Smart View may take an inordinate amount of time to return the members.

• 13001747

Planning: When you try to open Smart View from within Planning, you receive a Microsoft Excel error.

• 13253314

Planning: If the first worksheet of a composite form contains a formula, you cannot submit data from the other worksheets in the composite form.

• 13403509

Planning: After refreshing, column widths are reset even if Adjust Column Width is selected.

• 13419370

Planning: The "The form contains unsaved data." warning mesage is not displayed when you refresh a composite form.

• 13255112

Financial Management: Excel files in which Smart View is enabled may take a long time to open.

• 13401856

Financial Management: If "Member Name and Description" is selected as a member name display option, Smart View only displays only the description.

• 12559490

Reporting and Analysis: When you attempt to export a Financial Reporting query-ready report to Smart View, you get an error.

• 12982945

Reporting and Analysis: When you import a functional grid from Financial Reporting into Smart View, the formatting may be lost.

• 13093388

Reporting and Analysis: After you upgrade to Smart View 11.1.2.1.102, Excel may not launch.

• 13365904

Reporting and Analysis: After you upgrade to Smart View 11.1.2.1.102, Excel may terminate abruptly.

A different way to do currency conversion

I was at a client and needed to do some currency conversion for an application I was writing (BSO). In their case they had over 50 different currencies they would need to convert to USD (and only to USD). Their requirement was no maintenance to the calc scripts would be necessary if a new currency was added. The build of the currency dimension and loading of currency rates was automated in earlier versions of the project.  I got to thinking about the possibilities and the first thing that came to mind was to parse out a UDA and use it in my script. But alas, there is no function to do that. One idea down.

Next, I thought about an external table where I could pass a cost center number and pull in the currency rate for it. I would need a Custom Defined Function (CDF) to do this. This was a possible solution as my cube is built from Essbase Studio and I could write a SQL query to get the currency for any cost center and link that to the currency table to pull in the rate. Sadly, the client did not want to use CDFs in their environment. Plus, I don’t have one written to do that and am unsure of the speed as I have about 20k cost centers it would have to work against.

So finally, I thought of attribute dimensions. The client is on 11.1.2.1 and I remembered seeing some new attribute functions recently, I don’t remember what version but I think it was 11.1.1.1. So I looked through the tech reference and there was what I was looking for. @AttributeSVal. The function returns the string attribute value for the selected member. So I started experimenting.

First a little background. My cube has the following dimensions

Accounts – This is a standard account structure. It contains a balance sheet and multiple versions of a P&L. Each account is tagged with the type of rate it uses for conversion as a UDA (AVG or EOM).  It also has a section for statistical accounts. Included in this section buried are Average_rate, and EOM_Rate. (and a couple of others)

Periods – nothing special here, periods rolling to quarters

Years – just a list of years. Nothing special

Currency – This dimension has two parts. First are two members USD and Local. Data is loaded in as local and has to be converted to USD.  The second part of this dimension contains all of the currency codes used in the system

Scenario – again nothing  Actual and multiple forecast members.There are also some calculated members in this.

Organization. This is their entity structure. I won’t bore you with the structure, but the bottom level is cost center and this is where currency conversion needs to be done.

Products –again nothing special, This is a typical product hierarchy.

To this I added a new attribute dimension called Cost Center_Currency - It had the same members as are in the second part of the currency dimension (all the currency codes) but to make the member names unique, I prefixed them with the three letters “CUR” For example “USD” would be “CURUSD” and “EUR” would be “CUREUR”. I then associated this attribute dimension with the Organization dimension and wrote a quick load rule to populate the associations between cost center and the attribute dimension. I made sure if any cost center was missing an attribute member, I defaulted it to CURUSD. I figured is there was no currency conversion USD was always set to 1 anyway.No harm, no foul.

Now for the fun part, creating the calc script. I won’t bore you with the set statements or aggregations, etc, but just the currency conversion part. I’ll warn you at this point, I have changed the dimensions from the client to make it  generic and am not cutting and pasting code but typing it in by hand, so there could be minor typos in it. 

First a fix statement. I need to make sure I am at level zero Organization and other dimensions. I use substitution variables for the year and period I am going to calculate. I am going to replicate the script so I have different versions for Actual and forecast (I use different budget rates for forecasts).  Also note if the account does not have a UDA then I assume it is not convertible and make USD equal to Local. (This is because there are some non monetary items in the account structure).  Product has multiple rollups so I only grab the primary one and I only want the income statement and Balance sheet from Accounts.

Fix(&Year,&Per,Actual,@LEVMBRS(“Organization”,0),@relative(“All products”,0),@relative(“Income Statement”,0), @relative(“Balance Sheet”,0))

“USD(

IF (@ISUDA(“Accounts”,”AVG”)

“USD” = Local * @MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))->”No product”->”No Organization”->”Average_Rate”;

ElseIF (@ISUDA(“Accounts”,”EOM”)

“USD” = Local * @MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))->”No product”->”No Organization”->”EOM_Rate”;

ELSE

“USD” = “Local”;

END

)

EndFIX

So what does this do. First, I check to see what rate to use.(the IF statement). Next, I am getting the local value for each account. The

@MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))

is taking the value of the attribute dimension for the cost center we are working on and getting a substring of it starting in column 3. (remember when we created the dimension we added a prefix CUR to be beginning of the actual currencies).  Once we have the currency code back, it is returned as a string, so using @member we turn it back into a member name.  I then am using the cross dimension operators to define the intersections in the other dimensions where rates are stored.

The one issue I has was I forgot to tag my “No organization” member with an attribute value at first. When it got to that member, it crashed the calculation.

There you have it, a dynamic calc script to calculate currency conversion that does not need to be maintained. In my case, the dimensions were not huge so it ran pretty quick. I did find if I added dimensions to the cube, it slowed down considerably. I think the best use of this type of code would be in a business rule where you are fixing on one or a few cost centers but it could be used on bigger sets of data as well.

Open World is a wrap

I’m sitting at the SFO airport waiting for an 11pm flight home after a looooong 9 days in San Francisco for the Oracle Ace Directors meeting and Oracle Open World. From my earlier blogs, you should surely have seen the announcement of the Exlytic machine so I won’t repeat it here. I sat through a lot of sessions, most on stragetic direction and/or roadmap. They seemed to blur together as the slides overlapped in many of them. There was talk of many enhancements and potential new products. I say potential because until I have my fat little hands on them, they are just a dream to me.

Interestingly, I think the best sessions of the conference were today (Thursday) when most people had packed up and gone home. I sat through a session on Fusion Apps and EPM and the voice in that session was how important Essbase and the Hyperion applications are to Fusion apps. As others have reported, Essbase is the engine beneath many of the apps and the apps have been designed to integrate seamlessly with Planning, HFM, Financial Close and other applications. Since Fusion apps have now been released, we should quickly see how they work. 

I next sat through a Smart View session and saw interesting things they are for HFM and  what they are working on for Essbase. For OFA customers who are switching to Essbase, there will be (insert standard disclaimer here) a new “Set” retrieval that looks like the OFA retrieves.

I ended the day going to the “It’s a wrap” party where I listened to Berlin for a while, then went and got one of my favorite dinners. There is a restaurant in SF called Cioppinos that of course serves the fish stew cioppino. It was a great way to end my time in SF.