IBM Business Analytics Proven Practices: Configuring a SSAS HTTP XMLA Data Source Connection for use with IBM Cognos 10 BI

Product(s): IBM Cognos BI

An outline of the steps required to setup a SQL Server Analysis Services (SSAS) Extensible Markup Language for Analysis data source connection for use with the IBM Cognos Dynamic Query Mode.

Share:

Josh Labrecque, Technical Analyst, IBM

Josh Labrecque is a Software Engineer at IBM Cognos with almost 5 years of experience. He has been in the Customer Support team working with Cognos 8 and 10 for the entire time and for the last two years with the AVP support group.



13 February 2014

Also available in Russian

Introduction

Purpose of Document

This document outlines the steps required to setup a SQL Server Analysis Services (SSAS) Extensible Markup Language for Analysis (XMLA) data source connection for use with the IBM Cognos Dynamic Query Mode.

Assumptions

This document assumes that a fully functioning IBM Cognos Business Intelligence (BI) environment exists. This document also assumes that this existing IBM Cognos BI environment is able to connect to an existing SSAS data source using the Object Linking and Embedding, Database for Online Analytical Processing (ODBO) protocol.

Applicability

The steps in this document were validated using the following two server configurations:

  • Server1, which consisted of a single server IBM Cognos BI 10.2.1.3 install, IBM DB2 Content Store and Internet Information Services (IIS) 6.0.
  • Server2, which consisted of IIS 6.0 and SSAS 2008 R2.

Caveats

The steps and configuration settings outlined in this document should be thoroughly tested in a non-production environment. Any and all IIS security changes should be scrutinized to ensure they comply with existing security policies.


Overview

The IBM Cognos Dynamic Query mode, shipped as part of the IBM Cognos 10.2.1 product suite, has the ability to connect to SSAS using both the ODBO and XMLA protocol. The ODBO connectivity is installed as part of the Microsoft SQL Server Windows client install and can only be used by a Windows install of IBM Cognos BI. The XMLA connectivity is accessed via a Universal Resource Location (URL) and can be used by both an IBM Cognos BI install on a Windows or UNIX/Linux. Be sure to check the conformance page for an up to date list of supported IBM Cognos Dynamic Query Mode data sources.

The following sections of this document will cover the steps required to:

  • Configure IIS on the SSAS server to allow for XMLA connectivity.
  • Test XMLA connectivity Outside of the IBM Cognos product.
  • Setup an XMLA data source to SSAS within IBM Cognos Administration.

Configuring the Internet Information Services

The following section provides the step by to step to:

  • Copy the connectivity files to IIS.
  • Setting up the Application Pool used for the XMLA connectivity.

Copying the Required Files for XMLA Connectivity

  1. On the server where SSAS 2008 R2 is installed, navigate to the MSAS10_50.MSSQLSERVER\OLAP\bin\isapi directory under your SQL Server install folder. For this example the full path would be C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi.
  2. Within the isapi folder, highlight and copy the file msmdpump.dll and the msmdpump.ini configuration file.
  3. Navigate to the root directory for IIS. Ffor this example the root is located at C:\inetpub\wwwroot.
  4. Under the wwroot directory, create a new folder called olap.
  5. Paste the msmdpump.dll and msmdpump.ini configuration file into the newly created olap directory.

Setting up the IIS Application Pool

  1. Open IIS Manager console.
  2. Within the top left Connections pane, right click on Application Pools and create a new application pool called olap with the .NET Framework version set to version 2 and the Managed pipeline mode set to Classic. When completed, the Add Application Pool dialog box should display as follows.
    Figure 1 - IIS Add Application Pool dialog box
    Figure 1 - IIS Add Application Pool dialog box
  3. Click OK to create the application pool.
  4. Right-click on the new application pool and click the Advanced Settings link at the top right hand side of the screen.
  5. Within the Process Model section, select identity by clicking the ellipse button at the end of the Value column.
  6. Select the Custom Account radio button and specify an MSAS admin user account. Once completed click the OK button to commit the changes.
  7. Click the OK to return to the Advanced Settings dialog box.
  8. Click OK on the Advanced Settings dialog box to return to the IIS Manager console.
  9. Expand the Default Web Site to see the olap virtual directory. Right click on the olap virtual directory and select Convert to Application, then point the application to the new application pool that was created in Step 2.
  10. Right click on the new olap application and select Manage Application\Advanced Settings. Within the Advanced Settings dialog box, click the ellipse for the Physical Path Credentials.
  11. Select the Specific user radio button and use the same MSAS admin user account as used earlier.
  12. Click the OK button twice to return to the IIS Manager console.
  13. With the olap application selected within the top left Connections pane, double-click on the Authentication icon displayed under the ISS section of the middle pane.
  14. Once the Authentication settings appear, ensure that the Anonymous Authentication is enabled and all others are disabled.
  15. Right-click on Anonymous Authentication and select Edit. Then select the Specific user dialog box and enter that same MSAS admin user account as used in the previous steps.
  16. Click OK twice to return to the IIS Manager Console.
  17. With the olap application selected within the top left Connections pane, double-click on the Handler Mappings icon displayed under the ISS section of the middle pane.
  18. On the right side of the Actions pane, click on Add Script Map. When the Edit Script Map dialog appears, type *.dll for the Request path and browse to the msmdpump.dll file that was copied earlier for the Executable. When completed the Edit Script Map dialog box should look as follows.
    Figure 2 - IIS Edit Script Map dialog box
    Figure 2 - IIS Edit Script Map dialog box
  19. Once the request path and executable are set, click on Request Restrictions button.
  20. Within the Request Restrictions dialog box go to the Access tab and make sure that Execute is selected. Click the OK button twice to return to the IIS Manager console. If prompted to set an exception for the isapi extension to execute, click Yes.
  21. To commit all the changes, single click on the server level within the top left Connections pane.
  22. Within the top right Manage Server pane, click Restart to do an IIS reset.

Testing XMLA Connectivity Outside of IBM Cognos BI

The following section provides to steps required to test the XMLA connectivity outside of the IBM Cognos BI product suite.

  1. Login to the MSAS server as the MSAS admin user you have chosen to use.
  2. Open SQL Server Management Studio.
  3. Under Server type, select Analysis Services.
  4. For the server name, you will put in your new URL http://<server2_servername>/OLAP/msmdpump.dll.
  5. Click Connect.
  6. Once the request path and executable are set, click on Request Restrictions button.
  7. Within the Request Restrictions dialog box go to the Access tab and make sure that Execute is selected. Click the OK button twice to return to the IIS Manager console. If prompted to set an exception for the isapi extension to execute, click Yes.
  8. To commit all the changes, single click on the server level within the top left Connections pane.
  9. Within the top right Manage Server pane, click Restart to do an IIS reset.

Testing XMLA Connectivity Outside of IBM Cognos BI

The following section provides to steps required to test the XMLA connectivity outside of the IBM Cognos BI product suite.

  1. Login to the MSAS server as the MSAS admin user you have chosen to use.
  2. Open SQL Server Management Studio.
  3. Under Server type, select Analysis Services.
  4. For the server name, you will put in your new URL http://<server2_servername>/OLAP/msmdpump.dll.
  5. Click Connect.

Configuring the XMLA Data Source within IBM Cognos Administration

The following section outlines the steps required to setup an XMLA connection using the IBM Cognos Administration user interface.

  1. Launch IBM Cognos Administration and navigate to the Configuration tab.
  2. Under Data Source Connections, select New Data Source and provide a name.
  3. Under Type, select Microsoft Analysis Services (HTTP XMLA) and click Next.
  4. Enter the URL http://<server2_servername>/OLAP/msmdpump.dll, then check the checkbox for both User ID and Password.
  5. For the User ID and Password, enter the credentials for the MSAS admin user account used throughout this document.
  6. Click the Test the connection link. Once it tests successfully, click the Finish button to save the data source.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


  • Bluemix Developers Community

    Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.

  • Big data and analytics

    Crazy about Big data and analytics? Sign up for our monthly newsletter and the latest Big data and analytics news.

  • DevOps Services

    Software development in the cloud. Register today to create a project.

  • IBM evaluation software

    Evaluate IBM software and solutions, and transform challenges into opportunities.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=962425
ArticleTitle=IBM Business Analytics Proven Practices: Configuring a SSAS HTTP XMLA Data Source Connection for use with IBM Cognos 10 BI
publish-date=02132014