[Mondrian] MonetDB: Mondrian Foodmart Loader, NUMERIC/DECIMAL, and MDX results

[Brandon Jackson]

This is a screenshot from SquirrelSQL showing the Key IDs with a type of
"INT" and the columns used as measures using "DECIMAL".
http://www.screencast.com/t/YenHXOnbz

In case it is helpful, here was the command I issued to get foodmart to
load.

java -cp
"/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/monetdb-jdbc-2.5.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/eigenbase-xom-1.3.0.11999.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/eigenbase-resgen-1.3.0.11873.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/eigenbase-properties-1.1.0.10924.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/mondrian-3.4.0-SNAPSHOT.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/commons-logging-1.1.1.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/log4j-1.2.14.jar:/Users/bjackson/saiku/saiku-server/tomcat/webapps/saiku/WEB-INF/lib/olap4j-1.0.1-SNAPSHOT.jar"
mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data
-jdbcDrivers=nl.cwi.monetdb.jdbc.MonetDriver
-inputFile="/Users/bjackson/Desktop/AmbientBI/mondrian-3.4.1/demo/FoodMartCreateData.sql"
-outputJdbcURL="jdbc:monetdb://localhost/foodmart" -outputJdbcUser=monetdb
-outputJdbcPassword=monetdb





On Thu, Oct 25, 2012 at 2:53 PM, Luc Boudreau <lucboudreau at gmail.com> wrote:

> It could be that MonetDB returns a type for the cell columns that Mondrian
> doesn't expect or wrongfully maps to a different datatype. By setting it
> manually, you are forcing Mondrian to use a specific datatype and thus
> bypass the problem.
>
> We don't have a dialect specific to MonetDB. We might have to create one,
> but
>
data types are not handled by the dialects.
>
> Can you tell us which is the column type returned by the JDBC metadata for
> these columns? (should be an integer)
>
> Luc
>
>
> On Thu, Oct 25, 2012 at 3:19 PM, Brandon Jackson <usbrandon at gmail.com>wrote:
>
>> I was able to load MonetDB with foodmart using the foodmart loader.  I
>> noticed that when the tables were created some of the columns had the
>> NUMERIC data type.  Everything loaded correctly.  Any SQL queries that I
>> issued doing SUM etc through Squirrel and the relevant JDBC driver worked
>> fine.
>>
>> Mondrian 3.X included with Suite 4.5 issues proper SQL against MonetDB,
>> but the measure results come back as 0 in every query I tried.
>>
>> When I changed the data types to DOUBLE or FLOAT, Mondrian's queries
>> finally show correct results.
>>
>> Where should I look to understand where this problem is entering the
>> picture?
>> I don't know why SquirrelSQL receives and shows a table of correct
>> results, while Mondrian does not.  So far, by process of elimination:
>>
>> Not at fault:
>> JDBC Driver (because SquirrelSQL  uses it and gets correct results)
>> MonetDB (because direct queries work)
>>
>> At fault:
>> Mondrian somewhere...
>>
>> ---
>> Is this a sound way to look at the problem?
>> Would running the test suite against MonetDB give sufficient information
>> to fix this problem?
>> Is a database dialect what prevents these kinds of issues?
>>
>> If I can receive a little guidance where to check and what needs to be
>> done, maybe I can even trace into this issue.
>>
>> Thanks,
>>
>> Brandon
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20121025/00edc649/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: MonetDB-Column_Metadata.jpg
Type: image/jpeg
Size: 408967 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20121025/00edc649/attachment-0001.jpg