Two Pentaho questions
Razvan Popovici
Greenhorn
Posts: 14
posted 7 years ago
Currently I am looking for an efficient way to perform the Welch two sample t.test (t.test function in "R", TTEST with heteroscedastic populations in Microsoft Excel) on about 100 million pairs of vectors. The elements of the vectors are extracted from a database. Once the results are computed and stored in a database table, a correction for multiple comparisons (such as Bonferroni or FDR) has to be applied for the 100 millions p-values calculated.
My proposed approach includes MySQL and "R"; the problem is the large number of SQL queries to be executed and, out of the box, the lack of parallelism for both MySQL and "R".
The backup plan considers PostgreSQL and custom PostgreSQL functions, written either in C++ or PL/R.
Would Pentaho make any decisive difference by:
- enabling multicore and/or cluster computation for a more efficient resource usage
- provide a simple, out of the box approach for this class of problems (statistic functions + large data)?
The second question is about reporting, can Pentaho output a report from two databases? It will have to issue a query to each database and merge the results in memory. Since the databases are very large compared with relatively small report (that would fit in memory), it is unpractical to copy the databases to a third database and perform a classic SQL join.
Thank you,
Razvan
PS. quoted "R" because of getting this message:
We're sorry, but your post appears to contain abbreviations that we don't like people to use at the Ranch. Because JavaRanch is an international forum, many of our members are not native English speakers. For that reason, it's important that we all try to write clear, standard English, and avoid abbreviations and SMS shortcuts. See here for more of an explanation. Thanks for understanding.
If the abbreviation occurs within code, you can use code tags to post it successfully.
The specific error message is: "r" is a silly English abbreviation; use "are" instead.
My proposed approach includes MySQL and "R"; the problem is the large number of SQL queries to be executed and, out of the box, the lack of parallelism for both MySQL and "R".
The backup plan considers PostgreSQL and custom PostgreSQL functions, written either in C++ or PL/R.
Would Pentaho make any decisive difference by:
- enabling multicore and/or cluster computation for a more efficient resource usage
- provide a simple, out of the box approach for this class of problems (statistic functions + large data)?
The second question is about reporting, can Pentaho output a report from two databases? It will have to issue a query to each database and merge the results in memory. Since the databases are very large compared with relatively small report (that would fit in memory), it is unpractical to copy the databases to a third database and perform a classic SQL join.
Thank you,
Razvan
PS. quoted "R" because of getting this message:
We're sorry, but your post appears to contain abbreviations that we don't like people to use at the Ranch. Because JavaRanch is an international forum, many of our members are not native English speakers. For that reason, it's important that we all try to write clear, standard English, and avoid abbreviations and SMS shortcuts. See here for more of an explanation. Thanks for understanding.
If the abbreviation occurs within code, you can use code tags to post it successfully.
The specific error message is: "r" is a silly English abbreviation; use "are" instead.
darren hartford
Greenhorn
Posts: 25
posted 7 years ago
In response to the second question - "can Pentaho output a report from two databases?"
The Pentaho Reporting can take a direct feed from the Pentaho Data Integration tool. This means you can leverage the ETL tool to read from multiple RDBMS systems, and MDX query, and Excel, and flow out a single result stream and use that directly into Pentaho Reporting.
You would likely need either custom code to use the two (both PDI and Pentaho Reporting) in an embedded fashion, or use the Pentaho BI Server with it's XACTION orchestration approach (which makes pretty easy to do).
The Pentaho Reporting can take a direct feed from the Pentaho Data Integration tool. This means you can leverage the ETL tool to read from multiple RDBMS systems, and MDX query, and Excel, and flow out a single result stream and use that directly into Pentaho Reporting.
You would likely need either custom code to use the two (both PDI and Pentaho Reporting) in an embedded fashion, or use the Pentaho BI Server with it's XACTION orchestration approach (which makes pretty easy to do).
Maria Carina Roldan
author
Greenhorn
Greenhorn
Posts: 19
posted 7 years ago
Hi, Razvan
With regard to the first question, it's very specific and I don't think I'm the right person to give you a proper answer. I'd sugest you to post this in Pentaho forum
http://forums.pentaho.org/index.php
As to the second question, Pentaho reporting allows you to mix several sources: SQL (more than one DB at the same time), MDX, PDI, etc. In particular, PDI as a source multiply the available combinations, as it allows you to read data from other sources as well (Excel files, etc.)
With regard to the first question, it's very specific and I don't think I'm the right person to give you a proper answer. I'd sugest you to post this in Pentaho forum
http://forums.pentaho.org/index.php
As to the second question, Pentaho reporting allows you to mix several sources: SQL (more than one DB at the same time), MDX, PDI, etc. In particular, PDI as a source multiply the available combinations, as it allows you to read data from other sources as well (Excel files, etc.)
Maria Carina Roldan
Author of Pentaho 3.2 Data Integration: Beginner's Guide
