Thursday, December 18, 2008

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.

13 comments:

dwelden said...

I seem unable to get past the connection properties dialog in SSRS 2005 SP3. I keep getting: The connection either timed out or was lost.

C# + ADOMD.Net does not get me any further, but at least it gives me more information on the exception (a 401 Unauthorized error):

Unhandled Exception: Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized.
at System.Net.HttpWebRequest.GetResponse()
at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseStream()
--- End of inner exception stack trace ---
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest()
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Discover(String requestType, String requestNamespace, ListDictionary properties, IDictionary restrictions, Boolean sendNamespacesCompatibility)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Discover(String requestType, ListDictionary properties, IDictionary restrictions, Boolean sendNamespacesCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Discover(String requestType, IDictionary restrictions, InlineErrorHandlingType inlineErrorHandling, Boolean sendNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ReadDataSourceInfo()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()

Not sure if I have the connection string right as I tried to cut and paste from the SSRS designer. Here is what I used if you can provide a sanity check on it:
ConnectionString = 'Provider=Essbase;Data Source=https://MyAPSServer:13080/aps/XMLA;Initial Catalog=Sample;UserName=MyUser;Password=MyPassword;DataSourceInfo="Provider=Essbase;DataSource=MyEsbServer"'

Anonymous said...

Just curious if there was ever any resolution to the abouve question? I'm trying to do the same thing and producing the same error. Any suggestions would be great.

Thanks!
Jonathan

dwelden said...

I discovered that we have some issue with the SSL configuration on WebLogic. WebLogic is what is giving me 401 Unauthorized. When I bypass the SSL (https) link, and go through with a non-SSL (http) URL, I can pull data.

The project moved on, however, so I did not pursue a completed solution.

Anonymous said...

I have on a weblogic server as well. Did you have to change any settings on Weblogic or just use the non-SSL url (http instead of https). Also, what version of Essbase and ADOMD.NET were you using?



Any help would really help!

dwelden said...

I was using ADOMD.Net 9. I never did get that to work. However, I switched to COM using Microsoft.XMLHTTP and then was able to successfully POST a Soap call to XMLA and receive data from Essbase. The URL that was successful was http. I never was able to authenticate to the SSL (https) URL.

My conclusion was that some additional configuration was required on the WebLogic server since the authentication failure came from it, not Essbase. But as I said, the project pursued a different solution so I did not take the research any further.

I would recommend you follow up with Oracle if you need to discover the required configuration changes for WLS.

Anonymous said...

When using this on such as a portal, there might be many connections to Essbase. Each time the portal runs, a new session is set up. There will be a huge amount of threads towards Essbase. How do we "logout" for each request/session from the portal? Is this a bug?

Tim Tow said...

I would consider it a bug, but I don't know if it is a bug in the Essbase XMLA provider or in the Reporting Services engine.

I would report it to Oracle Tech Support.

Tim

aayush said...

Hi,
As essbase return the hierarchy levels in reverse order , 0 as child 1 as parent.
Is there any way to get the levels in normal order,0 as parent and 1 as child.

regards
ayush

Tim Tow said...

I don't know if you can get the info in your format directly in XML/A, however, you may be able to transform the xml that is returned..

Tim

sdkk said...

Tim,
Need some help..Not sure what i am doing wrong.. I am testing XMLA thru provder services.. I went and build a sample MDX query from SQL SErver Business Intelligence Studio. And for some reasons, its not returning any data. I took the same MDX query and ran thru EAS and it shows the numbers.. I get "null" in the data field.

Tim Tow said...

In my opinion, XML/A from SQL Server to Essbase seems very dependent on the versions of both SQL Server and Essbase. As there does not seem to be any active development on the XML/A side, and the XML/A Advisory Council has disbanded, I would probably not base a solution on XML/A at this time.

Tim

Karl Fredricson said...

I created a reverse proxy which intercepts and "corrects" the traffic between SSRS and Essbase XML/A WebService. The source code can be found here:

https://skydrive.live.com/?cid=0095466f82c8951e&sc=documents&id=95466F82C8951E%21113

/Karl

Tim Tow said...

Thanks Karl..

I briefly glanced at the code and decided to post it so others can know about it; I don't know if it works and other std disclaimers apply, etc..

Tim