Goodbye to Microsoft Analysis Services?

In a series of recent Twitter tweets, fellow Oracle ACE Director Mark Rittman posted the following (pasted/edited) statement referring to a post by a Microsoft BI person at http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/:

"History repeating itself. MS doing to MSAS what Oracle did to Express Server .  So with MS abandoning AS and MDX, will Oracle (through Essbase) be the main sponsor and supporter of MDX going forward? Oh the irony."

 

Ironic?  Certainly..  I was actually there at the initial XML for Analysis Advisory Council meeting when Microsoft announced to the world that MDX would be the foundation of the XML/A standard.  It had already been discussed with Hyperion but, as it was 9/11, yes, that 9/11, I was the only "Hyperion" representative in the room as the entire Hyperion team was grounded by the terrorist attacks.  In the past 9 years, it  has now come full circle that Oracle, apparently, will be the standard-bearer for MDX.

Maybe it is my good fortune, then, that I haven't worked extensively with MDX, not that I didn't have the opportunity.  I remember exactly when I got my first copy of the precursor to Analysis Services.  I was at my first Arbor Dimensions conference in Santa Clara in the fall of 1996.  I remember being invited to a private Arbor executives party at the hotel and hearing the rumor about Microsoft buying an Israeli company that was an Arbor competitor.  Little did anyone at the party know that I had an alpha version of the software in my computer bag.  But, you know what?  I never installed it.  I am a loyal Essbase fan to the core and, despite the fact that the consulting firm I worked for at the time actually wrote most of the interface that Microsoft shipped, it just wasn't my cup of tea.

Over the years, a number of our competitors went off and re-wrote their interfaces to use XML/A so they could expose their products to other databases.  When they did that, they were forced to stop supporting some of the features that make Essbase magic.  You don't hear about most of them anymore.  Proclarity anyone?  Purchased by Microsoft and now on the trash heap.  Temtec?  Acquired by IBM where, apparently, software goes to die (remember Lotus 1-2-3?  Alphablox?); Clarity recently joined them at IBM which means, strategically, their days in the Essbase world are numbered.

If you read through the comments at the end of the blog post referenced above, it looks like Microsoft is positioning Excel PowerPivot to be the ultimate BI user interface..  That is great for spreadsheet jockeys, but will lose many, many users who don't want, or need, to look through the raw data for their summarizations.

After reading all of this, I am thankful for a few things.  I am thankful we:

• Are exclusively focused on Essbase and that Essbase is very well tied in with the Oracle Fusion strategy and will continue to grow.
• Invested heavily in Dodeca.
• Focused Dodeca on the end user.

Essbase XML/A Update

Following up on an earlier post, I got SQL Server 2005 Service Pack 3 installed to test the fix to XML/A and SQL Server Reporting Services. I am happy to report that it works although I did have to recreate the report. I decided to take a quick look to see if I could figure out what was different and I discovered the query was slightly different. The original query was:

SELECT { [COGS], [Sales], [Margin] } ON COLUMNS, {CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM Sample.Basic CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING

The updated query is:

SELECT NON EMPTY { [COGS], [Sales], [Margin] } ON COLUMNS, NON EMPTY {CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM Sample.Basic CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING

I highlighted the differences. Once I removed PARENT_UNIQUE_NAME from the dimension properties, the old report ran. I don't know where the NON EMPTY statement comes from except it may be a tweak added by the Microsoft developers to improve the XML/A for Essbase reports. Here is the finished report (from the preview screen in Visual Studio):

Using XML/A with Essbase

There has been a number of questions asked recently on the forums about the use of XML for Analysis (XML/A) with Essbase. This functionality has been supported in Essbase for quite some time. In fact, I was at the charter meeting of the XML for Analysis Advisory Council held on the Microsoft campus on 9/11 (yes, that 9/11). I was asked to be there for my expertise writing web services that accessed Essbase and they thought I had something to add. Since that time, I have only dabbled in XML/A as it will never have the same functionality as the Grid API that is contained in the Java API; that has been where I keep my focus. That being said, it doesn't hurt to know how to do it so I have done a small bit. This post will talk about how to connect and use XML/A to connect to Essbase using SQL Server Reporting Services ("SSRS").

XML/A underwent a major overhaul in the 9.3.1 timeframe to be more compliant with the Microsoft flavor of XML/A. This happened before the Oracle merger when Hyperion and Microsoft were getting a bit cozy and there was some cross-breeding that allowed Hyperion Reporting tools to connect to SQL Server Analysis Services ("SSAS") and allowed SSRS to connect to Essbase. For this to happen, you must have at least Essbase and APS 9.3.1 and SQL Server 2005 SP2 or higher; I am using Essbase/APS 11.1.1 and SQL Server 2005 SP2.

To begin, select Start, All Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio to start the development environment. The SSRS development environment is actually a version of Visual Studio 2005 so, for those of you new to this, welcome to the Microsoft .NET programming experience! After Microsoft Visual Studio starts, select File, New Project from the menu to display the New Project dialog. Select the Report Server Project template and give the new project a name and directory.

Press the OK button to continue and the new project will be created. In the Solution Explorer, select Shared Data Sources, right mouse click and select Add New Data Source.

In the Shared Data Source dialog, add a Name and select Hyperion Essbase from the Type dropdown.

Press the Edit button to configure the connection. Fill in the relevent properties on the dialog. If you have filled everything in properly, you should get a list of applications to choose from in the appropriate dropdown.

Next, select the Reports node in the Solution Explorer, right mouse click and click on Add New Report. This will launch the Report Wizard.


Hit the Next button to process past the wizard introduction step to the Select Data Source step. Select the Shared Data Source you just created.



Hit the Next button to proceed to the Query Builder.



I found that when I dragged any members not in the Measures dimension into the grid, Essbase threw an error "OLAP_error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query". It seems to me to be related to outlines enabled with non-unique member names but I don't know for sure; perhaps there is an option somewhere that I missed. Note that the Query Builder does have an Auto Execute button that is turned on by default. I ended up turning off Auto Execute in my Query Builder.

In any case, make sure you select enough members/hierarchies/etc that you can have different dimensions to define the rows, columns and data (details) areas of a matrix report.

Hit OK to close the Query Builder and the MDX query string will be displayed in the wizard.

Hit the Next button and select the report type.

In my example, I added the Measures dimension members to the 'Details', the Years hierarchy to the Columns and the Market hierarchy to the Rows.

Hit the Finish button, give the report a Name and save it.

At this point, I was ready to test it and see data in the SSRS report. Guess what? The error I saw earlier came back to bite me. It seems the new provider changed enough in 9.3.1 and higher to cause a problem. Microsoft has fixed the issue in SQL Server 2005 SP3. The following line is found in the fixed buglist for SP3 at http://support.microsoft.com/kb/955706:

FIX: Error message when you use the new Essbase provider name: "OLAP error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query"

OK, I will upgrade to the new service pack and see what happens.