Transcript Oracle Spatial 11g Geo Coding

Reporting &
Analytics for EBS
with Oracle OLAP
Kailash Pareek
Agenda
Author Introduction
 Background Information
 Oracle OLAP
 Designing the Cube
 Reporting with Excel

2
Author Introduction
B.E (ECE), I.I.Sc., 1980
 16 years Heading IT departments
 11 Years in IT Industry
 > 15 Years Oracle Tech Experience
 >10 years Oracle EBS Experience
 Independent IT Consultant

3
Introduction to DWH, BI, OLAP & DM
Data Warehouse
The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the
following way: "A warehouse is a subject-oriented, integrated, time-variant and nonvolatile collection of data in support of management's decision making process". He
defined the terms in the sentence as follows:
(Source: "What is a Data Warehouse?" W.H. Inmon, Prism, Volume 1, Number 1, 1995).
Business Intelligence
In a 1958 article, IBM researcher Hans Peter Luhn used the term business
intelligence. He defined intelligence as:"the ability to apprehend the
interrelationships of presented facts in such a way as to guide action towards a
desired goal.“
OLAP was a term coined by E F Codd (1993) and was defined by him as:
“the dynamic synthesis, analysis and consolidation of large volumes of
multidimensional data”
4
Introduction to DWH, BI, OLAP & DM
Data Mining
Data mining involves the use of sophisticated data analysis tools to
discover previously unknown, valid patterns and relationships in
large data sets.
These tools can include statistical models, mathematical algorithms,
and machine learning methods (algorithms that improve their
performance automatically through experience, such as neural
networks or decision trees).
Consequently, data mining consists of more than collecting and
managing data, it also includes analysis and prediction.
5
Introduction to DWH, BI, OLAP & DM
Integration
6
Understanding Oracle OLAP

OLAP option to database 10g









Multi Dimensional Data type (Analytical Workspace)
OLAP Engine
OLAP Server side APIs
Analytical Workspace Manager (AWM) client
tool
Oracle Warehouse Builder
BI Beans
Oracle discoverer for OLAP
BI Spreadsheet Add-In
Oracle Planning & Budgeting
7
Installing / verifying OLAP in database
OLAP option is automatically installed with EE of database. To verify
run the SQL
select * from v$option where upper(parameter) ='OLAP‘ and
select comp_name,status from dba_registry
where upper(comp_name) like '%OLAP%'
If the OLAP option is installed, the value will be TRUE in first query
and status VALID in second.
If the OLAP is installed but not configured for, use Oracle Database
Configuration assistant (DBCA) to configure the database and
add the OLAP option.
If OLAP is not installed, use OUI to add the component and then
configure to use with DBCA.
8
Installing AWM
Analytical Workspace Manager (AWM) is bundled with Oracle client.
Use the custom installed and select the existing Oracle 10g Home.
Select product OLAP Analytic Workspace Manager and
Worksheet from the list of components.
Analytical Workspace Manager (AWM) is also standalone java client
application at
http://www.oracle.com/technology/products/bi/olap/index.html. The
installation instructions are provided in README.TXT and it can be
installed by unzipping the contents in a suitable folder.
To verify the version, either AWM from Programs->10g home or run
bin\awm.exe. Help->About.
9
Installing BI Spread Add-In





Download BI Spread Add-In from
http://www.oracle.com/technology/products/bi/spreadshe
et_addin/index.html
Excel should not be running during installation.
Run the installed executable and choose a folder to
install.
To run the spreadsheet Add-In, just run Excel. The new
menus will be available. To remove the cells populated
with BI SS Menus, use the SS menus to delete. Do not
delete with standard Excel operations.
To un-install SS Add-in either run uninstall.exe from the
installation folder or use control panel->add / remove
programs.
10
Views Used in Example
In this example, following views are created on Order
Management tables to load Dimensions and Measures.





SO_Time_V: Based on Custom table to store time
dimension data.
SO_Customer_V: Customer/Geography dimension view
SO_Product_V: Product dimension view.
SO_Sales_persion_V: Sales person dimension view.
SO_Measures_V: Measures
11
Analytic Workspace
Oracle Database stores the dimensional model in an
analytic workspace. An analytic workspace can be
considered as a collection of multidimensional data types
and the physical implementation of the logical
dimensional model.
An analytic workspace is owned by a particular user ID,
and other users can be granted access to it. Within a
single database, many analytic workspaces can be
created and shared among users.
The AW is implemented as relational table AW$<AW
name> within the database and individual components
are stores as BLOB.
12
Using AWM11 Interface
AWM is a client java tool to create & maintain AW in the database
and all other components of multi dimensional model.
AWM provides one or more view of data to DBA, Application
developer or end user.
Model View – Used often for development
 Object view – Used for OLAP DML by experts for directly manipulating
the OLAP.
There are three general steps in AW creation
 Creation of Logical Model (Dimension, Cubes & measures)
 Mapping of logical model to physical tables and columns
 Loading of data

Click AMW.EXE to start the AWM and give the connection information.
13
Using AWM11 Interface
14
Create an AW
Login to AWM. Right click to Analytical workspace under logged in
schema. The AW can be created by entering details or from existing
template.
The workspace is created with necessary structure as under.
15
Dimensional Data Model
The dimensional data model is composed of cubes, measures,
dimensions, hierarchies, levels, and attributes.
16
Cube & Measures

Cubes provide a means of organizing measures that have the same
shape; that is, they have the exact same dimensions. The edges of
the cube contain dimension members and the body of the cube
contains data values. Cubes are the parents of measures and
calculated measures.

Measures are used to store fact data within a cube. Common
examples include Unit Sales and Dollar Sales. Measures are
organized by dimensions, which typically include a Time dimension.

Calculated Measures are created by performing calculations on the
base measures stored in an analytic workspace. These derived facts
are not stored; the calculations are performed in response to
individual queries.
17
Dimensions, Levels & Hierarchies

Dimensions provide context and structure to the factual
data. They form the edges of a logical cube, and the
measures within the cube. Dimensions are the parents of
levels, hierarchies, and attributes in the logical model.
Users define these supporting objects, in addition to the
dimension itself.

Levels represent positions within the hierarchy. For
business analysis, data is typically summarized at
various levels. For example, a data warehouse may
contain monthly snapshots of a transactional database. If
months are at the base level, summarization would occur
at the quarterly and yearly levels.
18
Dimensions, Levels & Hierarchies

Hierarchies organize data at different levels of aggregation. For
example, in the Time dimension, a hierarchy is used to aggregate
data from the month level to the quarter level to the year level.
Hierarchical structures enable analysts to detect trends at the higher
levels and, by drilling down to the lower levels to identify the factors
that contributed to a trend.

Attributes provide information about the individual members of a
dimension. They are used for selecting data and organizing
dimension members.

Analytic Workspace Manager supports all common styles of
dimensions, including list dimensions, level-based dimensions and
value-based (also known as ‘parent-child’) dimensions.
19
Types of Hierarchies

List or flat dimension has no hierarchies

Level Based Hierarchy
Create a level-based hierarchy when the dimension has parent-child
relationships that define levels, such as Month and Year, or City and
Region. You must define the levels before you can finish defining
the hierarchy.

Value Based Hierarchy
Create a value-based hierarchy when parent-child relationships
exist, but you cannot group them into meaningful levels. For
example, an employee dimension may have parent-child
relationships defined in the data that identify each employee's
supervisor, but these relationships may not form meaningful levels
across the organization. You can define a value-based hierarchy
only when the dimension members are unique in the data source;
20
Types of Hierarchies

A level based Hierarchy can be

Normal hierarchies consist of one or more levels of aggregation.
Members roll up into the next higher level in a many-to-one relationship,
and these members roll up into the next higher level, and so forth to the
top level.
 Ragged hierarchies contain at least one member with a different base,
creating a "ragged" base level for the hierarchy.
 Skip-level hierarchies contain at least one member whose parents are
more than one level above it, creating a hole in the hierarchy. An
example of a skip-level hierarchy is City-State-Country, where at least
one city has a country as its parent (for example, Washington D.C. in
the United States).
21
Data Warehouse V/s OLAP
If your source data is already in a star or snowflake
schema, then you already have the elements of a
dimensional model:







Fact tables correspond to cubes.
Data columns in the fact tables correspond to measures.
Foreign key constraints in the fact tables identify the
dimension tables.
Dimension tables identify the dimensions.
Primary keys in the dimension tables identify the baselevel dimension members.
Parent columns in the dimension tables identify the
higher level dimension members.
Columns in the dimension tables containing descriptions
and characteristics of the dimension members identify
the attributes.
22
Creating a Dimension

Every member of dimension must have a unique key
across all levels.




Natural Keys are read from relational sources without modification.
If a dimension is flat or value-based, then it must use natural keys
because no levels are defined as metadata. You must take whatever
steps you need to assure that the dimension members are unique.
Surrogate keys are system generated and ensure uniqueness by
adding a level prefix to the members while loading them into the analytic
workspace
Time dimension table must have period end date and
time span. This is required for time series analysis such
as comparison with previous periods. Time dimension
should have at least one level to support time based
analysis.
23
Creating a Dimension

Right click Dimensions->Create dimension.
24
Creating Level/Hierarchy

Right click the Levels (PRODUCT)->Create Level. Enter the levels
from top to bottom. Total_Product, Class, Family, Item.
25
Mapping Dimensions


Tabular view. Drag-and-drop the names of individual columns from
the schema navigation tree to the rows for the logical objects.
Graphical view. Drag-and-drop icons, which represent tables and
views, from the schema navigation tree onto the mapping canvas.
Then you draw lines from the columns to the logical objects.
26
Viewing Data in Dimensions
After data has been loaded in the dimension, it can be viewed by right
click->View data.
27
Creating Cube/Measures & Mappings

Right click Cubes->Create cube. Enter name Sales_Cube and
select all dimensions.
28
Loading Cubes/Dimensions

Loading Cubes in similar to loading dimensions. OLAPSYS.XML_LOAD_LOG Stores the load log.
29
Viewing the Data

Query Builder
30
Viewing the Data

Right click the cube SALES_CUBE and select View Data.
31
Calculated Measures

Calculated measures return values computed at run time from the
data stored in one or more measures.

They are stored as queries just like views on relational data.

Since they do not occupy storage, there is no storage overhead

They can be used in queries as well defining more calculated
measures giving the depth to type of calculations that can be made.
32
Functions for calculated Measures

Basic Arithmetic


Advanced Arithmetic


Cumulative total, index, percent markup, percent variance, rank, share,
variance
Prior/Future Comparison


Addition, subtraction, multiplication, and division, using two measures or
a measure and a number
Prior value, difference from prior period, percent difference from prior
period, future value
Time Frame

Moving average, moving maximum, moving minimum, moving total,
year to date
33
Calculated Measures – Examples
% variance
Calculates % variance between two measures.
(Target unit – Base Unit) / Target Unit
e.g. Target Unit = Price , Base Unit = Cost
34
Calculated Measures – Examples
Index
Index calculates % difference between a measure and selected value
that serves as base number.
35
Calculated Measures – Examples
Rank
Ranks the dimensions based on value of a measure. The ranking can
be for all (Total), parent (within a parent) or Level (within same
level).
36
Calculated Measures – Examples
Share
Share calculates the ratio of measure’s value for current dimension
members to base line that can be




Total (total value of all values at same level at current member),
Parent (total value of members at same level as parent of current member),
Level (total values of all members at specific level),
Member (Value of specified member).
37
Calculated Measures – Examples
Cumulative Total
Cumulative totals start with the first time period within a particular rank
and calculate a running total up to the current member. The range
can be all members of the level or just members with the same
parent.
38
Calculated Measures – Examples
Prior Period
Gives the value at previous period which can be An year Ago, Period
Ago or Number of years, quarters etc.
39
Calculated Measures – Examples
Time frame – Period to Date
Period to Date calculates the running total within the time frame.
40
Spread Sheet Add-In
The Resultant spread sheet after the changes in Query.
41