obiee 11g - performance tuning real success stories
DESCRIPTION
OBIEE 11G performance TuningTRANSCRIPT
-
Oracle Business Intelligence 11g
Performance Tuning Real Success Stories
Antony Heljula April 2013
-
Peak Indicators Limited 2
About this Presentation Performance Tuning
Previous presentations have discussed database performance tuning at length
The general conclusion was that a lot of time can be spent on quick win tuning where you only achieve performance gains of 5-20%
Typically, it is only when you implement some form of aggregation do you get significant performance gains in excess of 20x faster (e.g. minutes down to seconds)
-
Peak Indicators Limited 3
About this Presentation Aggregation using Oracle BI
This presentation will show how to achieve significant performance gains by using the aggregation capabilities of the Oracle BI Server
Demonstrating the power of the Aggregate Persistence Wizard
2 customer examples will be used
The methods and techniques discussed can apply to all customers, from small implementations to those involving Exalytics or Exadata
-
Peak Indicators Limited 4
Agenda Performance Tuning Real Success Stories
Customer Overview Proposed Solution Dashboard Analysis Tuning Strategy Aggregate Persistence Incremental Loading Summary
-
Peak Indicators Limited 5
Performance Tuning Real Success Stories Customer Overview
-
Peak Indicators Limited 6
The world's leading provider of global satellite communication services
-
Peak Indicators Limited 7
The UKs Biggest Outdoor Stores
-
Peak Indicators Limited 8
Customer Overview Oracle BI Specifics (At the beginning)
Oracle BI Version 10.1.3.4 11.1.1.5
No. of Users 100 50
Database Oracle Database EE 10G Oracle Database EE 10G
Data Volumes 450GB (Compressed) 230GB
Platform Windows (OBI) / Solaris (DB) Linux
Dashboards/Reports (approx) 20 / 320 10 / 100
Subject Areas (example) Network, Voice/Data Traffic, Registrations, Faults Sales, Stock, POs, Budgeting,
Deliveries, Customers, Supplier
-
Peak Indicators Limited 9
Customer Overview Observations/Issues
Reports taking up to 27 minutes
Reports were mainly of an operational nature, hard to optimise
Many different Time Series calculations used in some reports, going back 2 years
No aggregates deployed yet
Some reports taking up to 2 minutes
Still using OBI 10g, due to resource constraints and other projects taking precedence
Further aggregates on the data warehouse were not created due to project time scales and scope
Data Warehouse on an Oracle DB 10g platform, hardware/software not latest generation
-
Peak Indicators Limited 10
Customer Overview Dashboard Response Times (Avg)
60 sec average
15 min average
Target
Target
-
Peak Indicators Limited 11
Performance Tuning Real Success Stories Proposed Solution
-
Peak Indicators Limited 12
Proposed Solution Aggregation using Oracle BI
A solution based on Aggregate Persistence was proposed to both customers: Agile creation of aggregates through the BI Server Rapid build and deployment Elimination of disk/network bottlenecks No dependencies on DW/ETL team to build and
maintain aggregates Flexible approach for maintaining aggregates Maximising scalability and performance Reduced load on the underlying data warehouse
For Inmarsat, the solution would be prototyped in Oracle BI
11g. RPD and dashboards had to be upgraded as part of the exercise
-
Peak Indicators Limited 13
Proposed Solution Aggregate Persistence
With minimal effort, OBI will automatically build and populate a series of aggregate tables on a chosen data source
OBI will then automatically redirect any summary reports to use these aggregates
New aggregates built without downtime
OBI is 100% available whilst aggregates are being built
-
Peak Indicators Limited 14
Proposed Solution Aggregate Persistence - Exalytics
In the case of Exalytics, the aggregate tables are placed in the TimesTen in-memory database
TimesTen is located on the Exalytics server
With data placed in-memory on the BI Server the result is complete elimination of disk and network bottlenecks
Exalytics-only features include: Columnar Compression Exalytics Summary Advisor
-
Peak Indicators Limited 15
Further Notes In-Memory Performance
TB RAM, 32 cores Databases for
medium businesses / departmental
1 TB RAM, 40 cores, 2.4TB Flash Middleware focussed
OBI / TimesTen / Essbase / Endeca
4 TB RAM, 22TB Flash, 160 cores, 14 Exadata Storage Cells
Extreme performance for all types of OLTP/DW database workload.
Database Consolidation.
Half/Quarter/Eighth rack options available
-
Peak Indicators Limited 16
Performance Tuning Real Success Stories Dashboard Analysis
-
Peak Indicators Limited 17
Dashboard Analysis Overview
A selection of dashboard pages were selected at each customer
The aim was to build a set of common aggregates that could each be used across multiple dashboard pages. A period of analysis was therefore required to identify the facts/dimensions used across all the dashboards
The Exalytics Summary Advisor would have helped to automate/simplify this process
Without the Summary Advisor, the process of defining the aggregates had to be done manually
Intensive process Approximately 2-3 days of analysis required for 30 reports
-
Peak Indicators Limited 18
Dashboard Analysis Complications
In order to obtain the facts/dimensions you have to consider several areas of metadata, including: Analysis columns Analysis filters Analysis formulas (e.g CASE or FILTER expressions) Is Prompted Filters Dashboard Prompts Column Selectors
-
Peak Indicators Limited 19
Dashboard Analysis Mock-up of Inmarsat Dashboard
Dashboards typically analytical in nature, showing summaries for network/traffic performance over time
Dynamically choose to summarise data by Hour/Day/Month Column selectors allow additional dimensions/facts to be used on reports
-
Peak Indicators Limited 20
Dashboard Analysis Go Outdoors
Dashboards typically operational in nature, providing a snapshot of the current position
Many metrics covering Sales, Stock, Payments over last 2 years >10 different Times Series metrics on each report 10s of Millions of records being scanned
-
Peak Indicators Limited 22
Dashboard Analysis Example Fact/Dimension Matrix
Analyses
Logical Fact Tables
Dimensions
Dimension Hierarchy
Levels
-
Peak Indicators Limited 23
Dashboard Analysis Conclusion
9 aggregates identified that would provide good levels of summarisation and cater for all reports
Summarising to Hourly, Day and Month levels
About 18 dimensions in total used across 9 aggregates
Aggregates were similar in dimensionality to the existing aggregates on the DW, but with some
very granular dimensions removed e.g. Subscriber (IMSI)
No suitable aggregates identified
All reports tended to be at the lowest levels of the 3 biggest dimensions:
Day, Product, Store
The new aggregate tables would not provide enough summary to significantly improve reports
e.g. 36M rows down to 30M rows
A further round of analysis was required summarising existing data would not provide
sufficient benefit
-
Peak Indicators Limited 24
Performance Tuning Real Success Stories Tuning for Operational Reports
-
Peak Indicators Limited 26
Tuning for Operational Reports Dimension Elimination
If you cannot summarise data to a higher level of granularity, then try to see if dimension elimination can help:
Day
Product Store
If we could eliminate the Time dimension then data volumes could, in
theory, be reduced by 365x per year
-
Peak Indicators Limited 27
Tuning for Operational Reports
Dimension Elimination Take this report as an example, the columns on the report suggest that we
should only need to aggregate sales data across 2 dimensions: Product Dimension (at Company Division level) Stores Dimension (at Store level)
Company Division Store
-
Peak Indicators Limited 28
Tuning for Operational Reports
Dimension Elimination But there are two places which force additional dimension levels
into the query:
This filter is applied across all reports, the Stock Type attribute is at the lowest
level of the Product dimension
The column formula uses a FILTER() expression based on a dimension
attribute from the Time dimension
-
Peak Indicators Limited 30
Tuning for Operational Reports Dimension Elimination
We can achieve Dimension Elimination by pushing these calculations and filters down into the RPD
A new set of metrics were created that combined the common Formula/Filter logic used across the reports:
-
Peak Indicators Limited 31
Tuning for Operational Reports Dimension Elimination
The report was now simplified and only involved two dimensions: 1. Column formulas replaced with RPD metrics 2. Redundant filters removed
-
Peak Indicators Limited 32
Tuning for Operational Reports Dimension Elimination
End result: Time dimension now eliminated completely from report Data summarised up to Company Division level of the Product dimension
Aggregate Persistence would be configured to work of these new dedicated metrics
A 12x reduction in aggregate data volumes was achieved: 36M records down to 3M records
Company Division Store
-
Peak Indicators Limited 33
Performance Tuning Real Success Stories Aggregate Persistence
-
Peak Indicators Limited 34
Aggregate Persistence Building the Aggregates
Now that the aggregates had been defined and the custom calculations created, the Aggregate Persistence Wizard could be invoked to create the aggregate scripts
NOTE: On an Exalytics machine, the Summary Advisor does this automatically for you
-
Peak Indicators Limited 35
Aggregate Persistence Before your begin
The Create Aggregates scripts perform a number of tasks: 1. Create dimension/fact tables 2. Create unique indexes (based on logical keys) 3. Perform full load of data 4. Analyse tables 5. Model new aggregates into the RPD
Before running them, it is important to verify: Your RPD is consistent, not locked Your logical keys in the RPD are properly unique Your dimension hierarchies are configured with all the required columns The necessary create privileges are granted on the database schema
-
Peak Indicators Limited 36
Before running the Aggregate Persistence scripts, run the Model Checker to identify any issues with your dimensional models e.g. non-unique keys
File > Check Models > Complete
Aggregate Persistence
BI Admin Tool Model Checker
-
Peak Indicators Limited 37
Aggregate Persistence Model Checker: Reconfigure Dimension Hierarchies
The Model Checker found dimension hierarchy levels configured in the RPD that were not strictly unique
This would have caused the Aggregate Persistence to fail when creating unique keys on these columns
There are two PLAN_NAME values which are the same. It is not strictly a
Logical Level Key
-
Peak Indicators Limited 38
Aggregate Persistence
Model Checker: Reconfigure Dimension Hierarchies
The fix was simply to move the offending column to a higher level in the dimension hierarchy:
-
Peak Indicators Limited 39
Aggregate Persistence
Adding Columns to Dimension Hierarchies
Aggregate Persistence will only consider dimension attributes which are present in the Dimension Hierarchies
In the example shown, a number of additional columns were added to the Product dimension so that Aggregate Persistence would include them in the aggregate tables
Your dimension hierarchies will naturally be larger as a result
-
Peak Indicators Limited 40
Aggregate Persistence
TimesTen Data Types
When creating tables in TimesTen, pay very close attention to the data types being used. With TimesTen the NUMBER data type works differently to Oracle and it defaults to a very large precision your tables will be significantly larger than they need to be
Recommendations: For any integer, use one of the TimesTen integer data types (TT_TINYINT, TT_SMALLINT, TT_INTEGER,
TT_BIGINT) If you need to have decimal places, then specify the exact precision e.g. NUMBER(5,2)
Read the TimesTen documentation to find out the most appropriate data types for your needs.
For example:
Name Oracle Data Type TT Data Type ---------------------- ------------------ ----------------- PRODUCT_KEY NUMBER TT_SMALLINT SUPPLIER_KEY NUMBER TT_SMALLINT CUSTOMER_KEY NUMBER TT_INTEGER SALES_VALUE NUMBER NUMBER(5,2) SALES_QTY NUMBER TT_SMALLINT
Failure to use the right data types with TimesTen can make your tables 5x bigger
than they need to be. In one case we reduced a table from 2.4GB
down to 600MB by changing data types
-
Peak Indicators Limited 41
Aggregate Persistence The Results
9 aggregates created for Inmarsat 8 aggregates created for Go Outdoors
Hour / Day / Month aggregates for Inmarsat
Call Summary facts
Year AGO and Week sales snapshots for Go Outdoors
-
Peak Indicators Limited 42
Aggregate Persistence Go Outdoors Performance Gain
88x average performance gain across 6 dashboards Some reports reduced down from 25 minutes to less than 20 seconds Delivered in
-
Peak Indicators Limited 43
Aggregate Persistence Inmarsat Performance Gain
27x average performance gain across 6 dashboards Delivered using TimesTen in-memory aggregates (without Compression) Delivered in
-
Peak Indicators Limited 44
Performance Tuning Real Success Stories Incremental Loading
-
Peak Indicators Limited 45
Incremental Loading Problems with Full Load
The results of the performance test were well above expectations
There was one key issue which prevented it from being considered for production use: Inmarsat: The full load of 9 aggregates took 15 hours Go Outdoors: The full load of 8 aggregates took 4.5 hours
A solution for incremental loading had to be found, but the solution had to be based on Aggregate Persistence
-
Peak Indicators Limited 46
Incremental Loading It is possible!
Incremental load is actually possible with Aggregate Persistence (and Exalytics)
You can modify the create aggregate scripts to implement incremental loading via the BI Server
NOTE: this will be covered in presentation Incremental Loading Exalytics using Notepad
The incremental load scripts can do the following: Create an aggregate staging table Populate the staging table with a subset of data extracted from source system Incrementally update the target aggregate table Analyze the target aggregate table
-
Peak Indicators Limited 47
Incremental Loading Peak ETA
To take things a step further, instead of using scripts to incrementally load the aggregates, we developed tool called Peak ETA (Extract Transform Aggregate) The tool reuses all the code generated by the Aggregate Persistence Wizard and automates the
process of producing all the incremental load scripts
The key benefits are: In-memory aggregates loaded incrementally and in parallel No downtime (aggregates are fully available whilst being loaded) A GUI console to orchestrate and monitor the loading of aggregates Error Detection with BI Dashboards with alerting capability Automatic code generation Automatically caters for Surrogate Keys RPD Metadata never dropped/recreated
All this is achieved using standard Exalytics technology and methodology
-
Peak Indicators Limited 48
Incremental Loading Aggregate Groups
With Peak ETA, you assign each dim/fact aggregate to an aggregate group The aggregate groups are processed in a pre-determined order The tables within each group are loaded in parallel You can configure the number of parallel threads in each group For example, you could have a sequence of 3 groups, each group is processed in parallel:
Group 1: Dimensions
Group 2: Hourly Aggregates
Group 3: Daily Aggregates
-
Peak Indicators Limited 49
Incremental Loading Peak ETA
Demonstration
-
Peak Indicators Limited 50
Incremental Loading Optimised Incremental Loading
9x faster 20x faster
-
Peak Indicators Limited 51
Performance Tuning Real Success Stories Summary
-
Peak Indicators Limited 52
Summary Go Outdoors - Going Forwards
Aggregate Persistence solution went live December 2012
Incremental loading running every day
Extremely reliable no failures
Further aggregates continue to be created as required, turnaround time typically 1 man-day
88x faster
-
Peak Indicators Limited 53
Summary Inmarsat - Going Forwards
Following the performance prototype, an Oracle BI 11g upgrade project was approved
11g upgrade completed March 2013 Upgraded 11g solution making use of Aggregate Persistence
Inmarsat have recently doubled the number of OBI users
New dashboards being implemented involving maps and
Oracle Spatial
Further lines of business being introduced to OBI 11g
Hopefully in the future a consolidation exercise will see the DW migrated onto their Exadata platform
27x faster
-
Peak Indicators Limited 54
Summary Inmarsat Satellite Spot Beam Map
NOTE: This map has been produced with mock-up test
data. The shading across the map bears no relevance to actual figures
-
Peak Indicators Limited 55
Summary Aggregate Persistence
Aggregate Persistence has the potential to become the standard aggregation tool for all customer implementations
Especially as incremental loading is now possible
The wider the practice is adopted, the stronger
the case for Exalytics
These 2 examples prove Oracle BI 11g can add significant business value to customers - bringing savings in time, cost and complexity
We look forward to further enhancements from Oracle!
-
Peak Indicators Limited 56
Performance Tuning Real Success Stories Further Notes
-
Peak Indicators Limited 57
Further Notes TimesTen vs Oracle DB
Query Response
Time
# fact records analysed TimesTen can be up to 9x faster for small-med workloads, especially with
Columnar Compression (gains of at least 40% on every query)
The Oracle DB will alter its query plan to improve efficiency with larger queries
e.g. hash joins, parallel query
Oracle DB eventually becomes optimal for the larger analytical queries
-
Peak Indicators Limited 58
Further Notes Designed for TimesTen/Exalytics
TimesTen In-Memory Cache excels at producing rapid high-density visuals
-
Peak Indicators Limited 59
Further Notes Optimal BI Caching Architecture?
Oracle BI Server Cache
TimesTen In-Memory Cache
Oracle Database
Responsiveness
Cache Size: 2GB Optimal use when
-
Peak Indicators Limited 60
Further Notes TimesTen Value
On a per-user basis, the list price for TimesTen for Exalytics is approx. 70% cheaper than Oracle Database Enterprise Edition
-
Peak Indicators Limited 61
Further Notes TimesTen Value
Do the maths for 208 users! Oracle DB Enterprise Edition: 208 x 950 = $197,600 Oracle TimesTen for Exalytics: 208 x 300 = $62,400
The difference is: $135,200
Choosing TimesTen instead of the Oracle DB will save you enough money to pay for an Exalytics box!
-
Peak Indicators Limited 62
-
Helping Your Business Intelligence Journey
Slide Number 1Slide Number 2Slide Number 3Slide Number 4Slide Number 5Slide Number 6Slide Number 7Slide Number 8Slide Number 9Slide Number 10Slide Number 11Slide Number 12Slide Number 13Slide Number 14Slide Number 15Slide Number 16Slide Number 17Slide Number 18Slide Number 19Slide Number 20Slide Number 22Slide Number 23Slide Number 24Slide Number 26Slide Number 27Slide Number 28Slide Number 30Slide Number 31Slide Number 32Slide Number 33Slide Number 34Slide Number 35Slide Number 36Slide Number 37Slide Number 38Slide Number 39Slide Number 40Slide Number 41Slide Number 42Slide Number 43Slide Number 44Slide Number 45Slide Number 46Slide Number 47Slide Number 48Slide Number 49Slide Number 50Slide Number 51Slide Number 52Slide Number 53Slide Number 54Slide Number 55Slide Number 56Slide Number 57Slide Number 58Slide Number 59Slide Number 60Slide Number 61Slide Number 62Slide Number 63