OBIEE & Microsoft SQL Server Analysis Services

by Suman Busireddy

Oracle Business Intelligence Enterprise Edition (OBIEE) 11g provides multi-dimensional navigation/analysis support.  This multi-dimensional feature is supported for almost any data source including Microsoft SQL Server Analysis Services (SASS).  For Microsoft SQL Server Analysis Services 2008/2012, we need to make an initial configuration before getting started with OBIEE.

OBIEE connects to Microsoft SASS through XML for Analysis.  The XML for Analysis (XMLA) open standard supports data access to data sources that reside on the World Wide Web. Microsoft SQL Server Analysis Services implements XMLA per the XMLA 1.1 specification.

To enable the connectivity we need to follow the following steps:

1. Copy the MSMDPUMP files to a folder on the web server

• Copy MSMDPUMP.DLL, MSMDPUMP.INI, and a Resources folder containing language resource files. These files can be found at C:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi.
• Create a new folder: C:\inetpub\wwwroot\OLAP and paste the copied files here

2. Create an Application Pool and Virtual directory in IIS

• From Start, point to Run, and then type Inetmgr.
• Add Application Pools, and create an application pool named OLAP, using .NET Framework v2.0.50727, with Managed pipeline mode set to Classic.
• By default, IIS creates application pools using Network Service as the security identity. To change the identity of the application pool you created, right-click OLAP, and then select Advanced Settings.
• In IIS Manager, open Sites, right-click Default Web Site and then click add Virtual Directory.

• Alias: OLAP.
• Path: C:\inetpub\wwwroot\OLAP. Click OK.

• Then convert the OLAP virtual directory to an Application.

3.  Configure IIS Authentication and the extension

• Open IIS Manager --> Sites-->Default web site-->OLAP Directory
• Select the Authentication type and Enable it...

• Click on OLAP Directory again and click Handler Mappings and then select Add Script Map. Specify *.dll as the request path, c:\inetpub\wwwroot\OLAP\msmdpump.dll as the executable, and type OLAP as the name and click Ok and When Prompted to Allow the ISAPI extension say Yes.

4. Edit the MSMDUMP.INI, if the MSAS and OBIEE are on different server

Change the server name

Now we are set to start importing MSAS CUBES into OBIEE, using the URL http://localhost/OLAP/msmdpump.dll.  To test XMLA, use the direct URL in a browser to get an XML response.

Make sure the SQL Server Database is running and the related ports are not blocked.  User credentials used to import the cubes must have access to the cubes.

Open the repository (RPD) and navigate to import and enter the details.

You may get a warning message, ignore it. Click Ok…

You will see the list of Cubes, import the Cubes required.  I have imported sample Cubes… as you can see below in my Physical Layer…

Physical Dimension: When we move objects from a Physical layer to the Business Model and Mapping (BMM) for every Physical dimension, multiple BMM tables are shown for each level.  

Four tables are returned at each level as in the figure above.  You can manually delete the tables of different levels and keep the top level table, which consists of all the columns, or you can delete the physical hierarchies from the properties of the physical dimensions

We can delete these Physical Hierarchies and keep only FY-Time which has the entire Columns of Time dimension.

Once it is in the BMM with the required tables and columns, it can be moved to the presentation layer.

All set for Analysis…