How to Connect Jpivot with Microsoft Analysis Services

How to connect Jpivot with Microsoft Analysis Services

Creator: Javier Gimenez (Stratebi Business Solutions)

Principio del formulario

Last days, we are making many tests, as much internal, as for our clients around solutions Business Intelligence Open Source. One of more interesting is the one to connect Jpivot (viewfinder OLAP that uses Pentaho), with cubes AS of Microsoft. This is a solution fast and economic to have a viewfinder OLAP more (flexible and burglaryable) connecting with the most extended motor OLAP of the market. The result has been this brief tutorial that we want to share with all you.
1. Target
The present document contains the steps and the data obtained after the investigation made with the intention of connecting the OLAP interface JPivot to a cube of Analysis Services of Microsoft via XMLA.
2. Ste by Step
2.1. Prerequisites
- We needed a Cube working in the Analysis Services edition 2000 SP4 of SQL Server. This example has been made with the cube of test Food Mart 2000.
- We need a IIS (>=4.0) working. This simple test has been made using IIS 6.0.

- We needed a server JBoss with the application Web unfolded JPivot.war.
- In principle it is not essential to have pentaho if it is only desired to verify the access from JPivot to the cube. But you can use it to get a more valuable application.
This test has been made using Windows 2003 Server, JBoss 4.0.4 and JPivot 1.6.
2.2. Step by Step
1.We should install the SQLServer XMLA extension For Analysis from:

2. We should configure a datasource in the next file::
XMLA\ Config\datasources.xml
With something similar to this one:

You cab have a look to the instructions in the help file of XMLA for Analyisis located in: Microsoft XML For Analysis SDK\Help\xmla10.chm

Also, it´s very useful the configuration information of XMLA available in the next URL of Microsoft:

3. Create a virtual XMLA directory in IIS.

We should follow the instructions ib the XMLA help, you can read as follows:
To set up the virtual directory
1. Open Internet Services Manager.
2. In the tree pane, expand the server you want to administer.
3. Right-click the name of the Web site you want to use, point to New, and then click Virtual Directory.
4. The Virtual Directory Creation Wizard steps you through creating a virtual directory.

• For the directory containing the content to publish, enter the path to the
folder containing Msxisapi.dll (for example, C:\Program Files\Microsoft XML for
Analysis SDK\Isapi).
• For access permissions for the virtual directory,
select Read, and set the execute permissions to Run scripts and Execute.
If you are configuring IIS 4.0, select Allow Read Access, Allow Script Access and Allow Execute Access.

Note: For simplicity, when you specify the content directory for the virtual directory, use the path to the installation folder that contains Msxisapi.dll (for example, C:\Program Files\Microsoft XML for Analysis SDK\Isapi). If you specify a different folder for the content directory, you must then copy Msxisapi.dll from the installation folder to that location.
To configure security for the XML for Analysis Provider, use Internet Information Services Manager to manage the virtual directory security.
4. Configuration of Windows 2003 IIS with extensions.

By default, Windows 2003 IIS (6.0), is installed in a way that it´s not allowed dlls executions.To avoid this, you need to aggregate a new server extension that points to the Msxisapi.dll and marks the rights allowed.

From the URL provided with information of XMLA for SQL Server configuration, check the tag: Enabling the XML for Analysis Web Service Extension on Windows Server 2003
5. Check the server is running

Alter doing this, we need to make the next test from a browser: go to the URL
(being asxmla the name of the virtual directory we´ve choosen).

We need to get a SOAP answer with this content, otherwise we need to check the previous steps:

6. Add the security roles desired to the Cubes.

If we don´t go through this step, we´ll get an error showing that the database it´s not found.

a) From the MSSQL Analysis Manager we modify the database functions.
b) Select All User function and click in Modif..

c) Select all the users we want they have access to the database (for this test we can select all).
This allow us that JPivot could access to cube datasource.
2.3. From the Jpivot web Application.

1. Download jpivot.war and deploy it over Pentaho Jboss in the directory: Server/default/deploy. We can leave it as a .war file and open it to modify with winrar, for instance. Or we can unzip and place the directories in a directory called jpivot.war, so it will be easier for editing.

2. Add a jsp in WEB-INF/queries that contains the query we want to test, something similar to: (in bold, what we need to change)

Follow the indications of thispost:

3. The name we provide to queries JSP (queryxmla), will be the parameter to send to the Jpivot test page, through URL as a aquery, following the next schema:

If we´ve made corectly all the previous steps, it should Works without problems, getting something like this when we access through the URL:

Final del formulario