Skip Headers
Oracle® Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher (Oracle Fusion Applications Edition)
11g Release 1 (11.1.1)

Part Number E26386-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Structuring Data

This chapter describes techniques for structuring the data that is returned by BI Publisher's data engine, including grouping, linking, group filters, and group-level and global-level functions.

This chapter includes the following sections:

3.1 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.

3.1.1 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 3-1, one query selects products and another selects customers. Notice that there is no relationship between the products and customers.

Figure 3-1 Multipart Unrelated Data Set

Multipart unrelated data set

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

Figure 3-2 Data Structure of Multipart Unrelated Data Set

Data structure

3.1.2 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 3-3, two data sets are linked by the element Customer ID. The Orders data set a child of the Customers data set.

Figure 3-3 Multipart Related Data Sets

Multipart related data sets

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

Figure 3-4 Data Structure of Multipart Related Data Set

Data structure

3.1.3 Guidelines for Working with Data Sets

Following are recommended guidelines for building data models:

  • Reduce the number of data sets or queries in your data model as much as possible. In general, the fewer data sets and queries you have, the faster your data model will run. While multiquery data models are often easier to understand, single-query data models tend to execute more quickly. It is important to understand that in parent-child queries, for every parent, the child query is executed.

  • You should only use multiquery data models in the following scenarios:

    • To perform functions that the query type, such as a SQL query, does not support directly.

    • To support complex views (for example, distributed queries or GROUP BY queries).

    • To simulate a view when you do not have or want to use a view.

3.2 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

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 3-5 highlights some of the features and actions available in the data model editor.

Figure 3-5 Features of Data Model Editor

Features of the Data Model Editor

3.3 About the Interface

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

Figure 3-6 Diagram View

Diagram view

The data set structure builder has three views:

3.4 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:

3.4.1 About Element-Level Links

Element-level links create a bind (join) between two data sets and define a master-detail (parent-child) relationship between them. This is the preferred method of defining master detail relationships between data sets. The simplest way to link data sets is by creating element-level links because they do not require you to code a join between the two data sets through a bind variable.

3.4.2 About Group-Level Links

Group-level links also determine the way data sets are structured as hierarchical XML, but lack the join information that the data engine needs to execute the master and detail queries. When you define a group-level link, you must also update your query with a link between the two data sets through a unique bind variable.

3.5 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:

3.5.1 Deleting Element-Level Links

To delete an element link:

  1. Pause your cursor over the element connector to display the linked element names and the delete button.

  2. Click the delete button.

Or, alternatively:

  1. Open the element action menu for either element and click Delete Link.

3.6 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 3-14.

Figure 3-14 Resulting XML Data Structure

Resulting XML data structure

To define a group-level link:

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

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

    Figure 3-15 Creating a Group Link

    Creating a group link
  3. In the Create Group Link dialog, select the Child Group from the list and click OK. The Create Group Link dialog is shown in Figure 3-16.

    Figure 3-16 Create Group Link Dialog

    Create Group Link dialog
  4. Click the View Actions menu and then click Edit Data Set to add the bind variables to your query.

    An example is shown in Table 3-1.

    Table 3-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.

3.6.1 Deleting Group-Level Links

To delete a group link:

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

  2. Click Delete Group Link.

  3. In the Delete Group Link dialog, select the Child Group from the list and click OK.

3.7 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 3-17.

    Figure 3-17 Creating a Subgroup

    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 3-18 shows how the grouped data set is displayed in the Diagram View along with the structure.

    Figure 3-18 Subgroup Data Structure

    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 View Actions and then click Ungroup.

3.8 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 select 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 Move this element to Parent Group. In Figure 3-19, the element action menu for OFFICE_DSC displays the option to move the element to the parent group.

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

Moving element from child group to parent group

Important:

Before moving an element be aware of any dependencies on other elements.

3.9 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:

To create group-level aggregate elements:

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

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

    Figure 3-20 Creating a Group-Level Aggregate Function

    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 3-21 shows the new aggregate element, CS_1. with the default Count function defined.

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

    New element created by the 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 3-22.

    Figure 3-22 Choosing a Function

    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 3-23.

    Figure 3-23 Properties Dialog

    Properties dialog

    Important:

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

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

    Table 3-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.

    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.


3.10 Creating Group Filters

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

To create a group filter:

  1. Click the View Actions menu and select Create Group Filter.

  2. This displays the Edit Group Filter dialog, as shown in Figure 3-24.

    Figure 3-24 Edit Group Filter Dialog

    Edit Group Filter dialog
  3. Choose 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 Section 1.8, "Setting Data Model Properties."

  4. 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 Section 3.17, "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 3-25.

Figure 3-25 Filter Indicator

Filter indicator

To edit or delete a group filter:

  1. Click the data set View Actions menu.

  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.

3.11 Performing Element-Level Functions

You can perform the following functions at the element level:

3.12 Setting Element Properties

You can set properties for individual elements. Note that these properties are also updatable from the Structure View. If you need to update multiple element properties, it may be more efficient to use the Structure View. See Section 3.16, "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 3-26.

    Figure 3-26 Properties Dialog

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

    Table 3-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.


3.13 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 3-27 shows the Properties dialog for the DEPARTMENT_ID element with the Sort Order list displayed.

    Figure 3-27 Properties Dialog Showing Sort Order List

    Properties dialog showing Sort Order list

3.14 Performing Group-Level Functions

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

3.14.1 The Group Action Menu

The View Actions menu available at the group level enables you to perform the following:

The group-level Actions menu is shown in Figure 3-28.

Figure 3-28 Group-Level Actions Menu

Group-level Actions menu

3.14.2 Editing the Data Set

To edit the underlying data set:

  1. Click Edit Data Set to launch the data set editor.

See the appropriate section for the data set type in Chapter 2, "Creating Data Sets" for more information.

3.14.3 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 3-29.

Note:

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

Figure 3-29 Removing an Element

Removing an element

3.14.4 Editing the Group Properties

To edit the group properties:

  1. Click the View Actions menu and select Properties.

  2. Edit the Group Name and click OK, as shown in Figure 3-30.

    Figure 3-30 Edit the Group Name

    Edit the group name

3.15 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:

Important:

If you select a data type of Integer for any calculated element and the expression returns a fraction, the data is not truncated.

The Global Level Functions object is shown in Figure 3-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, choose the appropriate action from the View Actions menu.

Figure 3-31 Global Level Functions Object

Global Level Functions object

3.15.1 Adding a Global-Level Aggregate Function

To add a global aggregate function:

  1. Drag and drop the data element from the data set to the "Drop here for aggregate function" area of the Global Level Functions object.

    For example, Figure 3-32 shows creating a global level aggregate function based on the Salary element.

    Figure 3-32 Creating Global-Level Aggregate Function

    Creating global-level aggregate function
  2. When you release the mouse, the data model editor assigns a default name to the aggregate element and assigns Count as the default function. Available functions are:

    • Count

    • Average

    • First

    • Last

    • Maximum

    • Minimum

    • Summary

    To change the function, click the function icon to the left of the new element name and choose the function from the list.

    Figure 3-33 shows the function for the new global level element CS_1 being modified from Count to Average.

    Figure 3-33 Applying a Function

    Applying a function
  3. To change the default name, click the actions icon to the right of the element name and click Properties to launch the Edit Properties dialog, See Section 3.12, "Setting Element Properties." for more about the properties available on this dialog.

3.15.2 Adding a Group-Level or Global-Level Element by Expression

To add a group-level or global-level element by expression:

  1. To add a group-level element: On the Group object, click the View Actions menu and select Add Element by Expression.

    To add a global level element: On the Global Level Functions object, click the View Actions menu and select Add Element: by Expression, as shown in Figure 3-34.

    Figure 3-34 Add Element: by Expression

    Add Element by Expression
  2. In the Add Element by Expression dialog, enter the fields, as shown in Figure 3-35.

    Figure 3-35 Add Element by Expression Dialog

    Add Element by Expression dialog

    Table 3-4 Add Element by Expression Dialog Fields

    Field Description

    Name

    Enter a name for this element.

    Alias

    Enter the tag name that the element has in the XML data file.

    Display Name

    The Display Name appears in the report design tools. Enter a name that is meaningful to your business users.

    Data Type

    Select from the list of data types: String, Integer, Double, Float, or Date.


  3. Enter the expression.

    Use the shuttle arrow to move the data elements required for the expression from the Available box to the Expression box.

    Click an operator to insert it in the Expression box, or choose from the function list.

    Refer to Section 3.17, "Function Reference" for a description of the available functions.

  4. Click Validate Expression to validate.

3.15.3 Adding a Global-Level Element by PL/SQL

The PL/SQL function must return a VARCHAR data type.

To add a global-level element by PL/SQL:

  1. On the Global Level Functions object, click the View Actions menu and then click Add Element by PL/SQL.

  2. In the Add Element by PL/SQL dialog, enter the fields, as shown in Figure 3-36 and as described in Table 3-5.

    Figure 3-36 Add Element by PL/SQL Dialog

    Add Element by PL/SQL dialog

    Table 3-5 Add Element by PL/SQL Dialog Fields

    Field Description

    Name

    Enter a name for this element.

    Alias

    Enter the tag name that the element has in the XML data file.

    Display Name

    The Display Name appears in the report design tools. Enter a name that is meaningful to your business users.

    Data Type

    Must select String.

    Sort Order

    Select a sort order.

    Value if Null

    Enter a value to return if the value returned from the PL/SQL function is null.


  3. Select the PL/SQL package from the Available box and click the shuttle button to move the function to the Group Filter box.

3.16 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 3-37.

Figure 3-37 Structure View

Structure view

3.16.1 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 3-38 shows renaming the DEPARTMENT_ID element to display as Department Number.

Figure 3-38 Editing the Display Name of an Element

Editing the display name of an element

3.16.2 Adding Value for Null Elements

The Structure also enables you to enter a value to use for an element if the data model returns a null value for the element.

Enter the value to use in the Value if Null field for the element.

3.17 Function Reference

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

Table 3-6 Supported Functions from the Add Element by Expression Dialog

Function Description

IF

operator

NOT

operator

AND

operator

OR

operator

MAX

Returns the maximum value of the element in the set.

MIN

Returns the minimum value of the element in the set.

ROUND

ROUND (number [, integer]) returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integer can be negative to round off digits left of the decimal point. integer must be an integer.

Example:

round (2.777)

returns

3

Example:

round (2.777, 2)

returns

2.78

FLOOR

FLOOR(n) returns largest integer equal to or less than n.

CEILING

CEILING(n) returns smallest integer greater than or equal to n.

ABS

ABS(n) returns the absolute value of n.

AVG

AVG(expr) returns average value of expr.

LENGTH

The LENGTH(char) function returns the length of char. The LENGTH function calculates the length using characters as defined by the input character set. If char is null, the function returns null. If char is an array, it returns the length of the array.

Example to return length of an array: length({1, 2, 4, 4}) returns 4.

Example to return length of a string: length('countries') returns 9.

SUM

SUM(expr) returns the sum of value of expr.

NVL

NVL(expr1, expr2) lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

CONCAT

CONCAT(char1, char2) returns char1 concatenated with char2.

STRING

STRING(number) returns the number as a string data type.

SUBSTRING

The substring function allows you to extract a substring from a string. The syntax for the substring function is:

substring(string, start_position, end_position)

string is the source string.

start_position is the position to start the extraction.

end_position is the end position of the string to extract (optional).

Examples:

substring('this is a test', 5, 7)

returns "is"

substring('this is a test', 5)

returns "is a test"

INSTR

The instr function returns the location of a substring in a string. The syntax for the instr function is:

instr(string1,string2)

string1 is the string to search

string2 is the substring to search for in string1.

Example: instr('this is a test', 'is a')

returns 5.

DATE

DATE(date_str,format_str) Converts char to date data type. The format string must be a valid Java date format string.

Example: DATE('01-01-2011','MM-dd-yyyy')

FORMAT_DATE

The FORMAT_DATE function takes a date argument in Java date format and converts it to a to formatted string.

For example:

FORMAT_DATE(HIRE_DATE, 'MM-DD-YYYY') where the value of HIRE_DATE is 1987-09-17T00:00:00.000+00:00 would return 17-Sep-1987.

FORMAT_NUMBER

The FORMAT_NUMBER function takes a number argument and converts it to a string in the format specified. For example, FORMAT_NUMBER (SALES_UNITS,'9G990D000')

NUMBER

NUMBER(char) converts char to a number data type.