Access your SQL Server Analysis Service OLAP using Web service with HTTP

SSAS OLAP is very secure and allows only windows authentication. You can’t access OLAP using SQL Server Authentication or any other ways. Directly or indirectly you must have windows level permission to access OLAP Database.
If you want to access your OLAP Server for reporting purpose on web, you can create a web service. For doing this you need PUMP component loaded into your IIS Server servers as a ISAPI, this pump your data from Client to OLAP Server and vice-versa.
The Steps are explained in detail below: 

STEP: 1    If your SSAS service is installed properly on you Server just copy the %SQL Server Installation Location%\OLAP\bin\isapi folder into the folder where you want to make a base location of your virtual directory.
In this example coping folder to c:\inetpub\wwwroot\OLAP.

STEP: 2    Now open your IIS Server, to open this simply go to Start > Control panel. Now go to Administrative Tool. Then go to IIS Information Service. 

If IIS Service is not installed you can go to Add remove Component and install IIS using you Windows XP CD.

STEP: 3    Open IIS Server, here you can create your virtual directory by right clicking on Default Web site > New > Virtual Directory.

STEP: 4    A Virtual Directory Creation Wizard window pops up click next , This step will ask you the virtual directory Alias name, Type you desired name here ( in my Example “OLAP”).

STEP: 5    Click Next, In this step click browse and point to your OLAP Directory recently created in wwwroot folder and click Next.

STEP: 6    Now in Access Permission Step just click Run Scripts (Such as  ASP) only now  Click Next then Finish. Your Virtual Directory created successfully.

STEP: 7    Now you have to set up the Property of your Virtual Directory, for this right click on your Virtual Directory. And click property. You may see the window given below.

STEP: 8    Here click the configuration button, a new Application Configuration window appears. Click Add button here.

STEP: 9    Enter the full patch of msmdpump.dll in Executable box in my case its C:\Inetpub\wwwroot\olap\isapi\msmdpump.dll in Extension box type .dll and click OK.


STEP: 10 In this step, select Directory Security Tab in main Property window and click Edit Button.

STEP: 11        On getting Authentication Methods window, select the Anonymous access check box and enter your Windows authenticated User name and password, Note that, this must be the same user who can access SSAS Database, click OK.

Now this service is ready to access your SSAS OLAP Server, Actually when you call this service, it hits the OLAP DB with the windows credentials what you gave into the Authentication mode and server your query. .

Now here you should be able to connect your SSAS Services using http://LocalServer/olap/../msmdpump.dll as a server Name.

Please comment and let me know for any query regarding this..... happy programming :)


1 comment:

  1. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating MicroStrategy Online Training