2 Creating Data Sets
Overview of Creating Data Sets
Oracle BI Publisher can retrieve data from multiple types of data sources.
To create a data set:
-
On the component pane of the data model editor, click New Data Set and select your source data set type.
-
Complete the required fields. See the corresponding section:
Editing an Existing Data Set
Modify data models by editing any of the data sets they contain.
- Click Edit Selected Data Set. The dialog for the data set opens. For information about each type of data set, see the corresponding section in this chapter.
Creating Data Sets Using SQL Queries
These topics explain how to create data sets using SQL queries.
Entering SQL Queries
Use these steps to enter SQL queries.
For information about optimizing your SQL Queries and the Generate Explain Plan option, see Best Practices for SQL Data Sets.
- Click New Data Set and then click SQL Query. The Create Data Set - SQL dialog opens, as shown below.
Creating Non-Standard SQL Data Sets
In addition to creating data sets using basic SQL commands, you can create data sets using more complex commands.
Procedure Call
Use this query type to call a database procedure. For example, Oracle PL/SQL statements start with BEGIN. When you use this SQL data type, no metadata is displayed on the data model structure tab, therefore you cannot modify the data structure or data fields. To construct your SQL with a procedure call enter the code directly in the text box or copy and paste from another SQL editor. You cannot use the Query Builder to modify or build these types of queries.
Non-standard SQL
Use this query type to issue SQL statements that can include the following:
-
Cursor statements that return nested results sets
For example:
Ex:SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE , CURSOR (SELECT d.order_id department_id, d.order_mode department_name , CURSOR (SELECT e.cust_first_name first_name, e.cust_last_name last_name, e.customer_id employee_id, e.date_of_birth hire_date FROM customers e WHERE e.customer_id IN (101,102) ) emp_cur FROM orders d WHERE d.customer_id IN (101,102) ) DEPT_CUR FROM dual -
Functions returning
REFcursorsFor example:
create or replace PACKAGE REF_CURSOR_TEST AS TYPE refcursor IS REF CURSOR; pCountry VARCHAR2(10); pState VARCHAR2(20); FUNCTION GET( pCountry IN VARCHAR2, pState IN VARCHAR2) RETURN REF_CURSOR_TEST.refcursor; END;
create or replace PACKAGE BODY REF_CURSOR_TEST AS FUNCTION GET( pCountry IN VARCHAR2, pState IN VARCHAR2) RETURN REF_CURSOR_TEST.refcursor IS l_cursor REF_CURSOR_TEST.refcursor; BEGIN IF ( pCountry = 'US' ) THEN OPEN l_cursor FOR SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE , d.order_id department_id, d.order_mode department_name FROM orders d WHERE d.customer_id IN (101,102); ELSE OPEN l_cursor FOR SELECT * FROM EMPLOYEES; END IF; RETURN l_cursor; END GET; END REF_CURSOR_TEST;To use
REFcursor in Oracle BI Publisher:create SQL dataset with query as SELECT REF_CURSOR_TEST.GET(:PCNTRY,:PSTATE) AS CURDATA FROM DUAL
-
Anonymous blocks/Stored procedures
BI Publisher supports executing PL/SQL anonymous blocks. You can perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.
The requirements are:
-
The PL/SQL block must return a result set of type
REFcursor -
You must declare the out variable with the name,
xdo_cursor;. If you do not declare the name properly, the first bind variable is treated as an out variable type and binds withREFcursor -
Declare the data model parameter with name
xdo_cursor. This name is reserved for out variable type for procedure/anonymous blocks.
Example:
DECLARE type refcursor is REF CURSOR; xdo_cursor refcursor; empno number; BEGIN OPEN :xdo_cursor FOR SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = :P2; COMMIT; END; -
-
Conditional queries can be executed if you use an if-else expression. You can define multiple SQL queries in a single data set, but only one query executes at run time depending on the expression value. The expression validates and returns a boolean value. If the value is true, executes that section of the SQL query.
The limitations are:
-
The following syntax is supported to evaluate expressions:
$if{,$elseif{,$else{ -
The expression must return true, false
-
Only the following operators are supported:
== <= >= < >
Example: create sql dataset with following query $if{ (:P_MODE == PRODUCT) }$ SELECT PRODUCT_ID ,PRODUCT_NAME ,CATEGORY_ID ,SUPPLIER_ID ,PRODUCT_STATUS ,LIST_PRICE FROM PRODUCT_INFORMATION WHERE ROWNUM < 5 $elsif{(:P_MODE == ORDER )}$ SELECT ORDER_ID ,ORDER_DATE ,ORDER_MODE ,CUSTOMER_ID ,ORDER_TOTAL ,SALES_REP_ID FROM ORDERS WHERE ROWNUM < 5 $else{ SELECT PRODUCT_ID , WAREHOUSE_ID ,QUANTITY_ON_HAND FROM INVENTORIES WHERE ROWNUM < 5 }$ $endif$ -
When your data set is created using non-standard SQL statements, no metadata is displayed on the data model structure tab, therefore you cannot modify the data structure or data fields. You cannot use Query Builder to modify or build these types of queries.
To define XML row tag for non-standard SQL dataset:
Use xmlRowTagName="" in data model definition to define xml row tag for non-standard SQL query dataset. This allows you to enter a valid tag name. If the attribute is empty, it defaults to ROW at runtime.
Dataset definition:
<dataSet name="Q1" type="simple">
<sql dataSourceRef="bipdev4-demo" nsQuery="true" xmlRowTagName="">
,,
</sql>
</dataset>
Using the SQL Query Builder
Use the Query Builder to build SQL queries without coding. The Query Builder enables you to search and filter database objects, select objects and columns, create relationships between objects, and view formatted query results with minimal SQL knowledge.
This section describes how to use the Query Builder and includes the following topics:
Overview of the Query Builder
The Query Builder page is divided into an Object Selection pane and a design and output pane.
-
Object Selection pane contains a list of objects from which you can build queries. Only objects in the current schema display.
-
Design and output pane consists of four tabs:
-
Model — Displays selected objects from the Object Selection pane.
-
Conditions — Enables you to apply conditions to your selected columns.
-
SQL — Displays the query.
-
Results — Displays the results of the query.
-
Supported Column Types
Columns of all types display as objects in the Design pane.
The column restrictions are:
-
You can select no more than 60 columns for each query.
-
You can select the following column types:
-
VARCHAR2, CHAR
-
NUMBER
-
DATE, TIMESTAMP
Note:
The
TIMESTAMP WITH LOCAL TIMEZONEdata type is not supported. -
Binary Large Object (BLOB)
Note:
The BLOB must be an image. When you execute the query in the Query Builder, the BLOB does not display in the Results pane; however, the query is constructed correctly when saved to the data model editor. BI Publisher does not support querying of BLOB columns in an Oracle BI EE data source.
-
Character Large Object (CLOB)
Note:
BI Publisher does not support querying of CLOB columns in an Oracle BI EE data source.
See Using Data Stored as a Character Large Object (CLOB) in a Data Model for more information about working with CLOB data in the data model.
-
Adding Objects to the Design Pane
Select each object you want to add to the Design pane.
-
When you add an object, an icon representing the data type displays next to each column name.
-
When you select a column, it appears on the Conditions tab. The Show check box on the Conditions tab controls whether a column is included in query results. Be default, this check box is selected.
-
To select the first twenty columns, click the small icon in the upper left corner of the object and then select Check All.
-
You can also execute a query by pressing the CTRL + ENTER keys.
- Select an object.
- Select the check box for each column to include in your query.
- To execute the query and view results, select Results.
Removing or Hiding Objects in the Design Pane
You can remove or hide objects in the Design pane.
To remove an object:
-
Click Remove in the upper right corner of the object.
To temporarily hide the columns within an object:
- Click Show/Hide Columns.
Specifying Query Conditions
Conditions enable you to filter and identify the data you want to work with.
As you select columns within an object, you can specify conditions on the Conditions tab (shown below). You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions.
The table below describes the attributes available on the Conditions tab.
| Condition Attribute | Description |
|---|---|
|
Up and Down Arrows |
Controls the display order of the columns in the resulting query. |
|
Column |
Displays the column name. |
|
Alias |
Specify an optional column alias. An alias is an alternative column name. Aliases are used to make a column name more descriptive, to shorten the column name, or prevent possible ambiguous references. Note that multibyte characters are not supported in the alias name. |
|
Object |
Displays the object name. |
|
Condition |
The condition modifies the query's WHERE clause. When specifying a column condition, you must include the appropriate operator and operand. All standard SQL conditions are supported. For example: >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15 |
|
Sort Type |
Select ASC (Ascending) or DESC (Descending). |
|
Sort Order |
Enter a number (1, 2, 3, and so on) to specify the order in which selected columns should display. |
|
Show |
Select this check box to include the column in your query results. You do not need to select Show to add a column to the query for filtering only. For example, to create following query: SELECT ename FROM emp WHERE deptno = 10 To create this query in Query Builder:
|
|
Function |
Available argument functions include:
|
|
Group By |
Specify columns to be used for grouping when an aggregate function is used. Only applicable for columns included in output. |
|
Delete |
Deselect the column, excluding it from the query. |
As you select columns and define conditions, Query Builder writes the SQL for you.
To view the underlying SQL:
-
Select the SQL tab.
Creating Relationships Between Objects
You can create relationships between objects by creating a join. A join identifies a relationship between two or more tables, views, or materialized views.
About Join Conditions
When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition.
A join condition specifies how the rows from one object combine with the rows from another object.
Query Builder supports inner, outer, left, and right joins.
-
An inner join, also called a simple join, returns the rows that satisfy the join condition.
-
An outer join extends the result of a simple join.
An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Saving a Query
Save queries after building them.
Once you have built the query, click Save to return to the data model editor. The query appears in the SQL Query box. Click OK to save the data set.
Editing a Saved Query
When you have saved the query from the Query Builder to the data model editor, you can also use the Query Builder to edit the query.
If you have made modifications to the query, or did not use the Query Builder to construct it, you may receive an error when launching the Query Builder for editing the query. If the Query Builder cannot parse the query, you can edit the statements directly in the text box.
Note:
You cannot edit a customized or an advanced query by using Query Builder.- Select the SQL data set.
- On the toolbar, click Edit Selected Data Set to launch the Edit Data Set dialog.
- Click Query Builder to load the query to the Query Builder.
- Edit the query and click Save.
Adding a Bind Variable to a Query
After creating a query, you may want users to be able to pass a parameter to the query to limit the results. For example, in the employee listing, you want users to be able to choose a specific department.
The image shows the columns in the department table.
Adding a Bind Variable Using a Text Editor
Use the Data Model Editor to update a SQL query.
- In the Edit Data Set text box, update the SQL query by adding the following after the where clause in your query:
and "COLUMN_NAME" in (:PARAMETER_NAME)for example:
and "DEPARTMENT_NAME" in (:P_DEPTNAME)where
P_DEPTNAMEis the name you choose for the parameter, as shown below. - In the data model editor, create the parameter that you entered with the bind variable syntax as shown in the image.
Adding Lexical References to SQL Queries
You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING.
Use a lexical reference when you want the parameter to replace multiple values at runtime. Lexical references are supported in queries against Oracle applications only.
Create a lexical reference in the SQL query using the following syntax:
¶metername
Note:
You also use lexical references to include flexfields in your query. For more information about using flexfields, see Adding Flexfields.
- Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.
- In the data model editor, on the Properties page, specify the Oracle DB Default Package.
- In the data model editor, create a Before Data event trigger to call the PL/SQL package. See Adding Before Data and After Data Triggers for more information about procedures.
- Create your SQL query containing lexical references.
- When you click OK to close your SQL query, you are prompted to enter the parameter.
For example, create a package called employee. In the employee package, define a parameter called where_clause:
Package employee AS where_clause varchar2(1000); ..... Package body employee AS ..... where_clause := 'where DEPARTMENT_ID=10'; .....
Reference the lexical parameter in the SQL query where you want the parameter to be replaced by the code defined in the package, for example:
select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."SALARY" as "SALARY",
from "OE"."EMPLOYEES" "EMPLOYEES"
&where_clause
When you click OK on the Create SQL Data Set dialog box, the lexical reference dialog box prompts you to enter a value for lexical references you entered in the SQL query, as shown in the image that follows. Enter the value of the lexical reference as it is defined in the PL/SQL package.
At runtime, the data engine replaces &where_clause with the contents of where_clause defined in the package.
Defining SQL Queries Against the Oracle BI Server
When you launch the Query Builder against the Oracle BI Server, the Query Builder displays the subject areas from the catalog. You can drag the subject areas to the Query Builder workspace to display the columns. Select the columns to include in your data model.
Keep the following points in mind when creating a data set against the Oracle BI Server:
-
When you create a SQL query against the Oracle BI Server using the SQL Data Editor or the Query Builder, logical SQL is generated, not physical SQL like other database sources.
-
Hierarchical columns are not supported. The highest level is always returned.
-
Within a subject area, the join conditions between tables are already created; it is therefore not necessary to create joins in the Query Builder. The Query Builder does not expose the primary key.
It is possible to link data sets using the data model editor's Create Link function. See Creating Element-Level Links. For data sets created from the Oracle BI Server, there is a limit of two element-level links for a single data model.
-
In the Query Builder, the functions Sort Order and Group By shown on the Conditions tab are not supported for queries against the Oracle BI Server. If you enter a Sort Order or select the Group By check box, the Query Builder constructs the SQL, and write it to the BI Publisher SQL Query text box, but when you attempt to close the Data Set dialog, the query fails validation.
To apply grouping to the data retrieved by the SQL query, you can use the data model editor's Group by function instead. See Creating Subgroups.
-
If you pass parameters to the Oracle BI Server and you choose Null Value Passed for Can Select All, you must ensure that you handle the null value in your query.
- From the Catalog drop-down list, select a subject area as shown below. The list displays the subject areas defined in the Oracle BI Server.
Notes for Queries Against Oracle Fusion Applications Tables
Special considerations for Oracle Fusion Applications customers apply when writing queries against the Oracle Fusion Applications tables
-
You cannot return month name from
sysdateusingto_char(sysdate,"mon"). This function returns the month number. To display month name, use one of the following solutions:-
Format the date field in your layout using the following syntax:
<?format_date:fieldname;MASK)?>See Formatting Dates in Report Designer's Guide for Oracle Business Intelligence Publisher.
-
To display month name based on month number, use the following syntax in your layout:
<?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?>where
monthis the numeric value of the month (January = 1) and[abbreviate?]is the value 0 for do not abbreviate or 1 for abbreviate.For example:
<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>returns January
-
To add an expression in the data model, use the following expression:
Format_date(date, format_String)For example:
SUBSTRING(FORMAT_DATE(G_1.SYSDATE,MEDIUM),0,3)returns Nov (when the current
SYSTDATEis November)
-
Creating a Data Set Using a MDX Query Against an OLAP Data Source
BI Publisher supports Multidimensional Expressions (MDX) queries against OLAP data sources.
MDX enables you to query multidimensional objects, such as Essbase cubes, and return multidimensional cell sets that contain the cube's data. You create MDX queries by manually entering the MDX query or by using MDX Query Builder to build the query.
Creating a Data Set Using a MDX Query
You create MDX queries either by manually entering the MDX query or by using MDX Query Builder to build the query.
- On the toolbar, click New Data Set and then select MDX Query.
The New Data Set - MDX Query dialog is shown below.
Note:
Ensure that in your OLAP data source that you do not use Unicode characters from the range U+F900 to U+FFFE to define any metadata attributes such as column names or table names. This Unicode range includes half-width Japanese Katakana and full-width ASCII variants. Using these characters results in errors when generating the XML data for a BI Publisher report.
For more information on writing MDX queries, see Writing MDX Queries in the Oracle Essbase Database Administrators Guide which can be found here: Oracle Essbase for BI documentation.
Using MDX Query Builder
Use MDX Query Builder to build MDX basic queries without having to code them. MDX Query Builder enables you to add dimensions to columns, rows, pages, and point of view axes and preview the query results.
Note:
MDX Query Builder only enables you to build data sets against Essbase data sources. For all other OLAP data sources, you must manually create the query.
Understanding the MDX Query Builder Process
You create MDX queries either by manually entering the MDX query or by using MDX Query Builder to build the query.
To use MDX Query Builder to build a MDX query:
- On the toolbar, click New Data Set and then select MDX Query to launch the New Data Set - MDX Query dialog.
- Enter a name for the data set.
- Select a data source.
- Launch MDX Query Builder.
- Select an Essbase cube for the query.
- Select dimensions and measures by dragging and dropping them to the Columns, Rows, Slicer/POV, and the Pages axes.
- (Optional) Use actions to modify the query.
- (Optional) Apply filters.
- Set the query options and save the query.
Using the Select Cube Dialog
In the Select Cube dialog, select the Essbase cube that you want to use to build the MDX query.
The MDX data source connection that you selected previously drives which Essbase cubes are available for selection.
Selecting Dimensions and Measures
You build MDX queries by selecting dimensions for the Columns, Rows, Slicer/POV, and Pages axes.
Account dimension members are listed individually by member name. All other dimension members are represented by generation name as shown below.
You can drag dimension generations and individual measures from the Account dimension to the Columns, Rows, Slicer/POV, and Pages axes.
Build the query by dragging dimension members or measures from the Dimensions panel to one of the following axes areas:
-
Columns— Axis (0) of the query
-
Rows — Axis (1) of the query
-
Slicer/POV— The slicer axis enables you to limit a query to only a specific slice of the Essbase cube. This represents the optional WHERE clause of a query.
-
Axis — Axis (2) of the query
You can nest dimension members in the Columns and Rows axes, but you can only add a single dimension to the Slider/POV axis.
Adding Dimension Members to the Slicer/POV Axis
When you add a dimension to the Slicer/POV axis, the Member Selection dialog launches.
You can only select one dimension member for this axis. Simply select the dimension in the Member Selection dialog, and then click OK.
The Member Selection dialog does not display if you add a measure to the Slicer/POV axis.
Performing MDX Query Actions
The MDX Query Builder toolbar contains the following buttons for modifying the MDX query:
-
Click Swap Rows and Columns to flip dimensions between columns and rows.
-
Click Actions to display the following menu items for selection:
-
Select Cube - Selects a different Essbase cube for the query.
-
Set Alias Table - Selects the alias table used for dimension display names. Alias names are used for display only and are not used in the query.
-
Auto Refresh - Displays the results as dimension members are placed in the Columns, Rows, Slicer/POV, and Pages axes and automatically refreshes the MDX query syntax.
-
Clear Results - Clears the results and removes member selections from all of the axes and any filters added to the query.
-
Show Empty Columns - Displays columns that do not contain data.
-
Show Empty Rows - Displays rows that do not contain data.
-
Show Query - Displays the MDX query syntax resulting from how the dimensions are placed in the Columns, Rows, Slicer/POV, and Pages axes.
-
Applying MDX Query Filters
You can create filters for dimensions on the Columns, Rows, and Pages axes in MDX Query Builder to further streamline your MDX query.
You can create multiple filters for a query, but you can only create one filter for each Columns, Rows, or Pages axis.
- To create a filter, click the down-arrow button to the right of a dimension in the Columns, Rows, or Pages axes to display it in the Filters area. You create the filter by selecting the desired dimension member as shown below.
Selecting MDX Query Options and Saving MDX Queries
Use the Options dialog to select the dimension properties to include in the query for each of the dimensions in the Columns, Rows, and Pages axes.
Once you have built the query, click Save to display the Options dialog as shown below.
By default, none of the properties are selected.
The dimension properties are as follows:
-
Member Alias — Dimension member alias names as listed in the Essbase outline.
-
Ancestor Names — Ancestor dimension names as listed in the Essbase outline.
-
Level Number — Dimension level numbers as listed in the Essbase outline.
-
Gen Number — Generation number of the dimensions as listed in the Essbase outline.
For example, if you select the Member Alias and Level Number properties for Columns, the MDX query results are as follows:
SELECT
NON EMPTY Hierarchize([Market].Generations(2).Members)
PROPERTIES MEMBER_ALIAS,LEVEL_NUMBER ON Axis(0),
NON EMPTY CROSSJOIN(Hierarchize([Product].Generations(3).Members),
{[Accounts].[Margin],[Accounts].[Sales],[Accounts].[Total_Expenses]})ON Axis(1),
NON EMPTY [Year].Generations(3).Members ON Axis(2) FROM Demo.Basic
For more information on Essbase dimension properties, see: Oracle Essbase Database Administrator's Guide which can be found here: Oracle Essbase for BI documentation.
After you select options for the MDX query, click OK to return to the New Data Set - MDX Query dialog and review the MDX query output as shown below.
Click OK to return to the data model editor, and save your changes.
Note:
If you modify a MDX query after you save it in BI Publisher, Oracle recommends that you manually change the syntax and not use MDX Query Builder to do so.
Creating a Data Set Using an Oracle BI Analysis
If you have enabled integration with Oracle Business Intelligence, then you can access the Oracle Business Intelligence Presentation catalog to select an Oracle BI analysis as a data source.
An analysis is a query against an organization's data that provides answers to business questions. A query contains the underlying SQL statements that are issued to the Oracle BI Server.
See User's Guide for Oracle Business Intelligence Publisher.
Note:
Hierarchical columns are not supported in BI Publisher data models.
- Click the browse icon to connect to the Oracle Business Intelligence Presentation catalog, as shown below.
- Enter a Time Out value in seconds, as shown below. If BI Publisher has not received the analysis data after the time specified in the time out value has elapsed, then BI Publisher stops attempting to retrieve the analysis data.
Additional Notes on Oracle BI Analysis Data Sets
Parameters and list of values are inherited from the BI analysis and they display at run time.
The BI Analysis must have default values defined for filter variables. If the analysis contains presentation variables with no default values, it is not supported as a data source by BI Publisher.
If you want to structure the data based on Oracle BI Analysis Data Sets, the group breaks, group filters, data links and group-level functions are not supported.
The following are supported:
-
Global level functions
-
Setting the value for elements if null
For more information about the above supported features, see Structuring Data.
Creating a Data Set Using a View Object
BI Publisher enables you to connect to your custom applications built with Oracle Application Development Framework and use view objects in your applications as data sources for reports.
Performance of the query execution is better as the SQL is executed directly against the database.
Before you can create an Oracle BI Publisher data model using a view object, you must first create the view object in your application following the guidelines in Developer's Guide for Oracle Business Intelligence Publisher.
Additional Notes on View Object Data Sets
To structure data based on view object data sets, the group breaks, data links and group-level functions are not supported.
The following is supported: Setting the value for elements if null.
For more information about this supported feature, see Structuring Data.
Creating a Data Set Using a Web Service
BI Publisher supports data sets that use simple and complex Web service data sources to return valid XML data.
To include parameters for Web service methods, it is recommended that you define the parameters first, so that the methods are available for selection when setting up the data source. See Adding Parameters and Lists of Values.
Multiple parameters are supported. Ensure the method name is correct and the order of the parameters matches the order in the method. To call a method in the Web service that accepts two parameters, you must map two parameters defined in the report to the two parameters in the method. Note that only parameters of simple type are supported, for example, string and integer.
Note:
Only document/literal Web services are supported.
To specify a parameter, click Add Parameter and select the parameter from the drop-down list.
Note:
The parameters must be set up in the Parameters section of the report definition. For more information, see Adding Parameters and Lists of Values.
Web Service Data Source Options
Web service data sources can be set up on the Administration page or as a data source.
-
On the Administration page
Connections to Web service data sources can be set up on the Administration page and then used in multiple data models. See Setting Up a Connection to a Web Service in Administrator's Guide for Oracle Business Intelligence Publisher.
-
As a private data source
You can also set up a private connection accessible only to you. See Managing Private Data Sources for information about private data source connections.
You must set up the connection before you create the data model.
BI Publisher supports data sets that use simple and complex Web service data sources to return valid XML data:
Creating a Data Set Using a Simple Web Service
If you are not familiar with the available methods and parameters in the Web service to call, you can open the URL in a browser to view them.
- Click the New Data Set toolbar button, and then select Web Service.
The New Data Set - Web Service dialog is shown below.
- Define the parameters to make them available to the Web service data set as follows:
-
Select Parameters on the Data Model pane, click Create New Parameter, and then enter the following attributes:
-
Name - Enter an internal identifier for the parameter, for example, Symbol.
-
Data Type - Select String.
-
Default Value - Enter a default for the parameter, for example, ORCL.
-
Parameter Type - Select Text.
-
Row Placement - Select a row placement value. The default is 1.
-
-
In the New_Parameter_1:Type: Text region, enter the following attributes:
-
Display Label - Enter the label you want displayed for your parameter, for example, Stock Symbol.
-
Text Field Size - Enter the size for the text entry field in characters.
-
-
(Optional) Select the following parameter options:
-
Text field contains comma-separated values - Enables the user to enter multiple comma-delimited values for this parameter.
-
Refresh other parameters on change - Performs a partial page refresh to refresh any other parameters whose values are dependent on the value of this one.
-
-
- Return to the Web service data set and add the parameter as follows:
-
Click the data set name.
-
On the toolbar, click Edit Selected Data Set to launch the Edit Data Set dialog.
-
In the Edit Data Set dialog, click Add Parameter.
-
Name the parameter, and then click OK to close the Edit Data Set dialog.
-
Creating a Data Set Using a Complex Web Service
BI Publisher supports data sets that use complex Web service data sources to return valid XML data. A complex Web service type internally uses soapRequest / soapEnvelope to pass the parameter values to the destination host.
When a data set uses a complex Web service as a data source, the data model editor displays the WSDL URL, available Web service, and operations associated with the complex Web service. For each selected operation, the data model editor displays the structure of the required input parameters. If you choose Show optional parameters, all optional parameters as displayed as well.
If you are not familiar with the available methods and parameters in the Web service, open the WSDL URL in a browser to view them.
- Return to the Web Service data set and add the parameter.
-
Select the Web service data set, and then click Edit Selected Data Set to launch the Edit Data Set dialog.
- Select the parameters as shown below.
-
Creating a Data Set Using an LDAP Query
BI Publisher supports queries against Lightweight Directory Access protocol (LDAP) data sources.
You can query user information stored in LDAP directories and then use the data model editor to link the user information with data retrieved from other data sources.
For example, to generate a report that lists employee salary information that is stored in the database application and also include employee e-mail addresses that are stored in the LDAP directory in the report, you can create a query against each and then link the two in the data model editor to display the information in a single report. The figure below shows a sample LDAP query.
Creating a Data Set Using a XML File
You can use an XML file to create a data source.
Do one of the following:
-
Place the XML file in a directory that your administrator has set up as a data source. See Setting Up a Connection to a File Data Source in Administrator's Guide for Oracle Business Intelligence Publisher.
-
Upload the XML file to the data model from a local directory.
Note:
To use BI Publisher's layout editor and interactive viewer, sample data from the XML file source must be saved to the data model.
About Supported XML Files
Support of XML files as a data set type in BI Publisher follows certain guidelines.
-
The XML files that you use as input to the BI Publisher data engine must be UTF-8 encoded.
-
Do not use the following characters in XML tag names: ~, !, #, $, %, ^, &, *, +, `, |, :, \", \\, <, >, ?, ,, /. If your data source file contains any of these characters, use the data model editor Structure tab to change the tag names to an acceptable one.
-
Use valid XML files. Oracle provides many utilities and methods for validating XML files.
-
There is no metadata available from XML file data sets, therefore grouping and linking are not supported.
Using a XML File Stored in a File Directory Data Source
Create data sets using XML files stored in file directories.
To create a data set using a XML file from a file directory data source:
- On the toolbar, click New Data Set and select XML File. The New Data Set - XML File dialog launches, as shown below.
Uploading a XML File Stored Locally
You can create data sets using locally stored XML files.
To create a data set using a XML file stored locally:
- On the toolbar, click New Data Set and select XML File. The New Data Set - XML File dialog launches, as shown below.
Refreshing and Deleting an Uploaded XML File
You can refresh and delete uploaded local XML files.
After uploading the file, it is displayed on the Properties pane of the data model under the Attachments region, as shown below.
See Setting Data Model Properties for more information about the Properties pane.
To refresh the local file in the data model:
-
In the component pane, click Data Model to view the Properties page.
-
In the Attachment region of the page, locate the file in the Data Files list.
-
Click Refresh.
-
In the Upload dialog, browse for and upload the latest version of the file. The file must have the same name or it will not replace the older version.
-
Save the data model.
To delete the local file:
- In the component pane, click Data Model to view the Properties page.
- In the Attachment region of the page, locate the file in the Data Files list.
- Click Delete.
- Click OK to confirm.
- Save the data model.
Creating a Data Set Using a Content Server
You can set up connections to Content Server data source on the Administration page and then use that in multiple data models.
For more information, see Setting Up a Connection to a Content Server in Administrator's Guide for Oracle Business Intelligence Publisher.
- Select the Content Type from the LOV.
Creating a Data Set Using a Microsoft Excel File
These topics describe requirements, options, and procedures for using Microsoft Excel files as a data source.
Options for Uploading Excel Files to BI Publisher
To use a Microsoft Excel file as a data source, you have the two options for providing the file to BI Publisher.
-
Place the file in a directory that your administrator has set up as a data source. See Setting Up a Connection to a File Data Source in Administrator's Guide for Oracle Business Intelligence Publisher.
-
Upload the file to the data model from a local directory.
About Supported Excel Files
Support of Microsoft Excel files as a data set type in Oracle BI Publisher follows certain guidelines.
-
Save Microsoft Excel files in the Excel 97-2003 Workbook (*.xls) format by Microsoft Excel. Files created by a third party application or library are not supported.
-
The source Excel file can contain a single sheet or multiple sheets.
-
Each worksheet can contain one or multiple tables. A table is a block of data that is located in the continuous rows and columns of a sheet.
In each table, Oracle BI Publisher always considers the first row to be the heading row for the table.
-
The first row under the heading row must not be empty and is used to determine the column type of the table. The data type of the data in the table may be number, text, or date/time.
-
If multiple tables exist in a single worksheet, the tables must be identified with a name for BI Publisher to recognize each one. See Accessing Multiple Tables per Sheet.
-
If all tables in the Excel file are not named, only the data in the first table is recognized and fetched.
-
When the data set is created, BI Publisher truncates all trailing zeros after the decimal point for numbers in all cases. To preserve the trailing zeros in your final report, you must apply a format mask in your template to display the zeroes. See Formatting Numbers, Dates, and Currencies in Report Designer's Guide for Oracle Business Intelligence Publisher.
-
Single value parameters are supported, but multiple value parameters are not supported.
Accessing Multiple Tables per Sheet
If the Excel worksheet contains multiple tables that you want to include as data sources, then you must define a name for each table in Excel.
Note:
The name that you define must begin with the prefix: BIP_, for example, BIP_SALARIES.
- Insert the table in Excel.
- Do one of the following:
- Using Excel 2003, select the table. On the Insert menu, click Name and then Define. Enter a name that is prefixed with BIP_.
- Using Excel 2007, select the table. On the Formulas tab, in the Defined Names group, click Define Name, then enter the name in the Name field. The name you enter appears on the Formula bar
For example, you could use the Define Name command in Microsoft Excel 2007 to name a table BIP_Salaries.
Using a Microsoft Excel File Stored in a File Directory Data Source
Create data sets using Microsoft Excel files stored in file directories.
Note that to include parameters for your data set, you must define the parameters first, so that they are available for selection when defining the data set. See Adding Parameters and Lists of Values.
Note:
The Excel data set type supports one value per parameter. It does not support multiple selection for parameters.
To create a data set using a Microsoft Excel file from a file directory data source:
- If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this data set, as shown below.
Uploading a Microsoft Excel File Stored Locally
To use a local Microsoft Excel file as a data source, you must first upload it.
Note that to include parameters for the data set, you must define the parameters first, so that they are available for selection when defining the data set. See Adding Parameters and Lists of Values.
Note:
The Excel data set type supports one value per parameter. It does not support multiple selection for parameters.
To create a data set using a Microsoft Excel file stored locally:
- If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this data set, as shown below.
Refreshing and Deleting an Uploaded Excel File
You can refresh and delete uploaded local Excel files.
After uploading the file, it displays on the Properties pane of the data model under the Attachments region, as shown below.
See Setting Data Model Properties for information about the Properties pane.
To refresh the local file in the data model:
-
Click Data Model in the component pane to view the Properties page.
-
In the Attachment region of the page, locate the file in the Data Files list.
-
Click Refresh.
-
In the Upload dialog, browse for and upload the latest version of the file. The file must have the same name or it will not replace the older version.
-
Save the data model.
To delete the local file:
- Click Data Model in the component pane to view the Properties page.
- In the Attachment region of the page, locate the file in the Data Files list.
- Click Delete.
- Click OK to confirm.
- Save the data model.
Creating a Data Set Using a CSV File
BI Publisher supports data sets that use CSV file data sources to return valid XML data.
The following topics describe using requirements and procedures for using a CSV as a data source:
About Supported CSV Files
Support of CSV files as a data set type in BI Publisher follow certain guidelines.
-
You can use a CSV file that is located in a directory that your administrator has set up as a data source. See Setting Up a Connection to a File Data Source in Administrator's Guide for Oracle Business Intelligence Publisher.
You can upload a file from a local directory.
-
The supported CSV file delimiters are Comma, Pipe, Semicolon, and Tab.
-
If your CSV file contains headers, the header names are used as the XML tag names. The following characters are not supported in XML tag names: ~, !, #, $, %, ^, &, *, +, `, |, :, \", \\, <, >, ?, ,, /. If your data source file contains any of these characters in a header name, use the data model editor Structure tab to edit the tag names.
-
CSV data sets support editing the data type assigned by the data model editor. See Editing the Data Type. If you update the data type for an element in the data set, you must ensure that the data in the file is compliant with the data type that you selected.
-
The CSV files that you use as input to the Oracle BI Publisher data engine must be UTF-8 encoded and cannot contain empty column headers.
-
Group breaks, data links, expression and group-level functions are not supported.
-
Data fields in CSV files must use the canonical ISO date format for mapped date elements, for example, 2012-01-01T10:30:00-07:00, and ######.## for mapped number elements.
-
Data validation is not provided for CSV files.
Creating a Data Set from a Centrally Stored CSV File
You can use a CSV file from a file directory to create a data set.
- On the data model editor toolbar, click New Data Set and select CSV File. The New Data Set - CSV File dialog launches.
Uploading a CSV File Stored Locally
Create data sets using CSV files stored in local file directories.
To create a data set using a CSV file stored locally:
- On the toolbar, click New Data Set and select CSV File. The New Data Set - CSV File dialog launches, as shown below.
Editing the Data Type
After uploading a CSV file data type, you can edit it as needed.
To edit the data type for a CSV file element, click the data type icon or update it from the element Properties dialog.
The data for an element must be compliant with the data type that you assign. The user interface does not validate the data when you update the data type. If the data does not match, for example, a string value is present for an element you defined as Integer, errors may occur in the layout editing tools and or at runtime.
You can only update the data types for CSV file data sources.
Refreshing and Deleting an Uploaded CSV File
You can refresh and delete uploaded local CSV files.
After uploading the file, it is displayed on the Properties pane of the data model under the Attachments region, as shown below.
See Setting Data Model Properties for more information about the Properties pane.
To refresh the local file in the data model:
-
In the component pane, click Data Model to view the Properties page.
-
In the Attachment region of the page, locate the file in the Data Files list.
-
Click Refresh.
-
In the Upload dialog, browse for and upload the latest version of the file. The file must have the same name or it will not replace the older version.
-
Save the data model.
To delete the local file:
- In the component pane, click Data Model to view the Properties page.
- In the Attachment region of the page, locate the file in the Data Files list.
- Click Delete.
- Click OK to confirm.
- Save the data model.
Creating a Data Set from an HTTP XML Feed
Using the HTTP (XML Feed) data set type, you can create data models from RSS and XML feeds over the Web by retrieving data through the HTTP GET method.
Note:
You might require additional configuration to access external data source feeds depending on your system's security. If the RSS feed is protected by Secure Sockets Layer (SSL), see Configuring BI Publisher for Secure Socket Layer (SSL) Communication in Administrator's Guide for Oracle Business Intelligence Publisher.
To include parameters for the data set, it is recommended that you define the parameters first, so that they are available for selection when defining the data set. See Adding Parameters and Lists of Values.
There is no metadata available from HTTP XML feed data sets, therefore grouping and linking are not supported.
Creating a Data Set from an HTTP XML Data Set
You can set up an HTTP (XML Feed) data sources in two different ways.
-
On the Administration page:
Connections to HTTP data sources can be set up on the Administration page and then used in multiple data models. See Setting Up a Connection to a HTTP XML Feed in Administrator's Guide for Oracle Business Intelligence Publisher.
-
As a private data source:
You can also set up a private connection accessible only to you. See Managing Private Data Sources for information about private data source connections.
- On the toolbar, click New Data Set and select HTTP (XML Feed). The New Data Set - HTTP (XML Feed) dialog launches, as shown below.
Using Data Stored as a Character Large Object (CLOB) in a Data Model
BI Publisher supports using data stored as a character large object (CLOB) data type in your data models. This feature enables you to use XML data generated by a separate process and stored in your database as input to a BI Publisher data model.
Use the Query Builder to retrieve the column in your SQL query, then use the data model editor to specify how you want the data structured. When the data model is executed, the data engine can structure the data either as:
-
A plain character set within an XML tag name that can be displayed in a report (for example, an Item Description)
-
Structured XML
Note:
Ensure that your data does not include line feeds or carriage returns. Line feeds and carriage returns in your data may not render as expected in BI Publisher report layouts.
To create a data set from data stored as a CLOB:
- Enter the SQL query or use the Query Builder to construct your query to retrieve the CLOB data column. See Using the SQL Query Builder for information on the Query Builder utility. For example, you could create a query in which the CLOB data is stored in a column named "DESCRIPTION".
- By default, the data model editor assigns the CLOB column the "CLOB" data type. To change the data type to XML, click the data type icon and select XML.
How the Data Is Returned
When you execute the query, if the CLOB column contains well-formed XML, and you select the XML data type, the data engine returns the XML data, structured within the CLOB column tag name.
Example output when data type is XML:
Note the <DESCRIPTION> element contains the XML data stored in the CLOB column, as shown below.
Example output when data type is CLOB:
If you select to return the data as the CLOB data type, the returned data is structured as shown below.
Additional Notes on Data Sets Using CLOB Column Data
More information is available on CLOB column data.
For specific notes on using CLOB column data in a bursting query, see Adding a Bursting Definition to Your Data Model.
Handling XHTML Data Stored in a CLOB Column
BI Publisher can retrieve data stored in the form of XHTML documents stored in a database CLOB column and render the markup in the generated report.
To enable the BI Publisher report rendering engine to handle the markup tags, you must wrap the XHTML data in a CDATA section within the XML report data that is passed by the data engine.
It is recommended that you store the data in the database wrapped with the CDATA section. You can then use a simple select statement to extract the data. If the data is not wrapped in the CDATA section, then you must include in your SQL statement instructions to wrap it.
The following sections describe how to extract XHTML data in each case:
To display the markup in a report, you must use the syntax described in Rendering HTML Formatted Data in a Report in Report Designer's Guide for Oracle Business Intelligence Publisher. This section also describes the supported HTML formats. Rendering the HTML markup in a report is supported for RTF templates only.
Retrieving XHTML Data Wrapped in CDATA
This exercise assumes you have the following data stored in a database column called "CLOB_DATA".
<![CDATA[ <p><font style="font-style: italic; font-weight: bold;" size="3"> <a href="http://www.oracle.com">oracle</a></font> </p> <p><font size="6"><a href="http://docs.oracle.com/">Oracle Documentation</a> </font></p> ]]>
Retrieve the column data using a simple SQL statement, for example:
select CLOB_DATA as "RTECODE" from MYTABLE
In the data model editor, set the data type of the RTECODE column to XML, as shown below.
Wrapping the XHTML Data in CDATA in the Query
This exercise assumes you have the following data stored in a database column called "CLOB_DATA".
<p><font style="font-style: italic; font-weight: bold;" size="3"> <a href="http://www.oracle.com">oracle</a></font> </p> <p><font size="6"><a href="http://docs.oracle.com/">Oracle Documentation</a> </font></p>
Use the following syntax in your SQL query to retrieve it and wrap it in the CDATA section:
select '<![CDATA' || '['|| CLOB_DATA || ']' || ']>' as "RTECODE"from MYTABLE
In the data model editor, set the data type of the RTECODE column to XML.
Testing Data Models and Generating Sample Data
The data model editor enables you to test your data model and view the output to ensure your results are as expected.
After running a successful test, you can choose to save the test output as sample data for your data model. You can also use the Export feature to export sample data to a file. If your data model fails to run, you can view the data engine log.
To test your data model:
-
In the data model editor, select the Data tab, as shown below.
-
For SQL Query, Oracle BI Analysis, and View Object data sets: On the Data tab, select the number of rows to return. If you included parameters, enter the desired values for the test.
-
Click View to display the XML that is returned by the data model.
-
Select one of the following options to display the sample data:
-
Use Tree View to view the sample data in a data hierarchy. This is the default display option.
-
Use Table View to view the sample data in a formatted table like you see in BI Publisher reports.
You can create a report based on this data model.
-
To save the test data set as sample data for the data model:
-
After the data model has successfully run, click Save as Sample Data. The sample data is saved to the data model. See Adding Attachments to the Data Model for more information.
To export the test data:
-
For SQL Query, Oracle BI Analysis, and View Object data sets: On the Data tab, select the number of rows to return.
-
After the data model has successfully run, click Export. You are prompted to open or save the file to a local directory.
To view the data engine log:
-
Click View Data Engine Log. You are prompted to open or save the file to a local directory. The data engine log file is an XML file.
To test UCM dataset:
For Content Server, based on the document ID and the content type the document content is retrieved from the content (UCM) server.
Note:
If the Document ID is empty or null, then the document content will be empty.Including User Information Stored in System Variables in Your Report Data
BI Publisher stores information about the current user that can be accessed by your report data model.
The user information is stored in system variables as described below.
| System Variable | Description |
|---|---|
|
xdo_user_name |
User ID of the user submitting the report. For example: Administrator |
|
xdo_user_roles |
Roles assigned to the user submitting the report. For example: XMLP_ADMIN, XMLP_SCHEDULER |
|
xdo_user_report_oracle_lang |
Report language from the user's account preferences. For example: ZHS |
|
xdo_user_report_locale |
Report locale from the user's account preferences. For example: en-US |
|
xdo_user_ui_oracle_lang |
User interface language from the user's account preferences. For example: US |
|
xdo_user_ui_locale |
User interface locale from the user's account preferences. For example: en-US |
Adding the User System Variables as Elements
To add the user information to the data model, you can define the variables as parameters and then define the parameter value as an element in your data model.
You can also simply add the variables as parameters then reference the parameter values in your report.
The following query:
select :xdo_user_name as USER_ID, :xdo_user_roles as USER_ROLES, :xdo_user_report_oracle_lang as REPORT_LANGUAGE, :xdo_user_report_locale as REPORT_LOCALE, :xdo_user_ui_oracle_lang as UI_LANGUAGE, :xdo_user_ui_locale as UI_LOCALE from dual
returns the following results:
<?xml version="1.0" encoding="UTF-8"?> <! - Generated by Oracle BI Publisher - > <DATA_DS> <G_1> <USER_ROLES>XMLP_TEMPLATE_DESIGNER, XMLP_DEVELOPER, XMLP_ANALYZER_EXCEL, XMLP_ADMIN, XMLP_ANALYZER_ONLINE, XMLP_SCHEDULER </USER_ROLES> <REPORT_LANGUAGE>US</REPORT_LANGUAGE> <REPORT_LOCALE>en_US</REPORT_LOCALE> <UI_LANGUAGE>US</UI_LANGUAGE> <UI_LOCALE>en_US</UI_LOCALE> <USER_ID>administrator</USER_ID> </G_1> </DATA_DS>
Sample Use Case: Limit the Returned Data Set by User ID
The following example limits the data returned by the user ID.
select EMPLOYEES.LAST_NAME as LAST_NAME, EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER, EMPLOYEES.HIRE_DATE as HIRE_DATE, :xdo_user_name as USERID from HR.EMPLOYEES EMPLOYEES where lower(EMPLOYEES.LAST_NAME) = :xdo_user_name
Notice the use of the lower() function, the xdo_user_name is always be in lowercase format. BI Publisher does not have a USERID so you must use the user name and either use it directly in the query; or alternatively you could query against a lookup table to find a user id.
Creating Bind Variables from LDAP User Attribute Values
To bind user attribute values stored in your LDAP directory to a data query you can define the attribute names to BI Publisher to create the bind variables required.
Prerequisite
The attributes that can be used to create bind variables must be defined in the Security Configuration page by an administrator.
The attributes are defined in the Attribute Names for Data Query Bind Variables field of the LDAP Security Model definition. See Configuring BI Publisher to Use an LDAP Provider for Authentication and Authorization in Administrator's Guide for Oracle Business Intelligence Publisher. Any attribute defined for users can be used (for example: memberOf, sAMAccountName, primaryGroupID, mail).
How BI Publisher Constructs the Bind Variable
You can reference the attribute names that you enter in the Attribute Names for Data Query Bind Variables field of the LDAP Security Model definition in the query.
The following shows how bind variables are constructed:
xdo_<attribute name>
Assume that you have entered the sample attributes: memberOf, sAMAccountName, primaryGroupID, mail. These can then be used in a query as the following bind variables:
xdo_memberof xdo_SAMACCOUNTNAME xdo_primaryGroupID xdo_mail
Note that the case of the attribute is ignored; however, the "xdo_" prefix must be lowercase.
Use these in a data model as follows:
SELECT :xdo_user_name AS USER_NAME, :xdo_user_roles AS USER_ROLES, :xdo_user_ui_oracle_lang AS USER_UI_LANG, :xdo_user_report_oracle_lang AS USER_REPORT_LANG, :xdo_user_ui_locale AS USER_UI_LOCALE, :xdo_user_report_locale AS USER_REPORT_LOCALE, :xdo_SAMACCOUNTNAME AS SAMACCOUNTNAME, :xdo_memberof as MEMBER_OF, :xdo_primaryGroupID as PRIMARY_GROUP_ID, :xdo_mail as MAIL FROM DUAL
The LDAP bind variables return the values stored in the LDAP directory for the user that is logged in.