WAMOnline Blog

I was playing around trying to troubleshoot a report I was having problems with, when I struck upon another issue that I didn’t know how to solve, updating the JFreeReport Jar’s without reinstalling the whole lot. This is how its done….

Download the latest archive

Extract the pentaho-reporting/libs/ directory and dump its contents in

jboss/server/default/deploy/pentaho.war/WEB-INF/lib/

When you restart the system, if there is a load of error messages, remove the gnujaxp.jar file as it caused me a right load of problems.

This may sound rather obvious but it took some working out on my part. I wanted a report to be emailed out but the query was MDX, and the report designer doesn’t directly support MDX generation, ie: unlike sql you can’t see what your doing.

So publish an analysis cube with the JPivot stuff, load it up and work out how you want your table displayed and setup. It may be an idea to clean up the code a bit but once that’s done copy it out, and paste it into the report wizard/designer and it should all work, just make sure you use the same cube

Another rather obvious tip but certainly useful.

If you want to load the selections for a secure filter/prompt in a xaction dynamically, then in the editor select a Get Data -> relational

Input the query to retrieve the relevant information and then setup the result-set at the bottom of the page and assign the columns.

The in the Secure Filter/Prompt do the usual just selecting the dynamic result-set. As long as the details are correct you will see the query results next time you run the xaction.

Here’s a quick tip, if you generate calculated members in your code but can’t work out how to format them this is for you..

For some reason Members and Calculated Members have different syntax to format calculated members add them in a similar way to the code here.

<CalculatedMember name="Rolling Budget Margin" dimension="Measures">
<formula>
((([Measures].[Current DAY_ID] / [Measures].[Month DAY_ID])) * [Measures].[Budget Margin])
</formula>
<CalculatedMemberProperty name=”FORMAT_STRING” value=”#,#00;;\Z\e\r\o”/>
</CalculatedMember>

I am going to assume you’ve at least got Pentaho installed and started, if you haven’t pop over the site and pick up a copy of the installer to get you started. The first thing to make everyone aware of because it will be your life saver, is Mondrian the MDX server. Now I have to admit that when I first installed Pentaho I knew absolutely nothing about MDX, Mondrian or anything of that nature, hell I hadn’t used SQL since I was at uni a few years ago. So my boss gave me the task of bringing the 100’s of reports we create every month together into a more centralised and automated fashion, so I started looking at ways of doing this. I started by scripting a website, creating the tables and code straight in PHP, HTML and an excel generator for some proper output, I got the first few reports done but there had to be a better solution.. and on a budget. I played around with a few CMS systems to see if they fit the bill but they still involved a lot of coding, the eventual output looked better but creating the reports was going to be a chore. Then I stumbled across Pentaho, it look just like what I wanted, an open source(I’ve been using Linux for years), web based, business intelligence suite. So I got it installed and looked at the sample data, bingo I thought, but how does this all work then? I uploaded some of my data to the database and set to work trying to figure out how it all worked. I played for days trying to generate useful data just with SQL and various report types, but never getting close to what I wanted, I had picked one of the harder reports to start with but what the hell. Then I thought what about this MDX stuff I’ve been seeing whist playing around and my quest for the best reporting system began.

MDX history

MDX stands for Multidimensional Expressions it is a query language for Online Analytical Processes(OLAP) Databases and cleverly it allows relatively easy calculations using formula’s similar to spreadsheets. To my surprise it was actually invented by a guy at Microsoft, they’ve come up with a useful specification and actually seemed to keep it pretty quiet. So unsurprisingly it runs on MSSQL, MYSQL, Oracle, DB2, the usual culprits.

How it works

Well MDX with Pentaho works by creating a Cube which references tables in a main database. In this Cube you will define ‘Dimensions’, ‘Hierarchies’, ‘Levels’ and ‘Measures’ these create the levels of information that the MDX server uses to reference. So for example you may have:

<Dimension name="TIME" foreignKey="DATE_ID">
<Hierarchy name="TIME" hasAll="true" allMemberName="All time" primaryKey="DATE_ID">
<Table name="TIME"/> <Level name="YEAR" table="TIME" column="YEAR_ID" uniqueMembers="false"/>
<Level name="QUARTER" table="TIME" column="QUARTER_ID" uniqueMembers="false"/>
<Level name="MONTH" table="TIME" column="MONTH_ID" uniqueMembers="false"/>
</Hierarchy>
</Dimension>

So as you can see first we define the dimension which in this case is called ‘Time’ which references the foreign key ‘Date_ID’.

Next we define a hierarchy which in this case is also called ‘Time’.

Then we define the levels which are reasonably self expanatory these decend in diminsihing size as it quite obvious with this date example.

<Measure name="TOTAL_SALES" column="TURNOVER_EUR" aggregator="sum" datatype="Numeric" formatString="# ##0,###"/>

Lastly we can create Measures which do pretty much do as the name suggests and we use them as the core data in our tables. The name is the name you want to see in the cube, the column if the column name from the database, there are several different aggregators your can set as with datatypes and formatStrings.

But don’t worry this doesn’t all have to be coded by hand Pentaho have developed a Cube Designer which you can download and use to create and upload the cubes to your server. This is much easier than creating by hand, the only problem being at the moment your can’t reopen a saved cube for editing, so once you’ve published your cube check it to make sure its as you expect before you close the designer, because you can always make adjustments and re-upload. Once the cube is published you can always edit the xml file directly, it will be called cubename.mondrian.xml. Take a look and check out its structure.

Anyway, I’ll be back later with the basics of starting query writing.

I dreamt up the idea of this blog whilst sitting around at work this week, I’ve been hammering away at Pentaho for weeks now and not really documented a single thing I’ve done, sure I can look at my code but I have nothing documented. So I recon I’ll start from the ground up. Yes I realise there wont be anything ground breaking in this blog as of yet, but I hope my notes and code will help people who stumble across here with the more obvious problems that I’ve encountered over the last few weeks whilst setting up a Pentaho Server at work.

Also a big thanks to the folks at the forums(especially Taqua) for answering my rather stupid and plain obvious questions, and for bug fixing.

With WAMOnline closing a year or so ago I was at a bit of a loose end as with what to do with the site, I can’t really let it go as that would mean changing email addresses and that would be a pain in the behind. So as my new job largely entails business intelligence and data analysis, I thought it may be a good idea to store my hints, tips and thoughts online as a thanks to the people who created such good software and then decided to give it away.

I’ll also blog about other stuff that I’ll try to keep separate so check back to see whats been going on, and I promise to make it look a bit more aesthetically pleasing over the next few weeks.

Tom