Understanding Links
You can link two tables together so records from one table are related to records in another table. Between two tables there can be only one link. When you link, you identify a column that is common to both tables. BIRT Analytics supports the following linking methods to match records in different tables:
*1 to 1, associate a single record in one table to a single record in another table. For example, a table that gives customer names and customer id can link to a customer id in another table that gives additional details about each customer.
*1 to N, associate a single record in one table to multiple records in another table. For example, a single customer can link to multiple orders.
The column in the table you are linking from must only have discrete values such as customer id numbers. The second table that you link to does not require discrete values. BIRT Analytics sets the table with only discrete values as the parent table. If both tables contain only discrete values, then the table that is linked from becomes the parent table. The FastDB engine does not support linking from a column with continuous values, such as the time of day or a range of prices.
For example, if you use an Orders table and a Customers table, and you link a customer id column that exists in both tables, BIRT Analytics will set the Customers table as the parent table because all of the customer ids in the Customers table are unique.
Links with a 1 to N relationship, link a column indicated in the "From" field to the column indicated in the "To" field. Figure 4‑7 shows a 1 to N relationship between two tables. BIRT Analytics sets the Demo.Household.Household ID column as the parent table because all of the Household ID values are unique in that table. The Demo.Customer.Household ID table is set as the child table because more than one customer can have the same household id.
Figure 4‑7 Populating FROM and TO fields when creating 1 to N links
Link types are shown in Data Management—Links. The following types are used in by the FastDB engine:
*Final
*Transient
Final links are the links you create using the Links option or the transformation instruction, LINK. For more information on LINK, see “LINK” in Chapter 5, “Transforming data.” Transient links are automatically generated by the software for performance reasons.
For example, the transient link, shown in Figure 4-8 , gets created if you create a Crosstab with [Customer].[Occupation] as a dimension and Count( Order Detail ) as a measure.
Figure 4‑8 Managing links
To clear the transient links, choose Remove all transients.
How to create a new link
1 Choose Links in Data Management.
2 In Link—From, select the database, table, and column where the link starts.
3 In Link—To, select the database, table, and column where the link goes, as shown in Figure 4‑9.
Figure 4‑9 Creating links
4 Choose New to create the link.
5 Confirm your link creation, choose Yes when asked to confirm that you want to create a new link.
How to remove a link
1 In Link—From, select the database, table, and column where the link starts.
2 In Link—To, choose the database, table, and column where the link goes.
3 Choose Remove to drop the link.
4 Choose Yes when asked to confirm the change. The link disappears from the list of links.