BIN06-IS Understanding the Data Mining Add-Ins for Excel 2007Lynn LangitMSDN Developer Evangelist – Southern Californiahttp://blogs.msdn.com/SoCalDevGal

Session Prerequisites

• Working SQL Server 2005 Developer• Understanding of OLAP concepts• Working SQL Server Analysis Server 2005

Developer• Interest in or basic knowledge of Data Mining

concepts

Session Objectives and Agenda• Understand how to set up a development

environment for working with Excel 2007 Data Mining Extensions

• Understand the core functionality of the Data Mining extensions

• Understand the advanced functionality of the Data Mining extensions.

Predictive AnalyticsPredictive AnalyticsPredictive AnalyticsPredictive Analytics

PresentatioPresentationn

ExplorationExploration DiscoveryDiscovery

PassivePassive

InteractiInteractiveve

ProactivProactivee

Role of SoftwareRole of Software

Business Business InsightInsight

Canned reportingCanned reporting

Ad-hoc reportingAd-hoc reporting

OLAPOLAP

Data miningData mining

What and Why Data Mining?

Data Mining Problems

From Scenarios to Tasks

From Tasks to Techniques

Microsoft’s Predictive Analytics

Data Mining SQL extensionsData Mining SQL extensions(DMX)(DMX)

Application Application DeveloperDeveloper

Data Mining Data Mining SpecialistSpecialist

Microsoft Dynamics CRMMicrosoft Dynamics CRMAnalytics FoundationAnalytics Foundation

SQL Server 2005 SQL Server 2005 Business Intelligence Development StudioBusiness Intelligence Development Studio

Microsoft SQL Server 2005 Analysis ServicesMicrosoft SQL Server 2005 Analysis Services

Information Information WorkerWorker

Data Mining Add-ins for Data Mining Add-ins for the 2007 Microsoft Office systemthe 2007 Microsoft Office system

Microsoft SQL Server 2005 Data MiningMicrosoft SQL Server 2005 Data Mining

BI AnalystBI Analyst

Custom Custom AlgorithmsAlgorithms

Data Mining Add-ins for Office 2007

Table Analysis Tools for Excel 2007Table Analysis Tools for Excel 2007

Data Mining Template for Visio 2007Data Mining Template for Visio 2007

Data Mining Client for Excel 2007Data Mining Client for Excel 2007

SSASSSAS(Data(Data

Mining)Mining)ExcelExcel

SSAS SSAS (DSV)(DSV)QueryQueryExcelExcel

SSISSSISSSASSSASSSRSSSRSExcelExcelYour AppsYour Apps

SSISSSISSSASSSASExcelExcel

Business Business UnderstandiUnderstandi

ngng

Data Data UnderstandiUnderstandi

ngng

Data Data PreparationPreparation

ModelingModeling

EvaluationEvaluation

DeploymentDeployment

DataData

Microsoft Data Mining Lifecycle CRISP-DM

www.crisp-dm.org

Understand & Prepare specifics

Demo 1 – Explore / Clean / Partition Data

Modeling Specifics

Demo 3 – Modeling

Evaluation Specifics

Demo 4 – Evaluation

Configuration

• Model Creation/Management• Database Administrators• Session Mining Models

• Model Application• Permissions on models• Permissions on data sources

Deployment• Browse

• Copy to Excel• Drillthrough

• Query• Default• Advanced

• Excel Services• Manage models and structures

• Export/Import• Rename

• Connection• Database• Trace

Advanced Techniques - DMX

Excel Functions*

DMPREDICTTABLEROW ( Connection, ModelName, PredictionResult, TableRowRange[, string CommaSeparatedColumnNames])

DMPREDICT ( Connection, Model, PredictionResult,

Value1, Name1, [...,Value32, Name32])

DMCONTENTQUERY (Connection, Model, PredictionResult[, WhereClause])

Data Mining Extensions (DMX) CREATE MINING MODELCREATE MINING MODEL CreditRiskCreditRisk

(CustID(CustID LONG KEY, LONG KEY,

Gender TEXT DISCRETE,Gender TEXT DISCRETE,

Income Income LONG LONG CONTINUOUS,CONTINUOUS,

Profession TEXT DISCRETE,Profession TEXT DISCRETE,

RiskRisk TEXT DISCRETE PREDICT) TEXT DISCRETE PREDICT)

USINGUSING Microsoft_Decision_Trees Microsoft_Decision_Trees

INSERT INTOINSERT INTO CreditRisk CreditRisk

(CustId, Gender, Income, (CustId, Gender, Income, Profession, Risk)Profession, Risk)

Select Select

CustomerID, Gender, Income, CustomerID, Gender, Income, Profession,RiskProfession,Risk

From CustomersFrom Customers

SelectSelect NewCustomers.CustomerID, NewCustomers.CustomerID, CreditRisk.Risk, CreditRisk.Risk, PredictProbability(CreditRisk.Risk)PredictProbability(CreditRisk.Risk)

FROMFROM CreditRisk CreditRisk PREDICTION JOINPREDICTION JOIN NewCustomersNewCustomers

ONON CreditRisk.Gender=NewCustomer.GenderCreditRisk.Gender=NewCustomer.Gender

ANDAND CreditRisk.Income=NewCustomer.Income CreditRisk.Income=NewCustomer.Income

AND AND CreditRisk.Profession=NewCustomer.ProfessionCreditRisk.Profession=NewCustomer.Profession

DMX Column Expressions

• Predictable Columns• Source Data Columns• Functions

Predict“Workhorse”Discrete scalar valuesContinuous scalar valuesAssociative nested tablesSequence nested tablesTime SeriesOverloaded to

PredictAssociationPredictSequencePredictTimeSeries

• PredictProbability• PredictSupport• PredictHistogram• Cluster• ClusterProbability• GetNodeId• IsInNode

• Arithmetic operators• Stored Procedure• Subselect

• Select from nested tables

Data Mining Interfaces – XMLA ++

Analysis Server (msmdsrv.exe)

OLAP Data Mining

Server ADOMD.NET

.Net Stored Procedures Microsoft Algorithms Third Party Algorithms

XMLAXMLAOver TCP/IPOver TCP/IP

OLEDB for OLAP/DM ADO/DSO

XMLAXMLAOver HTTPOver HTTP

Any Platform, Any Device

C++ App VB App .Net App

AMO

Any App

ADOMD.NET

WANWAN

DM Interfaces

Summary

• Familiar client for SQL Server Data Mining• Model Creators• Model Users• Excel Data or Server Data

• Implement the full DM Cycle• Data Understanding• Data Preparation• Modeling• Validation• Deployment

Resources

Technical Communities, Webcasts, Blogs, Chats & User Groupshttp://www.microsoft.com/communities/default.mspx

Microsoft Developer Network (MSDN) & TechNet http://microsoft.com/msdn http://microsoft.com/technet

Trial Software and Virtual Labshttp://www.microsoft.com/technet/downloads/trials/default.mspx

Microsoft Learning and Certificationhttp://www.microsoft.com/learning/default.mspx

SQL Server Data Mininghttp://www.sqlserverdatamining.comhttp://www.microsoft.com/bi/bicapabilities/data-mining.aspxhttp://www.microsoft.com/bi/bicapabilities/data-mining.aspx

BI Resources from Lynn Langit

Foundations of SQL Server 2005 Business Intelligencepublished by Apress in April 2007

Blog: http://blogs.msdn.com/SoCalDevGalBlog: http://blogs.msdn.com/SoCalDevGal

Q&A

• BIN302 Microsoft Office Excel and Analysis Services: An In-Depth Look at Integration

• OFF312 Using Data in Excel Solutions Built with Visual Studio Tools for the Office System

Related Content

© 2007 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only.MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.