KPI Partners Blog

OBIEE & Microsoft SQL Server Analysis Services

Posted by KPI Partners News Team on Wed, May 07, 2014 @ 11:10 AM

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

describe the image 

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

 describe the image

  • Then convert the OLAP virtual directory to an Application.
  1.  Configure IIS Authentication and the extension
  • Open IIS Manager --> Sites-->Default web site-->OLAP Directory
  • Select the Authentication type and Enable it...

describe the image

  • 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.
  1. Edit the MSMDUMP.INI, if the MSAS and OBIEE are on different server

 describe the image

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.

describe the image

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

describe the image

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…

 describe the image

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.  

describe the image

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.


describe the image


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

All set for Analysis…

 

 describe the image


headshot Reddy Suman

Suman Busireddy is a business intelligence and data integration professional at KPI Partners. Check out Suman's blog at KPIPartners.com. 

 

See A Video Overview Of Oracle BI Apps 11g

 

Tags: Business Intelligence, Blog, Oracle BI, OBIEE, Tutorial, Microsoft, Suman Reddy, SQL Server, XML



Subscribe to the KPI Blog