Manual General de DM

MS SQL Server 2005 Implementando Data Mining

MANUAL DE IMPLEMENTACION DE UNA SOLUCION DE DATA MINING CON MS SQL SERVER

Ing. Ricardo Mendoza Rivera [email protected]. http://rimenri.blogspot.com

MCP. Ing. Ricardo Mendoza Rivera

Page 1



Page 2


TABLA DE CONTENIDO

Sesión

Tema

1

Introduccion al Data Mining

2

Data Mining en SQL Server

3

Recuperando Datos

4

Uniendo Múltiples Tablas

5

Sumarizando Datos

6

Implementando Integridad de Datos

7

Modificando Datos

8

Implementando Vistas

9

Implementado Triggers

10

Implementando Stored Procedures y Funciones Implementando Código Administrado

11


Page 3


Capítulo 01

Introducción al Data Mining


Page 4


Capítulo 1 Introducción al Data Mining

Esta sesión incluye los conceptos iniciales del Data Mining y un resumen metodológico de cómo desarrollarlo. Asi mismo conocer las distintas estructuras y aplicación que pueden darse.

Planificación de la Clase Al finalizar este capítulo, el participante aprenderá a: ; ; ; ; ;

Describir Microsoft SQL Server. Describir como SQL Server toma ventajas de la Plataforma Windows Determinar los requerimientos mínimos de Hardware en SQL Server 2000 de acuerdo a cada Sistema Operativo. Determinar las opciones de instalación apropiadas Configurar el SQL Server Enterprise Manager para trabajar con un servidor y otros servidores.

Panorama Actual "Segmentamos a nuestros clientes usando Data Mining..", "Data Mining incrementa la satisfacción de nuestros clientes..", "Nuestros competidores están usando DM para incrementar su cuota de mercado, necesitamos levantarnos! ..". Son algunos de los comentarios en las organizaciones que se pueden percibir. Pero que es el Data Mining? Como esta tecnología puede resolver los problemas diarios de las organizaciones? Cuál es el ciclo de vida de un DM?

Qué es Data Mining? Data Mining constituye un miembro clave del Business Intelligence (BI) y permite analizar datos, hallando patrones escondidos, de manera automática o semi-automatica. En lo que va del tiempo muchas empresas han acumulado una gran cantidad de datos en sus bases de datos, el resultado de esta colección de datos es que las organizaciones tienen “datos ricos” pero “pobre conocimiento”. El propósito principal del DM es extraer de los datos patrones, incrementar su valor intrínseco y transformar la data en conocimiento. Imagine los datos de una tabla relacional, como se muestran en la fig. 1 conteniendo información de clientes.


Page 5


Tenebrosa Nombre

Edad

Ingresos

Tipo Empleo

Nivel Deuda

Riesgo Crédito

Alex Espejo

<=30

Bajo

Independiente

Alto

Malo

Sócrates B.

<=30

Bajo

Dependiente

Alto

Malo

Rossy Vásquez

31...40 Alto

Dependiente

Alto

Malo

Alejo Pereda

>40

Medio

Dependiente

Bajo

Bueno

Camila Rojas

>40

Medio

Independiente

Bajo

Malo

Kathy León

31...40 Alto

Independiente

Bajo

Bueno

...

...

Dependiente

...

Bueno

...

Fig. 1 Tabla de Clientes Una de las metas a encontrar podría ser: ¿A qué cliente o grupo de clientes le puedo dar un préstamo con un nivel de riesgo Bueno? Podríamos escribir una consulta para buscar cuantos con tipo de Empleo dependiente hay y cuantos no. El impacto de la edad seria otra variable a tener en cuenta o tal vez en función a su nivel de ingresos o deuda que tenía y seguramente tendríamos que escribir miles de consultas cuando queramos combinarlas algunas o todas a la vez, imagine si existiera mas columnas a usar y algunas columnas sean valores numéricos como los ingresos mensuales de un cliente. En contraste el DM hace un acercamiento más simple ha esta pregunta. Todo lo que tiene que hacer es seleccionar el Algoritmo correcto de DM y especificar el uso las columnas a usar, el significado de las columnas de entrada y las columnas predictivas. En el caso anterior las columnas: edad, ingresos, tipo de empleo, nivel de deuda serian las de entrada. La columna Riesgo Crédito seria la columna predictiva. Un modelo de decisión de árbol podría ayudarnos a responder esa preguntar, El algoritmo revisa la data y analiza el impacto de cada atributo ingresado


Page 6


Árboles de Decisión (Decision Trees) All Riesgo Crédito = bueno: 3 Riesgo Crédito = malo: 4

Nivel Deuda = Bajo Riesgo Crédito = bueno: 3 Riesgo Crédito = malo: 1

Tipo Empleo = Independ. Riesgo Crédito = bueno: 1 Riesgo Crédito = malo: 1

Nivel Deuda = Alto Riesgo Crédito = bueno: 0 Riesgo Crédito = malo: 3

Tipo Empleo = Depend. Riesgo Crédito = bueno: 2 Riesgo Crédito = malo: 0

Volvamos a la pregunta original ¿A qué cliente o grupo de clientes le puedo dar un préstamo con un nivel de riesgo Bueno? Se imagina llegar a la respuesta de: los clientes con tipo de empleado Dependiente que tengan un nivel de deuda bajo y que tengan más de 40 años son los que representan menos riesgo de deuda. El DM proporciona un enorme valor a las organizaciones. En estos tiempos el DM puede implementarse con más transparencia:

•

•

•

Gran cantidad de data disponible: las organizaciones llegaron a implementar sistemas transaccionales (ventas, almacenes, producción, personal, contabilidad, etc) y estos en el tiempo han ido almacenando información aunado a la baja de los costos de almacenamiento han acumulado grandes volúmenes de datos. Alto nivel de competencia: la competencia actualmente es alta como resultado de marketing moderno y canales de distribución como internet y comunicaciones, así como la participación de corporaciones nacionales y extranjeras en el mercado. En este 2008 en nuestra ciudad Trujillo estamos siendo testigos de la aparición de 2 malls con una infraestructura bastante atractiva para los clientes, por mencionar un ejemplo de competencia. Tecnología Lista: el DM anteriormente era mayormente una solución de laboratorio, ahora ya es una tecnología madura y está lista para ser aplicada en las organizaciones. Los algoritmos y el equipamiento existente son más eficientes para trabajar con data complicada si fuera el caso. Las API del DM están estandarizándose cada vez mas amplitud y esto permite a los desarrolladores construir aplicaciones

Realidad! Hace poco conversaba con un Gerente de una empresa comercializadora, de gran presencia en el mercado regional y me comentaba entre otras cosas que, sino contara con un sistema de información, no MCP. Ing. Ricardo Mendoza Rivera

Page 7

MS SQL Server 2005 Implementando Data Mining podría estar competido con estas corporaciones – cuenta con gran cantidad de datos y competencia de primera- y que justo había invertido en un servidor con una configuración de primera –Tecnología Lista - . Piense la ventaja de conocer la información que descubriría un DM •

Imagine una organización de retails en donde identifique los grupos de clientes que adquieren ciertos grupos de productos.

•

En un casino de juegos las personas mayores de 55 de género femenino que permanecen 20 minutos “jugando” prefieren ciertos juegos rentables.

•

Conocer que clientes son los que continuamente cambian de operador. En algunos países la inversión de un operador telefónica por cliente representa un promedio de 200 dólares, de ahí la importancia de retenerlo

•

Conocer el perfil de los clientes que constantemente realizan reclamos en una empresa de servicios.

•

Tener la posibilidad de plantear un conjunto de promociones a un determinado grupo de clientes.

•

Disminuir el riesgo de proporcionar un préstamo a un solicitante en una entidad financiera.

Qué datos usa Data Mining? Si su organización cuenta con un Data WareHouse o Data Mart, que es donde mayormente se aplica DM, donde la data ya se encuentra “limpia”. En pequeñas organizaciones es posible que no exista un Data Warehouse por lo que se podría “minar” directamente en las tablas transaccionales. En este sentido se recomienda tener una BD a parte con los datos necesarios y validados. También es posible aplicarlo directamente en un Cubo OLAP, que como vimos en capítulos posteriores es una BD Multidimensional compuesta por Medidas y Dimensiones. En general el DM busca descubrir y evaluar patrones y tendencias con miras a presentar un nuevo conocimiento de la organización.


Page 8


Evaluación de Patrones, Tendencias

Presentación de conocimiento

Data Mining

DATA WAREHOUS

OLTP

OLAP

Que Datos usa Data Mining Ciclo de un Proyecto en DM Seguramente se estarán preguntando cuales son los pasos para construir un proyecto de DM, tomaremos la propuesta de CRISP-DM (www.crisp-dm.org), aquí van!

Fases del modelo de referencia CRISP-DM 1.0

Comprensión del negocio (Business Understanding) •

Está enfocada en la comprensión de los objetivos del proyecto y exigencias desde una perspectiva de negocio, luego convirtiendo este conocimiento de los


Page 9

MS SQL Server 2005 Implementando Data Mining datos en la definición de un problema de minería de datos y en un plan preliminar diseñado para alcanzar los objetivos.

Comprensión de los datos (Data Understanding) •

El entendimiento de datos comienza con la colección de datos inicial y continúa con las actividades que le permiten familiarizar primero con los datos, identificar los problemas de calidad de datos, descubrir los primeros conocimientos en los datos, y/o descubrir subconjuntos interesantes para formar hipótesis en cuanto a la información oculta.

Preparación de datos (Data Preparation) •

Esta fase cubre todas las actividades necesarias para construir el conjunto de datos final -los datos que serán provistos en las herramientas de modelado- de los datos en brutos iniciales. Las tareas de preparación de datos probablemente van a ser realizadas muchas veces y no en cualquier orden prescripto. Las tareas incluyen la selección de tablas, registros, y atributos, así como la transformación y la limpieza de datos para las herramientas que modelan. Normalmente las tareas de ETL de una Data WareHouse podrían servir para tener una data consistente.

Modelado (Modeling) •

En esta fase, varias técnicas de modelado son seleccionadas y aplicadas, y sus parámetros son calibrados a valores óptimos. Típicamente hay varias técnicas para el mismo tipo de problema de minería de datos. Algunas técnicas tienen requerimientos específicos sobre la forma de datos. Por lo tanto, volver a la fase de preparación de datos es a menudo necesario.

Evaluación (Evaluation) •

En esta etapa en el proyecto, usted ha construido un modelo (o modelos) que parece tener la alta calidad de una perspectiva de análisis de datos.

•

Antes del proceder al despliegue final del modelo, es importante evaluar a fondo ello y la revisión de los pasos ejecutados para crearlo, para comparar el modelo correctamente obtenido con los objetivos de negocio. Un objetivo clave es determinar si hay alguna cuestión importante de negocio que no ha sido suficientemente considerada. En el final de esta fase, una decisión en el uso de los resultados de minería de datos debería ser obtenida.

Implementar (Deployment) MCP. Ing. Ricardo Mendoza Rivera

Page 10

MS SQL Server 2005 Implementando Data Mining •

La creación del modelo no es generalmente el final del proyecto. Incluso si el objetivo del modelo es de aumentar el conocimiento de los datos, el conocimiento ganado tendrá que ser organizado y presentado en el modo en el que el cliente pueda usarlo. Ello a menudo implica la aplicación de modelos "vivos" dentro de un proceso de toma de decisiones de una organización, por ejemplo, en tiempo real la personalización de página Web o la repetida obtención de bases de datos de mercadeo. Dependiendo de los requerimientos, la fase de desarrollo puede ser tan simple como la generación de un informe o tan compleja como la realización repetida de un proceso cruzado de minería de datos a través de la empresa. En muchos casos, es el cliente, no el analista de datos, quien lleva el paso de desarrollo. Sin embargo, incluso si el analista realizara el esfuerzo de despliegue, esto es importante para el cliente para entender de frente que acciones necesita para ser ejecutadas en orden para hacer uso de los modelos creados actualmente.


Page 11


Capítulo 02

Data Mining con SQL Server


Page 12


Capítulo 1 Data Mining con SQL Server

Esta sesión incluye los conceptos de Data Mining asociados al SQL Server, los diferentes modelos que soporta y los pasos recomendados para su implementación.

Planificación de la Clase Al finalizar este capítulo, el participante aprenderá a: ; ;

Describir los modelos de Data Mining Modelos Soportados en Microsoft SQL Server.

Tareas y Algoritmos La “magia” que genera el Data Mining se origina a partir de 3 campos: • Modelos Estadísticos (Aquellos diseñados para hallar correlación de datos como : Naive Bayes y Clustering • Maquina de Aprendizaje (Diseñados para hallar campos arboles de decisión y redes neuronales) • Teoría de Base de Datos (procesar largos volúmenes de datos) DM puede ser aplicado en diferentes tareas, las mas populares son: asociación, clasificación, segmentación, regresión y pronostico. Para estas tareas se usa el OLE DB para DM (OLEDB/DM).

Algoritmos Tarea Clasificación

Arboles De Decisión X

Clustering

(Segmentación)

Asociación

X

Naive Bayes

Sequence Clustering

Redes Neuronales

X

X

X

X

X

X

Segmentación Asociación

X

Regresión

X

Series de Tiempo

X X

X X

Pronóstico Tabla 2.1 Tareas y Algoritmos

Estructuras en Data Mining Podriamos describir de una manera simple que DM es una colección de columnas de tablas y algoritmos de minado que actúan sobre los datos contenidos en esas columnas para analizarlos y ejecutar predicciones usando diferentes algoritmos. MCP. Ing. Ricardo Mendoza Rivera

Page 13

MS SQL Server 2005 Implementando Data Mining Existen ciertas tareas que pueden ser desarrolladas usando los mismos datos con diferentes algoritmos.

Contenido de una Columna: Valores Discretos y Valores Continuos Es necesario de definir el contenido de una columna mediante la propiedad Content. Los valores mas comunes son Discreto y Continuo. Un ejemplo de un valor Discreto lo constituye el genero de un cliente, ya que contiene pocos valores distintos como son: Masculino y Femenino. UN ejemplo de valor Continuo podría ser el Nivel de Ingresos de un cliente ya que este datos podría contener valores arbitrarios. Hay que tener en cuenta que algunos algoritmos no soportan los dos tipos de valores.

Diseñando el Proceso de Data Mining en SQL Server Los pasos descritos a continuación se han tomado como base de la propuesta de CRISP-DM adaptados al SQL Server. El cual presentamos a continuación:

Paso 1: Definir el Dominio de Minado Iniciar su proyecto de DM obteniendo los requerimientos del negocio, apuntando a tener un objetivo claro de lo que se desea realizar, podemos mencionar: • Pronósticos de ventas? • Personalización de clientes? • Análisis de ventas? Ejemplo: “Ejecutar un análisis de DM para identificar clientes que demandan determinados productos basado en su ubicación geográfica para una campana publicitaria vía email” Una vez definido el objetivo determinar la disponibilidad de la data en estudio que soportarax el modelo. Para ello puede formularse las siguientes preguntas: •

Que datos de entrada se necesitan? Ejemplo: Genero, ingresos, edad del cliente


Page 14

MS SQL Server 2005 Implementando Data Mining • •

Donde se encuentra la data necesaria? Como puede ser extraída? Recuerde que dentro de los Servicios de Analisis se puede incluir orígenes de BD relacionales o de un cubo UDM.

Paso 2: Preparar Data Es posible que la data no se encuentre directamente disponible y requiera del proceso de ETL y cargar la data en el formato deseado. Si este fuera el escenario considere a Integration Services El resultado de este proceso es un esquema de datos relación o un UDM del cubo. Deberá estar seguro de la semántica (significado) de cada columna y su propósito en el negocio. Mientras más conozca sus datos más efectiva será la predicción.

Paso 3: Construir el Esquema de Datos Es necesario construir un Vista de la Conexión al margen que provengan los datos de una BD Relacional o de un UDM. El resultado final de este paso es la definición de la estructura del DM la que servirá de fundamento para el modelo de DM a implementar.

Paso 4: Construir un Modelo Empezar por la tabla 2.1 para identificar uno o mas algoritmos que pueden ser usados para ejecutar la tarea de DM. Luego usar el Diseñador BI Studio Data MIning para implementar el modelo. Por ejemplo si desea promocionar productos por email el escenario corresponde a la tarea de Clasificación y el algoritmo a implementar es Arboles de Decisión, Clustering, Sequence Clustering, Naive Bayes y Redes Neuronales. Se recomienda probar con más de uno y determinar el algoritmo que más se adapta.

Paso 5: Explorar el Modelo Una vez que el modelo es construido y entrenado, se encuentra listo para se explorado y analizado a fin de obtener los resultados predictivos deseados. Los Servicios de Análisis proporcionan resultados gráficos y de diferentes vistas como podrá a apreciar.

Paso 6: Validar Modelo Deberá validar la exactitud del modelo que ha construido. Esto es especialmente importante probar con más de un algoritmo la tarea elegida. Se recomienda trabajar con una data pequeña como muestra y evaluar.

Paso 7: Implementar Modelo Una vez ejecutado el paso anterior ahora necesita configurar e implementar el modelo en su servidor de producción. Deberá de configurar accesos a las cuentas del Windows. Una vez implementado el sistema se pueden usar una serie de alternativas para generar reportes para usuarios finales.

CASO DE ESTUDIO. Imagine que está introduciendo un nuevo producto y el area de Marketing se encuentra planificando una campana para el lanzamiento. Se puede deducir que estas campañas son MCP. Ing. Ricardo Mendoza Rivera

Page 15

MS SQL Server 2005 Implementando Data Mining costosas ya que se necesita diseñar, imprimir y entregar correspondencia a sus clientes. Realizar publicidad por TV o Radio es normalmente mas costoso. El aplicar DM podría permitir identificar grupos de clientes y direccionar el marketing, por ejemplo a público adulto.

Paso 1: Definiendo el Dominio de Minado LA empresa en estudio administra alrededor de 15,000 clientes y se debe identificar que grupos de clientes podrían demandar este nuevo producto. El dominio del DM a resolver es el ejemplo clásico de una tarea de clasificación. De acuerdo a la tabla 7.2 existen varios algoritmos, elegiremos un mínimo de dos para identificar el más apropiado.

Paso 2: Preparar Data Nuestro enfoque principal es una Tarea de Campaña orientada al cliente, por lo que necesitaremos los datos de la tabla cliente.

Lastimosamente no existen reglas para determinar que columnas son las que deben participar, pero una buena forma es crear una vista de la conexión (DSV) con los potenciales datos involucrados y usar las opciones de Pivoteo del Analysis Services

Recuerde que el objetivo es identificar clientes potenciales que adquirieron algun producto de MCP. Ing. Ricardo Mendoza Rivera

Page 16

MS SQL Server 2005 Implementando Data Mining la categoría a la que pertenece el producto a ofertar. Para ello usaremos estadísticas de ventas efectuadas.

Paso 3: Construyendo el Esquema de Datos A continuación presentamos los datos a partir de los cuales se construirán los modelos indicado:

SELECT C.CustomerKey, C.FirstName + ' ' + ISNULL(C.MiddleName + '. ', '') + C.LastName AS FullName, C.MaritalStatus, C.Gender, C.YearlyIncome, C.TotalChildren, C.EnglishEducation, C.NumberCarsOwned, C.CommuteDistance, C.EnglishOccupation, C.HouseOwnerFlag, DATEDIFF(yy, C.BirthDate, GETDATE()) AS Age, CustomerFilter.Subcategory AS ProductCategory FROM DimCustomer AS C INNER JOIN (SELECT C.CustomerKey, PS.EnglishProductSubcategoryName AS Subcategory FROM DimCustomer AS C INNER JOIN FactInternetSales AS S ON C.CustomerKey = S.CustomerKey INNER JOIN DimProduct AS P ON S.ProductKey = P.ProductKey INNER JOIN DimProductSubcategory AS PS ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey WHERE (PS.ProductCategoryKey = 1) GROUP BY C.CustomerKey, PS.EnglishProductSubcategoryName HAVING (COUNT(PS.ProductSubcategoryKey) = 1)) AS CustomerFilter ON C.CustomerKey = CustomerFilter.CustomerKey

Paso 4: Construyendo el Modelo Empecemos con la construcción del Modelo: • Clic derecho sobre : Estructuras de Mineria de Datos

• •

Clic sgte En esta interfaz


Page 17


•

Clic en siguiente ya que nuestra información se leerá directamente desde una tabla relacional. Técnica de Minería de Datos a aplicar:

Como estamos analizando una Campana de Promocion de un Producto elegiremos una tarea de Clasificacion con el Algoritmo de Microsoft Decision Tree. •

Vista de origen de datos: seleccione Bike Buyers


Page 18


•

Clic Siguiente El tipo de Tabla a seleccionar es :Escenario

•

b Clic next Especificar los datos de entrenamiento: es el momento de definir los datos de entrada y la columna predictiva, recuerde que necesitamos predecir que grupos de clientes adquirirían el producto promocionado. Seleccionar CategoriaProducto como columna Entrada y Columna Predictiva.


Page 19


Luego proceda a marcar las entradas manualmente, pero podría pulsar el botón Sugerir.

•

Seleccione de acuerdo a la propuesta:


Page 20


•

Especificar el contenido y tipo de columnas

•

Clic Siguiente Paso Final


Page 21


•

Finalizar!

Conociendo el Data Mining Designer

Imagine que se quiera tener unicidad en el conjunto de datos de cliente obtenido, donde es posible que un cliente pueda poseer mas de 1 producto. Incluiremos a la clave la categoría del producto de la siguiente forma: • • •

Selección: CustomerKey En propiedades, elija la propiedad: KeyColumns y clic en el botón … Agregar la columna: productCategory para crear una llave compuesta por: CustomerKey y ProductCategory

•

Imagine que desea ver el Nombre del cliente en vez de su código. En la propiedad: NameColumn elija: FullName


Page 22


Fig 10. Modificando la Key

Fig 11. Enlazando un

Visualizando el Modelo de Minería de Datos


Page 23


Agregando un Nuevo Modelo de DM (Naive Bayes) •

Haga clic derecho: Nuevo Modelo , digite y elija:

•

Si apareciera algún mensaje es porque Naive Bayes solo trabaja con valores discretos.

Creando Valores Discretos •

• • •

En el caso de la edad , Nivel de Ingresos es posible que se desee trabajar como valor discreto ya que para el análisis el nivel de ingresos podría influir en la compra del producto ofertado. Proceder de esta forma o Volver a la ficha: Estructura de DM o Ubicarse sobre la lista de campos, clic derecho : Agregar una nueva columna. Seleccione: Yearly Income Renombre la columna : YearlyIncome 1 como : YearlyIncome Discreto, y click en Yes Cambie la propiedad content a: Discretized Cambie la propiedad: DiscretizationMetohd: Automatic . Luego de ello el servidor agrupará estos cambios en 5 categorias.

•

•

Vaya a la ficha Modelos de Mineria y cambie : YearlyIncome Discreto a: Input.


Page 24


Implementando el Modelo •

Clic en el botón Procesar de la barra de herramientas

•

Paso 5: Explorando el Modelo Haga clic en la ficha: Visor de Modelos de Minería

Interpretando los resultados Como se puede apreciar tenemos una sola variable predictiva la cual constituye la raíz del grafico. Por defecto el visor muestra todas las categorías de productos. MCP. Ing. Ricardo Mendoza Rivera

Page 25

MS SQL Server 2005 Implementando Data Mining Comprendiendo el Grafico de Arboles de Decisión El modelo ha determinado que la variable mas significativa son los Ingresos Anuales (Yearly Income). Mientras más oscuro sea el recuadro implica que la mayoría de casos sucede en el mismo. Comprendiendo la Dependencia de Redes

Interpretando Naive Bayes


Page 26


Paso 6: Validando el Modelo Esto lo haremos en la ficha: Grafico de Precisión de Minería de Datos, para evaluar siga los siguientes pasos:

Especificando las columnas a mapear • Clic en Grafico de Precision de Mineria de Datos • Clic en: Seleccionar Tabla de Escenarios y seleccione de la tabla de clientes


Page 27


•

Se produce el mapeo automáticamente.

•

Mapear el YearlyIncome Discreto con YearlyIncome

Obteniendo los resultados a predecir • Clic en Predicción de Modelos de DM

•

Clic en Seleccionar Modelo y elija:


Page 28


•

Seleccione Tabla Escenarios y elija Customers

•

Seleccione las columnas que desea ver como parte de la predicción o CustomerKey hacia el panel inferior:

o o

Luego Full Name Incluir la función de Prediccion


Page 29

MS SQL Server 2005 Implementando Data Mining o

Incluir función personalizada de acuerdo a lo siguiente

Viendo Resultados a predecir Estos son los clientes

q


Page 30

Manual General de DM

Recommend Documents