Oracle BI Publisher 11g: Working with Multiple Data Sources

Exclusive offer: get 50% off this eBook here
Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting

Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting — Save 50%

Create and deliver improved snapshots in time of your Enterprise data using Oracle BI Publisher 11g with this hands-on book and eBook guide

$29.99    $15.00
by Daniela Bozdoc | October 2011 | Oracle

Oracle BI Publisher is Oracle's reporting XML-based technology, which generates highly formatted data output using multiple data sources. The Data Model editor provides tools to build queries, define the data structure, and create formulae from different data sources.

In this article by Daniela Bozdoc, author of Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting, we'll look at how we can work with multiple data sources in the BIP.

(For more resources on Oracle 11g, see here.)

The Data Model Editor's interface deals with all the components and functionalities needed for the data model to achieve the structure you need. However, the main component is the Data Set. In order to create a data model structure in BIP, you can choose from a variety of data set types, such as:

  • SQL Query
  • MDX Query
  • Oracle BI Analysis
  • View Object
  • Web Service
  • LDAP Query
  • XML file
  • Microsoft Excel file
  • Oracle BI Discoverer
  • HTTP

Taking advantage of this variety requires multiple Data Sources of different types to be defined in the BIP. In this article, we will see:

  • How data sources are configured
  • How the data is retrieved from different data sets
  • How data set type characteristics and the links between elements influence the data model structure

Administration

Let's first see, how you can verify or configure your data sources. You must choose the Administration link found in the upper-right corner of any of the BIP interface pages, as shown in the following screenshot:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

The connection to your database can be choosen from the following connection types:

  • Java Database Connectivity (JDBC)
  • Java Naming and Directory Interface (JNDI)
  • Lightweight Directory Access Protocol (LDAP)
  • Online Analytical Processing (OLAP)

Available Data Sources

To get to your data source, BIP offers two possibilities:

  1. YOu can use a connection. In order to use a connection, these are the available connection types:
    • JDBC
    • JNDI
    • LDAP
    • OLAP
  2. You can also use a file.

In the following sections, the Data Source types&mdashJDBC, JNDI, OLAP Connections, and File&mdashwill be explained in detail.

JDBC Connection

Let's take the first example. To configure a Data Source to use JDBC, from the Administration page, choose JDBC Connection from the Data Sources types list, as shown in the following screenshot:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

You can see the requested parameters for configuring a JDBC connection in the following screenshot:

  • Data Source Name: Enter a name of your choice.
  • Driver Type: Choose a type from the list. The relating parameters are:
    • Database Driver Class: A driver, matching your database type.
    • Connection String: Information containing the computer name on which your database server is running, for example, port, database name, and so on.
  • Username: Enter a database username.
  • Password: Provide the database user's password.

The Use System User option allows you to use the operating system's credentials as your credentials. For example, in this case, your MS SQL Database Server uses Windows authentication as the only authentication method.

When you have a system administrator in-charge of these configurations, all you have to do is to find which are the available Data Sources and eventually you can check if the connection works. Click on the Test Connection button at the bottom of the page to test the connection:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

JNDI Connection

JNDI Connection pool is in fact another way to access your JDBC Data Sources. Using a connection pool increases efficiency by maintaining a cache of physical connections that can be reused, allowing multiple clients to share a small number of physical connections.

In order to configure a Data Source to use JNDI, from the Administration page, choose JNDI Connection from the Data Sources types list. The following screen will appear:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

As you can see in the preceding screenshot, on the Add Data Source page you must enter the following parameters:

  • Data Source Name: Enter a name of your choice
  • JNDI Name: This is the JNDI location for the pool set up in your application server, for example, jdbc/BIP10gSource

The users having roles included in Allowed Roles list only will be able to create reports using this Data Source.

OLAP Connection

Use the OLAP Connection to connect to OLAP databases. BI Publisher supports the following OLAP types:

  • Oracle Hyperion Essbase
  • Microsoft SQL Server 2000 Analysis Services
  • Microsoft SQL Server 2005 Analysis Services
  • SAP BW

In order to configure a connection to an OLAP database, from the Administration page, choose OLAP Connection from the Data Sources types list. The following screen will appear:

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

On the Add Data Source page, the following parameters must be entered:

  • Data Source Name: Enter a name of your choice
  • OLAP Type: Choose a type from the list
  • Connection String: Depending on the supported OLAP databases, the connection string format is as follows:
    • Oracle Hyperion Essbase

      Format: [server name]

    • Microsoft SQL Server 2000 Analysis Services

      Format: Data Source=[server];Provider=msolap;Initial Catalog=[catalog]

    • Microsoft SQL Server 2005 Analysis Services

      Format: Data Source=[server];Provider=msolap.3;Initial Catalog=[catalog]

    • SAP BW

      Format: ASHOST=[server] SYSNR=[system number] CLIENT=[client] LANG=[language]

  • Username and Password: Used for OLAP database authentication

File

Another example of a data source type is File. In order to gain access to XML or Excel files, you need a File Data Source. In order to set up this kind of Data Source, only one step is required&mdashenter the path to the Directory in which your files reside. You can see in the following screenshot that demo files Data Source points to the default BIP files directory. The file needs to be accessible from the BI Server (not on your local machine):

 

Oracle BI Publisher 11g: Working with Multiple Data Sources

 

Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting Create and deliver improved snapshots in time of your Enterprise data using Oracle BI Publisher 11g with this hands-on book and eBook guide
Published: November 2011
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

(For more resources on Oracle 11g, see here.)

Multiple Data Sources

Having a large amount of data at your disposal for creating a Data Source necessitates a great effort in creating a good structure of data with the necessary relationships between its elements.

Here are some of the tools, provided by BIP 11g Data Model Editor to design links between elements in your Data Model structure.

Add different types of Data Sets to a Data Model

You will see how the structure of the Data Model changes, after different data connections (links) are created.

As shown in the following screenshot, the Data Sets added are:

  • Independent: A HTTP type Data Set
  • trend: An Oracle BI Analysis type Data Set
  • Sales: A XML file type Data Set
  • countries: A Microsoft Excel file type Data Set
  • reader, library, and book: SQL Query type Data Sets

No links between the inserted Data Sets are created at this moment:

From the XML file and HTTP Data Sets, no metadata is available, hence you cannot create links using these types of Data Set.

Oracle BI Publisher 11g: Working with Multiple Data Sources

In the following screenshot, you can see from the Structure view that there is no hierarchy in the Data Model:

Oracle BI Publisher 11g: Working with Multiple Data Sources

Joins between Data Set objects

Using the Query Builder, you can create links between table columns as shown in the following screenshot. This kind of relationship is called a join and specifies how the rows of one table are combined with the rows of the second table. Columns of a table, view, or materialized view can be used to create links. Query Builder supports inner, outer, left, and right joins. You can find a detailed description of these operations in the Structured Query Language (SQL) documentation:

Oracle BI Publisher 11g: Working with Multiple Data Sources

In the SQL tab of the Query Designer, you can see the SQL syntax generated for the created links. Three conditions were generated in a where clause. This is shown in the following screenshot:

Oracle BI Publisher 11g: Working with Multiple Data Sources

Links between Data Sets

There are two options when creating Data Sets links. You can create an element-level or a group-level links.

 

Element-level links

An Element-level link refers to a link created between an element of a Data Set and an element of another Data Set. In this way, a parent-child relationship is created between different Data Sets.

As the following screenshot shows, by binding the READER_ID column from the G_Library group and the READER_ID_1 column from the G_Reader group, a link was created between the two mentioned groups:

Oracle BI Publisher 11g: Working with Multiple Data Sources

You can link different types of Data Sets using element-level links. For example, as you can see in the following screenshot, a link between an SQL Query Data Set and a Microsoft Excel file Data Set was created by dragging the parent field Country from the G_Reader group over the Code field in the G_Country child group:

Oracle BI Publisher 11g: Working with Multiple Data Sources

Group-level links

A group-level link also defines a link between two Data Sets to obtain a hierarchically-structured XML. But in this case, the child group must have the same Data Set type as the parent group. For example, you cannot create a group-level link between G_Reader and G_Country (from the preceding screenshot), as one is an SQL Query Data Source type and the other is an MS Excel type.

To create a group-level link, go to the group menu (upper-right corner of the group) and choose the Create Group Link option. The following screenshot shows, how available Child Groups are displayed for selection when a Group link is created:

Oracle BI Publisher 11g: Working with Multiple Data Sources

There is another request for a group-level link to work. You have to define a bind variable in the child query, as shown in the following screenshot. This variable is linked to a column from the parent Data Set.

Considering G_Book (the child group), you can see that only the Book_ID field could be used as a link between G_Library and G_Book, as Book_ID is the only field common in between these two groups:

Group-level links are provided for backward compatibility with Data Templates from earlier versions of BIP. However, element-level links are preferred.

Group-level aggregate elements

Once a parent-child link is created between two Data Sets, you can insert values obtained by applying aggregate functions on the child's elements into the parent data set. Depending on the element type, different aggregation functions are available. As you can see in the following screenshot, for a string data type element, you can choose between the following functions:

  • COUNT
  • FIRST
  • LAST

Oracle BI Publisher 11g: Working with Multiple Data Sources

Global-level functions

There is a special group in the Data Model designer: Global Level Functions, and you can add values to this group, obtained by the aggregation of elements belonging to any of the available Data Sets. For example, let's add a value called TOTAL_BOOKS to the Global Level Functions group. To obtain this value, as the following screenshot reveals, the aggregation function COUNT is used on the BOOK_ID field from the G_Book group. The same procedure is used to obtain the values: TOTAL_LOANS, TOTAL_READERS, and TOTAL_COUNTRIES.

Oracle BI Publisher 11g: Working with Multiple Data Sources

You can further refine the result and create more complicated expressions, such as the example shown in the following screenshot:

Oracle BI Publisher 11g: Working with Multiple Data Sources

Parameters

In order to add parameters to a data set, parameters must be first declared.

For example, let's add a List of Values (LOV) named countries, as shown in the following screenshot:

Oracle BI Publisher 11g: Working with Multiple Data Sources

A parameter named country is defined and it uses the LOV countries, as shown in the following screenshot:

Oracle BI Publisher 11g: Working with Multiple Data Sources

After a parameter is defined, there are many ways to use it. The advantage is more accurate and precise data for the reports using the parameterized data model.

SQL Query statements

You can use parameters in SQL Query statements. For example, by including the parameter country, as shown in the following screenshot, the readers list is narrowed to the readers from a specific country:

Oracle BI Publisher 11g: Working with Multiple Data Sources

Group filter expressions

Another option is to use parameters in group filter expressions. The result is similar to the previously described case, with the difference being that a group filter can also be used for another Data Set type than SQL type; for example, an Oracle BI Analysis Data Set (G_Trend) can be filtered using a parameter-based filter expression as you can see in the following screenshot:

Oracle BI Publisher 11g: Working with Multiple Data Sources

Data Set parameters

You can also specify parameters when you create a Data Set. In the following screenshot, you can see the interface to add parameters to a HTTP Data Set:

Oracle BI Publisher 11g: Working with Multiple Data Sources

The following screenshot shows the interface for a Microsoft Excel file data set type:

Microsoft Excel Data Set types support only one value per parameter.

As opposed to HTTP and Microsoft Excel Data Set types, for an Oracle BI Analysis Data Set type, you cannot add parameters, as you can see in the following screenshot. Parameters and lists of values will be inherited from the BI analysis and they will show up at runtime:

However, there are a few actions supported by this Data Set type:

  • Global level functions
  • Group filters
  • Setting the value for elements in case of null values

Finally, the following screenshot shows, how the Data Model structure changed to reflect the links between Data Sets and group-level filters:

Oracle BI Publisher 11g: Working with Multiple Data Sources

There are only a few cases in which Data Models with multiple Data Sources are recommended:

  • When you need to perform functions not supported by the query type
  • When the Data Model has to support complex views
  • When you want to simulate a view, in case you don't have a view or you don't want to use one

Otherwise, it is recommended that the number of Data Sets used be reduced. The reason is that single Data Set models execute faster than multiple Data Set models—in a parent-child hierarchy the child is executed for each element of the parent. However, the main advantage of a Data Model based on multiple data sets remains, and thus, a model offers a more intuitive and simple view of the data structure.

Summary

In this article, we saw how to use different tools offered by the Data Model Editor to shape a data structure composed of multiple data sets, into a compact and organized form.

For reports, the advantage of using a good structured Data Model is easy and also intuitive data access.



Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting Create and deliver improved snapshots in time of your Enterprise data using Oracle BI Publisher 11g with this hands-on book and eBook guide
Published: November 2011
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Daniela Bozdoc

Daniela Bozdoc is an IT professional with experience of almost a decade working as a software developer, analyst, data and software architect on different technologies. These range from MS .NET Framework to Sybase Power Builder, Oracle and MS SQL Server, and continuing as an ERP consultant and report developer - MS dynamics NAV, Oracle EBS, and BI Publisher respectively in the present.

She is a graduate of Babes-Bolyai University of Cluj-Napoca, Romania, with a B.D. in Computer Science.

Daniela lives in Romania, where she has full family support of her career and enjoys taking pictures of beautiful landscapes and nature catching pieces.

Books From Packt


Oracle ADF Enterprise Application Development—Made Simple
Oracle ADF Enterprise Application Development—
Made Simple

Oracle 11g R1/R2 Real Application Clusters Essentials
Oracle 11g R1/R2
Real Application Clusters Essentials

Oracle GoldenGate 11g Implementer's guide
Oracle GoldenGate 11g Implementer's guide

Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

Getting Started with Oracle BPM Suite 11gR1 – A Hands-On Tutorial
Getting Started with Oracle BPM Suite 11gR1 – A Hands-On Tutorial

Oracle Weblogic Server 11gR1 PS2: Administration Essentials
Oracle Weblogic Server 11gR1 PS2: Administration Essentials

Oracle Identity and Access Manager 11g for Administrators
Oracle Identity and Access Manager 11g for Administrators

Oracle Information Integration, Migration, and Consolidation
Oracle Information Integration, Migration, and Consolidation


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software