APPFLUENT TECHNOLOGY

1
APPFLUENT TECHNOLOGY Actionable Metrics to
Improve Query Performance and Manage BI/DW
Deployments
2
Agenda

• Introduction of Appfluent Technology
• Customer Scenarios - (Environment, Challenges
  and Approach)
• Demonstrate Practical Examples
• Q A

3
Owens Minor

• Industry Multi-billion dollar Medical Supplies
  Distributor
• Environment
• Extranet developed on Business Objects
• Mainly Business Objects 5.1.9 some Business
  Objects v6.5
• 5000 extranet users (customers, suppliers and
  partners)
• Centralized data warehouse (Oracle 9i)
• Challenges
• Control support costs as end-users grow
• Proactively control rising number of query
  performance issues
• Maintain optimal performance during and after
  migration to BO 6.5

Don Stoller, Director, OM Solutions, Owens
Minor It is very important for us to ensure
that we continue delivering significant value to
our extranet users both in terms of business
information as well as user-experience. We needed
better insight into data usage and query
performance to help us be proactive in managing
the growth in our Business Intelligence
environment.
4
State of New Jersey

• Industry State and Local Government
• Environment
• Data management Services division
• 800 Business Objects users
• Business Objects 6.5
• 14 Oracle Data Marts
• Challenges
• Limited resources and staff Growing number of
  projects
• Slow running reports Increasing complexity of
  queries
• End-user complaints overwhelming staff
• Too expensive to re-create every performance issue

Dan Paolini, Director, Data Management Services,
State of New Jersey In this era of austere
budgets, NJ State CIO Charles S. "Steve" Dawson
has directed that we find ways to improve the
effectiveness of our current resources. We are
providing better service and we will be able to
support more projects more effectively using our
existing staff.
5
Business Objects

• Industry Leading Business Intelligence Software
  Company
• Environment
• 3000 global users of BI applications
• Globally distributed data warehouses (Paris/San
  Jose/Vancouver)
• Data Supported Global Customers, Sales, Finance
  and Support
• Multiple BI Applications - Business Objects
  client- server,
• Webi, XI and Crystal Enterprise
• Challenges
• Quickly standardize on single platform Business
  Objects XI
• Consolidate data into single data warehouse
• Develop best practices to improve
  performance/reduce TCO
• Audit data access to Financial, HR (Peoplesoft)
  and Sales

Thierry Leleu, Senior Director, BI
Competency, We need both the Business Objects
Auditor and Appfluent to get a full picture of BI
and data warehouse environment. Together it helps
us develop the best practices to optimize and
maximize our BI infrastructure investment
6
BI Model Deployment at Business Objects
EDW
Universes Meta Data
Staging
OLTP
Data Warehousing

• BI Version Control
• Data Usage Analysis
• and Auditing

• Transfer
• Mapping
• Cleansing

• Load
• Index
• Aggregation

• Extract
• Scrub
• Transform

• Dashboard Access Analysis
• Resource Scheduling Distribution
• Reporting delivery

• Data and Query Usage
• Metrics

Meta Data
Monitoring Auditing
7
Defining the Challenges
No Visibility for BI and DW Team

• Increasing complexity of BI environment Growing
  support costs
• Query performance issues and too many end-user
  complaints
• Inadequate information from DBA tools
• Too many BI applications and databases to
  maintain
• Dont know what data is used and/or dormant
• Unable to easily track who/when/how sensitive
  data is used
• Need to keep a record of access and update to
  data to ensure security compliance

Performance
Consolidation
Compliance Audits
8
Value of Data and Query Usage Metrics
Baseline KPIs and Measure Variances
Develop Strategies for Reducing Query Effort
Recommend Optimal Indexing Strategies
Reduce Data Footprint Validate Data Use

Improve Query Quality
Audit Data Access
Demo.
9
Data Usage and Query Performance Metrics to
Manage BI and DW Deployments
Measure KPIs
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
Product Architecture
10
Main
Key Performance Indicators
KPI and Variances
Query Activity
Data Usage Activity
Track KPIs for data usage and query performance.
Measure variances for proactive management.
Application Activity
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
11
Main
Key Performance Indicators
KPI and Variances
Query Activity
Measure activity by queries generated by users.
Data Usage Activity
Application Activity
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
12
Main
Measure table and column usage activity by
databases, users, geography or department.
Key Performance Indicators
KPI and Variances
Query Activity
Data Usage Activity
Application Activity
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
13
Main
Key Performance Indicators
KPI and Variances
Query Activity
Data Usage Activity
Application Activity
Measure activity across applications and
standardize on few applications
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
14
Main
Key Performance Indicators
Reduce Query Effort
Expensive Queries
Aggregation Candidates
Aggregate Dimension Keys
Expensive Database Objects
Complex Calculations
Identify and resolve the most frequently run long
running queries and deliver the most impact with
optimization.
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
15
Main
Create pre-aggregated data structures by
identifying frequently summarized data.
Key Performance Indicators
Reduce Query Effort
Expensive Queries
Aggregation Candidates
Aggregate Dimension Keys
Expensive Database Objects
Complex Calculations
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
16
Main
Discover dimensions and functions used to
create summarized data. Build optimal cubes.
Key Performance Indicators
Reduce Query Effort
Expensive Queries
Aggregation Candidates
Aggregate Dimension Keys
Expensive Database Objects
Complex Calculations
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
17
Main
Key Performance Indicators
Reduce Query Effort
Expensive Queries
Aggregation Candidates
Aggregate Dimension Keys
Expensive Database Objects
Complex Calculations
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
18
Main
Create pre-calculated columns or views for
frequently run expensive operations.
Key Performance Indicators
Reduce Query Effort
Expensive Queries
Aggregation Candidates
Aggregate Dimension Keys
Expensive Database Objects
Complex Calculations
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
19
Main
Key Performance Indicators
Reduce Query Effort
Proactively identify end-user generated
performance issues.
Improve End-User Activity
Unconstrained Queries
Query Errors
Frequently Used Data
Column Usage Metadata
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
20
Main
Track and measure query errors. Proactively
quantify the type of errors to plan and
implement an optimal support strategy.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Unconstrained Queries
Query Errors
Frequently Used Data
Column Usage Metadata
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
21
Main
Identify most frequently used data by users and
the impact on performance.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Unconstrained Queries
Query Errors
Frequently Used Data
Column Usage Metadata
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
22
Main
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Unconstrained Queries
Analyze usage metadata for a specific column
for data design and optimization
Query Errors
Frequently Used Data
Column Usage Metadata
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
23
Main
Reduce complexity of views by identifying
infrequently used views, as well as columns
within views that are never used.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Usage of Views
Active Joins
Expensive Joins
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
24
Main
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Usage of Views
Active Joins
Expensive Joins
Validate joins used against those designed.
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
25
Main
Collapse tables or views after identifying
frequently run queries that perform large number
of expensive joins.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Usage of Views
Active Joins
Expensive Joins
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
26
Main
Define new indexes by identifying the most
frequently used columns that are used with
constraints or joins without indexes.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Frequently Used Columns
Validate Existing Indexes
Define Compound Indexes
Reduce Data Footprint
Audit Data Access
27
Main
Validate and optimize existing compound indexes
by analyzing how columns are used together in
queries.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Frequently Used Columns
Validate Existing Indexes
Define Compound Indexes
Reduce Data Footprint
Audit Data Access
28
Main
Build optimal compound indexes by analyzing how
columns are used together in queries.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Frequently Used Columns
Validate Existing Indexes
Define Compound Indexes
Reduce Data Footprint
Audit Data Access
29
Main
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Discover databases that are infrequently used.
Drill down to identify and eliminate unused or
dormant columns.
Optimize Indexing Strategy
Reduce Data Footprint
Dormant-Unused Data
Duplicate Objects
Duplicate Table Details
Audit Data Access
30
Main
Discover duplicate tables across multiple
databases.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Dormant-Unused Data
Duplicate Objects
Duplicate Table Details
Audit Data Access
31
Main
Eliminate redundant tables by quickly
discovering tables with similar structures
across multiple databases.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Dormant-Unused Data
Duplicate Objects
Duplicate Table Details
Audit Data Access
32
Main
Audit tables and columns accessed by a specific
user based on date, time and database schema.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
Audit by Specific User
Audit by Specific Data
Audit by SQL TYPE
33
Main
Audit all users that access specific data and
track how the data is queried.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
Audit by Specific User
Audit by Specific Data
Audit by SQL TYPE
34
Main
Drill down to audit data access by SQL Type.
Based on statement ID discover the details of the
full statement text.
Key Performance Indicators
Reduce Query Effort
Improve End-User Activity
Decrease Complexity
Optimize Indexing Strategy
Reduce Data Footprint
Audit Data Access
Audit by Specific User
Audit by Specific Data
Audit by SQL TYPE
35
Appfluent Technology Architecture
Main
Business Users
IT Users
Client Tier
Application Tier
Watcher
Appfluent Server
Repository
Database Tier
Analyzer
Cataloger
Watcher
Databases
36
Business Objects Auditor and Appfluent Technology
Main
Thierry Leleu, Senior Director, BI
Competency, We need both the Business Objects
Auditor and Appfluent to get a full picture of BI
and data warehouse environment. Together it helps
us develop the best practices to optimize and
maximize our BI infrastructure investment

• Business Objects Auditor
• Audits user sessions and usage of reports,
  universes and objects within Business Objects
  server.
• Benefits
• Impact analysis of documents and universes
• Lineage information of objects
• Object and security change tracking

• Appfluent Query Performance
• Provides actionable metrics for data usage and
  query performance for Business Objects and other
  reporting applications and Data Warehouses.
• Benefits
• Track KPIs and measure variances
• Optimize query performance based on usage
• Clean-up unused data Audit data accessed

37
Business Objects Auditor and Appfluent Technology
Main

• Business Objects Auditor
• Audits user sessions and usage of reports,
  universes and objects within Business Objects
  server.
• Benefits
• Impact analysis of documents and universes
• Lineage information of objects
• Object and security change tracking

• Universe Management
• Universe objects usage
• Change Impact analysis
• User Activity Metrics
• Users login and sessions
• User profile and information
• Report refresh and edit activity
• Document Management
• Document usage metrics
• Change impact analysis
• System Information
• Session duration by users, clusters
• Concurrent users, peak usage
• Broadcast Agent
• Metrics on Jobs by BCA, users, frequency

• Appfluent Query Performance
• Provides actionable metrics for data usage and
  query performance for Business Objects and other
  reporting applications and Data Warehouses.
• Benefits
• Track KPIs and measure variances
• Optimize query performance based on usage
• Clean-up unused data Audit data accessed

38
Business Objects Auditor and Appfluent Technology

• Appfluent Query Performance
• Provides actionable metrics for data usage and
  query performance for Business Objects and other
  reporting applications and Data Warehouses.
• Benefits
• Track KPIs and measure variances
• Optimize query performance based on usage
• Clean-up unused data Audit data accessed

Main

• Measure variances with key performance indicators
  (KPIs)
• Query activity and complexity
• Application and user activity (Business Objects,
  Cognos, Access and others.)
• Query quality and errors
• Data warehouse performance ratios
• Reduce Query Effort
• Optimize most frequently run expensive queries
• Identify candidates for pre-aggregation
• Pin-point expensive database objects
• Reduce impact of expensive queries
• Improve User Experience and Activity
• Identify ad-hoc query performance issues
• Analyze the most common queries
• Measure query errors and support activity
• Decrease Design Complexity
• Prune view trees and view stacks

• Business Objects Auditor
• Audits user sessions and usage of reports,
  universes and objects within Business Objects
  server.
• Benefits
• Impact Analysis of documents and universes
• Lineage information of objects
• Object and security change tracking

39
Main
Danny Siegel, Senior Manager, Business
Technology, Pfizer The Appfluent software has
greatly enhanced our auditing capability and has
improved security around our financial data. The
product is non-intrusive and hasnt added any
performance overhead. Frankly, its allowed a
non-DBA/business user, like myself, to quickly
and reliably report on data usage in a simple,
yet detailed, manner.
Ed Giebel, VP IT Architecture, Avnet Appfluent
has made an enormous difference in our data
management environment. We are able to identify
data usage and more importantly non-usage of old
databases, some of which have only 10 of the
data ever being used. We are able to eliminate
dormant data that has proliferated via corporate
acquisitions, and helps us manage the system more
efficiently and cost effectively. Based on usage
analysis we are also able to consolidate vendors
to minimize costs and leverage relationships. The
return on investment is less than 6 months.
Don Stoller, Director, OM Solutions, Owens
Minor It is very important for us to ensure
that we continue delivering significant value to
our extranet users both in terms of business
information as well as user-experience. Appfluent
Query Performance gives us the needed insight
into data usage and query performance to help us
be proactive in managing the growth in our
Business Intelligence environment.
Dan Paolini, Director, Data Management Services,
State of New Jersey Since implementing
Appfluent, we have improved the efficiency and
productivity of our Data Management Services
unit. It is able to quickly pinpoint application
usage and performance inefficiencies, slow
reports, and bad queries, allowing us to optimize
our data stores and enhance performance. We are
providing better service and we will be able to
support more projects more effectively using our
existing staff.