predictive analytics with excel and sql server data mining add-ins

© 2013 Impact Analytix, LLC

q u i c k l y m a k e a p o s i t i v e i m p a c t

Excel Predictive Analytics with SQL Server Data Mining Add-Ins

Jen UnderwoodFounder & Principal ConsultantImpact Analytix, LLC

[email protected]


Impact Analytix, LLC

o Impact Analytix, LLC is a boutique business intelligence and predictive analytics firm based in Tampa, Florida.

o Jen Underwood, Founder & Principal Consultant • ~20 years of business intelligence industry experience• Former Global Microsoft BI and Analytics Technical Product

Manager and seasoned Big-Four Consulting BI Practice Lead• Passionate technology blogger, evangelist and volunteer,

TDWI, BeyeNETWORK, PASS, SharePoint Conference, and Microsoft TechEd

• Bachelor of Business Administration degreeUniversity of Wisconsin Milwaukee

Post Graduate Certificate Computer Science - Data Mining University of California, San Diego


Excel Predictive Analytics


Agenda

o Overview of Data Miningo SQL Server Data Mining Add-Ino Predictive Analytics Developmento Integrating and Extending Data Mining


Predictive Analysis

Presentation Exploration Discovery

Passive

Interactive

Proactive

Role of Software

Business InsightCanned Reporting

Ad-Hoc Reporting

OLAP

Data Mining

Data Mining enabling Predictive Analysis


The Value of Predictive Analysis

Predictive Analysis

Seek Profitable Customers

Understand Customer

Needs

Anticipate Customer

Churn

Predict Sales &

Inventory

Funnel Marketing Campaigns

Estimate Survey Results

Inform Common Business Decisions with Actionable Insight


SQL Server Predictive Analysis

Com

plet

e • Pervasive Delivery through Microsoft Office

• Comprehensive Development Environment

• Enterprise Grade Capabilities

• Rich and Innovative Algorithms

Inte

grat

ed • Native Reporting Integration

• In-Flight Mining during Data Integration

• Insightful Analysis

• Predictive KPIs

Exte

nsib

le • Predictive Programming

• Custom Algorithms and Visualizations

Part of SQL Server Analysis Services


Complete Predictive Analysis

Comprehensive• Empower all users with

predictive analysis capabilities• Enable advanced users with

more validation and control

Intuitive• Enable complex data

mining through simple, automated tasks

• Reduce the learning-curve with a familiar environment

• Deliver actionable insight with clear graphical visualizations

Collaborative• Share analysis through

interactive graphical visualizations

• Share insight with clear and prompt publishing capabilities

Pervasive Delivery through Microsoft Office


Data Mining Add-Ins

“What Microsoft has done is to make data mining available on the desktop to everyone” - David Norris, Associate Analyst, Bloor Research


Automated Tasks in Data Mining for Excel

o Analyze Key Influencers - Detects key characteristics that influence an outcome.

o Detect Categories - Helps identify and segment data based on common properties.

o Fill From Example - Helps complete a partially populated column automatically based on patterns in the table.

o Forecast - Enables users to predict future values based on trends in the data set. The forecast values are added to the original table and charts.

o Highlight Exceptions - Enables users to detect values outside the expected range.

o Scenario Analysis - What If and Goal Seeking, Enables users to gain insight into the impact of a potential changes applied to values of the data set.

o Prediction Calculator - Generates an interactive form for scoring new cases. The influence of each attribute is translated into a set of scores.

o Shopping Basket Analysis - Enables users to detect the relationship between items frequently purchased together for bundling or improved product placement.


Data Mining Add-In

oData Preparationo Explore, clean and set up your data for

data mining

oData Modelingo Build patterns and trends from data to

make predictions

oAccuracy and Validationo Test and validate your model

oModel Usage & Managemento Browse, modify, and manage existing

mining models that are stored on an instance of Analysis Services

oDocumentationo Trace your actions as Data Mining

Extensions (DMX) statements or as Analysis Services Scripting Language (ASSL).

Full Development Lifecycle within Excel



o Intuitive Data Mining Wizardo Graphic Data Mining Designero Visual & Statistical Validation

o Cross-validationo Lift chartso Profit charts

o Easy and Efficient Access to Source Datao Cachingo Filteringo Aliasing

Comprehensive Development Environment



Rapid Development

High Availability

Superior Performance

and ScalabilityRobust

Security Features

Enhanced Manageability

Enterprise Grade Capabilities

Analysis Services



Broad Range of Choices to Build Optimal Models

Traditional Algorithms

such as ARIMAInnovative

Algorithms from

Microsoft Research

Rich and Innovative Algorithms

Algorithms to solve common business problems

Market Basket Analysis Churn Analysis Market Segment Analysis Forecasting Data Exploration Unsupervised Learning Web Site Analysis Campaign Analysis Information Quality Text Analysis


Data Mining AlgorithmsAlgorithm Description

Decision Trees Calculates the odds of an outcome based on values in a training set

Association Rules Helps identify relationships between various elements.

Naïve Bayes Clearly shows the differences in a particular variable for various data elements

Sequence Clustering

Groups or clusters data based on a sequence of previous events

Time Series Analyzes and forecasts time-based data combining the power of ARIMA for long-term prediction and the power of ARTXP (developed by Microsoft Research) for short-term prediction. Together optimizing prediction accuracy

Neural Nets Seeks to uncover non-intuitive relationships in data

Text Mining Analyzes unstructured text data

Linear Regression Determines the relationship between columns in order to predict an outcome

Logistic Regression

Determines the relationship between columns in order to evaluate the probability that a column will contain a specific state


Data Mining Architecture

o Data Mining Structureso Define the data columns used for analysis

o Data Mining Modelso Apply data mining algorithms to data structures


Cross Validation and Algorithm Evaluation


Integrated Predictive Analysis

o Create reports that include prediction

o Build reports using data mining queries as your data source

o Access visual prediction Query Builder directly within Report Designer

o Generate parameter-driven reports based on predictive probability o For example, present high-risk

customerso Probability to churn is over 65%

Native Reporting Integration


Prediction Queries

o Data Mining Prediction Queries can be used in database and ASP.NET applications to return “predictions”

o After a data mining model is created, trained and tested, developers can create DMX queries or SQL Server Stored Procedures to pass variables or a data set to the data mining model via a prediction join to automate return of predictions

Prediction Query Syntax SELECT <select expression list> FROM <data mining model> | <sub select> [NATURAL] PREDICTION JOIN <source data query> [ON <join mapping list>] [WHERE <condition expression>]


Prediction Queries



o Enhance ETL:o Flag anomalous datao Classify business entitieso Identify missing valueso Perform text mining

o Extend SQL Server Integration Services:o Score rows with Data Mining

Query transformationso Train mining models with

Data Mining Training destinations

Automate Data Mining During Data Integration



o Use the OLAP cube for data mining

o Include data mining results as dimensions in OLAP cubes

o Include prediction functions in calculations and KPIs

Insightful Analysis



o Combine predictive and retrospective KPIs for more insightful dashboardso Forecast future performance

against targets to anticipate potential challenges

o Discover and monitor trends in key influencers

Predictive KPIs


Extensible Predictive Analysis

Automatic Data

Mining

• Create a built-in recommendation engine

• Update models based on most recent data

• Warn for flawed data on-the-fly

Pattern Exploration

• Display leading indicators for factors/metrics

• Identify profile for churning/high-value customers

Prediction

• Recommend relevant products

• Anticipate customer risk/churn

• Focus promotions on customers with a high expected life-time value

Predictive Programming

Incorporate predictive analysis into your business

applications through

comprehensive APIs

?


Extensible Predictive Analysis• Add custom data mining algorithmsPlug-in Algorithms

• Redistributable Viewer - embed standard visualizations in your application• Plug-in Viewer APIs - embed custom visualizations in your applicationVisualizations

• Exchange models with other software vendorsPMML

• Industry standard metadataXMLA

• SQL-like query languageData mining Extensions (DMX)

• Access and query models from clients or stored proceduresADOMD.NETand OLE DB

• Management interfacesAMO

Data Mining APIs


Recent enhancements64-Bit Office Data Mining Add-InEnhanced Mining Structures

o Split data into training and testing partitions more effectivelyo Query against structure data to present complete information beyond the scope of the modelo Build models over filtered datao Create incompatible models within the same structureo Use cross-validation to:

o Test multiple models simultaneously o Confirm the stability of results given more or less data

Better Time Series Supporto Accuracy & Stability

o Combine best of both worlds blending ARTXP for optimized near-term predictions and ARIMA for stable long term predictionso Prediction Flexibility

o Build a forecasting model on one series and apply the patterns to data from another series.o What If

o Anticipate the impact of changes in near-term future values, on long-term forecasts

More Data Mining Add-Ins for Officeo New Analysis Tools

o Generate interactive forms for scoring new cases with Prediction Calculatoro Discover the relationship between items, which are frequently purchased together with Shopping Basket Analysis

o New Query and Validation Toolso Choose training and test sets from mining structureso Render richly-formatted cross validation and accuracy reports in Excelo Leverage model documentation for reference and collaboration


• Native Reporting Integration seamlessly infuses prediction into reports• In-Flight Mining during Data Integration dynamically enhances data quality & relevance• Insightful Analysis enables to slice data by the hidden patterns within• Predictive KPIs extend monitoring with insights to future performance

• Predictive Programming embeds prediction within the application• Custom Algorithms & Visualizations provide the flexibility to meet uncommon needs

• Pervasive Delivery through Microsoft Office empowers all users with predictive insight• Comprehensive Development Environment delivers an intuitive and rich environment• Enterprise Grade Capabilities provide enhanced server advantages• Rich and Innovative Algorithms support common business problems effectively

Summary


www.impactanalytix.com

quickly make a positive impact

Want to learn this material? Email [email protected].

predictive analytics with excel and sql server data mining add-ins

Technology

llc o impact analytix

llc data mining

llc impact analytix

excel o

o scenario analysis

o forecast

complex data mining

data mining available