faq/data.html - www.eclipse.org/birt - Git at Google

 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 transitional//EN">
 <html>

 <head>
 <title>BIRT FAQ</title>
 <link rel="stylesheet" href="../style/compose.css" type="text/css"/>
 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
 </head>

 <body>

 <p class="head">BIRT FAQ</p>
 <p class="subhead">Data Access</p>

 <h1>General</h1>

 <h2>Q: What is a "data source" and a "data set"?</h2>
 <p>
 BIRT uses these two terms to define how the report accesses data. <br>
 <dl class="arrow-list">
 <dt>Data Set
 <dd>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.

 <dt>Data Source
 <dd>is a connection to an external system, such as a JDBC
 connection. Data sources are optional, some data sets, such as XML or CSV
 files, don't need a connection.
 </dl>
 Any one BIRT report can contain any number of data sets and
 data sources.<h2>Q: Can I build my own data set?</h2>
 <p>
 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:
 <dl class="arrow-list">
 <dt>Scripted data set
 <dd>Best for data sets that are used by one specific report. (For example, to
 access a specific set of application objects.)

 <dt>Extended data set
 <dd>Created using BIRT's 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.)
 </dl></p>

 <h1>Supported Data Sources</h1>

 <h2>Q: What data sources does BIRT support?</h2>
 <p>BIRT Release 1 supports two data sources:
 <dl class="arrow-list">
 <dt>JDBC
 <dd>Works with any JDBC driver.

 <dt>Scripted
 <dd>Lets you write code to access other data sources.
 </dl></p>

 <h2>Q: How do I add my JDBC driver to BIRT?</h2>
 <p>See the
 <a href="../build/install.html">installation instructions</a>.</p>

 <h2>Q: Which Oracle JDBC driver do I need?</h2>
 <p>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.</p>

 <h2>Q: How do I use a stored procedure?</h2>

 <p>The JDBC ODA plugin included in BIRT currently supports SQL
 SELECT queries and simple stored procedure queries.&nbsp; 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 <br>
 output parameter like those in Oracle).</p>

 <p>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.</p>

 <p>We encourage the BIRT community to provide additional Open Data
 Access (ODA) extensions to handle various types of data sources, including
 complex stored procedures.</p>

 <h1>Optimizing Filters &amp; Sorting</h1>

 <h2>Q: Does BIRT push sorting &amp; filtering to the database?</h2>

 <p>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 &amp; filtering, and so you'll want
 BIRT to perform these tasks. </p>

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

 <p>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.</p>

 <p>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.</p>

 <h2>Q: When do I use BIRT vs. SQL filtering?</h2>

 <p>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.</p>

 <p class="caution"><span class="caution-head">Release 1.0 Note</span>: 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.</p>

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

 <p>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.</p>

 <p>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.</p>

 <h2>Q: When do I use BIRT vs. SQL sorting?</h2>

 <p>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.</p>

 <p class="caution"><span class="caution-head">Release 1.0 Note</span>: 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.</p>

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

 <h1>Scripted Data Sources</h1>

 <h2>Q: How do I get data from a POJO (Plain Old Java Object)?</h2>
 <p>
 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.
 <p>
 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."
 <p>
 Then, we work out exactly what the glue code needs to do. The scripted data
 set has three methods: <code>open</code>, <code>fetch</code> and
 <code>close</code>. Let's suppose that the <code>open</code>
 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 <code>open</code>  we might say something like the
 following:

 <pre class="code-block">// Import your package.

 importPackage( com.company.YourApp );

 // Get an instance of the chart node.

 var theChart = OrgChartFactory.getChart( );

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

 iter = theChart.iterator( );
 </pre>
 <p>Then, in the fetch method, we'd need to create a row for each org chart
 node:

 <pre class="code-block">// 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;
 </pre>

 <p class="caution"><span class="caution-head">Release 1.0M1 Note:</span>
 The above code samples are preliminary; the scripted data set is not yet
 available, and so its implemetation is subject to change.</p>
 <p>
 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.</p>
 <p>
 There is some other boilerplate needed to declare the row schema, but the
 above illustrates the key point about calling into Java.

 <h2>Q: How do I get data from a Java Bean?</h2>
 <p>TBD.</p>

 <h2>Q: How do I use XML as a data source?</h2>
 <p>TBD.</p>

 <h2>Q: How do I use Web Services as a data source?</h2>
 <p>TBD.</p>

 <h2>Q: How do I use web server logs as a data source?</h2>
 <p>
 TBD. The BIRT project is working with the
 <a href="http://www.eclipse.org/webtools">Web Tools Project</a> (WTP)
 to create reports from web logs, but the work is very much at an early stage.
 </p>

 <h2>Q: How do I use popular ORM (Object-Relational Mapping) tools such as
 Hibernate and JDO as a data source?</h2>
 <p>
 JDO (Java Data Objects) is used to connect to any source (relational or non-relational)
 while Hibernate is used to connect to relational databases.
 <p>
 TBD. You can access the objects as is described for POJOs above.
 </p>

 <h2>Q: My class implements java.sql.ResultSet. How do I use it as
 a data source?</h2>
 <p>
 TBD. You can access the objects as is described for POJOs above.
 </p>

 <h1>Data Set Parameters</h1>

 <h2>How can I use a data set parameter with an SQL query?</h2>

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

 <p>Start with an SQL parameter. That is:</p>

 <pre class="code-block">WHERE custID = ?</pre>

 <p>Then,  follow the SQL rules for
 parameters. An SQL &quot;?&quot; Parameter must represent an actual data value such as &quot;Foo&quot;
 or 10. You can also do:</p>

 <pre class="code-block">WHERE customName LIKE ?</pre>

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

 <p>Next, define a data set parameter. The parameters must have a
 one-to-one correspondence with your &quot;?&quot; markers.</p>

 <p>A parameter can be one of three types: input, output, or in/out (both input
 and output.) The M2 build has separate properties for input and output, allowing
 an invalid fourth state: neither input nor output. Unfortunately, that invalid
 fourth state is the default. An SQL SELECT allows only input parameters. At
 present, does not automatically mark your parameters as input. So, you'll need
 to do that yourself.</p>

 <p>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 = &quot;A&quot;]
 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 &quot;test configuration.&quot; 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. In M2,&nbsp; if you enter an expression such as params.RptParam,
 you'll get errors.</p>

 <p>So, bottom line, in M2, you must enter a default value if you want to test
 your data set using the data set preview feature of the data set builder.</p>

 <h2>How do I use Data Set parameters?</h2>

 <p>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 <a href="params.html">Report Parameters FAQ</a> for
 information on doing this.</p>

 <p>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 &quot;customer ID&quot; parameter in
 the detail database, and bind it to the &quot;customer id&quot; column in the outer
 database using the Binding tab within the Property Editor for the List or Table
 item that represents the detail report.</p>

 <h2>Data Set parameters have an &quot;is nullable&quot; property. What does this mean?</h2>

 <p>This means that the value can be a (database) null.</p>

 <p>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 &quot;nullable&quot;), and issue a runtime error.</p>

 <h1>Application Integration</h1>

 <h2>Q: How can I pass a connection object to BIRT?</h2>
 <p>
 Not at present. We're looking into how to achieve this in future release.
 </p>

 <h2>Q: How can I secure the user name and password used to connect
 to a data source?</h2>
 <p>
 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.
 <p>
 BIRT provides a <i>configuration variable</i> feature that lets you bind named
 values to values set outside of the report design. The details of this feature are
 still under discussion.
 </p>

 <h2>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?</h2>
 <p>
 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.
 <p>
 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.
 </p>

 <h2>Q: How can I access application-specific metadata to simplify
 report design?</h2>
 <p>
 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.
 <p>
 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.
 <p>
 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.
 <p>
 There are two ways that you can integrate BIRT with the BPM system at
 design time.
 <p>
 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.
 <p>
 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.
 <p>
 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.
 <p>
 In either case, you'd leverage one of BIRT's key goals: the ability for
 others to add application-specific functionality to BIRT.
 </p>

 <h1>Extended Data Sets</h1>

 <h2>Q: How can I create a custom extended data set?</h2>
 <p>
 BIRT provides an Open Data Access (ODA) extension framework.
 ODA provides extension points to allow anyone to add a custom <i>data
 access driver</i>. See the ROM Data Specification for details.
 <p>
 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.
 <p>
 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.
 <p>
 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.
 <p>
 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.
 <p>
 For a preview, the ODA runtime interfaces source are available in the
 <a href="../build">Eclipse BIRT CVS</a>
 source code repository, in the org.eclipse.birt.data.oda package under the
 org.eclipse.birt.data subproject.
 <p>
 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.
 </p>

 <h2>Q: Is odaconfig.xml documented anywhere?</h2>
 <p>
 ODA and its configuration files are documented in
 the JavaDoc for ODA itself. It is in:
 <br>
 source/org.eclipse.birt.data/src/org/eclipse/birt/data/oda
 <p>
 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.
 </p>


 <h1>Troubleshooting</h1>

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

 <p>BIRT build M2 and earlier do not yet report database errors, but
 this is planned for a future release. 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 <a href="../build/install.html">install
 instructions</a> and <a href="../deploy/viewer-deploy.html">viewer integration</a>
 instructions for details.</p>


 </body>
 </html>