oracle olap - nyoug€¦ · olap data mining 8. any sql interface or query tool sql, pl/sql 1....
TRANSCRIPT
Competitive Analysis-Key Indicator Tracking-
Trend Analysis-Exception Reporting-
Budgeting-Consolidation-
Variance Analysis-
Financial Modelling-Foreign Exchange/Cash Management-
Asset Liability Modelling-Activity Based Management-
Investment/Acquisitions-Reorganization Analysis-
Long Range Planning-
-Resource Allocation-Capacity Planning-Human Resource Planning
-Sales Tracking-Promotion Analysis-Sales Forecasting
-Product Profitability-Customer Profiling-Distribution Analysis-Sales Performance/Effectiveness
BoardBoard
Planning / Planning / AnalystsAnalysts
FinanceFinanceDeptDept
Sales &Sales &MarketingMarketingDeptDept
Just about everyone in the
company!!
The Business RequirementsWho generates them?
OLTP vs. OLAP
Transactional
Who?What?Where?
Report data
Analytical
Why?How?What if ?
Analyze & use data
Pedigree (short) …
OLAP is alive and well at Oracle
Over thirty years of innovation yields a complete and compelling OLAP platform
– Express, the first multidimensional database– Oracle 9iR2, the first (and only) relational-
multidimensional database– Oracle 10g
The first (and only) Grid capable OLAP platformAll new administrationAll new data access toolsAll new applications
What if …– A single database offered the openess of a relational
solution?
– … and provided the calculation power of a multidimensional engine ?
– The calculations could be defined as easily as spreadsheet formulas?
– The system was efficient to build and maintain ?
– Users experienced excellent query performance ?
Purely Relational, ROLAP, or MOLAP?A typical MOLAP implementation
• Multidimensional model: They want to inspect their data in a multidimensional format that includes dimensions, levels, hierarchies and attributes.
• Calculations: They want to define calculations that adhere to the proprietary rules that govern their particular multidimensional view of the data. For example, as in aggregation.
• Processing efficiency: Since analysis is an intensively re-iterative process, the query response time must be sub-second. OLAP engines are better designed to meet this requirement.
• Transaction model: A read-repeatable transaction model that supports what-if analysis.
The Business RequirementsWhy do they need OLAP?
Businesses need OLAP because:
Design – Logical modelsDesign – Logical models
88
coc_Measure_Result_Graph
PK,FK1 ASSOC_IDPK MEASURE_ID
coc_Officer_Dim
PK ASSOC_IDPK ROLE_CD
Lookup
coc_Profitability
PK,FK1 CST_IDPK,FK2 ASSOC_IDPK,FK2 ROLE_CDPK,FK3 PRD_CDPK,FK4 SYS_ASOF_DTPK,FK4 PERIOD_CDPK,FK4 SOURCE_DT
coc_Customer_Dim
PK CST_ID
coc_Officer_Dim
PK ASSOC_IDPK ROLE_CD
coc_Time_Dim
PK SYS_ASOF_DTPK PERIOD_CDPK SOURCE_DT
coc_Product_Dim
PK PRD_CD
coc_Opportunity
PK,FK1 CST_IDPK,FK2 ASSOC_IDPK,FK2 ROLE_CDPK,FK3 SYS_ASOF_DTPK,FK3 PERIOD_CDPK,FK3 SOURCE_DT
coc_Customer_Dim
PK CST_ID
coc_Officer_Dim
PK ASSOC_IDPK ROLE_CD
coc_Time_Dim
PK SYS_ASOF_DTPK PERIOD_CDPK SOURCE_DT
Select a purely relational implementation when …
• The analytic requirements of the business are met by the capabilities of SQL.
• There are appropriate in-house SQL skills.
• The relational engine provides satisfactory query performance.
Purely Relational, ROLAP, or MOLAP?
PRODUCT• prod A• prod B• prod C• prod D• prod E SALES
• Prod• Cust• Time
CUST• cust A• cust B• cust C• cust D• cust E
TIME• Year• Quarter• Month• Week• Day
A purely relational implementation is designed and optimized to support the efficient movement and calculation of large volumes of data.
Purely Relational, ROLAP, or MOLAP?Relational Technology
Select a ROLAP implementation when …
• The analytic requirements of the business are met by the capabilities of SQL.
• User is looking for an easier way to formulate complex queries.
• The detail data is very sparse.
Use Materialized Views to optimize performance.
Purely Relational, ROLAP, or MOLAP?
This is a ROLAP IMPLEMENTATION.
PRODUCT• prod A• prod B• prod C• prod D• prod E SALES
• Prod• Cust• Time
CUST• cust A• cust B• cust C• cust D• cust E
TIME• Year• Quarter• Month• Week• Day
Cube
Dimension Object• Dimension• Hierarchy• Level• Attribute
Dimension ObjectDimension
Object
Dimension Object
Purely Relational, ROLAP, or MOLAP?Relational Technology
Select a MOLAP implementation …
• When the analytic requirements of the business need the extended analytic, forecasting and planning functionality of Multidimensional Database Technology.
• When the analysis includes lots of calculated and aggregated Key Performance Indicators
• Need an easier way to define complex or proprietary calculations.
• Need a transaction model that supports what-if analysis.
Purely Relational, ROLAP, or MOLAP?
$ $ $ $$ $ $ $$ $ $ $$ $ $ $
SALES dimensioned by PRODUCT, CUSTOMER,TIME
Cube
Purely Relational, ROLAP, or MOLAP?Multidimensional Technology
This is a MOLAP implementation
Some benefits of the multidimensional processing model . . .
• A separate query is formulated and executed for each dimensional component of the query.
Ease of use feature!
• No JOIN is required when using the multidimensional technology.
Improved performance!
• “Aggregate then filter” methodology is used.Consistent, correct results. Intelligent drill!.
Purely Relational, ROLAP, or MOLAP?Multidimensional Technology
Components include:
• A powerful SQL calculation engine• A powerful multidimensional calculation engine• Multidimensional data storage and retrieval• Programming APIs for SQL, PL/SQL, and Java• Dimensionally aware data manipulation language
(OLAP DML)
• SQL access to multidimensional data
ArchitectureThe OLAP Option in the Oracle Database
Prior Architecture
Clients
MultidimensionalDatabases
OperationalSources
Data MartsData Warehouse
Runtime
Warehouse ETL
OLAP ETL
DB2, SQL Server,
Oracle, etc.
Oracle Call InterfaceOracle Call Interface
Relational TechnologyRelational Technology
SQL EngineSQL Engine
Object TechnologyObject Technology
Table FunctionsTable Functions
OLAP TechnologyOLAP Technology
Multidimensional Multidimensional EngineEngine
Storage
Relational Data
MultidimensionalData
Oracle Database
OLAP APIOLAP APIMetadataMetadata
JDBCJDBC
Current ArchitectureThe OLAP Option in the Oracle Database
Because no single tool will satisfy all of the users in an organization …
… Oracle has products that represent every class of reporting tool.
Architecture – Open access
DW
ETL
OLAP
Data Mining
8. Any SQL interface or query tool SQL, PL/SQL 1. Simple reporting tools
that execute pre-defined SQL statements, and has no knowledge of OLAPHTML DB
3. Multidimensional object-aware OLAP-aware tools thatgenerate SQLDiscovererPlus OLAP
2. OLAP-aware tools that generate SQL Discoverer Plus
4. Tools that provide the ability to define highly formatted reportsin multiple formatsReports
6. Spreadsheet toolsOracle Excel Add-in
7. Custom built applicationsOLAP Java API
5. OLAP-aware application building toolsBI Beans
HTML DB
DiscovererPlus OLAP
Oracle Reports(OLTP)BI Beans
OLAP ExcelAdd-in
Forms Builder.lnkOracle Forms
Any SQL Query Tool
DiscovererPlus
Architecture – Open access
DW
ETL
OLAP
Data Mining
8. Any SQL interface or query tool SQL, PL/SQL 1. Simple reporting tools
that execute pre-defined SQL statements, and has no knowledge of OLAPHTML DB
3. Multidimensional object-aware OLAP-aware tools thatgenerate SQLDiscovererPlus OLAP
2. OLAP-aware tools that generate SQL Discoverer Plus
6. Spreadsheet toolsOracle Excel Add-in
7. Custom built applicationsOLAP Java API
5. OLAP-aware application building toolsBI Beans 4. Tools that provide
the ability to define highly formatted reportsin multiple formatsReports
HTML DB
DiscovererPlus OLAP
Oracle Reports(OLTP)BI Beans
OLAP ExcelAdd-in
Forms Builder.lnkOracle Forms
Any SQL Query Tool
DiscovererPlus
Architecture – Open access
Every Oracle tool can access the power of the
Analytic Workspace.
DW
ETL
OLAP
Data Mining
8. Any SQL interface or query tool SQL, PL/SQL
3. Multidimensional object-aware OLAP-aware tools thatgenerate SQLDiscovererPlus OLAP
2. OLAP-aware tools that generate SQL Discoverer Plus
4. Tools that provide the ability to define highly formatted reportsin multiple formatsReports
6. Spreadsheet toolsOracle Excel Add-in
7. Custom built applicationsOLAP Java API
5. OLAP-aware application building toolsBI Beans
1. Simple reporting tools that execute pre-defined SQL statements, and has no knowledge of OLAPHTML DB
HTML DB
DiscovererPlus OLAP
Oracle Reports(OLTP)BI Beans
OLAP ExcelAdd-in
Forms Builder.lnkOracle Forms
Any SQL Query Tool
DiscovererPlus
Architecture – Open access
In fact, thepower of the
Analytic Workspace can be accessed by any third-party tool that emits
SQL!
select … from view or table
Relational Multidimensional
Relational View(s)
SQL Generator
Application
Metadata
OLAP API
Row and Table ADTs
OLAP_TABLE
This object represents the
Analytic Workspace
Let’s take a closer look at
the AW.
A Closer Look
• An Analytic Workspace is a container that holds multidimensional data and objects.
• The data data in the AW is manipulated by the multidimensional calculation engine that is imbedded in the RDBMS.
• AWs and the multidimensional engine were designed for efficient processing of multidimensional calculations.
A Closer LookAnalytic Workspace
While ADo B
End
Program source code
Hierarchydefinitions
Datarelationships
Definitions for logical groupings of data
--- CUBES CUBES CUBES ---
Dimensiondata
Measuredata
Formulas and equations
Dimension definitionsMeasure definitions
OLAPDML
A Closer LookAnalytic Workspace
Use these tools to design and build your OLAP data warehouse:
• Oracle Warehouse Builder – End-to-end ETL tool
• Enterprise Manager – Describe the star or snowflake logical data model
• Analytic Workspace Manager – Build AW from star schema
• DBMS_AWM APIs – Build AW from star schema
• OLAP DML – Programmatically build the AW and all of its objects
Prepare the OLAP Data Warehouse The tools
Dimensional Model
Dimensional Model
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S
