So Long Performance Point

Some of my friends in the Hyperion world thought they were jumping-the-line by getting a headstart on turning their attentions over to Microsoft Performance Point. Well, it looks like they jumped too fast. Microsoft made a somewhat surprising announcement last Friday. According to Visual Studio Magazine:

"Microsoft will discontinue its PerformancePoint business intelligence product and will merge its function into SharePoint Server, the company said late last week."

The information for this statement apparently came from a post on a Microsoft BI blog posting at http://blogs.msdn.com/bi/archive/2009/01/23/microsoft-bi-strategy-update.aspx.

The OLAP Report has written a report detailing the reasons at http://www.olapreport.com/Comment_Bizdemise.htm.

To my friends who had left to concentrate on Performance Point, welcome back! I wondered why I had seen you all answering questions on the Hyperion forums recently.. <BigGrin>

Using EssCalc in Excel

There was a question on the Network54 board about our EssCalc component and how to use it in Excel. I didn't know anyone was still using the code, which was last touched in the VB5 days, but I thought the answer may make a good blog post.

EssCalc is a free utility that I wrote many years ago to show how to create and execute custom tokenized calculations from VB. It is, however, just as usable from Excel. To use it from Excel, follow these steps:

1. Download the code from the downloads area of our website at http://www.appliedolap.com/.
2. Extract the code to a directory.
3. In the VBA editor, import the file CEssCalc.cls into your project.
4. Confirm the API declarations in CEssCalc match those for your version of Essbase.
5. Import the file essxlvba.txt to get the Excel VBA declarations.
6. Instead of using the code in modMain to get a context handle (hCtx), use the Excel functionEssVGetHctxFromSheet.
7. Use the class to create/run a calc.

Here is the sample code that works on my system (including a calc script with a valid syntax):

Sub RunCalc()
Dim oCalc As New CEssCalc ''' calc object
Dim hCtx As Long

On Error GoTo ErrorHandler

''' get the hctx
hCtx = EssVGetHctxFromSheet(GetSheetname())

''' the context handle is required
oCalc.hCtx = hCtx

''' this is how you get a file off the server
'oCalc.CalcFile = "Test"

'oCalc.CalcFileLocal = "c:\temp\test.csc"

''' This is how you create a script in code
With oCalc
.AddLine "FIX(""T.MARKET"", ""T.PRODUCT"",""T.SCENARIO"") ", True
.AddLine " CALC DIM(""Measures""); "
.AddLine "ENDFIX;"
End With

''' this is how you show the calc string
MsgBox oCalc.CalcString

''' set the process state check to 2 seconds
oCalc.Interval = 2000

''' this how you replace one token
oCalc.ReplaceToken "T.MARKET", "New York"

''' replace a bunch of tokens (if they exist)
Dim cTokens As Collection
Dim cReplacements As Collection

Set cTokens = New Collection
Set cReplacements = New Collection

cTokens.Add "T.PRODUCT"
cTokens.Add "T.SCENARIO"
cReplacements.Add "Cola", "T.PRODUCT"
cReplacements.Add "Budget", "T.SCENARIO"

oCalc.ReplaceTokens cTokens, cReplacements

''' see the calc string again with the rest of the tokens replaced
MsgBox oCalc.CalcString

Exit Sub

ErrorHandler:
MsgBox Err.Description
End Sub


''' get the sheetname in Essbase required format
Function GetSheetname(Optional oSheet As Worksheet)
If oSheet Is Nothing Then
Set oSheet = ActiveSheet
End If

GetSheetname = "[" & oSheet.Parent.Name & "]" & oSheet.Name
End Function

What I realized after looking at this code is how old it actually is.. I wrote this code over 10 years ago as I recognize portions of it from our ActiveOLAP for Essbase 1.0 product which shipped in 1999.

By the way, to do this same thing in our Dodeca product takes zero lines of code. We have a feature called Workbook Script which would allow you to attach one or more tokenized calc scripts to an event. For example, you could attach the calc scripts to the 'WorkbookAfterSend' event which would cause Dodeca to automatically replace the tokens in the script and run the calc whenever a user presses the Send button (and after all Send Ranges in the workbook have been successfully sent to the Essbase server). In other words, Dodeca makes running a custom Essbase calculation much easier.

IOUC Meeting / ODTUG Board Meeting

Sorry I have been off-line for a few days but I traveled to Oracle Headquarters last week for the International Oracle Users Group Community ("IOUC") Leaders' Summit meeting and for the Oracle Development Tools User Group ("ODTUG") Board of Directors meeting.

The IOUC is an organization of Oracle User Groups from around the world and the purpose for the meeting was for the various user groups to meet with Oracle user group coordinators. The Summit is organized by Mary Lou Dopart, Senior Director, Global Customer Programs at Oracle. Mary Lou has helped us in the Hyperion developer world by securing John Kopcke, head of the Oracle EPM Global Business Unit and former Hyperion Chief Technical Officer, to give the conference keynote to all Kaleidoscope 2009 attendees. Some of you may remember that John had agreed to do a keynote for us at Kaleidoscope last year but that, in the end, he was not able to attend. This was in no way a reflection of John, but more a case of those of us from the Hyperion world didn't understand how Oracle works with their executives in scheduling these types of events. As luck would have it, John was scheduled to be elsewhere 'through the proper channels'. This year we used the proper channels! Mary Lou is also working to confirm the keynote for the Hyperion Symposium at Kaleidoscope.

The other purpose for the meeting is to foster greater cooperation between all of the various user groups. Networking and the sharing of ideas between groups is helpful and productive for all involved. I had an opportunity to meet with the other Hyperion related board members, Larry Costello and Diane Maluzhinsky. Larry works for Textron has been elected to the Board of Directors of the New England Oracle Applications User Group. Diane works for General Dynamics Land Systems and is working with the Board of the Michigan Oracle User Group. Congratulations to both Larry and Diane and I look forward to working with you!

The ODTUG Board of Directors meeting started Friday afternoon and lasted until noon on Sunday. The agenda for the meeting was very busy and included discussions on the upcoming Kaleidoscope Conference being held in June of this year in Monterey, CA. I was recently elected to be Treasurer of ODTUG and was thus responsible for the Treasurer's report. We also discussed potential locations for next years Kaleidoscope. Although I can't disclose any of the locations discussed, where would you want the conference to be?

In summary, we have many places where ODTUG can help the Hyperion development community. It is my hope that more people will volunteer their time to help us fulfill that mission.

Technology Trends (According to Accountants)

Most people reading my blog probably don't realize that I am also a Certified Public Accountant, or "CPA". It has been tremendously helpful in my Hyperion-oriented career as I understand the needs of financial software users. When people find out I am a CPA, usually the first question they ask is "Do you do your own taxes?" I don't.

As a member of the American Institute of Certified Public Accountants, or "AICPA", I do try to keep up with all of the information they send out to members and this morning I saw something that I thought I would share here. The "InfoTech Update" newsletter had a section entitled "Six important emerging technologies for accounting". Interestingly enough, three of the six items have quite a bit of relevence in the Hyperion world:

• Portals
• Virtualization
• Cloud Computing

Portals have been part of the Hyperion world for a number of years now so they really aren't emerging, so to speak, in our market.

Virtualization has been a hot topic over the past couple of years. For those of you who don't know, virtualization is a technology where a 'virtual machine' runs an operating system separate from the 'base' operating system. One of the leaders in the virtualization market is VMWare which has had products on the market for many years. Competition is hot and even Oracle introduced the "Oracle VM" product with quite a bit of splash a couple of years ago. We have some customers who have had Essbase on VMWare for over five years but it is really just coming into the mainstream now.

We use VMWare daily in our company. We support Dodeca on every version of Essbase from 6.5.3 through 11.1.1.1. We have a library of VM's targeting each Essbase version and a couple of quad-core servers running them so our developers can easily access any Essbase version with minimal effort. In fact, it takes less than a minute to change a connection from pointing to 6.5.3 and instead point it to, say, 9.3.1.

I expect the virtualization trend to continue. My hope is that Oracle starts delivering a version of the Oracle VM with the Oracle EPM stack preinstalled.

Cloud Computing takes virtualization to a new level where not only the Operating System is 'virtualized' but the hardware is virtualized as well. This is typically accomplished by having the VM running on hardware accessible via the web. Amazon has opened business in this arena and Microsoft is following suit with its Azure platform which is a Windows OS hosted somewhere on the web. When I first read about Azure and requirements for applications running on the platform, it sounded to me like Microsoft had created the OS with Dodeca in mind!

At ODTUG Kaleidoscope 2009, we have scheduled a presentation on Cloud Computing with Essbase. I plan to do a separate post in the near future on that topic.

Useful Essbase Debugging Link

I often answer user questions on the OTN forum and the Network 54 board about issues that they are seeing. Normally, the user posts the error when they really don't know where to turn for help. Often, what they don't realize is, much of the information they need in order to look for a solution is already embedded within the error message. The key piece of information is the error number. Essbase error numbers are generally easy to spot as they are seven digit numbers. Consider this excerpt from my Essbase log:

[Fri Dec 12 13:13:32 2008]Local/ESSBASE0///Error(1012004)
Invalid member name Markett

When you look at the error messages in the log, the root cause of the error may or may not be apparent. In the example above, it is apparent that the member name is spelled incorrectly. Some error messages, however, are not so easy to decipher. Recently, I found that Oracle has a nice reference site that sometimes gives you much better information on an Essbase error:

http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_error/erhelp.htm

The site provides a nice summary for this particular error:



Unfortunately, not all Essbase errors are documented on the site. In those circumstances, it is best to use your favorite internet search engine to search for "Essbase error xxxxxxx'" where the x's represent the error number you are seeing in your logs. It is almost certain that you are not the first person to experience the same issue and you will often find the answer already posted somewhere online. If you don't find the solution posted, then post a question on one of the forums. Of course, if all else fails you can always call Oracle Tech Support!

How Does the Essbase Excel Add-in Work? (Part 3: Why Dodeca is Easier and Better)

In the first two parts of this series, I discussed the basics of the Essbase Query by Example query engine, some of it's benefits and some of it's limitations. Fortunately the Query by Example engine is exposed to developers as part of the Java API which gave us the opportunity to leverage the best of QBE within our Dodeca product but also allowed us to remove some of the limitations.

Dodeca removes or minimizes the effects of the following limitations found in the Excel Essbase add-in:

• More than one retrieval range per sheet is allowed.
• Each worksheet may retrieve data from multiple Essbase databases.
• Extraneous text can be ignored.

Dodeca accomplishes this functionality via the use of retrieval ranges. These ranges, which use reserved range names, define both the cell range that is to be retrieved and, optionally, the database connection to use for the retrieval. Further, you can have a virtually unlimited number of retrieval ranges per worksheet. By contrast, to overcome these limitations in the Essbase Excel add-in, users must manually select the retrieval range by selecting the Retrieve option from the Essbase menu. Alternatively, this process may be automated in the Essbase Excel add-in by writing complex VBA code to retrieve each range. In other words, it is easier and faster to implement multiple retrieve ranges in Dodeca.

The first step is to create the range name. This is accomplished in the Excel template using the Define Names dialog:

Dodeca uses the range name format Ess.Retrieve.Range.x where x is a number. When the administrator uses the template in a Dodeca view, they choose how Dodeca will interpret the worksheet to determine the retrieval range. In this case, the RetrievePolicy needs to be set to RetrieveRanges.

At runtime, Dodeca automatically cycles through the range names that are defined and retrieves each one separately. As I posted in an earlier blog post, one of our customers is using this functionality to retrieve over 250 different retrieve ranges in a single workbook.

Similarly, if the administrator wants to associate that retrieve range with a specific database connection, they would use a similar range name. In Dodeca, Essbase connections are defined as an object in one of the built-in Dodeca metadata editors. Here is how a typical Essbase connection may be look in the metadata editor:

The connection ID, as circled above, is used in the range name to indicate the connection to use for the corresponding retrieval range:



The connection range name is optional in Dodeca. If a range name is not present, the Excel template will be connected to the ConnectionID defined at for the view level:



In this series, I have examined how the Essbase Query by Example concept works and have talked about its benefits, its pitfalls and some solutions. I hope you learned some information that will help you get the most out of Essbase.

How Does the Essbase Excel Add-in Work? (Part 2)

In part 1 of this series, I talked about the normal case of how Essbase Query by Example ("QBE") works. Determining the members represented at data intersections, or datapoints, in Essbase is generally very easy to understand. That being said, there are some potentially confusing layouts that bear some discussion.

The data intersection depicted in the part 1 of this series shows a block header layout that clearly represents all dimensions in the row and column of the data intersection itself. Sometimes, however, the intersection is not so clear. Those cases are best illustrated in the following examples.



What members are represented at this datapoint? Looking left from the datapoint, it is clear that South is the member that represents the Market dimension. Looking up from the datapoint, it is also clear that Qtr4 represents the Years dimension and Budget represents the Scenario dimension. However, the row and column have no members representing the Measures and Product dimensions. Page Fields represent the entire retrieval and thus Measures and Product represent the members from their dimensions regardless of their position on the grid.

Now, consider this layout and try to determine the members for the indicated datapoint.



In this case, the members West and Variance are the only two members in the row and column of the datapoint, so those two members are easy. Further, Sales is the only member from the Measures dimension and thus is a page field; it is automatically included. But what about the members from the Product and Year dimensions? The rules for those dimensions get a little more involved and are made more difficult. One reason it is so difficult is the rules for determining datapoint members for row and column fields are a bit inconsistent. The rules are:

• If the dimension is in row orientation, follow the row left to the column containing the dimension. If that cell does not contain a member, then look up through the rows in that column to find the first member. In the example above, look left to column ‘A’, then up to row ‘4’. That cell contains the member name Colas and thus Colas is the member for the Market dimension. Members in row orientation are very consistent and easy to determine; the same is not true for the columns.
  
• If the dimension is in column orientation, follow the column up to the row containing the dimension. If that cell does not contain a member, then first look one cell to the left in that row for a member. If that cell does not contain a member, look one cell to the right from the original column. If that cell does not contain a member, look two cells to the left from the original column. Continue this pattern until you find a member. In the example above, the cell F2 contains the member name Feb which represents the Years dimension in the datapoint.

This inconsistency can easily cause confusion with its ambiguous layout. Fortunately, there is an easy way to resolve it via the use of block headers. Block headers explicitly list the members for each intersection in each row and column. Below is the confusing grid shown above after being converted to use block headers. Note the correct variance is now retrieved in the indicated datapoint.

There is anecdotal evidence that an Essbase retrieve using block headers performs slightly slower than not using block headers (i.e. pyramid headers). However, the risk of a user relying on an incorrect interpretation of the numbers is a huge price to pay for a slight performance improvement.

In part 3 of this series, I will talk about how our Dodeca product leverages the best part of the Essbase Query by Example paradigm but eliminates many of the limitations of the classic Excel add-in.

How Does the Essbase Excel Add-in Work? (Part 1)

A lot of people use the Essbase Excel add-in but don't really know all of the rules of how it detects Essbase members on the worksheet using the standard Essbase Query By Example (”QBE”) paradigm. We have a nice summary explanation of how it works in the Dodeca Administrators Guide and I thought I would share it with everyone. There is enough in our summary that I am going to split the post into a two part series.

Query By Example is a very powerful concept as it makes it very easy for business users to layout an Excel worksheet to get data into the format they need. QBE templates are created by typing the Essbase member names into the worksheet in a specific layout. QBE does follow some rules the users will need to be familiar with before they start creating templates. Here are some basic Essbase retrieval rules and terminology used in this guide:

• One retrieval range per sheet is allowed.
  
• Each worksheet may only be connected to one Essbase database at a time.
  
• All dimensions in the database must be represented or any missing dimensions will be inserted automatically.
  
• At least one Essbase dimension must be represented in row orientation.
• No extraneous text is allowed that may be confused with Essbase member name.
• Numeric member names must be preceded with a single quote to make them look like text rather than numbers.

Note: Information about removing some of these limitations with Dodeca is available later in this section.

Consider the following simple Essbase retrieval in Excel from the Sample Basic database which contains five dimensions. In this example, 3 dimensions are oriented in page orientation, 1 dimension is oriented in column orientation and 1 dimension is oriented in row orientation. Collectively, dimensions in an orientation are often referred to as fields as in Page Fields, Column Fields and Row Fields.

When the Essbase engine parses the grid, it looks at the cells selected in the worksheet and, if no cells are selected, looks at the entire used range of the worksheet. When it does the parsing, it looks for members for each dimension in the connected database and follows these rules:

• At least one dimension must be represented in row orientation. The reason is that Essbase returns data at the data intersections represented by a member in each dimension. If no dimension is represented in Row orientation, then it is impossible to have an intersection represented.
  
  
  
  The cell intersection depicted here is the intersection of the West, Qtr1, Scenario, Product and Measures members from the Market, Year, Scenario, Product and Measures dimensions, respectively.
• Multiple members in any single column must be from the same dimension unless the Use Both Members and Alias option is selected. In that case, the multiple members from a group of two adjacent columns must be from the same dimension. Members in this orientation are referred to as Row Fields as they form the row headers for the data retrieval.
• Multiple members in any single row must be from the same dimension if there is more than one member from that dimension represented on the grid. These members are referred to as Column Fields as they form the column headers for the data retrieval.
• Dimensions that contain only a single member may appear in any cell above and to the left of the first data cell as long as they don’t appear in any row or column used by the Row
  Fields or Column Fields. These dimensions effectively act as filters for the data retrieval and are referred to as ‘Page Fields’.
• Extraneous text may appear within the retrieval as long as it doesn’t resemble an Essbase member name. If Essbase confuses the text with a member name, the parsing algorithm used in the QBE query engine will get confused and will return a Member Out of Place error. Similarly, if a numeric member name is not entered as text by pre-pending the member name with a single quote, the parsing engine will get confused and return a Data item found before member error.

Determining the members represented at data intersections, or datapoints, in Essbase are generally very easy to understand. That being said, there are some potentially confusing layouts that bear some discussion. I will pick up with that point in the second part of this post.