Sunday, March 20, 2016

Installing the Open Source Xavier XML/A client on the Jedox Premium OLAP Suite

Jedox is a software vendor that specializes in OLAP services and solutions. The company has been around quite a while and is probably best known for their PALO MOLAP engine and the matching add-in for Microsoft Excel.

Jedox' flagship product, Jedox Premium comprises the Palo MOLAP engine, API's, a REST server, and ETL server, and client tools. It also comes with a MDX interpreter and a XML for Analysis server. An interesting tidbit is that the MDX layer is not considered native, and Jedox' own clients use a lower level API, or address it via the REST service.

In this blog post I will explain how to install and configure the Open Source browser-based ad-hoc query and analysis tool Xavier to use it with Jedox. A video of the process is embedded below:


Here's a written list of instructions to get up and running with Xavier and Jedox:
  1. Download Jedox Premium. Run the downloaded installer to actually install the product. By default, it will be installed in C:\Program Files (x86)\Jedox\Jedox Suite. In the remainder of this post, I will refer to this directory as "the Jedox Suite directory".
  2. Download xavier.zip. Unpack the zip. A xavier directory will be extracted.
  3. Stop the JedoxSuiteHttpdService. If you don't know about windows services, then look here.
  4. Copy the xavier directory that you extracted from xavier.zip into the Jedox Suite\httpd\app\docroot directory.
  5. Open the Jedox Suite/httpd/conf/httpd.conf file in a text editor. You should probably make a backup copy of the httpd.conf file before editing it so you can always revert your changes.
  6. Add a line to load the HTTP proxy module. To do that, search the httpd.conf file for a bunch of lines that start with LoadModule. Look for a line that reads:

    LoadModule proxy_http_module modules/mod_proxy_http.so

    In my installation, the line is already present, like this:
    <IfDefine JDX_DEV>
    LoadModule log_config_module modules/mod_log_config.so
    LoadModule proxy_http_module modules/mod_proxy_http.so
    LoadModule setenvif_module modules/mod_setenvif.so
    </IfDefine>
        
    Now, what you'll want to do is to cut this line out of the <IfDefine JDX_DEV> block, and put it outside that block, for example, right before it, like this:
    LoadModule proxy_http_module modules/mod_proxy_http.so
    <IfDefine JDX_DEV>
    LoadModule log_config_module modules/mod_log_config.so
    LoadModule setenvif_module modules/mod_setenvif.so
    </IfDefine>
        
  7. Add a proxy configuration so that web applications deployed on the Apache HTTP server can access the Jedox XML/A service as if it lives in the same domain as the web application. To do that, add a Location directive at the end of the httpd.conf file, like this:
    <Location /xavier/Xmla>
      ProxyPass http://localhost:4242/xmla/
      ProxyPassReverse http://localhost:4242/xmla/
      SetEnv proxy-chain-auth
    </Location>
        
    This allows a web application on the Apache HTTP server to access the XML/A service via the URL /xavier/Xmla. By default, the place where the Jedox XML/A service lives is http://localhost:4242/xmla. You can verify this by crosscecking this with the configation in Jedox Suite\odbo\config.ini: the values for the MDXAddress and MDXPort should match the server and port in the URLs configured for ProxyPass and ProxyPassReverse
  8. Save the changes to your httpd.conf file, and start the JedoxSuiteHttpdService. If the service starts, you should be good to go. If it doesn't, check the Jedox Suite/log/apache_error.log file and see if you can find some information there that can help you troubleshoot your problem.
If all went well, you should now be able to navigate to http://localhost/xavier/resources/html/index.html and you should see the xavier welcome screen. Note that this assumes the Jedox HTTP server is running on its default port (80). If you chose another port for the HTTP server when installing Jedox, the URL for xavier would have to be amended respectively. For example, I chose port 8181, and hence my URL would be http://localhost/xavier/resources/html/index.html instead.

If you're in doubt what port you chose for your Jedox HTTP server, you can look it up in the Jedox Suite/httpd/conf/httpd.conf file. Look for a line that starts with Define JDX_PORT_HTTP. The port is specified right after that, enclosed in double quotes.

Saturday, March 19, 2016

Installing the Open Source Xavier XML/A client on icCube OLAP suite

IcCube is a relatively young Swiss company specialized in creating OLAP software products. Their flagship product is the icCube Suite, providing an OLAP server, an XML for Analysis service, and a bunch of browser-based client tools for modeling cubes and dimensions, and for querying, reporting and visualizing OLAP data.

When you download the icCube trial edition, you'll get a java web server with an already deployed instance of the icCube java web application. This set-up makes it super-easy to install any browser-based third party XML/A clients for icCube.

In this blog post I want to provide you with the instructions to deploy the ad-hoc OLAP query tool called Xavier directly on your icCube server. This allows you to do ad-hoc, drag 'n drop style reporting on any data exposed by the icCube server.

If you like, you can watch the video below to see how it's done. Detailed, step-by-step instructions appear in writing right below the video.



  • Go to the icCube download site. Find the appropriate version for your operating system, and click it to download the installer.
  • After downloading the icCube installer, run it. This will install icCube for you.
  • Download xavier.zip.
  • Unpack xavier.zip. Move the extracted xavier directory to web directory below the icCube home directory
  • Start icCube.
If all goes well you should now be able to navigate to xavier. If you installed icCube locally and kept the default ports, this would be the link: http://localhost:8282/xavier/resources/html/index.html

I hope this was useful for you!

Need a Mondrian .WAR? Check out XMondrian.

To whom it may concern, this is a quick note to bring the xmondrian project to your attention.

Introduction: Open Source OLAP, Mondrian, Pentaho, and JasperSoft

Mondrian is the open source OLAP engine. Mondrian provides:
  • a multi-dimensional view of a relational database (ROLAP)
  • a MDX query engine
  • Clever, advanced caching layers to speed up OLAP query performance (making it a MOLAP/ROLAN hybrid i.e., HOLAP)
  • Standards compliant OLAP data access by providing XML for Analysis (XML/A) and OLAP4J access APIs
Mondrian was designed and invented by Julian Hyde, who acted as technical and architectural lead of the Mondrian project for many years.

Mondrian was adopted by Pentaho, and is included in the Pentaho BI Stack as Pentaho Analysis Services. Mondrian is also the OLAP engine that ships with the Tibco/JasperSoft Reporting server, and with Meteorite BI's Saiku product.

Running Mondrian Standalone

While Pentaho, Jaspersoft and Meteorite all do a good job of integrating Mondrian inside their respective BI servers, some people would like to run only Mondrian directly in their java servers. The Mondrian project used to make that quite easy, since it shipped a .WAR (web-archive) file containing Mondrian itself, documentation, sample cubes, and the JPivot mondrian client.

Unfortunately, the Mondrian project stopped supporting the .WAR and sample content. This happpened a while ago already, but there are still people that are finding out about it only now. This might have to do with the fact that the Mondrian documentation has not been very well maintained and still refers to the .WAR as if it is part of the Mondrian project.

Introducing XMondrian

I felt the need to have a Mondrian .WAR myself. Main reason is that I created a couple of OLAP client tools myself, and I want to provide potential users with a quick and easy path to check them out. So, I decided to pack them all in a .WAR, together with Mondrian, the Foodmart Sample cube, and an embedded dataset.

The result is called xmondrian which you can find on github.

Getting started with XMondrian

Getting started with XMondrian is easy:
  • Download the .WAR file
  • Deploy to your java server. In theory, the process to do that will be dependent upon which webserver you are running. I tried with Apache Tomcat, Jetty, and Tiny Java Web Server, and for all these products you can simply copy the .WAR to the webapps directory
  • Find the XMondrian homepage by navigating your browser to the xmondrian webapp. For example, suppose you installed Tomcat or Jetty locally, using the default port of 8080, then http://localhost:8080/xmondrian will bring you there.

What's inside XMondrian

Once you're on the XMondrian homepage, you can find more information about what's inside, but I'll summarize below:
  • Mondrian 3.12
  • A web.xml to instantiate and hook up the MondrianXmlaServlet. After installation of xmondrian, your webserver can receive XML/A requests via /xmondrian/xmla
  • HSQLDB embedded database engine
  • Sample Datasets and Schemas Both the Foodmart and Steelwheels datasets are included as embedded hsqldb database in a .jar file. There are predefined Mondrian Schema files for each dataset as well, which specify how these databases are mapped to cubes, measures, dimensions, etc. Finally, there are datasource files that tell mondrian to connect to the sample database and use the respective schema file
  • xmla4js - A javascript XML/A client library. You can use this in browser-based web applications to communicate with Mondrian via the XML/A protocol. Xmla4js ships with code samples as well as API documentation
  • Client Applications
    • XMLash - XML/A Shell: an interactive MDX command line interface for inspecting Mondrian schema objects, an for creating and running MDX queries. (See a demonstration )
    • Xavier - XML/A Visualizer: an interactive OLAP ad-hoc reporting and charting tool with a graphical user interface

The XML/A Shell Application

:

The XML/A Visualizer Application

:

Finally

I hope this post was useful to you. Please let me know how you get along with the xmondrian .WAR. I'm open to suggestions and I would love to collaborate to make xmondrian better. Please use the github issue tracker to provide your feedback. Thanks for your time and interest.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...