Structuring Data

Working with Data Models

The Data Model diagram helps you to quickly and easily define data sets, break groups, and totals for a report based on multiple data sets.

About Multipart Unrelated Data Sets

If you do not link the data sets (or queries) the data engine produces a multipart unrelated query data set.

For example, in the data model shown in Figure 15-1, one query selects products and another selects customers. Notice that there is no relationship between the products and customers.

Figure 15-1 Multipart Unrelated Data Set

This results in the data structure shown in Figure 15-2.

Figure 15-2 Data Structure of Multipart Unrelated Data Set

About Multipart Related Data Sets

In many cases, the data fetched for one part of the data set (or query) is determined by the data fetched for another part. This is often called a "master/detail," or "parent/child," relationship, and is defined with a data link between two data sets (or queries). When you run a master/detail data model, each row of the master (or parent) query causes the detail (or child) query to be executed, retrieving only matching rows.

In the example shown in Figure 15-3, two data sets are linked by the element Customer ID. The Orders data set a child of the Customers data set.

Figure 15-3 Multipart Related Data Sets

This produces the data structure shown in Figure 15-4.

Figure 15-4 Data Structure of Multipart Related Data Set

Features of the Data Model Editor

The data model editor enables you to combine data from multiple data sets into a single XML data structure. Data sets from multiple data sources can be merged either as sequential XML or at line-level to create a single combined hierarchical XML. Using the data model editor you can easily combine data from the following data set types: SQL query, OLAP (MDX query), LDAP, and Microsoft Excel.

The data model editor supports the following

• Group data — Groups are created to organize the columns in your report. Groups can do two things: separate a query's data into sets, and filter a query's data.

  When you create a query, the data engine creates a group that contains the columns selected by the query; you can create groups to modify the hierarchy of the data appearing in a data model. Groups are used primarily when you want to treat some columns differently than others. For example, you create groups to produce subtotals or create breaks.

• Link data — Define master-detail links between data sets to group data at multiple levels.

• Aggregate data — Create group level totals and subtotals.

• Transform data — Modify source data to conform to business terms and reporting requirements.

• Create calculations — Compute data values that are required for your report that are not available in the underlying data sources.

The data model editor provides functions at the element level, the group level, and the global level. Note that not all data set types support all functions. See the Important Notes section that accompanies your data set type for limitations. Figure 15-5 highlights some of the features and actions available in the data model editor.

Figure 15-5 Features of Data Model Editor

About the Interface

By default, the data sets that you created are shown in the Diagram View as separate objects, as seen in Figure 15-6.

Figure 15-6 Diagram View

The data set structure builder has three views:

• Diagram View — (Shown in Figure 15-6) This view displays your data sets and enables you to graphically create links and filters, add elements based on expressions, add aggregate functions and global-level functions, edit element properties, and delete elements. The Diagram View is typically the view you use to build your data structure.

• Structure View — This view has two modes:

  Table View and Output

  The table view displays element properties in a table and enables you to update XML element alias names, presentation names of the elements, sorting, null values, and reset options. Figure 15-7 shows the structure Table View.

  Figure 15-7 Structure Table View

  
  

  The Output view provides a clear view of the XML structure that is generated. The Output view cannot be updated. Figure 15-8 shows the Output view.

  Figure 15-8 Output View

  
  

• Code View — This view displays the data structure code created by the data structure builder that is read by the data engine. The code view cannot be updated. Figure 15-9 shows the code view.

  Figure 15-9 Code View

  
  

Creating Links Between Data Sets

Joining and structuring data at the source into one combined data set is sometimes not possible. For example, you cannot join data at the source when data resides in disparate sources such as Microsoft SQL Server and an Oracle database. You can use the BI Publisher data engine to combine and structure data after you extract it from the data source. Even if your data is coming from the same source, if you are creating large reports or documents with potentially hundreds of thousands of rows or pages, structuring your data so that it matches the intended layout optimizes document generation.

Create a link to define a master-detail (or parent-child) relationship between two data sets. You can create links as element-level links or group-level links. The resulting, hierarchical XML data is the same. Creating links as element-level links is the preferred method. Group-level links are provided for backward compatibility with data templates from earlier versions of BI Publisher.

A data link (or parent-child relationship) relates the results of multiple queries. A data link can establish these relationships:

• Between one query's column and another query's column

• Between one query's group and another query's group (this is useful when you want the child query to know about its parent's data)

Creating Element-Level Links

Link data sets to define a master-detail (or parent-child) relationship between two data sets. Defining an element-level link enables you to establish the binding between the elements of the master and detail data sets.

To define an element-level link, do one of the following:

• Open the element action menu and click Create Link.

  Figure 15-10 Creating a Link Using the Element Action Menu

  
  

  The Create Link dialog launches and displays the elements from the other data sets. Choose the element and click OK to create the link. The Create Link dialog is shown in Figure 15-11.

  Figure 15-11 Create Link Dialog

  
  

• Alternatively, from the parent group, click and drag the element you want to bind to the matching element in the child group, as shown in Figure 15-12.

  Figure 15-12 Creating a Link by Dragging and Dropping the Bind Element

  
  

• After dropping the element from the parent data set to the matching element on the child data set, a connector displays between the data sets. Pause your cursor over the connector to display the link (as shown in Figure 15-13).

  Figure 15-13 Displaying the Link

  
  

Creating Group-Level Links

A group-level link defines a master-detail relationship between two data sets. The following figure shows two data sets with a group-level link defined. Next to the data sets the resulting XML data structure is shown, as in Figure 15-14.

Figure 15-14 Resulting XML Data Structure

To define a group-level link:

1. In the parent group, click Menu (in the upper right corner of the object).

2. Click Create Group Link as shown in Figure 15-15.

   Figure 15-15 Creating a Group Link

   
   

3. In the Create Group Link dialog, select the child group and click OK. The Create Link dialog is shown in Figure 15-16.

   Figure 15-16 Create Link Dialog

   
   

4. Click Menu and then click Edit Data Set to add the bind variables to your query.

   An example is shown in Table 15-1.

   Table 15-1 Example: Edit Data Set

   Data Set: DEPT	Data Set: EMP
   Select DEPT.DEPTNO as DEPTID, DEPT.DNAME as DNAME, DEPT.LOC as LOC from OE.DEPT DEPT	Select EMP.EMPNO as EMPNO, EMP.ENAME as ENAME, EMP.JOB as JOB, EMP.MGR as MGR, EMP.HIREDATE as HIREDATE, EMP.SAL as SAL, EMP.COMM as COMM, EMP.DEPTNO as DEPTNO from OE.EMP EMP where DEPTNO=:DEPTID

   
   
   
   

   Important: A unique bind variable must be defined in the child query.

   
   

Creating Subgroups

In addition to creating parent-child structures by linking two data sets, you can also group elements in the same data set by other elements. This might be helpful if your query returns data that has header data repeated for each detail row. By creating a subgroup you can shape the XML data for better, more efficient document generation.

To create a subgroup:

1. Select the element by which you want to group the other elements in the data set.

2. Click the element action menu icon to open the menu and select Group by as shown in Figure 15-17.

   Figure 15-17 Creating a Subgroup

   
   

   This creates a new group within the displayed data set. The following figure shows the G_3 data set grouped by the element COMPANY. This creates a new group called G_4, that contains the other four elements in the data set. Figure 15-18 shows how the grouped data set is displayed in the Diagram View along with the structure.

   Figure 15-18 Subgroup Data Structure

   
   

   You can perform any of the group actions on the new group you have created.

To remove a subgroup:

1. On the group's title bar, click Menu and then click Ungroup.

Moving an Element Between a Parent Group and a Child Group

Once you have created a group within your data set, two new options display on the element action menu that enable you to move elements between the parent and child groups.

For the element that you want to move, click the element action icon to open the menu. If the element is in the parent group and you want to move it to the child group, and then Move this element to Child Group.

If the element is in the child group and you want to move it to the parent group, select and then Move this element to Parent Group. In Figure 15-19, the element action menu for OFFICE_DSC displays the option to move the element to the parent group.

Figure 15-19 Moving Element from Child Group to Parent Group

Creating Group-Level Aggregate Elements

You can use the data model editor to aggregate data at the group or report level. For example, if you group sales data by Customer Name, you can aggregate sales to get a subtotal for each customer's sales. Note that you can only aggregate data for at the parent level for a child element.

The aggregate functions are:

• Average — Calculates the average of all the occurrences of an element.

• Count — Counts the number of occurrences of an element.

• First — Displays the value of the first occurrence of an element in the group.

• Last — Displays the value of the last occurrence of an element in the group.

• Maximum — Displays the highest value of all occurrences of an element in the group.

• Minimum — Displays the lowest value of all occurrences of an element in a group.

• Summary — Sums the value of all occurrences of an element in the group.

To create group-level aggregate elements:

1. Drag the element to the Drop here for aggregate function field in the parent group.

   Figure 15-20 shows creating a group-level aggregate function in the G_DEPT based on the SALARY element.

   Figure 15-20 Creating a Group-Level Aggregate Function

   
   

   Once you drop the element, a new element is created in the parent group. By default, the Count function is applied. The icon next to the name of the new aggregate element indicates the function. Pause your cursor over the icon to display the function.

   Figure 15-21 shows the new aggregate element, CS_1. with the default Count function defined.

   Figure 15-21 New Element Created by Group-Level Aggregate Function

   
   

2. To change the function: Click the function icon to view a list of available functions and choose from the list, as shown in Figure 15-22.

   Figure 15-22 Choosing a Function

   
   

3. To rename the element or update other properties, click the element's Action menu icon. On the menu, click Properties. The Properties dialog is shown in Figure 15-23.

   Figure 15-23 Properties Dialog

   
   
   
   

   Important: Be careful when renaming an element as it can have dependency on other elements.

   
   

   Set the properties described in Table 15-2 as needed.

   Table 15-2 Element Properties

   Property	Description
   Column Name	The internal name assigned to the element by the BI Publisher data model editor. This name cannot be updated.
   Alias (XML Tag Name)	BI Publisher assigns a default tag name for the element in the XML data file. You can update this tag name to assign a more user-friendly name within the data file.
   Display Name	The Display Name appears in the report design tools. Update this name to be meaningful to your business users.
   Function	If you have not already selected the desired function, then you can select it from the list here.
   Data Type	BI Publisher assigns a default data type of Integer or Double depending on the function. Some functions also provide the option of Float.
   Value if Null	If the value returned from the function is null, you can supply a default value here to prevent having a null in your data.
   Round	By default, the value is rounded to the nearest third decimal. You can change the round value, if needed.
   Do Not Reset	By default, the function resets at the group level. For example, if your data set is grouped by DEPARTMENT_ID, and you have defined a sum function for SALARY, then the sum is reset for each group of DEPARTMENT_ID data, giving you the sum of SALARY for that department only. If instead you want the function to reset only at the global level, and not at the group level, select Do Not Reset. This creates a running total of SALARY for all departments. Note that this property is for group level functions only.

   
   

Creating Group Filters

Filters enable you to conditionally remove records selected by your queries. Groups can have two types of filters:

• Expression — Create an expression using predefined functions and operators

• PL/SQL Function — Create a custom filter

To create a group filter:

1. Click Menu, and then select Create Group Filter. The Edit Group Filter dialog is displayed as shown in Figure 15-24.

   Figure 15-24 Edit Group Filter Dialog

   
   

2. Select the Group Filter Type: Expression or PL/SQL.

   
   

   Note: For PL/SQL filters, you must first specify the PL/SQL Package as the Oracle DB Default Package in the data model properties. See Setting Data Model Properties.

   
   

3. Enter the Filter:

   • To enter an expression, select the elements and click the shuttle button to move the element to the Group Filter definition box. Click the predefined functions and operators to insert them in the Group Filter box.

     Refer to Function Reference for a description of the available functions.

     Click Validate Expression to ensure that the entry is valid.

   • To enter a PL/SQL function, select the PL/SQL package from the Available box and click the shuttle button to move the function to the Group Filter box.

     Your PL/SQL function in the default package must return a Boolean type.

After you have added the group filter, the data set object displays the filter indicator, as shown in Figure 15-25.

Figure 15-25 Filter Indicator

To edit or delete a group filter:

1. Click Menu for the data set.

2. Choose the appropriate action:

   • To edit the group filter, choose Edit Group Filter to launch the Group Filter dialog for editing.

   • To delete the group filter, choose Delete Group Filter.

Performing Element-Level Functions

You can perform the following functions at the element level:

• Group by an element to create a subgroup, as described in Creating Subgroups

• Create element-level links between data sets, as described in Creating Element-Level Links

• Set element properties, as described in Setting Element Properties

Setting Element Properties

You can set properties for individual elements. Note that these properties are also editable from the Structure View. If you need to update multiple element properties, it may be more efficient to use the Structure View. See Using the Structure View to Edit Your Data Structure.

To set element-level properties using the element dialog:

1. Click the element's action menu icon. From the menu, select Properties. The Properties dialog is shown in Figure 15-26.

   Figure 15-26 Properties Dialog

   
   

2. Set the properties as needed, as described in Table 15-3.

   Table 15-3 Element Properties

   Property	Description
   Alias	BI Publisher assigns a default tag name to the element in the XML data file. You can update this tag name to assign a more user-friendly name within the data file.
   Display Name	The Display Name appears in the report design tools. Update this name to be meaningful to your business users.
   Data Type	BI Publisher assigns a default data type of Integer or Double depending on the function. Some functions also provide the option of Float.
   Sort Order	You can sort XML data in a group by one or more elements. For example, if in a data set employees are grouped by department and manager, you can sort the XML data by department. Within each department you can group and sort data by manager, and within each manager subgroup, employees can be sorted by salary. If the element is not in a parent group, the Sort Order property is not available.
   Value if Null	If the value returned from the function is null, you can supply a default value here to prevent having a null in your data.

   
   

Sorting Data

Sorting is supported for parent group break columns only. For example, if a data set of employees is grouped by department and manager, you can sort the XML data by department. Within each department you can group and sort data by manager. If you know how the data should be sorted in the final report, you specify sorting at data generation time to optimize document generation.

To apply a sort order to a group:

1. Click the action menu icon of the element you want to sort by. From the menu, select Properties.

2. Select the Sort Order.

   Figure 15-27 shows the Properties dialog for the DEPARTMENT_ID element with the Sort Order list displayed.

   Figure 15-27 Properties Dialog Showing Sort Order List

   
   

Performing Group-Level Functions

This section describes how to perform group-functions. It includes the following topics:

• The Group Action Menu

• Editing the Data Set

• Removing Elements from the Group

• Editing the Group Properties

The Group Action Menu

The Menu button is available at the group level and enables you to perform the following:

• Create and delete group links, as described in Creating Group-Level Links

• Create, edit, and delete group filters, as described in Creating Group Filters

• Add an element to the group based on an expression, as described in Adding a Group-Level or Global-Level Element by Expression

• Edit the data set, as described in Editing the Data Set

• Remove elements from the group, as described in Removing Elements from the Group

• Edit group properties, as described in Editing the Group Properties

The group-level Menu button is shown in Figure 15-28.

Figure 15-28 Group-Level Actions Menu

Removing Elements from the Group

To remove an element from the group:

1. On the element row, click the menu and then click Remove Element. An example is shown in Figure 15-29.

Note: You can only remove elements added as a group function (sum, count, and so on) or added as an expression.

Figure 15-29 Removing an Element

Performing Global-Level Functions

The Global Level Functions object enables you to add elements to your report data set at the top report level. You can add the following types of elements as top-level data:

• Elements based on aggregate functions

• Elements based on expressions

• Elements based on PL/SQL statements (for Oracle Database data sources)

The Global Level Functions object is shown in Figure 15-31. To add elements based on aggregate functions, drag the element to the "Drop here for aggregate function" space of the object. To add an element based on an expression or PL/SQL, click Menu, and choose the appropriate action.

Figure 15-31 Global Level Functions Object

Using the Structure View to Edit Your Data Structure

The Structure view enables you to preview the structure of your data model. The Data Source column displays the date elements in a hierarchical tree that you can collapse and expand. Use this view to verify the accuracy of the data model structure. The Structure view is shown in Figure 15-37.

Figure 15-37 Structure View

Renaming Elements

Use the Structure page to define user-friendly names for elements in the data model. You can rename both the XML element tag name (XML View) and the name that displays in the report layout tools (Business Name). Figure 15-38 shows renaming the DEPARTMENT_ID element to display as Department Number.

Figure 15-38 Editing the Display Name of an Element

Function Reference

Table 15-6 describes the usage of supported functions available from the Add Element by Expression dialog and the Edit Group Filter dialog.