Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for January, 2011

Data Warehousing – Definitions

Posted by Santosh Kumar Gidadmani on January 31, 2011

Thought to put some basics of Data Warehousing Concepts before I start with more focused subjects. I did lot of research in gathering information on these topics. Let us see some important topics. I would cover all these topics in series, so check out all series to gain some basics of data warehousing, data warehouse, architecture, life cycle etc. These topics would be helpful for any data warehouse beginners.

What is Data Warehousing?

“A process of transforming data into information and making it available to users in a timely enough manner to make a difference” – Forrester Research, April 1996.

What is a Data Warehouse?

1. “A single, complete and consistent store of data obtained from a variety of different sources made available to end users in a what they can understand and use in a business context” – Barry Devlin.

2. “A data warehouse is a subject-oriented, integrated, time-varying, non-volatile. It is collection of data that is used primarily in organizational decision making” – Bill Inmon, Building the Data Warehouse 1996.

3. A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources – Oracle.

A fundamental concept of a data warehouse is the distinction between data and information. Data is composed of observable and recordable facts that are often found in operational or transactional systems. The Information is an integrated collection of facts and is used as the basis for decisionmaking.

What are the characteristics of Data Warehouse?

Subject-Oriented: Information is presented according to specific subjects or areas of interest, not simply as computer files.

Integrated: Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. That is, if two different source systems store conflicting data about entities, or attributes of an entity, the differences need to be resolved during the process of transforming the source data and loading it into the data warehouse.

Non-Volatile: Stable information that doesn’t change each time an operational process is executed. Information is consistent regardless of when the warehouse is accessed.

Time-Variant: Containing a history of the subject, as well as current information. Historical information is an important component of a data warehouse.

Accessible: The primary purpose of a data warehouse is to provide readily accessible information to end-users.

Process-Oriented: It is important to view data warehousing as a process for delivery of information. The maintenance of a data warehouse is ongoing and iterative in nature.

What is the Advantages of Data warehouse?

Enhances end-user access to a wide variety of data.

Business decision makers can obtain various kinds of trend reports.

Increased data consistency.

Potentially lower computing costs and increased productivity.

Providing a place to combine related data from separate sources.

Creation of a computing infrastructure that can support changes in computer systems and business structures.

Empowering end-users to perform any level of ad-hoc queries or reports without impacting the performance of the operational systems.


What are the historical developments in the areas data warehousing?

1960s — General Mills and Dartmouth College, in a joint research project, develop the terms dimensions and facts.

1970s — ACNielsen and IRI provide dimensional data marts for retail sales.

1970s — Bill Inmon begins to define and discuss the term: Data Warehouse

1983 — Teradata introduces a database management system specifically designed for decision support.

1988 — Barry Devlin and Paul Murphy publish the article An architecture for a business and information systems in IBM Systems Journal where they introduce the term "business data warehouse".

1990 — Daniel Linstedt begins work on Developing the Data Vault model and methodology for data warehouses

1990 — Red Brick Systems introduces Red Brick Warehouse, a database management system specifically for data warehousing.

1991 — Prism Solutions introduces Prism Warehouse Manager, software for developing a data warehouse.

1991 — Bill Inmon publishes the book Building the Data Warehouse.

1995 — The Data Warehousing Institute, a for-profit organization that promotes data warehousing, is founded.

1995 — Daniel Linstedt adds SEI/CMMI and Six Sigma to the Data Vault Methodology to manage projects in data warehousing.

1996 — Ralph Kimball publishes the book The Data Warehouse Toolkit.

2000 — Daniel Linstedt releases the Data Vault, enabling real time auditable Data Warehouses

Source: Wiki

– Santosh

Posted in Data Warehousing, DW Definitions | Leave a Comment »

OBIEE 10g Architecture

Posted by Santosh Kumar Gidadmani on January 15, 2011

Most of the people in the OBIEE community are aware of the OBIEE 10g Architecture, thought to put together a consolidated view of the architecture.

In simple way, it includes the following components;

1. Clients
1.1 Answers – Web Based
Oracle BI Answers is a set of graphical tools used to build, view, and modify Oracle BI requests. The requests are queries against an organization’s data.
1.2 Interactive Dashboard – Web Based
An Oracle BI Dashboard is used to display the results of Answers requests that are embedded in the dashboard.
1.3 Administration Tool – Windows based
The Oracle BI Administration Tool is used to build the Oracle BI repository and is the focus of this

2. Oracle BI Presentation Services
Acts as middle layer in architecture in receiving and sending requests. It receives processing instructions from an Oracle BI client, retrieves the requested information from Oracle BI server, and then renders the information inside the requesting client.

3. Oracle BI Server
Is the core server behind Oracle Business Intelligence. The BI Server communicates with the BI Presentation Server via ODBC, and then connects out to the various supported data sources through ODBC, OCI, XML/A, the Essbase etc.
· Uses metadata to direct processing.
· Generates dynamic SQL to query data in the physical data sources.
· Connects natively or through ODBC to the RDBMS.

Some of the components of Oracle BI server are;
1) Intelligent Request Generator which takes the incoming queries and converts them into physical queries against the connected data source.
2) Navigator which takes the incoming query, compares it against cached requests and decides on the best results. There are some more components within the Navigator like multiple physical queries, aggregations, fragmentation etc.
3) Optimized Query Rewrite engine which handles the aggregate navigation and fragments and converts to the correct physical SQL statements.
4) Execution Engine which fires the queries to the relational, multi-dimensional, file and XML sources to fetch the data.
5) Cache Services which stores the results of previously run queries, matches incoming SQL against that used before and returns data from the cache rather than making the BI server query the underlying databases again.
6) Security Services for setting up users and groups in the RPD, filters, subject area security, links to outside LDAP servers and custom authenticators.
7) Query Governance, for placing limits on numbers of rows returned and length of query execution for users and groups.

4. Data Sources
Data sources are the physical sources where the business data is stored. They can be in any format, including transactional databases, online analytical processing databases, text files, XMLA etc. SQL is generated by Oracle BI Server against the data sources using the data source connection, information from the repository, and database-specific parameters stored in a DBFeatures.INI file.

– Santosh

Posted in 10g Architectu​re, OBIEE 10g, OBIEE 10g Concepts | Leave a Comment »

Data Warehouse Approaches

Posted by Santosh Kumar Gidadmani on January 9, 2011

There are two major approaches to data warehouse design.

1. Bottom-up approach

• This approach is recommended by Kimball.

• In the bottom-up approach data marts are first created to provide reporting and analytical capabilities for specific business processes.

• Data marts contain, primarily, dimensions and facts. Facts can contain either atomic data and, if necessary, summarized data. The single data mart often models a specific business area such as Sales or Production.

• These data marts can eventually be integrated to create a comprehensive data warehouse.

• The integration of the data marts in the data warehouse is centered on the conformed dimensions.

• The actual integration of two or more data marts is then done by a process known as "Drill across". A drill-across works by grouping (summarizing) the data along the keys of the (shared) conformed dimensions of each fact participating in the "drill across" followed by a join on the keys of these grouped (summarized) facts.

• Some consider it an advantage of the Kimball method, that the data warehouse ends up being "segmented" into a number of logically self contained and consistent data marts, rather than a big and often complex centralized model.

• Business value can be returned as quickly as the first data mart is built.

2. Top-down approach

• This approach is recommended by Bill Inmon.

• Inmon is one of the leading proponents of the top-down approach to data warehouse design, in which the data warehouse is designed using a normalized enterprise data model.

• In the Inmon vision the data warehouse is at the center of the "Corporate Information Factory" (CIF), which provides a logical framework for delivering business intelligence (BI) and business management capabilities.

• The top-down design methodology generates highly consistent dimensional views of data across data marts since all data marts are loaded from the centralized repository.

• Generating new dimensional data marts against the data stored in the data warehouse is a relatively simple task.

• The main disadvantage to the top-down methodology is that it represents a very large project with a very broad scope, cost and time.

• In addition, the top-down methodology can be inflexible and unresponsive to changing departmental needs during the implementation phases.

– Santosh

Posted in Data Warehousing, DW Aproaches | Leave a Comment »

%d bloggers like this: