Transcript ExpertSeminar_AnalyticsPerformance(May 26 2010)

Gain Performance & Scalability With
RightNow Analytics
Expert Seminar
May 26, 2010
Presenters:
Nick Lenzmeier
Software Development Manager
RightNow Technologies, Inc.
Rob Nash
Professional Services Consultant
RightNow Technologies, Inc.
Greg Rice
Director, Product Management
RightNow Technologies, Inc.
© RightNow Technologies, Inc.
Agenda
Presenter introductions
Best practices discussion (20 minutes)
Basic best practices (5 minutes)
Advanced best practices (15 minutes)
Answer questions posed on registration site (20 minutes)
Answer other questions (20 minutes)
Other Resources
Conclusion
Basic Best Practices
© RightNow Technologies, Inc.
RightNow Business Intelligence Solutions
Architecture - RightNow Analytics
RightNow CX
Production Solution
RightNow
Analytics
RightNow CX
Operational Database
RightNow CX
Report Database
RightNow CX
Basic Best Practices for Performance &
Scalability – RightNow Analytics
Basic Best Practices
1. Use the Report Database to report on large data volumes
–
–
–
Shifts processing from live production system
Improves performance of live production system and of report
Larger thresholds for more processing intensive reports
2. Use the Operational Database for smaller & real-time reports
3. Allow server to change the data source as necessary
–
Use unless real-time information critical
4. Queue large reports & processing-intensive reports
–
–
–
Avoids report time-out
Shifts processing to a different server
Receive benefits of Report DB (when shifted to Report DB)
5. Report on small data sets
–
–
–
Use filters to narrow analyses as much as possible
Use fixed filters & provide default values for run-time filters
Inner join tables to return the smallest data set possible
RightNow Business Intelligence
Solutions Architecture
RightNow CX
Production Solution
RightNow CX
RightNow
Enterprise
Analytics
RightNow
Analytics
RightNow CX
Operational Database
RightNow CX
Report Database
Productized ETL
(ETL = Extract, Transform & Load)
Enterprise Analytics
Data Mart
RightNow Business Intelligence
Solutions Architecture
RightNow CX
Production Solution
RightNow CX
RightNow
Enterprise
Analytics
RightNow
Analytics
RightNow CX
Operational Database
Productized ETL
Enterprise Analytics
Data Mart
(ETL = Extract, Transform & Load)
RightNow CX
Report Database
Customer Data Center
Call
Switch
IVR
Returns
Orders
Acctng.
Other…
ETL
Basic Best Practices for Performance &
Scalability
Basic Best Practices
1.
Use the Report Database to report on large data volumes
–
–
–
Shifts processing from live production system
Improves performance of live production system and of report
Larger thresholds for more processing intensive reports
2.
Use the Operational Database for smaller & real-time reports
3.
Allow server to change the data source as necessary
–
4.
Queue large reports & processing-intensive reports
–
–
–
5.
Avoids report time-out
Shifts processing to a different server
Receive benefits of Report DB (when shifted to Report DB)
Report on small data sets
–
–
–
6.
Use unless real-time information critical
Use filters to narrow analyses as much as possible
Use fixed filters & provide default values for run-time filters
Inner join tables to return the smallest data set possible
Use Enterprise Analytics for extended insights and scalability
–
–
–
–
Provides a data mart, star schema and OLAP technology
Optimized for high performance reporting on large data volume
Extends historical reporting capabilities and breadth of metrics
Supports analytics on external data sources
Advanced Best Practices
© RightNow Technologies, Inc.
Best Practices – Key Terms
Query Threshold
• The maximum estimated number of rows a report query is allowed to analyze in order to return a result
set for a given configuration.
Operational Database
• Live Data
• Highly Transactional
• Default query threshold: 2,000,000 rows.
–
Report queries exceeding this threshold will automatically execute on the Reporting Database unless specified not to do so.
Reporting Database
• Near-Live Data
• Reporting Only – No Transactions
• Default query threshold: 5,000,000 rows.
–
Report queries exceeding this threshold will not execute.
Deferred Reports
• Reports that have required more than a minute (by default) to execute will be marked as Deferred.
• Default query threshold: 200,000 rows.
–
Report queries exceeding this threshold will be prompted for queuing.
Best Practices – Improving Query Performance
Filters
Filter on indexed fields whenever possible.
• How do I determine whether a field is indexed?
–
–
–
Data Dictionary lists the indexed standard fields for each table.
Custom Field editors identify which custom fields are indexed.
Report Analyzer identifies which report filters are on indexed fields.
• What if I need to filter on a field that is not indexed?
–
If possible, add additional filters that are on indexed fields but won’t change the desired result set. Example: searching on a street address that only exists in a
certain state. contacts.street is not indexed but contacts.prov_id is indexed.
Avoid using expressions on the left hand side of the filter.
• Example: Use contacts.last_name LIKE ‘last10%’ instead of substr(contacts.last_name, 1, 6) = ‘last10’
Tables
Use outer joins only as necessary.
Avoid joining large tables, i.e. inc_performance to transactions.
• If joining large tables is necessary, add filters between the two tables.
Columns
Avoid the use of sum_distinct() whenever possible.
Expressions in General
Allow the database to cache your report queries.
• Queries that contain the current timestamp cannot be cached. Use date_trunc(sysdate(), MINUTES) or
date_trunc(sysdate(), HOURS) instead of sysdate() in end-user reports or other reports that execute at a high
frequency.
Best Practices – Administering Report
Performance
Identifying ‘Deferred’ reports
Run the standard report \Public Reports\Common\Site Administration\Reports\Deferred
Reports.
What can I do once a report becomes Deferred?
Identify the reason for deferral/queuing:
• One user chose inappropriate run-time filter values. Resolution: Edit the report and uncheck the ‘Set report to
deferred execution’ option.
• Report examines too much data, regardless of run-time filters. Resolution: re-design report or plan for report
queuing.
My agents rely on report A for real-time time data but the data is often
stale. Why does that happen?
The server automatically switches intensive queries from the operational database to
the reporting database.
If the report must execute against the operational database, edit the report and
• Select the ‘Operational Database’ as the data source.
• Unselect the ‘Allow server to change the data source as necessary’ option.
To identify reports targeting the Reporting Database, create a custom report on the
analytics_core table and filter on the analytics_core.opts field.
Improving drilldown/up performance
By default, each drilldown/up re-executes the report query. To avoid this, use split level
display or select the ‘Cache drilldown levels’ report option.
Questions
© RightNow Technologies, Inc.
Other Resources
© RightNow Technologies, Inc.
Other Resources
RightNow Guide: How to Improve Reporting Performance (available in June 2010)
Advanced RightNow Analytics presentation from 2009 RightNow Summit
Customer Community > Library > Presentations > 2009 RightNow North America User
Conference > Power Users > Advanced Analytics
Answers on RightNow Support Site:
Answer ID #2380 “Improving performance of reports and console view”
Answer ID #2817 “Operational vs. reporting database in RightNow Analytics”
Answer ID #2776 “Common questions regarding queued reports”
Answer ID #2149 “Receiving a message “Query processes too much data”
Answer ID #2223 “Analytics Error: Data Set has exceeded maximum size”
Answer ID #1266 “Impact of indexing a custom field”
Answer ID #1839 “Types of table joins used with reports and views”
Answer ID #2844 “Using the report analyzer with custom reports”
RightNow CX Analytics Manual
RightNow Customer Care
Your RightNow Customer Success Manager
Thank You
© RightNow Technologies, Inc.