Introduction

Oracle Business Intelligence 11g is Oracle Corporation’s platform for delivering business
intelligence across a complete range of data sources, applications, and business processes.
It consists of a number of servers, query and analysis tools, plug-ins to popular third-party
applications such as Microsoft Office, and tools for managing business metadata.
If your organization has recently licensed Oracle Business Intelligence to provide reporting,
analysis, and dashboards across your organization—congratulations! You have made a wise
decision. However, like most enterprise-class software products, Oracle Business Intelligence
is a complex product that rewards those developers who take the time to learn how to
correctly configure, build, and then deploy solutions built using it. By purchasing this book,
you will gain a great introduction to the complexities of Oracle Business Intelligence, together
with an invaluable reference for when you hit those tricky technical issues.
This book takes you through the complete life cycle for deploying Oracle Business
Intelligence within your organization, and it is based on the 11g Release 1 (11.1.1.6) version
of the product. The assumed audience for the book is developers who will be responsible for
installing, configuring, and developing the initial business intelligence solution, who also need
to know something about the internals of the product and how it has been put together.

What You Will Learn

With this book, you will learn how to perform the following tasks:
 Install Oracle Business Intelligence 11.1.1.6 on Microsoft Windows and Linux platforms
 Create an Oracle BI Repository using data sourced from a relational data mart
 Extend the repository to include data from multiple data sources, add calculations and
drill paths, integrate detail-level and summary data, and understand more complex
topics such as table sources, federation, and fragmentation
 Import metadata into the repository from Oracle Essbase, Oracle OLAP, and Microsoft
Analysis Services databases, and integrate that data with relational and other data
sources
 Manage and maintain the Oracle BI Server component, configuring features such as
query caching, write back, usage tracking, and aggregate persistence
 Create analyses and dashboards, together with prompts and other dashboard objects
 Define key performance indicators and use them to create interactive scorecards
 Create production-quality reports and distribute them in PDF and other formats
 Create alerts and send them via e-mail, pager, or other delivery channels
 Embed business intelligence in your applications and business processes
 Define highly available systems with resilience, failover, and clustering
 Add row- and subject-level security to your reports, and integrate with external identity
management systems such as Microsoft Active Directory
  Use Enterprise Manager to administer your BI infrastructure and learn how to script
systems management tasks using JMX MBeans and the WebLogic Scripting Tool
(WLST)
 Configure Oracle Exalytics In-Memory Machine after initial installation, and use the
Summary Advisor to create in-memory aggregates to provide “speed-of-thought”
analysis

What’s in the Book

This book contains twelve chapters, starts with a product overview and installation steps, then
covers topics from creating repositories through to deploying dashboards and making them
secure. Each chapter contains detailed background and explanations for the concepts we are
discussing, and has worked examples that you can try out yourself to practice key
development techniques. This book is the product of three years of research, testing, and
writing, and I hope covers the most important techniques you’ll need to master to get the best
out of Oracle Business Intelligence 11g.

Chapter 1: “Oracle Business Intelligence Overview and Architecture”

In this chapter, you’ll learn about the capabilities and features within Oracle Business
Intelligence, how the product is built on Oracle Fusion Middleware, and how components
such as the Oracle BI Server, Oracle BI Presentation Server, and Oracle BI Scheduler work to
deliver dashboards and reports to your users.

Chapter 2: “Installing and Upgrading Oracle Business Intelligence”

You’ll explore the various installation scenarios for Oracle Business Intelligence and look at
how metadata and dashboards from earlier releases can be upgraded to the 11g release.
You’ll also see how Oracle Business Intelligence can be extended and clustered over multiple
servers, and how you can configure it to provide failover should a component fail.

Chapter 3: “Modeling Repositories Using Relational, File, and XML

Sources”
In this chapter, you’ll use the Oracle BI Administration tool to create a dimensional, semantic
model over your data sources, starting with a simple Oracle Database data mart as your
source and then extending it through vertical and horizontal federation techniques to cover
additional sources of data. We will also look at creating calculations in the repository and at
tools available to help you manage and work with the repository.

Chapter 4: “Creating Repositories from Oracle Essbase and Other OLAP

Data Sources”
In this chapter, you’ll learn how Oracle Essbase, Oracle OLAP, and Microsoft Analysis
Services metadata can be imported into the repository, as well as how you can make best use
of these multidimensional data sources, including combining them with relational tables and
columns.
Chapter 5: “Configuring and Maintaining the Oracle BI Server”
You’ll learn how the Oracle BI Server component works and how you can work with features
such as query caching, usage tracking, write back, and aggregate persistence.

Chapter 6: “Creating Analyses, Dashboards, KPIs and Scorecards”

You’ll learn how to create ad-hoc analyses and display the results in the form of graphs,
tables, pivot tables, and maps. You’ll see how these analyses and other BI content can be
added to interactive dashboards and then made interactive through prompts, sliders, and
other controls. You’ll also see how key performance indicators (KPIs) can be defined and how
they can be used to create scorecards to help analyze the overall performance of your
organization or department.

Chapter 7: “Actionable Intelligence”

In this chapter, you’ll learn how to create actions, agents, and conditions, and use them to
create proactive alerts that deliver information to relevant users when business events occur.
You’ll also see how Oracle Business Intelligence can integrate with business applications and
business processes through Oracle Fusion Middleware using a feature called the Action
Framework.

Chapter 8: “Security”
In this chapter, you’ll learn about the security capabilities of Oracle Business Intelligence, how
to create users, groups, and application roles, how to use them to apply catalog content and
row-level filtering, and how to integrate with external directories such as Microsoft Active
Directory.

Chapter 9: “Creating Published Reports”

You’ll learn how to create pixel-perfect published reports using Oracle BI Publisher and how to
distribute them via e-mail or other channels, or embed them in a dashboard.

Chapter 10: “Systems Management”

In this chapter, you’ll learn how Oracle Enterprise Manager is used to provide systems
management for Oracle Business Intelligence, how the various components stop and start,
and how you can script systems management tasks using the WebLogic Scripting Tool
(WLST).

Chapter 11: “Managing Change”

You’ll learn about the various ways that teams can develop solutions using Oracle Business
Intelligence and how project metadata is promoted through development, testing, and
production environments. You’ll see how version control works with the Oracle BI Repository
and how large teams of developers can work collaboratively using the Multiuser Development
Environment (MUDE).
Chapter 12: “Oracle Exalytics In-Memory Machine”
In this final chapter, you’ll get a preview of the new Oracle Exalytics BI Machine, an
engineered system from Oracle Corporation that combines optimized versions of Oracle
Business Intelligence, Oracle Essbase, and Oracle TimesTen to deliver “speed-of-thought”
analysis to users.

Intended Audience
This book is suitable for the following readers:
Developers who need to create repositories against relational, OLAP, and other data
sources, integrate BI with business processes and applications, and support business
users creating analyses, dashboards, and reports
Administrators and support staff who need to understand the systems management
features of Oracle Business Intelligence, clustering and high-availability options, and
how to support deployments into production
Business users who need to create ad-hoc analyses, dashboards, reports, KPIs,
scorecards, and other BI content
Technical managers or consultants who need an overview of the capabilities and
architecture of Oracle Business Intelligence
No prior knowledge of Oracle Business Intelligence is required for this book, but a basic
understanding of databases, SQL, reporting, and analysis is assumed.

About the Examples, and Retrieving the Sample Data Set

The examples used in this book are based around a fictitious coffee shop and bakery store
company called “Gourmet Coffee and Bakery Company” (GCBC), based in the United States,
primarily in California and the Bay Area. In the examples and over the various chapters, we
build up a repository, sourcing data from several database schemas, and go on to create
dashboards, reports, scorecards, and other BI objects using this data.
All the SQL scripts, programs and other files used in this book can be downloaded from the
Oracle Press web site, at www.OraclePressBooks.com, and require Oracle Database
11g Release 2. (Any edition, including the free XE edition, should be suitable.)
The files are contained in a Zip file. Once you’ve downloaded the Zip file, all files can be
found in a folder called “Sample Data,” together with a Microsoft Windows batch file that
installs the data for you into your database. Note that you will need to have access to the SYS
account’s password (or amend the batch file to reference another account with DBA
privileges) to install data in this way, as the script creates database accounts for various sets
of data.
To install the sample data on an Oracle Database 11gR2 database, follow these steps:
1.Obtain and unzip the sample data to a suitable directory on your workstation that also
has the Oracle database installed (for example c:\Sample_Data).
(If you are installing the sample data on a server remote from your workstation and
would normally connect to the database using a TNSNAMES connection string, edit
 the GCBC_Setup.bat file to add your database connection string when calling
sql*plus.)
2.Open a command-line prompt and run the following two commands to install the
sample data, substituting the password for your SYS account for dba_password:
cd c:\Sample_Data
GCBC_Setup.bat dba_password
3.The sample data should then be installed for you, automatically.
I hope you enjoy this book!

Chapter 1: Oracle Business Intelligence Overview and

Architecture
Oracle Business Intelligence 11g Release 1 is a platform for delivering business intelligence
across a wide range of data sources and to a wide range of audiences within the enterprise.
You can consider it a “toolkit” in that, in itself, it does not come with any prebuilt reports, data,
or other content, although as we will see later in this chapter, you can license content from
Oracle Corporation and other providers that can be used with Oracle Business Intelligence.

Introducing Oracle Business Intelligence

As an end user, your first encounter with Oracle Business Intelligence would be when logging
in to, and interacting with, a web-based dashboard such as that shown in Figure 1-1. Oracle
Business Intelligence dashboards are made up of pages of analyses, displayed as tables,
pivot tables, charts, gauges, or other views using data from potentially many sources. These
can be interacted with, allowing the end user to, for example, start with a set of summarized
figures and then progressively drill into more detail. Oracle Business Intelligence dashboards
are typically highly graphical and provide a familiar, point-and-click environment for users to
explore their data.
As well as providing an ad-hoc query environment suitable for data exploration, Oracle
Business Intelligence also comes with tools for publishing reports in formats such as Adobe
PDF and distributing them to large numbers of recipients through technologies such as e-
mail. Analyses created using Oracle Business Intelligence can also be accessed through
collaboration and office products such as Microsoft Outlook, Microsoft Word, Microsoft
PowerPoint, and Microsoft Excel, or can be embedded directly into applications such as
Oracle E-Business Suite, Siebel Customer Relationship Management (CRM), or in the new
Fusion Applications from Oracle Corporation.
The 11.1.1.6 release of Oracle Business Intelligence 11g, on which the examples in this book
are based, introduces new capabilities and visualization options, including the ability to create
scorecards and key performance indicators, display data in the form of maps, and integrate
with applications and business processes through a feature called the Action Framework. We
will look at these capabilities in more detail in later chapters of this book.

The Development Toolkit

As developers, you have a number of tools within Oracle Business Intelligence that you can
use to develop business intelligence solutions. The main tool that you will use is the Oracle
Business Intelligence Administration tool, a Microsoft Windows–based tool that is used to
define and then maintain the business metadata layer known as the Oracle BI Repository,
used for creating reports and analyses. Several chapters of this book are devoted to this tool,
and as a developer you will need to understand in detail the functionality of this tool and the
Oracle BI Repository.
Figure 1-3 shows the Oracle Business Intelligence Administration tool, with a repository open
for editing. The tool is also used for managing connection details through to data sources,
defining security policies that control users’ access to data, and performing a number of
administration tasks such as defining variables, managing caching, and checking the status of
the cluster.

From the 11g release of Oracle Business Intelligence, a number of systems administration

tasks previously carried out using the Oracle Business Intelligence Administration tool are
now performed using Oracle Enterprise Manager Fusion Middleware Control. These tasks
include enabling and disabling caching, setting the cache size, and managing the status of
the system components in a cluster.
Other tools for developers provided as part of Oracle Business Intelligence 11g Release 1
include the Catalog Manager, a Java application used for managing the catalog of reports,
dashboards, and other business intelligence objects; Oracle Enterprise Manager Fusion
Middleware Control, for administering the business intelligence platform; and Oracle
WebLogic Server Administration Console, for controlling the functionality of the WebLogic
Server application server.
Reports, analyses, and dashboards are created using web-based authoring tools that do not
require any separate software to be installed on developers’ desktops. Oracle Business
Intelligence 11g Release 1 brings together all report-authoring tools into a single integrated
environment using the same semantic model, and later chapters in this book will show you
how easy it is to create compelling, interactive dashboards using these tools.
Platform Support
At the time of writing this book, Oracle Business Intelligence is at release 11.1.1.6 and can be
installed on a number of Microsoft Windows, Linux, and Unix platforms, running on both 32-bit
and 64-bit processors. Because the report and dashboard-authoring tools within Oracle
Business Intelligence are mostly web-based, any operating system that supports these web
browsers can be used to create reports. There are separate certifications for server and client
tools within Oracle Business Intelligence, and you should refer to the Oracle Technology
Network web site, and in particular the System Requirements and Supported Platforms for
Oracle Business Intelligence Suite Enterprise Edition 11gR1 document, to obtain the latest list
of supported platforms and operating systems.
Note The list of certified operating systems and platforms can change from release to
release, and you should check the System Requirements and Supported Platforms for
Oracle Business Intelligence Suite Enterprise Edition 11gR1 document, available on the
Oracle Technology Network web site (http://otn.oracle.com), for your particular version of
Oracle Business Intelligence.

How Does Oracle Business Intelligence Work?

So you now know that Oracle Business Intelligence comes with a number of end-user tools
for developing and viewing reports, together with developer tools for administration, creating
the semantic model, and maintaining the system. But how does Oracle Business Intelligence
work, how does it access your various data sources, and what use does it make of other
business intelligence systems such as data warehouses, online analytical processing (OLAP)
servers such as Oracle Essbase, or data that might be of interest in your applications or
company databases?
At a high level, Oracle Business Intelligence uses a four-tier architecture that provides access
to your data through two main servers and a semantic model. Figure 1-4 shows a high-level
schematic for Oracle Business Intelligence, with your data being accessed through two
servers—the Oracle Business Intelligence (BI) Server and the Oracle BI Presentation Server
—before it is presented to end users through a web browser.

Considering this four-tier architecture from the perspective of an end user requesting a
dashboard of business information, the components within Oracle Business Intelligence
perform the following high-level functions to return data to the user:
1.The web browser requests a dashboard of data, consisting of analyses, published
reports, and other BI content.
2.This request is received by the Oracle BI Presentation Server, which translates
requests for individual analyses and reports into logical SQL queries. These logical
queries are then passed to the Oracle BI Server.
3.The Oracle BI Server takes these logical SQL queries, written against the semantic
model contained in the Oracle BI Repository, and translates them into native SQL and
MDX queries that are then sent to the underlying data sources.
4.The underlying data sources process the native SQL and MDX queries, and return
results to the Oracle BI Server.
 5.The Oracle BI Server returns a data result set to the Oracle BI Presentation Server.
In instances where more than one data source is needed to satisfy the query, the BI
Server is capable of combining multiple data sets into a single set of results.
6.Finally, the Oracle BI Presentation Server presents the results back to the end user,
in the form of analyses, published reports, dashboards, and other BI content.
Unlike many other business intelligence tools that combine data presentation with query
generation in a single server, Oracle Business Intelligence splits these functions into two
separate servers:
Oracle BI Server This server provides simultaneous connectivity to heterogeneous
data sources, a calculation and data federation engine, access to a semantic
(metadata) model, and a security layer.
Oracle BI Presentation Server This server connects to the Oracle BI Server and
provides users with a catalog of analyses, reports, and dashboards that they can use
to analyze their data.
Oracle Business Intelligence 11g actually makes use of other servers to handle clustering,
scheduling of reports, and other services; but, for now, consider these two servers the “core”
of the Oracle Business Intelligence’s functionality.

So Where Does the Data Come From?

As you will have seen from the above schematic, Oracle Business Intelligence does not itself
hold data; instead, it uses a metadata layer to create a “virtual dimensional model” over one
or more data sources and then generates SQL and MDX queries to retrieve data, on demand,
from these data sources for presentation back to the user. As such, it leverages any
investment you have made in data warehouse technology such as Oracle Database
Enterprise Edition, or in OLAP technology such as Oracle Essbase, rather than replacing the
need for them.
While Oracle Business Intelligence can optionally be configured to hold a cache of data to
enable faster display of results, queries that it generates are otherwise sent directly to your
underlying data sources. It follows, therefore, that your underlying data sources should be as
optimized for queries as possible. Furthermore, the Oracle BI Server can also take advantage
of any analytic functionality that is available on a particular data source to enable more
efficient processing by “passing down” calculations to the underlying data source. Therefore,
the recommended, optimal data source for Oracle Business Intelligence would be an
enterprise data warehouse running on a database platform such as Oracle Database
Enterprise Edition, potentially supplemented or enhanced by an OLAP server such as Oracle
Essbase or the OLAP Option for the Oracle Database Enterprise Edition, to provide fast
access to aggregated data.
However, Oracle Business Intelligence also has the ability to connect to more than one data
source and “join together” results from each one into a single data set, giving you the ability to
create “virtual” data warehouses made up of data taken in real time from separate databases,
which can be departmental data warehouses, data marts, or even online transaction
processing (OLTP) databases or nonrelational database sources such as OLAP servers, files,
or sources such as Microsoft Excel spreadsheets. This ability to work with “federated” data
sources gives you great flexibility in how you design your reporting system, allowing you to,
for example, source the majority of your reporting data from a data warehouse but
supplement it with data sourced in real time from a range of applications, file sources, and
OLAP servers, as shown in Figure 1-5.
This approach becomes possible due to two key features provided by the Oracle BI Server:
The semantic model, which can create a metadata model over multiple data sources
from different vendors, presenting users with a single, unified view over their data
regardless of the data source
The ability of the BI Server to generate native, optimized queries for each data source
and to combine the results returned into a single result set
So, it’s clear that the BI Server and the semantic model that it uses are key to how Oracle
Business Intelligence provides access to data. With this in mind, let’s take a look at how the
semantic model works and how it structures data so that it is optimized for querying.

The Oracle Business Intelligence Semantic Model

The Oracle Business Intelligence semantic model has three main objectives:
 To represent your enterprise’s data as a logical dimensional model
 To map this logical dimensional model onto the data sources used by the enterprise
 To provide personalized views over the logical dimensional model for subsets of users
giving them access to just the data they need while preserving, “under the covers,” a
single unified business intelligence data model
Semantic models in the Oracle Business Intelligence repository therefore have three distinct
layers:
 Physical layer This layer contains metadata on the physical databases and other data
sources that provide data for the semantic model.
 Business Model and Mapping layer This layer contains the logical dimensional
model defined for the business.
 Presentation layer This layer provides personalized subsets of the logical dimensional
model tailored for different audiences.
Data flows through the semantic model, as shown in Figure 1-6, from the Physical layer,
through mappings into the Business Model and Mapping layer, and is eventually accessed by
endusers through the Presentation layer. The semantic model is defined and maintained
using the Oracle Business Intelligence Administration tool and stored in the Oracle BI
Repository, andChapters 3 and 4 show you how semantic models can be created that access
data from relational, OLAP, files, and other nonrelational sources.
Packaged Business Intelligence Solutions
The fact that Oracle Business Intelligence is, in effect, a toolkit that does not in itself provide
any data or reports that you can use “out of the box” means that, realistically, you will need to
spend a significant amount of time developing a solution before your users can start analyzing
their data. In addition, because many organizations have standardized on packaged
enterprise resource planning (ERP) systems such as Oracle E-Business Suite and
PeopleSoft Enterprise or customer relationship management (CRM) suites such as Siebel
CRM, the work you would be doing might essentially be “reinventing the wheel,” as many
organizations would have had requirements similar to yours in the past and created similar
business intelligence solutions to deliver similar dashboards and reports.
Oracle Corporation, as well as third-party vendors such as Noetix, have addressed this
opportunity by developing packaged sets of dashboards, data models, and data extraction
routines that you can install, along with Oracle Business Intelligence, to provide dashboards
and reports within days or weeks rather than the usual months that are required to create a
custom solution. The Oracle Business Intelligence Applications, from Oracle Corporation, are
a suite of packaged BI products built around Oracle Business Intelligence that provides
applications such as the following:
Financial Analytics
Human Resources Analytics
Project Analytics
Procurement Analytics
 Supply Chain Analytics
In addition to these, it provides other, industry-specific “vertical” packaged applications for the
financial services, pharmaceuticals, and other industries. Figure 1-7 shows the relationship
between Oracle Business Intelligence Applications and Oracle Business Intelligence, and how
data for this combined system is either accessed directly from application and database data
sources, or through a prebuilt data warehouse fed by predefined data extraction routines.

In addition to these, it provides other, industry-specific “vertical” packaged applications for the
financial services, pharmaceuticals, and other industries. Figure 1-7 shows the relationship
between Oracle Business Intelligence Applications and Oracle Business Intelligence, and how
data for this combined system is either accessed directly from application and database data
sources, or through a prebuilt data warehouse fed by predefined data extraction routines.

Oracle Exalytics In-Memory Machine

For customers looking for “speed-of-thought” analysis of very large sets of detail-level data,
Oracle Exalytics In-Memory Machine is a combination of hardware and Oracle Business
Intelligence software that uses an in-memory database cache and special management tools
to manage the cache.
Figure 1-8 shows the architecture for Oracle Exalytics In-Memory Machine, which comes with
Oracle Business Intelligence and Oracle Essbase installed on the Exalytics hardware device,
along with Oracle TimesTen for Exalytics as the in-memory database. Exalytics is typically
used by those customers looking to interactively analyze large volumes of data in a very
graphically rich environment and is sold as an “appliance” that you can add to your data
center and connect, via InfiniBand to Oracle Exadata or via Ethernet, to your corporate
databases and data warehouses.
What Does Oracle Business Intelligence “Not Do”?
When considering a new software tool, it’s worth understanding also what it does “not do.”
While Oracle Business Intelligence is a suite of products that provides a wide range of
analysis tools, data source adapters, and—with the Oracle Business Intelligence Applications
—prebuilt content for a wide range of business applications, it is worth understanding what it
is not:
It is not a replacement for Microsoft Excel. Microsoft Excel provides a free-form
environment for analyzing and reporting on data, is programmable, and places few
restrictions on how data is presented, input, analyzed, and distributed. Oracle Business
Intelligence, in contrast, provides a structured analysis environment based around a
dimensional model, primarily providing analytical views of data through a web-based
dashboard. Individual analyses can, however, be imported into Microsoft Excel, either
through an export from the dashboard or through a plug-in to Microsoft Excel, and the
BI Server can also act as an ODBC data source for Excel (and other clients).
Because it uses a logical dimensional model for presenting data to users, it is not
generally suitable for querying unstructured data, being better suited to reporting on
transactional, data warehouse, and OLAP data sources.
While it can report against the transactional databases used by applications such as
Oracle E-Business Suite, due to the complexity and existing workload on these
databases, repositories created directly against them are generally not recommended;
instead, data from the transactional databases used by these applications is generally
extracted into a data warehouse, or OLAP cube, and then queried from that location by
Oracle Business Intelligence.
Although it can access data in Oracle Essbase or other OLAP servers, Oracle
Business Intelligence is not itself a multidimensional OLAP server; instead, it could be
thought of as a relational OLAP (ROLAP) server, providing access to a dimensional
model through (virtual) relational data structures, themselves mapped to either
relational, file or multidimensional physical data structures.

A History of Oracle Business Intelligence

There have been several “business intelligence” products released by Oracle Corporation
over the years, and you may have come to this book planning an upgrade from a previous
generation of tools such as Oracle Discoverer or Oracle Reports. These tools were developed
in-house by Oracle Corporation’s developers and were designed to work primarily with
Oracle’s own database, application server, and security products. As Oracle Corporation
moved from being solely focused on database technologies and started to make acquisitions
in the middleware, applications, and infrastructure industry sectors, it became clear that it
needed a “next-generation” business intelligence platform that did not have such a
dependency on Oracle database technologies but that could still take advantage of them if, as
is often the case, the customer had used Oracle technology for their data warehouse.
The acquisition of Siebel CRM Systems, Inc., though primarily for their Siebel CRM platform
and their extensive customer base, presented Oracle with an opportunity to update their
business intelligence platform through another, lesser-known product that Siebel offered;
Siebel Analytics. Though well regarded in the industry, Siebel Analytics was not as well known
as similar products from vendors such as Business Objects (now part of SAP) and Cognos
(now part of IBM), but the Siebel Analytics platform met many of Oracle Corporation’s
requirements for a next-generation business intelligence platform in that it could access data
from many different data sources, had an industry-leading metadata layer (the “semantic
model”), very user-friendly dashboards and reporting tools, and also came ready-integrated
with popular ERP and CRM systems such as SAP, Oracle E-Business Suite, PeopleSoft—
and, of course, Siebel CRM—in a package called Siebel Business Analytics.
Oracle announced in 2005 that what was previously called Siebel Analytics would now be
adopted by Oracle Corporation as their strategic business intelligence platform, and they
renamed it Oracle Business Intelligence Enterprise Edition. The existing Oracle Discoverer
and Oracle Reports tools would be packaged as Oracle Business Intelligence Standard
Edition, and while customers would not be forced to upgrade from the older toolset to the new
one, in time upgrade tools and services would be made available to make this process easier
for those customers who chose to do so.
Siebel Analytics was itself, though, developed outside of Siebel CRM Systems, Inc., and was
in fact originally developed by a technology startup out of Minneapolis, MN, called nQuire.
Led by Larry Barbetta and a number of ex-Platinum Software engineers and product
managers, nQuire released the nQuire Server, the predecessor to what eventually became
the Oracle BI Server, back in the late 1990’s as a stand-alone analytics and search server
that featured connectivity to a wide range of data sources. The nQuire Query Server featured
a metadata model (which eventually became the semantic model) that provided a virtual
logical dimensional model over these data sources, and over time the nQuire Query Server
was joined by nQuire Answers and nQuire Delivers, giving us the core of what is now Oracle
Business Intelligence. nQuire was itself acquired by Siebel CRM Systems, Inc., in October
2001, and it developed the product further and licensed data models and data extraction
routines from Informatica Corporation that now form the core of the Oracle Business
Intelligence Applications. So while what you know as Oracle Business Intelligence may be a
product that is only a few years old, the core of the product itself can be traced back to
groundbreaking work done by the nQuire team back in the mid-1990’s.

Oracle Product Release History

Shortly after Oracle Corporation acquired Siebel Systems, what was Siebel Analytics was
renamed Oracle Business Intelligence Enterprise Edition, whilst Siebel Business Analytics
was renamed Oracle BI Applications. The initial release of Oracle Business Intelligence was
the 10g 10.1.3.2 version, with subsequent major releases of Oracle Business within the
10g timeline:
 Oracle Business Intelligence 10.1.3.2 First “Oracle-branded” release of Oracle
Business Intelligence; introduced Oracle BI Publisher as a replacement for Actuate, a
new Oracle “look and feel” with 64-bit support; time-series functions and features for
multiuser development.
 Oracle Business Intelligence 10.1.3.3.x MS Office integration, support for metadata
import through the Oracle Call Interface; support for embedded database functions;
initial support for Oracle Essbase as a data source.
 Oracle Business Intelligence 10.1.3.4.x Integration with Hyperion Workspace;
integration with Oracle Smart View and Oracle Smart Space; the introduction of a utility
to upgrade Oracle Discoverer End-User Layers to Oracle Business Intelligence
repositories.
In addition, at the time of writing this book there have been three major releases as part of the
11g Release 1 timeline:
 Oracle Business Intelligence 11.1.1.3 Initial 11g release, provided new “look and
feel,” support for KPIs and scorecards, the Action Framework, and other new features.
Platform support limited to Microsoft Windows, Linux, and IBM AIX, with Oracle
WebLogic Server as the sole JEE (Java Platform, Enterprise Edition) application
server.
 Oracle Business Intelligence 11.1.1.5 Oracle Extension of platform support to HP/UX
and Sun Solaris, introduction of iOS (Apple iPhone, Apple iPad) native clients, and
restoration of data sources temporarily desupported in the 11.1.1.3 release.
 Oracle Business Intelligence 11.1.1.6 Support for the Exalytics In-Memory Machine
platform and integration with version control tools. New visualization options and new
certified data sources, including Oracle TimesTen.
In addition, there are three editions of Oracle Business Intelligence, the first two of which in
the following list are within the scope of this book, and the third that is not:
 Oracle Business Intelligence Enterprise Edition The full set of business intelligence
tools and servers. This book will concern itself primarily with this edition.
 Oracle Business Intelligence Standard Edition One “Departmental” or budget
version of Oracle Business Intelligence that comes with certain restrictions on, for
example, the number of allowable users and CPUs. Check with your Oracle
representative or http://www.oracle.com for up-to-date details on this and other product
packages.
 Oracle Business Intelligence Standard Edition Somewhat confusingly, a different
family of products altogether. This is a container for “legacy” Oracle Business
Intelligence tools such as Oracle Discoverer and Oracle Reports. You can use upgrade
tools to migrate Discoverer metadata to Oracle Business Intelligence Enterprise Edition
and Standard Edition One; but, otherwise, this edition is outside the scope of this book.
Now that you know a little more about Oracle Business Intelligence’s background and a little
of its history, let’s take a look in more detail at the individual products within the platform, its
architecture, and how it works “under the covers.”

Chapter 2: Installation and Upgrading Oracle Business

Intelligence
The first chapter of this book provided an overview of Oracle Business Intelligence and took
you through the architecture of the product platform. In this chapter, we will look at how you
install Oracle Business Intelligence into Microsoft Windows, Linux, and other platforms, and
how you upgrade existing installations of both the 10g and 11g versions of the product up to
the latest release. Before we start the installation or upgrade, though, let’s take a quick look at
what preparations you need to make for your environment.

Preparing for the Installation

Oracle Business Intelligence 11g has a number of hardware and software prerequisites,
including software that you need to install into your environment before you start the
installation. As this list of supported hardware and operating system software changes from
release to release, you should check the System Requirements and Supported Platforms for
Oracle Business Intelligence Suite Enterprise Edition 11gR1 document available on the
Oracle Technology Network web site for the latest certifications, at the time of writing found
athttp://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html and
located within the “Oracle Business Intelligence” section.
In addition to ensuring that you have adequate hardware and the correct operating system
version installed, there are a number of Microsoft Windows and Linux- or Unix-specific
preinstallation tasks that you will need to carry out, and you will also need to ensure that a
suitable database server is available to host the supporting database schemas.
Note that in subsequent chapters of this book we refer to where you have installed Oracle
Business Intelligence on your server’s file system as [middleware_home]; for example:
[middleware_home]\Oracle_BI1
This would equate to the following file system location on a Microsoft Windows–based system
if you had installed Oracle Business Intelligence to c:\Middleware:
c:\Middleware\Oracle_BI1
Note also that, for Microsoft Windows Server environments, the configuration of the desktop
Start menu can change depending on how you have configured the Start menu properties.
For all examples in this and later chapters, it is assumed that the Windows Server 2003
classic Start menu has been chosen for your system. (Right-click the Start menu on your
desktop taskbar, and then select Properties. In the Taskbar And Start Menu Properties dialog
box, select the Start Menu tab and then choose Classic Start Menu.) If you are using the
default, “new-style” Start menu, you may have to alter some of the walkthrough instructions to
reference the correct menu item location in your Start menu.

Microsoft Windows–Specific Preinstallation Tasks

Before you install Oracle Business Intelligence into a Microsoft Windows environment, if the
server does not have a permanent, static IP address but rather an IP address provided
automatically through a DHCP server, you should install the Microsoft Loopback Adapter and
configure that to have a permanent static IP address instead. If you are in this situation and
do not have the loopback adapter configured for your environment, the installation may fail or
you may subsequently encounter connectivity problems among the business intelligence
components.
Most problems encountered when installing Oracle Business Intelligence 11g on Microsoft
Windows can be traced back to not having installed or properly configured the loopback
adapter, and you should therefore ensure that the following steps are carried out before you
start the installation process.

Installing the Microsoft Loopback Adapter with Microsoft Windows Server 2003
To install the Microsoft Loopback Adapter using either the 32- or 64-bit version of Microsoft
Windows Server 2003, follow these steps, which assume that you have one other network
adapter already installed that connects you to your corporate network or the Internet (or
adjust the network adapter naming in the steps if this is not the case):
1. Log into your Microsoft Windows Server 2003 using an account with administrator
privileges.
2. Select Start to bring up the Windows Start menu; then select Settings | Control Panel.
 3. When the Control Panel window is displayed, double-click the Add Hardware icon to
display the Add Hardware Wizard.
4. Click Next to progress past the opening screen in the wizard, and when asked “Is the
hardware connected?” select the radio button for “Yes, I have already connected the
hardware.”
5. The wizard will then present you with a list of already installed hardware. Scroll to the
bottom of the list, select Add A New Hardware Device, and click Next.
6. On the next screen of the wizard, select the “Install the hardware that I manually select
from a list (Advanced)” radio button, and then click Next.
7. From the list of common hardware types displayed on the next screen of the wizard,
select Network Adapters from the list and click Next.
8. On the Select Network Adapter screen, select Microsoft in the Manufacturer list and
Microsoft Loopback Adapter in the Network Adapter list; then click Next.
Click Next and then Finish to compete the installation.
Now that the loopback adapter is installed, you need to give it a static IP address. To do this,
follow these steps:
1.Select Start to bring up the Windows Start menu; then select Settings | Control
Panel.
2.From the list of icons in the Control Panel window, select Network Connections.
3.Within the Network Connections window, there should be a new network adapter
listed called Local Area Connection 2. (The number may differ if you have more than
one physical network adapter installed.) This network adapter will be labeled as using
the Microsoft Loopback Adapter, and there should be a message stating that it has
limited or no connectivity.
4.Double-click this new network adapter icon, and when the Local Area Network
Connection 2 Status dialog box is shown, click the Properties button.
5.With the Properties dialog box displayed, ensure that the General tab is selected and
then double-click the Internet Protocol (TCP/IP) entry, so that you can set the static IP
address for the network adapter.
6.With the Internet Protocol (TCP/IP) Properties dialog box shown, select the “Use the
following IP address” radio button and enter the following details:
IP address: 10.10.10.10 
Subnet mask: 255.255.255.0
Leave all other values blank, or at their default value.

Click OK and then OK again to close the various dialog boxes. When you view the Local Area
Connection 2 icon within the Network Connections window now, you should see that its status
has changed to Connected.

Installing the Microsoft Loopback Adapter with Microsoft Windows Server 2008
To install the Microsoft Loopback Adapter using the 32- or 64-bit version of Microsoft Windows
Server 2008, follow these steps, which assume that you have one other network adapter
already installed that connects you to your corporate network or the Internet (or adjust the
network adapter naming in the steps if this is not the case):
1. Log into Microsoft Windows Server 2008 using an administrator account.
 2. Select Start to bring up the Windows Start menu, and in the search box at the bottom
of the screen, type Device Manager.
3. When the Device Manager dialog box is shown, right-click the machine name at the top
of the device list and select Add Legacy Hardware.

The Add Hardware Wizard will then be displayed. Click Next, select the “Install the hardware
that I manually select from a list (Advanced)” radio button on the next screen, and then click
Next to proceed.
1.Click Next to have the wizard search for new hardware, and then click Next when it
does not find the loopback adapter. When the list of common hardware types is then
shown on the subsequent screen, select Network Adapters and click Next.
2.At the Select Network Adapter screen, select Microsoft in the Manufacturer list and
Microsoft Loopback Adapter in the Network Adapter list.

Click Next and then Finish to complete the installation.

Now that the loopback adapter is installed, you need to give it a static IP address. To do this,
follow these steps:
1.Select Start to bring up the Windows Start menu, and in the search box at the bottom
of the menu, type Network and Sharing Center.
2.With the Network And Sharing Center dialog box open, locate and click the link for
your new network adapter. (It usually will be called Local Area Network Connection 2
and will appear within the View Your Active Networks section.)

The Local Area Network Connection 2 Status dialog box will then be displayed. Click the
Properties button at the bottom of the dialog box to display the Properties dialog box, and
then, with the Networking tab selected, double-click the Internet Protocol Version 4
(TCP/IPv4) entry.
1.In the Internet Protocol Version 4 (TCP/IPv4) Properties dialog box that appears,
select the “Use the following IP address” radio button and enter the following details:
IP address: 10.10.10.10 
Subnet mask: 255.255.255.0
Leave the remaining entries in the dialog box blank, or at their default value.

1. Click OK, and OK again; then click Close to close the various dialog boxes.

Linux- and Unix-Specific Preinstallation Tasks

When installing Oracle Business Intelligence into a Linux or Unix environment, there are a
number of preinstallation tasks that you should carry out to ensure a successful installation.
The tasks outlined in this section are specific to Oracle Linux 5, so you should check the
product documentation for any steps specific to other Linux or Unix versions.

Required Packages and Operating System Changes

As with other Oracle products that can be installed on Linux and Unix platforms, Oracle
Business Intelligence has specific requirements for packages and other operating system
changes that need to be applied prior to the installation. As these packages and changes can
vary from operating system to operating system, and version to version, you should check
the System Requirements and Supported Platforms for Oracle Business Intelligence Suite
Enterprise Edition 11gR1 document available as part of the product documentation on the
Oracle Technology Network web site before performing the installation. As a guideline,
though, if you have configured your operating system such that you can install the
11g release of Oracle Database Server or Client, you will normally be able to install Oracle
Business Intelligence without any further configuration; if not, the Universal Installer will warn
you or stop you from performing the installation if your host environment does not meet the
installation prerequisites.

Network Settings
Unlike Microsoft Windows–based installations, you do not need to install a separate loopback
adapter before installing Oracle Business Intelligence. Instead, using a text editor, edit the
/etc/ hosts file so that it contains the following entries (substituting your own host name for
obisrvlnx64, as appropriate), which then ensure that requests made to your machine’s host
name are directed to the pre-installed loopback adapter:

127.0.0.1 obisrvlnx64
127.0.0.1 localhost.localdomain localhost

To check that your host name now resolves back to the loopback IP address, use the
/bin/ping utility to check connectivity. The results of this check should look like this:

[root@obisrvlnx64 ~]# ping obisrvlnx64

PING obisrvlnx64 (127.0.0.1) 56(84) bytes of data.
64 bytes from obisrvlnx64 (127.0.0.1): icmp_seq=1 ttl=64 time=0.027
ms
64 bytes from obisrvlnx64 (127.0.0.1): icmp_seq=2 ttl=64 time=0.038
ms
64 bytes from obisrvlnx64 (127.0.0.1): icmp_seq=3 ttl=64 time=0.052
ms

In general, Unix and Linux systems take their machine name from this hosts file entry, so if
you see the machine name displayed at the command prompt like this, you can usually be
reasonably confident that the host name will resolve properly.

Database Server Requirements

Before you can successfully install Oracle Business Intelligence 11g, you have to run the
Repository Creation Utility, a component of Oracle Fusion Middleware 11g, which creates
supporting database schemas and tables that are used to hold application metadata. You
cannot install Oracle Business Intelligence until you have successfully carried out this step,
and therefore you will need database administrator access to a supported database type
before you start the product installation.
As of Oracle Business Intelligence 11g, here are the supported database types for these
schemas:
 Oracle Database
 Microsoft SQL Server
 IBM DB/2
 Oracle Database enabled for edition-based redefinition
 MySQL Database
However, as with the hardware and software requirements outlined earlier in this chapter, you
should check the System Requirements and Supported Platforms for Oracle Business
Intelligence Suite Enterprise Edition 11gR1 document on the Oracle Technology Network web
site for up-to-date details and certifications, including specific versions of each product that
are supported by the Repository Creation Utility.
When creating an Oracle database for use with Oracle Business Intelligence, ensure that you
select Unicode (AL32UTF8) as the database character set, for maximum compatibility. Non-
Unicode database character sets will usually work without issue, but future functionality or
wider use of Oracle Fusion Middleware may require the Unicode character set.
Because the Repository Creation Utility creates database accounts, data files, and other
system-level database artifacts, you will need a database administrator to enter their
credentials when you use this utility. Because of this, you might wish to make it the
responsibility of a database administrator to run the Repository Creation Utility, with your
assistance. Once these database accounts are used, you can then perform the main
installation using the database accounts that were created for you by the Repository Creation
Utility.

Chapter 3: Modeling Repositories Using Relational, File,

and XML Sources
When working on a project using Oracle Business Intelligence, the single most important
thing that you need to get right is the Oracle BI Server Repository, or “repository” for short.
The repository defines the data that your users work with, its relationship to your various data
sources, and the calculations and analyses that your users can produce. A well-designed
repository reflects the way that users think about their data and the organization of your
company, and makes it easy for them to quickly find the information they are looking for. In
contrast, a poorly constructed repository will not make sense to users, will not accurately
reflect the way you do business, and will actively work against your users rather than help
them find the numbers that they need.
This chapter is probably the most important one in this book, and I would recommend that you
read and understand it before progressing too far with other topics. It will outline the basics of
the repository, explain how you plan its design, and then take you through a number of
scenarios that illustrate different aspects of repository modeling. As we progress through the
scenarios, I will explain some of the more complex topics as we go along, explaining why we
need to make use of more advanced features, and in the process hopefully show you some of
the remarkable capabilities of the Oracle Business Intelligence Repository, and the Oracle BI
Server that it is used with.
To start, though, let’s first look at what the Oracle Business Intelligence Repository is, how it is
stored, what products use it, and how it relates to other metadata stores such as the Oracle
BI Presentation Catalog.

What Is the Oracle Business Intelligence Repository?

The Oracle Business Intelligence Repository is a metadata store that holds the logical
dimensional models that users work with when creating their analyses and reports. The
repository presents itself to users as one or more subject areas, made up of tables, columns,
and hierarchies, which are then mapped internally within the repository to the underlying data
sources that provide data for your reports.
The repository is primarily used by the Oracle BI Server, which uses it to translate incoming
logical SQL requests into physical SQL, MDX, and other queries against underlying data
sources.Figure 3-1 shows at a conceptual level the flow of data through the repository, with
users selecting data items from one or more subject areas that are subsets of a wider,
integrated logical data model. This data model contains mapping instructions so that the BI
Server can make the necessary physical SQL and MDX queries to provide data for the
requested user query.

The Oracle BI Repository is used alongside another metadata store within Oracle Business
Intelligence, and that is the Oracle BI Presentation Server Catalog. The Oracle BI
Presentation Server Catalog, or “catalog” for short, contains definitions of analyses,
dashboards, agents, conditions, scorecards, and other BI objects that users create in order to
analyze their data. As such, then, an Oracle Business Intelligence system can be thought of
as containing three layers of data and metadata, listed here and shown in Figure 3-2:
The Oracle BI Presentation Server Catalog, containing the report definitions
The Oracle BI Server Repository, containing the logical dimensional model used to
populate the reports
The underlying databases, OLAP cubes, and other data sources that actually contain
the data
The main user of metadata from the Oracle BI Repository is Oracle BI Answers, the analysis
editor and ad-hoc query component within the Oracle Business Intelligence web site.
Other users of this metadata include Oracle BI Publisher, which allows report developers to
create reports using data from the repository, and third-party reporting tools, which can work
with the repository through the Oracle BI ODBC client.
The repository is traditionally stored in a single, binary file within the middleware home
directory structure, which is typically at
[middleware_home]\instances\instance1\bifoundation\OracleBIServerComponent\coreapplicat
ion_obis1\repository
with the file having an “.rpd” suffix, such as the SampleAppLite.rpd that ships with every
installation of Oracle Business Intelligence 11g.
This file is encrypted using a password, and like any operating system file, while it can be
read by many processes in parallel, only one can write to it at any one time. This, as you will
see, has implications for team development because you will need to serialize write access to
this file using one means or another.
With the 11.1.1.6 release of Oracle Business Intelligence, a new storage format for the
repository was introduced, called MDS XML Documents. This storage format is primarily used
when integrating Oracle Business Intelligence with source-control systems, as it breaks the
repository down into individual XML files that can be checked in and checked out of source
control. In this chapter, we assume that you will be using the traditional, RPD file-based
format, and we cover the new MDS XML format, along with source control, in Chapter 11.
Repositories in either storage format can be accessed either offline or, for RPD file-based
storage only, online. Offline access is when you are directly connecting to the repository file
through, for example, the Oracle BI Administration tool, and in this case only
one developer can access the file at one time in this way. Repositories can also be accessed
online, which involves the BI Server itself connecting to the repository through an ODBC
connection, with subsequent, potentially multiple, connections to the repository then being
routed through the BI Server. You will see the differences between offline and online access to
the repository later in this chapter.

An Oracle Business Intelligence Repository Modeling Primer

So, before we get into the details of how you model the Oracle Business Intelligence
repository, let’s take some time to look into the makeup of the repository in more detail. We’ll
start by looking at the three layers that make up the repository, what those layers contain,
what they are used for, and what other information is held in the repository. Taking a step back
for a moment, we’ll look at some of the concepts around the dimensional model on which
much of the repository is based, and then we’ll take a look at various strategies you can use
to create an effective repository.

The Three-Layer Repository Architecture

The Oracle Business Intelligence repository is made up of three layers of metadata, which
build on each other to create a “semantic model” that describes in business terminology the
data coming from your source databases:
 Physical layer This layer describes the physical databases, schemas, tables,
columns, joins, and keys that make up your relational data stores, and similar metadata
for OLAP and other nonrelational sources.
 Business model and mapping layer This layer contains definitions of one or more
business models, made up of logical tables, columns, and dimensions, together with
table sources that contain mappings among the logical objects in the business model
and physical objects in the physical layer.
 Presentation layer This layer contains one or more subject areas made up of
presentation tables, columns, hierarchies, and folders.
Figure 3-3 shows these three layers, as displayed in the Oracle BI Administration tool. The
physical layer is on the right-most side, with the business model and mapping layer in the
middle and the presentation layer on the far left.
All of this metadata is held in the repository, which is generally built and administered through
the Oracle BI Administration tool. Let’s take a look in more detail at the three metadata layers
within the repository using this tool.

The Physical Layer

The physical later is where details of the physical data sources that provide data for your
reports are held. Typically, you use the Import Metadata function within the BI Administration
tool to read in table and object metadata from your source databases into this layer, which
you can then enhance by adding, removing, or correcting key and join definitions, and adding
table aliases to improve manageability. Later on, you will map the physical table columns
within this layer to the logical columns within the business model and mapping layer through
objects called logical table sources that hold sets of these mappings.
Data Sources defined within the physical layer can be one of three main types:
 Relational data sources These are sources that present their metadata using
relational structures such as tables, columns, joins, and keys.
 Multidimensional data sources This is a special type of data source recognized by
Oracle Business Intelligence that, at the time of writing, is used for Oracle Essbase,
Microsoft Analysis Services, and SAP B/W data sources (with Oracle OLAP slated for
inclusion in this list in a subsequent release).
 File, XML, ADF, and other non-database data sources In addition to data sources
from relational and OLAP sources, Oracle Business Intelligence can import metadata
 from nonrelational sources such as files, XML sources, and Oracle Application
Development Framework (ADF) sources.
Relational and relational-like data sources in the physical layer, such as those shown in Figure
3-4, have the following objects associated with them; however, not all relational-like sources
implement all object types:
 Database This is the overall container for the metadata relating to a physical database.
It contains the definition of what type of database holds the data (for example, vendor,
name, or version), together with a list of features that the database is capable of
providing and that the BI Server can therefore make use of when creating queries for
this database type.
 Connection pool Connections to databases are made through connection pools,
which aggregate individual sessions and aid scalability. Connection pool definitions
include username, password, and other connection details, together with settings for
the number of concurrent connections, the call interface to use, details of any scripts to
run once the connection is made, and other specifics about how the BI Server
physically connects to the underlying data source. This also removes the need for
individual uses to have a database account.
 Physical display folder This is an optional way of grouping collections of physical
objects in order to help with organizing and sorting your metadata.
 Physical schema Some relational databases (Oracle Database, for example) organize
their physical tables into schemas to provide a physical separation between different
application data sets. Physical schemas can be set up within databases to mirror this
organization and to provide schema name suffixes for physical tables when accessing
data through a superuser-type account.
 Physical catalog Physical catalogs are optional objects that are used to group sets of
schemas or, in the case of databases such as Microsoft SQL Server, to group physical
tables directly. Whether you use physical schemas or physical catalogs usually comes
down to the type of database server you are using and the way data has been set up
within it. If in doubt, consult with your database administrator to determine the best way
to structure your data in the Oracle Business Intelligence physical layer.
 Physical table Physical tables represent the actual tables of data within your source
database. They can either be configured as physical tables, select tables (analogous to
a database view but defined within the repository rather than the database data
dictionary), or stored proc (procedure) tables, typically used for Microsoft SQL Server
data sources and which take their data from the output of a stored procedure.
 Alias table Alias tables are variants on physical tables and are used to create
alternative names for the same physical database object. This feature is useful if you
wish to provide more descriptive names for your database objects while preserving
their physical names in the repository, or for situations where the same table plays
many “roles,” such as to provide order dates, shipping dates, payment dates, and so
on, through alternative join relationships.
 Physical column Physical columns are held within physical or alias tables, and they
represent the actual columns that store data within the tables in your physical databas

In addition to implementing database and connection pool physical layer objects,

multidimensional data sources implement their own special physical layer objects that
distinguish OLAP data sources from regular relational ones, an example of which is shown
in Figure 3-5. Following is a list of these data sources:
Database This is the container for all the cube tables, physical cube dimensions, and
other objects that make up a multidimensional data source. In Oracle Essbase terms, a
database would correspond to a physical Essbase server.
Physical catalog This is a container for one or more cube tables. In Oracle Essbase
terms, each catalog would correspond to an Essbase application.
Cube table A cube table contains the physical cube columns, physical cube
dimensions, and other objects that together in Oracle Essbase terms would be
considered a database containing a database outline.
Physical cube column These are individual data elements (either measures or
columns derived from hierarchy levels) that will go on to form the logical columns in the
business model and mapping layer.
Physical cube dimension This is contained with the cube table and represents the
dimensions that are used to organize the measures in the cube table.
Physical hierarchy One or more physical hierarch may be contained within each
cube dimension and represent individual roll-ups such as day-month-year or day-
period-financial year.

In this chapter, we will focus on relational data sources, along with flat file and XML data
sources. In the following chapter we will cover the additional things you need to know when
working with multidimensional data sources, such as Oracle Essbase and Microsoft Analysis
Services.

Physical Joins and Keys

In addition to the actual tables, columns, dimensions, and other data dictionary objects that
you find in the physical layer, you will need to define keys and table joins, or what in the world
of database administrators is called “constraints.” You can either import these from the source
database, along with the table and column definitions, or create them yourself manually (or,
most often, do a bit of both).

Key definitions (and the alias, stored proc, and select table variants) are created within
physical tables, and they define the identifier column for the table (that is, the “primary key”).
This does not have to be the same primary key column that the underlying database uses, but
you must ensure that it uniquely identifies each row and that there are no duplicate values
within the associated column; otherwise, the BI Server may return unexpected or incorrect
results for a query.
If you have created your underlying data sources yourself or you can be absolutely sure that
their primary key definitions are correct, then you can probably just import your key definitions
along with your table and column definitions; if, however, these key definitions do not exist or
you are not sure they are correct, you can choose not to automatically import them and can
instead create them yourself manually after the initial metadata import.
Keys are primarily used when creating physical or complex joins from a master-level table (for
example, one containing dimensional data) to a table containing detail-level data (typically,
data containing facts and measures). These joins tell the BI Server how to retrieve data from
more than one table—and again, they can either be automatically imported from the
underlying database (by reading the foreign key information in the database data dictionary)
or you can create them yourself manually.

Correctly and efficiently setting up keys and joins in your physical layer is probably one of the
most important tasks you will undertake as an Oracle Business Intelligence developer, and
we will explore several scenarios involving different types of keys and joins later in this
chapter. Note that, unlike tools such as Oracle Discoverer, joins can only be set up by an
administrator; and if there is more than one possible join path between two physical tables,
these have to be resolved in advance by the administrator rather than giving the user the
ability to select between them when creating the query.

Table Aliases and Physical Display Folders

Aliases are physical layer metadata objects that represent alternative names for a single
physical table. You create them typically for two reasons:
 To add additional usage information to the physical object names in your repository; for
example, a physical table called DAY might be aliased to Dim_DAY to indicate that it
contains dimensional-type data. Adding this additional information makes it easier to
understand the purpose of the physical tables in your repository

To allow a table to be joined to more than once. For example, where an ORDERS table
contains multiple foreign key references to a DAY table to represent order date, ship date,
payment date, and return date, you will need to create aliases such as Dim_DAY_Order_Date
and Dim_DAY_Ship_Date for each “role” that the table performs.
To organize sets of physical objects into groups, you can also define physical display folders
under the main database definition, into which you can place sets of physical objects.

Physical display folders are particularly useful if your naming standard is always to work with
aliased table names and you want to place these aliases in a display folder to keep them
separate from your physical table names.
Database Definitions
When defining a new database in the physical layer, you can select from a number of
database servers from different vendors to correspond with how your data is stored.

Refreshing and Updating Physical Layer Metadata

Typically, you create your initial physical layer database metadata using the Import Metadata
feature within the Oracle BI Administrator tool and then make manual amendments to this
metadata to add or correct keys or joins, and to add aliases, display folders, and the like. If,
after this initial metadata import, your underlying database schema changes, you can reimport
your physical database metadata and it will update the physical model. Alternatively, you can
add or remove tables, columns, or other objects yourself from the physical layer, although you
will need to ensure that any changes you make correctly reflect the physical database
structures in your database.

The Business Model and Mapping Layer

The business model and mapping layer, usually the middle layer when viewing the semantic
model using the Oracle BI Administration tool, performs two functions:
 It defines the logical, dimensional business model for your BI system.
 It maps the logical columns within this business model to the physical columns in the
physical layer of the semantic model.
Your semantic model can contain multiple business models within this layer, something that
typically happens when you want to report across multiple source systems but your data sets
are not well integrated. Your long-term objective, though, should be to have a single,
integrated (or “conformed”) business model so that users can easily create analyses that
span multiple subject areas using common dimensions. Figure 3-7 shows a typical business
model containing two fact tables and conformed dimensions that, in turn, map to a larger
number of source databases. Such a model would make it possible for users to query across
both sets of fact measures using dimensions that are common to both.
Each business model defined within the business model and mapping layer contains some or
all of the following repository objects:
Business models The business model is the container for a set of logical tables,
logical columns, logical table sources, and logical dimensions. Business models can
map to more than one physical data source, and data mapped into the business model
can come from data sources at differing levels of detail (or “granularity”).
Logical display folders As with the physical layer of the semantic model, logical
objects within a business model can be organized into logical display folders. Logical
display folders are useful when you have large amounts of objects within a business
model and you wish to organize them to make navigating the business model easier
and more efficient.
Logical tables These are “virtual” tables that represent the data set that users will
use to create their analyses, sourced from a single physical table or a set of tables, and
either through direct mappings or through SQL expressions. They can be categorized
into three types: facts—logical tables containing measures such as profit, revenue,
cost, and margin; dimensions—logical tables containing reference data by which
measures are analyzed, such as customers, products, times, and stores; and lookups
—new with the 11g release of Oracle Business Intelligence.
Logical columns Logical tables, whether used for facts or dimensional data, are
made up of one or more logical columns. Logical columns are the “building blocks” of
logical tables, and each one is either mapped to one or more physical columns,
inheriting their data types, or is derived from an expression based on other logical
columns.
Logical dimensions Logical dimension (not to be confused with logical tables
containing dimension, or reference, data) define the drill paths, or “hierarchies,” within
your logical tables. Logical dimensions contain either level-based hierarchies (which
themselves can either be balanced, ragged, and/or skip-level) or parent-child
hierarchies, where the hierarchical relationship between data items is determined by
member ID and parent ID columns.
Logical table sources A logical table source is a mapping that maps logical columns
either directly to physical columns or indirectly through an SQL expression. Figure 3-
 8shows a typical logical table source that maps logical columns within a logical
dimension table to physical columns held in multiple linked physical tables, either
directly or through SQL expressions.
For simple business models with a single physical data source, a logical table might be
mapped 1:1 with a corresponding physical table in your database. For more complex models,
you might map data in from aggregate or summary tables, and you might source certain
logical columns from additional sources such as files, OLAP cubes, or other databases.
Logical tables represent a way for semantic model designers to create a simplified,
dimensional view of the organization’s data even when that data might be stored in more
complex, normalized database schemas. As such, along with the rest of the business model,
you can think of them as an “abstraction layer” over your organization’s physical data
sources.
Logical tables typically contain a single logical table source when you first develop them, but
as you map in additional sources of data for the logical table, typically you will end up creating
more than one. For example, in Figure 3-9, a logical fact table has three logical table source
mappings associated with it that provide data at a detailed, aggregated level and data for
logical columns that are sourced from a separate database.

When a user’s analysis requests data from this logical table, the Oracle BI Server will use one
or several of these logical table sources when sending physical SQL queries to source
databases, depending on which of the logical columns were used in the analysis and the level
of aggregation requested.

Logical Keys and Joins

Like physical tables, logical tables have keys and joins. Each logical table containing
dimensional data must have a logical key, which usually (but not always) corresponds to the
primary key in the associated physical table.
Logical joins are like physical joins, but they do not specify any logical columns to join on.
Instead, the Oracle BI Server determines which particular physical column to join the
underlying physical tables on based on the particular logical table sources that are being used
to access the underlying data sources or, in the case of multidimensional sources, uses other
methods to return data from the fact and dimension. Logical joins are the way that you tell the
BI Server that a particular dimension joins to a particular fact, and if users request data from
both, the data can be returned. Conversely, not declaring a logical join between fact and
dimension logical tables, even if they are physically joined in the database, will prevent the
user from reporting on columns from two tables.
This allows the Oracle BI Server to handle data sources for a logical table that might hold
aggregated data and not, therefore, join on detail-level dimension keys. It also allows the BI
Server to work with sources that provide compatible data but that might themselves be joined
on different physical columns (or that, as in the case of data sources such as Oracle Essbase,
do not feature “columns” or “joins” in the same sense as relational data).

The Presentation Layer

While it is best practice to have as few separate business models in your semantic model as
possible and try to integrate as much of your source data into a single business model,
presenting this full data set to users would probably overwhelm them. In addition, you might
want to separate your business model into subject area–orientated data sets, which users can
still combine together since you’ve done the hard work in creating links between them. This,
together with the ability to further alter the naming and organization of tables to suit particular
audiences, is the purpose of the third layer in the semantic model: the presentation layer.
At its simplest, the presentation layer could just be a 1:1 reflection of what is in your business
model and mapping layer. More usually, though, the presentation layer contains subject areas
for each of the fact tables in the business model and mapping layer, with each subject area
also containing copies of the dimension tables that link to each fact table. Figure 3-10 shows a
presentation layer containing two subject areas that, in turn, both map back to a single
business model in the business model and mapping layer.

The analysis editor allows report developers (where allowed by their security settings) to

include more than one subject area in an analysis, as long as they all originate from the same
business model. By creating presentation layer subject areas in this way, you can break your
otherwise integrated business model into small, “easily digested” chunks of data customized
for particular groups of report consumers in your organization.
A presentation layer within the semantic model can contain the following repository objects,
as shown in Figure 3-11:
Subject areas A container for presentation tables and other objects within the
presentation layer is a subject area. Subject areas are analogous to databases, or data
marts, and are selected by users when creating analyses in order to work with a
particular selection of tables and columns. A subject area can contain a single fact
table together with associated dimension tables, or it can contain multiple facts and
dimensions. Indeed, a subject area can contain any selection of tables sourced from a
single business model, but it is important to ensure that tables included have some
relationship to each other; otherwise, users may select combinations of tables for their
reports that cannot be joined together.
Presentation tables These are containers for presentation columns and presentation
hierarchies. In many cases, presentation tables are derived in their entirety from
corresponding logical tables in the business model and mapping layer, but in fact you
can drag-and-drop columns from any logical table within the same business model into
a presentation table. You still need to be careful, however, that column selections made
by users will result in a valid SQL query.
Presentation columns These are columns within presentation tables, which are
usually created when you drag-and-drop logical columns from a business model into
presentation tables. Presentation columns in Oracle Business Intelligence 11g can be
either attribute columns, which contain columns from dimension logical tables and to
which the Oracle BI Server applies a GROUP BY and DISTINCT clause, or measure
columns, which typically are numeric columns from a fact logical table to which the
Oracle BI Server applies an aggregate function (for example, SUM ()).
 Presentation hierarchies These are new with the 11g release of Oracle Business
Intelligence and are created when you drag-and-drop logical dimensions from the
business model and mapping layer into a presentation table. Separate presentation
hierarchies are created for every hierarchy within a logical dimension, and when
included in an analysis they create a new type of column called a hierarchical
column that can be used alongside attribute and measure columns when analyzing
data.

Other Information Stored in the Oracle BI Repository

In addition to the three-layer semantic model, the Oracle BI Repository contains other
information used by the Oracle BI Server to define and manage variables, manage row-level
and subject area security within the semantic model, and define subsets of the repository that
can be checked out by developers to enable multiuser development.

Variables and Initialization Blocks

Variables can be defined within the repository that can then be referenced in expressions and
filters. These variables are defined using the Oracle BI Administration tool and initialized once
when the Oracle BI Server process starts, when a user logs into the dashboard, or to a set
schedule defined by the administrator.
Oracle BI Server variables can be one of two types:
 Repository variables These have the same value systemwide and are either
initialized on system startup or have their values set by a database lookup on a
predefined schedule.
 Session variables These are private to each user session and are generally set when
a new user session is created, although in the 11g release of Oracle Business
Intelligence this initialization can be deferred until the variable is first accessed.
The process that sets the value of dynamic repository variables, and all session variables, is
called an initialization (or “init”) block. Initialization blocks read from a data source and use the
results to populate the variable, and like variables are defined using the Variable Manager
within the Oracle BI Administration tool.

Row and Subject Area Security Settings

The Security Manager within the Oracle BI Administration tool is used for managing object
permissions, row-level data filters, and query limits that can be applied to application roles
and individual user accounts. Together with the Oracle BI Administration tool’s ability to define
access rights to objects at any level in the presentation layer, security can be defined at row
level and subject area level for all data in the repository, or responsibility for such filtering can
be delegated to the source database through technologies such as Oracle’s Virtual Private
Database.
Oracle BI Server variables, and the security settings that make the best use of them, are
described in more detail in Chapter 8.

Projects
The Oracle BI Repository is in most cases stored in a single, monolithic file, and therefore
Oracle has had to develop a special process for handling concurrent editing of the file. One
such process is the Multiuser Development Environment (MUDE), which allows developers to
check out subsets of the repository, called projects, that are edited separately and then
checked back into the main repository. These projects are defined using the Project Manager
and are pointers to business models, subject areas, variables, users, initialization blocks and
application roles that can then either be manually exported as a project (a subset repository
file) or automatically exported using MUDE.
Projects and multiuser development are described in more detail in Chapter 11.

Identifying the Logical Dimensional Model

When defining the data layer for your Oracle Business Intelligence 11g system, you need to
think in terms of the “model” that will be required to support the range of queries your users
will require, rather than individual data sets to support individual reports. This is often termed
a “model first” approach and ensures that users can write queries that go across a range of
subject areas and measures. The key to this is getting the right design for the business model
and mapping layer within your semantic model.
As you will have seen from the overview of the semantic model, while data can be sourced for
the physical layer from any number of database types and designs, the business model and
mapping layer is designed around a logical dimensional model that, at a minimum, contains a
single fact and single dimension with a join between them. The logical tables within a
business model are organized into star schemas, a database design approach popularized by
Ralph Kimball in the book The Data Warehouse Lifecycle Toolkit. Figure 3-12 shows a typical
starschema, with a sales fact table in the center linked to product, times, customer, and store
dimensions.
In addition to star schemas, Oracle Business Intelligence supports the more normalized
version of this data model type, called a snowflake schema. Snowflake schemas are
sometimes used by customers with large data sets looking to minimize the storage required
for their data warehouse, or in cases where their schema has lots of hierarchies and levels
that can be built easily from snowflake-dimension “building blocks.”
Each dimension has one or more hierarchies that define the hierarchical relationship between
columns in the table and a key that defines the unique reference for table rows. The fact table
contains one or more measures, and while in the business model and mapping layer there is
no need to include dimension keys in the logical fact table, these keys are derived by the
Oracle BI Server at query time through logical table source mappings back to the physical
layer of the semantic model.
If you’re lucky enough to be creating a semantic model where your data source is a single
data warehouse or an Essbase OLAP cube, creating the logical dimensional model will be as
easy as dragging and dropping the whole schema from the physical layer over to the
business model and mapping layer. If, however, your source database is in third-normal form
(a common data modeling approach for transactional systems that minimizes duplicate
storage of data at the cost of a highly fragmented and complex data model, defined in more
detail athttp://en.wikipedia.org/wiki/Third_normal_form) or is an enterprise data warehouse or
operational data store organized in a normalized fashion, how do you go about identifying
what goes where in the logical dimensional model?

Overview of a Dimensional Model

A dimension model divides data into two main categories:
 Fact tables These contain either numbers or dimension keys (references to dimension
table IDs).
 Dimension tables These are containers for reference data that are used to analyze, or
“slice and dice,” the measures in the fact table.
Fact tables can themselves contain reference data (usually called “degenerate dimensions” in
this context) but from a purist point of view should only contain measures and keys.
Dimension tables can also contain numbers (for example, the square footage of a store), but
only when they are used to make selections from the dimension. Within the dimension table,
columns are referred to as “attributes,” which contain values referred to as “members.”
Columns within the fact table containing values are called “measures,” with the values they
contain referred to as “measure values.”
Dimension tables within a dimensional model usually contain one or more hierarchies. Within
Oracle Business Intelligence 11g, these hierarchies are either level based or parent-child
based (value based), and these hierarchies define how data is aggregated within the fact
table. When defining a dimension, you generally have to work out whether it will be level
based or parent-child based. If it is level based, you also have to know what the levels are,
and if parent-child based, which column will provide the member ID and which will provide the
parent ID. In the case of level-based hierarchies, you then need to organize the various levels
into the hierarchy itself, while for parent-child hierarchies the data itself will define the
hierarchy (through a recursive relationship in the data).
Fact tables are perhaps easier to define, although as we will see later on, there is the
additional question of how many fact tables to use and how to source them. You will, however,
need to choose a default aggregation method for each measure (for example, sum or
average) and decide to which dimension each fact table joins. So how do we go about
identifying the facts and dimensions and, in Oracle Business Intelligence 11g, candidates for
lookup tables?

Identifying Facts
Your source database, if not already a dimensional data warehouse, will typically be a
transactional database designed along the principal of third-normal form modeling. Third-
normal form modeling is a design approach where data is stored just once, in the most space-
efficient manner and in a way that makes transactions as efficient as possible to process.
Your task as a dimensional modeler is to identify those data items within this data model that
will provide the “facts” for your business model.
Candidates for facts are typically data items that record events, and business processes
within your organization. For example, if you are an insurance company, your main business
processes are likely to be the following:
 Selling insurance policies
 Placing them on risk
 Processing claims
 Paying commission to salespeople
Similarly, in the examples used in this book, a retail chain selling food and beverages is likely
to run the following key business processes:
 Baking cakes and manufacturing sandwiches
 Selling food and beverages in the stores
 Paying its staff and suppliers
 Monitoring quality and running “mystery shopper” exercises
These activities and business processes will form the most likely candidates for the fact tables
in your logical model. Each process will be associated with measures and metrics (how many
sandwiches you sold, how many claims were made), and interviews with potential users of
your business intelligence system should help you focus on what facts and measures you
should have in your model.

Identifying Dimensions
While it would be useful to know the total number of claims or sandwiches your organization
has handled over all the years you have been in business, it’s usually more useful to be able
to break this total figure down by customer type, region, year, and so on. Then, once you start
thinking about year, for example, you realize that it would also be useful to break this figure
down by quarter, month, and sometimes day. It would also be useful to analyze data by other
calendars (or “hierarchies”) such as fiscal, academic, or taxation.
The things that you break facts and measures down by are your dimensions. Dimension
tables are typically organized by theme, such as customer, product, store, or time, with each
dimension typically containing a number of levels and hierarchies. Within your logical
dimensional model, fact tables contain the numbers you wish to analyze, while dimensions
contain the reference data you analyze these numbers by. If you keep your model as simple
as that, you won’t go far wrong.

Identifying Columns
Your source database will typically have hundreds of columns, some of which users are
interested in for reporting on and some of which they aren’t. The temptation, when you aren’t
sure which are which, is to include them all in the logical model and then, through a process
of “natural selection,” monitor which ones are used and which ones you can safely discard.
Instead, focus on bringing into your model just those columns that are needed, which for the
fact table are those containing your measures, and in the dimension tables are those which
either form the dimension unique key or those that will be required by users to make
dimension member selections. If in doubt, leave them out initially, and you can easily add
them in once you have a better handle on your users’ requirements.

Identifying Lookup Tables

A new feature in the 11g release of Oracle Business Intelligence is the ability to create a third
type of table in the business model and mapping layer, called a lookup table. Lookup tables
contain IDs and reference data that can be accessed by the LOOKUP function also
introduced with 11g, and they are typically used to include data in an analysis that needs to
be accessed outside of the usual grouping and aggregation that takes place in a query, such
as in the following cases, for example:
 You wish to perform a currency conversion after the main aggregation has taken place
in a query.
 You need to access data stored in a physical column with a CLOB data type that
cannot be included in a GROUP BY clause.
In most cases, you would use the new LOOKUP function within a logical table source
mapping to reference such data directly from the physical layer in your semantic model, but
when the lookup data you wish to reference is held in a separate database to your main data
set, a lookup table can bring this data directly into your business model where its original
source is not relevant.
You would therefore plan for lookup tables in your logical model when your requirement fits
this narrow set of circumstances but the lookup data is held in a source database that cannot
easily be joined to within a single logical table source.

What Tools Do I Use to Build the Repository?

In general, you would use the Oracle BI Administration tool to build the repository, which ships
as part of every installation of Oracle Business Intelligence or can be installed stand-alone,
downloaded from the home page on the Oracle Business Intelligence web site. The Oracle BI
Administration tool lets you import source table metadata into the semantic model and then
use this metadata to build out the business model and mapping layer and then the
presentation layer. You can also use it to manage variables, security (row-based and subject
area security), and caching, as well as perform other administrative functions. This book
focuses entirely on the Oracle BI Administration tool as the tool to create the repository, as
this is by far the most common method used by developers.
Historically, another tool that you might consider is Oracle Warehouse Builder (OWB). OWB
ships as part of the Oracle Database and provides data loading, data modeling, and data
warehouse lifecycle functionality for developers looking to create an Oracle-based data
warehouse. OWB does, however, come with functionality for creating a “first-cut” Oracle
Business Intelligence semantic model, deriving the repository from the dimensional metadata
contained in a Warehouse Builder project. Figure 3-13 shows Warehouse Builder deriving
metadata from a warehouse project for use as an Oracle Business Intelligence repository.
Using OWB is certainly an interesting idea, as it allows you to trace metadata lineage from
derived business intelligence repository information right back to the original source database,
and comes with version control, change management, and other project lifecycle tools. It does
not, however, replace the Oracle BI Administration tool, as it has no features for managing
variables, caching, projects, and other repository information; and repositories created by
Warehouse Builder and subsequently amended outside of the tool cannot then be reimported
back into OWB.
Moreover, Warehouse Builder is no longer on Oracle’s strategic data integration roadmap;
customers will eventually need to move to its replacement, Oracle Data Integrator, and there
are no plans to introduce similar functionality in this tool. As such, while Warehouse Builder
may be a useful option for repository creation if you already use the tool extensively, it is
probably not worth investing time in if you use other methods to populate your supporting data
warehouse.

Example: Creating the Oracle BI Repository

Now that you know the basics and have the sample data installed, how do you go about
creating a new repository from scratch? The following steps outline the basic process for
creating a new repository, and later on in this chapter we will look at more complex scenarios
such as integrating (or “federating”) separate data sources, working with complex hierarchies,
integrating historic and real-time data, and adding time-series and other advanced analytics.
For now, though, let’s walk through the basic process of creating a repository against a single
Oracle data warehouse data source.
At a high level, the ten steps to create a new repository are as follows:
1.Create the new repository offline, and import source object metadata.
2.Create aliases and display folders for source objects.
3.Create physical keys and physical joins.
4.Create the business model, logical tables, and logical columns.
5.Add logical keys and logical joins, and configure the column to be sorted by another
column’s values if required.
6.Define logical dimensions and hierarchies.
7.Define calculations and other derived columns.
8.Publish a business model as one or more subject areas.
9.Perform a consistency check for errors and warnings.
10.Publish the repository online so that it becomes available to users.
Let’s now take a look at these steps in more detail.

Step 1: Create the New Repository and Importing Source Data

Your first step in creating a new repository is to open the Oracle BI Administration tool and
select the option to create a new, blank repository. Using this option will display a dialog box
asking if you wish to import source data, and you will also be prompted to set a repository
password. Make a note of this password, as there is no way to recover it if the password is
lost.
In the following example, we will create a new repository called GCBC_Repository.rpd and
import database metadata into it, with the repository file being stored in a working area on the
workstation PC.
1. Select Start to bring up the Windows Start menu; then select Programs | Oracle
Business Intelligence | BI Administration. When the BI Administration tool opens, select
File | New Repository to start creating the new repository.
2. In the Create New Repository dialog box, enter the following values:
Create Repository: Binary
Name: GCBC_Repository.rpd 
Location: c:\biee_workarea 
Import Metadata: Yes
Repository Password: welcome1 
Retype Password: welcome1
Note Ensure that the directory specified under Location actually exists; otherwise, you will not
be able to proceed to the next screen.

In the Create New Repository – Select Data Source dialog box that appears, select the
connection type for your source database from the Connection Type drop-down list, and then
enter the connection details; for example:
Connection Type: Oracle OCI10g/11g
Data Source Name: orcl 
User Name: gcbc_sales 
Password: password
Note For Oracle sources, the data source name should be the Oracle Net (TNSNAMES)
connection name to your database. See Chapter 2 for details on how to configure the
 embedded Oracle Client software within Oracle Business Intelligence to recognize this
connection name.
4.In the Create New Repository – Select Metadata Types dialog box, select the
metadata types (database object types) that you wish to import (for example, table,
keys, and foreign keys).
You will be shown a listing of all of the database objects that your database username
has SELECT privileges for. For this example, we use the dialog box to select the
PRODUCTS, SALES, and TIMES tables from within the GCBC_SALES schema.

4.After initially selecting the metadata objects to import, the Connection Pool dialog box
will be shown. For now, just click Finish to close this dialog box, and we will look in
more detail at the connection pool settings in a moment.
If you look within the physical layer of your new semantic model and expand the database
node, you will see your newly imported source data.

You now have the basic set of tables imported into the physical layer of your semantic model.
Now you can enhance this physical model by creating aliases for the physical tables and then
placing them in a separate display folder.

Step 2: Create Aliases for Imported Table Names, and Physical Display
Folders to Organize Them
Aliases are alternative names for a physical layer object that can be used to make the names
more descriptive or to distinguish between roles for an object. In this example, we will create
aliases for our imported tables to make it clear what role the table plays, as a form of “naming
standard” to make it easier for other people to understand our model.
1. Within the physical layer, locate the database that you have just imported. Right-click
the first of the tables and select New Object | Alias. Use the Physical Table dialog box
to give the alias a name; for example, for a physical table called PRODUCTS that
holds product dimension information, call the new alias Dim_PRODUCTS.
2. Repeat this for the other physical tables, using the following alias prefixes:
Dim_: Source table used for dimension information
Fact_: Source table used for fact (measure) information
Lkp_: Source table used for lookup information
If a table is used for multiple roles, for example, a table containing dates or times,
create an alias for each role that it plays, such as Dim_TIMES_Order_Date,
Dim_TIMES_Ship_DATE, and so on. If a source table contains both dimension and
fact information, create an alias for each of these roles, such as Dim_ORDERS and
Fact_ORDERS. Do this for every table in the physical schema.
3. Once all the aliases are created, right-click the physical database item orcl, and select
New Object | Physical Display Folder. Give the folder a name (for example, GCBC
Sales), and then drag-and-drop the aliases you just created into this new folder.
4. Dragging and dropping the aliases into the display folder copies them there rather than
moving them. To display only these aliases within this folder, from the menu select
Tools | Options, and then select the Repository tab. Select the “Show tables and
dimensions only under display folders” check box and click OK; then return to the
 physical layer. Expand the database entry, and you will see that the aliases are shown
now only under the display folder. Going forward, you will only work with these aliases
and not the physical tables you initially imported.
Now that you have renamed and simplified your physical model, you can create or check the
primary and foreign keys.

Step 3: Create Primary and Foreign Keys in the Physical Model

If your database source has primary and foreign keys defined, and you are confident that
these keys are valid, then you can import them into the physical layer as part of the metadata
import process. If you are not sure that these keys are correct, or if they would not be valid
when used within the Oracle BI Repository (for example, recursive joins or multiple joins
between two tables), you can create them yourself manually.
Note Only create keys on physical tables that will be used for dimension logical table
sources. Physical tables used for fact table sources do not need keys; instead, you
create joins from them to the physical dimension tables, joining to the keys that you
created for them.
To manually create keys (primary keys) and joins (foreign keys), take the following steps:
1. To create keys on physical layer tables and alias tables, locate the table or alias in the
physical layer and right-click it. Select Properties, and then select the Keys tab. With
the Keys tab selected, use the Key Name column to name the key (use something
descriptive, such as Dim_PRODUCTS_Key), and then use the Columns drop-down
menu to select the column on which the key will be created.

To create joins (foreign keys) in your physical layer, you can either create them using the table
properties dialog box as with keys, or you can use the Physical Diagram view to create them
graphically. To create joins using the table properties dialog box, select a physical table
containing fact data, right-click it, and select Properties; then select the Foreign Keys tab and
click the Add button to display the Physical Foreign Key dialog box. Use the dialog box to
select the dimension table and column you wish to join to, as well as the fact table column
that joins to them, and click OK to save the join.
Alternatively, to create joins using the Physical Diagram view, using the physical layer to
locate the tables or aliases you wish to join, CTRL-click them to select them all, and then
right-click and select Physical Diagram | Selected Object(s) Only. When the Physical Diagram
view is then displayed, starting with the fact table and with the New Join button selected, click
the fact table and then draw lines (joins) to the dimension tables, selecting the columns to join
on when the Physical Join dialog box is shown. Repeat this step until the SALES fact table is
joined to the TIMES and PRODUCTS dimension tables.

When creating the physical joins, give them a descriptive name such as
Sales_Fact_to_Product_Dim_FK so that you can identify the purpose of the join if it is
referenced in a warning or error raised when using the Consistency Checker, detailed later on
in this chapter.
Step 4: Create the Business Model, Outline Logical Tables, and Add
Logical Columns
Now that you have your initial physical model, you can now start to create the business model
to go with it. Your objectives when creating this business model are to create a dimensional
model consisting of fact tables containing measures, together with dimension tables
containing attributes, to which you will add calculations, hierarchies, and other information
that users will find useful when creating analyses.
To start this process, create empty fact and dimension logical tables, and then join them
together so that the Oracle BI Server knows whether each logical table is a fact or dimension:
1. Using the Oracle BI Administration tool, locate the business model and mapping layer
in the semantic model. Move the mouse pointer to an area away from any other objects
(for your first model, there should be none), right-click and select New Business Model.
2. When the Business Model dialog box is shown, type in the name of the new model (for
example, Sales).
3. When the new business model is then displayed, right-click it and select New Object |
Logical Table).
4. At the Logical Table dialog box, enter the name of the logical table (for example, Fact
Sales). Now repeat steps 3 and 4 to create the remaining logical tables in your
business model, starting with the fact tables and then moving on to the dimension
tables.
Now you can start adding columns to the logical tables you just created. Starting with the fact
table, locate the column in the physical layer that you wish to map across, and then drag-and-
drop it from the physical model into the logical fact table. Notice how within the Sources folder
in your logical fact table you now have an entry with the same name as the source table.
When adding columns to the fact table, only drag the measure (numeric) columns across, not
the dimension ID columns, as the Oracle BI Server takes care of the join for you in the
background based on the keys and joins you set up in the physical layer of the semantic
model. Make sure you drag across the dimension key columns, though, as you will need
these later on to create your logical table keys. In some situations, you might need to source
columns for a logical table from more than one physical table. Later on in this chapter, we will
take a look at a number of situations where this is the case, and how you model this in the
repository.

Once you have added all of the columns to your logical model, rename them so that they use
regular business names rather than the system names that were used in the source
database. For example, you might want to rename the incoming logical column
FCAST_SAL_AMOUNT to Amount Sold so that users are better aware of the meaning of the
column. To rename a column, either select it and then single-click it to open the name for
editing or right-click the logical column and select Rename.
Note Never rename columns in the physical layer unless the name has also changed in the
underlying database, as doing so may cause errors when the Oracle BI Server tries to
access the database source.
7. Finally, you need to set the default aggregation rule for the measure columns in your
logical fact table. To do this, double-click each measure column in the fact table to
display the Logical Column dialog box, select the Aggregation tab, and then select the
 required aggregation type from the Default Aggregation Rule drop-down list.
Alternatively, if you wish to set the same default aggregation rule for a set of logical
columns, CTRL-click all of the logical columns to select them, and then right-click and
select Set Aggregation. When the Aggregation dialog box is displayed, select the
default aggregation that you wish to apply to the logical columns. You can also uncheck
the All Columns The Same check box if you wish to set different default aggregation
types for the columns that you selected.

Step 5: Create Logical Keys and Logical Joins

In the business model and mapping layer, you need to ensure that all logical tables used for
dimension data have suitable keys defined for them. If you have already created keys in the
physical layer, these keys will come across when you drag the relevant physical column into a
logical table. If you do not have physical keys defined yet, though, or you wish to create a
logical key using a different column, follow the steps below to create them.
1. Right-click the logical table that you wish to create the logical key for, and select
Properties; then use the Keys tab to create the logical key, naming it and selecting the
relevant logical column.
2. Now CTRL-select all of the logical tables that you have just created; then right-click
them and select Business Model Diagram | Selected Table(s) Only. When the Business
Model diagram opens, with the New Join button selected, join the fact table to the
dimension tables, starting with the fact table. As these are logical joins, you do not
need to specify the columns on which the join takes place, as this will be determined by
the Oracle BI Server at query run time.
You now have a basic business model, made up of a logical fact table and one or more
logical dimensions. Check that your business model looks similar to Figure 3-14, with a
pound or hash (#) icon over the fact table; if the pound or hash sign is over the
dimension tables instead, you have created your logical joins the wrong way round.

To change the order in which columns are listed within a logical table, right-click it and select
Properties (or just double-click the logical table to display the Properties dialog box), ensure
that the General tab is selected, click the logical column you wish to move, and then use the
Move Up and Move Down arrow buttons.
1.Similarly, if you wish to sort one logical column using the values in another (for
example, sort a column containing months in the format Feb-2010 by a column
containing months in the format 201002), double-click the logical column to display the
Logical Column dialog box. Then, using the Set button next to the Sort Order Column
area, select the column you wish to sort this one by.

Step 6: Define Logical Dimensions and Hierarchies

An optional, but recommended, next step is to create the logical dimensions and hierarchies
(or “drill paths”) within your business model. The term “logical dimension” can be confusing
because you have, of course, just created logical tables holding dimension information in the
previous steps, but these logical dimensions are additional metadata elements that help users
navigate through the hierarchies in your data. To create simple, balanced, level-based
hierarchies (we will get into the more complex ones later on), follow these steps:
 Using the Oracle BI Administration tool within the business model and mapping layer,
1.

right-click the business model for which you wish to create a logical dimension. Select
New Object | Logical Dimension | Dimension With Level-Based Hierarchy.
Note If the option to create a logical dimension is not shown, check that you have created
logical joins between your logical tables, as this option only becomes available when
you have logical dimension tables identified that do not otherwise have logical
dimensions defined for them.
2.When the Logical Dimension dialog box is shown, type in the dimension name (for
example, Products). Ignore the other options on this dialog box for the moment, and we
will look in detail at these later on in this chapter.
3.Now you will create the levels in the level-based hierarchy for this logical dimension.
Select the logical dimension that you have just created, right-click it, select New Object
| Logical Level, and the Logical Level dialog box will be shown. This first level will be
the “grand total” level for your dimension’s hierarchy, so name it All <Dimension
Name> (for example, All Products), and select the Grand Total Level check box.
4.Right-click this grand total level and select New Object | Child Level. Using the
Logical Level dialog box, type in the name of the level, and then when the dialog box
closes, right-click this level and repeat this step, creating levels under each other to
form the level-based hierarchy.

Now that you have created the levels, the next step is to associate logical columns with the
levels. To do this, drag-and-drop columns from the logical table associated with the dimension
onto the relevant level, and then right-click the column and select New Logical Level Key.
… Repeat this step for all of the levels in the logical dimension apart from the grand total
level.

Ensure that each logical column that you set as a logical level key does, in fact, contain
unique values; otherwise, the Oracle BI Server may provide incorrect results for a query. In
cases where you have an ID and a descriptive value for a dimension level (for example,
Product ID and Product Name), if just the ID is unique, drag both values into the logical level
but set just the ID as the level key. If both the ID column and the descriptive column are
unique, you can set both columns as level keys, and then select the descriptive column for
display during drill-down by right-clicking the logical level, selecting the Keys tab, and
checking the Use For Display check box for just the key containing the descriptive column.
This will be used when drilling down in the front end as the column to be displayed.

Some dimensions can contain more than one hierarchy. For example, a logical time
dimension may need to aggregate data through a calendar hierarchy and a fiscal hierarchy. In
these situations, all of the hierarchies will share a common top (grand total) and bottom (leaf)
level, but in between they will contain their own levels organized into alternate hierarchies. To
create a dimension with two alternative hierarchies, follow these steps:
1.Create the new logical dimension as before, and create a grand total level directly
under the logical dimension, naming it, for example, All Times. Ensure you select the
Grand Total Level check box when you create the level. (Note that it is not mandatory
to create a grand total level, but it is considered “best practice.”)
 2.Start by creating the first hierarchy. Right-click the grand total level and create, one
by one, the child levels under it, until you reach the bottom (leaf) level.
3.Now return to the grand total level, right-click it, and select New Object | Child Level.
Continue now creating the levels under this alternative hierarchy, until you reach the
level just above the leaf level.
4.To join this alternative hierarchy back to the main one at the leaf level, right-click the
last level you created in the alternative hierarchy and select New Object | Shared Level
As Child. Then select the leaf (bottom) level from the list of levels so that this
alternative hierarchy is then joined back to the main one, at the leaf level.
5.Then, as before, drag-and-drop the logical columns from the related logical table onto
the levels, and create the logical level keys. You will only need to add the logical
column for the leaf level once, even though it is displayed as part of two separate
hierarchies under the logical dimension.

You may have noticed when creating your logical levels a setting labeled “Number of
elements at this level.” Best practice is for you to enter into this setting the number of unique
values (members) in the column designed as the level key. Providing this value is optional,
but when it is present it helps the BI Server determine the most optimal execution plan for a
query. You can either enter the number in manually or populate the values automatically when
the repository is open in online mode by right-clicking the logical dimension and selecting
Estimate Levels, which causes the BI Administration tool to run a series of queries on the
source database(s) to count the number of distinct values at each level. Note that the values
used in this setting need only be estimates, and you do not need to update them unless the
number of unique values in the associated logical column change significantly.

Step 7: Define Calculations and Other Derived Columns

In addition to logical columns that you create by dragging and dropping physical columns into
the logical table, you can create additional ones by deriving their values from other logical
columns in the business model. For example, you could create a logical column for profit by
subtracting costs from revenue. We will cover the different types of calculations you can
create in the business model and mapping layer in more detail later on, but to create a basic
derived logical column, follow these steps:
1. Right-click the logical table for which you wish to create the new logical column, and
select New Object | Logical Column.
2. When the Logical Column dialog box is shown, ensure the General tab is selected and
then type in the name for the column (for example, Profit).
3. Next, click the Column Source tab, and select the “Derived from existing columns using
an expression” radio button. Then click the Edit Expression button to the left of this
radio button to display the Expression Builder dialog box.
4. Using the dialog box, use the Category panel to select the Logical Tables entry, and
then use the Logical Tables panel to select the fact table containing your measures.
With the fact table selected, select from the Columns panel the first measure in your
expression, and then use the list of operators on the bottom right-hand side of the
dialog box to select the operator (for example, a minus operator: –). Then, to complete
the expression, repeat this step to select the second measure.
When you return to the Logical Column dialog box, your expression will be listed in the bottom
panel of the dialog box, and the logical column will be calculated at query time using the
expression you used as its column source.

Step 8: Publish the Business Model as Subject Areas

Now that you have a business model ready for querying, you should publish it to the
presentation layer of the repository so that it can be used in analyses. You can either publish
the business model automatically, creating separate subject areas for each fact table and its
associated dimensions, or you can create a custom subject area by dragging and dropping
individual logical tables and columns into the presentation layer.
To automatically publish subject areas based on the fact tables in your business model, right-
click a business model in the business model and mapping layer of the semantic model and
select Create Subject Areas For Logical Stars And Snowflakes. This will then create a
separate subject area in the presentation layer for each fact table in your business model.
To create a custom subject area based on tables in a single business model, follow these
steps:
1. Using the Oracle BI Administration tool, right-click anywhere that is empty in the
presentation layer and select New Subject Area.
2. When the Subject Area dialog box is shown, type in the name of the subject area.
Leave the other options at the default, and we will look in more detail later in this
chapter at what they are for.
3. Drag-and-drop the logical tables and logical columns that you wish to add to the
subject area from where they are in the business model and mapping layer. Double-
click the presentation column or table to rename it, and use the Properties dialog box
for the presentation table if you wish to change the ordering of the columns.
Notice how when you drag-and-drop a logical table from the business model over to the
presentation layer, it also brings across any logical dimensions associated with the logical
table. If the logical dimension has more than one hierarchy, each hierarchy will become its
own presentation hierarchy, allowing users to select which hierarchy they wish to use when
creating an analysis.

Having a presentation layer separate to the business model gives us an opportunity to create
a further level of abstraction away from the underlying data. We can, for example, split an
existing logical table into two or more presentation tables, or create presentation tables that
contain columns taken from more than one logical table. (Be careful, though, not to create
presentation tables that combine columns from logical tables that need to be joined via a fact
table before they can be displayed together.)
You can also “nest” one set of presentation tables within another to create a set of subfolders
under a master folder. In an example where you have a fact table containing both based
(physically mapped) and derived (calculated) measures, to place each set of measures in
their own folder, follow these steps:
1.Navigate to the presentation layer within your semantic model and locate the subject
area that you wish to work with.
2.Right-click the subject area and select New Presentation Table.
3.When the Presentation Table dialog box is shown, enter a name for the new
presentation table (for example, Base Facts), and within the Description area, type in –
 > (a minus sign, followed by a greater-than sign). Repeat this step for any other
subfolders that you wish to create.

Drag-and-drop the columns that you wish to add to these subfolders, either from the existing
presentation table containing them in the subject area or from the corresponding business
model if you have not already dragged them to the presentation layer.
1.Finally, double-click the subject area you are working within the presentation layer,
select the Presentation Tables tab, and use the Move Up and Move Down buttons to
place the two subfolders under the presentation table that you wish to nest them under.
When you return to the main presentation layer view in the semantic model, you will see just
these two subfolders listed under the main folder. However, when you place the repository
online and view the list of folders within Oracle BI Answers, you will see folders nested and
with presentation columns listed under each subfolder.
Note This can also be done by putting a dash (–) in the name of the presentation table (for
example, – Base Facts)

Step 9: Check the Repository for Consistency Warnings and Errors

At this point, it would probably be a good idea to check your repository for errors and
violations of best practices. To do this, you use the Consistency Check Manager.
You can either use the Consistency Check Manager automatically, as you save changes to
your repository, or manually, for either a single object in the repository or the whole repository.
To use the consistency checker as part of a repository save, with the Oracle BI Administrator
tool open select File | Save and answer Yes to whether you wish to check global consistency.
The Consistency Check Manager dialog box will then be shown, and you can select whether
to display warnings, errors, or best practices violations. Once you have resolved any issues,
you can then save the repository to the file system.

To check consistency manually, again with the Oracle BI Administrator tool open, either select
a single object, right-click it and select Check Consistency, or select File | Check Global
Consistency from the menu to check all objects in the repository. If you receive the message
“Consistency check didn’t find any errors, warnings or best practice violations,” you are free to
move on to the last stage of the process. If not, either resolve the issue and recheck the
repository as before or mark the business model as unavailable and come back and resolve
any issues later on.
When you are first creating a repository, though, you may well end up with warnings or errors
reported by the consistency checker. Here are examples of some common warnings and
errors, and how to resolve them:
 [39002] Level “Sales”.”Products”.”All Products” has no defined key to uniquely
identify members. If you get this warning for a level in a logical dimension hierarchy
that is the grand total (topmost) level, you have forgotten to check the Grand Total
Level check box for it. Go back and select this box, and the warning will go away. If the
level referenced in the warning is not the grand total level, though, this means that you
have forgotten to define the logical level key for it. Double-click the warning in the
 consistency checker to go straight to this level, and then set the key to remove the
warning.
 [39001] Logical table Dim Products has no defined key. This means that while you
have defined a logical table as a dimension table, you haven’t created the table key for
it. Double-click the warning row in the consistency checker, and use the Keys tab to set
the logical key.
 [38018] Business model Sales does not have corresponding subject area. This
error is shown when you have defined a business area but not yet a corresponding
subject area in the presentation layer for it. Create the subject area as you would do
normally, and the error will go away.

Step 10: Publish the Repository as the Default, Online Repository

So far you have been working “offline” with this repository. This means that the Oracle BI
Administration tool is connected to the repository file directly, and only you are able to make
changes to it. It also means that nobody can run analyses using it, as it has not yet been
uploaded to the Oracle BI Server and placed “online,” ready for queries.
To place the repository online, you have to use Oracle Enterprise Manager Fusion
Middleware Control to deploy the repository file to the Oracle BI Server file system, activate
the changes, and then restart the Oracle BI Server component. To do this, follow these steps:
1. Using your web browser, navigate to the URL where Fusion Middleware Control is
located (for example, http://obisrv1:7001/em).
2. When prompted, enter the username and password for an administrator account
(typically, the account details you used when installing Oracle Business Intelligence
Enterprise Edition (OBIEE); for example, weblogic/welcome1).
3. When the Fusion Middleware Control home page is displayed, navigate to the
Business Intelligence folder on the left-hand side under the Farm_bifoundation_domain
menu. Open the Business Intelligence folder and click the coreapplication entry within
it.
4. A set of tabs and subtabs will then be shown on the right-hand side of the screen. Click
the Deployment tab and then the Repository subtab. Click the Lock And Edit
Configuration button at the top of the screen to lock the system configuration for
editing. No other administrator will be able to make configuration changes until you
either activate your changes or release the configuration.
5. Navigate to the Upload BI Server Repository section, and click the Browse button to
locate your repository file on your workstation. Then enter the Repository Password
under the Repository File section, and enter it again in the Confirm Password section.
Note Be sure to enter the password correctly because while the two passwords are checked
as matching at this point, they have not been checked as to whether they are valid for
the repository that you have selected. If they are invalid, you will still be able to proceed
to complete the rest of these steps, but the Oracle BI Server will not restart at the end
and you will need to repeat these steps again.
6. Locate and click the Apply button on the right-hand side of the screen (you may have to
scroll up to see it), and then click the Activate Changes button on the top of the screen.
Be sure to click the Apply button before the Activate button, as otherwise the activation will not
include your changes, and you will not know this until the whole process has completed and
your repository has not been uploaded and made active.
7.You should then receive the message “Activate Changes – Completed Successfully.”
8.Using the two rows of tabs at the top of the screen, select the Capacity Management
tab and then the Availability tab. Within the System Components Availability area,
locate the Oracle BI Servers folder, select it, and click the Restart Selected button.
Click Yes when you see the “Are you sure you want to restart the selected
component?” message, and then wait for the restart process to complete.

After a few minutes, you should then see the message “Restart Selected – Completed
Successfully.” If you receive an error message, repeat steps 3–9, but check carefully that you
have entered the correct password; and if the process fails again, use the Consistency Check
Manager on the offline repository to check that there are no errors that are stopping the
Oracle BI Server from starting with this repository online.
Congratulations! Your repository is now online and ready for use. Now that you know the
basics, let’s take a look in more detail at what else you can do with the repository.

Calculations and Formulas

In the repository creation steps detailed earlier in this chapter, we outlined briefly the way in
which you can create additional logical columns that are based on calculations involving other
logical columns. In fact, you can create a range of calculations involving both logical and
physical columns, which gives you the ability as a developer to considerably enhance your
repository by adding calculations and derivations that may be of use to report developers, and
to also ensure that all report developers use a consistent definition of derived measures: a
“single version of the truth.”
Before we start looking at the range of calculations available, though, let’s take a moment to
think about how the repository and the Oracle BI Server handle calculations.

How the Oracle BI Server Performs Calculations

You can define calculations, or “formulas,” in several places within Oracle Business
Intelligence; if the calculation is only needed for a specific analysis, you can define it in the
analysis criteria; if it will be used across many analyses and by many developers, you can
define it directly in the repository instead. If you define the calculation in the repository, you
can either base it on logical columns in a business model or you can work directly with source
columns and tables in the semantic model physical layer.
However you define your calculations, though, the same process takes place when the BI
Server calculates your data: where possible, the BI Server takes your calculations and
converts them into database-specific SQL functions (“function push-down”), and where the
database lacks the capability to do the calculation directly or when the data used in the
calculation comes from more than one physical database, the BI Server requests the raw data
and performs the calculation itself (“functional compensation”).
Function Push-Down
The Oracle BI Server uses a form of query language called “logical SQL.” Logical SQL is very
much like regular Oracle, Microsoft, and other dialects of SQL, except that in most cases it
does not require GROUP BY, ORDER BY, or aggregation functions because these are
instead defined as part of the business model definition in the repository. Logical SQL lets you
focus on the data retrieval parts of SQL without worrying about joins, ordering, or default
aggregations.
So that a common set of functions can be provided for all data in the semantic model,
regardless of the data source, the BI Server converts logical SQL functions used in the
repository and in analyses into the specific SQL and MDX functions used by each underlying,
supported data source.
For example, a logical column that requests rank of sales would use the logical SQL function
RANK (column_name), which would be translated into RANK () OVER (ORDER BY…) when
pushed down to an Oracle, Microsoft SQL Server, or IBM database. Whether or not the
Oracle BI Server can perform this function push-down is determined by the database type
and version you select for the physical database, as well as what capabilities that database
has been configured for. You can display these by right-clicking a physical database in the
semantic model and selecting the Features tab, as shown in Figure 3-15.

Functional Compensation
If the source database had less functionality, though (such as a Microsoft Access database),
the BI Server “compensates” for this by just requesting the raw information from the source
database, and the BI Server then performs the additional calculations in memory.
Taking this approach allows Oracle Business Intelligence to provide the same calculation
capabilities regardless of the data source, so users can create analyses spanning different
source database types without worrying which sources support which types of calculations. All
of this happens transparently to the end user, though as a developer you need to be aware of
the extraload this can place on both the source database (which potentially has to return a
larger set of raw data to the BI Server) and the BI Server (which has to perform what can be
quite complex calculations).

Logical and Physical Calculations

For calculations defined in the repository, you can use either logical columns or columns from
the physical layer of the semantic model in your expression. You use logical columns when
you define the calculation as part of a logical column’s properties, and you use physical
columns when you define the calculation within a logical table source mapping. Regardless of
where you create the calculation, it will still get pushed down to the underlying data source
(where the data source supports it), but if more than one column is used in the calculation, the
results might get aggregated differently, as logical calculations are carried out following
aggregation, while physical calculations are performed prior to aggregation.
Note This aggregation behavior is only for measure columns in fact tables. For dimensional
calculations, in some cases the logical calculations will get pushed to the grain of the
query, before aggregation.
Example: Comparing the Aggregation Outcome of Logical and Physical Column
Calculations
As an example, consider a situation where you wish to calculate the percentage margin for
sales of bread assortments. The repository has physical columns for revenue and profit, and
you calculate margin by dividing profit by revenue, like this:
  Revenue Profit Margin%
Transaction #1 26.95 4.95 0.18367347
Transaction #2 29.95 14.95 0.49916528
Transaction #3 29.95 80.85 9.95 0.33222037
The revenue and profit figures are modeled as physical columns in the physical layer of the
semantic model, and logical columns are directly mapped to their corresponding physical
ones, with a default aggregation rule of Sum.

Should you now wish to calculate the average percentage margin for these sales as a new
logical column, there are two ways in which you could derive the calculation: you could base
the calculation on the revenue and profit logical column in the logical layer (a “post-
aggregation” calculation), or you could define the calculation directly in the logical table
source mapping, based on the physical columns (a “pre-aggregation” calculation), and
depending on which route you take, you’ll potentially get different numbers returned.
In this example, to create the logical column using a logical calculation, you would do the
following:
1.Using the Oracle BI Administration tool, right-click the fact table and select New
Object | Logical Column.
2.Using the Logical Column dialog box, select the General tab and give the column a
name; for example, Avg. Margin % (Logical).
3.Click the Column Source tab for this new column, and select “Derived from existing
columns using an expression” as the column source type.
4.Click the Edit Expression button to the right of this label. Then, using the Expression
Builder dialog box, enter the expression for the column source, such as (“Sales”.”Fact
Sales”.”Profit” / “Sales”.”Fact Sales”.”Revenue”) * 100.
5.Close the dialog box and copy the new logical column to the presentation layer of the
semantic model. Finally, save the repository, ensure it is online, and then create a test
analysis to view the results.
For the preceding data set, the average percentage margin for sales of bread assortments is
reported as 34.37, which has been calculated in the following way:
sum (all revenue) / sum (all profit) = % margin
You now create a second percentage margin logical column, but this time you’ll use a physical
calculation to perform it. To do this, follow these steps:
1.Using the Oracle BI Administration tool, right-click the fact table, and select New
Object | Logical Column.
2.Using the Logical Column dialog box, select the General tab and give the column a
name; for example, Avg. Margin % (Physical).
3.Click the Column Source tab for this new column, and select “Derived from physical
mappings” as the column source type.
 4.Click the logical table source mapping under this option to select it, and click the Edit
button to edit the table source mapping and ensure that the Show Unmapped Columns
check box is selected.
5.With the Logical Table Source dialog box open, click the blank area next to the new
logical column, and click the Edit Expression button.
6.Using the Expression Builder dialog box, create the calculation; for example:
("orcl".."GCBC_SALES"."Fact_SALES"."PROFIT" /
"orcl".."GCBC_SALES"."Fact_SALES"."REV_AMT")
* 100
7.As this is a physical calculation derived using column mappings, you will need to set
the default aggregation type for the column. With the Logical Column dialog box still
open, switch to the Aggregation tab and set the default aggregation rule to Avg.
8.Again, close the dialog box and copy the new logical column to the presentation layer
of the semantic model. Then save the repository, ensure it is online, and create a test
analysis to view the results of this second query.
This time, the average percentage margin reported back is 33.84. So why do the two
numbers differ?

It has to do with where the percentage margin calculation is done. The logical calculation
sums up all the revenue figures and all the profit figures, and then calculates the percentage
margin based on dividing one total by another. The physical calculation, however, calculates
the percentage margin at a line level, and then averages these figures, giving a different
overall total to the logical calculation. So when you create calculations like these in your
business model, make sure that you do them consistently; also be aware that percentages
and other calculations can come out differently depending on the point at which you
aggregate your data.

A Guide to Repository Functions

So what type of calculations and functions can you make use of in your repository? Oracle
Business Intelligence provides you with a set of logical SQL functions that it translates into the
equivalent physical SQL and MDX functions used by each supported data source. Where a
data source does not have an equivalent function (because the logical SQL function is unique
to Oracle Business Intelligence or because the data source does not support more advanced
calculations), the BI Server requests the basic data set required to perform the calculation
and does the calculation itself.
A comprehensive guide to logical SQL functions can be found in the online product
documentation, and many of them will be familiar to you through working with Oracle,
Microsoft, and other databases, as well as tools such as Microsoft Excel. However, a few are
specific to Oracle Business Intelligence, and others may have a different syntax than what
you are used to, so it is worth reviewing them at a high level if you are new to the platform.
Note Not all logical SQL functions are available for use within the repository. See later
chapters for functions that can be accessed when creating analyses using Oracle BI
Answers, and see the product documentation for a full logical SQL reference guide.

Mathematical, String, and Calendar/Time Functions

Logical SQL contains the usual set of mathematical (ABS, COS, LOG, MOD, TRUNCATE,
and so on) and string (CONCAT, LOCATE, equivalent to INSTR in Oracle, SQL, LENGTH,
ASCII, and so on) functions found in most physical databases. You may find that the function
names differ or that the syntax differs slightly from what you are used to with your particular
physical database, but most logical function names are reasonably obvious, and the online
documentation details the syntax should you need clarification.
Logical SQL has a number of functions used for returning the current date and time, and for
manipulating date/time data types and converting to and from them. The syntax for these
differs from most physical database platforms, though, and functions in this category that are
worth noting include the following:
 CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP (integer) These
are for returning the current date, time, or timestamp (with a specified number of digits
of precision), based on the system clock.
 DAYNAME (dateExpr), DAYOFWEEK (dateExpr), MONTHOFQUARTER (dateExpr),
DAYOFQUARTER (dateExpr), MONTHNAME (dateExpr), SECOND (timeExpr), and
YEAR (dateExpr) These and other similar functions are used to extract elements of a
date, time, or timestamp for use in a calculation.
 TIMESTAMPADD (interval, intExpr, timestamp) and TIMESTAMPDIFF (interval,
timestamp1, timestamp2) These are for adding a specified number of days, months,
seconds, or other intervals to a timestamp, or for calculating the number of intervals
between two timestamps.

Conversion Functions
Logical SQL comes with a number of conversion functions for converting between data types,
dealing with missing values, or performing Oracle BI–specific functions such as making use of
variables or switching between columns:
 IFNULL (expr, value) This provides a value should the column contain a NULL (a
missing value) and is equivalent to Oracle’s NVL and other similar functions; for
example, IFNULL (company_name, ‘Allied Bakers’).
 CAST (expr | NULL as data_type) This converts between two different data types,
converting, for example, string columns into dates using the expression CAST
(commence_date AS DATE).
 VALUEOF (variable_name) This is unique to logical SQL and allows a calculation to
reference the value held in a repository variable; for example, VALUEOF
(NQSESSION.home_store) accesses the value contained within the repository session
variable home_store, which would typically be set using an initialization block at the
start of a new user session. There are various variable name formats that you need to
use, depending on the type of repository variable being used, all of which are detailed
later in this book.
 INDEXCOL (integer, expr_list) This returns the column or expression in expr_list,
starting with 0 for the first entry in the list, based on the integer value passed to the
function. This function can be useful, for example, when you wish to dynamically select
a column from a list based on a value in a variable, allowing a single function to make
use of a set of columns with the particular column selected at query time. For example,
consider a situation where you wish to display data in either US dollars or UK pounds,
based on the currency code set for a user. For users who wish to display values in US
dollars, the session variable PREF_CURRENCY is set to 0, while for UK pounds it is
set to 1. You could then use the INDEXCOL function to display the correct currency
 amount based on this variable:
INDEXCOL (VALUEOF(NQ_SESSION.pref_currency), usd_amount, gbp_amount)
 CHOOSE (expr1, expr2, expr3 … exprN) This performs a similar function to
INDEXCOL but selects the column to be returned based on the first one in the list that
the user has permissions to view, rather than an index value. For example, if four
columns are defined that contain product data at total, category, type,
and product_name level, and users are only given permission to access those columns
appropriate to their role, the following function would return the first column in the list
that the user had permission to view:
CHOOSE (product_total, product_category, product_type, product_name)
This function is again useful for displaying one particular column for a user based on
the user’s role, this time based on security permissions rather than an index value
stored in a variable.

Aggregate and Running Aggregate Functions

Logical SQL has a number of aggregate and running aggregate functions equivalent to
analytic functions found in databases such as Oracle Database. A subset of these, detailed
here, are available for use within the Oracle BI Repository, while others, detailed in a later
chapter, are available within Oracle BI Answers when you create an analysis:
 RANK (numExpr), TOPN (numExpr, integer), MAX (numExpr), NTILE (numExpr,
numTimes), and PERCENTILE (numExpr) These and others are standard
aggregation functions and are typically translated to the equivalent analytic functions
on the source database platform (where supported).
 MAVG (numExpr, integer), RSUM (numExpr) These and other moving and rolling
aggregation functions also typically translate to analytic functions on the source
database.

Time-Series Functions
Logical SQL has three time-series functions that allow you to calculate the value of a measure
a set number of periods ago, up to a particular period, or between two arbitrary periods. All of
these functions require you to create a “time dimension,” a logical dimension based on your
own calendar table that has one or more chronological keys defined.
A chronological key has to satisfy three requirements:
 The members (values) in it must be sequential, with their own natural order and with
members equally spaced out.
 The member list must be complete (that is, no missing months, days, and so on).
The Oracle BI Server then uses chronological keys to generate mathematically correct time
period predictions, working out, for example, that January, 2011 plus two periods would equal
March, 2011. Ideally, you will create chronological keys for each level in the time dimension,
but you can get by with just a chronological key at the bottom (leaf) level. If you perform a
time-series calculation at a hierarchy level that does not contain a chronological key, however,
the BI Server is forced to aggregate up from the next level down that does have such a key,
which can cause performance problems.
Earlier in this chapter we looked at how a logical dimension with a basic, balanced level-
based hierarchy was set up. Time dimensions are a variation on these types of dimension but
with additional metadata to assist with time-based calculations. Having a time dimension is a
prerequisite for performing time-series functions, so to set one up, follow these steps:
1. Ensure that you have a logical table within a business model that contains calendar
data columns (months, days, years, quarters, and so on), and that you have not
previously associated with a logical dimension.
2. Using the Oracle BI Administration tool, navigate to the business model within the
business model and mapping layer that contains the calendar logical table, right-click
the business model, and select New Object | Logical Dimension | Dimension With
Level-Based Hierarchy.
3. When the Logical Dimension dialog box is shown, type in the name (for example,
Times), and within the Structure section, select the Time check box.

Now create the levels within the hierarchy as you would do for a regular level-based logical
dimension, creating logical level keys for each level as usual. A typical hierarchy for a time
dimension would contain levels for all times, years, quarters, months, and days.
1.As you have created a time dimension, you now have to designate at least one of the
logical level keys as a chronological key, each of which must pass the three tests
described earlier. To define a chronological key, double-click the level for which you
wish to define the key, and when the Logical Level dialog box is shown, select the Keys
tab. Locate the key in the key list that you wish to designate as the chronological key,
and select the Chronological Key check box for it. You may have to scroll over to the
right to see this check box.

Repeat step 5 for any other chronological keys you wish to create for the time dimension.
Once you have defined at least one time dimension, you can then make use of Oracle
Business Intelligence’s time-series functions in your business model. These functions are
listed here:
 AGO (expr, time_dimension_level, periodoffset) This is used for calculating the
value of a measure a set number of time dimension levels (periods) ago. For example,
this would calculate the value for revenue, one month ago:
Ago (“Sales”.”Fact Sales”.”Revenue” , “Sales”.”Times”.”Month” , 1)
Substitute other time dimension levels (ideally with chronological keys defined for
them) and numbers of offset periods for other time-offset calculations.
 TODATE (expr, time_dimension_level) This is used for calculating the period to date
value for a measure; for example, year to date:
TODATE (“Sales”.”Fact Sales”.”Revenue” , “Sales”.”Times”.”Year”)
 PERIODROLLING (measure, x, y [,hierarchy]) This is a new time-series function
introduced with the 11g release of Oracle Business Intelligence. It can be used to
calculate the value of a measure from offset x to offset y, with x typically being a
negative figure to indicate the past, and y being a number of periods on from x,
potentially into the future. The period (time dimension level) type is either inferred from
the period used in the analysis, or it can be specified by use of the AGGREGATE AT
function. For example, to calculate the value of revenue for the previous month, the
current month, and the next month, use the following formula:
PERIODROLLING (“Sales”.”Fact Sales”.”Revenue”, -1, 1)
The keyword UNBOUND can be used to specify all periods that are available, that is,
to the current period. For example, to calculate the value of revenue for the previous
 six months to now (assuming no data exists beyond the current period), use the
following formula:
PERIODROLLING (“Sales”.”Fact Sales”.”Revenue”, -6, UNBOUND)
The optional hierarchy clause can be used to specify a particular hierarchy if the logical
dimension has more than one (for example, Times – Fiscal Year). For situations where
you wish to directly specify the dimension level to which to apply the PERIODROLLING
function, rather than infer it from the grain of the analysis, the AGGREGATE AT
function can be embedded within the PERIODROLLING function, like this:
PERIODROLLING (AGGREGATE(“Sales”.”Fact Sales”.”Revenue” AT quarter), -2,
UNBOUND)
Care should be taken with the use of time-series functions, as the Oracle BI Server can
generate quite complex and expensive SQL to return values using these functions. Because
regular database SQL cannot normally return data from two time periods simultaneously
using a single WHERE clause, the Oracle BI Server typically generates two or more queries
to retrieve data for all time periods, the result sets for which it then either joins together
internally in memory, or has the source database join if it has the capability to do so.
If you plan to make extensive use of time-series functions, consider using a multidimensional
database such as Oracle Essbase, which supports time-series functions natively and can
perform such calculations using far fewer database and system resources.

Evaluate (Database) Functions

While logical SQL comes with a large number of functions, including ones not normally found
in physical database SQL, you may have a particular function that is unique to your particular
database platform (or indeed one that you have written yourself) that you wish to make use
of. Evaluate, or database, functions allow you to call native database SQL functions, along
with any required parameters, and use them either to return a scalar (single) value, return an
aggregatable value, make use of a database analytic function, or return a Boolean value for
use in a query predicate:
 EVALUATE (‘db_function(%1…%N)’ [AS data_type] [, column1, columnN]) This is
typically used with logical table attribute (dimension) columns to apply functions to
modify the column value. For example, the following would return the third to the fifth
characters contained within the product_category column when used with an Oracle
database:
EVALUATE (‘SUBSTR(%1,%2,%3)’, “Dim Products”.”Product Category”, 3, 5)
The first parameter in the EVALUATE function represents the database function name
(either built in, or user-defined), followed by placeholders for the function parameters.
The remaining parameters are then substituted for these placeholders when pushing
the function down to the underlying physical database.
 EVALUATE_AGGR(‘db_agg_function(%1…%N)’ [AS data_type] [, column1,
columnN) This is typically used with fact table measures, and it returns a value that
has been aggregated and can be used along with a GROUP BY clause. Measures
defined using EVALUATE_AGGR must have their default aggregation rule set to
Evaluate_Aggr so that the Oracle BI Server knows that an external function provides
aggregation for this measure. Therefore, when modeling the repository, this function
can only be used with physical columns within a logical table source mapping and
cannot be used with a logical column expression.
For example, the following function uses the Oracle Database STDDEV built-in function to
calculate the standard deviation for the revenue measure:
Evaluate_Aggr (‘STDDEV(%1)’, “orcl”.”
“.”GCBC_SALES”.”Fact_SALES”.”FCAST_REV_AMT”)
 EVALUATE_ANALYTIC (‘db_function(%1…%N)’ [AS data_type] [, column1,
columnN]) This is used for calling database analytic functions; for example, the
following calculation returns the dense rank based on the revenue measure:
EVALUATE_ANALYTIC (‘dense_rank() over(order by %1 )’ AS INT, (“Sales”.”Fact
Sales”.”Revenue”)
 EVALUATE_PREDICATE (‘db_function(%1 …%N)’, [, column1, columnN) This is
typically used within an analysis filter or within a logical table source filter clause, and it
returns a Boolean value based on an expression evaluation. Its most common use is
when handling data from an Oracle OLAP source, using database views over
multidimensional objects in an analytic workspace.

Lookup Functions
A new type of function introduced with the 11g release of Oracle Business Intelligence is the
lookup function. Lookups are a concept familiar to most BI and data loading developers,
andtypically they involve retrieving a descriptive value for a given ID value. You can do this
fairly simply in the Oracle BI Repository by editing a logical table source mapping using the
Add (+) button to add the physical tables to the table source mapping, and then using lookup
columns provided by the new mapped physical table to populate corresponding ones in the
logical table.
The lookup function introduced with Oracle Business Intelligence 11g is for a different
scenario, though, in which, for one reason or another, you wish to isolate one part of a query
from the main part.
There are two types of lookup functions that you can use in Oracle Business Intelligence 11g:
 LOOKUP (DENSE (lookupColumn, commaSepExprs)) This is used for lookups
where you can be certain that a lookup value will be returned for every input value
(equivalent to an inner join in SQL).
 LOOKUP (SPARSE (lookupColumn, alternateColumn, commaSepExprs)) This is
where you cannot be certain that you will always get a lookup value returned
(equivalent to a left outer join in SQL).
The LOOKUP function can also be used with both physical columns and logical columns
when used in conjunction with a logical table designed as a lookup table. Lookup tables are
just like regular logical tables; however, by designating them as lookup you remove the need
for them either to play the role of fact tables (with other tables joining to them) or dimension
tables (which join directly to fact tables).

To explain how the LOOKUP function works, let’s take a look at an example of the lookup
function in use, where we wish to perform a currency conversion.
When converting currency measures in a fact table from a base currency to a reporting
currency, a common approach is to create an exchange rate table in the physical layer, join
this table to the physical table containing the fact table measures in the logical fact table
source, drag the exchange rate to the logical fact table, and then multiply the base currency
with it to obtain the reporting currency amount. This converts base currency to reporting
currency successfully, but it has the disadvantage of including the join to the exchange rate
physical table into the main SQL GROUP BY clause, giving you a physical SQL query looking
like this log file excerpt:

WITH
SAWITH0 AS (select distinct T1114.EXCH_RATE as c1,
T1128.PROD_CAT_DESC as c2,
T1173.MONTH_YYYYMM as c3
from
GCBC_SALES.TIMES T1173,
GCBC_SALES.PRODUCTS T1128,
GCBC_MAILORDER_SALES.EXCHANGE_RATES T1114,
GCBC_MAILORDER_SALES.UK_MAILORDER_SALES T1120
where ( T1114.MONTH_YYYYMM = T1120.MONTH_YYYYMM ...

If it were possible instead to perform the main aggregation of the query as one step, and then
apply the exchange rates to the final, aggregated data set, your query might perform better
and use fewer database resources. This would be where you could use a dense lookup
function. By retrieving the exchange rate from either the physical exchange rate table or from
a logical lookup table, let’s say you use an expression such as the following, where the
Lkp_EXCHANGE_RATES table contains a single exchange rate between the base currency
and the reporting currency, keyed on the month and year:

LOOKUP (DENSE "orcl".."GCBC_MAILORDER_SALES"

."Lkp_EXCHANGE_RATES"."EXCH_RATE" ,
"orcl".."GCBC_MAILORDER_SALES"
."Fact_UK_MAILORDER_SALES"."MONTH_YYYYMM" )

This would instead result in a physical SQL query looking like the following excerpt:

WITH
SAWITH0 AS (select sum(T1120.LOC_REV_AMT) as c1,
T1128.PROD_CAT_DESC as c2,
T1173.MONTH_YYYYMM as c3
from
GCBC_SALES.TIMES T1173,
GCBC_SALES.PRODUCTS T1128,
GCBC_MAILORDER_SALES.UK_MAILORDER_SALES T1120
where ( T1120.MONTH_YYYYMM = T1173.MONTH_YYYYMM
and T1120.PROD_ID = T1128.PROD_ID )
group by T1128.PROD_CAT_DESC, T1173.MONTH_YYYYMM),
...
SAWITH2 AS (select T1114.EXCH_RATE as c1,
T1114.MONTH_YYYYMM as c2
from
GCBC_MAILORDER_SALES.EXCHANGE_RATES T1114),

As you will see, the access to the exchange rate table has now moved outside of the main
GROUP BY in the SQL query, potentially improving the efficiency of the query.

System Functions
In some situations you may wish to include the user’s login details in a query, perhaps to help
filter the data returned by the database. In others, you might want to return the name of the
default subject area. To do this, you can use the two following system functions that do not
have any arguments:
 USER This returns the login name for a user.
 DATABASE This returns then name of the default subject area.

Hierarchy Functions
There are also a number of functions introduced with the 11g release of Oracle Business
Intelligence that you cannot normally access through the repository or through your own
analyses in Oracle BI Answers but that are used by various parts of Oracle Business
Intelligence to traverse hierarchies, create hierarchical groups, and perform aggregations.
These include ISLEAF, ISPARENT, and ISDESCENDENT, and more details on these are
included in the product documentation; however, you would not normally use them yourself in
a column definition or Oracle BI Answers analysis.

The Calculation Wizard

In addition to creating calculations manually, you can also use the Calculation Wizard to
automatically create calculations based on two measures and to create standard sets of
derived measures. The Calculation Wizard is typically used in conjunction with time
dimensions and time-series calculations, where your repository already contains time and
date-offset measures and you now wish to generate standard derivations such as change and
percentage change.
To see how the Calculation Wizard is used in practice, let’s walk through a simple example.

Example: The Calculation Wizard in Use

The existing business model with which we will use the Calculation Wizard has a fact table
containing four measures: Revenue, Cost, Sale Amount, and Sale Amount Month Ago. The
Sale Amount Month Ago measure was defined using the AGO time-series function, and we
will now use the Calculation Wizard to create standard derivations based on it and on the
Sale Amount measure:
1. To start using the Calculation Wizard, right-click the first logical column fact table
measure (in this case, Sale Amount) and select Calculation Wizard.

The Calculation Wizard – Introduction dialog box will then be shown. Click Next to proceed.
 1.The Calculation Wizard – Select Columns dialog box will then be shown. For every
column you select, the Calculation Wizard will then create a set of derived measures,
comparing them to the original logical column fact table measure. Select the
measure(s) that you wish to compare against the first measure (for example, Sale
Amount Month Ago), and click Next to proceed.
2.The Calculation Wizard – New Calculations dialog box will then be shown.

From the following list, select the calculations (derivations) that you wish to create:
Change This subtracts the second column from the first (for example, Sale
Amount – Sale Amount Month Ago).
Percent Change This subtracts the second column from the first and shows
the value as a percentage; for example: (100.0 * (Sale Amount – Sale Amount
Month Ago) / Sale Amount Month Ago).
Index This divides the first column by the second; for example: (1.0 * Sale
Amount / Sale Amount Target).
Percent This divides the first column by the second and expresses the result
as a percentage; for example: (100 * (Sale Amount / Sale Amount Target )).
2.Each of the preceding calculation types has special cases, such as when one of the
two numbers is missing, null, or above or below a certain value. For each calculation,
click it and use the panel on the left-hand side of the wizard to specify any special
handling for it.
3.Click Next, and then click Finish to complete the wizard. New calculations to match
your selections and special handling instructions will then be created in your business
model.

Advanced Repository Modeling Scenarios

In the preceding example, you have modeled a repository based on a single data source that
is already organized dimensionally. This is a “best case” scenario where your data is probably
in a single data mart or a data warehouse and all you are really doing is exposing this
dimensional model through Oracle Business Intelligence’s metadata layer so that you can
analyze it in a dashboard.
Most projects aren’t as simple as that, though. You might want to combine data from more
than one data source, and some of this data may not be organized dimensionally in a star
schema. To make queries run faster, your DBAs may have pre-aggregated some of your data
into summary tables, and you may wish to incorporate these summary tables into your
repository model.
You may have a transactional data source that you wish to use alongside your data
warehouse in analyses so that you can combine historical and real-time data in the same
query. Your hierarchies may not be simple, balanced, level-based hierarchies; instead, they
may be organized in a parent-child (value-based) fashion or with ragged or skip levels. You
might also want to create business models with more than one fact table and create analyses
across all of them even when they do not necessarily share the same dimensionality.
For the remainder of this chapter, we will look at some of these more advanced repository
modeling concepts and see how the Oracle BI Administrator tool can work with more complex
relational data structures.

Repository Development Tools and Concepts

Before we get on to more advanced concepts such as federation, fragmentation, and
aggregates, though, it’s worth taking a moment to look at a few Oracle BI Administration tool
concepts that are often misunderstood by beginners. We’ll start by looking at a concept more
or less unique to Oracle Business Intelligence and something that confuses a lot of beginners:
logical table sources.

Logical Table Sources

When you create your first logical table in the business model and mapping layer, once you
start dragging and dropping physical columns into your various logical tables, you may notice
that entries start appearing in the Sources folder under your logical table. For simple business
models that map to a single dimensional physical database, each folder may contain a single
entry, but more complex models sourced from multiple physical tables may result in many
entries within the Sources folder, each one representing a different possible data source for
your logical table. So what do these entries represent?

Each entry within the Sources folder represents a logical table source. A logical table source
is a set of mappings between logical columns in your business model and physical columns in
the physical layer of the semantic model. When you drag-and-drop columns from the physical
layer to a logical table, the Oracle BI Administration tool automatically creates the logical table
source for you, or you can create it yourself if you wish to create these mappings manually. In
cases where the physical tables you wish to map into the logical table are in fact part of a
physical database already mapped into the logical table via an existing logical table source, in
most cases you should extend the existing logical table source by editing it and adding it to
the existing logical table source mapping, but in some instances you will actually want to
create whole new logical table sources.
As an example, suppose that you have created a logical table called Fact Sales that is
primarily sourced from a physical (alias) table called Fact_SALES, within a physical database
and schema called orcl.GCBC_SALES. You create the outline (empty) logical table in the
business model and mapping layer and then add the columns by dragging and dropping
physical columns from the corresponding physical table to the logical Fact Sales table. If you
double-click the single entry under the Fact Sales Sources folder and select the Column
Mapping tab, you’ll see that it contains the column mappings between these two metadata
layers, as shown in Figure 3-16.

It is, however, possible to create more than one logical table source mapping for a logical
table. This situation occurs when, as described in more detail later in this chapter, you wish
to add additional logical columns to a logical table that are sourced from a second, separate
physical database, and you cannot of course just join these two tables together using a
traditional SQL join clause.
Instead, the Oracle BI Server will need to generate a second, separate physical SQL query to
obtain data from this second database’s table, and the BI Server then needs to join together
the two sets of returned queries before presenting the results back to the user using an
Oracle BI Server capability called “horizontal federation.”
Another reason that a logical table might have more than one logical table source is if the
same set of logical columns can be sourced from physical databases containing data of
differing levels of granularity. For example, the initial physical data source that you map in
may be at the lowest level of detail, such as with transaction-level data, while a second
source may contain the same data but pre-aggregated into a summary table or OLAP cube.
In this case, the summary-level table source would need to be configured to be used only by
the Oracle BI Server when the user requested an analysis at an appropriate level of
aggregation using a feature known as “vertical federation.” Figure 3-17 shows how these three
sets of logical table source column mappings then provide the Oracle BI Server with three
possible physical SQL statements that could be used to return data for a logical table, with the
Oracle BI Server choosing which ones to use based on the particular columns requested and
the level of detail requested by the analysis.

As mentioned, logical table sources can be configured to be used only when an analysis
requests data at a particular level of detail or aggregation, and this setting is controlled by
editing the table source properties and selecting the Content tab. This ability requires you to
define logical dimensions in your business model, and if this logical table source provides the
only source for a particular column and the user requests the column at a more detailed level
of granularity, the Oracle BI Server will return a NULL or an error for the column, depending
on what fact measures are chosen.
Typically, though, another table source will provide data at this lower level of detail, and the
Oracle BI Server will switch to this other table source when the user requires data at the lower
level of detail.

In addition to containing direct logical column to physical column mappings, logical table
source mappings can contain transformations (expressions) that use the same logical SQL
syntax used in logical column definitions. These transformations are often called “physical
calculations” and are performed before column data is aggregated, as opposed to after
column aggregation, as is the case with logical column calculations. See the section titled
“Logical and Physical Calculations” within the wider “How the Oracle BI Server Performs
Calculations” section for more details on this topic.

Online and Offline Repository Development

In the repository development example used in the “Creating the Oracle BI Repository”
section earlier in this chapter, you worked offline with the repository file. That example
described a situation where the Oracle BI Administration tool was connected directly to a
repository (RPD) file and any changes you made to the repository were visible to you only
and saved to the repository when you saved and closed the file. If you open a repository file
offline, but the repository is also the online, default repository for your Oracle BI Server, the
offline repository will open in read-only mode only.
To open a repository offline, start the Oracle BI Administration tool from the Windows Start
menu. In the Administration tool, select File | Open | Offline, and then select the repository file
to open.

When you open a repository offline in this way, you are opening the repository in the
traditional binary format. You can, however, save and then open the repository in the MDX
XML format, which is typically used when checking a repository in, and out, of a source
control system. MDX XML format repositories, and integration with source control systems,
are covered in detail inChapter 11.
After you have selected the repository file to open, you are then prompted to enter the
repository password. The repository password secures the repository file, is set when you first
create the repository, and can be changed after you have successfully opened the repository
file from the Oracle BI Administration menu when the repository is accessed offline (File |
Change Password).
Note that, unlike the 10g release of Oracle Business Intelligence, you should not open
repository files directly by double-clicking them to open them directly within the Oracle BI
Administration tool, as this bypasses a script that the Oracle BI Administration tool requires to
configure various environment variables and will stop you from accessing physical database
sources. Instead, always start the Oracle BI Administration tool from the Windows Start menu
or run the script itself manually by calling the following command-line script (amending path
names for your particular environment):

C:\WINDOWS\system32\cmd.exe "/cC:\Middleware\instances\instance1\
bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd
coreapplication_obis1 2 &&
C:\Middleware\Oracle_BI1\bifoundation\server\bin\
AdminTool.exe"

In addition to offline repository development, you can also connect online to a repository file.
In this case, you connect to the repository not directly to the file, but through an ODBC
connection to the Oracle BI Server, which in turn connects you to the default, online repository
for that Oracle BI Server. When you work with a repository file online, you check in and check
out repository objects as you work with them, and changes that you save are visible to users
once the Oracle BI Presentation Server’s copy of the repository has been refreshed and are
visible to other developers after they reconnect online to the repository.
To connect to a repository online, open the Oracle BI Administration tool and select File |
Open | Online from the application menu. You will then be presented with the Open Online
dialog box, into which you should enter the repository password and the connection details for
an account that has been granted the BI Administrator application role (or a comparable role if
you have created additional application policies).

When you connect to a repository online, other developers can also be connected online and
edit the same repository, checking in and checking out objects as they are edited. Oracle
Corporation states that best practice is for only a single developer to be working online with
each repository, as concurrent editing can cause performance issues for both developers and
users, although the 11g release of Oracle Business Intelligence does support up to
five developers working online at the same time (as opposed to a single developer in Oracle
Business Intelligence 10g). However, be careful when editing a repository online because
while the Oracle BI Administration tool tries to stop you from saving an invalid repository, if
you do manage to do so it may bring down the Oracle BI server and stop
other developers and users from working.
Editing a repository online does, however, give you access to a few functions that are not
available when working offline:
Managing scheduled jobs
Managing user sessions
Managing the query cache
Managing clustered servers

Example: Uploading a Repository to the Oracle BI Domain

To upload a repository and place it online, use Oracle Enterprise Manager Fusion Middleware
Control to perform the following steps:
1. Ensure that you have saved any offline changes to the repository and that you have
used the Consistency Check Manager to ensure that there is at least one subject area
in the repository that is valid and available for querying.
2. Using your web browser, navigate to Fusion Middleware Control using the URL
http://[machine_name]:7001/em. For example:
http://obisrv1:7001/em
Then enter the username and password of an account that has been assigned the
BIAdministrator application role or a role with equivalent permissions.
3. When the Fusion Middleware Control website opens, navigate to the
Farm_bifoundation_domain menu on the left-hand side of the screen, open the
Business Intelligence folder, and click the coreapplication menu entry. Then, when the
coreapplication screen opens, click the Deployment | Repository tab and subtab to
open the BI Server Repository screen.
4. Click the Lock And Configuration button within the Change Center area to lock the
configuration of your Oracle BI Domain.
5. Click the Browse button within the Upload BI Server Repository area of the screen and
navigate to the location on the file system when you have saved your offline repository.
Double-click the repository file to select it, and then enter the repository password twice
in the area under the repository file name.
6. Click the Apply button to record the selection of the repository file. Then click the
Activate Changes button, and Fusion Middleware Control will upload the repository to
the Oracle BI Server file system area and edit the internal configuration file
(NQSConfig.INI) that references the file location.

1. Note that this configuration file and others used by the BI Server component are
described in more detail in Chapter 5.
2. To make the Oracle BI Server start using your new repository, switch to the Availability
Processes tab and subtab within Fusion Middleware Control and navigate to the BI
 Servers folder. Select the folder by clicking the box to the left of the folder name, and
click the Restart Selected button. Click Yes when prompted, and then check that the
activity completes successfully.
Note If the restart of the BI Server components does not complete successfully, check the log
files for the failure reason. In most cases, it is because you did not enter the correct
repository password or the repository is invalid and cannot be brought online.

Administration Tool Utilities

There are a number of utilities that are available to you from the Tools | Utilities menu in the
Oracle BI Administration tool. These are discussed next.

Replace Column or Table in Logical Table Source

Changes to the tables and columns in your physical data sources may require you to update
the logical table source mappings in your semantic model to accurately reflect these changes.
This update may be required because you are moving from one source database schema to
another, or you may be switching database types and require your mappings now to point to
this new source.
The Replace Column or Table in Logical Table Source utility works in the following way:
1. Using the Oracle BI Administration menu, select Tools | Utilities | Replace Column Or
Table In Logical Table Source.
2. Then select whether you wish to change an individual physical column or a complete
physical table.
3. Select the physical column or physical table that you wish to change from, as well as
the one you wish to change to.
4. Review the list of physical columns that will be affected by the change.
5. Review the list of logical table sources that will be updated by the change.
6. Click Finish to complete the process and make the changes.
Once complete, any logical table sources that reference your original physical column or
physical table will reference the new ones instead.

Oracle BI Event Tables

The Oracle BI Server query cache, when enabled, stores the results of analyses and uses
these to return results quicker to subsequent analyses. For BI systems that are sourced from
a data warehouse or other data source loaded using an ETL (extract, transform, load)
process, the query cache can significantly improve the performance of queries by minimizing
round-trips to the database data source.
One drawback with the query cache, though, is that it is not aware of when the underlying
data source has been updated, potentially leaving its cached results out of date, or “stale.” To
address this issue, there are several ways in which the query cache can be invalidated or
“flushed,” one of which is the event polling table.
Starting with the 11g release of Oracle Business Intelligence, the event polling table is
automatically created for you as S_NQ_EPT within the BIPLATFORM schema set up by the
Repository Creation Utility. The Oracle BI Event Tables tool allows you to register this table
with the repository so that the Oracle BI Server can then start polling it to see which tables
need to have their entry in the query cache flushed. (Note that there are several other ways to
manage cache entries, detailed later in this book.)
To use the Oracle BI Event Polling Tables tool, do the following:
1. Using the Oracle BI Administration tool, use the Import Metadata Wizard (File | Import
Metadata) to import the S_NQ_EPT table into the semantic model physical layer from
the BIPLATFORM schema (for example, DEV_BIPLATFORM).
2. Select Tools | Utilities | Oracle BI Event Tables from the menu.
3. Use the Oracle BI Event Tables dialog box to select the S_NQ_EPT table from the list
of physical tables.
4. Use the “Polling Frequency every … minutes” text box to enter the number of minutes
that the Oracle BI Server should wait between checks on the entries in this table.
5. Choose File | Save to save your changes. This configuration change will take effect as
soon as you save the repository if it is running online, or whenever you upload the
repository and take it online, if it is currently offline.
See Chapter 5 for more details on query caching.

Externalize Strings
For BI systems that are used by users in different regions, it can be useful to display table,
column, subject area, and hierarchy names from the presentation layer in the user’s particular
language. The externalize strings feature allows the administration to select particular
presentation layer objects and then either store names or descriptions for these objects in an
external file, which you can then populate with versions of these names and descriptions in
particular languages.
Setting up the externalize strings feature requires two steps:
1. First, select the presentation layer objects for which you wish to externalize names or
descriptions by right-clicking the object in the Oracle BI Administration presentation
layer and selecting either Externalize Display Names | Generate Custom Names or
Externalize Descriptions | Generate Custom Descriptions. Nothing will be displayed on
the screen when you make these selections, but the list of objects you have selected
will be recorded by the Oracle BI Administration tool for use later.
2. Once you have marked the presentation layer objects that you wish to externalize
strings for, run the Externalize Strings utility by selecting Tools | Utilities | Externalize
Strings from the Oracle BI Administration menu. When the Externalize Strings dialog
box is shown, select those subject areas that you wish to externalize into a file (you
can select them all or just single ones, with each one being externalized into a
separate file), and then click Save to save the externalized strings files.

Rename Wizard
When you create logical columns in your business model by dragging and dropping physical
columns, your logical columns inherit the physical column names. Physical column names are
often restricted by the source database to uppercase letters and may use underscores (_)
and other characters in the place of spaces. If you wish to replace these system-generated
logical column names with more user-friendly ones, you can either manually edit them
yourself or you can use the Rename Wizard to automatically rename them using rules that
you specify so that you can change, for example, a logical column name imported from your
physical layer as PRODUCT_NAME to instead be called Product Name.
To use the Rename Wizard, follow these steps:
1. Using the Oracle BI Administration menu, select Tools | Utilities | Rename Wizard.
 2. Using the Rename Wizard – Select Objects dialog box, select either the presentation
layer objects or business model and mapping layer objects that you wish to rename.
Click the Add button to add just the selected object, or the Add Hierarchy button to add
the object plus all its dependent (child) objects.
3. On the Rename Wizard – Select Types screen, check or uncheck those object types
contained in the object set you selected that you wish to rename.
4. On the Rename Wizard – Select Rules screen, create the rules that you wish to apply
to your selected objects; for example, you may wish to capitalize all first letters, change
letters to lowercase, add spaces, or replace one character with another. Click the Up
and Down buttons to change the sequence in which the rules are applied.
5. Click Next to review the changes that will be applied, and then click Finish to make the
changes.

Update Physical Layer

The tables, columns, and other objects in your source databases may have changed or been
removed since you performed your initial metadata import. The Update Physical Layer utility
performs a check on the physical databases listed in the physical layer of the semantic model
and can update tables and column definitions that have changed, as well as remove tables
from the physical layer that are no longer present in the source database. Note that this utility
does not alter table keys in your physical layer, nor does it add tables or columns that are not
in the semantic model but are now present in the source database.
To run the Update Physical Layer Wizard, do the following:
1. From the Oracle BI Administration menu, select Tools | Utilities | Update Physical
Layer.
2. There will normally now be a short pause while all the source databases are scanned.
The Update Physical Layer Wizard will then be displayed, and the Select Connection
Pool screen will be displayed if you have more than one connection pool defined in
your physical layer. Use this screen to select which connections to use to connect to
your source databases.
3. The Update screen will then be displayed. Every object that is present in the physical
layer but changed or missing in the source database will then be displayed. Select the
check box for each item you wish to update.
4. The Checkout screen will then confirm the action to perform on each physical layer
object. Click Back to return to the previous screen and amend your choice of objects to
update, or click Finish to perform the selected actions.
The Update Physical Layer Wizard relies on your connection pool names staying constant
between your initial metadata import and your physical model now. It looks for objects within
the same connection pool name, with the same object name, and checks to see if the object
still exists in the source database or if the data type or length has changed. As an alternative
to this utility, you can select File | Import Metadata from the Oracle BI Administration menu
and reimport your source database metadata, but doing so will automatically overwrite any
objects that have changed since import and will not prompt you to confirm these changes
before making them.

Repository Documentation
Given that there are three layers of object metadata in the repository, it is useful to be able to
generate a “lineage” report that shows how each presentation layer object is sourced through
the repository model, and equally how the removal or change of a physical layer object could
affect downstream objects in the repository. The Repository Documentation utility goes some
way to providing this lineage information, though it does not extend to Oracle BI Presentation
Server objects and really requires you to load this information into a database to make much
use of it.
To generate this repository documentation, take the following steps:
1. From the Oracle BI Administration menu, select Tools | Utilities | Repository
Documentation.
2. When prompted, select a file system location and document format to output to.
The Repository Documentation utility will then generate a file that, for each presentation layer
object, lists the presentation table, business model and mapping layer table and column, any
derivations, and the same for the corresponding physical layer objects and derivations. You
can then load this information into a relational database to track the lineage of your objects
and any changes to them over the development lifecycle.

Generate Metadata Dictionary

The Generate Metadata Dictionary utility generates a set of files that can be hosted in the
Oracle Business Intelligence environment and can provide more information to users on the
data in their BI system. Generating and integrating the metadata dictionary is a two-stage
process; first, you use the Generate Metadata Dictionary utility to create the dictionary, and
then you configure the Oracle BI Presentation Server to make use of it.
For example, consider a situation where you have a repository called GCBCRepository.rpd
that is currently deployed as the online, default repository, for which you wish to generate a
metadata dictionary. To create a metadata dictionary for this repository, follow these steps:
1. Before you start, use your web browser to navigate to Fusion Middleware Control
using, for example, the URL http://obisrv1:7001/em and log in using the Weblogic
administration username and password. Then navigate to the coreapplication entry in
the Business Intelligence folder in the left-hand menu and select Deployment |
Repository from the tabs and subtabs. Within the BI Server Repository section, make a
note of the name of the default, online repository; for example, this may be called
GCBCRepository_BI0031.
2. Using the Oracle BI Administration tool, open a copy of that same repository file offline
(using file system tools if necessary to make a copy first), and then select Tools |
Utilities | Generate Metadata Dictionary.
3. When prompted, enter a location to save the metadata dictionary files to, such as
c:\files.
4. Locate the created folder structure within the directory that you specified. The folder
created by the utility should be named the same as your repository, without the .rpd
suffix; in this example, it would be named c:\files\GCBCRepository.
Rename this subfolder to match the currently deployed name of the repository, as
noted in the first step. In this example, the folder would therefore be renamed to
C:\files\GCBCRepository_BI0031. Copy the contents of this renamed folder to the
analyticsRes folder under your Oracle Business Intelligence installation so that the
dictionary files are located here:
C:\Middleware\instances\instance1\bifoundation\
OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\GCBC
Repository_BI0031
5. Next, you need to deploy the analyticsRes folder within the WebLogic Server Admin
Console. To do this, using your web browser, navigate to http://obisrv1:7001/console
 (changing the host name for your particular installation) and log in using the
Weblogic administration user account and password. Then navigate to the
Deployments entry under the Domain Structure menu, click this entry, and then click
the Lock And Edit Configuration button. To deploy the analyticsRes folder, within the
Summary Of Deployments area, click the Install button. Then, using the directory
browser, navigate to the area in the file system in which the analyticsRes folder
resides, and click the radio button to the side of it to select it. Then click Next, select
“Install this deployment as an application,” and click Next again. Under the Clusters
section, select bi_server1 as the deployment location. Finally, in the Source
Accessibility section on the next screen, select “I will make the deployment accessible
from the following location,” click Next, and then click Finish.
6. On the left-hand side of the Administration Console screen, click the Activate Changes
button. Once the changes are confirmed as activated, return to the Deployments entry
under the Domain Structure menu on the left-hand side of the screen, and select the
check box to the left of the analyticsRes entry that now exists. Locate the Start button
above all of the deployment entries, click it, and select Servicing All Requests. When
prompted, click Yes to start deployments, and then check that the state of the service is
now listed as Active.
7. You can now check that your metadata dictionary is available by calling it though a
URL. Use your web browser again, this time to access the dictionary, using in this
example the following URL:
http://obisrv1:9704/analyticsRes/GCBCRepository/TreeIndex.xml
The Top Level Repository Objects screen in the metadata repository should now show
in your browser.
8. Next, you need to configure the Oracle BI Presentation Server to make use of this
dictionary file. To do this, using a text editor, open the instanceconfig.xml file typically
found in the following location:
C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\co
reapplication_obips1
Add the following text just before the </ServerInstance> tag at the end of the file:
<SubjectAreaMetadata><DictionaryURLPrefix>
http://localhost:9704/analyticsRes/</DictionaryURLPrefix>
<!--CollateSubjectAreaNames>true</CollateSubjectAreaNames-->
</SubjectAreaMetadata>
Save the file once you have made this change.
9. Use your web browser to navigate to Fusion Middleware Control, using, for example,
the URL http://obisrv1:7001/em and log in using the Weblogic administration username
and password. Then, navigate to the coreapplication entry in the Business Intelligence
folder in the left-hand menu and select Capacity Management | Availability from the
tabs and subtabs. With the Availability tab showing, select the Presentation Servers
entry and click the Restart Selected button to restart the Oracle BI Presentation Server.
10. Now when you create an analysis you will see an Open Metadata Dictionary
icon above the subject areas available for your criteria.

When you select a subject area column or other object and click the Open Metadata
Dictionary button, your web browser will display a metadata report for the object in question.
Remove Unused Physical Objects
The Remove Unused Physical Objects utility can be used to remove physical tables, views,
and other objects from the semantic model if they are no longer present in the underlying data
source. Removing unused objects can be useful, as it reduces the size of the repository file
and consequently reduces the memory usage of the Oracle BI Server.
To use this utility, do the following:
1. Using the Oracle BI Administration tool, open your repository online or offline, and then
select Tools | Utilities | Remove Unused Physical Objects.
2. The Oracle BI Administration tool will then scan your data sources, as defined through
the physical layer of the Oracle BI Repository, and present you a list of candidate
objects to be removed.
3. Select the check box for any objects you wish to remove, and click the Yes button to
remove them from the repository.

Aggregate Persistence
The Aggregate Persistence Wizard can automate the production and population of aggregate
(summary) tables, which are then automatically mapped into your semantic model using the
vertical federation feature described later in this chapter. Aggregate tables can improve the
response time of your analyses through precomputing common aggregations, and the Oracle
BI Server will automatically make use of them when the analysis requests the appropriate
level of aggregation.
The Aggregate Persistence Wizard creates physical database tables, along with scripts, to
create indexes and then populate these tables. As with the parent-child relationship table
created by the Parent-Child Relationship Table Wizard, you run the table creation script first
and then rerun the population script every time the underlying data changes. As such, it is
best suited to scenarios where you control the loading of data into your system (for example,
a data warehouse) and can therefore trigger the calling of the aggregate table refresh routine
after each data load.
If you are using a database such as Oracle Database 10g Enterprise Edition or higher, you
might also want to consider having the database create your aggregate tables for you, in the
form of materialized views, which the database will then automatically use through a feature
called query rewrite. The Aggregate Persistence Wizard is therefore particularly well suited to
situations such as the following:
 You are not able, or allowed, to create materialized views (or equivalent) in your source
database.
 You wish to store the aggregates in a separate database to your main data set,
potentially using a different database type.
 You are using, for example, the Standard Edition of the Oracle Database, and
advanced features such as materialized views are not available to you.
 You wish to create aggregate tables for logical fact table mapping to multiple source
databases but store the aggregates in a single location.
To use the Aggregate Persistence Wizard, follow these steps:
 1. Using the Oracle BI Administration tool, open your repository either online or offline.
Then, select Tools | Utilities | Aggregate Persistence from the application menu.
2. At the Aggregate Persistence – Select File Location dialog box, enter a complete path
and file to which the aggregate persistence scripts will be saved. Select the “Generate
DDL in a separate file” check box if you wish to generate the table creation script
separate to the table population script (normally they are combined into one), for
example, if you wish to amend this script before running it.
3. At the Aggregate Persistence – Select Business Measures dialog box, select the
business model, and then the fact table measures that you wish to aggregate.
4. At the Aggregate Persistence – Select Levels dialog box, select the logical dimension
levels at which you wish to aggregate these measures. Select the Use Surrogate Key?
check box only if the primary key for the logical level is a noninteger or extends over
several columns.
5. At the Aggregate Persistence – Select Connection Pool dialog box, select the physical
database, catalog/schema, and connection pool that connect to the physical database
where you wish to create the aggregate tables.
6. Click Next and then click Finish to create the aggregate table scripts.
At this point, while the scripts have been generated, you need to actually execute them to
create the aggregate tables and map them into the semantic model. Unlike the parent-child
relationship table scripts that you execute through your database command-line utility, the
scripts generated by this utility are executed by the Oracle BI Server so that it can also use
them to automatically map the tables into your repository’s physical layer.
To run the script(s), you need to use the nqcmd.exe utility, which can normally be found within
your Oracle BI installation, typically in the following location:
C:\Middleware\Oracle_BI1\bifoundation\server\bin\nqcmd.exe
Before you run this command, though, you must run the initialization command file, bi-
init.cmd, typically found here:
C:\Middleware\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-
init.cmd
The nqcmd.exe utility is run from the command shell launched from this command file.
The repository to which you wish to deploy the aggregates needs to be online when you run
this utility. To run nqcmd.exe, you must supply five parameters:
 The ODBC DSN name for the connection to the BI Server hosting the repository (for
example, coreapplication_OH1094451294)
 A username and password (for example, weblogic/welcome1) that connects to Oracle
Business Intelligence and that has the BIAdministrator (or equivalent) application role
granted to it
 The name of the input file, which will be the name of the file just generated by the
Aggregate Persistence utility
 The name of an output file to which to output the results of the command
You should also enable query logging for this user so that you can see if any queries have
failed or exited abnormally.
For example, to run the nqcmd.exe utility for your script, you might run the following
command:
C:\Middleware\Oracle_BI1\bifoundation\server\bin\nqcmd.exe -d
coreapplica-
tion_OH1094451294 -u weblogic -p welcome1 -s c:\files\agg_wiz.txt -o
c:\files\
agg_wiz_output.txt

Your aggregate tables will then be generated and automatically mapped in your semantic
model.

Advanced Repository Modeling Concepts

In the “Creating a Repository” example in this chapter, we looked at creating a simple
business model from a single data source that was organized into a star schema with simple,
balanced, level-based hierarchies. This is the most straightforward type of data source to
model against, and if possible you should work closely with the data warehousing team within
your organization to ensure that the data source for your BI system is a single, conformed,
dimensional model, as this makes your job easier and also maximizes the performance of
your system.
Real life often is not as simple as this, though, and you may instead have more than one
dimensional data source to work with (for example, a set of departmental or country data
marts), or you may need to integrate data with differing levels of granularity. Luckily for you,
Oracle Business Intelligence 11g comes with a range of features for integrating disparate data
sources into a single semantic model, together with features for integrating historic data as
well as real-time data and handling more complex hierarchy types than simple, balanced,
level-based hierarchies.

Ragged and Skip-Level Hierarchies

So far in this chapter, we have modeled simple, level-based balanced hierarchies for our
logical dimensions. A level-based hierarchy is one that has distinct, named levels within the
hierarchy (for example, Product Category, Product Type, and Product Name),
and balanced refers to the fact that each branch of the hierarchy descends to the same level
and each member has a parent at the level immediately above it in the hierarchy.
In some situations, though, this may not be the case. Consider a situation where a store
network has to be modeled, which has the following named levels:
 All Stores, the grand total level
 Regions
 Stores
 Concessions
In our store network, however, the following special situations occur:
 Only certain stores are responsible for concessions, meaning that some branches of
the hierarchy have a leaf member at the concession level, while some stop at the
branch level.
 One concession is so large that it reports directly into the regional office.
  Both stores and concessions can have sales recorded against them.
When the hierarchy has leaf members at differing levels, we call this a ragged hierarchy.
When members miss a level when linking to their parent, this is called a skip-level
hierarchy. Figure 3-18 shows a typical ragged and a typical skip-level hierarchy, based on our
store network.

Oracle Business Intelligence 11g allows you to create logical dimensions with both ragged
and skip-level hierarchies, though care should be taken with these as they can have
performance implications in some cases.
In the example in Figure 3-19, a physical table called STORES_RAGGED has been defined
that contains columns for each level in the hierarchy, together with a surrogate (synthetic) key
that links the table to a fact table called SALES. Figure 3-19 shows the table, as displayed in
Oracle SQL*Developer, with the columns that we will use for the logical dimension.

Note how some rows feature concession IDs and descriptions that are null; these represent
either stores with no concessions linked to them or a single record for a store that does have
a concession for the purposes of recording sales specifically against the store. One other row
features a concession but not a store; this is a skip-level member and represents a
concession that reports directly into the regional office. Finally, if you are working with ragged
and/or skip-level hierarchies, you will need to ensure that all of the levels for your hierarchy
are contained in a single table, rather than in normalized, snowflaked tables, as the ID
columns that would normally contain level keys may in fact contain NULL values and are
therefore not valid as keys.
To create a ragged and skip-level hierarchy that uses this physical table as a source, follow
these steps, which are the same as for creating a balanced, level-based hierarchy, apart from
marking the hierarchy as ragged and/or skip-level:
1.Using the Oracle BI Administration tool, ensure that the physical table containing the
data for your ragged/skip-level hierarchy is present in the physical layer of the semantic
model, that the table is aliased, and that you have defined a key for the physical table
(based on the STORE_PK column in the example we are using). Then, using this key,
create a physical join between this table and the fact table it is associated with.
2.Within your business model, create a logical table to correspond with the physical
dimension table, ensure that you have defined a logical key for it, and create a join
between this logical table and your logical fact table. So far, this process has been
identical to how you would prepare your business model for a regular balanced, level-
based hierarchy.
3.To create the logical dimension that will contain the ragged and skip-level hierarchy,
right-click the business model containing the logical table you just created and select
New Object | Logical Dimension | Dimension With Level-Based Hierarchy.
4.When the Logical Dimension dialog box is shown, within the Structure area select the
Ragged check box if the hierarchy is ragged and the Skipped Levels check box if the
hierarchy features skip levels. Note that you can select both of these boxes if required,
but these options are grayed out and unavailable if you first select the Time check box,
as time dimensions must have balanced hierarchies.
Now, as you did for the balanced hierarchy earlier in this chapter, right-click the new logical
dimension and select New Object | Logical Level. This will be your grand total level, so name
it appropriately and select the Grand Total Level check box.
1.Now, as with balanced, level-based hierarchies, right-click the grand total level and
select New Object | Child Level. Name the level, enter the estimated number of distinct
members at this level, and then repeat this step to create the remainder of the level,
down to the concession level in the case of our example.
2.Drag-and-drop columns from the dimension logical table onto the levels that you just
created, right-click them, and select New Logical Level Key to create the level keys.
3.Finally, drag-and-drop the logical dimension table (not the hierarchy) to your
corresponding subject area in the presentation layer. Your hierarchy and logical table
are now ready for use within an analysis.

Performance Implications of Ragged and Skip-Level Hierarchies

With the steps for creating a ragged and/or skip-level hierarchy so similar to those used for a
balanced, level-based hierarchy, as well as the fact that the ragged and skip-level settings
work fine for hierarchies that are in fact balanced, you might conclude that the safest option
when creating level-based hierarchies is to mark all level-based hierarchies this way. There
are, however, performance implications when using ragged and skip-level hierarchies in
combination with another new feature in Oracle Business Intelligence 11g called hierarchical
columns.
For example, consider a situation where you have created an analysis using the Products
logical dimension defined earlier in this chapter, along with the Amount Sold measure. Using a
pivot table view, the user can drill-down from the top of the product hierarchy (All Products)
down to the next level, in this case Product Category.

If you have query logging (described later in this chapter in the section “How Does the Oracle BI
Server Handle Vertically Federated Data?”) enabled for this account and you examine the
physical SQL generated for the analysis, an excerpt of the SQL would look like this:

WITH
SAWITH0 AS (select distinct sum(T71.FCAST_SAL_AMT) as c1
from
SALES T71 /* Fact_SALES */ ),
SAWITH1 AS (select sum(T71.FCAST_SAL_AMT) as c1,
T52.PROD_CAT_DESC as c2
from
PRODUCTS T52 /* Dim_PRODUCTS */ ,
SALES T71 /* Fact_SALES */
where ( T52.PROD_ID = T71.FCAST_COST_AMT )
group by T52.PROD_CAT_DESC),…
Note how only those columns required for the query are included in the WHERE clause and
only a regular GROUP BY is used to provide the aggregation.
If we now create a similar analysis involving the ragged and skip-level hierarchy, the resulting
pivot table would look the same, as there are no ragged members or skip levels at this point
in the hierarchy.

However, if you examine the physical SQL generated by the query, you will note how, in this
instance, all of the levels and therefore columns in the hierarchy are included in the query and
how GROUPING_ID / GROUPING_SETS has been used to provide the aggregations:

WITH
SACOMMON26514 AS (select sum(T71.FCAST_SAL_AMT) as c1,
max(T414.REGION_NAME) as c2,
max(T414.CONCESSION_NAME) as c3,
max(T414.STORE_NAME) as c4,
T414.REGION_NAME as c5,
T414.STORE_NAME as c6,
T414.CONCESSION_NAME as c7,
grouping_id(T414.REGION_NAME,
T414.STORE_NAME,
T414.CONCESSION_NAME) as c8
from
STORES_RAGGED T414 /* Dim_STORES_RAGGED */ ,
SALES T71 /* Fact_SALES */
where ( T71.STORE_ID = T414.STORE_PK )
group by grouping sets (
(),
(T414.REGION_NAME),
(T414.STORE_NAME, T414.REGION_NAME),
(T414.CONCESSION_NAME, T414.STORE_NAME, T414.REGION_NAME))),
…

GROUPING_ID and GROUPING_SETS are used across all level-based hierarchies when
hierarchical columns are used in a pivot table and when the analysis requires subtotals and
totals across multiple hierarchies in levels. They are used much earlier in ragged and skip-
level hierarchies, however, because for the following reasons the Oracle BI Presentation
Server has to consider more than just the immediate levels selected for analysis:
 A member being displayed may in fact be a ragged member, and the Presentation
Server will need to indicate that no more drilling to detail can be performed on that
branch of the hierarchy.
 A particular hierarchy branch may feature a skip level, and therefore members from the
lower levels in the hierarchy may need to be displayed if its parent is skipped in the
level being displayed.
This means that, in practice, when an analysis uses a ragged or skip-level hierarchy, all of the
levels and columns for a query will be included in the physical SQL to retrieve that hierarchy’s
columns from the database, and results will be aggregated across all of these levels to deal
with these situations. Therefore, you may find that queries using ragged and skip-level
hierarchies generate more expensive database queries than those with balanced, level-based
hierarchies, so you should reserve use of this feature for those logical dimensions that
actually feature ragged or skip-level hierarchies.

Parent-Child Hierarchies
In addition to supporting ragged and skip-level hierarchies, the 11g release of Oracle
Business Intelligence now also supports parent-child, or value-based, hierarchies. Instead of
having the hierarchy defined by separate columns in the database, one for each level, parent-
child hierarchies define the hierarchy through a member ID and a parent ID, with the parent
ID recursively joining back to the member ID.
Consider a situation where we have a STAFF_PARENT_CHILD table in the physical layer of
the semantic model, as shown in Figure 3-20, that features five columns:
 STAFF ID This is the key for the table and ID for individual staff members.
 STAFF_NAME This is the descriptive column for the staff member.
 STAFF_GENDER and STAFF_DOB These are attributes for the staff member.
 MANAGER_ID This is a column that references the STAFF_ID column and contains
the staff ID for the staff member’s manager.

This type of table and hierarchy can be modeled within the Oracle Business Intelligence
11g semantic model. To assist with using it in analyses, a parent-child relationship table, or
“closure” table, is generated for you by the Oracle BI Administration tool when defining the
hierarchy. This parent-child relationship table then has to be maintained by you, using a
supplied SQL script whenever the underlying data in the dimension physical table changes.
To create a logical dimension with a parent-child hierarchy, follow these steps:
1.Using the Oracle BI Administration tool, select File | Import Metadata to import the
physical table metadata for the source table into the semantic model, create an alias
for it (for example, Dim_STAFF_PARENT_CHILD) and create a table key. Ensure that
there is no foreign key defined for this physical table from the Parent ID to the Member
ID column, as this may raise warnings from the consistency checker. Then, using the
dimension table key, create a physical join between this table and the fact table it is
associated with.
2.Within your business model, create a logical table to correspond with the physical
dimension table, ensure that you have defined a logical key for it, and create a join
between this logical table and your logical fact table. So far, this process has been
identical to how you would prepare your business model for a regular balanced, level-
based hierarchy.
3.To create the logical dimension that will contain the parent-child hierarchy, right-click
the business model containing the logical table you just created, and select New Object
Logical Dimension | Dimension With Parent-Child Hierarchy.
4.The Logical Dimension dialog box will then be shown. Check and update if necessary
the name chosen for the hierarchy, and check that the Member Key column is set to
the primary key for the logical table (the default setting). Then, using the Browse button
next to the Parent Column text box, select the parent ID (for example, Manager ID) for
this parent-child hierarchy.
With the Logical Dimension dialog box still open, click the Parent-Child Settings button to
create the parent-child relationship table. The Parent-Child Relationship Table Settings dialog
box will then be displayed.
1.With the Parent-Child Relationship Table Settings dialog box open, highlight the row
for your logical table and click the Create Parent-Child Relationship Table button to the
right of it (the middle button in the list of buttons, with a “new table” icon).
2.The Generate Parent-Child Relationship Table Wizard will then open. Type in a name
and file system location for the DDL Script To Create Parent-Child Relationship Table
and DDL Script To Populate Parent-Child Relationship Table settings. Note that the
wizard will not let you progress to the next screen unless the file system directory you
specify actually exists.
3.On the next screen of the wizard, type in a name for the table (for example,
STAFF_CLOSURE) and select the connection pool that corresponds with the physical
database that you intend to store the parent-child relationship table in. Once the wizard
completes, this physical database within your semantic model will be updated to
include the parent-child relationship table that you are now creating.
4.Review the scripts that the wizard will create for you, and click the Finish button to
create the scripts and place them in the file system location you specified. You will then
return to the Parent-Child Relationship Table Settings dialog box. Click OK to return to
the Logical Dimension dialog box, and click OK again to return to the main Oracle BI
Administration screen.

Now you have to run the two scripts generated by the previous steps. To do this, start a
command-line prompt for your database so that you can run the scripts. For example, if you
are using an Oracle database, select Run from your Windows Start menu, type
in cmd.exe, and then enter the following commands (assuming that you have saved the
scripts to the c:\files directory, you have chosen to store the closure table in the
GCBC_SALES/password schema, and your TNSNAMES net service name is ″ORCL″:
cd c:\files
sqlplus gcbc_sales/password@orcl
@create_closure_table.sql
@pop_closure_table.sql
commit;
1.Drag-and-drop the logical dimension table to the subject area within your
presentation layer containing the other data for your business model. Save your
repository, and use the consistency checker to ensure that there are no warnings or
errors for the repository.
If you take a look at the logical dimension created by the Oracle BI Administration tool, you
will notice that it has two levels within the hierarchy, one for the grand total level and one for
the detail level, containing all of the keys and attributes for the logical dimension table. The
presentation hierarchy within the subject area presentation table has just a single entry to
show that when it is to be included in an analysis you work with just a single object that
recursively drills into itself.

Showing the Member Description Rather Than the Member ID in a Parent-Child

Hierarchical Column
If you were to now publish your repository online and query the hierarchy in an analysis, you
will notice that, as the STAFF_ID and MANAGER_ID columns were used for the member ID
and parent ID for the parent-child hierarchy, it’s actually the STAFF_ID that is shown in the
hierarchical column member description, rather than the staff member’s name. To ensure that
the hierarchical column shows the descriptive column rather than the ID column, follow these
steps:
1. Using the Oracle BI Administration tool, navigate to the presentation hierarchy that you
wish to switch from ID to descriptive column display. Double-click the column to open
the Presentation Hierarchy Properties dialog box, and select the Display Columns tab.
2. Click the existing column listed within the Name section (for example, Staff ID), and
click the Delete button to remove it from the list.
3. Click the Add button to open the Browse dialog box, and then expand the subject area
listing on the left-hand panel to locate the column you wish to display instead of the ID
(for example, Staff Name).
4. Click OK to close the dialog box, and then save your changes to the repository.
Your parent-child hierarchical column will now use the descriptive column for a dimension
member rather than the ID column next time you take the repository online and refresh the
Oracle BI Presentation server’s cached copy of its metadata.

Altering the Aggregation Properties of a Parent-Child Hierarchy

If you view a parent-child hierarchy through a hierarchical column in an analysis, along with a
fact table measure, you may notice that, by default, values are not aggregated up the
hierarchy, so measure values shown against each member in the hierarchy actually represent
the total for that member, rather than that member and all its descendents.

This is not, however, the behavior for level-based hierarchies, where each member’s total is
equal to the sum of the lower-most leaf levels in the hierarchy that roll up (aggregate) into that
member. The reason for this is due to how the parent-child relationship table is included into
the physical layer when it is created by the Oracle BI Administration tool.
If you select the dimension table, fact table, and parent-child relationship table within the
physical model, right-click them and select Physical Diagram | Selected Object(s) Only, you
will see that the parent-child relationship table is not, by default, joined to either the dimension
table or the fact table. Instead, the fact table joins to the dimension table only, or in this
instance the STAFF_ID column, which is why the Oracle BI Server only includes values for
that particular staff member when calculating the total for each staff member.

To ensure that the Oracle BI Server includes the dimension member’s descendents in its total,
and therefore that values aggregate up the hierarchy, you have to modify the physical layer
joins to include the parent-child relationship table. To do this, follow these steps:
1.Using the Oracle BI Administration tool, open your repository offline and then
navigate to the physical layer of the semantic model; then, right-click the physical
dimension table, fact table, and parent-child relationship table for your parent-child
hierarchy, and select Physical Diagram | Selected Object(s) Only.
2.With the Physical Diagram screen open, check that the physical dimension table
joins directly to the physical fact table and that the parent-child relationship table is not
included in the join (as per the previous screenshot).
 3.Click the join between the physical dimension table and physical fact table, and then
right-click and select Delete.
4.With the New Join button selected, draw a join starting at the fact table and ending at
the parent-child relationship table. When prompted to specify the join columns, select
the MEMBER_KEY column from the parent-child relationship table and the dimension
key column (for example, STAFF_ID) from the fact table.
5.Repeat step 4, but this time create a join between the parent-child relationship table
and the dimension table, joining the key column from the dimension table (for example,
STAFF_ID) to the ANCESTOR_KEY column in the parent-child relationship table. Your
physical diagram should now look like this:

As there is now no direct link between the dimension physical table and the fact physical
table, you will need to include the parent-child relationship table in the logical table source
used by the parent-child hierarchy’s logical table. To do this, navigate to the business model
and mapping layer in the semantic model, locate the parent-child dimension’s logical table,
expand the Sources folder, and double-click the table source to edit it.
1.With the Logical Table Source dialog box open, select the General tab to display the
list of mapped tables (currently just the physical dimension table). Click the Add button,
and then select the parent-child relationship table to add it to the table list.
2.To save the repository, select File | Save. The Consistency Check Manager dialog
box will then be displayed and will show a warning that the logical table source you just
edited contains columns not used in any column mappings. You can ignore this
warning, as it just refers to the parent-child relationship table that you needed to map in
to create the link between the fact and dimension table sources.
3.Click Close to close this dialog box, save your repository to the file system, and then
use Fusion Middleware Control to make this repository the default, online repository.
If you then display the parent-child hierarchical column in an analysis, along with a measure,
you will see that the total for each member now includes aggregated values for that member’s
descendants added to the value associated with that individual member.

Displaying Hierarchy Members When No Measure Values Are Associated (Left Outer
Joins)
If you display any type of hierarchical column in an analysis on its own, all of the members will
be displayed when you drill into the analysis. If, however, you include measures from a fact
table into the analysis criteria, only those members with corresponding values in the fact table
will be displayed, due to the inner join that is created by default between the two tables in the
business model and mapping layer of the semantic model.
If you change this logical join to a left outer join instead, all rows from the logical dimension
table will be returned when you include the hierarchy in an analysis, even if there is no fact
table data associated with the measure. Make this change with caution, though, as it will
affect all analyses that use this dimension table and can cause existing analyses to return
incorrect or different data than before.
To alter your business model to use a left outer join between a dimension and a fact table,
follow these steps:
 1.With your repository open in the Oracle BI Administration tool, navigate to the
business model containing the logical dimension and fact table. Locate the two tables,
right-click them, and select Business Model Diagram | Selected Tables Only.
2.The Business Model Diagram screen will then be displayed. Double-click the join
between the two tables to display the Logical Join dialog box. Within the Logical Join
dialog box, locate the Type drop-down list. Change the selected value from Inner to
Left Outer, and click OK to save the change. Save your repository as usual, and then
take your repository online, using Fusion Middleware Control if you have been working
offline with it.
When you next query the hierarchy, you will see that dimension members are displayed
regardless of whether fact table data is associated with them.

What Happens When We Join Two Fact Tables?

So what happens when we include measures from more than one logical fact table in an
analysis? How does the Oracle BI Server generate the physical SQL for such an analysis,
and also, does this change when we include, as with the last example, measures from two
physical fact tables into the same logical fact?
Let’s first take a look at what happens when we include measures from two different logical
fact tables in the same analysis; only shared dimensions are used in the analysis, and both
physical sources point to different physical schemas but come from the same physical
database. Figure 3-21 shows the relationship between the two logical fact tables and the two
dimension tables that join them.

When measures from the two fact tables are included in an analysis, along with attributes
from one or more of the logical dimensions, the Oracle BI Server internally generates two
logical queries, one for each of the fact tables. This is to avoid what is called a “fan trap” error,
where SQL would otherwise double-count measures from the fact tables when joining through
a common dimension. Where possible, though, the Oracle BI Server will try to join these two
logical queries together into a single physical SQL, using what is called subquery factoring.
The physical Oracle Database 11g Enterprise Edition SQL shown here, resulting from a query
against the business model in the preceding section, shows how this would look in practice:

WITH
SAWITH0 AS (select avg(T616.SAT_SCORE) as c1,
T414.REGION_NAME as c2,
T62.MONTH_YYYYMM as c3
from
GCBC_SALES.STORES_RAGGED T414 /* Dim_STORES_RAGGED */ ,
GCBC_SALES.TIMES T62 /* Dim_TIMES */ ,
GCBC_SURVEYS.CUSTOMER_SATISFACTION T616 /*
Fact_CUSTOMER_SATISFACTION */
where ( T62.MONTH_YYYYMM = T616.MONTH_YYYYMM
and T414.STORE_PK = T616.STORE_PK )
group by T62.MONTH_YYYYMM, T414.REGION_NAME),
SAWITH1 AS (select sum(T71.FCAST_SAL_AMT) as c1,
 T414.REGION_NAME as c2,
T62.MONTH_YYYYMM as c3
from
GCBC_SALES.STORES_RAGGED T414 /* Dim_STORES_RAGGED */ ,
GCBC_SALES.TIMES T62 /* Dim_TIMES */ ,
GCBC_SALES.SALES T71 /* Fact_SALES */
where ( T62.MONTH_YYYYMM = T71.MONTH_YYYYMM
and T71.STORE_ID = T414.STORE_PK )
group by T62.MONTH_YYYYMM, T414.REGION_NAME),
SAWITH2 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select 0 as c1,
case when D2.c2 is not null then D2.c2
when D1.c2 is not null then D1.c2 end
as c2,
case when D1.c3 is not null then D1.c3
when D2.c3 is not null then D2.c3 end
as c3,
D1.c1 as c4,
D2.c1 as c5,
ROW_NUMBER() OVER (PARTITION BY case
when D1.c3 is not null then D1.c3
when D2.c3 is not null then D2.c3 end ,
case when D2.c2 is not null then D2.c2
when D1.c2 is not null then D1.c2 end
ORDER BY
case when D1.c3 is not null then D1.c3
when D2.c3 is not null then D2.c3 end ASC,
case when D2.c2 is not null then D2.c2
when D1.c2 is not null then D1.c2 end ASC) as c6
from
SAWITH0 D1 full outer join
SAWITH1 D2 On D1.c3 = D2.c3
and nvl(D1.c2 , 'q') = nvl(D2.c2 , 'q')
and nvl(D1.c2 , 'z') = nvl(D2.c2 , 'z')
) D1
where ( D1.c6 = 1 ) )
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
SAWITH2 D1
order by c1, c2, c3 NULLS FIRST
Notice the two WITH blocks at the start of the query (this is subquery factoring) and the full
outer join at the end of the query, where the Oracle BI Server realizes that it can “push” the
joining of these two separate logical queries into a single physical SQL statement; if we were
working with a database such as Microsoft Access or mySQL, where subquery factoring is not
available, two separate physical SQL queries would have been issued and the Oracle BI
Server would perform a full outer join, or stitch join, in its memory instead.
A similar situation would occur if it was just a dimension table that was being horizontally
federated from a new physical data source; if the new data source came from the same
physical database as the rest of the user’s analysis, the Oracle BI Server would push the join
down to the physical database, by including it and the full set of tables in the physical SQL. If,
however, the new physical dimension table came from a different database, two separate
queries would be issued for the two data sources and the Oracle BI Server would join the
resulting data sets in memory before passing the results back to the Oracle BI Presentation
Server.

Example: Joining Facts of Differing Dimensionality

In the example we have been using so far, the new logical fact table shares two dimensions
with the existing logical fact table (products and times) but has one specific to itself (survey
organizations). In turn, the existing logical fact table has two other dimensions (staff and
products) that are not used by the new logical fact table. So what happens if we include
measures from both logical fact tables in one analysis and one of the dimensions not shared
by both fact tables? In this case, the measures that are not dimensioned by one of the logical
dimension tables really should not be shown in the results, as they cannot be “broken down”
by the extra dimension, and therefore no valid results should be shown for them. The
following screenshot shows this happening in practice:

What if, however, you specifically still want to return results from this fact table, even if an
unconfirmed dimension is included in the analysis criteria? Well, this approach is not
reallyadvisable because, as you will see, it returns repeated values for the measure in
question, but if you understand how the results will look, these are the steps you should carry
out:
1.Ensure that all of the logical dimension tables that you will include in the query have
logical dimensions and hierarchies defined for them, as we have with the Dim Products
logical table and its corresponding logical dimension.

Within the business model, locate the logical columns that you wish to display values for even
when the unconfirmed dimension is included in the analysis. Double-click the logical column
to display the logical column Properties dialog box, and then select the Levels tab.
1.With the Levels tab selected for those dimensions that do not ordinarily join to the
logical fact table containing the logical column (Product, in this example), set the
Logical Level to the grand total level for that logical dimension’s hierarchy.

Save your repository in the normal way, and check the repository’s consistency as usual.
 1.Now rerun the analysis that you created earlier and note how, for the column you just
changed, values now appear for the measure, but these are in fact repeated across all
of the rows from the unconfirmed dimension.

Vertical Federation (Aggregates)

In addition to joining together physical data sources at the same level of detail (granularity),
Oracle Business Intelligence 11g can also create single logical data sets out of data stored at
different levels of granularity. For example:
Detail-level data may be stored in a relational database such as Oracle Database 11g,
while summary-level data may be held in an OLAP server such as Oracle Essbase.
Detail-level data may be stored in an Oracle database, while summary tables may be
held in another Oracle database.
Data for sales, for example, may come from an invoicing system that stores some
measures solely at detail level, while other measures apply only at the whole-invoice
level.
Oracle Business Intelligence 11g can bring these sources of differing granularity together
using a feature called vertical federation. A common use case for this is handling summary-
level data, which may be created using an ETL process or may have been generated
automatically using the Aggregate Persistence Wizard. In Chapter 4, we will look at how you
might use vertical federation to bring together detail-level relational data and summary-level
OLAP server data, but for now let us take a closer look at vertically federating detail and
summary-level relational data.
Vertical federation relies on your having defined logical dimensions and logical hierarchies to
accompany the logical tables in your business model. You use these logical dimensions and
hierarchies to tell the semantic model at which level of detail a new logical table source
applies, and the Oracle BI Server will then switch to this new logical table source once an
analysis requests data aggregated to the appropriate level.
For a logical table source, you define the level of detail at which the Oracle BI Server should
use it by setting the logical level for the source in the logical table source Properties dialog
box. In the following example, the Oracle BI Server will start making use of the table source
once the analysis requests data at the Product Category and Quarter logical levels, and will
use a more detailed logical table source for its columns up until that point.

This type of repository modeling is useful when, for example, you cannot use database-
specific features such as Oracle materialized views to handle pre-aggregation at the physical
database level or where you wish to store aggregated values in a different physical database
than the one storing your detail-level data. In addition, as previously mentioned and as
covered in Chapter 4, it is also a useful technique for when you wish to combine relational and
multidimensional data sources into a single business model.
To see how this works in practice, let’s work through an example where we map some
aggregate tables into an existing logical business model and configure the new logical table
sources so that the Oracle BI Server uses them when analyses request the required level of
aggregation.
Example: Vertical Federation Using Relational Data Sources
In this scenario, we have a semantic model containing a single logical fact table that contains
sales data that is then dimensioned by product, store, times, and staff logical dimension
tables. We now wish to introduce a second data source, this time containing summary-level
data for this same logical fact table, which we intend to map in using the vertical federation
feature.
At the start of the process, the business model and corresponding physical layer looks as in
the following screenshot. Note the existing detail-level logical table sources under each logical
table’s Sources folder and the single physical schema holding their corresponding physical
table sources.

To incorporate the aggregated data using vertical federation, follow these steps:
1.Using the Oracle BI Administration tool, connect to the aggregated data source, for
example, the GCBC_AGGS schema that comes with the sample data that
accompanies this book. Import the objects into the physical layer of the semantic mode
and then alias the tables as required, and then create keys and foreign key joins
between just the tables in this physical schema. In the example we are using, three
tables are imported: A_PROD_CATEGORIES, which contains product category data;
A_TIMES_QUARTERS, which contains quarters and year information; and
A_SALES_AGG, which contains sales data aggregated to the product category and
quarters levels.

Unlike horizontal federation, you do not create physical foreign key joins between the new
physical data source tables and the existing ones. Instead, you define the links between the
detail- and aggregate-level data sources by creating additional table sources for individual
logical columns within your business model. Starting with the aggregated physical dimension
tables, locate a physical column that has a corresponding logical column in the business
model you wish to vertically federate into. For example, the Dim_A_PROD_CATEGORIES
alias table (based on the A_PROD_CATEGORIES physical table) has a physical column
called PROD_CAT_DESC that corresponds to the Dim Products Product Category logical
column in our business model. Drag-and-drop this physical column onto the corresponding
logical column to create the join.

If you now navigate to the logical table and column that you just dragged the aggregate-level
physical column to, you will notice two things: first, a new logical table source has been added
to the Sources folder for this logical table, and second, if you double-click the logical column
and select the Column Source tab, you will see that this logical column now has two possible
table sources that can be used to return values for it.

Now you should set the logical level for the logical table source created by your previous
mapping step. Navigate to the Sources folder under the logical table you just mapped the
physical column into and double-click the logical table source that corresponds to your
aggregated data source. With the Logical Table Source dialog box open, select the Content
tab. For a dimension logical table, only one logical dimension should be listed under this tab,
which will correspond to this logical table. Use the Logical Level drop-down list to select the
hierarchy level that this source should be used at (for example, Product Category).

Repeat step 4 for any other columns in the incoming aggregated table source, and then do
the same for any other dimension physical tables in the new data source.
1.Now do this for the physical fact table in the presentation layer, mapping in the
aggregated physical columns to connect to the existing logical columns in your logical
fact table. When setting the aggregation level for the logical fact table aggregated table
source, you will need to specify levels for all of the relevant dimensions, and some of
the dimension levels will need to be left blank if the aggregated source does not
contain aggregate data for these dimensions.
2.Once complete, save your repository and check its consistency. As you have not
created any new logical columns, you do not need to make any further changes to the
presentation layer of your repository.

Note When performing a consistency check, you may get an error along the lines of “Table
Dim Products is functionally dependent upon level Product Category, but a more
detailed child level has associated columns from that same table or a more detailed
table.” If so, check to see whether the dragging and dropping of your aggregate-level
physical column onto the logical table has created an additional logical key at the
aggregate level. If it has, delete the extra key and recheck consistency.
Other variations on this process include mapping a new column from the aggregated data
source, which provides an additional column for the logical table but only applies at this
aggregated level of detail. This is, in fact, a combination of vertical and horizontal federation,
which would require you to take steps similar to the preceding example, but you would need
to create a new, additional logical column and corresponding presentation column for the new
aggregate-only measure.
Once complete, your semantic model, with aggregated data sources mapped in, should look
similar to that shown in Figure 3-22.

How Does the Oracle BI Server Handle Vertically Federated Data?

So what happens when you create a vertically federated semantic model and the Oracle BI
Server has to switch between the detail-level and aggregated data sources?
To show what happens, let’s take the example that we used in the previous section (“Vertical
Federation Using Relational Data Sources”) and enable query logging to see the SQL that is
generated for the query. To enable query logging, use the Oracle BI Administration tool and
select File | Manage Identity, with the repository open in online mode, and create an analysis
that lists the amount sold by product category.

Taking a look at the corresponding entry in the query log file, you can see that the Oracle BI
Server has used the aggregate tables imported from the GCBC_AGGS schema referenced in
the previous section’s example to return data for the analysis. This is because the semantic
model maps the corresponding logical table sources in at this level of aggregation, and the
Oracle BI Server therefore assumes that it will return data more efficiently than the alternative,
detail-level table source.
WITH
SAWITH0 AS (select sum(T926.FCAST_SAL_AMT) as c1,
T924.PROD_CAT_DESC as c2
from
GCBC_AGGS.A_PROD_CATEGORIES T924 /* Dim_A_PROD_CATEGORIES */ ,
GCBC_AGGS.A_SALES_AGG T926 /* Fact_A_SALES_AGG */
where ( T924.PROD_CAT_DESC = T926.PROD_CAT_DESC )
group by T924.PROD_CAT_DESC)
select distinct 0 as c1,
D1.c2 as c2,
D1.c1 as c3
from
SAWITH0 D1
order by c2

However, if you now click the Product Category column to drill down, like this

and check the corresponding query log entry, you will see that the Oracle BI Server now
switches to the detail-level table sources, pointing to the GCBC_SALES physical schema in
our example data set:

WITH
SAWITH0 AS (select sum(T71.FCAST_SAL_AMT) as c1,
T52.PROD_CAT_DESC as c2,
T52.PROD_TYPE_DESC as c3
from
GCBC_SALES.PRODUCTS T52 /* Dim_PRODUCTS */ ,
GCBC_SALES.SALES T71 /* Fact_SALES */
where ( T52.PROD_ID = T71.FCAST_COST_AMT )
group by T52.PROD_CAT_DESC, T52.PROD_TYPE_DESC)
select distinct 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c1 as c4
from
SAWITH0 D1
order by c2, c3

In this way, the Oracle BI Server picks the most efficient table sources to satisfy a query,
based on the logical levels at which you mapped them into the business model, all
transparently to the end user.
Fragmentation
Fragmentation is the name for another repository feature for Oracle Business Intelligence that
makes it possible to combine specific multiple table sources for a particular logical table, all at
the same level of detail, with each fragment providing a particular discreet range of values.
For example, a logical table containing sales transactions may have two logical table sources
defined for it, one that provides transactions up until midnight yesterday (perhaps from a data
warehouse) and another that provides today’s transaction (from, for example, an operational
system).
To set up fragmentation, you need to consider the following:
 Each fragmented table source requires an expression that defines the range of values
that it provides; for example, you may define a fragmentation clause that specifies that
the table source provides values from 01-JAN-2010 until 31-DEC-2011.
 This fragmentation clause may reference a repository variable so it could specify that it
provides values up until midnight yesterday, for example.
 Fragmented logical table sources must have valid physical joins to appropriate fact or
dimension physical tables so that if the Oracle BI Server switches to using the fragment
it can still join to appropriate physical fact and dimension table sources.
Fragmentation is defined using the Logical Table Source properties dialog box, under the
Content tab, as shown in Figure 3-23.

To specify the range of values that the fragmented table source provides, use the Expression
Builder to specify the required expression, and select the “This source should be combined
with other sources at this level” check box.
You can also make reference to a repository variable within the fragmentation clause, allowing
you to set up fragmentation so that, for example, a historical fragment provides data up until
yesterday, while the current fragment provides real-time data for today only.

Example: Using Fragmentation to Combine Current and Historical Data

To see how fragmentation works in practice, let’s work through an example related to the
ones we have used earlier in this chapter but where the fact table data is split over two
physical tables, each of which holds a subset of the total fact data. The source schema
contains a shared set of physical dimension tables, and the two fact tables are split as
follows:
 SALES_CURRENT holds measure values from July, 2010 onward.
 SALES_HISTORIC holds measure values prior to July, 2010.
All of these physical tables have been aliased in the physical layer of the semantic model, and
we now wish to combine these two fact table sources together into a single logical table using
the federation feature of the Oracle BI Server.
The physical model at the start of the process looks like this, with both of the physical fact
tables joining to both of the physical dimension tables:
To set up fragmentation in this scenario, follow these steps:
1.First, using the business model and mapping layer, define the outline business model
and logical tables that will hold your data. Create a single logical fact table for the
 measures (for example, Fact Sales) and logical dimension tables for the dimension
attributes (for example, Dim Products and Dim Times).
2.Using the physical layer of the semantic model, select one of the logical fact table
source tables (in this example, Fact_SALES_HISTORIC, the alias for the
SALES_HISTORIC physical table), and drag-and-drop the measure columns into the
logical fact table that you just created. Rename the columns as appropriate, and then
expand the Sources folder under the logical fact table. You will see a new logical table
source listed, named after the physical fact table that you just dragged the columns
from.

Repeat step 2 to create the columns for the logical dimension tables so that each table in the
business model has logical columns defined and a corresponding logical table source. Create
logical keys for the dimension tables and use the Business Model Diagram tool to create
logical joins between the logical dimension tables and the logical fact table, as you would do
with a normal business model.
1.Double-click the logical table source for the logical fact table to display the Logical
Table Source properties dialog box. Click the Content tab and locate the Fragmentation
Content section headed. Click the Expression Builder button and use the Expression
Builder dialog box to create a suitable expression that defines the scope of the data
this initial table source contains; in this example, the initial table source provides data
up to June, 2010, so the expression becomes “Sales - Fragmented”.”Dim
Times”.”Month YYYYMM” <= 201006. Then select the check box under this text area
that reads “This source should be combined with other sources at this level.”
Now you can create the second logical table source for the fact table, which this time
references the second physical fact table that in this example contains measure data from
July, 2010 onward. To do this, navigate to the second physical fact table in the physical layer
and drag-and-drop its measure columns on top of the existing logical ones in the logical fact
table so that a second logical table source is added under the Sources folder.

1. As with the previous logical table source, use the Content tab under the Logical Table
Source dialog box to set the fragmentation content for the table source; in this
example, it will be set to “Sales - Fragmented”.”Dim Times”.”Month YYYYMM” >
201006. Ensure that the “This source should be combined with other sources at this
level” check box is selected, and then close the dialog box.
Note Ensure that there are no gaps or overlaps between the ranges covered by all of the
table sources in your fragmented tables.
6. This completes the process of working with fragmentation. Create your presentation
layer objects as usual, save and check the consistency of your repository, and then
take it online to see the effect of the fragmentation clause in action.
Now, when you create an analysis using this business model and restrict, for example, the
criteria to show only data for May, 2010, the Oracle BI Server uses just the
Fact_SALES_HISTORIC logical table source and the corresponding physical table to satisfy
the query, like this:

WITH
SAWITH0 AS (select distinct sum(T71.FCAST_REV_AMT) as c1
from
 TIMES T82 /* Dim_TIMES */ ,
SALES_HISTORIC T71 /* Fact_SALES_HISTORIC */
where ( T71.MONTH_YYYYMM = T82.MONTH_YYYYMM
and T71.MONTH_YYYYMM = 201006
and T82.MONTH_YYYYMM = 201006 ) )
select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1

If the user queried a date that corresponded to the other physical fact table, a similar query
would be issued but would reference the other physical fact table instead.
However, if you create an analysis that requests data that spans both physical fact tables, you
will see both tables used in the physical SQL query, with a UNION ALL clause to combine the
two sets of data:

WITH
SAWITH0 AS ((select T71.FCAST_REV_AMT as c2
from
TIMES T82 /* Dim_TIMES */ ,
SALES_HISTORIC T71 /* Fact_SALES_HISTORIC */
where ( T71.MONTH_YYYYMM = T82.MONTH_YYYYMM
and T71.MONTH_YYYYMM between 201006 and 201010
and T82.MONTH_YYYYMM between 201006 and 201010 )
union all
select T60.FCAST_REV_AMT as c2
from
TIMES T82 /* Dim_TIMES */ ,
SALES_CURRENT T60 /* Fact_SALES_CURRENT */
where ( T60.MONTH_YYYYMM = T82.MONTH_YYYYMM
and T60.MONTH_YYYYMM between 201006 and 201010
and T82.MONTH_YYYYMM between 201006 and 201010 ) )),
SAWITH1 AS (select distinct sum(D3.c2) as c1
from
SAWITH0 D3)
select distinct 0 as c1,
D2.c1 as c2
from
SAWITH1 D2

Finally, in this particular example, a common set of physical dimension tables were able to be
used for both of the physical fact tables because they held all of the dimension member
details referenced by both fact tables. Often, though, each fragmented physical fact table will
come with its own set of reference dimension tables, and you will therefore have to model
both sets in the physical layer of the semantic layer and make sure that both sets of physical
fact tables have the correct join to their respective physical dimension tables. This would
mean that not only would the logical fact table in the business model have two logical table
sources, but the logical dimension tables would also need two as well, adding to the
complexity of your repository model.

Double Columns
Double columns, or descriptor ID support, describes a feature within Oracle Business
Intelligence 11g where an ID column can be assigned to a descriptive column. This can
sometimes be useful to help the underlying data source optimize its query and also provides
the ability to see the ID in the front end without giving the users direct access to the ID
column.
For example, you might assign an ID column to a descriptive column if the underlying physical
table is partitioned on the ID column and you wish for the ID to be used in the query predicate
rather than the descriptive column. As another example, you might want to create multiple
language translation columns for a dimension attribute column but have each of them use a
common ID column if used in a query.
To specify the descriptor ID column for another logical column, do the following:
1. Using the Oracle BI Administration tool, open your repository either offline or online.
2. Navigate to the business model and mapping layer and locate the descriptive logical
column that you wish to designate an ID column for (for example, Product Name).
3. Double-click the column to display the Logical Column dialog box. Locate the
Descriptor ID Column area and click the Set button. Pressing this button displays the
Browse dialog box, which you can then use to select the column you wish to use as the
descriptor ID column (for example, Product ID).

Close the dialog box and save your repository as usual.

With this example, if the Product Name logical column is used as the filter (predicate) in an
analysis, the Oracle BI Server will substitute the Product ID column instead, generating, for
example, the following excerpt of SQL:

WITH
SAWITH0 AS (select sum(T117.REV_AMT) as c1,
T107.PROD_CAT_DESC as c2,
T107.PROD_ID as c3
from
GCBC_SALES.PRODUCTS T107,
GCBC_SALES.SALES T117
where ( T107.PROD_ID = T117.PROD_ID and (T107.PROD_ID in (12, 17,
31)) )

Existing analyses that filter on the Product Name column do not need to be changed, as the
Oracle BI Server will substitute the descriptor ID column in any filters automatically.
Until now in this chapter, we have looked at importing and then modeling data sourced from
relational data sources. In some cases, though, you may wish to report against data from
sources such as Microsoft Excel spreadsheets, text files, or XML documents either on
networked file systems or accessed over the Internet.
In the next section, we will look at how file and XML sources are accessed and how you
create physical models that can then be used along with other physical models to create the
business model and presentation layers in your Oracle BI Repository. As official support for
these sources varies by type, we will also look at the restrictions and limitations in working
with these sources so you can make a decision as to whether to access them directly from
the repository or copy their data into relational or multidimensional databases and access
them from there instead.