Defining joins in a data model
You define joins between data sets in a data model in the same way that you define joins between tables in a SQL query.
Joining data sets automatically
You can join data sets automatically based on matching column names. For example, in the data model shown in
Figure 10‑16, you can create the joins automatically by matching the CUSTOMERNUMBER columns in the Customers and Orders data sets and the ORDERNUMBER columns in the Orders and OrderDetails data sets.
How to join data sets automatically
1 Open the data object that contains the data model.
2 In Data Explorer, right-click the data model and choose Edit.
3 In Data Model, choose Auto Join.
4 In Column Link Selector, select the joins to create, as shown in
Figure 10‑17. Choose OK.
Figure 10‑17 Joining data sets automatically
Removing join loops
A join loop occurs when the data sets in a data model are joined in a circular pattern. For example, in
Figure 10‑18 the following joins form a circular pattern:
The Customers data set is joined to the Orders data set.
The Orders data set is joined to the OrderDetails data set.
The OrderDetails data set is joined to the Customers data set.
If the data model has a large number of data sets, it may not be possible to detect join loops visually. In this case, BIRT Designer Professional can detect join loops automatically.
Figure 10‑18 Join loop in a data model
How to remove a join loop
1 Open the data object that contains the data model.
2 In Data Explorer, right-click the data model and choose Edit.
3 In Data Model, choose Detect Loop.
4 In Column Link Selector, deselect the joins to remove, as shown in
Figure 10‑19. Choose OK.
Figure 10‑19 Removing a join loop
Disabling join push-down
If you use a JDBC Database Connection for Query Builder data source, in many cases, joins in a data model are pushed down to the database. If either of the following statements is true, disabling join push-down may improve query performance:
The join columns are not indexed in the underlying database.
Query performance is unacceptable.
In Data Model, joins with push-down disabled appear in blue.
How to display the effective query
The effective query is the query that is sent to the database.
1 Open the report design that uses the data model.
2 In the layout pane, in the white space, right-click and choose Show Query Execution Profile.
3 In Query Execution Profile, maximize the window, then expand nodes and scroll to the right until the effective query is visible.
4 Hover the mouse pointer over the effective query to display the full text.
How to disable push-down for all joins
1 Open the data object that contains the data model.
2 In Data Explorer, right-click the data model and choose Edit.
3 In Data Model, choose Disable push-down on all links.
How to disable push-down for a single join
1 Open the data object that contains the data model.
2 In Data Explorer, right-click the data model and choose Edit.
3 In Data Model, double-click the join line.
4 In Edit Column Link, select Disable automatic push-down for links between these two data sets, as shown in
Figure 10‑20. Choose OK.
Figure 10‑20 Disabling push-down for a single join
Creating data set aliases
Use a data set alias if you need multiple instances of the same data set, such as in a self-join.
How to create a data set alias
1 Open the data object that contains the data model.
2 In Data Explorer, right-click the data model and choose Edit.
3 In Data Model, right-click the appropriate data set and choose Create Alias.
Joining data sets on NULL column values
If you join two data sets on columns that contain NULL values on both sides of the join, the query may yield different results depending on whether the join is pushed down to the database or processed by BIRT. The reason for this is that, in most cases, databases treat two NULL values as not equal, while BIRT treats two NULL values as equal. For example, consider a Customers data set and an Orders data set, each of which has a NULL value in the CustomerID column:
Customers data set: Orders data set:
CustomerID Country OrderID CustomerID
001 USA 99991 001
002 France 99992 002
NULL Germany 99993 NULL
The following query joins the Customers data set and the Orders data set on the CustomerID column:
SELECT Country, OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
If BIRT processes the join, NULL == NULL and the query returns the following result set:
Country OrderID
USA 99991
France 99992
Germany 99993
If the join is pushed down to the database, NULL != NULL and the query returns the following result set:
Country OrderID
USA 99991
France 99992
