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:

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

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.

IIS Manager context menu showing “Add Virtual Directory…” selected under Default Web Site for OLAP setup.

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

$IIS “Add Virtual Directory” dialog configuring alias “OLAP” with physical path C:\inetpub\wwwroot\olap.$

- Then convert the OLAP virtual directory to an Application.

Configure IIS Authentication and the extension

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

IIS Features view highlighting “Authentication” for the OLAP virtual directory configuration.

- 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.

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

MSMDPUMP.INI configuration snippet with <ServerName>localhost</ServerName> highlighted.

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.

OBIEE “Import Metadata – Select Data Source” screen using XMLA URL http://localhost/OLAP/msmdpump.dll with provider “Analysis Services 2008.”

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

OBIEE warning dialog: mismatch between metadata type “Analysis Services 2008” and provider type “OLAP Server.”

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…

OBIEE Physical layer tree showing imported MSAS “Sample Cube” dimensions (Business Unit, Channel, Company, GL Account, Time) and measures (Amount, Cost, Net Amount).

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.

OBIEE BMM/Physical dimension view showing multiple Time-related tables/levels (FY Quarter, FY Time, FY Year, Month, Date, Measures).

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.

Time dimension hierarchy levels highlighted in OBIEE (Date, FY Quarter, FY Time, FY Year, Month) to show duplicate tables created per level.