RealTime Analytics at Salesforce com Donovan Schneider Principal

Real-Time Analytics at Salesforce. com Donovan Schneider Principal Architect SDForum May, 2010

Agenda § Motivation § Our approach § Making it work § Conclusions and future directions

Evolution of Business Intelligence: Canned reports ➜ Ad-hoc query ➜ DW ➜ Real-Time Cloud Analytics More than 50 percent of data warehouse projects will have limited acceptance, or be an outright failure

Our Vision for CRM Analytics Deliver Insight That is Accessible, Real-time, and Trustworthy Accessible By Mere Mortals Real-time. Always. Flexible. In Sync. Reportable.

What Drives Actionable Insight? Reporting and Analytics that are… Reliable Powerful capabilities to answer real-world business questions Relevant Accurate & consistent results Responsive Fast performance, timely insight when needed Increased User Adoption Business user friendly Actionable Easy to Use Real. Time Visibility User Adoption Integrated into the CRM to enable actions from insight Actionable Insight

And Our Customers Use It. A Lot! 12 M+ reports 2. 5 M+ run per day 750 K dashboards 700 K views per day

Agenda § Motivation § Our approach § Making it work § Conclusions and future directions

We take a fundamentally different approach than most Complicated, Out of Date, & Rigid Real-Time Reporting DW Reporting Single tenant DW Easy, Real-Time and Flexible 1 x / day CRM ETL Processes Other Systems ERP 72, 000+ Companies HR Other Systems Other Clouds Painfully slow unless against DW Data is never fresh against DW Changes to CRM propagate slowly ETL process is complicated and expensive Usable by all, from rep to SVP Real-time, all the time Flexible & customizable Powerful w/o complicated DW One sharing model

Is a Data Warehouse Needed for CRM Analytics? Why people think they need a DW Performance Pre-aggregation was the only way to get decent reporting/analytics performance out of OLTP/CRM Requirement to combine multiple data sources in 1 report/dashboard -CRM systems were hard to integrate with external data sources -And then, they were not built at all for BI Why we don’t need one Cloud Computing Scale & Multitenant Optimization Engine Force. com API 200 M+ API calls/day 10 M records/hour Consumes External Web Services Business View of the Data Entire System is Business Driven -Corporate wide ontology -Single view of the customer -Historical data capture -Business people configure the system with their business terms, there is no IT translation req’d -Sales, Service, Analytics Clouds are all on same platform -History Tables and Analytical snapshots And DW based architecture makes the system out of date, rigid and expensive

Analytics § Dashboards § Reports § List Views § Search

Agenda § Motivation § Our approach § Making it work § Conclusions and future directions

Building a Multi-tenant Cloud Platform is Hard! Lots of Pieces to Assemble! § Relational / Text / Non-relational § Application Services / Lifecycles § Caching and Performance § Scalability and Reliability § Infrastructure and Backups § Release Processes § Development Lifecycle

Brief Review of Force. com Multi-tenancy Massive Shared Database Shared General Purpose Kernel Real-time App Composition

True Multi-tenancy: Why Share Everything? ~15 Databases ~100 Servers 2 Mirrors 100, 000’s of Unique Applications 1 Code Base

Force. com Data Architecture Shared Metadata Cache Bulk Processing Engine Multi-Tenant-Aware Query Optimizer Runtime Application Generator Full-Text Search Engine Real-time App Composition

Sharing Relational Data Structures is Hard Your Definitions Your Data Your Optimizations Indexes Pivot table for nonunique indexes Unique. Fields Pivot table for unique indexes Harrah’s Data Dell’s Products Relationships Pivot table foreign keys MRUIndex Pivot table for most -recently-used Your Rep’s Data Fall. Back. Index Pivot table for Name field index …others…

Flex Schema on Steroids: Everyone’s Data Flex Column: Multiple Data Types ID Tenant Data 2 1000001 Harrah’s $190 1000002 Harrah’s $250 1000003 Harrah’s $680 1000004 Harrah’s Poker 1000005 Harrah’s Black Jack 1000006 Harrah’s Craps 1000007 Dell Display 1000008 Dell Laptop 1000009 Dell Server

Flex Schema: Everyone’s Optimizations Muti-Tenant Table Multi-tenant Index Data 1 Tenant Data 2 Tenant 1000001 Harrah’s $190 Harrah’s 1000002 Harrah’s $250 $680 Harrah’s $680 1000003 1000004 1000005 1000006 1000007 1000008 1000009 Harrah’s Dell Craps Sync Copy Harrah’s Black Jack Harrah’s Craps Display Dell Laptop Dell Server Dell Number Display Laptop Server Data 2 10002 unus erat toto naturae 10003 vultus in orbe 10004 quem dixere chaeos 10005 rudis indigestaque $190 Harrah’s Poker Black Jack Text ID ID 10006 meis perpetuum 10007 deducite temopra 10008 carmen ante 10009 mare et terras 10010 tegit et quod 10011 omnia caelum 10012 unus erat toto naturae 10013 vultus in orbe 10014 quem dixere chaeos 10015 rudis indigestaque 10016 meis perpetuum 10017 deducite temopra 10018 carmen ante 10019 mare et terras 10020 tegit et quod 10021 omnia caelum 10022 unus erat toto naturae 10023 vultus in orbe 10024 quem dixere chaeos 10025 rudis indigestaque 10026 meis perpetuum 10027 deducite temopra 10028 carmen ante 10029 mare et terras 10030 tegit et quod 10031 omnia caelum 10032 unus erat toto naturae 10033 vultus in orbe

Reporting Index Optimization Muti-Tenant Table Reporting Index ID Tenant Data 2 1000001 Harrah’s $190 1000002 Harrah’s $250 Tenant Data 2 1000003 Harrah’s $680 Dell Display 1000004 Harrah’s Poker Dell Laptop 1000005 Harrah’s Black Jack Dell Server 1000006 Harrah’s Craps 1000007 Dell Display 1000008 Dell Laptop 1000009 Dell Server Sync Copy Data 7 … Data k

But How Do You Make the Queries Fast? Shared Metadata Cache Bulk Processing Engine Multi-Tenant-Aware Query Optimizer Runtime Application Generator Full-Text Search Engine Real-time App Composition

A Real World Question Michael Dell wants to know if Servers are selling well in the West. How will Force. com answer this question quickly?

Multi-tenant Query Optimizer Indexes ID Data 1 Data 2 10002 unus erat toto naturae 10003 vultus in orbe 10004 quem dixere chaeos 10005 rudis indigestaque 10006 meis perpetuum 10007 deducite temopra 10008 carmen ante 10009 mare et terras 10010 tegit et quod 10011 omnia caelum 10012 unus erat totonaturae 10013 vultus in orbe 10014 quem dixere chaeos 10015 rudis indigestaque 10016 meis perpetuum 10017 deducite temopra 10018 carmen ante 10019 mare 10020 tegit Servers The fastest path to the answer ID West Millions of Sales Line Items Visibility Data 1 Data 2 10002 unus erat toto naturae 10003 vultus in orbe 10004 quem dixere chaeos 10005 rudis indigestaque 10006 meis perpetuum 10007 deducite temopra et terras 10008 carmen ante et quod 10009 mare et terras 10021 omnia caelum 10010 tegit et quod 10022 unus erat toto naturae 10011 omnia caelum 10023 vultus in orbe 10012 unus erat totonaturae 10024 quem dixere chaeos 10013 vultus in orbe 10025 rudis indigestaque 10014 quem dixere chaeos 10026 meis perpetuum 10015 rudis indigestaque 10027 deducite temopra 10016 meis perpetuum 10028 carmen ante 10017 deducite temopra 10029 mare et terras 10018 carmen ante 10030 tegit et quod 10019 mare et terras 10031 omnia caelum 10020 tegit et quod 10032 unus erat toto naturae 10021 omnia caelum 10033 vultus in orbe M. Dell 10022 unus erat toto naturae 10023 vultus in orbe 10024 quem dixere chaeos 10025 rudis indigestaque 10026 meis perpetuum 10027 deducite temopra 10028 carmen ante 10029 mare et terras 10030 tegit et quod 10031 omnia caelum 10032 unus erat toto naturae 10033 vultus in orbe

Multi-tenant Query Optimizer Go Shared Visibility Indexes ID Data 1 Data 2 ID Data 1 Run pre-queries Check user Visibility Data 2 10002 unus erat toto naturae 10003 vultus in orbe 10004 quem dixere chaeos 10005 rudis indigestaque 10006 meis perpetuum 10007 deducite temopra 10008 carmen ante 10009 mare et terras 10010 tegit et quod 10011 omnia caelum 10012 unus erat totonaturae 10013 vultus in orbe 10014 quem dixere chaeos 10015 rudis indigestaque 10016 meis perpetuum 10017 deducite temopra 10018 carmen ante 10019 mare et terras 10020 tegit et quod 10021 omnia caelum 10022 unus erat toto naturae 10023 vultus in orbe 10024 quem dixere chaeos 10025 rudis indigestaque 10026 meis perpetuum 10027 deducite temopra 10028 carmen ante 10029 mare et terras 10030 tegit et quod 10031 omnia caelum 10032 unus erat toto naturae 10033 vultus in orbe Multi-tenant Optimizer Statistics Check filter selectivity Build query based on results of pre-queries Execute query Stop User Visibility = Filter Selectivity = # of rows that the user can access How specific is this filter?

Acting on pre-queries Pre-Query Selectivity measurements Construct final database query, forcing… User Filter Low …nested loops join; drive using view of rows that the user can see Low High …use of index related to filter High Low …ordered hash join; driving using data table High … use of index related to filter

Report Execution Application Server Pre-queries SQL Sorts Aggregations Filters cache rows Joins Filters Hints Aggregations

Agenda § Motivation § Our approach § Making it work § Conclusions and future directions

Conclusions § Real-time BI is possible § A data warehouse is not required § Interesting technical challenges – Cannot rely on database’s cost-based optimizer – Sophisticated sharing models great for customers but technically challenging – Real-time data limits caching applicability – Protect tenants from each other § We need help

Technical Direction § Increased focus on usability § Expanded analytical capabilities § Collaboration § Scalability