JPivot + MS Analysis 2000

Introduction

JPivot is one of rich features OLAP clients in the market that comes up with web based interface. Its capability to run in multiple platform and compliant with XMLA standard as its data source make JPivot a viable and affordable solution for every organizations that already implemented XMLA OLAP server but needed more robust client solution.

MS SQL Server 2000 and MS 2000 Analysis Server is one of the mostly wide used database and OLAP server in enterprise market. And since Microsoft is also the initiator in this XMLA technology, it is not surprising that MS Analysis Server also support the standard.

But the drawback is that both products don't come out with any OLAP client. Hence, since JPivot is XMLA compliant we can use it nicely to fill the gap.

Setup IIS and MS 2000 Analysis Server

First you need to download Microsoft XMLA sdk and set it up with IIS and MS Analysis Server. I will not go through this step as you can see more detailed instructions in this MSDN article.

For my working sample I use XMLA 1.1 SDK version. And if you have installed, configured and running all the things correctly then try browse to msxisapi.dll from your XMLA virtual host. In my case my virtual directory name is set to xmla and located in my local desktop so I'll browse to http://localhost/xmla/msxisapi.dll and should see result like the picture below

Figure 1: URL Testing for SQL Server 2000 XMLA Implementation

or if you are using Firefox you may get result like in figure 2.

Figure 2: URL Testing for SQL Server 2000 XMLA Implementation (Firefox)

Figure 3 is a screenshot of my IIS config for this xmla virtual directory.

Figure 3: IIS xmla Virtual Directory

And for your complete reference this is the list of prerequisite applications that I use :

• Windows XP Professional SP2
  
• Microsoft Internet Information Services 5.1 that comes with Windows XP
  
• Microsoft SQL 2000 SP4 with Microsoft Analysis installed
  
• Java Development Kit 1.6
• Apache Tomcat 5.5.17
• JPivot 1.8
  

JPivot Query File

Now you can create a flat jsp file named salesCube.jsp and put it into your $JPIVOT/WEB-INF/queries folder. In my case the folder is located in C:\apache-tomcat-5.5.17\webapps\jpivot\WEB-INF\queries.

Listing 1 : salesCube.jsp

<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>


select
{[Measures].[Unit Sales], [Measures].[Store Cost]} on columns,
{[Customer].[All Customer]} ON rows
from Sales


MS Analysis Services Cube: Sales (via XMLA)

Pay attention to above listing for uri and catalog attribute of jp:xmlaQuery tag. For uri we will use the msixisapi.dll reference url that we already tested before. And for catalog I'm using Tutorial which is Ms Analysis Server catalog for Foodmart database sample. Make sure that in this example you already have setup Sales cube with Unit Sales and Store Cost measures and Customer dimension.

Get the result by executing http://localhost:8080/jpivot/testpage.jsp?query=salesCube in your browser. If everything working fine you will get the result like figure 4 and figure 5 below. Enjoy the great features of JPivot including drill down option and graphichal cart generation.

Figure 4: JPivot with Axis in hierarchical mode

Figure 5: JPivot with Generated Bar Chart

Conclusion

JPivot is an OLAP web based client that can work perfectly with XMLA standard OLAP provider like Microsoft Analysis Server 2000. You will need an additional installation of Microsoft XMLA SDK and a virtual directory IIS configuration to make it work.

Here I have shown how you can consume MS Analysis's OLAP source using XMLA with a standard JPivot installation. Create a jsp file, specify your jp:xmlaQuery tag's attributes and specify an MDX query. You now have all the rich features of JPivot.

Online Resources

• Julian Hyde Blog
• Javier Gimenez's StrateBI