Reporting & Analytics for EBS with Oracle OLAP. Kailash Pareek. Agenda. Author Introduction Background Information Oracle OLAP Designing the Cube Reporting with Excel. Author Introduction. B.E (ECE), I.I.Sc., 1980 16 years Heading IT departments 11 Years in IT Industry
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 non-volatile 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).
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”
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.
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.
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.
In this example, following views are created on Order Management tables to load Dimensions and Measures.
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.
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.
There are three general steps in AW creation
Click AMW.EXE to start the AWM and give the connection information.
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.
The dimensional data model is composed of cubes, measures, dimensions, hierarchies, levels, and attributes.
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.
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;
If your source data is already in a star or snowflake schema, then you already have the elements of a dimensional model:
After data has been loaded in the dimension, it can be viewed by right click->View data.
Calculates % variance between two measures.
(Target unit – Base Unit) / Target Unit
e.g. Target Unit = Price , Base Unit = Cost
Index calculates % difference between a measure and selected value that serves as base number.
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).
Share calculates the ratio of measure’s value for current dimension members to base line that can be
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.
Gives the value at previous period which can be An year Ago, Period Ago or Number of years, quarters etc.
Period to Date calculates the running total within the time frame.
The Resultant spread sheet after the changes in Query.