Deep Dive into Oracle BI EE 10g
Download
1 / 151

Collaborate’10 IOUG Forum, Las Vegas, April 2010 Introduction to the Session - PowerPoint PPT Presentation


  • 68 Views
  • Uploaded on

Deep Dive into Oracle BI EE 10g. Collaborate’10 IOUG Forum, Las Vegas, April 2010 Introduction to the Session. T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com. Who Am I?. Oracle BI&W Architecture and Development Specialist

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Collaborate’10 IOUG Forum, Las Vegas, April 2010 Introduction to the Session' - herne


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Deep Dive into Oracle BI EE 10g

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Introduction to the Session

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Who am i
Who Am I?

  • Oracle BI&W Architecture and Development Specialist

  • Co-Founder of Rittman Mead Consulting

    • Oracle BI&W Project Delivery Specialists

  • 12+ years with Discoverer, OWB etc

  • Oracle ACE Director, ACE of the Year 2005

  • Writer for OTN and Oracle Magazine

  • Author of forthcoming Oracle Press OBIEE Book

    • Due Q4 2009

  • Longest-running Oracle blog

    • http://www.rittmanmead.com/blog

  • Ex-Chair of UKOUG BIRT SIG

  • Co-Chair of ODTUG BI&DW SIG

  • Editor of UKOUG “Oracle Scene” Magazine

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com


Rittman mead consulting
Rittman Mead Consulting

  • Oracle BI&DW Project Specialists providing consulting, training and support

  • Clients in the UK, USA, Europe, Middle-East

  • Voted UKOUG BI Partner of the Year 2008

  • Consultants in Europe and North America

  • Regular speakers at user group and Oracle events

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com


What do we do
What Do We Do?

  • Consulting, mentoring and support for Oracle BI projects

  • Technical design, project management, QA

  • Deliver entire project or just supplement your team

  • Advise on Oracle BI technology strategy

  • On-site training

  • Event organization

  • Support

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Rittman mead blog
Rittman Mead Blog

  • Longest-running Oracle (BI) Blog

  • Ten authors including Mark Rittman, Borkur Steingrimsson, Stewart Bryson, Peter Scott

    • and now Venkat J (ex-http://oraclebizint.wordpress.com)

  • Articles on OBIEE, OWB, DW, Hyperion etc

  • All past presentations and articles

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Bi forum brighton 2009
BI Forum Brighton 2009

  • Expert-level developer conference run in Brighton, May 2009

  • Attended by Oracle, Client and Industry developers and experts from around the world

  • Two days of presentations, discussions, knowledge exchange & networking

  • Focus on OBIEE, ODI, Essbase

  • Running again in May 2010

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com


What is this session about
What Is This Session About?

  • Oracle Business Intelligence Enterprise Edition Plus

  • Oracle’s Next-Generation Business Intelligence Platform

  • In today’s session you will learn:

    • What is OBIEE

    • How it connects to data sources

    • What data models it supports

    • How it federates data

    • How the BI Server joins data

    • How reports and portals are created

    • How it handles Essbase data

    • What’s coming in OBIEE 11g

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Agenda
Agenda

  • Introduction to Oracle BI EE

  • Oracle BI EE 10g Architecture

  • Data Modeling using star and 3NF data

  • Federating multiple data sources

  • Creating reports, dashboards and alerts (an overview)

  • Incorporating Essbase and other Multi-dimensional data

  • What’s coming in OBIEE 11g

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


The seminar format
The Seminar Format

  • Presentations and discussions

  • A break around 12pm

  • Finish at 3pm

  • Slides will be available online

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Participant introduction
Participant Introduction

  • The most important people in this room are you

  • It helps if I know a bit about you

    • Your Oracle BI experience

    • Your course objectives

    • Where you work

  • Introduce yourself to me, and to the other seminar participants

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Introduction to the Session

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 1 : Introduction to OBIEE 10g

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle business intelligence enterprise edition plus 10g
Oracle Business Intelligence Enterprise Edition Plus 10g

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Hot pluggable and standards based
Hot-Pluggable and Standards-Based

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Connectivity to most applications and data sources
Connectivity to Most Applications and Data Sources

  • Oracle Business Intelligence Enterprise Edition is a “federated query server”

  • Connectivity to Oracle, MS SQL Server, Sybase, IBM DB2, Teradata etc

  • MS AS, Essbase, Oracle OLAP

  • Files, XML

  • Microsoft Excel

  • etc

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Common enterprise information model
Common Enterprise Information Model

  • Enables consistency, security, reuse, flexibility

  • Role-based views of the organization

  • Single metadata model of the business

  • Consistent definition of business measures, metrics, calculations

  • Drill paths, summaries, security

  • Model once, deploy anywhere

  • Across any data source

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Pervasive information access through a unified bi foundation
Pervasive Information Access through a Unified BI Foundation

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle business intelligence applications prebuilt analytics
Oracle Business Intelligence Applications - Prebuilt Analytics

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle interactive dashboards
Oracle Interactive Dashboards Analytics

  • “One-stop shop” for reporting and analysis

  • All your reports in one place

  • Fully interactive

  • Graphs, reports, links

  • Delivered through your browser

  • Download to Excel

  • Print & Save your favoritereports

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle answers
Oracle Answers Analytics

  • Used for creating and amending reports

  • Point-and-click interactive environment

  • Tables, pivot-tables

  • Graphs

  • Gauges

  • Commentary text

  • Answers reports are then publishedto dashboard

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Sense and respond
Sense and Respond Analytics

  • Delivers/Analytic Agents provides true real-time in context sense and respond capabilities across systems tailored for any user on any device.

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Sense and respond1
Sense and Respond Analytics

  • BI can also be embedded directly within the business processes.

  • All BI Content can be exposed as a set of services that are callable by SOA enabled technologies and applications.

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle disconnected analytics
Oracle Disconnected Analytics Analytics

  • Fully-featured disconnected client

  • The user experience is identical to the connected user.

  • Dashboard and Answers functionality will act identically as if the user were connected to the server.

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Guided analytics
Guided Analytics Analytics

  • Provide guidance and “best next step” for users

  • Conditionally display alerts, messages and entire reports

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle briefing books pack and go
Oracle Briefing Books - “Pack and Go” Analytics

  • Bring along an electronic “Briefing Book” which is a collection of your most important dashboards.

  • Briefing Books are extremely small, and can be delivered daily for enterprise collaboration.

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi publisher pixel perfect bi publishing
Oracle BI Publisher - “Pixel-Perfect” BI Publishing Analytics

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Microsoft office bi integration
Microsoft Office BI Integration Analytics

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

Oracle BI EE 10g

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Summary
Summary Analytics

  • Oracle BI EE enables analytic analysis within your organization

  • Next-Generation Architecture, web-based analysis

  • Oracle BI Answers and Oracle BI Dashboards for analysis of data

  • Oracle BI Delivers provides scheduling and “sense and respond”

  • Offline analysis using Oracle BI Disconnected Analytics and Briefing Books

  • Microsoft Office integration to provide BI directly within your working environment

  • Many tools and opportunities

  • Oracle BI EE comes with a default repository so you can experiment straightaway

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g Analytics

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 1 : Introduction to OBIEE 10g

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g Analytics

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 2 : OBIEE Architecture and Relational Data Modeling

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi suite enterprise edition plus
Oracle BI Suite Enterprise Edition Plus Analytics

  • Oracle “Next-Generation” BI tools platform

  • Based on Siebel Analytics

  • Originally developed by nQuire

  • Standards (J2EE) based

  • Heterogeneous connectivity

  • Server and Web-based

    • Oracle BI Server

    • Oracle Presentation Server

    • Oracle BI Answers

    • Oracle BI Delivers

    • Oracle BI Administration

    • Job Manager, Catalog Manager etc

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Obiee 10g architecture
OBIEE 10g Architecture Analytics

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi server
Oracle BI Server Analytics

  • Calculation and Data Integration Engine

    • “Virtual Data Warehouse”

    • Allows reporting across multiple sources

    • Data accessed though metadata layer

  • Connects to RDBMS and OLAP data

    • Oracle Database (including Oracle OLAP)

    • IBM DB2

    • Microsoft SQL Server

    • Teradata

    • Microsoft Analysis Services (OLAP)

    • SAP BW 'info cubes'

  • Security, Summary Management

  • Translates incoming “logical SQL” in to optimized, physical SQL

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi presentation server
Oracle BI Presentation Server Analytics

  • “BI Application Server”

  • Single application that provides Oracle BI Answers, Dashboards

  • Metadata stored in Web Catalog

  • User Profiling, Security and Session Management

  • SOAP Web Services, XML and URL Interface

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi delivers server
Oracle BI Delivers Server Analytics

  • Scheduling and distribution

  • Uses iBots (“Intelligent Bots”)

  • Integration (through Java) with BPEL

  • iBot chaining, calling from BPEL, calling BPEL

  • Permits alerts, through execution of Answers reports

    • If report returns rows, alert triggered

    • Alert appears on dashboard, via email, SMS, cell phone

    • Can trigger narrative andconditionally-appearing reportson dashboard

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi publisher server
Oracle BI Publisher Server Analytics

  • New in 10.1.3.2, based on Oracle XML Publisher

  • Separate application, integrated with Oracle BI Presentation Server

    • Initially usernames and passwords integrated

    • Thereafter separately administered

  • Provides high-fidelity layout, positioning ofdata, charts etc

  • Multiple data sources

    • OBIEE Metadata Layer

    • Oracle BI Answers reports

    • Any source accessible via SQL

    • XML over HTTP

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Oracle bi administration
Oracle BI Administration Analytics

  • MS Windows application for managing BI Server

  • Creates and manages Common Enterprise Information Model

    • Physical, logical and presentation layers

  • Manage other aspects of BI Server

    • Security

    • Scheduler (Delivers server)

    • Jobs

    • Joins

    • Marketing

    • Sessions

    • Cache

  • Utilities and Tools

  • Connect to BI Server via ODBC DSN

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Other tools
Other Tools Analytics

  • Catalog Manager

  • Job Manager

  • OC4J Start / Stop (when hosted in OC4J)

  • System Manager

  • ODBC Client

  • Oracle BI Publisher Enterprise

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Core bi server functionality
Core BI Server Functionality Analytics

  • Data Integration through Business Model View

  • Calculation and analytical engine

  • Support for RDBMS and OLAP data sources

  • Intelligent Request Generation and Optimized Data Access

  • Security and Data Scoping

  • ODBC Interface out, native RDBMS andOLAP interfaces in

  • Clustering, replication, supportfor Windows and Unix/Linux hosts,32-bit and 64-bit

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Data access
Data Access Analytics

  • Reporting data may be sourced frommultiple databases, applications

    • Oracle Database (including Oracle OLAP)

    • IBM DB2

    • Microsoft SQL Server

    • Teradata

    • Microsoft Analysis Services (OLAP)

    • SAP BW 'info cubes'

    • SAP, Peoplesoft, Siebel, E-Business Suite

  • Oracle BI Server joins this sourcedata together and presents alogical business model

    • Facts

    • Dimensions

    • Calculations

    • Joins

    • Presentation folders

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Common enterprise information model1
Common Enterprise Information Model Analytics

  • Metadata layer for OBIEE tools

  • Three layers of metadata

    • Physical layer, connections to sources

    • Logical layer, denormalized factsand dimensions

    • Presentation layer, “report-centric” folders

  • Maintained using BI Administrator

  • Model is the “center” of the OBIEE universe

    • “Model First” development

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Data modeling using oracle bi server
Data Modeling using Oracle BI Server Analytics

  • Design goal for the BI Server metadata layer is to create a Star Schema

    • Fact tables

    • Dimension tables

    • Drill paths

  • Source data can either be pre-integrated (data mart,data warehouse) or integrated using the BI Server (“federated”)

    • Federated can be faster to deliver (report in place)

      • Assumes data sources share common key values

    • Data marts are simpler and faster to query

  • MDX (OLAP, Multi-dimensional) data is converted into RDBMS row-sets

  • Logical layer is main integration point

  • Presentation layer allows “report centric” folders

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Information model layers
Information Model Layers Analytics

  • Physical Layer contains connections, physical tables

    • Connections start as ODBC, switch to native (OCI etc)

    • Primary keys and foreign keys

  • Logical layer is where the data is integrated

    • Logical fact tables, logical dimension tables

    • Dimensions

    • Calculations

    • Complex joins to define relationships

  • Presentation layer is where data is presented

    • Can be a simple copy of the logical layer

    • Or can be made more personalized

    • Report centric

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Star schema design approach
Star Schema Design Approach Analytics

  • Classic “Kimball” approach

  • Fact tables contain measures, and joins to dimensions

    • No attributes except for degenerate dimensions

    • Leave IDs in, remove in Presentation layer

  • Dimension tables contain lookup data

    • Customer, Product, Time etc

    • Dimensions can however be facts

      • Use alias tables to disambiguate

  • Dimension objects define drill path

    • Multiple hierarchies supported

    • Ragged hierarchies possible (see later)

    • Time dimensions enable time-series queries

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Sample business model
Sample Business Model Analytics

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Data modeling workflow steps
Data Modeling Workflow Steps Analytics

  • Create logical model in business model & mapping layer

  • Import physical data sources & define primary and foreign keys

  • Map logical model to physical model

  • Create initial calculations

  • Create dimensions

  • Rename and tidy-up logical model

  • Create initial presentation model

  • Create complex calculations, time dimensions etc

  • Add additional columns to presentation model

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Exercise reporting scenario
Exercise Reporting Scenario Analytics

  • Global Company has a data warehouse, containing order and customer data

  • Requirement is to take the existing warehouse (normalized)and turn it into a star schema (denormalized)

  • For now, OBIEE will be used to map the data

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 1 create logical model in business model layer
Step 1: Create Logical Model in Business Model Layer Analytics

  • Firstly, create new logical model

  • Then create logical tables and columns

    • Either create from scratch, then connect to physical(best practice)

    • Or define by dragging and dropping physical tables

  • Start from fact table and work outward to the dimensions

  • Do one fact/dimension combination first

  • Then add more facts

  • ... and more dimensions

  • Add details on datatypes etc later on

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Create a new Logical Business Model in the Repository

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 2 create physical layer
Step 2: Create Physical Layer Analytics

  • Create either a native (i.e. OCI) or ODBC connection through to data source

  • Import tables, views, keys etc from data source

  • Define keys, foreign keys if needed

  • Update row counts

  • Define table aliases

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Physical join types
Physical Join Types Analytics

  • If source data has foreign key joins, they will be imported

  • If not, they can be defined in the physical model

  • Two types of join

    • Foreign key joins (e.g. CUSTID = CUSTID)

      • 1:N joins only

    • Complex joins (TRAN_DATE between STARTDATEID and ENDDATEID)

      • also supports 1:1, O:1, 1:O etc

  • Important to create physical joins before importing in to logical layer

    • Logical joins rely on physical joins to define paths

    • Makes life a lot simpler

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Importing the Physical Table Metadata into the Repository

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 3 mapping the logical model to the physical model s
Step 3: Mapping the Logical Model to the Physical Model(s) Analytics

  • Once you have your logical model and one or more physical models, you can map them together

    • The simpler the mapping, the better

  • Drag and drop physical columns on to the logical model

  • Logical model will inherit physical data types

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Logical table sources
Logical Table Sources Analytics

  • Logical tables can have more than one physical source

  • Can create >1 Logical Table Source

    • BI Server creates two physical queries

    • Useful when mapping data from different levels

    • and when using “fragmentation”

  • Each Logical table source can have >1 table mapped

    • BI server creates one query per LTS, joiningas per physical foreign key joins

    • Used when you need columns from other joined tables in your logical table (join only happens if additional columns requested)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Consider where to join data
Consider Where to Join Data Analytics

  • Again, consider where to integrate and join data

  • Whilst the BI Server can join data, it may be faster to load into a data mart

    • ETL tools can integrate data at time of load

    • Denormalized data structures can be faster than joining at report time

    • Indexing, Summary Management, Query Optimization

  • Creating a data warehouse still is the optimal solution

    • BIEE data source joining features are most appropriate when bringingnew data in that’s not yet in the data warehouse

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Logical join types
Logical Join Types Analytics

  • Logical tables are by default not joined

  • Logical layer supports both foreign key and complex joins

  • Foreign key joins not recommended

    • Restrict choices that BI server can make over join paths

    • Only there for backward compatibility

  • Complex joins are preferred

    • Indicates relationship between tables

    • Allows BI Server to determine best join path

      • Useful when more than one Logical Table Source

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Map Logical to Physical Model, and create Logical Joins

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 4 create initial calculations
Step 4: Create Initial Calculations Analytics

  • Calculations are created as additional logical columns

  • Sit alongside existing logical columns derived from physical layer

  • Calculations can alsobe created within report

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Obiee sql and the expression editor
OBIEE SQL and the Expression Editor Analytics

  • Calculations within the repository are defined using the Expression Editor

  • Uses OBIEE SQL Syntax

  • Reference functions, expressions, logicalcolumns, constants,variables

  • Same SQL syntax acrossall supported databases

  • Ability to pass-throughfunction calls usingEVALUATE function

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Create calculations in the optimal location
Create Calculations in the Optimal Location Analytics

  • Whilst the BI Server can create calculations, it may be more appropriateto do this in the underlying database

    • Oracle Database analytic functions (LAG, LEAD, RANK etc)

    • Oracle Database OLAP functions (time-series, forecast, allocation)

  • Use the available architecture efficiently

    • Perform calculations as close to the source data as possible

    • Use dedicated OLAP technology if available

    • Use BI Server to provide features not found in the sources

  • Just because the BI Server can do something, doesn’t mean it should do something

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Creating Calculations

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 5 create dimensions and hierarchies
Step 5: Create Dimensions and Hierarchies Analytics

  • Dimensions define hierarchical relationships between columns

    • Also used when defining level-based measures and summaries

  • Level-based, starts with Grand Total level

    • One-way export out to Oracle dimensions (DDL) also possible

  • Enables hyperlinks on columns within dimension levels

    • Drill-down through the hierarchy

    • No drill-up or drill to related (yet)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Multiple hierarchies and ragged unbalanced hierarchies
Multiple Hierarchies and Ragged / Unbalanced Hierarchies Analytics

  • Dimensions with multiple hierarchies are possible

    • All hierarchies must share common top and bottom levels

  • Ragged hierarchies, unbalanced hierarchies only supported formulti-dimensional data sources

    • MS Analysis Services, SAP BW, Oracle Hyperion Essbase

    • For relational, need to balance hierarchy beforehand

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Creating Dimensions

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 7 create initial presentation models
Step 7: Create Initial Presentation Models Analytics

  • Business models themselves are not visible to users

    • Users see presentation models whose columns are defined in business models.

    • Appear as “subject areas” in the Oracle BI Answers

    • Appear as “databases” to client tools using ODBC

  • Can create multiple Presentation Catalogs from single Logical Model

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Creating Presentation Models

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 9 creating time dimension and time series calculations
Step 9: Creating Time Dimension and Time-Series Calculations Analytics

  • OBIEE 10.1.3.2 supports limited time-series functions

  • AGO – value of a measure x months, quarters, years agoAGO(Videostore."Sales fact view".Sales, Videostore.TimesDim."Month", 1)

  • TODATE – value of a measure to the end of a month, quarter, yearTODATE(Videostore."Sales fact view".Sales,

  • Videostore.TimesDim."Quarter", 1)

  • Requires at least one “time dimension” in the metadata layer

    • Requires at least one or more Chronological keys

    • Monotonically increasing value (DAY etc)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


The calculation wizard
The Calculation Wizard Analytics

  • Wizard that generates share, percentage change etc calculations

  • Useful in conjunction with Time Dimension

    • % Change since last month

    • Change since last month etc

    • Index

    • Percent

  • Wraps ISNULL around calcs

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Creating Time Dimension and Time-Series Calculations

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Reporting across multiple data sources
Reporting Across Multiple Data Sources Analytics

  • OBIEE can combine and join data from multiple data sources

  • Data is retrieved in separate SQL, MDX calls and joined using the BI Server

  • Allows you to perform federated queries

  • Watch out for performance though

    • End goal should be to eventually move reporting data to a data warehouse

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Federated query scenario
Federated Query Scenario Analytics

  • Additional data exists in an Excel spreadsheet

  • Contains quotas, plus information on products and dates

  • Quotas are at the product category and month level

    • Existing data is at the product and day level

  • Requirement is to join data, but only show quotas the relevant level

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Joining a second data source step 1
Joining A Second Data Source : Step 1 Analytics

  • Import the new schema into the physical model, ensure FK and PKconstraints are set

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Joining a second data source step 2
Joining A Second Data Source : Step 2 Analytics

  • Use the Join Manager, or Physical Model Diagrammer, to create joins between tables in the two schemas

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Joining a second data source step 3
Joining A Second Data Source : Step 3 Analytics

  • Add the new dimension tables as additional logical table sourcesto the existing logical dimension tables

  • Set the dimension hierarchy level at which they are applicable

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Joining a second data source step 4
Joining A Second Data Source : Step 4 Analytics

  • Add the additional measures to the fact table

  • Set the dimension levels at which the measures become relevant

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Demonstration Analytics

  • Adding Additional Data Sources

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Summary1
Summary Analytics

  • The Business Model is central to Oracle BI Suite Enterprise Edition

    • Consists of physical, logical and presentation layers

  • Foundation is properly-joined physical table sources

  • Denormalized star schema built using complex logical joins

  • Calculations (including time-series calculations) not available in the source database can be added to logical model

    • Where possible though, use in-database analytics and OLAP

  • Dimensions defined to determine drill paths

  • Additional data sources can be combined in a single business model

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g Analytics

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 2 : OBIEE Architecture and Relational Data Modeling

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Inside the Oracle BI Server (10g) Analytics

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 3 : Inside the Oracle BI Server

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Inside the oracle bi server
Inside the Oracle BI Server Analytics

  • In this session, we will look in-depth at the Oracle BI EE Oracle BI Server

  • What components are in the BI Server

  • What is the Navigator, and how does itturn incoming logical requests intooptimized physical SQL and MDX

  • What happens when it processes a query

  • What happens when the BI Server joinsresultsets in memory

  • And what influences SQL generation, joinstrategy and BI Server resource usage

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Why is this topic important
Why Is This Topic Important? Analytics

  • Now this could be a pretty boring topic

  • But its one of the most important parts of the BI EE technology stack

  • It provides all of these key features

    • Query Federation

    • Heterogeneous Connectivity

    • The Semantic Model

    • Aggregate Navigation

    • Time Travel (sort of)

  • So what really happens when Answers fires off a query, and the BI Server goes and gets the data...?

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


The oracle bi ee 10g server architecture
The Oracle BI EE 10g Server Architecture Analytics

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


The oracle bi server 10g architecture
The Oracle BI Server 10g Architecture Analytics

  • Logical SQL interface via ODBC and JDBC

  • Intelligent Request Generator, made up of

    • Logical request generator

    • Navigator

    • Optimized Query Rewrites

    • Execution Engine

    • Cache Services

  • Other services include

    • Load balancing (in conjunction with cluster controller)

    • Query governance

    • Session Management

    • System / Perf Monitoring (JMX etc)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Intelligent request generation and the bi server navigator
Intelligent Request Generation and the BI Server Navigator Analytics

  • The Navigator is the key component thatparses incoming requests and generatesthe relevant physical SQL and MDX queries

  • Invoked if cache cannot be used

  • Handles the following translations

    • Function push-down

    • Multi-pass logic (time series etc)

    • Aggregate navigation

    • Fragmentation navigation

  • Responsible for generating optimizedSQL and MDX for the mapped data sources

  • Uses rules and definitions present in the RPD

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


The BI Server Functional Compensation Model Analytics

  • The BI Server tries to push functions and joins down to the underlying databases

  • Where not possible, the BI Server will functionally compensate by calculation,joining data sources in memory

Parse Incoming Logical SQL

Generate Optimized Physical SQL(s)

Receiving Incoming Resultsets

Apply any required functions and joins

Return data to user

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


How does the bi server process queries
How Does The BI Server Process Queries? Analytics

  • So what happens when the BI Server processes an incoming query?

  • What are the steps involved in translating logical requests to physical queries?

  • What does the Navigator do?

  • What rules and statistics does it use?

  • How can we influence the physical queries that it generates?

  • How can we tell what is going on?

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 1 request arrives at intelligent request generator
Step 1 : Request Arrives at Intelligent Request Generator Analytics

  • Query arrives from Answers or other ODBC tool, against Semantic Model

  • Passed to Intelligent Request Generation function

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 2 request passed to logical request generation engine
Step 2 : Request Passed to Logical Request Generation Engine Analytics

  • Request passed to Logical Request Generation engine, responsible for generating the one or more logical SQL required to satisfy the query

  • In this case, request requires multiple columns and a derivation (calculation)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 3 check cache if enabled
Step 3 : Check Cache if Enabled Analytics

  • If caching is enabled, Cache Services is checked for the result

  • Avoids calls to physical databases for frequently-requested data

  • Intelligent checking of cache including derivation, projection, nested formulas etc

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Rules for a cache hit
Rules for a Cache Hit Analytics

  • Caching is enabled (CACHE=Y in the NQSConfig.INI file);

  • WHERE clause is semantically the sameor a logical subset of a cached statement;

  • All of the columns have either to be presentor derivable from cached copies

  • It has equivalent join conditions

  • If DISTINCT is used, the cached copy has to use this attribute as well

  • Aggregation levels have to be compatible, being either the same or more aggregated than the cached query

  • No further aggregation (for example, RANK) can be used in the incoming query

  • Compatible ORDER BY clause

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 4 request passed to the navigator
Step 4 : Request Passed to the Navigator Analytics

  • Navigator is responsible for navigating the semantic model and generating the execution plan, and most optimal physical SQLs, for the request

  • Contains multiple functions that are used dependent on the nature of the request

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 5 multi pass sub request logic
Step 5 : Multi-Pass / Sub-Request Logic Analytics

  • Analyzes request and works out the complexity of the query

  • Determines whether multi-pass logic is required (time-series etc)

  • Works out whether one request depends on the results of another (sub-request)

  • Navigator uses this function to determine the most efficient way to satisfy the request

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 6 fragment optimization
Step 6 : Fragment Optimization Analytics

  • When a measure is fragmented, the Fragment Optimization function will transform base-level logical SQL into logical SQL fragments

  • Also automatically resolves fan-trap issues

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 7 aggregate navigation
Step 7 : Aggregate Navigation Analytics

  • Aggregate Navigation checks aggregates mapped in RPD for relevance

  • Execution Plan will be written to use aggregates if they return less rows thanthe default logical table sources

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 8 optimized query rewrite and pass to execution engine
Step 8 : Optimized Query Rewrite, and pass to Execution Engine

  • Optimized Query Rewrite checks database capabilities for function shippingand ability to join subqueries

  • Generates optimal physical SQL or MDX for relevant data sources

  • Passed to ExecutionEngine for transferto physical databases

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Step 9 copy back to cache services if enabled
Step 9 : Copy Back to Cache Services, if Enabled Engine

  • Results are returned to calling application (Answers etc)

  • If caching is enabled, results are copied back to Cache Services

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Key navigator functionality
Key Navigator Functionality Engine

  • BI Server will pick one or more LTSs at run-time based on query context

  • Generates an execution plan

  • Will try and generate as few physical SQL as possible

  • Will try to push down functions if possible

  • Will try to push down joins if possible

  • Will write the optimal SQL or MDX for the data source, based on DBFunctions.INI matrix

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : [email protected] W: www.rittmanmead.com


Level 5 query logging and logical execution plans
Level 5 Query Logging and Logical Execution Plans Engine

  • The execution plan generated by the Navigator can be viewed when Level 5 logging is enabled

  • Shows the execution plan, step-by-step row counts, and the physical SQL / MDX generated for the request

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server joins
BI Server Joins Engine

  • (Almost) Every request requires joins

  • For simple queries, you don’t notice this because they are pushed down to the DB

  • For more complex queries, the BI Server may need to get involved

    • Where more than one physical database provides data for the request

    • Where multi-pass logic is used

    • Where measures are fragmented

    • Where more than one fact table is used in the request

  • Basic principle is that the BI Server tries to push down all joins

  • Depending on the database type and version and nature of request,the BI Server may need to perform the joins instead

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


The bi server source to target metadata model
The BI Server Source-to-Target Metadata Model Engine

  • Facts and dimensions are mapped to physical sources Logical Table Sources

  • LTS are chosen at run time based on RPD definitions (level mapping, fragmentation, federation etc)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


So what does the bi server do when it joins data in memory
So What Does The BI Server Do, When It Joins Data In Memory? Engine

  • So just what goes on when the BI Server joins data in memory?

  • How can we tell when the BI Server has performed the join?

  • Why does the BI Server sometimes do the join, and sometimes get the database to do it instead?

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server join type 1 fact dimension from single database
BI Server Join Type 1 : Fact + Dimension from Single Database

  • Single logical fact and dimension table, joined by a logical join

  • Both logical tables have single LTS, which both point to tables in the same physical database

  • Physical tables are joined using a physical foreign key

Single Database

LTS Mapping

ProductsLogical Dimension

ProductsPhysicalTable

SalesLogical Fact

SalesPhysicalTable

LTS Mapping

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Single source fact and dimension execution plan sql
Single Source Fact and Dimension Execution Plan & SQL Database

  • BI Server generates execution plan with single logical SQL including join clause

  • Single physical SQL with join pushed down to database

RqList <<2105>> [for database 3023:2820:orcl3,44]

PRODUCTS.PROD_SUBCATEGORY_DESC as c1 GB [for database 3023:2820,44],

sum(SALES.QUANTITY_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c2 GB [for database 3023:2820,44]

Child Nodes (RqJoinSpec): <<2136>> [for database 3023:2820:orcl3,44]

PRODUCTS T2874

SALES T2911

DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]

GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,44]

OrderBy: c1 asc [for database 3023:2820,44]

select T2874.PROD_SUBCATEGORY as c1, sum(T2911.QUANTITY_SOLD) as c2from PRODUCTS T2874, SALES T2911where ( T2874.PROD_ID = T2911.PROD_ID ) group by T2874.PROD_SUBCATEGORYorder by c1

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server join type 2 fact dimension from two databases
BI Server Join Type 2 : Fact + Dimension from Two Databases Database

  • Single logical fact and dimension table, joined by a logical join

  • Both logical tables have single LTS, which point to different databases

  • Physical tables are joined using a physical foreign key

Database #1

ProductsPhysicalTable

Database #2

LTS Mapping

ProductsLogical Dimension

SalesLogical Fact

SalesPhysicalTable

LTS Mapping

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Multiple source fact and dimension execution plan
Multiple Source Fact and Dimension Execution Plan Database

  • Excerpt of plan (source rqList cropped)

  • Two logical SQL blocks with InnerJoin between fact and dimension tables

    • Can also be LeftOuterJoin, RightOuterJoin and FullOuterJoin

(

RqList <<2495>> [for database 3023:132:orcl,44]

SALES.PROD_ID as c2 [for database 3023:132,44],

sum(SALES.QUANTITY_SOLD by [ SALES.PROD_ID] ) as c3 [for database 3023:132,44]

Child Nodes (RqJoinSpec): <<2504>> [for database 3023:132:orcl,44]

SALES T211

GroupBy: [ SALES.PROD_ID] [for database 3023:132,44]

OrderBy: c2 asc [for database 3023:132,44]

) as D901

InnerJoin <<2492>> On D901.c2 = D902.c2; actual join vectors: [ 0 ] = [ 1 ]

(

RqList <<2517>> [for database 3023:2500:orcl2,44]

PRODUCTS.PROD_SUBCATEGORY_DESC as c1 GB [for database 3023:2500,44],

PRODUCTS.PROD_ID as c2 [for database 3023:2500,44]

Child Nodes (RqJoinSpec): <<2523>> [for database 3023:2500:orcl2,44]

PRODUCTS T2502

OrderBy: c2 asc [for database 3023:2500,44]) as D902

OrderBy: c2, c3 [for database 0:0,0] ) as D1

OrderBy: c1 asc [for database 0:0,0]

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Multiple source fact and dimension physical sql
Multiple Source Fact and Dimension Physical SQL Database

  • Two physical SQL queries sent to the two databases

  • BI Server joins the two sources in-memory using a sort-merge join algorithm

-------------------- Sending query to database named orcl (id: <<6057>>):select T211.PROD_ID as c2, sum(T211.QUANTITY_SOLD) as c3from SALES T211group by T211.PROD_IDorder by c2+++Administrator:2b0000:2b000b:----2010/04/17 00:04:03-------------------- Sending query to database named orcl2 (id: <<6074>>):select T2502.PROD_SUBCATEGORY as c1, T2502.PROD_ID as c2from PRODUCTS T2502order by c2

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server join type 3 fact dimension using driving table
BI Server Join Type 3 : Fact + Dimension Using Driving Table Database

  • Single logical fact and dimension table, joined by a logical join

  • Two tables typically LTS mapped to different physical databases

  • One of the logical tables defined as a driving table in the BMM logical join

  • Driving table uses Parameterized NestedLoop Join (PNLJ) to “probe” other table

Database #1

ProductsLogical Dimension

ProductsPhysicalTable

Database #2

LTS Mapping

SalesLogical Fact

SalesPhysicalTable

PNLJProbeLookup

LTS Mapping

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Driving table considerations
Driving Table Considerations Database

  • By default, federated queries will cause both tables to be loaded into the BI Server memory, and then joined (as per previous example)

  • Can be expensive if one or more of the tables are large

  • An alternative is to designate one of the tables as the “driving table”

    • Needs to be small, typically <1000 rows

  • This table is then queried and filtered first, then it’s remaining rows are then applied as a filter to the second table

  • Driving table is defined in the Logical Join dialog

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Parameterized nested loop join algorithm physical sql
Parameterized Nested Loop Join Algorithm & Physical SQL Database

  • Start reading rows from the driving table

  • Submit a parameterized query request to non-driving table

  • Bind values from the drive-table rows into the parameterized query and fetch results

  • Repeat until all rows from drive table are processed

RqList PRODUCTS.PROD_SUBCATEGORY as c1 GB, QUANTITY_SOLD:[DAggr(SALES.QUANTITY_SOLD by [ PRODUCTS.PROD_SUBCATEGORY] )] as c2 GBOrderBy: c1 asc

select T211.PROD_ID as c2, sum(T211.QUANTITY_SOLD) as c3from SALES T211where ( T211.PROD_ID in (:PARAM1, :PARAM2, :PARAM3, :PARAM4, :PARAM5, :PARAM6, :PARAM7, :PARAM8, :PARAM9, :PARAM10, :PARAM11, :PARAM12, :PARAM13, :PARAM14, :PARAM15, :PARAM16, :PARAM17, :PARAM18, :PARAM19, :PARAM20) ) group by T211.PROD_ID order by c2

select T2502.PROD_SUBCATEGORY as c1, T2502.PROD_ID as c2from PRODUCTS T2502order by c2

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Fact and dimension driving table execution plan
Fact and Dimension Driving Table Execution Plan Database

  • Excerpt of plan

  • Note InnerJoin (left drive) and parameterized DetailFilter in plan

( RqList <<13655>> [for database 3023:2500:orcl2,44] PRODUCTS.PROD_SUBCATEGORY as c1 GB [for database 3023:2500,44], PRODUCTS.PROD_ID as c2 [for database 3023:2500,44] Child Nodes (RqJoinSpec): <<13658>> [for database 3023:2500:orcl2,44] PRODUCTS T2502 OrderBy: c2 asc [for database 3023:2500,44] ) as D901 InnerJoin (left drive) <<13689>> On D902.c2 = D901.c2; actual join vectors: [ 1 ]=[ 0 ] ( RqList <<13673>> [for database 3023:132:orcl,46] SALES.PROD_ID as c2 [for database 3023:132,46], sum(SALES.QUANTITY_SOLD by [ SALES.PROD_ID] ) as c3 [for database 3023:132,46] Child Nodes (RqJoinSpec): <<13676>> [for database 3023:132:orcl,46] SALES T211 DetailFilter: SALES.PROD_ID = ?1 or SALES.PROD_ID = ?2 or SALES.PROD_ID = ?3 or SALES.PROD_ID = ?4 or SALES.PROD_ID = ?5 or SALES.PROD_ID = ?6 or SALES.PROD_ID = ?7 or SALES.PROD_ID = ?8 or SALES.PROD_ID = ?9 or SALES.PROD_ID = ?10 or SALES.PROD_ID = ?11 or SALES.PROD_ID = ?12 or SALES.PROD_ID = ?13 or SALES.PROD_ID = ?14 or SALES.PROD_ID = ?15 or SALES.PROD_ID = ?16 or SALES.PROD_ID = ?17 or SALES.PROD_ID = ?18 or SALES.PROD_ID = ?19 or SALES.PROD_ID = ?20 [for database 0:0] GroupBy: [ SALES.PROD_ID] [for database 3023:132,46] OrderBy: c2 asc [for database 3023:132,46] ) as D902

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server join type 4 two facts using a single database
BI Server Join Type 4 : Two Facts Using a Single Database Database

  • Query requires data from two fact tables, sharing a common dimension

  • BI Server creates two logical queries and “stitches” results together, to avoid “fan trap”

  • Join is pushed down to the database if possible

Single Database

CostsPhysicalTable

CostsLogical Fact

LTS Mapping

ProductsPhysicalTable

ProductsLogical Dimension

LTS Mapping

SalesPhysicalTable

SalesLogical Fact

LTS Mapping

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Facts with conformed dimensions execution plan
Facts with Conformed Dimensions Execution Plan Database

  • Two result sets are generated, with a FullOuterStitchJoin between them

  • Capabilities of physical database determine whether join is pushed down to database

(

RqList <<3474>> [for database 3023:2820:orcl3,46]

sum(COSTS.UNIT_COST by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,46],

PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,46]

Child Nodes (RqJoinSpec): <<3507>> [for database 3023:2820:orcl3,46]

PRODUCTS T2874

COSTS T2830

DetailFilter: COSTS.PROD_ID = PRODUCTS.PROD_ID [for database 0:0]

GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,46]

) as D1 FullOuterStitchJoin <<3565>> On D1.c2 = D2.c2

(

RqList <<3511>> [for database 3023:2820:orcl3,46]

sum(SALES.AMOUNT_SOLD by [ PRODUCTS.PROD_SUBCATEGORY_DESC] ) as c1 GB [for database 3023:2820,46],

PRODUCTS.PROD_SUBCATEGORY_DESC as c2 GB [for database 3023:2820,46]

Child Nodes (RqJoinSpec): <<3544>> [for database 3023:2820:orcl3,46]

PRODUCTS T2874

SALES T2911

DetailFilter: PRODUCTS.PROD_ID = SALES.PROD_ID [for database 0:0]

GroupBy: [ PRODUCTS.PROD_SUBCATEGORY_DESC] [for database 3023:2820,46]

) as D2

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Facts with conformed dimensions single physical sql
Facts with Conformed Dimensions Single Physical SQL Database

  • Where database supports subquery factoring (Oracle 10gR2+) a single physical SQL will be generated with a full outer join between all shared dimension columns

WITH

SAWITH0 AS (select sum(T2830.UNIT_COST) as c1,

T2874.PROD_SUBCATEGORY_DESC as c2

from

PRODUCTS T2874,

COSTS T2830

where ( T2830.PROD_ID = T2874.PROD_ID )

group by T2874.PROD_SUBCATEGORY_DESC),

SAWITH1 AS (select sum(T2911.AMOUNT_SOLD) as c1,

T2874.PROD_SUBCATEGORY_DESC as c2

from

PRODUCTS T2874,

SALES T2911

where ( T2874.PROD_ID = T2911.PROD_ID )

group by T2874.PROD_SUBCATEGORY_DESC)

select distinct SAWITH0.c1 as c1,

SAWITH1.c1 as c2,

case when SAWITH0.c2 is not null then SAWITH0.c2 when SAWITH1.c2 is not null then SAWITH1.c2 end as c3

from

SAWITH0 full outer join SAWITH1 On SAWITH0.c2 = SAWITH1.c2

order by c3

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Facts with conformed dimensions multiple physical sql
Facts with Conformed Dimensions Multiple Physical SQL Database

  • Otherwise, multiple physical SQL queries will be generated

  • BI Server will perform in-memory sort-merge stitch full outer join on shared dim columns

-------------------- Sending query to database named orcl3 (id: <<3018>>):

select sum(T2830.UNIT_COST) as c1,

T2874.PROD_SUBCATEGORY_DESC as c2

from

PRODUCTS T2874,

COSTS T2830

where ( T2830.PROD_ID = T2874.PROD_ID )

group by T2874.PROD_SUBCATEGORY_DESC order by c2

+++Administrator:2a0000:2a0005:----2010/02/28 15:05:31

-------------------- Sending query to database named orcl3 (id: <<3055>>):

select sum(T2911.AMOUNT_SOLD) as c1,

T2874.PROD_SUBCATEGORY_DESC as c2

from

PRODUCTS T2874,

SALES T2911

where ( T2874.PROD_ID = T2911.PROD_ID )

group by T2874.PROD_SUBCATEGORY_DESC order by c2

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Stitch joins and federated queries
Stitch Joins and Federated Queries Database

  • Stitch joins are used whenever multiple fact tables are used in queries

  • Also used when federated fact table sources are used, and aggregate sources

  • Basic principle is to join facts using FullOuterStitchJoinacross all dimension attribute columns used in the query

...

) as D901 FullOuterStitchJoin <<7800>> On D901.ProductCategory =NullsEqual D902.ProductCategory and D901.ProductCategory =NullsEqual D902.ProductCategory; actual join vectors: [ 0 1 ] = [ 0 1 ]

(

...

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Rules for bi server join strategy
Rules for BI Server Join Strategy Database

  • Try to generate as few physical SQL / MDX queries as possible

  • Push as many joins (and functions) down to the database as possible

  • Where required, join fact and dimensions in memory using inner and outer sort-merge joins

  • If driving table is specified, iterate through join using nested loop algorithm

  • Generate one logical SQL query per fact table access, push down stitch join to database using full outer join if possible, if not have the BI Server do it

  • Logical execution plan and resulting physical SQLs visible in the Level 5 log file

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server join variations 1 fragmentation partitioned measures
BI Server Join Variations 1 : Fragmentation (Partitioned Measures)

  • RPD has a measure mapped through two or more LTS to different physical tables

  • One physical source for historic data, one for real-time

  • One for Southern Region, one for Northern Region

  • Known as fragmentation

  • BI Server has to UNION the resultsets together

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Fragmentation partitioned measures logical execution plan
Fragmentation (Partitioned Measures) Logical Execution Plan Measures)

  • Execution Plan will contain two logical SQL blocks (excerpt shown below)

  • RqUnion All union’s the two resultsets, BI Server will attempt to push down to databaseif possible (if not, two physical SQLs will need to be retrieved and UNION done in memory)

(

RqList <<7613>> [for database 3023:6594:orcl7,44]

PRODUCTS.PROD_SUBCATEGORY_DESC as c2 [for database 3023:6594,44],

SALES_UPTO_2003.AMOUNT_SOLD as c3 [for database 3023:6594,44]

Child Nodes (RqJoinSpec): <<7617>> [for database 3023:6594:orcl7,44]

PRODUCTS T6596

SALES T6629

DetailFilter: PRODUCTS.PROD_ID = SALES_UPTO_2003.PROD_ID [for database 0:0]

RqUnion All <<7690>> [for database 3023:6594:orcl7,44]

RqList <<7668>> [for database 3023:6594:orcl7,44]

PRODUCTS.PROD_SUBCATEGORY_DESC as c2 [for database 3023:6594,44],

SALES_BEYOND_2003.AMOUNT_SOLD as c3 [for database 3023:6594,44]

Child Nodes (RqJoinSpec): <<7672>> [for database 3023:6594:orcl7,44]

PRODUCTS T6596

SALES T6637

DetailFilter: PRODUCTS.PROD_ID = SALES_BEYOND_2003.PROD_ID [for database 0:0]

) as D3

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Bi server join variations 2 time series queries
BI Server Join Variations 2 : Time-Series Queries Measures)

  • Generated when time-series functions are used in a request

  • Requires time dimension to be defined in RPD

  • Currently limited to AGO and TODATE functions

  • Generates two or more logical SQL queries which need to be joined together to return time-series results

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Time series logical execution plan
Time Series Logical Execution Plan Measures)

  • Execution plan will show multiple nested logical SQL blocks

  • More complex set of nested blocks will be doing the time offset

  • Time offset block will need to be FullOuterStitchJoined to the present-day resultset

  • Excerpt shown below

  • Depending on DB capabilities, will either be shipped as one SQL (using subqueries)or as two or more SQLs (with results full outer stitch joined in memory)

...DetailFilter: ITEMS.ORDID = ORDERS.ORDID and ORDERS.ORDERDATE = D4.c2 and D3.c1 = D4.c1 and D3.c2 = 'May-2007' [for database 0:0] GroupBy: [ D3.c2, D3.c3] [for database 3023:4210,44] ) as D1 OrderBy: c1 asc [for database 3023:4210,44] ) as D901 FullOuterStitchJoin <<4892>> On D901.c1 =NullsEqual D902.c1; actual join vectors: [ 0 ] = [ 0 ] ( RqList <<4963>> [for database 3023:4210:orcl4,44] D2.c2 as c1 [for database 3023:4210,44], D2.c1 as c2 GB [for database 3023:4210,44], D2.c3 as c3 [for database 3023:4210,44] ...

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Memory and disk usage during bi server joins
Memory and Disk Usage During BI Server Joins Measures)

  • So what happens when the BI Server performs an in-memory join?

  • The BI Server requires memory to join the data sets using a sort-merge algorithm

  • Initial memory usage is determed by size/complexity of the RPD

  • Memory usage then rises with # of concurrent sessions, in-memory joins, aggregations

  • Typically paged to file at {ORACLEBIDATA}/tmp

  • Because of paging, use of BI Server functional compensation is largely I/O intensive rather than memory intensive

  • Extent of paging driven by VIRTUAL_TABLE_PAGE_SIZE parameter

    • May benefit from increase on Unix systems

    • Docs do not imply much benefit though

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


So what influences execution plans and physical query ies
So What Influences Execution Plans and Physical Query(ies)? Measures)

  • Logical Execution Plan largely determined by the LTS mappings in the RPD

  • BI Server Navigator generates execution plan based on context of the requestand the LTS configuration in the BMM layer

  • All can be determined by looking at the RPD

    • No equivalent to the Oracle database CBO

  • Physical SQL then determined byexecution plan and DBFeatures.INI

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Are there the equivalent of object statistics in the rpd
Are There The Equivalent of Object Statistics in the RPD? Measures)

  • There are some limited statistics in the RPD that influence the query plan

    • Row counts on physical tables are ignored

  • Number of Elements at this Level value can be used by the Navigator to determine which of several aggregates to use for a request

  • Needs to be manually populated (rough figure is OK)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Dimension level element counts in use
Dimension Level Element Counts In Use Measures)

  • Consider the following dimension

    • Time Dim -> Year (2), Month (24), Days (730)

    • Products Dim-> Type (10), Sub-Type (20), SKUs (1000)

    • Customers Dim-> Regions(20), Districts(500), Customers (100000)

  • Sales Fact joins to these dimensions at the lowest level.

  • Also assume I have a couple of aggregate tables :

    • Sales Fact Agg1 -> (Time Dim)Year, (Customer Dim) Districts

    • Sales Fact Agg2 -> (Time Dim)Year, (Product Dim) Sub-Type

  • Consider this Query Example :

  • Select Year, Sum(Revenue) from Sales ;

  • This can be satisfied by either Agg1 or Agg2, but BI Server has no way of knowing which is the best option unless the number of elements are entered.

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


What does the logical execution plan tell us
What Does the Logical Execution Plan Tell Us? Measures)

  • It tells us how the BI Server Navigator turned the logical request into physical SQL and MDX queries

  • But there’s nothing that you couldn’t infer from the RPD

  • There are no parameters etc that you can use to alter join strategies etc

    • This is driven by the repository design only

  • Extra information provided by Level 7 logging is interesting

    • Times logged by each entry/step

    • Tells us how long the BI Server takes to do each join

  • Can be used to fine-tune the VIRTUAL_TABLE_POOL_SIZE parameter

?

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


So what does this all tell us
So What Does This All Tell Us? Measures)

  • Co-locate data as much as possible, minimize in-memory joins

  • Make sure the database version and capabilities is set correctly for each source

  • Maximize function push-down, mimimize functional compensation

  • Understand what happens with BI Server joins, and how to read the execution plan

  • Understand what a FullOuterStitchJoin is, and how to avoid unneccesary ones

  • Understand the role of LTSs and when to add or remove them

  • There is some diagnostic data available for the BI Server, but most of whatyou need is contained in the RPD design

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Thank you for listening
Thank You for Listening Measures)

  • More details on this topic, and on OBIEE in general, can be found on our blog

    • http://www.rittmanmead.com/2010/02/25/inside-the-oracle-bi-server-part-1-the-bi-server-architecture/

    • http://www.rittmanmead.com/2010/03/01/inside-the-oracle-bi-server-part-2-how-is-a-query-processed/

    • http://www.rittmanmead.com/2010/03/03/inside-the-oracle-bi-server-part-3-bi-server-in-memory-joins/

  • Thank you for your time

  • Any questions :

    • Web : http://www.rittmanmead.com

    • Email : mark.rittman@rittmanmead.com

    • Twitter : @markrittman

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Inside the Oracle BI Server (10g) Measures)

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 3 : Inside the Oracle BI Server

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g Measures)

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 4 : Building Reports and Dashboards

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Introducing oracle bi answers
Introducing Oracle BI Answers Measures)

  • The Ad-hoc report editor for Oracle BI Suite Enterprise Edition

  • Modify existing reports, create new reports

  • Add graphs, formatting, titles and other features, then publish to dashboard

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Starting oracle answers
Starting Oracle Answers Measures)

  • New reports are created using Oracle Answers

  • Click on the Answers link at the top right-hand side of the dashboard

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Selecting subject areas and existing reports
Selecting Subject Areas and Existing Reports Measures)

  • Answers initially presents you with a list of subject areas

  • Existing reports, in your own, and shared folders

  • Click on a subject area to start creating a report

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


The oracle bi answers interface
The Oracle BI Answers Interface Measures)

Filters AppliedTo Results

Data Items in Your Report(“Criteria”)

Other DataYou Can Add

Save, Openand New Buttons

Tabs to ViewResults, Prompts

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


The report criteria
The Report Criteria Measures)

  • Lists out the data items (“columns”) that make up your report

  • Buttons for filtering, formatting, renaming columns

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Adding data items to a report
Adding Data Items to a Report Measures)

  • The left-hand pane in Answers lists the tables and columnsyou can add to your report

  • Click on the tab name to display the available columns

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Showing report results
Showing Report Results Measures)

  • Click on the “Results” tab to show the report results

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Viewing the results
Viewing the Results Measures)

  • Initial view of the results is a table

  • Simple listing of items

  • Notice the Title bar above the table - you will now configure it

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Using the view selector
Using the View Selector Measures)

  • Reports in Oracle Answers can have multiple Views associated with them

  • Default views are Table, Title and Compound

    • Table view is a simple listing

    • Title view provides a title, subtitle and icon

    • Compound view combines multiple views

  • Compound view is normally the viewdisplayed in the dashboard

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Adding a graph
Adding a Graph Measures)

  • From within the Results tab view, select “Chart” from the view selector

  • Chart view is then displayed, with Bar Chart as the default graph

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Graph types
Graph Types Measures)

  • Area

  • Horizontal Bar

  • Bubble

  • Vertical Bar

  • Line

  • Line-Bar Combo

  • Pareto

  • Pie

  • Radar

  • Scatter

  • Step

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Adding gauges
Adding Gauges Measures)

  • Gauges are another view option

  • Select using View Selector, choose Gauge type using drop-down menus

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Adding images into reports
Adding Images into Reports Measures)

  • Reports can be more informative when using pictures to illustrate values

    • Gauges to show KPIs

    • Arrows to show trends

    • Warning signs to show issues

    • Stars to show popularity

  • Answers can conditionally format any valueusing a set of images

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Adding navigation to views
Adding Navigation to Views Measures)

  • By default, drilling on a column or heading displays data in more detail

    • Drill down from country > city > town > customer

  • Views can be instructed to instead navigate to a different report or URL

    • Column value clicked on to the target request

    • Receiving request needs to set filter to “Is prompted”

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Demonstration Measures)

Building Reports with Oracle BI Answers

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Introducing oracle bi dashboard
Introducing Oracle BI Dashboard Measures)

  • One-stop shop for all your users’ reports

  • Fully interactive, single UI for report consumers

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Dashboard key features
Dashboard Key Features Measures)

Log In / Log Off

Tabs

Reports

Narratives

Graphs

Selectors

Print & Save

Modify

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


The dashboard editor
The Dashboard Editor Measures)

Objects you can

add to dashboard

Control forViewing and AddingTabs (pages)

Button forEditing DashboardProperties

Buttons for EditingReport and Section Properties

Reports you

add to dashboard

DashboardSections

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Adding requests to the dashboard
Adding Requests to the Dashboard Measures)

  • Drag and Drop from the list of available reports

  • Add Sections from the Dashboard Objects palette as required

  • Select view of data

  • Add links for printing, downloading etc

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com


Demonstration Measures)

Creating Dashboards

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


Deep Dive into Oracle BI EE 10g Measures)

  • Collaborate’10 IOUG Forum, Las Vegas, April 2010

  • Lession 4 : Building Reports and Dashboards

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com


ad