Wednesday, September 30, 2009

All in one BI tool for the non-geeks

A colleague of mine once asked me if I knew a program that can connect to almost all relational databases and offers MsAccess like features to build queries. Sure thing says I. Wabit.


So he downloads it and installs it in 5 minutes. It's free and open source. No hassle. He then creates his connections and manages to do everything he needs to fulfil his duties as a business analyst. Pretty kewl story, heh? Short too. But that's a good sign because as a developer on this project, I can confirm first hand that this is exactly what we aimed for. Making business intelligence easy and painless.

The Wabit is more than that. It's also an OLAP data warehouse browsing and reporting tool. It creates charts in 10 seconds and features a template engine for easy corporate branding. Version 1.0 will feature a server repository for multi-user collaboration and incremental saves, scheduling and fine grained security. The enterprise server is not open sourced though, but the Wabit client is a fully featured platform. You can still save all your queries and reports as an XML file for easy import and export and share it with your fellow co-workers.

The Wabit approaches 1.0 now. We need to reinforce the community around it and we need more feedback. The Wabit works on all platforms with a Java JVM, so whatever your background is, I'm sure that we can make good use of your comments or contributions. You are a GUI designer or a BI consultant or even just the regular Java developer, we have need of your help.

Wabit on Google Code
Wabit homepage

Wednesday, September 9, 2009

Olap4j vs. Oracle and Ruby

During my monthly checkup of this blog analytics data (thank you Google Analytics), I discovered a new trend. More and more, people are searching for information on olap4j's compatibility.Here are the interesting keywords used and the number of occurrences for the last month.
  • "olap4j ruby" - 28 occurrences
  • "olap4j oracle" - 3 occurrences
Oracle; I can understand. Olap4j is picking up momentum and is more widely adopted. We support both Microsoft Analysis Services and Mondrian via XMLA. Oracle does have an XMLA server, Hyperion Essbase, although we never tested it with olap4j. If one of you reading this post happens to be a Oracle wizard, please contact us so we can have a chat. The more OLAP servers we support the better.Ruby; now that's intriguing. Ruby can run in a JVM thanks to the JRuby project. Would olap4j work well with JRuby? probably. Are there are any OLAP API for Ruby? Google says no. Digging further in the analytics data didn't reveal to me the actual intent of those who are searching for "olap4j ruby" keywords. What a mystery... I therefore send out there a general call to anyone interested in using olap4j inside JRuby, for we might have common interests.

Friday, August 28, 2009

Creating Mondrian Schemas with Power*Architect

Since I don't have time to write much software myself these days, I figured I'd share this gem with you all. SQL Power, the Canadian Business Intelligence Authority (that's their tag line these days...), sponsors many open source projects. One of them is called Power*Architect; a marvellous cross-platform data modelling tool.

As far as I know, there are close to none "enterprise ready" data modelling tools that work on Linux and Mac. I also suspect none are free, whatever the platform. Visio is certainly not one of them.

Why is it so wonderful? Well, to start with, it can retro/forward engineer most JDBC compatible databases. That's a big plus. And it gets better. You can also use it to create a Mondrian schema. Yep. The team at SQL Power published a tutorial for that last week.

I do have to disclose that I will be working on their projects starting in October. I'm not trying to sell it to you; it's free anyways.  One thing is for sure though. I can't wait to get my hands in there. So I encourage everyone to grab a copy here and fill as many bug reports as you can. It's not 1.0 yet, so community contributions are a must. Having worked with the team for three weeks back in July, I can guarantee that each and every reported bug and suggested feature is closely studied by the development team.

Thursday, August 20, 2009

olap4j - A comprehensive tutorial

I've been very busy lately with the new job comming up and many other changes in my personal life, but fear not; I'm cooking something up for you people. I'm working on a comprehensive guide to olap4j. Many people have expressed a need for a more step-by-step introduction on olap4j, what it is, and how to unleash it's raw power. In the next few weeks, I should be able to finally put some more time on it and release a first final draft. Until then, take care!

UPDATE: I finally finished the tutorial. See http://devdonkey.blogspot.com/2010/09/olap4j-tutorial.html

Thursday, May 21, 2009

Connect Microsoft SQL Server from olap4j

Browsing my Google Analytics statistics, I realized there is a lot of people out there that are searching for ways to connect Microsoft SQL Server with olap4j.

Here is a nice example.

// We must use the XMLA driver.
Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");


// This code is for Java 5. With Java 6, you can directly
// unwrap the underlying connection with the .unwrap() call.
OlapConnection connection =
(OlapConnection) DriverManager.getConnection(


// This is the SQL Server service end point.
"jdbc:xmla:Server=http://example.com/olap/msmdpump.dll"


// Tells the XMLA driver to use a SOAP request cache layer.
// We will use an in-memory static cache.
+ ";Cache=org.olap4j.driver.xmla.cache.XmlaOlap4jNamedMemoryCache"


// Sets the cache name to use. This allows cross-connection
// cache sharing. Don't give the driver a cache name and it
// disables sharing.
+ ";Cache.Name=MyNiftyConnection"


// Some cache performance tweaks.
// Look at the javadoc for details.
+ ";Cache.Mode=LFU;Cache.Timeout=600;Cache.Size=100",


// XMLA is over HTTP, so BASIC authentication is used.
"username",
"password" );



// We can execute a query. MDX of course.
CellSet set = connection.createStatement().executeOlapQuery(
"SELECT {} ON COLUMNS FROM CUBE");


Update : Some useful links

Wednesday, May 20, 2009

PAT 0.3 - Integration and nifty features…

Pentaho Analysis Tool sprint 2 is now over and released since a few weeks. We're currently in the process of wrapping up the third sprint. What's new? What new features will there be?

Nothing very spectacular really. Yet there are a few nice features worth mentioning.

  • Integration with the Pentaho BI server
    PAT can now run embedded in the Pentaho User Console and be configured remotely. It only supports XMLA connections for this first draft, but don't worry; more compatible connection types are to be supported in the medium term. This also means that you can seamlessly use any XMLA provider. Sql Server? Essbase? Mondrian? You choose. All that thanks to olap4j. If you want specific details on this, Gretchen Moran has written a bunch of documentation on this. Congratulations to Paul Stoellberger and Gretchen Moran for this feature.

  • Create multiple queries at once
    This was a requirement that was passed to us by Pentaho's engineering team. People want to build and use more than one query at a time without concurrency issues. That was not properly supported by the age old JPivot application. Now and then we encountered some problems with multiple queries, so this was something pretty high on our features list. The backend supported that since sprint 1 , but there was higher priorities for the GUI components.

  • OlapTableModel first draft
    The Java API doesn't include a proper TableModel for OLAP data, so we're planning to write one. We still have lots of things to figure out on this subject, but we're planning to mock a draft specification before the sprint 3 deadline.


Our initial intent for the third sprint was to make the GUI nicer, but we're still trying to figure out if XUL would be a better generation tool than SWT. We are waiting for developments between the GWT-Mosaic team and Nick Baker of Pentaho and hope that all those nice gents would team up and cook something up to their talent. Fingers crossed here. :)

Also worth mentioning,  we started talks to review the Query model currently residing in olap4j. This is a soon to be major component as it will provide developers and GUI enthusiasts with a properly abstracted API to build queries against a data warehouse. Olap4j is  very great API for low level stuff, but it still needs an abstraction layer for the common folks. Building queries should not require in-depth knowledge of MDX, for you cannot expect all business analysts to master MDX anyways. Anyone who wants to participate in the process is warmly invited to manifest himself. We are really looking for input on this, whatever your background is. (As long as it's related to BI I guess...)

Monday, April 13, 2009

Pentaho Analysis Tool - Final push for sprint 2

I've been really busy lately working on sprint 2 of Pentaho Analysis Tool. We almost reached all our goals for this sprint and are hoping to wrap it all up in the next two weeks. There is still time to add a few late requirements in this sprint so if anyone has a very special wish, now is the time to express it.

About Pentaho Analysis Tool (PAT)


PAT is an attempt to replace the good ol' JPivot application, widely used in the Java world, as a web based browser for OLAP data. There are quite a few similar projects out there, yet none of them quite make the cut in terms of enterprise requirements.

  • Ad-hoc connections

  • ACL management

  • User defined connections saved for later use

  • Saved queries

  • Multiple queries editing at once

  • [ insert even more entreprise software mumbo jumbo here]


We're writing a Google Web Toolkit (GWT) front-end and a Spring based backend as a core. All data manipulations are possible thanks to the Olap4j API. There was talk of a Json bridge later on in development, but this requirement is not part of any sprint planning for now.

The project is hosted on Google code and all the project management is done in the Jira tracker. If you have any further questions about our project or want to chat for whatever reasons, we can be reached via the mailing list or ##pentaho.pat on freenode.

Wednesday, March 25, 2009

Of easy and painless systems monitoring

I'm not a systems administrator. I only have 8 servers to babysit and it used to be enough to be a time consuming problem. You might not be a systems administrator either, nor have many machines / services / websites to monitor, yet the fact remains that as IT professionals we need to keep a close eye on what's going on. I'm not talking about 99.999% uptime here, but a 1% downtime is enough to make a lot of customers, clients and managers angry; especially since outages have a way to happen exactly when it should not.

What are your options? How much does it cost? What can you monitor? These are all questions I'll try to shed a light on. The solution I'm proposing today is one I used myself for years. I'm not legally obliged to 5-9 availability, yet this is what I achieved with a total cost of 0. Yep, z.e.r.o. zero. El zilcho.

I'm not saying this will work for anybody, neither am I pretending to be an expert on the issue at hand, but I learned a lot in a few years on the subject so here it is.

Monday, March 16, 2009

Evading (D)DOS attacks with Apache HTTPD

Just a quick tech-tip. Ever wondered how to prevent your HTTPD server from being knocked off the net by a DOS (Denial Of Service) attack? Check this nifty little module.

Mod Evasive


Its pretty easy to setup. Compile the module as you would normally do for HTTPD modules and create a configuration file. There are many options available. Here's an example of how to configure it.




<IfModule mod_evasive20.c>
DOSHashTableSize 3097
DOSPageCount 6
DOSSiteCount 100
DOSPageInterval 2
DOSSiteInterval 2
DOSBlockingPeriod 600
DOSEmailNotify "my-monitoring-contact@domain.com"
DOSWhitelist  192.168.*.*
</IfModule>


More details on the configuration and how each parameter will affect the module behavior can be found out there on the net.


Beware though, before installing this, make sure you won't blacklist some legitimate users. For example, if you have a AJAX application that sends a burst of requests once in a while, it might get blacklisted. Make sure you test it in a development environment so you get the thresholds right.

Thursday, February 12, 2009

Of economic opinions and commentators

There is a lot of blogging being done out there in these times of economical crisis -- yes, it is a crisis. Most of it is utter garbage, mixing opinions with specifically picked facts to serve a given purpose, yet somehow,  I still find it important to read it all. There is an old saying that goes something like : "Fool is the one who ignores what he considers not worthy, for wise is the one who can learn from anything."

Most of the wisest things i read were published in my favorite monthly publication, Le Monde Diplomatique. On the counterpart, a hellish lot of garbage can be found pretty much anywhere. Then again, during one of my many news scavenging sessions, I was genuinely surprised to find this little post from a man i never noticed before. I do believe this man has a proper sense of economical and political analysis. Here's an excerpt.
US policymakers have ignored the fact that consumer demand in the 21st century has been driven, not by increases in real income, but by increased consumer indebtedness.  This fact makes it pointless to try to stimulate the economy by bailing out banks so that they can lend more to consumers.  The American consumers have no more capacity to borrow.

With the decline in the values of their principal assets--their homes--with the destruction of half of their pension assets, and with joblessness facing them, Americans cannot and will not spend.

Why bail out GM and Citibank when the firms are moving as many operations offshore as they possibly can?

(...)

The US government really has only two possibilities for financing its budget deficit.  One is a second collapse in the stock market, which would drive the surviving investors with what they have left into “safe” US Treasury bonds.  The other is for the Federal Reserve to monetize the Treasury debt.

Monetizing the debt means that when no one is willing or able to purchase the Treasury’s bonds, the Federal Reserve buys them by creating bank deposits for the Treasury’s account.  

In other words, the Fed “prints money” with which to buy the Treasury’s bonds.

Once this happens, the US dollar will cease to be the reserve currency.  

In addition, China, Japan and Saudi Arabia, countries that hold enormous quantities of US Treasury debt in addition to other US dollar assets, will sell, hoping to get out before others.  

The US dollar will become worthless, the currency of a banana republic.

I'll keep on the lookout for more interresting articles on this. I beleive that the current economical difficulties are of enoumous importance to us all. Not only are we at risk of loosing big, decisions will soon be made that will dictate the governance of our everyday life for decades to come. I might don't think much of the last decades of governance we just endured, but I certainly won't fallback to cynicism and apathy.

Comments? More reading suggestions?