On the fly Mondrian Date Dimensions
So I have an annoyance that occurs every single time I do a demo or proof of concept with Saiku or other BI tool. I have to spend time, digging out the date dimension sample transformation from Pentaho Data Integration, point it at my new database, and run it to generate a long list of dates and the date parts that allows us as users to slice and dice effectively.
I know that Mondrian now has a Date Generator thing, but thats not what I’m looking for because that involves writing to the database, I wanted something a) quicker to get going b) non destructive.
So a while ago I had an idea, and last night, with the Woodstock Blu Ray on, whilst the Mrs slept and in between giving the baby some formula and answering random questions on the website Help system, I wrote a Mondrian Schema Processor to allow be to inject Date information into my schema as an inline table on the fly.
I’ve been expanding it a bit this morning to provide more flexibility and solve more use cases and now it works well enough, on MySQL at least, for me to publish it and let people have a play.
It is not supposed to provide the same performance that an optimized date dimension on a database itself it supposed to provide. What Mondrian effectively does is create the table structure directly in the query, and as such joining date columns together isn’t the most performant query I’ve ever run, that said, its not that bad, and if you happen to have an incremental ID relating to date in existence, then its actually pretty quick.
I see it being used in demos, proof of concepts, but also in situations where you can’t change the source tables to add a date dimension but you’d still like to break it down and slice and dice.
Currently it only works on Mondrian 4 which is sufficient for my needs because most of the work I do is Saiku Server/Mondrian 4, but it would be pretty easy to port.
Here is a Foodmart example connection string:
As you can see I pass it a start date string, end date string, and which cubes I want it to populate along with the foreign key for the cube. This will then create a date table with date keys to join to the fact. If, like I mentioned, you already have a date key, you can pass it InitID and a value and it will start an Integer key instead of the date key and join to that, which is far more performant.
You can find the source code on github:
Contribute to mondriandategenerator development by creating an account on GitHub.github.com
Or download a build from here.
Let me know if you’d like to see anything else added or changes you’d like to make it more useful.