Schema is a logical description of the entire database. It includes the name and description of records of all types including all associated data-items and aggregates. Much like a database, DW also requires to maintain a schema. Database uses relational model, while DW uses Star, Snowflake, and Fact Constellation schema (Galaxy schema).

Star Schema

In a Star Schema, there are multiple dimension tables in de-normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose. These schemas are multidimensional structures which are used to create reports using BI reporting tools.

Dimensions in Star schemas contain a set of attributes and Fact tables contain foreign keys for all dimensions and measurement values.

In the above Star Schema, there is a fact table “Sales Fact” at the center and is joined to 4 dimension tables using primary keys. Dimension tables are not further normalized and this joining of tables is known as Star Schema in DW.

Fact table also contains measure values − dollar_sold and units_sold.

Snowflakes Schema

In a Snowflakes Schema, there are multiple dimension tables in normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose.

Only difference between a Star and Snowflakes schema is that dimension tables are further normalized. The normalization splits up the data into additional tables. Due to normalization in the Snowflake schema, the data redundancy is reduced without losing any information and therefore it becomes easy to maintain and saves storage space.

In above Snowflakes Schema example, Product and Customer table are further normalized to save storage space. Sometimes, it also provides performance optimization when you execute a query that requires processing of rows directly in normalized table so it doesn’t process rows in primary Dimension table and comes directly to Normalized table in Schema.

Granularity

Granularity in a table represents the level of information stored in the table. High granularity of data means that data is at or near the transaction level, which has more detail. Low granularity means that data has low level of information.

A fact table is usually designed at a low level of granularity. This means that we need to find the lowest level of information that can be stored in a fact table. In date dimension, the granularity level could be year, month, quarter, period, week, and day.

The process of defining granularity consists of two steps −

Determining the dimensions that are to be included.
Determining the location to place the hierarchy of each dimension of information.

Slowly Changing Dimensions

Slowly changing dimensions refer to changing value of an attribute over time. It is one of the common concepts in DW.

Example

Andy is an employee of XYZ Inc. He was first located in New York City in July 2015. Original entry in the employee lookup table has the following record −

Employee ID	10001
Name	Andy
Location	New York

At a later date, he has relocated to LA, California. How should XYZ Inc. now modify its employee table to reflect this change?

This is known as “Slowly Changing Dimension” concept.

There are three ways to solve this type of problem −

Solution 1

The new record replaces the original record. No trace of the old record exists.

Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

Employee ID	10001
Name	Andy
Location	LA, California

Benefit − This is the easiest way to handle the Slowly Changing Dimension problem as there is no need to keep track of the old information.
Disadvantage − All historical information is lost.
Use − Solution 1 should be used when it is not required for DW to keep track of historical information.

Solution 2

A new record is entered into the Employee dimension table. So the employee, Andy, is treated as two people.

A new record is added to the table to represent the new information and both the original and new record will be present. The new record gets its own primary key as follows −

Employee ID	10001	10002
Name	Andy	Andy
Location	New York	LA, California

Benefit − This method allows us to store all the historical information.
Disadvantage − Size of the table grows faster. When the number of rows for the table is very high, space and performance of table can be a concern.
Use − Solution 2 should be used when it is necessary for DW to keep historical data.

Solution 3

The original record in Employee dimension is modified to reflect the change.

There will be two columns to indicate the particular attribute, one indicates original value and other indicates the new value. There will also be a column that indicates when the current value becomes active.

Employee ID	Name	Original Location	New Location	Date Moved
10001	Andy	New York	LA, California	July 2015

Benefits − This does not increase the size of the table, since new information is updated. This allows us to keep historical information.
Disadvantage − This method doesn’t keep all history when an attribute value is changed more than once.
Use − Solution 3 should only be used when it is required for DW to keep information of historical changes.

Normalization

Normalization is the process of decomposing a table into less redundant smaller tables without losing any information. So Database normalization is the process of organizing the attributes and tables of a database to minimize data redundancy (duplicate data).

Purpose of Normalization

It is used to eliminate certain types of data (redundancy/ replication) to improve consistency.
It provides maximum flexibility to meet future information needs by keeping tables corresponding to object types in their simplified forms.
It produces a clearer and readable data model.

Advantages

Data integrity.
Enhances data consistency.
Reduces data redundancy and space required.
Reduces update cost.
Maximum flexibility in responding to ad-hoc queries.
Reduces the total number of rows per block.

Disadvantages

Slow performance of queries in database because joins have to be performed to retrieve relevant data from several normalized tables.

You have to understand the data model in order to perform proper joins among several tables.

Example

In the above example, the table inside the green block represents a normalized table of the one inside the red block. The table in green block is less redundant and also with a smaller number of rows without losing any information.

So, this brings us to the end of blog. This Tecklearn ‘Understanding Schemas in OBIEE’ blog helps you with commonly asked questions if you are looking out for a job in Oracle BI. If you wish to learn OBIEE and build a career in Business Intelligence domain, then check out our interactive, Oracle Business Intelligence Enterprise Edition (OBIEE) Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-business-intelligence-enterprise-edition-training-and-certification/

Oracle Business Intelligence Enterprise Edition (OBIEE) Training

About the Course

Oracle Business Intelligence Enterprise Edition (OBIEE) Training and Certification training course lets you master the Oracle Business Intelligence and Analytics platform. You will gain in-depth knowledge on OBIEE advanced features, BI dashboards, reports, etc., through hands-on training sessions. This course is meant to provide detailed knowledge of the OBIEE 12c. You will be trained in various Business Intelligence scenarios, BI life cycle, concepts of OLAP and working with charts and graphs.

Why Should you take OBIEE Training?

The average salary for “oracle business intelligence consultant” ranges from approximately $76,703 per year for Business Consultant to $113,285 per year for Full Stack Developer. -Indeed.com.
Oracle Business Intelligence has a market share of around 8% globally.
OBIEE gives you experience with next-generation solutions that can help strategize, plan and optimize business operations. Allow you to become an Oracle Certified Expert or Oracle Implementation Specialist of an in-demand solution.

What you will Learn in this Course?

Data Modelling and Data Warehousing Concepts

Introduction to Oracle Business Intelligence Enterprise Edition (OBIEE)
Data models
Data warehousing
Data modelling implications and the impact of Data modelling on business intelligence

Business Intelligence Concepts

Overview of Business Intelligence
Description of BI Stack: BI technology, BI Server and BI Scheduler
Need for reporting in business
Difference between OLTP and OLAP
Multidimensional and relational analytical processing

OBIEE Installation

OBIEE Installation (12c)
Oracle Business Intelligence Suite
Architecture of OBIEE
Key features and components

Online Analytical Processing (OLAP)

Concept of Online Analytical Processing
Significance of OLAP in business intelligence life cycle
Star Schema and Designing with Star Schema
Enterprise information model
Snow flake and constellation

Oracle BI Repository

What is Oracle Business Intelligence Repository?
Directory structure installation, services,
Analytics and interactive reporting,
Dashboard creation and multiple report creation

Business Intelligence Repository Business Model

How to build a Business Model and Mapping Layer in BI Repository
Data format, Conditional format, Removing filters,
Report Saving
Creation of new folder
Working with the Enterprise Manager
Testing and validation of the Repository
Understanding the process of Cache disabling
Dashboard prompt and filtering

Dashboard Creation

Creation and Setup of OBIEE Dashboard
Basics of OBIEEE dashboard
Deploying Dashboard Builder for building Dashboards
Edit, Share and Save options in Dashboard analysis
Process of Cache creation and clearing
ODBC functions
Logical Table Source, Summary and Detail Report

OBIEE Repository

OBIEE Repository
Creation of Test Report and adding calculations
Deployment of OBIEE analysis
Repository variables, session and presentation variables

OBIEE Security & Management

Securing the Oracle Business Intelligence Suite with Enterprise Manager
Creation of alerts and notifications
Administration, Maintenance and Grouping
Types of security in OBIEE
Task and folder level security

Differences between OBIEE 11g and 12c

Hands on Variables

New Features of OBIEE 12c

Repository Development
Catalog Development
Deployment-Online
RPD Deployment

703