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 April, 2011

Basics of OBI Apps

Posted by Santosh Kumar Gidadmani on April 21, 2011

Oracle BI Applications – Overview

Oracle Business Intelligence Applications are a set of prebuilt solutions that deliver business intelligence across an organization, giving users the key information they need to maximize effectiveness. These solutions transform and integrate data from a range of enterprise sources, including Siebel, Oracle E-Business Suite, as well as corporate data warehouses.

Oracle BI Applications includes the following modules:

1. Oracle Financial Analytics
2. Oracle Human Resources Analytics
3. Oracle Supply Chain and Order Management Analytics
4. Oracle Procurement and Spend Analytics
5. Oracle Project Analytics
6. Oracle Sales Analytics
7. Oracle Service Analytics
8. Oracle Contact Center Telephony Analytics
9. Oracle Marketing Analytics
10. Oracle Loyalty Analytics

The BI Applications are built upon the foundation of Oracle BI Enterprise Edition which means that they allow extension of the suite with additional data sources and own-built dashboards and reports.

The Oracle BI Applications pre-packaged content comes with three main pre-built repositories:

1. The OBIEE repository and catalog
2. The ETL repository tailored for the source applications
3. The Data Warehouse model

The figure below diagram presents a functional overview of OBIA.

The pre-built ETL repository includes not only routines for changed data capture, extract to staging area and the load into data warehouse tables, but also seed data for common dimensions like the time dimension or dimension lookup files with domain values or dimension value sets to support sources independent metric calculation.

The pre-built Business Analytics Warehouse is one single but modular data warehouse model to support one or a combination of the source systems.

The pre-built OBIEE repository and web catalog are aligned with the Business Analytics Warehouse and contain the domain specific and end user facing dashboards, reports and KPI’s.

The below diagram shows the Architecture of the OBI Apps

Data Flow Description

1. First, the DAC scheduler kicks off jobs to loadrefresh the OBAW at regular intervals or alternatively, these jobs could be kicked off manually from the DAC client.
2. The DAC server uses the object and configuration data stored in the DAC repository to issue commands to the informatica Server.
3. The informatica server executes the commands issued from DAC, and uses the objects and configuration data stored in the informatica repository.
4. Then the data are extracted, transferred and loaded from the transactional databases into the OBAW target tables.
5. After the ETL is complete and OBAW is online, an OBIEE end user runs a dashboard or report in the Answers or Interactive dashboard.
6. The request comes through the web server and interacts with the presentation server.
7. The presentation server interacts with OBI Server and OBI server will understand the requests and hits the OBAW if it’s not cashed and extracts the data and provides it to the presentation server.
8. The presentation server formats the data into the required format and through web server, the request is presented to the end user.

Oracle BI Applications Vs OBIEE Implementation Approach

An OBIA implementation differs from a traditional data-warehouse build, in that it is complete with pre-built, pre-mapped and pre-packaged insights based upon best practice principles. When building a traditional data warehouse, a significant amount of time and effort is given over to the analysis and build of the warehouse.

The diagram below shows a comparative approach to a traditional data warehouse against OBIA.

This pre-built approach differs from the OBIEE manifests itself in the application layer as can be seen from the screenshots below. The upper image is of the BI Administration Tool and as can be seen the platform is installed but there is no content, this all needs adding manually after an analysis and design stage. The lower image is of the same tool but as installed with OBIA. Here all the content of the Physical, Business and Presentation Layers comes already mapped thus removing the need for the design, analysis and build stages.

Due to the large amount of pre-built content and pre-defined structures, implementation time is greatly reduced, upgrade paths guaranteed and integration with your source systems assured. The following diagram shows a comparative of OBIEE implementations versus OBIA.

– Santosh

Posted in Basics of OBI Apps, OBI Applications | 5 Comments »

Fast Changing Dimensions

Posted by Santosh Kumar Gidadmani on April 12, 2011

Fast changing dimensions are those dimensions if one or more of its attributes changes frequently and in many rows. A fast changing dimension can grow very large if we use the Type-2 approach to track numerous changes. These dimensions some time called rapidly changing dimensions.
Examples of fast changing dimensions are
Test score
Credit history score
Customer account

How to solve fast changing dimensions
After identifying the fast changing dimensions attributes, you have to create a mini dimension table with these attributes joined directly to fact table and not snowflaking.

A mini-dimension is a dimension that usually contains fast changing attributes of a larger dimension table. This is to improve accessibility to data in the fact table.

Before creating a mini dimension table, you have to convert these identified attributes individually into band ranges. The concept behind this method is to take limited discreet values as shown below.

Rows in mini-dimensions will be fewer than rows in large dimension tables because it restricts the rows in mini-dimensions by using the band range value method.

After identifying the fast changing attributes of the primary customer dimension, and determining the band ranges for these attributes, a new mini-dimension is formed called Cust_Mini_Dim as shown below.

– Santosh

Posted in Dimensional Modeling, Identify Dimensions | Leave a Comment »

Slowly Changing Dimensions

Posted by Santosh Kumar Gidadmani on April 10, 2011

Designing a dimensional model that accurately and efficiently handles changes is a critical consideration when building a data warehouse.
Handling changes to dimensional data across time can be difficult, and dimensional attributes rarely remain static. A customer address or name can change, sales representatives come and go, and companies introduce new products to replace older ones.

A slowly changing dimension is a dimension whose attribute or attributes for a record (row) change slowly over time.

Types of SCDs
1. Type-1 (overwriting the history)
2. Type-2 (preserving one or more versions of history)
3. Type-3 (preserving one or more columns of history)
4. Type-4 (preserving one or more table of history)

Type-1 (overwriting the history)
The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all.
This may be the best approach to use if the attribute change is simple, such as a correction in spelling. And, if the old value was wrong, it may not be critical that history is not maintained.
• It is the easiest and simplest to implement.
• It is extremely effective in those situations requiring the correction of bad data.
• No change is needed to the structure of the dimension table.

Type-2 (preserving the history)
A Type-2 approach adds a new dimension row for the changed attribute, and therefore preserves history. This approach accurately partitions history across time more efficiently than other change handling approaches. However, because the Type-2 approach adds new records for every attribute change, it can significantly increase the database’s size.
• Enables tracking of all historical information accurately and for an infinite number of changes.
• Causes the size of the dimension table to grow fast.
• Complicates the ETL process needed to load the dimensional model.

There are three methods to handle type 2 SCD.

Method A – No surrogate key – Use timestamp

When a change happens, a new record is added into the table. All the attributes are copied from the previous record except the changed values. The nature key is copied as well so the timestamps is used to differentiate the records.

When a fact table is joined with the dimension, if you are interested in the historical data, the timestamp will be used as part of the join condition. To ease the join, the record typically use two date columns – the effective start date and the effective end date.

Method B – No surrogate key – Use version number

Instead of using the date column, a version number is used to differentiate the different versions of the records.

This technique requires the fact table store both nature key and the version number to retrive a given version of the dimension date.

Method C – Use a surrogate key

When an attribue is change, a sequence generated key is used, the fact table will also use this key column as the foreign key.

Type-3 (preserving one or more versions of history)
The type-3 approach is typically used only if there is a limited need to preserve and accurately describe history. The Type 3 method tracks changes using separate columns.
• Does not increase the size of the table as compared to the type-2 approach, since new information is updated.

Type-4 (preserving one or more table of history)
The Type-4 method is usually referred to as using “history tables”, where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

– Santosh

Posted in Dimensional Modeling, Identify Dimensions | Leave a Comment »

%d bloggers like this: