FoodMart data import

[tomislav]

Hi everyone,

I've tried just about everything...and the only reason I say "just about" is because it seems that I'm the only one with this kind of problem: when I run the MondrianFoodMartLoader, it simply exits with a couple of irrelevant log4j warnings. I'll copy them just for clarity:

log4j:WARN No appenders could be found for logger (mondrian.test.loader.MondrianFoodMartLoader).
log4j:WARN Please initialize the log4j system properly.

I've tried importing data into MSSQL Server 2000 using the jtds-1.0 JDBC driver as well as a PostgreSQL 8.0 (both on Win 2003 Server and Gentoo linux...well, postgressql on both, anyway :)). The startup scripts I used are as follows (don't know how this is going to look like because of the forum content parser/filter):

1. for Postgresql 8.0:
#!/bin/bash
trenutni=`pwd`
java -cp
"$trenutni:$trenutni/mondrian/lib/mondrian.jar:$trenutni/mondrian/lib/log4j.jar:$trenutni/mondrian/lib/eigenbase-properties.jar:$trenutni/mondrian/lib/eigenbase-resgen.jar:$trenutni/mondrian/lib/eigenbase-xom.jar:$trenutni/mondrian/lib/jtds-1.0.jar" mondrian.test.loader.MondrianFoodMartLoader -tables -data -indexes -jdbcDrivers=org.postgres.Driver -inputFile=$trenutni/mondrian/demo/FoodMartCreateData.sql -outputJdbcURL="jdbc:postgresql://192.168.0.3:5432/FoodMart" -inputJdbcUser=postgres -inputJdbcPassword=....

2. for MS SQL 2000
#!/bin/bash
trenutni=`pwd`
java -cp
"$trenutni:$trenutni/mondrian/lib/mondrian.jar:$trenutni/mondrian/lib/log4j.jar:$trenutni/mondrian/lib/eigenbase-properties.jar:$trenutni/mondrian/lib/eigenbase-resgen.jar:$trenutni/mondrian/lib/eigenbase-xom.jar:$trenutni/mondrian/lib/jtds-1.0.jar" mondrian.test.loader.MondrianFoodMartLoader -tables -data -indexes -jdbcDrivers=net.sourceforge.jtds.jdbc.Driver -inputFile=$trenutni/mondrian/demo/FoodMartCreateData.sql -outputJdbcURL="jdbc:jtds:sqlserver://192.168.0.3/FoodMart?user=...&password=..."

I'm working on a linux machine with J2SE 1.5-06 with the most recent mondrian (2.0.0) and pentaho (1.0 RC2) binaries.

The funny thing (aside from having lost the better part of this day trying to solve this problem) is that the mondrian loader doesn't complain if I missspel the path and/or name of the eigenbase-* jars, if I use invalid credentials, if I reference a non existing server and/or database...it's just sensitive to log4j and mondrian.jar. Doesn't seem like it does anything with the jdbc driver, really, but it doesn't complain, either.

Oh, and one more thing: everyone seems to use the "-verbose" flag, but the version of mondrian loader I use complains when I use it and displays a usage screen listing other flags, but not "-verbose". Can't help but wonder about the versions...

TIA,
Tomislav

[jhyde]

Hi Tomislav,

I feel your pain. I tried to use the '-verbose' option a few days ago, and noticed that one of the Mondrian developers had removed it when he introduced log4j logging. I added back the '-verbose' option and kept the log4j logging facility also.

If you're running on the very latest mondrian, you can use '-verbose'. This will also be released in mondrian-2.0.1 very shortly.

You can enable error output in the current version. Create a log4j.properties file with the following contents:

log4j.rootLogger=DEBUG, MONDRIAN
log4j.appender.MONDRIAN=org.apache.log4j.ConsoleAppender
log4j.appender.MONDRIAN.layout=org.apache.log4j.PatternLayout
log4j.appender.MONDRIAN.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
log4j.category.mondrian.test.loader.MondrianFoodMartLoader=DEBUG, MONDRIAN
# End log4j.properties

Run MondrianFoodMartLoader with this file on the classpath, and you should get plenty of output!

Let me know if this solves your problem.

[tomislav]

At least for now, I've moved my focus to mondrian-mssql server communication but here goes...

The log4j settings really shed some new light on the problem and now it's clear - at the lowest level - why there is no data in the database.

Here's what I get when I run:


850 [main] INFO mondrian.test.loader.MondrianFoodMartLoader - CREATE TABLE "currency"(
"currency_id" INTEGER NOT NULL,
"date" DATE NOT NULL,
"currency" VARCHAR(30) NOT NULL,
"conversion_ratio" DECIMAL(10,4) NOT NULL)
973 [main] ERROR mondrian.test.loader.MondrianFoodMartLoader - Main error
mondrian.olap.MondrianException:
Mondrian Error:Mondrian loader could not create table 'currency'.

...

Caused by: java.sql.SQLException: Column or parameter #2: Cannot find data type DATE.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367)

...


It seems that the FoodMart DDL code is hardcoded into java classes (for heaven's sake, why?) so I took the oposite route: I added a custom datatype "DATE" (as a datetime value) and ran again. The result?
A similar error, now on the employee table, with the following definition:


796 [main] INFO mondrian.test.loader.MondrianFoodMartLoader - CREATE TABLE "employee"(
"employee_id" INTEGER NOT NULL,
"full_name" VARCHAR(30) NOT NULL,
"first_name" VARCHAR(30) NOT NULL,
"last_name" VARCHAR(30) NOT NULL,
"position_id" INTEGER,
"position_title" VARCHAR(30),
"store_id" INTEGER NOT NULL,
"department_id" INTEGER NOT NULL,
"birth_date" DATE NOT NULL,
"hire_date" TIMESTAMP,
"end_date" TIMESTAMP,
"salary" DECIMAL(10,4) NOT NULL,
"supervisor_id" INTEGER,
"education_level" VARCHAR(30) NOT NULL,
"marital_status" VARCHAR(30) NOT NULL,
"gender" VARCHAR(30) NOT NULL,
"management_role" VARCHAR(30))

Here the timestamp fields are the problem:


Caused by: java.sql.SQLException: A table can only have one timestamp column.
Because table 'employee' already has one, the column 'end_date' cannot be added.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367)


I tried the same approach again but was unable to create a custom timestamp datatype: "the datatype already exists in the current database"?!

Anyway, I'm back to square one.
Can't initialize FoodMart sample database, can't connect mondrian to sqlserver (details in the analysis-issues forum). Stuck between a rock and a hard place...

[jhyde]

The problem, as you deduce, is that the loader is not using the right column datatype. This problem was fixed in perforce change 4616 (see http://perforce.eigenbase.org:8080/@md=d&cd=//&c=Fz6@/4616?ac=10 ) and released in mondrian 2.0.1.


It seems that the FoodMart DDL code is hardcoded into java classes (for heaven's sake, why?)


We couldn't put the DDL statements into a file because every RDBMS seems to have its own unique DDL syntax and set of datatypes. This RDBMS/driver combination simply fell through the cracks for a while.