Gain Performance & Scalability With RightNow Analytics

1. Gain Performance & Scalability WithRightNow Analytics Expert Seminar May 26, 2010 Presenters: Nick Lenzmeier Rob NashGreg Rice Software Development Manager Professional Services Consultant Director, Product Management RightNow Technologies, Inc. RightNow Technologies, Inc. RightNow Technologies, Inc.

2. 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

3. Basic Best Practices

4. RightNow Business Intelligence Solutions Architecture - RightNow Analytics • RightNow CX • Production Solution • RightNow CX RightNow Analytics RightNow CX Operational Database RightNow CX Report Database

5. Basic Best Practices for Performance & Scalability – RightNow Analytics Basic Best Practices • 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 • Use the Operational Database for smaller & real-time reports • Allow server to change the data source as necessary • Use unless real-time information critical • 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) • 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

6. RightNow Business Intelligence Solutions Architecture • RightNow CX • Production Solution • RightNow CX RightNow Enterprise Analytics RightNow Analytics • Productized ETL RightNow CX Operational Database Enterprise Analytics Data Mart • (ETL = Extract, Transform & Load) RightNow CX Report Database

7. RightNow Business Intelligence Solutions Architecture • RightNow CX • Production Solution • RightNow CX RightNow Enterprise Analytics RightNow Analytics • Productized ETL RightNow CX Operational Database Enterprise Analytics Data Mart • (ETL = Extract, Transform & Load) RightNow CX Report Database Customer Data Center Returns Call Switch IVR • ETL Other… Orders Acctng.

8. Basic Best Practices for Performance & Scalability Basic Best Practices • 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 • Use the Operational Database for smaller & real-time reports • Allow server to change the data source as necessary • Use unless real-time information critical • 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) • 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 • 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

9. Advanced Best Practices

10. 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.

11. 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.

12. 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.

13. Questions

14. Other Resources

15. 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

16. Thank You