Jump to: navigation, search

Difference between revisions of "BIRT/FAQ/Data Access"

< BIRT‎ | FAQ
(Q: Can I build my own data set?)
(Q: How do I use a stored procedure?)
Line 39: Line 39:
 
The JDBC ODA plugin included in BIRT currently supports SQL SELECT queries and simple stored procedure queries. That is, those that 1) use scalar input parameters only or no parameters, and 2) retrieve a single result set directly, like those in SQL Server or Sybase (instead of via a cursor output parameter like those in Oracle).
 
The JDBC ODA plugin included in BIRT currently supports SQL SELECT queries and simple stored procedure queries. That is, those that 1) use scalar input parameters only or no parameters, and 2) retrieve a single result set directly, like those in SQL Server or Sybase (instead of via a cursor output parameter like those in Oracle).
  
You can access a simple stored procedure call statement using a statement in the JDBC query editor. Create a JDBC data set with a dummy query. Use the Edit data set option to open the data set editor. replace the SQL SELECT statement with your stored procedure call.
+
BIRT does support Input/Output Parameters from stored procedures:
 
+
{call testProcedure(?)}
We encourage the BIRT community to provide additional Open Data Access (ODA) extensions to handle various types of data sources, including complex stored procedures.
+
If the parameter in this example is an output parameter, you could reference it in the expression builder like:
 +
outputParams["param1"]
  
 
== Optimizing Filters & Sorting ==  
 
== Optimizing Filters & Sorting ==  

Revision as of 19:32, 17 July 2006

Back to BIRT FAQ Index

Contents

General

Q: What is a "data source" and a "data set"?

BIRT uses these terms to define how the report accesses data.

  • Data Set – is a description of data to be retrieved. SQL queries are the ones most often used, but BIRT also supports a wide variety of sources such as stored procedures, Java classes and so on.
  • Joint Data Set - A Joint Data Set is combination of two Data Sets created using an INNER or (Left/Right) OUTER join.
  • Data Source – is a connection to an external system, such as a JDBC connection.

Any one BIRT report can contain any number of data sets and data sources.

Q: Can I build my own data set?

Yes. The only requirement is that your data set return data in tabular format so that BIRT can perform sorting, aggregation, grouping and so on. There are two ways to build a data set:

  • Scripted data set – Best for data sets that are used by one specific report. (For example, to access a specific set of application objects.)
  • Extended data set – Created using the Data Tools Open Data Access (ODA) framework. Extended data sets are best when creating a generic feature used access a type of data source. (For example, to access web logs.)

Supported Data Sources

Q: What data sources does BIRT support?

BIRT Release 1 supports two data sources:

  • JDBC – Works with any JDBC driver.
  • Scripted – Lets you write code to access other data sources.

Q: How do I add my JDBC driver to BIRT?

See the installation instructions.

Q: Which Oracle JDBC driver do I need?

Use the ojdbc14.jar driver. The older classes12.jar drivers are for use with Java JDK 1.2 and 1.3. The ojdbc14.jar drivers are for use with JDK 1.4, which is what BIRT uses.

Q: How do I use a stored procedure?

The JDBC ODA plugin included in BIRT currently supports SQL SELECT queries and simple stored procedure queries. That is, those that 1) use scalar input parameters only or no parameters, and 2) retrieve a single result set directly, like those in SQL Server or Sybase (instead of via a cursor output parameter like those in Oracle).

BIRT does support Input/Output Parameters from stored procedures:

{call testProcedure(?)}

If the parameter in this example is an output parameter, you could reference it in the expression builder like:

outputParams["param1"]

Optimizing Filters & Sorting

Q: Does BIRT push sorting & filtering to the database?

No, BIRT will not modify your SELECT statement in any way. Real-world SELECT statements are often complex and carefully crafted, and you can count on BIRT to use the statement as you wrote it. This gives you control over whether filtering and sorting is done within BIRT or within the database. Further, BIRT works with a wide variety of data sources, not just SQL. Many non-SQL data source do not provide the means to do sorting & filtering, and so you'll want BIRT to perform these tasks.

Q: What's the difference between BIRT filtering and a SQL WHERE clause?

BIRT provides filtering capabilities in both the data set editor and in the Property editor for report items. These filters can include JavaScript expressions, including calls into your Java code. BIRT executes such expressions in its own data engine.

The SQL SELECT statement also performs filtering in the WHERE clause. In the case, the filtering happens on the database, and cannot include JavaScript code: it must include only SQL valid for your target database.

Q: When do I use BIRT vs. SQL filtering?

In general, use an SQL WHERE clause whenever possible. It is more efficient to restrict the data within the query to eliminate the overhead of sending the data to BIRT. However, if you are not using a SQL database, or your expression requires JavaScript or Java code, then, go ahead and perform the filtering within BIRT.

Release 1.0 Note: BIRT holds your entire result set in memory. Therefore, it is important that filtering be done on the database to reduce the amount of memory required by a BIRT report.

Q: What's the difference between BIRT sorting and a SQL ORDER BY clause?

BIRT provides a sorting option on tables and lists. When you define groups, BIRT implicitly defines a sort order. When using these options, BIRT will perform the sorting within BIRT's own data engine.

If your SELECT statement has an ORDER BY clause, and you omit the sort specification within BIRT, then you can ensure that the sorting is done on the database instead of in BIRT.

Q: When do I use BIRT vs. SQL sorting?

For performance, use SQL sorting whenever possible, especially if your database has indexes that will speed up the sort. If your report has no groups, and your SELECT statement has the correct ORDER BY clause, then simply do not specify a sort order within BIRT.

Release 1.0 Note: BIRT holds your entire result set in memory, and always sorts your data whether or not the database already did the sort. (The in-memory sort is slightly faster if the data is presorted.) In particular, if your report includes grouping, then BIRT will resort your data, and you must include a BIRT-defined sort order for the detail rows. A later release may provide a way to tell BIRT to use database sorting even for reports that have groups.

Of course, if your data source does not support sorting, then, again, use the sorting features of BIRT to sort your data.

Scripted Data Sources

Q: How do I get data from a POJO (Plain Old Java Object)?

This can be done using the scripted data set element. Let's take an example: exposing an org chart as a data set. Let's assume that the org chart is represented by a tree, and that the report wants to present this data in the form of, say, a phone list. We'll need to write some "glue" code to link the POJO to BIRT.

Next, we need to identify the right element within the design to hold the glue code. In this case, since we're creating a data set, we'd choose a "scripted data set."

Then, we work out exactly what the glue code needs to do. The scripted data set has three methods: open, fetch and close. Let's suppose that the open method will get a handle to our org chart, perhaps by calling a factory method exposed in Java. JavaScript has an import statement that creates a bridge to our Java objects. So, we'd import, say, the factory class and the "org chart node" class. In open we might say something like the following:

// Import your package.
importPackage( com.company.YourApp );
//NOTE : importPackage currently doesnt work in Release 1, you will get an error 
//       Solution is to use fully qualified names.
//       ie new Packages.com.company.YourApp.Constructor()

// Get an instance of the chart node.

var theChart = OrgChartFactory.getChart( );
//var theChart = Packages.com.company.YourApp.OrgChartFactory.getChart( ); 
// The above will actually do the trick
// the jar containing the class should be in : 
//             eclipse\plugins\org.eclipse.birt.report.viewer\birt\WEB-INF\lib

// The variable iter becomes a property of the data set
// instance object.

iter = theChart.iterator( );

Then, in the fetch method, we'd need to create a row for each org chart node:

// This variable becomes a property of the data set 

instance object;

// Done?

if ( ! iter.hasNext( ) ) { return null; }

// Turn the node into a data row

var node = iter.next( );
var row = newDataRow( );
row.name = node.getName( );
row.deptNo = node.getDeptNo( );
row.title = node.getTitle( );
row.phone = node.getPhoneNo( );
return row;

It is necessary to copy the data into a data row so that the Report Engine can resort it. In our case, the iterator may return the data using a depth-first traversal, but we want it sorted by name to create a phone list. You could also create the data row in Java if you prefer.

There is some other boilerplate needed to declare the row schema, but the above illustrates the key point about calling into Java.

Please make a jar out of your POJO classes and put the jars in eclipse\plugins\org.eclipse.birt.report.viewer\birt\WEB-INF\lib .

Note1: Why Packages.com.* ? Look at http://www.mozilla.org/rhino/ScriptingJava.html . Basically, only java.* are exported to the global namespace. Packages object loads the other classes dynamically.

Q: How do I get data from a Java Bean?

TBD.

Q: How do I get data from an EJB?

There are many ways to do this, but it is probably most appropriate to create an intermediate class, that calls the EJB and returns the values. You would then treat this class as any DAO/POJO object within BIRT, to retrieve the data.

For example if I have an EJB with the JNDI name "greeter", that has a method getGreeting() that returns a string, I would create a class that has two methods like

// Example Methods
public void setupEJB( String contextString, String providerString, String JNDIName ) throws Exception {
     Properties p = new Properties();
     p.put("java.naming.factory.initial", contextString );
     p.put("java.naming.provider.url", providerString );
     initContext = new javax.naming.InitialContext(p);
     Object objref = initContext.lookup(JNDIName);
     myGreeterHome = (GreeterHome)PortableRemoteObject.narrow(objref,
                                           GreeterHome.class);
}
public String makeCall() throws Exception {
     myGreeterRemote = myGreeterHome.create();
     String theMessage = myGreeterRemote.getGreeting();
     return (theMessage);
}

Within the scripted data set you would then call these methods like

// Open method of the data set
ejbclient = new Packages.GreeterEjbClient();
ejbclient.setupEJB( "com.sun.jndi.cosnaming.CNCtxFactory", "iiop://localhost:3700", "greeter" );
//assuming only one row will be needed
rowcount = 0;
//Fetch method of the data set
if( rowcount > 0 ){
	return false;
}else{
	var rtnString = ejbclient.makeCall();
	row["message"] = rtnString;
	rowcount++;
	return true;
}
//Close method of the data set
ejbclient = null;

Make sure your class and all associate jars (ie. j2ee.jar) are moved to the classes/lib directory under the viewer.

Q: How do I use XML as a POJO Scripted Data Source?

There are two ways that XML can be brought into BIRT. The first and easiest method, is to use the Scripted Data Source to communicate to Java objects that are used to parse and interogate the raw XML.

Scripted Data Sources always have three basic tasks:

  • Open - Initialize connections and Instantiate required Java Objects
  • Fetch - Bind values to the BIRT DataRow? object
  • Close - Clean up any connections and Java Objects

The following code is typical for an open method. In this case we are creating a URL object, and a DataInputStream to that object.

rurl= new Packages.java.net.URL("http://rss.cnn.com/rss/cnn_topstories/rss");
is = rurl.openStream();
dis = new Packages.java.io.DataInputStream( new Packages.java.io.BufferedInputStream( is ));

In the Fetch method we will use the DataInputStream to access the content of the XML Stream. This code reads one line at a time from the XML URL and sets a row variable values which are used in the report. Obviously, you will want use an XML parser in this method to parse the document and set several row variable values based on all of the XML attribute and entity values.

if( ( rline=dis.readLine() != null ){
     row["Test"] = rline;
     return true;
}
return false;

In the close method of the data set, you would clean up any objects or connections that are used while acquiring the data.


rurl = null;
is = null;
dis = null;

Q: How do I use Web Services as a data source?

TBD.

Q: How do I use web server logs as a data source?

TBD. The BIRT project is working with the Web Tools Project (WTP) to create reports from web logs, but the work is very much at an early stage.

Q: How do I use popular ORM (Object-Relational Mapping) tools such as Hibernate and JDO as a data source?

JDO (Java Data Objects) is used to connect to any source (relational or non-relational) while Hibernate is used to connect to relational databases.

TBD. You can access the objects as is described for POJOs above. An example of this will be placed on the web site shortly.

Q: My class implements java.sql.ResultSet. How do I use it as a data source?

TBD. You can access the objects as is described for POJOs above.

Data Set Parameters

Q: How can I use a data set parameter with an SQL query?

Parameters are still under development, so the following may change in the run-up to the 1.0 release.

Start with an SQL parameter. That is:

WHERE custID = ?

Then, follow the SQL rules for parameters. An SQL "?" Parameter must represent an actual data value such as "Foo" or 10. You can also do:

WHERE customName LIKE ?

In which case you could include wildcards such as %. (Note: the wildcards are DB-specific.)

Next, define a data set parameter. The parameters must have a one-to-one correspondence with your "?" markers.

A parameter can be one of three types: input, output, or in/out (both input and output.) An SQL SELECT allows only input parameters. At present, BIRT does not automatically mark your parameters as input. So, you'll need to do that yourself.

Data set parameters take a default value. In theory, the default value should not be required unless it makes logical sense for your report design. (For example, get active customers [status = "A"] unless a user of the data set specifically wants some other status.) In BIRT 1.0, the default value should not be needed very often; it will be more useful when BIRT supports data set reuse. When you decide to test a data set, the UI will eventually prompt you for the data set parameter values, and give you the option to save them as a "test configuration." In the mean time, you must provide a default (test value) in order to preview your data set. A side effect of this is that, since the default is a test value, then it should be a constant.

Q: How do I use Data Set parameters?

There are two common ways to use data set parameters. First, you can bind them to a report parameter so that your user can select the data to display in the report. See the Report Parameters FAQ for information on doing this.

Second, you can pass in data from another part of your report. For example, you can create a subreport that takes its parameter value from a master report. For example, you might get a list of customers (the master report) from an Oracle database. Then, for each customer, you might get the list of orders (the detail report) from a DB2 database. You'd create a "customer ID" parameter in the detail database, and bind it to the "customer id" column in the outer database using the Binding tab within the Property Editor for the List or Table item that represents the detail report. Data Set parameters have an "is nullable" property. What does this mean?

This means that the value can be a (database) null.

Here's how it works at runtime. If a report item bound to a data set has a parameter binding, evaluate that expression and use the value as the data set parameter. If not, then check if the data set parameter has a default value and use that. Otherwise, use null as the parameter value. Then, check if the parameter value is null. If it is, and if the parameter does not allow null (is not "nullable"), and issue a runtime error.

Application Integration

Q: How can I pass a connection object to BIRT?

Currently this is implemented as a 2.0 project. It requires writting or extending an existing ODA and using the Application Context as described in BPS35.

Q: How can I secure the user name and password used to connect to a data source?

The designer prompts you to enter the server name, user name and password when building a JDBC data source. While this works for testing, production systems will often externalize this information from the report design so that the same design can work against both a test and production database, or against different production databases.

BIRT provides a configuration variable feature that lets you bind named values to values set outside of the report design. The details of this feature are still under discussion.

Q: My customers generally don't know SQL, XML, database schemas etc. They just know the domain specific terms. Can I create a custom data source and custom data set designer?

Yes, BIRT provides excellent extension features via ODA as described above. You can provide both the run-time mechanism to access the data, as well as the design-time UI for defining your data set.

If your users are comforatable using a tool such as Eclipse, then they can use your custom UI to build reports within the BIRT designer. If, however, your users want a very simple tool, you can provide your own designer that asks the right questions, then generates the report for them, filling in all the "boiler plate", perhaps using one of several standard layouts defined by your app. This reflects the "embedded" nature of BIRT.

Q: How can I access application-specific metadata to simplify report design?

Some applications want to integrate BIRT into their application so that BIRT has access to the Business Process Model (BPM) (or other metadata) for the application's data objects and their schemas. This avoids having the report designer duplicate the efforts of the BPM designer. The result allow the report design tool to have Business Process Intelligence built-in.

While Release 1 of BIRT won't provide such integration out-of-the-box, others can certainly build on top of BIRT to provide it. To see how, let's divide reporting into two steps: design and execution. The design step is the one where BPM integration would likely occur.

A BIRT design is an XML file that contains instructions for accessing a data source, transforming the data (sorting, filtering, totals), and presenting the results. An integrated application needs the ability to build a query based on the BPM. Once the report developer has that data, he'd use BIRT's features for identifying totals and formatting the results. The result of this process would be a BIRT XML design file that the BIRT Report Engine executes to produce your final report.

There are two ways that you can integrate BIRT with the BPM system at design time.

First, you can develop a custom data set builder extension to the BIRT designer. The builder could leverage the meta-data information in the BPM. The custom builder would be integrated with BIRT using the extension features of Eclipse. The result would be a custom data set that looks to BIRT just like any other data set.

Suppose that the BPM system identifies that there is a Customer concept, and that Orders are related to Customers. Suppose further that the BPM system can provide descriptive information about customers, and about the fields for a customer. A custom data set builder could display this information to help the user understand the data, and use the relationships to help the user choose the data of interest. The details, of course, are likely to depend on the specific BPM system in question.

Second, you can develop a custom report designer that is tightly integrated with the BPM system. Suppose that the system provides sufficient information to generate a wide range of reports. Suppose further that the application requires only a small set of formats, so that designs could be created automatically. In this case, you can write an application that generates BIRT designs directly from the BPM system. You can then directly execute those designs in the BIRT report engine, or load them into the BIRT designer for further refinement.

In either case, you'd leverage one of BIRT's key goals: the ability for others to add application-specific functionality to BIRT.

Extended Data Sets

Q: How can I create a custom extended data set?

BIRT provides an Open Data Access (ODA) extension framework. ODA provides extension points to allow anyone to add a custom data access driver. See the ROM Data Specification for details.

BIRT ODA extension defines a set of public interfaces. It adopts the Eclipse plug-in framework to provide a designer tool, and a runtime driver for data retrieval during report generation.

In short, a custom data source implements the BIRT runtime data access interfaces. This interface is similar to JDBC, and it supports additional capabilities such as any types of data source, the use of data source specific query text ( i.e. not SQL specific), complex parameters, etc. A ResultSet-like interface with a next method implements an iterator, which your implementation could control and optimize the data generation or retrieval.

At design time, the Eclipse plug-in for BIRT data extension allows you to provide a designer GUI tool specific to an external data source. The plug-in designer tool then uses the BIRT public API to specify the data set and data source definitions. The custom data source and data set information is saved in the report design file. The BIRT Data Engine passes the data definition to the extension's ODA runtime driver. The driver can use this information for query execution. ODA then uses the result set API to retrieve results.

The ODA runtime interfaces are JDBC-like, but have been extended to support additional capabilities of non-RDBMS data sources. An ODA driver basically implements the public runtime interfaces. This implementation wraps the data source specific APIs (such as web services, etc.) to retrieve data rows.

For a preview, the ODA runtime interfaces source are available in the Eclipse BIRT CVS source code repository, in the org.eclipse.birt.data.oda package under the org.eclipse.birt.data subproject.

The JDBC implementation is available as a sample. It is in the Eclipse CVS under the org.eclipse.birt.report.data.oda.jdbc and ...oda.jdbc.ui subprojects.

Q: Is odaconfig.xml documented anywhere?

ODA and its configuration files are documented in the Javadoc for ODA itself. It is in: source/org.eclipse.birt.data/src/org/eclipse/birt/data/oda

A copy of the ODA configuration's XML schema definition (ODAConfig.xsd) and related annotations can be found in BIRT's Eclipse CVS, under the source/org.eclipse.birt.data/schema folder. The JDBC ODA driver's odaconfig.xml is a fairly good example of the main configurable options.

Troubleshooting

Q: My report runs in the Viewer, but produced no output. What happened?

If your report works within Eclipse when you preview, but does not work when run in your app server, then one possible problem is that you need to copy your JDBC driver into your app server. If the data set tests OK in the BIRT designer, but the report won't preview, then you may need to copy your JDBC driver into the BIRT viewer plugin. (Currently, your JDBC drivers need to appear in three places: in the org.eclipse.birt.report.data.oda.jdbc plugin directory; again in the org.eclipse.birt.report.viewer plugin directory and once more in the viewer deployed to your app server. See the install instructions and viewer integration instructions for details.