Oracle Advanced Analytics - Option To Oracle Database .
Oracle Advanced Analytics - Option to Oracle Database:Oracle R Enterprise and Oracle Data MiningData Warehouse Global LeadersWinter 2013Dan Vlamis, Vlamis Software SolutionsTim Vlamis, Vlamis Software Solutions816-781-2880http://www.vlamis.comMark Hornick, Oraclemark.hornick@oracle.com
Dan Vlamis and Vlamis Software Solutions Vlamis Software founded in 1992 in Kansas City, Missouri Developed more than 200 Oracle BI systems Specializes in ORACLE-based: Data WarehousingBusiness IntelligenceDesign and integrated BI and DW solutionsTraining and mentoringExpert presenter at major Oracle conferenceswww.vlamis.com (blog, papers, newsletters, services)Developer for IRI (former owners of Oracle OLAP)Co-author of book “Oracle Essbase & Oracle OLAP”Beta tester for OBIEE 11gReseller for Simba and Nokia map data for OBIEEHOL Coordinator for BIWA Summit 2013Copyright 2013, Vlamis Software Solutions, Inc.
Oracle Essbase & Oracle OLAP: The Guide toOracle’s Multidimensional Solution Published by Oracle Press Dan VlamisChris ClaterbosMichael NaderDavid CollinsFloyd ConradMitchell CampbellMichael Schrader Covers both Oracle Essbase and Oracle OLAP 500 PagesCopyright 2013, Vlamis Software Solutions, Inc.
Tim Vlamis’ Bio 20 years experience in business modeling and valuation,forecasting, and scenario analyses Trainer for Oracle University Two-Day Data Mining Course Professional Certified Marketer (PCM) from AMA Active Member of NICO (Northwestern Institute on ComplexSystems) Adjunct Professor of Business Benedictine College MBA Kellogg School of Management (Northwestern) BA Economics Yale UniversityCopyright 2013, Vlamis Software Solutions, Inc.
Mark Hornick’s Bio Director, Oracle Advanced Analytics Oracle’s R Technologies: ORE, ORD, ROracle, ORAAH Working with customers on POC/POV, training, applications Joined Oracle Data Mining Technologies group in 1999 throughacquisition of Thinking Machines Corp Recent publications through Oracle Press “Using R to Unlock the Value of Big Data” “Oracle Big Data Handbook” Blogger: blogs.oracle.com/R Twitter: @MarkHornick Connect on LinkedIn: Mark HornickCopyright 2013, Vlamis Software Solutions, Inc.
Presentation Overview Analytic options to the Oracle Database intro Oracle Advanced Analytics Overview (OBIEE demo) Oracle Data MiningOracle R EnterpriseAppsWhat is predictive analyticsOracle Data Mining Overview and PerspectiveOracle R Enterprise Overview and PerspectiveOracle Data Mining SQL Developer models demoOracle R Enterprise demoWhere to get started Start by establishing a common foundation through training Follow training with workshop and identification of POC project Training outlinesCopyright 2013, Vlamis Software Solutions, Inc.
Analytical Options to Oracle Database Oracle OLAP Defines a multi-dimensional data structure that allows information for highly complexcalculations to done quickly Fast query performance and incremental update Simplified access to analytic calculations Oracle Advanced Analytics (Data Mining & R) Delivers scalable, parallel in-database executionIn-database data mining algorithms and open source R algorithmsSQL, PL/SQL, R languagesWorkflow GUI and IDEsTargets wide range of users: business users, IT, and data scientistsIntegrated component of Oracle Database enabling enterprise analytical applications Oracle Spatial Provides the capability of relating data to geo positional coordinates, objects, and constructs Allows the construction and analysis of network topologiesCopyright 2013, Vlamis Software Solutions, Inc.
Spectrum of Oracle DB BI & AnalyticsOLAPSummaries,hierarchies anddimensional data“Analysis”What is theaverage incomeof mutual fundbuyers,by region,by year?Data Mining & RKnowledge discovery ofhidden patterns“Insight & Prediction”Who is likely topurchase a mutual fundin the next 6 monthsand why?Copyright 2013, Vlamis Software Solutions, Inc.SpatialSpatial relationshipsbetween data“Location”Where weremutual fundspurchasedin the last 3 years?
’s the best that can happen? Competitive AdvantagePredictive ModelingWhat will happen next?Forecasting/ExtrapolationWhat if these trends continue?Statistical AnalysisWhy is this happening?AlertsWhat actions are needed?Query/drill downWhere exactly is the problem?Ad hoc reportsHow many, how often, where?Standard ReportsAnalytic Degree of IntelligenceSource: Competing on Analytics, by T. Davenport & J. HarrisWhat happened?Access &Reporting
What is Data Mining? Automatically sifts through data tofind hidden patterns, discover new insights,and make predictions Data Mining can provide valuable results: Predict customer behavior (Classification)Predict or estimate a value (Regression)Segment a population (Clustering)Identify factors more associated with a businessproblem (Attribute Importance) Find profiles of targeted people or items (Decision Trees) Determine important relationships and “market baskets”within the population (Associations) Find fraudulent or “rare events” (Anomaly Detection)Copyright 2013, Vlamis Software Solutions, Inc.
Oracle Advanced Analytics Value PropositionTraditional AnalyticsOracle Advanced AnalyticsValue Proposition Fastest path from data to insightsData Import Fastest analytical developmentModel “Scoring” Fastest in-database scoring engineData PreparationandTransformation Flexible deployment options for analyticsSavings Lowest TCO by eliminating data duplication Secure, Scalable and ManageableModel BuildingData remains in the DatabaseData PreparationandTransformationAutomated data preparation for select analyticsModel “Scoring”Embedded Data PrepData ExtractionModel BuildingData PreparationHours, Days or WeeksSourceDataDatasets/ WorkAreaAnalyticalProcessingProcessOutputSecs, Mins or HoursTargetScalable distributed-parallel implementation of machine learningtechniques in-databaseScalable R leveraging database computational engineFlexible interface options – R, SQL, IDE, GUIFastest and most Flexible analytic deployment optionsRCan import 3rd party modelsCopyright 2013, Vlamis Software Solutions, Inc.
Examples of Oracle Applications Using OAA Human Capital Management Predictive Workforce—employee turnover and performance prediction and “What if?” analysis CRM Sales Prediction Engine--prediction of sales opportunities, what to sell, amount, timing, etc. Supply Chain Management Spend Classification-real-time flagging of noncompliance and anomalies in expense submissions Identity Management Oracle Adaptive Access Manager—real-time security and fraud analytics Retail Analytics Oracle Retail Customer Analytics—”shopping cart analysis” and next best offers Customer Support Predictive Incident Monitoring (PIM) Customer Service offering for Database customers Manufacturing Response surface modeling in chip design Predictive capabilities in Oracle Industry Data Models Communications Data Model implements churn prediction, segmentation, profiling, etc. Retail Data Model implements loyalty and market basket analysis Airline Data Model implements analysis frequent flyers, loyalty, etc.Copyright 2013, Vlamis Software Solutions, Inc.
Oracle Advanced Analytics ArchitectureSQL DeveloperR ClientOBIEEApplicationsOracle Database Enterprise EditionOracle Advanced AnalyticsNative SQL-PL/SQL Analytic Libraries plus high-performance R interfaceScalable, Distributed, Parallel ExecutionOracle R Distribution
Oracle DB Statistics & SQL Analytics (Free) Ranking functions Descriptive Statistics rank, dense rank, cume dist, percent rank, ntile Window Aggregate functions(moving andcumulative) Avg, sum, min, max, count, variance, stddev,first value, last value LAG/LEAD functions Direct inter-row reference using offsets Reporting Aggregate functions Sum, avg, min, max, variance, stddev, count,ratio to report Statistical Aggregates Correlation, linear regression family, covariance Linear regression Fitting of an ordinary-least-squares regressionline to a set of number pairs. Frequently combined with the COVAR POP,COVAR SAMP, and CORR functions DBMS STAT FUNCS: summarizes numerical columnsof a table and returns count, min, max, range, mean,median, stats mode, variance, standard deviation,quantile values, /- n sigma values, top/bottom 5 values Correlations Pearson’s correlation coefficients, Spearman's andKendall's (both nonparametric). Cross Tabs Enhanced with % statistics: chi squared, phi coefficient,Cramer's V, contingency coefficient, Cohen's kappa Hypothesis Testing Student t-test , F-test, Binomial test, Wilcoxon SignedRanks test, Chi-square, Mann Whitney test, KolmogorovSmirnov test, One-way ANOVA Distribution Fitting Kolmogorov-Smirnov Test, Anderson-Darling Test, ChiSquared Test, Normal, Uniform, Weibull, ExponentialCopyright 2013, Vlamis Software Solutions, Inc.
Oracle Advanced Analytics & OBI 11g DemoODM’s predictions & probabilitiesare available in the Database forreporting using Oracle BI EE andother toolsCopyright 2013, Vlamis Software Solutions, Inc.
Oracle Data Mining Perspective Bring the algorithms to the database kernel Focus on the business value of results rather thansources of error (glass is half full) Put predictive analytics into business processes Enable Oracle DB as an analytics engine forapplications Lots of “smart” decisions have already been made Data Miner GUI is great for analysts
Oracle R Enterprise Perspective Provide a powerful statistical programming language foradvanced analytics users Greatly reduce or eliminate R limitations:memory, parallelism, deployment Integrate comprehensive analytics into businessprocesses Leverage Oracle Database has HPC engine Participate in and contribute to the R ecosystem Provide leadership in analytics strategyCopyright 2013, Vlamis Software Solutions, Inc.
Oracle Data Mining Oracle Data Mining (ODM) is a component of theOracle Advanced Analytics (OAA) option to Oracle Database EE A collection of APIs and specialized SQL functions Includes a large number of specialized algorithms and built-inprocedures Makes use of many built-in capabilities of Oracle Database Provides a graphical user interface for creating analytical workflows
Data Mining ProvidesBetter Information, Valuable Insights and PredictionsCell Phone Churnersvs. Loyal CustomersSegment #3:IF CUST MO 7 AND INCOME 175K, THEN Prediction Cell Phone Churner, Confidence 83%, Support 6/39Insight &PredictionSegment #1:IF CUST MO 14 ANDINCOME 90K, THENPrediction Cell PhoneChurner, Confidence 100%,Support 8/39Customer MonthsSource: Inspired from Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management by Michael J. A. Berry, Gordon S. Linoff
Oracle Data Mining istic Regression (GLM)Decision TreesNaïve BayesSupport Vector Machines (SVM)Classical statistical techniquePopular / Rules / transparencyEmbedded appWide / narrow data / textRegressionLinear Regression (GLM)Support Vector Machine (SVM)Classical statistical techniqueWide / narrow data / textAnomalyDetectionOne Class SVMUnknown fraud cases or anomaliesAttributeImportanceMinimum Description Length (MDL)Principal Components Analysis (PCA)Attribute reduction, Reduce data noiseAssociationRulesAprioriMarket basket analysis / Next BestOfferClusteringHierarchical k-MeansHierarchical O-ClusterExpectation-Maximization Clustering (EM)Product grouping / Text miningGene and protein analysisFeatureExtractionNonnegative Matrix Factorization (NMF)Singular Value Decomposition (SVD)Text analysis / Feature reductionA1 A2 A3 A4 A5 A6 A7F1 F2 F3 F4Copyright 2011, Vlamis Software Solutions, Inc.
Oracle Data Miner Oracle Data Miner is a front end GUIfor Oracle Data Mining Extension for Oracle SQL Developer,a free IDE from Oracle that facilitatesdatabase interaction Functions as an object orientedprograming interface for designingdata mining processes andproceduresCopyright 2012, Vlamis Software Solutions, Inc.
Oracle R Enterprise Oracle R Enterprise (ORE) is a component of theOracle Advanced Analytics (OAA) option to Oracle Database EE Provides transparent access to database-resident data from R Execute R scripts at the database machine managed by OracleDatabase with data and task parallelism Execute R scripts from SQL Integrates R into the IT software stack Extends and enhances open source RCopyright 2013, Vlamis Software Solutions, Inc.
What is R? R is an Open Source scripting language andenvironment for statistical computing andgraphicshttp://www.R-project.org/ Alternative to SAS, SPSS, et al. The R environment R is an integrated suite of software facilities for datamanipulation, calculation and graphical display Around 2 million R users worldwide Widely taught in UniversitiesMany Corporate Analysts and Data Scientists know and use R Thousands of open sources packages Bioinformatics with RSpatial Statistics with RFinancial Market Analysis with RLinear and Non Linear ModelingCopyright 2013, Vlamis Software Solutions, Inc.
Why statisticians data analysts data scientists use RR is a statistics language similar to Base SAS or SPSS statisticsR environment is . PowerfulExtensibleGraphicalExtensive statisticsOOTB functionality withmany ‘knobs’ butsmart defaults Ease of installation and use Freehttp://cran.r-project.org/Copyright 2013, Vlamis Software Solutions, Inc.
Oracle’s R Technologies Oracle R DistributionSoftware available toR Community for free ROracle Oracle R Enterprise Oracle R Advanced Analytics for Hadoop 2013 Oracle – All Rights Reserved
Oracle Big Data PlatformOracle Big DataApplianceOptimized for Hadoop,R, and NoSQL ProcessingOracleBig DataConnectorsHadoopOracle RDistributionOracle NoSQLDatabaseOracle ROracle Big DataAdvancedAnalyticsConnectorsfor Hadoop Oracle cleExadataOracleExalytics“System of Record”Optimized for DW/OLTPOptimized forAnalytics & In-Memory WorkloadsOracle Oracle RDistributionOracle EnterprisePerformanceManagementOracle BusinessIntelligence ApplicationsOracle BusinessIntelligence ToolsOracle EndecaInformation DiscoveryDiscover & Analyze
Oracle R Distributionability to dynamically load:Intel Math Kernel Library (MKL)AMD Core Math LibrarySolaris Sun Performance LibraryOracleSupport Improve scalability at client and database for embedded R execution Enhanced linear algebra performance using Intel’s MKL, AMD’s ACML,and Sun Performance Library for Solaris Enterprise support for customers of Oracle Advanced Analytics option,Big Data Appliance, and Oracle Linux Free download Oracle to contribute bug fixes and enhancements to open source RCopyright 2013, Vlamis Software Solutions, Inc.
Oracle’s R Technologies ROracle R package enabling connectivity to Oracle Database Open source, publicly available on CRAN, free to R community Execute SQL statements from R interface Oracle Database Interface (DBI) for R based on OCI for high performance Supports Oracle R Enterprise database connectivity Oracle R Advanced Analytics for Hadoop (ORAAH) Provide transparent access to Hadoop ClusterManipulate data in HDFS, database, and file system - all from RWrite and execute MapReduce jobs with R leveraging CRAN R packagesProvide pre-packaged advanced analytics algorithmsCopyright 2013, Vlamis Software Solutions, Inc.
Traditional R and Database InteractionFlat Filesreadextract / exportDatabaseexportloadSQLRODBC / RJDBC / ROracleR scriptcron job Paradigm shift: R SQL RR memory limitation – data size, call-by-valueR single threadedAccess latency, backup, recovery, security ?Ad hoc script executionCopyright 2013, Vlamis Software Solutions, Inc.
Oracle R Enterprise A comprehensive, database-centric environment for end-to-endanalytical processes in R, with immediate deployment toproduction environmentsOperationalize entire R scripts in production applications –eliminate porting R codeSeamlessly leverage Oracle Database as an HPC environment forR scripts, providing data parallelism and resource managementAvoid reinventing code to integrate R results intoexisting applicationsTransparently analyze and manipulate data in Oracle Databasethrough R using versatile and customizable R functionsEliminate memory constraint of client R engineScore R models in Oracle DatabaseExecute R scripts through Oracle Database server machine forscalability and performanceGet maximum value from your Oracle Database and ExadataEnable integration and management through SQLIntegrate R into the IT software stack, e.g. OBIEECopyright 2013, Vlamis Software Solutions, Inc.Client R EngineSQL InterfacesSQL*Plus,SQLDeveloper, Transparency LayerORE packagesOracle DatabaseIn-dbstatsUser tablesDatabase ServerMachine
OBIEE Dashboard IntegrationParameterized analytics and graph customizationImprove time to insightAccommodate diverseconsumption pathsDeliver analytics that scalewith data volumes,variables, techniquesIntegrate readily with ITinfrastructure andsoftware stackLeverage CRAN packagesat database server 2013 Oracle – All Rights Reserved
Sensor Data Analysis 200K households, each with a utility“smart meter” 1 reading/meter/hour 200K x 8760 hours/year 1.752B readings per year 3 years worth of data 5.256B readings Each customer has 26280 readings Build one model per customer to understand/predict customermonthly usage If each model takes 10 seconds to build, 556 hours (23 days) with 128 DOP 4.4 hours
Smart Meter scenarioOracle DatabaseDatap1p2Datastoref(dat,args, )f(dat,args, )ModelModelpipnR ScriptRepositoryf(dat,args, )f(dat,args, )f(dat,args, ) {R Scriptbuildmodel}ModelModel
Smart Meter scenarioOracle DatabaseDatap1p2ModelDatastoreModelf(dat,args, )f(dat,args, )pipnR ScriptRepositoryf(dat,args, )f(dat,args, )f(dat,args, ) {R Scriptscoredatascores}scoresscoresscores
Build 200K models stored in database, partition on CUST IDore.groupApply (CUST USAGE DATA,CUST USAGE DATA CUST ID,function(x, ds.name) {cust id - x CUST ID[1]mod - lm(Consumption . -CUST ID, x)mod effects - mod residuals - mod fitted.values - NULLname - paste("mod", cust id,sep "")assign(name, mod)ds.name1 - paste(ds.name,".",cust id,sep "")ore.save(list paste("mod",cust id,sep ""), name ds.name1, overwrite TRUE)TRUE},ds.name "myDatastore", ore.connect TRUE, parallel TRUE)
Score 200K customers in database, partition on CUST IDore.groupApply(CUST USAGE DATA NEW,CUST USAGE DATA NEW CUST ID,function(dat, ds.name) {cust id - dat CUST ID[1]ds.name1 - paste(ds.name,".",cust id,sep "")ore.load(ds.name1)name - paste("mod", cust id,sep "")mod - get(name)prd - predict(mod, newdata dat)prd[as.integer(rownames(prd))] - prdres - cbind(CUST ID cust id, PRED prd)data.frame(res)},ds.name "myDatastore", ore.connect TRUE, parallel TRUE,FUN.VALUE data.frame(CUST ID numeric(0), PRED numeric(0)))
How to get started Lots of internal experts and lots of people who wouldlike to be involved and learn Lots of people intimidated by what they don’t know Start by “level setting” and establishing a strongfoundation with basic training (2 days) Immediately conduct a workshop (JAD style session)investigation of possibilities Decide on pilot projects and who works on it Start simple and return value quicklyCopyright 2013, Vlamis Software Solutions, Inc.
Oracle Data Mining Training (2 days) IntroductionData Mining Concepts and TerminologyThe Data Mining ProcessIntroducing Oracle Data Miner 11g Release 2Using Classification ModelsUsing Regression ModelsUsing Clustering ModelsPerforming Market Basket AnalysisPerforming Anomaly DetectionDeploying Data Mining ResultsCopyright 2013, Vlamis Software Solutions, Inc.
Oracle R Enterprise Training (2 Days) Oracle R Enterprise technologies introductionIntroduction to R hands-onORE transparency layer with hands-on exercisesORE embedded R execution with hands-on exercisesORE predictive analytics with hands-on exercisesUsing ROracleOverview of ORE with OBIEECopyright 2013, Vlamis Software Solutions, Inc.
Comparison of Training CoursesOracle Data MiningOracle R Enterprise Organized by algorithmIntro to data miningMBAs, BI Admin, DBAsFocused on business issuesUses GUIApproachable for new usersCopyright 2013, Vlamis Software Solutions, Inc.Organized by processIntro to Oracle R EnterpriseData Scientists, BI Admin, DBAsFocused on executing Rin Oracle Database Uses R scripts Technical
Oracle Test Drive Free to try out Oracle BI and Analytic OptionsGo to www.vlamis.com/tdRuns off of Amazon AWSHands-on Labs based on Collaborate 2012 HOLsTest Drives for: Oracle BIBI PublisherMap Views in OBIEEMicrosoft Excel against Oracle OLAPOracle Data MiningOracle R Enterprise Once sign up, you have private instance for 3 hours Available nowCopyright 2013, Vlamis Software Solutions, Inc.
Thank You!Analytic Options to the Oracle Database: R and Data MiningDan Vlamis, President, dvlamis@vlamis.comTim Vlamis, Consultant, tvlamis@vlamis.comVlamis Software Solutions, Inc.816-781-2880 www.vlamis.comMark Hornick, Director, WAsummit.comCopyright 2013, Vlamis Software Solutions, Inc.
Co-author of book “Oracle Essbase & Oracle OLAP” . Working with customers on POC/POV, training, applications Joined Oracle Data Mining Technologies group in 1999 through acquisition of Thinking Machines Corp
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
In-Database Analytics: Predictive Analytics, Oracle Exadata and Oracle Business Intelligence Charlie Berger Sr. Director Product Management, Data Mining and Advanced Analytics . 12 years ―stem celling analytics‖ into Oracle Designed advanced analytics into database kernel to leverage relational
Advanced Replication Option, Database Server, Enabling the Information Age, Oracle Call Interface, Oracle EDI Gateway, Oracle Enterprise Manager, Oracle Expert, Oracle Expert Option, Oracle Forms, Oracle Parallel Server [or, Oracle7 Parallel Server], Oracle Procedural Gateway, Oracle Replication Services, Oracle Reports, Oracle
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Oracle Analytics Cloud is built on a high-performance platform with flexible data storage, and provides you with a complete set of tools to derive and share data insights. Oracle Analytics Cloud supports your entire analytics workflow. 1-1. About Oracle Analytics Cloud. Features of Oracle Analytics Cloud. Editions: Enterprise and Professional
7 Messaging Server Oracle Oracle Communications suite Oracle 8 Mail Server Oracle Oracle Communications suite Oracle 9 IDAM Oracle Oracle Access Management Suite Plus / Oracle Identity Manager Connectors Pack / Oracle Identity Governance Suite Oracle 10 Business Intelligence
Oracle Advanced Analytics (Oracle Data Mining and Oracle R Enterprise) Data Warehousing Oracle OLAP, Oracle Spatial, OBIEE Expert presenters at major Oracle conferences www.vlamis.com (blog, papers, newsletters, services) Co-author of book “Oracle Essbase & Oracle OLAP” Beta tester for OBIEE 11g, Oracle 12c (in-memory)
Oracle Compute hosting Oracle Data Integrator, the Oracle BI Applications Configuration Manager and Oracle Database Cloud Service. See detailed deployment documentation published on Oracle Support. (Figure 2) Hybrid solutions deploy the BI semantic model, analyses and dashboards on Oracle Analytics Cloud with Oracle Data Integrator and Oracle .
