Oracle Openworld Directory Index

Agenda
Business Intelligence Trends
BI Reporting Technologies
BI Reporting Technologies
BI Reporting Technologies
Multidimensional BI Application Types
Desktop OLAP (DOLAP)
Multidimensional OLAP (MOLAP)
Relational OLAP (ROLAP)
Oracle 9i Data Warehouse Functionality
Sample Data Warehouse Schema
Sample Data Warehouse Star Schema
Query Rewrite
Materialized Views
Used as fact table aggregates.
Cost Based Optimizer rewrites queries and funnels query requests to associated materialized views.
Materialized View Refresh Methods
Materialized OLAP Cube
Oracle 9i allows for a materialized OLAP cube within the relational database.
Precalculating all of the OLAP permutations within the database will yield extremely fast query results.
Materializing all of the OLAP permutations may overwhelm the RDBMS.
Indexing
B Tree
Node and leaf organization in which the leaf levels contain the index column value and pointers to associated rows of data.
B-tree indexes are the most, “Expensive” Oracle index in terms of storage and time required to build.

Bitmap Index
Bitmap indexes store a bit (0,1) in the index entry instead of the indexed column value.
Space required for the Bitmap Indexes are dramatically less than a B-Tree index.
Bitmap indexes are more efficient for low cardinality columns.

Bitmap Join Index
Index that spans multiple tables that will improve performance between the joined tables.
Bitmap join indexes take less space, is a single index, and contains index values for only rows that match.
Particularly useful for star transformation queries.
Index Skip Scan
A query may skip the first or nth initial columns in a B-TREE index and avoid full table scans based on the optimizer’s evaluation of the next columns cardinality.


Star Transformation Queries
A type of query rewrite in which the cost based optimizer chooses to rewrite the query using a star transformation.
Applicable for DOLAP and ROLAP BI applications.
ROLAP applications may not have the opportunity to Rewrite queries using a Star Transformation due to ROLAP’s propensity for generation of multiple SQL passes and temporary tables.
Parallel Execution /Intra Partition Parallelism
Partitioning
Data is segregated into multiple physical partition objects.
BI application access smaller data sets.
Benefits all forms of BI applications.
Partitioning Methods:
Range
List
Hash
Composite
Transportable Table Spaces
Allows portions of a database to be copied or moved to a different database via operating system file copies.
Both databases are on-line
Transported data is still readable to the source database users.
Transportable Table Space Applications:
Moving data from the OLTP database to the Staging database
Moving data from the Staging database to the Data Warehouse database
Moving data from the Data Warehouse database to the Data Mart databases
Moving data from the Data Warehouse database to an Archive destination with the option of restoring at a later date.

SQL 99 Support
OLAP Enhancement
Functionality

Window Function Allows the calculation of moving and cumulative aggregates.
Aggregate and Rank Function Statistical functions for correlation, covariance, and linear regression.
Grouping Sets Extended group by functionality.
ANSI WITH Allows for the creation of temporary results within a query.
Summary
Utilizing Oracle Data Warehouse Functionality in Mainstream Business Intelligence Applications #814