Chapter 5
[
133
]
StaticSessionProvider
The
StaticSessionProvider
simply takes in an
org.hibernate.Session
object
as a constructor parameter, making available the already existing
Session
object to
the
HQLDataFactory
. This would be used if your system already has an initialized
Hibernate session.
DefaultSessionProvider
The
DefaultSessionProvider
requires no constructor parameters, and uses
the following API call to generate a
SessionFactory
from Hibernate:
sessionFactory = new Configuration().configure().
buildSessionFactory();
The created
sessionFactory
instance is used to create new sessions, which
the
HQLDataFactory

by
HQLDataFactory
.
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Working with Data Sources
[
134
]
HQLDataFactory Example
To demonstrate using
HQLDataFactory
, you must rst set up a simple
Hibernate application. To begin, download the latest version of Hibernate from

. This example uses version 3.2.6.ga. Place the
hibernate.jar
le and all the JAR les from the Hibernate distribution's
lib

folder into the
chapter5/lib
folder. You must also deploy the
pentaho-
reporting-engine-classic-extensions-hibernate.jar
le, located in
Pentaho Report Designer's
lib
folder, into the
chapter5/lib

}
}
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 5
[
135
]
Dene the Hibernate mapping between the HSQLDB database and the
LibraryInfo

class, saved as
chapter5/src/LibraryInfo.hbm.xml
:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
" /><hibernate-mapping>
<class name="LibraryInfo" table="LIBRARYINFO">
<id name="name" column="name" type="string"/>
<property name="description" type="string"/>
<property name="size" type="long"/>
</class>
</hibernate-mapping>
Now, you're ready to congure the Hibernate settings le with the appropriate
JDBC information and mapping input. Save the following as
chapter5/src/
hibernate.cfg.xml
:
<?xml version='1.0' encoding='utf-8'?>

method
within a freshly copied
Chapter2SwingApp
, renamed to
HQLDataFactoryApp
:
// load hql data source
DefaultSessionProvider sessionProvider = new DefaultSessionProvider();
HQLDataFactory factory = new HQLDataFactory(sessionProvider);
factory.setQuery("default", "select name as NAME, description as
DESCRIPTION, size as SIZE from LibraryInfo");
report.setDataFactory(factory);
Be sure to add the following import statements at the beginning of the le:
import org.pentaho.reporting.engine.classic.extensions.datasources.
hibernate.DefaultSessionProvider;
import org.pentaho.reporting.engine.classic.extensions.datasources.
hibernate.HQLDataFactory;
Due to the naming of column headers in
HQLDataFactory
being mapped to
the attributes of queried objects, you must also modify the sample report. Copy
chapter2_report.prpt
to
chapter5/data/hql_report.prpt
, and change the
column names, as shown in the following list:
•
Library Name
to
NAME

]
PmdDataFactory
The
org.pentaho.reporting.engine.classic.extensions.datasources.pmd.
PmdDataFactory
class allows you to populate your report, using a Pentaho Metadata
Query. Pentaho Metadata allows a database administrator to dene a business layer
of their relational data for end users, simplifying the ability to query the data, as
well as protecting users from the complexities that may exist in a database schema.
Pentaho's Metadata Query Language (MQL) is an XML-based query model that
simplies querying databases, and is currently used within the Pentaho Report
Designer and Pentaho Web Ad Hoc Report client tools.
In order for
PmdDataFactory
to initialize properly, it must have access to certain
Pentaho Metadata conguration properties that can be congured at runtime, or
be passed in by a conguration le.
XMI le
The XMI le contains a serialized version of the dened metadata model, and
is required in order to execute MQL queries. The XMI le contains information
including how to connect to the relational data source, as well as the business
model mapping of the relational data. This le is loaded at runtime into the
congured repository of Pentaho Metadata. The XMI le may be congured by
calling the setXmiFile method. This le is loaded with Pentaho Reporting Engine's
ResourceManager
.
Domain Id
The metadata domain id is used to map a name to the XMI le within the metadata
repository. This name is also referenced in the MQL query le. Therefore, it is
important to use the same name in the MQL query, as well as the

Connection gu8etConnection(DatabaseMeta databaseMeta) throws
ReportDataFactoryException;
// returns a metadata repository based on the domain id and xmi file
IMetadataDomainRepository getMetadataDomainRepository(String domain,
ResourceManager resourceManager, ResourceKey contextKey, String
xmiFile) throws ReportDataFactoryException;
Registering MQL Queries
Once you've congured the
PmdDataFactory
correctly, you need to provide named
MQL queries via the
setQuery(String name, String query)
method. Please see
/>Schema
to learn more about the MQL query format.
PmdDataFactory example
To begin, you'll need to build a very simple Pentaho Metadata model. First,
download Pentaho Metadata Editor from SourceForge:
/>projects/pentaho
. Click on the Download link, and select the Pentaho Metadata
package. Download the latest "pme-ce" zip or tar distribution, depending on
your operating system environment. For Windows, unzip the download, and run
metadata-editor.bat
. For Linux and Mac, untar the download and run
metadata-editor.sh
. From the main window, select File | new Domain File...
Now, it's time to dene your physical model. Right-click on the Connections tree
item and select New Connection... Name the Connection Library Info and select
Hypersonic as the connection type. Set the Host Name to le: and the Database
Name to the full path to your example

category with the same name as the business table.
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 5
[
141
]
Once completed, the main Business Model Tree should look like this:
Now that you've dened your metadata model, export the model as an XMI le by
selecting the File | Export to XMI File... menu item. First, you will be prompted to
save the Domain le. Name the Domain Library Info. Finally, save your XMI le as
chapter5/data/libraryinfo.xmi
.
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Working with Data Sources
[
142
]
Once you've exported your metadata model, you must set up your environment with
the necessary JAR les. Copy all the JAR les located in the
lib
and
lib-ext
folders
from the Pentaho Metadata Editor distribution into the
chapter5/lib
folder. Also,
copy the
pentaho-reporting-engine-classic-extensions-pmd.jar

" </selection>" +
" <selection>" +
" <view>BC_LIBRARYINFO</view>" +
" <column>BC_LIBRARYINFO_DESCRIPTION</column>" +
" </selection>" +
" <selection>" +
" <view>BC_LIBRARYINFO</view>" +
" <column>BC_LIBRARYINFO_SIZE</column>" +
" </selection>" +
" </selections>" +
"</mql>");
Notice that MQL is in XML format. Much like your other queries, you've selected
library name, description, and size from the data source.
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 5
[
143
]
Finally, make sure to add the following imports to the class:
import org.pentaho.reporting.engine.classic.extensions.datasources.
pmd.PmdDataFactory;
import org.pentaho.reporting.engine.classic.extensions.datasources.
pmd.PmdConnectionProvider;
Due to the built in naming of column headers in
PmdDataFactory
, you must also
modify your sample report. Copy
chapter2_report.prpt
to

ant

runpmd
on the command line to view the results!
You may also consider doing this example without the necessity
of the load data section, by adding a Metadata data source to
your report within Pentaho Report Designer.
KettleDataFactory
The
org.pentaho.reporting.engine.classic.extensions.datasources.
kettle.KettleDataFactory
class allows you to populate your report from a Kettle
transformation. Kettle is a data integration tool, also known as an ETL (Extract
Transform and Load) tool. Kettle transformations support a multitude of data
source inputs and transformation capabilities. Kettle, also known as Pentaho Data
Integration, provides mechanisms to incorporate data from Excel, SQL, XML, Text,
and many other data sources. It also provides the ability to combine the results into
a single result set, which Pentaho Reporting can use to render a report.
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Working with Data Sources
[
144
]
To initialize
KettleDataFactory
, you must provide the location of the Kettle
transformation to execute, along with the step within the transformation to use the
data from. This is done via the
KettleTransformationProducer

be instantiated with the following parameters:
final String repositoryName, // the repository name
final String directoryName, // the repository directory
final String transformationName, // the transformation name in the
repository
final String stepName, // the step name to collect data from
final String username, // the repository user name
final String password, // the repository password
final String[] definedArgumentNames, // the names of reporting
properties to be passed into Kettle via Transformation Arguments
final ParameterMapping[] definedVariableNames // the names of
reporting properties to be passed into Kettle via Transformation
Parameters
The
KettleDataFactory
has a default constructor. To add Kettle
transformation queries to the
KettleDataFactory
, call the
setQuery(String, KettleTransformationProducer)
method.
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 5
[
145
]
KettleDataFactory example
To start the example, you rst need to build a Kettle transformation. Download
Pentaho Data Integration 3.2 from SourceForge:

Working with Data Sources
[
146
]
Once you've congured your connection, click the Test button to make sure it
can connect successfully, and then click the Explore button and verify that the
LIBRARYINFO
table exists:
Now click the OK button to return to the Table input dialog.
Click the Get SQL select statement... button. This brings up the database explorer.
Select the LIBRARYINFO table from the list of tables and click OK. An additional
dialog should appear asking if you would like to include the eld names in the SQL.
Click the Yes button. Your Table input dialog should look like this:
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 5
[
147
]
Click OK on the Table input dialog to update the transformation step. Finally, save
your transformation as
chapter5/data/libraryinfo.ktr
.
Now that you've created your transformation le, it's time to set up the
DataFactory
.
First, you must place the necessary JAR les into the
chapter5/lib
folder. You'll
need to place all the JAR les located in Kettle's

// load Kettle data source
// Initialize Kettle
EnvUtil.environmentInit();
StepLoader.init();
JobEntryLoader.init();
// Build Data Factory
KettleTransFromFileProducer producer = new KettleTransFromFileProducer
("Embedded Repository", "data/libraryinfo.ktr", "Table input", "", "",
new String[0], new ParameterMapping[0]);
KettleDataFactory factory = new KettleDataFactory();
factory.setQuery("default", producer);
report.setDataFactory(factory);
StepLoader and JobLoader both may throw a KettleException, so you must also add
the following
catch
block to the end of the
onPreview
method:
catch (KettleException e) {
e.printStackTrace();
}
You must also add the following imports to complete the example:
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.util.EnvUtil;
import org.pentaho.di.job.JobEntryLoader;
import org.pentaho.di.trans.StepLoader;
import org.pentaho.reporting.engine.classic.core.ParameterMapping;
import org.pentaho.reporting.engine.classic.extensions.datasources.
kettle.KettleTransFromFileProducer;
import org.pentaho.reporting.engine.classic.extensions.datasources.

KettleDataFactoryApp
class with the new location
of the report PRPT le.
Finally, you'll need to add the following Ant target to the
build.xml
le:
<target name="runkettle" depends="compile">
<java fork="true" classpathref="runtime_classpath"
classname="KettleDataFactoryApp"/>
</target>
Type
ant runkettle
on the command line to view the results!
BandedMDXDataFactory
The
org.pentaho.reporting.engine.classic.extensions.datasources.
olap4j.BandedMDXDataFactory
class allows you to populate your report from
An
olap4j
data source.
olap4j
is a Java API for connecting to multi-dimensional
OLAP (Online Analytical Processing) data sources. As of olap4j 0.9.7.145, there
is a driver written for the Mondrian Relational OLAP Engine, as well as an
Extensible Markup Language for Analysis (XMLA) driver implementation, which
provides communication with Microsoft Analysis Services, along with other XMLA
compatible OLAP services.
Natively, OLAP data sources support result sets with more than two axes. In a
traditional result set used by Pentaho Reporting, there are column headers, along

DriverConnectionProvider
provides an implementation.
The
DriverConnectionProvider
contains a default constructor, and may
be congured with the following methods:
void setDriver(String driver);
The
setDriver
method species the driver class to use.
void setURL(String url);
The
setURL
method species the URL the driver should connect to.
void setProperty(String name, String value);
The
setProperty
method species additional connection properties.
After creating a valid
OlapConnectionProvider
, pass that object into the
BandedMDXDataFactory
constructor. Once you've created the factory, you may
add Multi-dimensional Expression (MDX) queries by calling the
setQuery
(String name, String mdxQuery)
method.
BandedMDXDataFactory example
To begin this example, you rst need to create a simple OLAP model that you
can query. First, download Mondrian's Schema Workbench from the following

Right-click on the Table element within the Hierarchy and select the Delete menu
item. This element is also not needed.
Right-click on the Hierarchy and select the Add Level menu item. Set the level's
name attribute to
Library Name
, and the column attribute to
NAME
. Now, right-
click on the level and select the Add Property menu item. Rename the property to
LibDescription
and set the column attribute to
DESCRIPTION
. Set the type attribute
to String.
Finally, right-click on the Library Info cube again and select the Add Measure menu
item. Set the measure's name to
Size
, and enter
SIZE
for the column attribute. Select
sum for the aggregator.
You're now done creating a very simple OLAP model. Go ahead and save this model
to
data/libraryinfo.mondrian.xml
. Once saved, verify the model by selecting the
menu item File | New | MDX Query, and typing in the following query:
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 5
[

Chapter2SwingApp
, renamed to
BandedMDXDataFactoryApp
:
// load olap data
DriverConnectionProvider provider = new DriverConnectionProvider();
provider.setDriver("mondrian.olap4j.MondrianOlap4jDriver");
provider.setUrl("jdbc:mondrian: ");
provider.setProperty("Catalog", "data/libraryinfo.mondrian.xml");
provider.setProperty("JdbcUser", "sa");
provider.setProperty("JdbcPassword", "");
provider.setProperty("Jdbc", "jdbc:hsqldb:file:data/libraryinfo");
provider.setProperty("JdbcDrivers", "org.hsqldb.jdbcDriver");
// create the factory
BandedMDXDataFactory factory = new BandedMDXDataFactory(provider);
// add the MDX query
factory.setQuery("default", "WITH MEMBER [Measures].[Name] AS
'[Library].CurrentMember.Caption' MEMBER [Measures].[Description]
AS '[Library].CurrentMember.Properties(\"LibDescription\")'
select [Library].Children on rows, {[Measures].[Name], [Measures].
[Description], [Measures].[Size]} on columns from [Library Info]");
report.setDataFactory(factory);
You must also add the following imports to complete the example:
import org.pentaho.reporting.engine.classic.extensions.datasources.
olap4j.DriverConnectionProvider;
import org.pentaho.reporting.engine.classic.extensions.datasources.
olap4j.BandedMDXDataFactory;
This material is copyright and is licensed for the sole use by David Martone on 16th September 2009
710 South Avenue West, , Westfield, , 07090Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Working with Data Sources

correct PRPT le to load. Finally, you'll need to add the following Ant target to
the
build.xml
le:
<target name="runmdx" depends="compile">
<java fork="true" classpathref="runtime_classpath"
classname="BandedMDXDataFactoryApp"/>
</target>
Type
ant runmdx
on the command line to view the results.
You may also consider doing this example without the necessity
of the load data section, by adding an olap4j data source to your
report within Pentaho Report Designer.
DenormalizedMDXDataFactory
The
org.pentaho.reporting.engine.classic.extensions.datasources.
olap4j.DenormalizedMDXDataFactory
class queries an
olap4j
data source in a
similar fashion as the
BandedMDXDataFactory
. The only difference is the mapping
from OLAP to a two-dimensional result set.
The
DenormalizedMDXDataFactory
maps all the axes of the OLAP result set to a
TableModel
, in a denormalized or attened fashion. The column headers display the