Hi,
I’ve finally found the time to upgrade my ICE 3.1 RC1 and try it with Mondrian as well.
I was also hoping the new version will let me use substring etc functions in GROUP BY, as I have my 300Million row table loaded and it has datetime column however I need results filtered and grouped by date only, not datetime… Unfortunately seems I can’t do it yet with ICE 3.1. GA, as I must depend on the datetime in its entirety. I simply don’t have the time to reload a new table to make those datetimes to date column.
Other than this, I also tried some OLAP-like querying using a time_by_day table so I joined my datetime column to the one in time_by_day and I got it working for situations when I want to elegantly run a query that filters on certain Quarter or Week or Month etc without specifying and figuring out date ranges (at least it worked fine for my datetimes that had 0 in the time part so the join worked). However I found the join is terribly slow, found it much faster to run the query directly against the table where I specify a BETWEEN op in the WHERE clause for my actual date range. This is not good but works. May have to do with the star schema discussion I read a little about… But I am a little loath to move all those dictionary cols into the main table and reload again… I think it is a question of two famous forces: clarity of code and speed. I think clarity of code (using separate tables) is very important but I have to shed it for sake of speed for now…
Finally, I was fooling around w/ Mondrian and I really liked it, kudos to the developers of it. I am especially impressed with MDX, which in itself is a miracle, funny I never used it before. Now onto the downside…. I had to painfully experience that it (ununderstandably) creates those funny UPPER(..) statements which should never be used on a RDBMS as it defeats the purpose of using indexes… With some config param (case sensitivity) I was able to get rid of it, but still the queries did not run with ICE 3.1 GA at all. I read somewhere that it is in the works to include support for IB in Pentaho EE, but c’mon, I really want this with Mondrian and ICE. So now since I dont know how long (it at all) it is before Mondrian supports ICE, my goal is to find some MDX processor that gives me decent queries that can be run against ICE fine. If think if mysql peculiarities had been avoided it would run fine with ICE as well, but they were not. What we need is some MDX processor that creates dumb-simple queries (even thousands of them) but against ICE. So far haven’t found much in the open source arena… Was looking at BEE but it talks little about MDX… Also OpenI seems to hide all this and just builds on Mondrian… There should be a simple tool around that takes and MDX query as an input and spits out a bunch of SQL queries on the output (maybe even according to different SQL standards/flavors). If you know of any such tool please drop a line, much appreciated.
Best to everyone,
Csaba
