Skip to end of metadata
Go to start of metadata

Purpose

The purpose of this page is to describe how to setup SAP Business Intelligence BI to use MS SSAS OLAP cubes as a data source for creating Web Intelligence documents.

Overview

SAP BI provided a new semantic layer tool is introduced (Information Design Tool) that supports only XMLA connectivity to OLAP data sources. This is a change from earlier versions which supported OLEDB connectivity.  This page will help cover the topics of prepping the BI server for XMLA and ODBC connectivity.  It will also cover creating the connection in both Universe Design Tool and Information Design Tool and creating a simple Web Intelligence report on each.

It assumes access to a Microsoft SQL Analysis Server and a SAP Business Intelligence server and does not cover installation of either application.

Supported Platforms 

Product Availability Matrix (PAM) 4.2 

Product Availability Matrix (PAM) 4.3 

Preparing the SAP BI Environment to handle OLE DB & XMLA OLAP requests

Different components are required to successful connect to Microsoft SQL Server Analysis Server from the BI Landscape.  Please be sure to use the correct version matching the version of SSAS.

The configuration will require three Microsoft components for a connection the  XMLA, the IIS and the Microsoft Native Drivers.

IIS

For information on Internet Information Service known as IIS please refer to the Microsoft wiki Here:  IIS 8

Installing Microsoft Core XML Services

XMLA services and IIS are available installed in some windows versions.  You can check your environment with these Microsoft steps: Check XML Version.

For security purposes, make sure your file is the latest available.  Here is a list of the Microsoft Versions released.

To download & install MS Core XML Services 6.0 from Microsoft, go to:
MS Core XML Services 6.0

Microsoft Native Drivers

Microsoft® SQL Server® 2012 SP4 Feature Pack

https://docs.microsoft.com/en-us/analysis-services/instances/configure-http-access-to-analysis-services-on-iis-8-0?redirectedfrom=MSDN&view=asallproducts-allversions&viewFallbackFrom=sql-server-ver15

Configuring UNX to connect to MS Analysis Services through XMLA

Connections to MS Analysis Services through XMLA do not use Connection Server.

HTTP Access to the MS Analysis Service is required. Please visit Microsoft's website on how to configure XMLA for IIS for more information on setting this up.

Creating the Project

  1. Launch the Information Design Tool (IDT) for SAP BI 4.0
  2. Create a new Project and name it (example: Northwind Cube)

Creating the Connection

  1. Right click on the Project and select New > New OLAP Connection
  2. Name it (example: Northwind Connection) and click Next
  3. Under the correct MS Analysis Service version select “XMLA”
  4. Enter the URL to the data pump binary and the login setup in IIS from the previous section
    Example: http://MSSQL05/olap/msmdpump.dll
  5. Select the cube to access and click “Finish”

    Note: “Do not specify a cube in the connection” can also be selected. If there are multiple cubes hosted by the Analysis Services, they will all be available when creating queries based on this UNX.
  6. After the connection is created, it needs to be published in order to access it by the Business Layer that will be created in the next step. Right click the new connection & Select “Publish to Repository”
  7. Login to the Repository and select a folder to Publish to and click “Finish”
  8. The next window to pop up, click “Yes” to create a connection shortcut
  9. Click Close, the connection has now been published

Creating & Publishing the Business Layer (UNX)

The Business Layer is essentially the UNX that will be published to the repository and used to run queries against the OLAP data source

  1. Right click on the Project and select New > Business Layer
  2. Select “OLAP Connection” and click “Next”
  3. Name the Business Layer (example: Northwind Cube) and click “Next”
  4. Select the “Secured” connection to the cube (this is the shortcut created in the previous section) and click “Next”
  5. Keep the rest of the defaults, click “Next” then “Finish” to create the Business Layer
  6. Now the Business Layer needs to be published to the Repository so it can be accessed by BI reporting tools. Right click the Business Layer and select Publish > To a Repository…
  7. It’s not necessary to do an integrity check at this stage, so click “Next”
  8. Select the folder the UNX should be published to and click “Finish”
  9. IDT has a “Repository Resources” section in the bottom left that can be used to view and test the UNX file.
    1. Double click on the UNX to open a Query Panel to run test queries:
    2. Run a test query to confirm it’s working

Configuring UNV to connect to MS Analysis Services through OLE DB

Universe Design Tool can be used to create a .unv type Universe using OLE DB to connect to the MS Analysis Services.
Be sure the OLE DB client is installed.

Creating the Connection

  1. Launch Universe Design Tool (aka UDT or Designer)
  2. Go to Tools > Connections to create a new connection
  3. Name the connection (example: NorthwindUNV) and click “Next”
  4. Expand the MS Analysis Server version section and select the OLE DB data provider
  5. In the Authentication section, enter the data source User Name/Password
  6. For the Server, enter the MS Analysis Server (example: MSSQL08) or use the data pump binary
    • Using the Data Pump URL:
    • Using the Server:
  7. Select the OLAP Cube then Click “Next” then “Finish”

Creating & Publishing the Universe

  1. Launch Universe Design Tool
  2. Go to File > New
  3. Name the Universe & select the connection created in the previous section, then click “OK”
  4. The objects and hierarchies are created automatically, edit them as needed
  5. Save and Publish the Universe to the Repository (File > Export)
  6. The Universe is now available for use by BI Reporting Tools

Creating a Web Intelligence Report based on the UNX

  1. Login to BI LaunchPad
  2. Go to “Applications” at the top left and select “Web Intelligence Application”
  3. Click the “New” icon
  4. Select “Universe” as the data source type
  5. Select the UNX created above
  6. Once the objects are loaded, select a few for the query and run it
    • Web Intelligence Rich Internet Application Query Panel (formerly known as Java Report Panel)
    • Query Results:

Related Content

Related Documents

SAP Business Intelligence Product Guides

Web Intelligence User Guides

Microsoft SQL Server 2012 Native Client (including OLE DB Drivers)

Microsoft Core XML Services 6.0

Microsoft TechNET on setting up HTTP Access to MS SQL Analysis Services:

Microsoft Patterns & Practices How to: Use Windows Authentication in ASP .NET 2.0


Microsoft Blogs on future of OLE DB:

  • http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx
  • http://blogs.msdn.com/b/analysisservices/archive/2011/09/14/impact-of-the-sql-server-ole-db-deprecation-on-analysis-services.aspx

Related Notes

3 Comments

  1. Former Member

    Hi,

    Thanks for the detailed description. I have successfully configured IDT to access SSAS cubes. Can you please give some idea on data level security.

    Data level security is setup in SSAS cube. I am not clear how BO can be setup inorder to access cube data. Please let me know if you need more inputs on this.

    Thanks in advance,

    Arun

  2. Hello!

    I have OLAP cube with Russia name and with English name in SSAS 2008 R2 

    When I create business layer in IDT on OLAP connection XMLA
    I can not see cube with Russia name.. But see English name cube successful..

    Can you help me how resolved this problem?