March 8, 2011

Configuring BI4.0 Connection: Advanced Analysis to Microsoft Analysis Services 2008R2

The process needed to get Advanced Analysis connected to a Microsoft Analysis Services cube is to copy a small handful of files and make them available as a web site through IIS. BusinessObjects will then connect to a that IIS site in order to send/receive requests to and from your cube.

These steps assume that everything is on the one machine, but of course this is not necessary (and the joy of using IIS/xmla to connect from client to data source).


I'm using:
  • Windows Server 2008R2 (the process is a little different in 2008, and quite different in 2003 and earlier).
  • 'Web Server (IIS)' role enabled on the server.
  • BI4.0, licenced for Advanced Analysis, Edition for Web Edition.
  • Microsoft Analysis Services (2008 R2 used here).

Getting the Files

  1. First, create a folder for the files that you're about to copy. Go to C:\inetpub\wwwroot and create a new folder called 'olap'. If this path doesn't exist, then check that IIS is installed and configured correctly.
  2. On a vanilla install of SQL 2008R2 you will find the files you need in the folder C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi. In this directory, there will be a Resources folder, a msmdpump.dll and a msmdpump.ini file.

    If you can't find the files, search for 'msmdpump' on your drive.
  3. Copy the contents of the isapi folder into the olap folder you created in step 1. You should now have a structure something like this:
    image

Setting up IIS

  1. Open up the Internet Information Services (IIS) Manager.
  2. In the Connections pane, expand Sites > Default Web Site. If you see olap in the tree, then everything's going well.
  3. In the Connections pane, right click on Application Pools and select Add Application Pool…
    image
  4. Configure the following settings:
    image
  5. Now, in the list of application pools, select the olap pool and click Advanced Settings… over on the right. Ensure that Enable 32-Bit Applications is set to False. If this is true, the connection won't work.
    image
  6. Convert your olap website to an application like so:
    image
  7. In the window that appears, next to Application pool: click the Select… button.
  8. Change the application pool to olap.
  9. Click OK.
  10. Click OK again.
  11. With the olap site selected in the Connections pane, double-click Handler Mappings in the IIS category.
    image
  12. With nothing in particular selected in the middle, click on Add Script Map… in the Actions pane on the right.
    image
  13. Fill out the fields like this:
    image
  14. Click OK.
  15. Click Yes to the message that appears.

    We're half way through. Now to set up the authentication.
  16. Click again on the olap site in the Connections pane.
  17. Double click on Authentication in the IIS category.
    image
  18. Right-click on the Windows Authentication entry and click Enable
    image

Security

From what I can see, there are two ways of allowing Business Objects to connect through IIS to your Analysis Services Cube. The first option is to simply enable Basic Authentication in the step above. If you're happy with the security implications of this, go for it, and skip ahead to Creating an OLAP Connection. Otherwise, follow these steps:
  1. Open SQL Server Management Studio, and log into your Analysis Services instance.
  2. Right-click on the instance node and select Properties…
    image
  3. Go to the Security page and click Add… down in the bottom right.
  4. Type IUSR in the box and click the Check Names button. The text should become underlined like so:
    image
  5. Click OK, then OK.

Disclaimer: I'm no security expert. Check with someone knowledgeable before putting this solution into a production environment.

 

Creating an OLAP Connection

  1. Open up the CMC and click on OLAP Connections in the Organize column of the home screen.
    image
  2. Click the New Connection button.
    image
  3. Give the connection a name.
  4. In the Provider box, select Microsoft Analysis Services 2008.
  5. In the Server box, type http://localhost/olap/msmdpump.dll (replacing localhost if applicable)
  6. If you allowed the ISUR account to access to your cube, here you can just type in the username and password required to access the cube.
    image
    If you instead have allowed Basic Authentication in your IIS site, you must enter the username in the format DOMAIN\User and a password.
    image
  7. Click OK.
  8. Select a cube (or a perspective) and click Select.
    image
  9. Enter the Authorization options to taste. Note that here you can just enter a user name (no domain prefix) regardless of the security path you chose.
  10. Click Save, and you're done!

Your cube will now be available to your Advanced Analysis Workspaces.