Oracle Database 10g: Using OLAP

This class is not applicable to Oracle9i users.

In Day 1 of the course you will learn Oracle’s OLAP technology, terminology, and concepts. By the end of day 1, you will have built a simple Analytic Workspace (AW) with the basic cubes and dimensionality. You will view elements of your data model using preview tools that are built into the Analytic Workspace Manager tool. You will begin to build a more sophisticated AW by the end of the day.

On Day 2, you will work on more sophisticated OLAP objects and analysis. You will and add objects of more complexity to the AW. Using the BI Spreadsheet Add-In and Discoverer Plus OLAP tools, you will view and analyze the data in the AW, including calculated measures. The last lesson is intended to introduce you to more strengths of the OLAP calculation engine.

• Describe OLAP concepts and terminology
• Build analytical reports that access the multidimensional data in the analytic workspace using the Discoverer Plus OLAP tool
• Create dimensions, cubes, and measures using the AWM10g tool for deployment with the OLAP analytical tools
• Use the Oracle BI Spreadsheet Add-in to query OLAP data in an Excel spreadsheet
• Create custom measures for analysis

Audience

• Business Intelligence Developer
• Technical Consultant

Course Topics Defining OLAP Concepts

• Reviewing business requirements
• Understanding the dimensional model
• Historically, how has OLAP been done?
• Handling Multidimensional Needs with Oracle OLAP 10g

Understanding Oracle’s OLAP Technology

• Server
• Tools for the end user
• Tools for the administrator

Building an Analytic Workspace

• What is an Analytic Workspace?
• Using the AWM 10g interface
• Creating an AW
• Understanding Cubes
• Creating Dimensions
• Using templates for dimensions
• Creating a Cube
• Creating Measures

Applying Advanced Dimensional Design and Cube Processing Techniques

• Adding Attributes to the dimensional model
• Using Partitioning
• Understanding aggregation methods
• Examining dimensional design considerations
• Performing ongoing maintenance: refreshing data

Creating Calculated Measures and Applying Data Refresh Options

• Calculated measures
• Modify calculated measures
• Refreshing data

Using the BI Spreadsheet Add-In to Query OLAP Data in Excel

• Installing the BI Spreadsheet Add-In tool
• Connecting to an Oracle OLAP data source
• Using the OLAP Query Wizard
• Creating a ranking report
• Using saved selections
• Creating OLAP calculations
• Applying Excel formatting to OLAP data

Building Analytical Reports with Discoverer Plus OLAP

• Enabling the Discoverer Plus OLAP user
• Creating a Connection, Workbook, and Worksheet
• Using the OLAP Query Wizard
• Using the Discoverer Plus Work Area
• Understanding Query Steps
• Applying User Attributes in Query Steps
• Revisiting OLAP Calculations
• Viewing and modifying the graph

Enhancing Analytic Content

• Understanding the difference between Model and Object View
• Using the OLAP Worksheet interface
• Entering OLAP DML commands
• Examining Oracle OLAP Calculation Engine Capabilities

Course Objectives

• Define Oracle’s OLAP terminology
• Describe the OLAP option and components
• Explain Oracle’s business intelligence end-user and administrative tools
• Create and populate an AW in the Oracle database
• Load data into the multidimensional cubes from a provided set of tables
• Describe how attributes are used
• Describe advanced cube-processing techniques, such as partitioning and aggregation methods
• Describe how to manage sparse data by applying Oracle OLAP compression technology
• Refresh data
• Create calculated measures based on basic arithmetic, advanced arithmetic, prior and future comparisons, and time frames
• Install and use the BI Spreadsheet Add-In tool
• Create worksheets that contain cross tabs and graphs of OLAP data, and use many Discoverer Plus OLAP analytic techniques
• Create and use saved selections
• Create reports of OLAP data based on trend analysis and ranking data
• Add visual cues (stoplights) to the report that highlight specific data ranges
• Discuss how OLAP DML can be used to enhance calculated measures