PDA

View Full Version : Test Roadmap for Pentaho CE 2



mikepre_olap
01-20-2009, 09:25 PM
Hi All.

Right now I have installed Pentaho CE 2 on Windows XP, Pentaho Schemma Workbench RC (cube designer), Pentaho Metadata Editor 2.0, Admin Console, PDI (Kettle) Kettle 3.1, Aggregation Designer 1.0.0 RC1, Design Studio 2.0 and MySQL 5.1 as the data container.

Pentaho repository is as it comes, running on Hypersonic.

I already connected the platform to SQL Server 2005 running in a Virtual Machine, and I used MySQL Workbench 5.0 to model a relational datawarehouse in MySQL.

I was able to create all the ETL tasks and transformations I needed and passed the data from an OLTP SQL 2005 database to a star schemma in MySQL 5.1 using PDI 3.0

Noy I'm facing the following challenges:

1) Are the apps described above supported in CE 2.0? Specially Aggregation Designer and Schemma Workbench?

2) When using Schemma Workbench, there's no way to create a dimension which has a 2 field foreign key with the fact table. I know Ralph Kimball suggets to convert it using a surrogate key or whatever, but there should be a way to define a 2 fields FK between the Dimension table and the fact table. I can do it in Oracle and SQL Server (2000-2005)

3) When trying to EXPORT to the server the XML schemma, a publication password is needed. Anybody knows the default password?

4) Also, when trying to create a Business Model in Metadata Editor, another password is required to publish it... Is the same password?

5) How can those passwords can be changed? Do I need to edit manually some XML files?

6) Is there any complete documentation for the CE version? As far as I can see, Wiki docs are incomplete... I'll be glad to help finish those documents, as far as I'm able to advance in my evaluation process...

and last

7) Once the ETL packages in PDI 3.0 are know to run OK, how can you publish them to run, let's say, every day at 3 AM?

I know it's a lot of questions, but I really would like to advance and be able to say, "Yeahh, I tested Pentaho and it really works as they say..." :)

Thank you...

Michael

PS.: If for some reason, what I have discovered untill know is valuable to anyone, just drop me an email to share those findings...

bugg_tb
01-21-2009, 01:24 AM
Hi All.

Right now I have installed Pentaho CE 2 on Windows XP, Pentaho Schemma Workbench RC (cube designer), Pentaho Metadata Editor 2.0, Admin Console, PDI (Kettle) Kettle 3.1, Aggregation Designer 1.0.0 RC1, Design Studio 2.0 and MySQL 5.1 as the data container.

Pentaho repository is as it comes, running on Hypersonic.

I already connected the platform to SQL Server 2005 running in a Virtual Machine, and I used MySQL Workbench 5.0 to model a relational datawarehouse in MySQL.

I was able to create all the ETL tasks and transformations I needed and passed the data from an OLTP SQL 2005 database to a star schemma in MySQL 5.1 using PDI 3.0

Noy I'm facing the following challenges:

1) Are the apps described above supported in CE 2.0? Specially Aggregation Designer and Schemma Workbench?

Evey app is fully supported in the CE version of Pentaho


2) When using Schemma Workbench, there's no way to create a dimension which has a 2 field foreign key with the fact table. I know Ralph Kimball suggets to convert it using a surrogate key or whatever, but there should be a way to define a 2 fields FK between the Dimension table and the fact table. I can do it in Oracle and SQL Server (2000-2005)

As far as I know, tough luck, but then again I've never had to look into it :)


3) When trying to EXPORT to the server the XML schemma, a publication password is needed. Anybody knows the default password?

You set it in pentaho-solution/system/publish_password.xml (or somewhere very similar), make sure you restart the BI server after setting it


4) Also, when trying to create a Business Model in Metadata Editor, another password is required to publish it... Is the same password?

Indeed it is squire


5) How can those passwords can be changed? Do I need to edit manually some XML files?

Question answered 2 questions ago


6) Is there any complete documentation for the CE version? As far as I can see, Wiki docs are incomplete... I'll be glad to help finish those documents, as far as I'm able to advance in my evaluation process...

Thats about it at the moment they are reasonably complete, some may say the search function and layout is in fact the problem with the wiki :) Anyway feel free to help contribute and swing by the IRC channel if you need any help or fancy a chat(details below)


7) Once the ETL packages in PDI 3.0 are know to run OK, how can you publish them to run, let's say, every day at 3 AM?

Cron or Windows Scheduler are the current ways to do this properly, depending on what you are running you will need to investigate kitchen or pan I would imagine.
[quote]

Anything else I can be of assistance with? :)

Tom

mikepre_olap
01-23-2009, 03:12 AM
Thank you Tom.

I was able to publish a mondrian schemma with no problem, but only if we're talking about the demo foodmart.xml file included in CE 2.0

First, doing my own cube, I realize Schemma Workbench (the one I have) has some limitations, since it does not generate the schemmas as Mondrian expects them.

I downloaded foodmart database dump for MySQL so I can test foodmart schemma. I was even able to design aggregation tables using Aggregation Designer and I can create very nice Analysis Views from user console.

But when I try to create from scratch an XML schemma (cube), I'm able to see aggregations for measures, but I'm not able to drilldown a dimension. If I follow videos at http://www.pentaho.com/products/demos/presales_tools.php I see another interface for schemma workbench, probably Enterprise Edition one. If I follow those instructions, as I said, I can build a cube, with no possibility to drilldown a dimension thas has only one level, no matter I make that dimension local to the cube, or public to the schemma and later on I use Dimension Usage feature.

What am I missing? Do I need a newer CE Schemma Workbench? Is this available only in EE? The one I was able to download from Pentaho site is "Release Candidate based on Mondrian HEAD, revision 9247".

- Second, when trying to create an analysisview.xaction from within Design Studio, the cube never shows up. For example, in the General Tab of the xaction, the result type created with the wizard is Analysisview, but the only way to see the cube is changing that manually to "report", as the one generated from NEW ANALYSIS VIEW in the user console inside Pentaho.

I found is much easier to create a New Analysis View from the user console, that way the xaction is created perfectly well.

So before entering in reporting and dashboards, I would like to know if the Schemma Workbench I have has a problem and need a newer one, or should I code it by hand using notepad :eek: :(

Regarding the double field foreign key, I guess I have to remodel the fact table and dimension table to use inly one field.

Thank you very much

Mike

dibe0015
01-23-2009, 03:51 AM
Hi Mike,

limitations of the Schema Workbench can't be the problem for not being able to create a drill down, it must be a problem with the model or the underlying data you have.

Maybe you can attach your Schema in the forum and some of the experts here can have a look at it.

If you were able to create a schema, I'm sure you know that documentation already: http://mondrian.pentaho.org/documentation/schema.php , it explains everything thats needed in the schema.

Benjamin

pstoellberger
01-23-2009, 06:13 AM
by the way.. if you have downloaded schema workbench from the pentaho sourceforge site you probably want to use the newer version... which you can get at the mondrian sourceforge project here: http://sourceforge.net/project/showfiles.php?group_id=35302&package_id=231604

mikepre_olap
01-23-2009, 09:26 AM
Thank you pstoellberger :):

You made my day... I did have an outdated version... This will allow me to continue testing...

Benjamin, thank you for your time. The data and star schema was previously tested with MS SSAS, so it should work. Al data is consistent, PK are well defined and also FK.

I think that you're right, I've been reading the guidelines to manually create a schema, something one should do at least once in a life, but we're in 2009 :D and I would expect to have a sort of help (in this case, Schemma Workbench).

Indeed, before writing my original post, I took the time to compare XML files (the one from foodmart.xml that comes with CE 2.0 and the one I created, and beleive me, they are "quite" different, that made me think I had an outdated version of the workbench...

I will continue testing...

This is like a chocolate bar... can't stop untill there's no left :cool:

Thank you again

Michael

bugg_tb
01-23-2009, 09:28 AM
This is like a chocolate bar... can't stop untill there's no left :cool:


Or be wise and don't start in the first place!

mikepre_olap
01-23-2009, 12:38 PM
Not an option... :)

I'm quite excited about this new release, as I said, I've been reading and kind of testing Pentaho from some time now... I would say this is the first release not targeting Java developers (which I would like to be, but I'm not!!)

Second, and to be honest, Benjamin answer was, as usual, very helpul... It happens that inadvertedly, I run the ETL packages with the source server down, so I got empty dimension tables (the packages truncated the tables before loading them) and a fact table full of data, that's why I had aggregations for measures and no ability to drilldown... sorry for that... Release 2 of those transformations will include Slowly Changing Dimension algorithms...

My cubes are up and running and, as I said, no way NOT to test and stay away from CE 2...

The chocolate??? just a saying, Indeed I do not eat much :D

Cheers and I'll be around

Michael