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.