DAC - Data Warehouse Console

Oracle Business Analytics Warehouse:

1.Data Integration engine that combines data from multiple source systems to build a data warehouse.
2.An open architecture to allow organizations to use third party analytical tools with OBAW using OBI Server.
3.Prebuilt data extractors to incorporate data from external applications into the Oracle Business Analytics Warehouse.
4.A set of ETL processes that takes data from multiple source systems and creates the OBAW tables.
5.DAC schema management as well as configuration,administration,loading and monitoring of OBAW.


OBAW Architecture:

High level analytical queries,like those commonly used in OBAW,scan and analyze large volumes of data using complex formulas.

For this reason,the Oracle Business Analytics warehouse was constructed using dimensional modelling techniques to allow for fast access to information required for decision making.

OBAW derives its data from Operational applications and uses informatica's data integration technology to extract,transform and load data from transactional databases into OBAW.



OBAW Architecture components:

1.DAC Client: A command and control interface for the DWH to allow for schema management,configuration,administration and monitoring of data warehouse processes.

It also enables you to design subject area and build execution plans.

2.DAC Server: Executes the instructions from DAC client.DAC server manages data warehouse processes,including loading of the ETL and scheduling execution plans.

3.DAC repository:Stores the metadata that represents the data warehouse processes.

4.Informatica server:Load and refreshes the OBAW.

5.Informatica Repository Server: Manages the Informatica Repository.

6.Informatica Repository:Stores the metadata related to informatica workflows.

7.Informatica client utilities:Tool that enable you to create and manage the informatica repository.


DAC--

It provides a framework for the entire life cycle of data warehouse Implemenations.

It enables you to create,configure,execute and monitor modular Datawarehouse Applications in a parallel,high performing environment.

Purging/Seeding Cache in OBIEE

Enabling Cache increases the speed of query as it will not hit the database on the query request but not always we will get refresh data,so we purge cache to get latest data.
Purging Cache/Seeding Cache can be done in Many ways:
1.At presentation services -Front End.

Using following Functions:

1.SAPurgeAllCache -Purges all cache.
2.SAPurgeCacheByDatabase-Purges all cache for a specific Physical Database.
3.SAPurgeCacheByTable-Purges cache for a specific physical table.
4.SAPurgeCacheByQuery-Purges cache for a specific query.

2.In Physical Layer - At each table level we can purge Cache/Enable Properties.

3.Using Event Polling Tables at Database level using Admin Tool.

Use of Event Polling Tables:

i)It is a way to notify the Oracle BI Server that one or more physical tables have been updated and then the query cache entries are stale.
ii)Each Row that is added to an event table describes a single update event.
iii)OBI Server cache system reads rows from the event table,extracts the physical table information from the rows and purges stale cache entries that reference those physical tables.

4.Disbling cache At NQSConifg.INI file.


 

Data Warehousing

Question:What is data warehousing?
Answer:A data warehousing is a relational database that is designed for query and analysis rather than for transaction processing.
It usually contains historical data derived from transactional data.
Willam Inmon -- 1.Subject Oriented 2.Integrated 3.Non-Volatile 4.Time Variant.

1.Subject Oriented:DWH are designed to help you analyze data.
2.Integration:Closely related to subject orientation.DWH must put data from disparate sources into consistent.
3.Non-Volatile:Once entered into datawarehouse,it should not change with time.It is used for analysis purpose.
4.Time Variant:Datawarehouse can change over time,it needs large amounts of  data for analysis.

2.Difference between OLTP and Datawarehouse?

1.Very few indexes in OLTP,when compared to DWH.
2.Many Joins in OLTP,when compared to DWH.
3.Normalized DMBS is in OLTP and Denormalized DMBS in Datawarehouse.
4.Dervied data and aggregates are very rare  in OLTP but derived data and aggregates are very common in datawarehouse.


Differences of DWH and OLTP:

Workload -- Datawarehouses are designed to accommodate adhoc queries.You might not know the workload of your data warehouse  in advanvce,so a datawarehouse should be optimized to perform well for a wide variety of possible query opeartions.
OLTP systems support only predefined operations.
Data Modifications:
A data warehouse is updated on a regular basis by the ETL process using bulk data modification techniques.
End users of a datawarehouse do not directly update the data warehouse.
In OLTP systems,end users routinely issue individual data modifications statements to the database.OLTP database is always up to date and reflects the currenct state of each business transaction.
Schema design:
Data warehouses often use denormalized or partially denormalized schemas to optimize query performance.
OLTP systems often use fully normalized schemas to optimize update/insert/delete performance and to guarantee data consistency.
Historical data:
Datawarehouses usually store many months or years of data.
This is to support historical analysis.
OLTP systems usually store data from only a few weeks or months.


3.What is Data Warehousing schemas?

A schema is a collection of database objects,including tables,views,indexes and synonyms.
You can arrange schema objects in the schema models designed for data warehousing in a variety of ways.Most datawarehouses uses a dimensional model.
The model of your source data and the requirements of your users help you design the datawarehouse schema.

Star Schemas:

This is the simplest datawarehouse schema.It is called a star schema becuase the diagram resembles a star,which points radiating from a center.The center of the star consists of one or more fact tables and the points of the star are the dimension tables.
The most natural way to model a data warehouse is as a star schema,only one join establishes between the fact table and any one of the dimension tables.
A star schema optimizes performance by keeping queries simple and providing fast response time.All the information about each level is stored in one row.

4.What are the Data warehousing Objects?

1.Fact tables and dimension tables are two types of objects commonly used in dimensional data warehouse schemas.

Fact Tables:

A fact table typically has two types of columns:those that contain numeric facts and those that are foreign keys to dimension tables.
A fact table contains either detail-level facts or facts  that have been aggregated.
Fact tables that contain aggregated facts are often called summary tables.
A fact table usually contains facts with the same level of aggregation.Though most facts are addivitive,they can also be semi-additive or non-additive.
Additive facts can be aggregated by simple arithmetical addition.Non-additive facts cannot be added at all.
Creating a New Fact Table:
1.You must define a fact table for each star schema.From a modelling standpoint,the primary key of the fact table is usually a composite key that is made up of all its foreign keys.

Dimension Tables:

A dimension is a structure,often composed of one or more hierachies,that categorizes data.Dimensional attributes help to describe the dimensional value.
They are normally descriptive,textual values.Several dimensions,combined with facts,enable  you to answer business questions.Commonly used dimensions are customers,products and time.
Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis.
These natural rollups or aggregations within a dimension table are called hierarchies.

Hierarchies:

Hierarchies are logical structures that use ordered levels as means of organizing data.A Hierarchy can be used to define data aggregation.For example: In a time dimension,a hierarchy might aggregate data from the month level to the quarter level to the year level.A hierarchy can also be used to define a navigational drill path and to establish a family structure.





















5.What is Logical design in datawarehouses?

1.Specific data content.
2.Relationships within and between groups of data.
3.The system environment supporting your data warehouse.
4.The data transformations required.
5.The frequency with which data is refreshed.

Logical design is more conecptual and abstract than the physical design,looking at logical relationship among the objects.


Creating a logical design :

A logical design is conceptual and abstract.E-R modeling involves identifying the things of importance entities,the properties of these things and how they are related to one another.

The process of logical design involves arranging data into a series of logical relationships called entites and attributes.

An entity represents a chunk of information.In relational databases,an entity often maps to a table.
An attribute is a component of an entity that helps define the  uniqueness of the entity.

In relational database,an attribute maps to a column.


In Physical design,we look at the most effective way of storing and retrieving the objects as well as handling them from a transpoartion and backup/recovery perspective.

Agents

Agents:It enables you to automate your business process.
We can use them to provide event-driven alerting,scheduled content publishing and conditional event-driven action executtion.
1.A schedule that the Agent runs on.
2.A data condition that determines what the agent does.
3.An analysis that can be distributed.
4.Actions that can be automatically executed depending on whether the data condition is met.
5.Agents can dynamically detect information -based problems and opportunties,determine the approraite individuals to notify and deliver information to them a wide range of devices(email,phones and so on).
What are alerts?
An alert is a notification generated by an agent that delivers personalized and actionable content to specified and to subscribers to the agent.

Oracle SQL

DML Commands: Data Manipulation Language Statements access and manipulate the data in existing schema objects. These statements need commit after the transactions.


1.SELECT  Statement: SQL select stmt is used to retrieve records from one table or more tables in Oracle database.

Syntax: Select <column1> from <table>;

2.INSERT Statement: It allows you to insert a single record or multiple records into a table.

Syntax: INSERT INTO <Table> (col1,col2) values (1,'xxx');

3.UPDATE Statement: It allows to update a single record into a table or multiple tables.

Syntax:UPDATE <Table>  SET col2='yyy' where col1=1;

4.DELETE Statement: It allows you to delete a single record or multiple records from a table.

Syntax:Delete from <Table1> where col1=1;

5.

Variables

Variables:
We can reference variables in several areas of OBIEE,Including in analyses,dashboards,KPIs,actions,agents and conditions.
They are four types of variables:
1.Session.
2.Repository.
3.Presentation.
4.Request.
Session Variables:
A session variable is a variable that is initialized at login time for each user.When a user begins a session,the Oracle BI Server creates a new instance of session variable and initializes it.
A system variable that the administrator creates and names.For example- Admin creates a salesregion non-system variable that initializes the name of a user's sales region.
There are many instances of a session variables as there are active sessions on the oracle Bi server.
Each instance of a session variable could be initialized to a different value.
There are two types of session variables:
1.System-- A session variable that the Oracle BI server and Oracle BI Presentation services use for specific purposes.
System session variables have resrved names that can not be used for other kinds of variables such as static or dynamic repository variables and non-system session variables.
Non-system-A system variable that the administrator creates and names.For example,the administrator might create a salesregion non-system variable that initializes the name of a user's sales region.
Repository variable: A repository variable is a variable that has a single value at any point in time.
There are two types of repository variables:
1.Static-- Repository variable whose value persist and do not change until the administrator decides to change them.
2.Dynamic- Repository variable whose values are refreshed by data returned from queries.
Presentation Variables:A presentation variable is a variable that you can create as part of the process of creating one of the following types of dashboard prompts.
1.Column Prompt--A presentation variable created as part of a column prompt is associated with a column,and the values that it can take from the column values

Creating Analyses

In the global header,Click New,then Analysis,then one of the following options:
1.Select a subject area.The analysis editor is displayed.
2.Create Direct database request.
3.Create Analysis from simple SQL.

Combining Columns using set opeartions:
1.Guidelines for selecting columns to combine.
When selecting columns to combine,keep the following guidelines in mind:
1.The number and data types of the columns to combine be the same.The number of rows that are returned for eack column can differ.
2.We can select columns from the same subject subjec area or from different area,but the columns must have some commonality.
3.Can select one set opeartion for one collection of criteria.
4.We can not use hierarchical columns,selection steps or grouos when you combine criteria.
5.We can not use Hierarchical columns,selection steps or groups when you combine criteria.
Difference between combining columns using set opeartions and adding columns from related subject areas.
1.Combining columns using set opeartions produce different results than adding columns from related subject areas:
-- When you combine columns using set operations,the analysis results show a single newly combined column governed by a set opeartion.
2.When we add columns from related subject area to an analysis,the results show each added column individually.

Types of Columns:

Attribute column -- It holds a flat list of values that are also known as members.
No hierarchical relationship exist between these members,as is the case for members of a hirerachical column.
An attribute column referred to as a presentation column.

Hierarchical column-- Holds data values that are organized using both named levels and parent-child relationships.This column is displayed using a tree like structure.
Hierarchies allow you to drill deeper into the data,to view more detailed information.Examples include Time or Geography.

Hierachical column types:
1.Level Based Hierarchy-- It consists of an ordered set of two or more levels.
For example-- A time hierarchy might have three levels for year,quarter and month.

2.Parent-Child Hierachy -- It consists of values that define the hierarchy in a parent-child realtionship and does not contain named levels.Example: An employee hierarchy might have no levels,but instead have names of employees who are managed by other employees.
Employees can have titles,such as vice president.Vice Presidents might report to other vice presidents and different vice presidents can be at diferent depths in the hierarchy.

Ragged Hierarchy: A hierarchy in which all the lowest level members do not have the same depth.
For Example: A time Hierachy might have data for the current month at the day level,the previous month's data at the month level and the previous 5 years data at quarter level--This type of hierarchy is also known as Unbalanced Hierarchy.
Skip-level Hierachy: A hierarchy in which certain memebrs do not have values for certain higher levels.
For example: In USA,washington dc is not belongs to state but it is city.

Analyses:An analyses is a query aganist an Organization's data that provides answers to Business questions.A query contains the underlying SQL statements that are issued to the OBI Server.
Analyses that you create can be saved in the OBI Presentation catalog and integrated into any Oracle BIEE Dashboard.

Subject Area contains folders,measure columns,attribute columns,hierachical columns and hierarchy levels that represent information about of an organization's business or about groups of users with an organization.
Subject Areas usually have names that correspond to the types of information that they contain,such as markerting contracts,service requests and orders.

Introduction to OBIEE 11G

1.To Collect up-to-date data from your organization.
2.Present the data in easy to understand formats.
3.Deliver data in a timely fashion to the employees in organization.
4.These capabilities enable organization to make better decisions,take informed actions and more-efficient business process.

In OBI we work with
1.Analyses:These are also known as Answers.
2.Dashboards:This consists of pages,which contains views , prompts,web links,etc.
3. Filters,Prompts and Selection steps:These allow to limit the data in displayed dashboards and analyses.
4.Agents:This is also known as Delivers.
5.Conditions:These are objects that return a single Boolean value that is based on evaluation of an analysis or Key performance indicators.
6.Actions:Actions provide functionality to navigate to related content or invoke operations,functions or process in external systems.
7.Scorecards:This area of OBIEE is known as Oracle Scorecard and Strategy Management.

Presentation catalog is to store of OBIEE Objects susch as analyses,dashboards and KPIs.
Users create their own folders and save their objects and share the objects depends upon the security access.