BI EE 11g now provides true multi-dimensional navigation/analysis support. This multi-dimensional analysis is supported for almost all the data sources that are supported by BI Server for reporting. Today we shall how BI EE 11g works on Microsoft SQL Server Analysis Services 2008. There is no explicit MSAS 2008 support yet but from a perspective of BI EE nothing much has changed. So lets see how this connectivity works in 11g.
Like BI EE 10g, 11g connects to MSAS through XMLA. So, the first step in enabling the connectivity is to enable the XMLA access in MSAS. To enable the XMLA access, we need to make the resource and dll files from the SQL Server installation to be accessible for IIS(IIS is necessary for XMLA). We basically have to copy the files from C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\isapi directory to the IIS directory C:\Inetpub\wwwroot\olap directory.
After copying the files, create a new Application Pool from the Computer Management Console.
From the Default Website create a new Virtual Directory.
Give the Virtual Directory a name of OLAP. Also, make the Virtual Directory to point to the olap folder under IIS.
Right click on the new Virtual Directory and go to the Properties section. Make sure the Application Pool and Application Name are pointing to the OLAP & olap respectively as shown below
Also click on the Configuration button to add the msmdpump.dll as a resource to be served through IIS.
And in the Directory Security tab, ensure that the following authentication is enabled. It might be different in your case depending on how you have configured SQL Server.
Click on Add a new Web Service Extension to add a new web service pointing to msmdpump.dll
This should provide us access to SSAS through XMLA. To test XMLA, just use the direct URL in a browser http://localhost/olap/msmdpump.dll. This should produce an XML response as shown below.
Now that we have the connectivity working, lets open up the repository and choose the Import Metadata > XMLA option. If you notice, there is no explicit support for SQL Server 2008 yet. Lets choose the Analysis Services 2005 and enter the login credentials.
We would get a provider mismatch as 2008 XMLA is different from 2005. Ignore the warning and click on OK.
This should list all the MSAS cubes that the user has access to as shown below. Lets choose 3 of the them for the import.
One of the first things that you will notice is, BI EE 10g did not support unbalanced SQL Server hierarchies (an error from 10g while doing the import). But i guess in 11g this restriction has been removed as we won't be getting an error while importing unbalanced hierarchies.
Also, in 10g while importing a SQL Server cube, every hierarchy got imported as a separate dimension as shown below
But in 11g, individual hierarchies still get imported but each hierarchy will reside in their own dimensions as shown below like Essbase
Also, unlike Essbase where Measure dimensions are imported into their own hierarchy (in 11g), Measures are imported as is from SSAS. The primary reason for this is, SSAS requires explicitly qualifying measures like Oracle OLAP. Now to see whether we can convert multiple hierarchy view into a single dimension view like Essbase, lets right click on the Date dimension and click on convert to a single hierarchy view
This will produce an error. Looks like all the new features that were introduced primarily for Essbase sources are not supported for MSAS. If you look at the error message, it will have "Essbase" in the error message. So, i am guessing this is supported only for Essbase & has not been implemented for SSAS.
Lets now create the BMM & Presentation layer and test out the Hierarchical columns.
Though it does work, in most cases, i received an internal assertion error as shown below. On further testing, looks like Hierarchical Columns do not handle No Results found properly for MSAS sources.
I am not sure whether it is due to the hierarchical columns as normal attribute columns worked really well.
In a future blog post, i shall be comparing the MDX generated for Essbase and MSAS by BI EE.