Caso de estudio de inteligencia de negocios aplicado al negocio de venta de muebles

Texto completo

(1)UNIVERSIDAD DE LOS ANDES FACULTAD DE INGENERIA DE SISTEMAS. Caso de estudio de Inteligencia de Negocios aplicado al negocio de venta de muebles. ASESOR. José Eusebio Abásolo Prieto. AUTOR. Nicolas Maffiold Salas. 11 de Enero del 2011. 1.

(2) Table of Contents 1.. RESUMEN..................................................................................................................................... 3. 2.. INTRODUCCIÓN ......................................................................................................................... 3 2.1. PROCESO ............................................................................................................................................... 3. 3.. DESCRIPCIÓN GENERAL ......................................................................................................... 5 3.1. OBJETIVOS ............................................................................................................................................. 5 3.1.1. OBJETIVO PRINCIPAL ....................................................................................................................... 5 3.1.2. Objetivos específicos ..................................................................................................................... 5 3.2. ANTECEDENTES ................................................................................................................................... 5 3.3. CONTEXTO DEL PROBLEMA ................................................................................................................ 6 3.4. JUSTIFICACIÓN ...................................................................................................................................... 6. 4.. DISEÑO Y ESPECIFICACIÓN ................................................................................................... 7 4.1. DEFINICIÓN DEL PROBLEMA .............................................................................................................. 7 4.2. ESPECIFICACIONES .............................................................................................................................. 7 4.3. RESTRICCIONES .................................................................................................................................... 7. 5.. DESARROLLO DEL DISEÑO .................................................................................................... 8 5.1. RECOLECCIÓN DE INFORMACIÓN....................................................................................................... 8 5.2. ALTERNATIVAS DE DISEÑO ................................................................................................................ 8. 6.. IMPLEMENTACIÓN ................................................................................................................... 9 6.1. DESCRIPCIÓN DE LA IMPLEMENTACIÓN ........................................................................................... 9 6.2. ETAPAS .................................................................................................................................................. 9 6.2.1. Preparación ...................................................................................................................................... 9 6.2.1.2. Empresa ........................................................................................................................................10 6.2.1.3. Entrevistas...................................................................................................................................13 6.2.2. Proyecto 1........................................................................................................................................18 6.2.3. Proyecto 2........................................................................................................................................28 6.2.4. Proyecto 3........................................................................................................................................29 6.2.5. Proyecto 4........................................................................................................................................34 6.2.6. Proyecto 5........................................................................................................................................43 6.2.7. Proyecto 6........................................................................................................................................46 6.2.8. Proyecto 7........................................................................................................................................50 6.3. RESULTADOS ESPERADOS ............................................................................................................... 51. 7.. VALIDACIÓN .............................................................................................................................51 7.1. MÉTODOS ........................................................................................................................................... 51 7.2. VALIDACIÓN DE RESULTADOS......................................................................................................... 51. 8.. CONCLUSIONES........................................................................................................................51 8.1. DISCUSIÓN ......................................................................................................................................... 51 8.2. TRABAJO FUTURO ............................................................................................................................. 52. 9.. ÍNDICES ......................................................................................................................................53. 10.. BIBLIOGRAFÍA ......................................................................................................................54. 2.

(3) 1. RESUMEN En este trabajo se desarrolla una caso de estudio para la clase de Inteligencia de Negocios (BI) para el caso de ventas la por mayor de muebles. Utilizando como referencia el caso de estudios que se desarrollo en la clase a lo largo de este semestre se plantea un caso que consta de 7 proyectos independientes para que el estudiante desarrolle conocimientos básicos sobre BI, sus ventajas y posibles aplicaciones. Se utiliza para este caso la empresa de muebles ficticia MDLA a la cual se le crea una base de datos conteniendo información de las diversas actividades de esta empresa para su análisis a lo largo de los proyectos. Cada uno de estos proyectos tiene su enunciado al igual que todas herramientasnecesarias para susolución, también se realiza una propuesta de solución para cada uno de los proyectos y su respectiva plantilla de calificación.. 2. Introducción La Inteligencia de Negocios “Es un termino genérico que describe el apalancamiento de una de los activos de información tanto interna como o externa para realizarmejores decisionesde negocio”(Kimball R. a., The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, 2002) en términos generales se refiere a utilizar la información para tomar mejores decisiones de negocio. BI se refiere a las capas del nivel de acceso entre lo usuarios y una bodega de datos, la cual consiste en una base de datos específicamente para mejorar el redimiendo de los análisis y querys sobre la información al igual que su facilidad de uso, e implementación de minería de datos proceso que se a popularizado, ya que permite encontrar patrones inesperados en los datos. No permite clasificar, estimar, predecir y encontrar eventos que ocurren simultáneamente por esto la minería de datos se ha convertido en un área bastante desarrollada y de continua evolución en las últimas décadas, de tal manera que sea ha integrado conocimiento de otras áreas, tales como: la estadística, la computación gráfica, la inteligencia artificial, la biología, entre otras. Beneficiándose una de la otra. Aunque una aplicación de BI no requiere de una bodega de datos necesariamente una bodega de datos agrega tal valor a la información de una organización que es conveniente utilizarla para construir la aplicación BI que empezar desde cero par realizar el mismo trabajo de la bodega la cual probablemente sea mas eficiente en ello.. 2.1. Proceso. 3.

(4) Este trabajo se desarrollara guiándose con la metodología propuesta por Kimball(R. Kimball, 2006) para el desarrollo de aplicaciones de inteligencia de negocios y bodegas de datos. Se realizaron 7 proyectos en los cuales se comienza conociendo la empresa por medio de documentos y entrevistas que describen su organización, que departamentos existen, cual es su negocio, competencia, procesos de negocio existentes, al igual que conocer y caracterizar la base datos de esta. Se analiza en detalle esta información y con ella se levantan los requerimientos del proyecto , las necesidades . es decir sus temas analíticos los cuales se comparan con sus procesos de negocio para realizar una priorización de estos para decidir por cual comenzar la implementación. Con el proceso seleccionado de debe realizar el Modelado Multidimensional. El Modelado Multidimensional consiste en una “técnica de diseño lógico para estructurar la información para que sea intuitiva para le usuario del negocio y proporcione un alto desempeño y velocidad de los Query” (Kimball R. a., The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, 2002) es la técnica mas popular al momento de implementar bodegas de datos ya que se basa en presentar lo mas simple posible la información , lo que es crucial para el desempeño de la herramientas de BI y facilita que los usuarios entiendan la base de datos. El modelado multidimensional divide al mundo en 2 Hechos las cuales son generalmente números, y se rodean de un contexto que es verdadero en el momento que se registrar el hecho y este se conoce como dimensiones las cuales describen el quien, que, cuando, donde, porque, y como del hecho Una vez se tiene este modelo implementado se pasa a diseñar el proceso de extraer y transformar la información para poder cargarla en la base de datos dimensional esto se conoce como ETL. Con la información cargada se diseño a un cubo de análisis esta estructura permite obtener la información fácil y rápidamente usando consultas en MDX y demás Como herramienta principal se usara la suite de Microsoft SQL Server y las herramientas que proporciona. 4.

(5) 3. Descripción General 3.1. Objetivos 3.1.1. Objetivo principal Crear un caso de estudio que sirva de apoyo la para la clase de Inteligencia de Negocios, facilitando que el estudiante comprenda los conceptos básicos, aprenda a utilizar las herramientas disponibles, los lenguajes de programación yse familiarice con el proceso que requiere implementar una aplicación de BI. 3.1.2. Objetivos específicos. Cada uno de los proyectos que se construyeron tiene un objetivo específico que se enumerara por proyecto a continuación: 1. Conocer a fondo el caso MDLA, cual es su negocio y como se encuentra estructurada esta empresa que será utilizada como caso de estudio para el desarrollo de los proyectos del curso. 2. Realizar un levantamiento de requerimientos para un proyecto de BI. 3. Realizar un ejercicio de modelado Multidimensional bajo la metodología Kimball (R. Kimball, 2006) 4. 4.1. Realizar un ejercicio de diseño físico de Bodegas de Datos en una plataforma específica. 4.2. Diseñar e implementar el proceso de ETL en un contexto restringido 5. 5.1. Trabajar con una base de datos dimensional. 5.2. Familiarizarse con los conceptos básicos de MDX. 6. Trabajar con KPI y acciones como instrumentos de análisis para satisfacer requerimientos de negocio 7. Usando los diferentes instrumentos de análisis que ofrece la inteligencia de negocios -Scorecards, Análisis OLAP, Tareas de Minería, Reportes – escoger los más adecuados para satisfacer los requerimientos identificados en las entrevistas realizadas a los usuarios del negocio.. 3.2. Antecedentes El curso de Inteligencia de Negocios es considerablemente reciente en la Universidad de Los Andes. Este proyecto se basa en el desarrollado a lo largo de segundo semestre del 2010por estudiantes de esta materia el cual se centraba alrededor de el ejemplo proporcionado por Microsoft de AdventureWorks(Andes, 2010) una empresa que se 5.

(6) centra en la manufactura y distribución de bicicletas y accesorios alrededor de varios países. En este proyecto en especifico se realizo una implementación sobre el proceso manufactura. Existe además el libro The Microsoft Data Warehouse Toolkit(R. Kimball, 2006) el cual sirvió también de guía para le proyecto de Inteligencia de Negocios . en el libro se aplica la metodología Kimball (diseñada por los autores ) sobre AdventureWorks para el caso de ordenes de trabajo documentando todo el proceso y utilizando la misma herramienta SQL Server. 3.3. Contexto del problema Como se menciono anteriormente el curso de Inteligencia de Negocios en la Universidad de los Andes es relativamente nuevo y todavía se esta terminando de establecer además los proyectos de BI requieren de una gran cantidad de datos disponibles y estos son difíciles de conseguir o crear, serequiere un contexto de una empresa con una estructura bien definida, con procesos de negocio definidos y objetivos claros. En al metodología Kimball los requerimientos se levantan por medio de entrevistas a los diferente cargos no solo a las cabezas de departamento sino a diversos empleados este proceso dentro de la metodología fácilmente puede tomar 6 meses que no es viable , entonces se necesita diseñar entrevistas pre-hechas como las que proporcionan en (R. Kimball, 2006) para AdventureWorks y solo des pues de tener todo esto definido y analizado se puede empezar a trabajar en forma en el proyecto , todo esto dificulta el desarrollo de casos de estudio para un curso de ese estilo.. 3.4. Justificación Dado lo complicado de realizar un proyecto de BI por su gran escala y lo mencionando anteriormente se ve la necesidad de crear un caso de estudio creando la base de datos que se usara de referencia al igual, diseñando tanto la empresa en todos sus aspectos como las diferentes entrevistas que se requieren para entender el contexto sobre el cual trabajar.. 6.

(7) 4. Diseño y especificación 4.1. Definición del problema Se desea satisfacer la necesidad de crear un caso de estudio para Inteligencia de Negocio dado la complejidad de realizar un proyecto de BI creando todo el contexto necesario para poder realizar un proyecto de inteligencia de negocios .. 4.2. Especificaciones Para poder crear el contexto necesario para realizar un proyecto de BI se debe primero diseñar o conseguir una empresa lo suficientemente grande para tener sus procesos de negocio estipulados, una jerarquía establecida, conocer que venden o producen por que a quienes y como.Debe tener la necesidad de implementar un proyecto de BI ya que para una empresa demasiado pequeña no es recomendable ya que además de los grandes costos no le generaría mucho beneficio al no tener una infraestructura de información tan robusta Crear/obtener un base de datos robusta es decir con una cantidad significativa de registros, que proporcione información relativamente concisa (algunas inconsistencias se puede limpiar en el proceso de ETL) y con un nivel de complejidad lo suficientemente grande para describir los proceso de negocios de la empresa Se deben realizar/diseñar las entrevistas que ayuden a proporcionar los temas analíticos que se van a tratar y realizar la priorización de los mismos, por esta razón deben ser varias por lo menos una de cada uno de los “Departamentos” que existan planeando una necesidad del mismo. 4.3. Restricciones Se debe escoger que herramienta se utilizará y que metodología que en este caso seráMicrosoft SQL Server y metodología Kimball. El caso no puede ser demasiado complejo ya que debe realizarse durante el tiempo del semestre pero tampoco puede ser demasiado simple ya que no facilitaría el comprender lo utilidad del un proyecto de BI . se debe limitar el caso a desarrollar un solo proceso analítico para que el tiempo alcance y se debe facilitarle ayudas en cada uno de los proyectos individuales. La calidad de los datos se debe tener en cuenta ya. 7.

(8) que una base de datos muy inconsistente requiere mas trabajo y tiempo del que un estudiante puede dedicarle durante el semestre.. 5. Desarrollo del diseño 5.1. Recolección de información La información de referencias para este caso se obtuvo de diversos libros de la metodología Kimball The Microsoft Data Warehouse Toolkit(R. Kimball, 2006)The Data Warehouse Lifecycle Toolkit(Kimball R. a., 2008)The Data Warehouse Toolkit(Kimball R. a., The Data Warehouse Toolkit, 2009)The Data Warehouse ETL Toolkit,(Kimball R. a., 2004)y del proyecto realizado en Inteligencia de negocios sobre AdventureWorks(Andes, 2010) (Microsoft, 2009) al igual que la pagina de Microsoft sobre AdventureWorks La complejidad de conseguir una base de datos pertinente para un proyecto de BI ya que las empresa no liberan esta información fácilmente conllevo que para este caso de estudio se modificara la base de datos de AdventureWorks para que se ajustar al caso de venta y mano factura esto requirió pequeños cambios ya que el modelo propuesto en AdventureWorksse pude ajusta a cualquier otro caso de retail(venta y manufactura) y se podría utilizar la ya robusta información que proporcionan.. 5.2. Alternativas de diseño Para le caso se podría o encontrar una empresa que estuviera dispuesta o diseñar una ya que no se logro encontrar una empresa que proporcionara toda la información necesaria para realizar un proyecto de BI se opta por diseñarla , en el diseño se pude o tomar algún ejemplo ya existente como por ejemplo AdventureWorks y modificarlo o al igual comenzar desde cero pero esto presupone el problema de crear una base de datos robusta y coherente. Por estas razones se decide ajustar el modelo de AdventureWorks para tener una base de datos optima y por ende diseñar una empresa de retail ficticia para que se ajuste al modelo planteado en AdventureWorks con el menor numero de cambios posible.. 8.

(9) 6. Implementación 6.1. Descripción de la implementación Para la implementación de este caso se decidió utilizar la base de AdventureWorks modificada para que se ajuste a el negocio de muebles ya que se usara como referencia Muebles Los Alpes S.A (MDLA) un caso para arquitectura de software en La Universidad de los Andes dado que proporciona una información detallada del negocio de muebles y contiene una estructura detalla de las empresas de esta índole. También se utilizaron como referencia las entrevistas planteadas en (R. Kimball, 2006) para crear unas entrevistas que se aplicaran en este caso. El Caso se desarrollo en 8 etapas 1 de preparación general y una porcada uno de los proyecto para el caso cada una de las cuales se divide a su vez en enunciado y solución.. 6.2. Etapas 6.2.1. Preparación 6.2.1.1. Negocio El negocio de los muebles se basa mucho en la imagen de los muebles por es importante poder describir claramente los productos que se venden. Los inventarios no tiene mucha rotación por lo cual las promociones son bastante importantes para moverlos . Clasificación Muebles o Muebles  Hogar Alcobas Salas Comedores Auxiliares  . Oficina Acesorios Mesa Cocina Organizadores Decoracion Iluminacion Lenceria Cama Baños 9.

(10) 6.2.1.2.. Empresa. Muebles Los Alpes S.A (MDLA) es una empresa que se dedica a la producción y comercialización de muebles para oficina y hogar. MDLA lleva 15 años de operación y cuenta con una gran reputación entre sus clientes, que la reconocen como una empresa que se preocupa por la calidad de sus productos, la innovación en estilos y en un gusto muy moderno y fresco, manteniéndose siempre en una posición vanguardista en la creación de diseños y en el uso de los materiales. La empresa cuenta con varias zonas de distribución en Colombia y puntos de fábrica ubicados en diferentes países en Latinoamérica, Europa y Norteamérica . MDLA planea expandirse al mercado internacional y para esto requiere mejorar su proceso de producción para así reducir sus costos y utilizar de lleno el canal de ventas web que tiene a su disposición. MDLA maneja 3 canales de venta principalmente Call center, Portal WEB,POS (Point Of Sale). 10.

(11) Figure 1 Organigrama MDLA. La planeación estratégica de la organización se ejecuta bajo un proceso evolutivo en el cual participan todos los directores de área, el gerente y la junta directiva. Los informes de resultados de cada área hacen parte de la materia prima requerida para ejecutar la labor de planeación estratégica. La dirección de compras se encarga de analizar cuáles son las necesidades de compras de la empresa, definir las políticas de compras (mercado, proveedores, etc.), planear y administrar el presupuesto de compras, generar informes y elaborar previsiones mensuales de compras. Adicionalmente, se encarga de gestionar los proveedores de la empresa, mediante la identificación de proveedores potenciales, la negociación de contratación con estos proveedores y el seguimiento de dichos contratos. La dirección administrativa se encarga de gestionar la contabilidad de la empresa, administrar las facturas, los cobros y los pagos asociados con los proveedores o los clientes, como también la nomina de la empresa y las bonificaciones entregadas a los representantes de ventas. Adicionalmente, se encarga de consolidar las necesidades financieras de todas las áreas y de definir los planes necesarios para atenderlas, como también de generar los informes pertinentes para conocer los estados financieros de la organización.. 11.

(12) Adicionalmente, se encarga de administrar la línea de crédito directo que ofrece la compañía a sus clientes. La coordinación de tecnología depende de la dirección administrativa y se encarga de administrar la plataforma tecnológica de la compañía, dar soporte técnico a los equipos y a la aplicaciones y cumplir los lineamientos en materia de tecnología definidos por la alta gerencia. La dirección técnica se encarga de definir los parámetros de calidad exigidos para los productos que entran y salen de la empresa y diseñar y ejecutar los procesos orientados a verificar que éstos se cumplan. Adicionalmente, se encarga de definir las necesidades de materiales y el proceso a seguir (tomar decisiones de subcontratación, realizar planes de fabricación, etc.) en la producción de nuevas colecciones impulsadas por la dirección de desarrollo de producto, como también definir los costos asociados a esta producción. La dirección de desarrollo de producto se encarga de generar nuevas colecciones a partir del análisis de tendencias del sector, contrastando las necesidades de los clientes con los catálogos actuales y generando bocetos que posteriormente serán evaluados por un grupo con representación de la junta directiva, la dirección comercial y la dirección técnica. Si los bocetos son aprobados, se encarga de guiar el proceso de producción de los prototipos, que también serán evaluados posteriormente por el grupo anterior. Si finalmente la colección ha sido aprobada, se encarga de gestionar la creación de los catálogos de la nueva colección. Adicionalmente, sobre colecciones ya establecidas, propone nuevos acabados, con el fin de evolucionar los productos actuales. La dirección comercial es responsable de diseñar y monitorear el plan de presupuesto y de acción de ventas, a partir del análisis de evolución de las facturas y las necesidades de los diferentes segmentos de clientes que tiene la compañía, como también de elaborar previsiones de ventas mensuales que podrían llegar a modificar el plan inicial. De igual forma, se encarga de ejecutar el plan de ventas a través de los canales establecidos (actualmente solo las salas de exhibición están habilitadas como canales de venta directas). Adicionalmente, se encarga de definir y monitorear las campañas de mercadeo, identificando el valor real generado a partir de cada una estas y de gestionar la relación con los representantes comerciales, contrastando sus resultados con los objetivos trazados por estos. Finalmente, la dirección comercial es la responsable de gestionar la relación de la compañía con sus clientes (personas naturales y PYMES), atendiendo sus inquietudes y requerimientos y asegurando que los pedidos hechos por estos se cumplan con las calidades y tiempos establecidos y permitiendo que estos puedan ser modificados o cancelados de acuerdo a un conjunto de reglas preestablecidas. La dirección de producción, como su nombre lo indica está encargada de dirigir todo el proceso de producción de las colecciones diseñadas por la dirección de 12.

(13) desarrollo de producto y validadas por el grupo interdisciplinario mencionado anteriormente. De igual forma produce los prototipos solicitados para las potenciales colecciones y diseña los planes de producción, con el fin de aprovechar al máximo los recursos de planta de la compañía. La dirección de recursos humanos es la encargada de consolidar las necesidades de recursos humanos de las demás áreas, definir los roles con sus responsabilidades y tareas que atenderán estas necesidades, validar y evolucionar la estructura organizacional, siempre que sea necesario, realizar las actividades de reclutamiento de nuevo personal y programar las capacitaciones requeridas por las demás áreas. Adicionalmente, se encarga de administrar el tema contractual con los empleados, incluyendo los aspectos relacionados con la nomina.. 6.2.1.3.. Entrevistas. Gerente General MDLA Planea mejorar su participación en los diferentes países en los que se encuentra, y para esto requiere nuevas tecnologías para soportar sus diversas operaciones, por esta razón la Inteligencia de negocios parece una buena opción a pesar de esto no se encuentra seguro si sea la mejor opción para MDLA dado que la compañía tiene múltiples aspectos que podría mejorar.. Satisfacción del cliente EL GERENTE GENERAL quiere asegurarse que sus clientes estén satisfechos, para esto desea monitorear a sus mayores clientes, en especifico le interesaría ver como esta MDLA en relación a su principal competencia y que puede hacer para mejorar su línea de productos. En la actualidad no se cuentan con muchas métricas en la información. Cuando entra una nueva orden se le otorga una fecha estimada de entrega, por esto no seria difícil calcular la diferencia entre este estimado y la verdadera fecha de envió. también esta interesado en saber cuales productos son los frecuentemente retornados. Rentabilidad de los productos. 13.

(14) Se desea saber cual de todos sus productos genera una mayor ganancia. Todos los muebles tiene diferentes precios ya sea por el tamaño, material o complejidad en la manufactura, además el precio se ve afectado por el mercado y la alta competencia en el sector por esto no pueden cobrar mucho mas del costo bruto del mueble, por lo cual seria útil saber cual producto contribuye mas a las ganancias totales. Rentabilidad de los clientes Al igual, para los clientes se desea saber cuales generan una mayor ganancia, algunos compran los muebles y los revenden otros compran muebles en oferta retornándolos por razones dudosas, otros venden productos que tienen un mayor margen o compran al por mayor para reducir los costos del envió. Si MDLA pudiera crear una forma de clasificar a los clientes bajo este criterio, podrían manejar mas fácilmente a los clientes de menor margen ya sea desasiéndose de ellos o tal vez convirtiéndolos a un canal menos costoso como teléfono o internet Problemas Adicionales Existen pocas personas en las diversas partes de la organización que pueden extraer información del sistema transaccional. Desafortunadamente, todos ellos reportan su información de manera diferente, como resultado el GERENTE GENERAL escucha diferente información de David y Brian acerca de las ventas al igual que diferente información sobre Pedro y Jerry acerca de la manufactura y producción. El sistema transaccional parece funcionar correctamente, se puede obtener fácilmente información mensual sobre la producción y financiera pero en un formato que no es muy útil, por ejemplo las tendencias y comparaciones son difíciles de conseguir , GERENTE GENERAL no puede obtener la información de las ordenes por internet y los clientes mayoristas si utilizar Excel para procesar la información. Dirección de producción Pronostico de Producción Pedro mira a la historia de las ventas para crear su pronostico de producción basándose en las ventas en las diferentes temporadas y el ciclo de vida del producto. Este lo compara con el pronostico de ventas del grupo de Víctor en Dirección Comercial. Si están muy desfasados hablan con Guillermo uno de los analistas de ventas para tratar de solucionar las discrepancias. este procesos no siempre es exitoso por que algunos los datos no se mantienen actualizados entre los diferentes canales. 14.

(15) Control de Producción La programación de la producción genera ciertos problemas ya que cada mueble se despieza y las piezas pasan por el proceso de producción de forma separada, en ocasiones al momento del envío se dan cuenta de que las piezas no están completas conllevando a soluciones poco elegantes y a retrasos en la producción.. Mejorar la Calidad del Producto Además del problema mencionado anteriormente que afecta la calidad, Pedro esta interesado en medir la calidad de todos los productos, no solo los que salen con problemas, para esto El gerente general le pidió que pensara como puede conseguir alguna información de los sistemas disponibles que les pueda decir si están haciendo un buen trabajo. Pedro no ha logrado completar esta tarea para esto le interesa poder realizar algunos análisis sobre la razones para las devoluciones, el motivo de las llamadas a soporte al cliente al igual que las realizadas por el portal Web. David Jefferson Dirección comercial Planeación y Monitoreo de Productos La mayoría de los análisis que realiza la dirección comercial se centran en los datos de las ordenes, ellos desean entender el rendimiento de las diferentes líneas de productos y realizar planeación de la producción para así evitar los inconvenientes que se puedan presentar en la que los productos se canibalicen generando perdidas Efectividad dela Campaña de Mercadeo MDLA desea dar a conocer su marca (crear Brand Awareness) y generar lealtad en sus clientes para esto ha iniciado diferentes campaña publicitarias y les gustaría poder medir la efectividad de estas en cada región estableciendo una base para poder calcular el incremento de las ventas a razón de las campaña, lo importante es poder agrupar los clientes en las regiones que utilizan el mismo vehículo para su campaña, es decir la región donde se presentaron los anuncios en revistas o en televisión etc… MDLA desea compara estas zonas antes y después dela campaña con las demás zonas que no estuvieron involucrados en la campaña. Monitoreo de los Canales 15.

(16) David se encuentra interesado en monitorear lo canales sobretodo el Callcenter ya que no se tiene mucho control sobre este y seria interesante para realizar promociones que motiven el uso de este canal . Helena Matthews Clasificación Demográfica de los Clientes por Internet Helena le ha dedicado mucho tiempo en estos últimos días a clasificar los clientes de internet demográficamente para así entender la naturaleza de los mismos y sus hábitos de compra. Desafortunadamente se han presentado inconvenientes con el esquema XML en el cual se captura la información en el sitio web.. Perfilamiento de Clientes y Mercado Objetivo A Helena le gustaría promocionarse mas sofisticadamente a sus clientes por internet ella sabe que las promociones enfocadas a clientes que entren en cierto perfil demográfico y de compras podría a marcar una gran diferencia en las ventas promedio por cliente. Ella y otros compañeros del departamento han hablando con algunos de los vendedores encargados de las promociones y el campañas de productos y MDLA podría estar mejoras bastante en este aspecto. A Helena también le interesa mirar los clientes por región lo cual es simplemente agrupar las personas de un departamento o provincia en la que viven. Helena ha notado grandes diferencias en las preferencias de modelo y color basado en el lugar donde viven. El departamento tiene múltiples definiciones para las regiones que usan en sus análisis, ella mira a sus clientes agrupados por regiones basadas en las ventas totales de internet pero lo administradores de los productos ven a las regiones de diferente forma es mas las definiciones de región son modificadas constantemente.. Programa de Lealtad Helena le gustaría tratar de construir una lealtad por parte de sus clientes por internet , ella piensa que por medio del algún tipo de programa de fidelidad podrían fomentar que los clientes realizaran mas compras directas con MDLA cuando los clientes desean un nuevo diseño, cambiar tapizados, mas muebles o comprar muebles para regalar. Seria muy conveniente poder identificar a los candidatos mas probables para este tipo de relación a largo plazo. David Thorsson Director de compras. 16.

(17) MDLA planea expandirse a Europa y otros países lo cual implica que la variación de las diferentes monedas puede afectar las ganancias de la empresa se necesita poder ver en cada una la moneda local y el peso para examinar los cambios en las tasa de cambio a través del tiempo para así poder dado el caso separar el impacto de un cambio en las ordenes del impacto de un cambio en la tasa de cambio. Moneda estándar para los reportes. El hecho de que la información extraída del sistema transaccional en dólares requiere una Query bastante complejo para obtener a sido un dolor de cabeza para la mayoría en la oficina central. La mayoría el negocio quiere ver la información de las ordenes en dólares para compararlas entre países. Al mismo tiempo a gente de ventas quiere crear reportes en la moneda local para mostrar a su clientes. El departamento quiere ambos para poder evaluar las tasa de cambio en las variaciones de presupuesto . Análisis de las tasa de cambio David piensa en crear un fondo para protegerse de las fluctuaciones de las tasas de cambio quiere evaluar el posible impacto que tendría en los reportes financieros. El cree que puede tener un impacto significativo reduciendo las perdidas por las fluctuaciones. 6.2.1.4.. Cambios a la base de datos. 17.

(18) Figure 2 diagrama product base de datos. Se decide que Size no es lo suficiente para denotar el tamaño de un mueble lo cual es crucial en el negocio de los muebles por esta razón se crean las columnas Lenght Weight y Width para representar las dimensiones del mueble. Para describir el mueble en su totalidad se crea también las columnas Material y Fabric estas describen que material es y que tela (si tiene). Además se poblaron los nuevos campos creados para cada uno de los productos en existencia. 6.2.2. Proyecto 1. 18.

(19) Enunciado Familiarización con el caso de estudio MDLA. Objetivo Conocer a fondo el caso MDLA, cual es su negocio y como se encuentra estructurada esta empresa que será utilizada como caso de estudio para el desarrollo de los proyectos del curso.. Contexto General Muebles Los Alpes S.A (MDLA) es una empresa multinacional que se dedica a la producción y comercialización de muebles para hogar y oficina, al igual que accesorios ( lencería colchones etc..) en los mercados de Norteamérica y Latinoamérica las ventas se realizan a través de 3 canales: Puntos de venta (POS), Call center, y ventas por internet. La mayor parte de los ingresos proviene de las ventas en los POS ya que ellos son los que tiene el inventario mas actualizado con promociones y nuevos diseños. Finalmente las ventas por internet han tenido ciertos problemas por información desactualizada lo cual conlleva que se vendan promociones que ya no existen o no se anuncien las nuevas desaprovechando la ventajas en precios competitivos que otorga este canal. La Base de datos de MDLA se basa en un esquema genérico para el caso de retail (http://sistemas.uniandes.edu.co/~isis3301/dokuwiki/doku.php?id=enlaces:awc#recur sos). Actividades 1. Estudiar documentación: a. Leer las paginas 21-24 del primer capitulo de[1] b. Visite la página: http://sistemas.uniandes.edu.co/~isis3301/dokuwiki/doku.php?id=enla ces:awc#recursos. En la sección Recursos encontrará información complementaria acerca del caso de estudio. Lea la descripción de los escenarios empresariales, el diccionario de Datos de la Base de Datos OLTP de AWC y revise el esquema de dicha base de datos. 2. Conocer el negocio a partir de los datos en la base de datos OLTP Resuelva las siguientes consultas usando SQL presente los resultados en forma tabular similar a lo observado en as paginas 23 y 24 de [1](cada consulta debe ir acompañado de una descripción de la lógica tras este y porque se realizó así al igual que cada tabla debe tener una pequeña descripción ): a. Analice los cambios realizados al la tabla producto con relacion esquema generico. 19.

(20) b. ¿Cuáles son los principales distribuidores de la compañía? ¿En dónde se encuentran ubicados? c. Identificar el comportamiento de las ventas durante las diferentes temporadas de compras (Verano invierno, primavera, otoño, navidad, acción de gracias, reyes magos etc.. ) d. Caracterice a los clientes del call center ¿Quiénes son? ¿Qué productos compran? ¿Dónde están ubicados? ¿frecuencia de compra? e. Siguiendo el ejemplo del libro , intente entender el proceso de fabricación de la empresa a través de la base de datos. ¿Como es el estado del inventario? ¿productos fabricados en mayor cantidad? ¿Cuál es le producto que presenta defectos mas frecuentemente? Entregables. Se debe elaborar un informe en el que se muestren: 1. Consultas SQL(Con su respectiva descripción ) (25%) 2. Resultados de las consultas en forma tabular(con su descripción) (20%) 3. Análisis de los resultados (50%) 4. Redacción y Ortografía (5%). Además se proporciona una guía para utilizar la maquina virtual con SQL server instalado En los anexos se encuentra la plantilla de calificación de este nivel Plantilla-calificacion-proyecto1.xslx. Solución. A. En la tabla de producto se agregaron nuevos campos como height, width y length para describir las dimensiones de los productos al igual que material y fabric para su composición estos e debe a que no todos los productos de los diferentes retail tiene las mismas características por eso deben ser adaptados con las que parezcan pertinentes para los clientes.. B.. CONSULTA SQL 1 useMDLA;. 20.

(21) selecttop 10 tb.Name,st.NameTerritory,st.CountryRegionCode,tb.Due,tb.PctDueTotal,t b.Due*100/(selectSUM(soh.TotalDue)Due fromSales.Stores,Sales.SalesOrderHeadersoh wheres.CustomerID=soh.CustomerID)PctDueResellers fromSales.SalesTerritoryst, (selects.Name,soh.TerritoryID,SUM(soh.TotalDue)Due,SUM(soh.TotalDue)* 100/(selectSUM(TotalDue)fromSales.SalesOrderHeader)PctDueTotal fromSales.Stores,Sales.SalesOrderHeadersoh wheres.CustomerID=soh.CustomerID groupbyName,TerritoryID)astb wheretb.TerritoryID=st.TerritoryID. Name. Territory. Country Due RegionC ode Outlet Muebles 209 Bogotá CO 13,512,248,618 Outlet Muebles 193 Bogotá CO 13,151,956,558 Mueblería 218 Cali CO 11,798,574,657 Outlet Muebles 501 Medellín CO 11,794,758,399 Muebleria 452 Bucaramanga CO 11,347,474,413 Outlet Muebles 240 Bogotá CO 10,844,390,265 Almacen 523 Cali CO 10,741,543,035 Tienda de Decoraciones 30 Cali CO 10,451,970,498 Almacen 56 Pereira CO 10,055,397,181 Tienda de Decoraciones Cali CO 9,843,240,473 678. PctDueTota l 0,9603 0,9347 0,8385 0,8382 0,8064 0,7707 0,7633 0,7428 0,7146 0,6995. orderbyDuedesc Table 1 Resultado Query. ANÁLISIS. En la consulta podemos apreciar que los principales distribuidores se ubican en Bogotá y son Outlet Muebles 209, Outlet Muebles 193.. C. CONSULTA SQL 1 useMDLA; GO SELECTt1.total1AS'Total (ENE-FEB-MAR)',t2.total2AS'Total JUN)',t3.total3AS'Total (JUL-AGO-SEP)',t4.total4AS'Total DIC)',t1.total1+t2.total2+t3.total3+t4.total4AS'Total'. 21. (ABR-MAY(OCT-NOV-. PctDueResell ers 1,248 12,147 10,897 10,894 10,481 10,016 0,9921 0,9653 0,9287 0,9091.

(22) FROM (SELECTSUM(TotalDue)AStotal1 FROMSales.SalesOrderHeadersoh,Sales.Storec WHEREsoh.CustomerID=c.CustomerIDAND(MONTH(soh.OrderDate)>= ANDMONTH(soh.OrderDate)<4) )t1 , (SELECTSUM(TotalDue)AStotal2 FROMSales.SalesOrderHeadersoh,Sales.Customerc WHEREsoh.CustomerID=c.CustomerIDAND(MONTH(soh.OrderDate)>= ANDMONTH(soh.OrderDate)<7)ANDc.CustomerType='S' )t2 , (SELECTSUM(TotalDue)AStotal3 FROMSales.SalesOrderHeadersoh,Sales.Customerc WHEREsoh.CustomerID=c.CustomerIDAND(MONTH(soh.OrderDate)>= ANDMONTH(soh.OrderDate)<10)ANDc.CustomerType='S' )t3 , (SELECTSUM(TotalDue)AStotal4 FROMSales.SalesOrderHeadersoh,Sales.Customerc WHEREsoh.CustomerID=c.CustomerIDAND(MONTH(soh.OrderDate)>= 10)ANDc.CustomerType='S' )t4 GO. 1. 4. 7. Total (ENE-FEBTotal (ABR-MAY- Total (JUL-AGO-SEP) Total (OCT-NOV- Total MAR) JUN) DIC) $21,826,311.6438 $26,902,079.6230 $31,347,130.2991 $28,190,724.1359 Table 2 Resultado Query. ANÁLISIS. Se puede apreciar que las mayores ventas se realizan durante el tercer trimestre del año esto se puede deber a que los distribuidores se provisionan durante este periodo para poder mantener la demanda durante las fiestas, al igual que durante los meses de julio, agosto, septiembre se dan un gran numero de matrimonios y estos compra muebles para sus hogares. Durante el primer trimestre las ventas disminuyen considerablemente lo cual puede ser a causa de las compras navideñas que agotan en el capital de compra .. D. CONSULTA SQL 1 selecttop 20 c.FirstName,c.LastName,(CASEWHENv.HomeOwnerFlag>0 THEN'YES'ELSE'NO'END)as'HomeOwnerFlag',v.Education,v.Occupation,(CASE. 22. $108,266,245.701.

(23) WHENv.Gender='F'then'Female'else'Male'end)asGender,(CASEWHENMaritalSt atus='S' then'Single'else'Married'end)asMaritalStatus, (CASEWHENTotalChildren>0 THEN'YES'ELSE'NO'END)as'Children?' from Person.Contactasc,Sales.SalesOrderHeaderassoh,Sales.vIndividualDemogr aphicsasv whereCallCenterOrderFlag=1 Table 3 Resultado Query. HomeOwnerFlag YES NO YES NO YES YES YES YES YES NO NO YES YES YES NO NO YES YES YES NO. Education Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Bachelors Partial College Partial College High School Partial College High School. Occupation Professional Professional Professional Professional Professional Professional Professional Professional Professional Professional Professional Professional Management Management Management Skilled Manual Skilled Manual Skilled Manual Clerical. Gender Male Male Male Female Female Male Female Male Female Male Female Male Female Male Female Female. MaritalStatus Married Single Married Single Single Single Single Married Single Single Single Married Married Married Single Single. Children? YES YES YES NO YES NO NO YES YES NO NO YES YES YES YES NO. Male. Married. NO. Female. Single. YES. Male. Single. YES. Skilled Manual. Male. Single. NO. CONSULTA SQL 2 SELECTCategory,SUM(SUM_MONEY)ASSUM_MONEY,SUM(SUM_UNITS)ASSUM_UNITSFRO M ( SELECTSOD.ProductID,SUM(SOD.LineTotal)ASSUM_MONEY,SUM(SOD.OrderQty)AS. 23.

(24) SUM_UNITSFROMSales.SalesOrderDetailSOD, Sales.SalesOrderHeaderSOHWHERESOH.CallCenterOrderFlag=1 ANDSOD.SalesOrderID=SOH.SalesOrderIDGROUPBYSOD.ProductID )XINNERJOIN( SELECTP.ProductIDASID2,P.Name,PC.NameASCategoryFROMProduction.Product P,Production.ProductCategoryPC,Production.ProductSubcategoryPSWHERE P.ProductSubcategoryID=PS.ProductSubcategoryID ANDPS.ProductCategoryID=PC.ProductCategoryID )YONX.ProductID=ID2GROUPBYCategoryORDERBYSUM_MONEYDESC. Table 4 Resultado Query. Category. Venta Clientes ($). Hogar Partes Lenceria Oficina. $22,441,362 $3,954,318 $572,718 $182,517. Venta Clientes (Unidades) 25653 16303 20670 8231. Porcentaje de Ventas ($) 82.65% 14.56% 2.11% 0.67%. ANÁLISIS Se puede apreciar que la mayoría de los clientes de callcenter son personas solteras con nivel universitario o superior pero con hijos, esto se puede deber a la gran cantidad de divorcios en la actualidad lo cual implica que tengan que comprar bastantes muebles al mudarse a su nuevo hogar lo que se evidencia en el flag de propietarios de casa y se facilita hacerlo por teléfono dada la ayuda prestada por los asesores. También se puede apreciar que la gran mayoría de las ventas se centra en hogar y luego en los repuestos para los muebles ya que la mayoría de productos caen la categoría de hogar y cada compra que se hace generalmente incluye varios productos de esta. E. Inventario: SELECT. top 30 PP.Name AS CATEGORIA, SUM(PV.Quantity) ASCantInventario, COUNT(PB.ProductAssemblyID)ASNoComponentesFROMProduction.ProductASPPJ OINProduction.ProductInventoryAS PVONPP.ProductID=PV.ProductIDJOIN Production.BillOfMaterials AS PB ONPV.ProductID=PB.ProductAssemblyID GROUPBYPP.NameorderbyCantInventarioDESC. 24. Porce.

(25) Table 5 Resultados Query. CATEGORIA Escritorio Oficina 42 Escritorio Oficina 45 Sillla Comedor 13 Centro de Entretenimiento 22 Centro de Entretenimiento 23 Sillla Comedor 5 Centro de Entretenimiento 24 Sillla Comedor 15 Sillla Comedor 17 Sillla Comedor 9 Sillla Comedor 7 Sillla Comedor 21 Sillla Comedor 18 Sillla Comedor 8 Sillla Comedor 10 Sillla Comedor 16 Mesa Comedor 28 Sillla Comedor 14 Biblioteca 2 Mesa Comedor 26 Centro de Entretenimiento 29 Sillla Comedor 12 Sillla Comedor 6 Sillla Comedor 20 Sillla Comedor 11 Mesa Comedor 27 Centro de Entretenimiento 31 Centro de Entretenimiento 30 Mesa Comedor 25. CantInventario 9174 8856 8756 8260. NoComponentes 66 72 66 60. 7909. 66. 7670 7502. 60 66. 7308 7191 7104 6544 5928 5859 5817 5551 5481 5474 5448 5408 5327 5310. 18 18 16 16 16 14 14 14 14 21 16 12 21 18. 5152 5054 5012 4746 4632 4615. 14 14 14 14 18 15. 4525. 15. 4505. 15. 25.

(26) Mesa Comedor 24. 4410. 15. Producto mas fabricado: SELECTtop 30 Name,SubCategory,Category,CantAConstruir,CantDescartada FROM ( SELECTP.ProductID,P.Name,SUM(WO.OrderQty)ASCantAConstruir,SUM(WO.Scra ppedQty)ASCantDescartada FROMProduction.WorkOrderWO,Production.ProductPWHEREWO.ProductID=P.Pro ductID GROUPBYP.ProductID,P.Name ) X LEFTOUTERJOIN ( SELECTDISTINCTP.ProductIDASID2,PS.NameASSubCategory,PC.NameASCategory FROMProduction.ProductP, Production.ProductCategoryPC,Production.ProductSubcategoryPS WHERE P.ProductSubcategoryID=PS.ProductSubcategoryIDANDPS.ProductCate goryID=PC.ProductCategoryID ) YONX.ProductID=Y.ID2 ORDERBYCantAConstruirDESC. Table 6 Resultado Query. Name Biblioteca 2 Sofa 12 Sofa Biblioteca 4 Silla Jardin Mesa de noche 4 Sofa 14 Cama 5 Mesa de noche 2 Sofa 15 Sofa 16 Silla 2 SofaCama 2 Sofa 13 Mesa de noche Centro de Entretenimiento. CantAConstruir CantDescartada 911890 1031 469468 1154 236002 736 236002 1374 234734 585 128226 178 118001 692 118001 264 117367 342 117367 247 117367 348 117367 571 96080 43 94218 84 94218 422 91081 174 26.

(27) 25 Mesa de Centro 32 Mesa Comedor 27 Mesa Comedor Centro de Entretenimiento 30 Mesa de noche 3 Mesa Comedor 25 Mesa Comedor 23 Estanteria 13 Biblioteca 3 Estanteria 14 Mesa Comedor 24 Estanteria 15 Sillla Comedor 17 Sillla Comedor 2 Defectos. 90621 62822 62302 62231. 105 154 143 225. 57583 48531 43146 41148 30054 26956 26324 23085 22919 22264. 32 38 140 107 123 14 21 75 47 63. SELECTtop 10 PP.NameASCategoria,SUM(PW.OrderQty)ASCantidad,SUM(PW.ScrappedQty)ASDe fectuoso, SUM(PW.ScrappedQty)*100.00/SUM(PW.OrderQty)ASPorcentajeFROMProduction .ProductASPP JOINProduction.WorkOrderASPWONPP.ProductID=PW.ProductIDGROUPBYPP.Name orderbyPorcentajedesc Table 7 Resultado Query. Categoria Mesa Comedor 32 Silla Jardin 15 Mesa de Centro 31 Sillla Comedor 21 Cama 5 Cojin Silleteria 8 Biblioteca 4 Cojin Silleteria 4 Sillla Comedor 7 Silla Jardin 9. Cantidad Defectuosos 800 6 634 4 2311 14 14751 87 118001 692 2232 13 236002 1374 346 2 17809 100 2270 12. ANÁLISIS. 27. Porcentaje 0.750% 0.631% 0.606% 0.590% 0.586% 0.582% 0.582% 0.578% 0.562% 0.529%.

(28) Se pone en evidencia que los productos de los cuales se tiene mas inventario son los que mas partes requieren ya que son mas complejos de producir también se puede inferir que los escritorios de oficina se compran al por mayor por ende se necesita tener un inventario considerable de los mismos. Se puede apreciar que los productos que mas se producen son los sofás y las bibliotecas ya que probablemente tienen una alta circulación a pesar de esto se podría mejora las ganancias si se redujera el margen de errores para no tener que descartar en tantas ocasiones, pero como se nota en porcentaje de descarte no son los primeros sino mesa de comedor 32 con 0.75% descartado que aun así es una cifra despreciable por cual la empresa tiene una línea de producción eficiente. 6.2.3. Proyecto 2 Enunciado Análisis de los requerimientos de BI para los diferentes roles de la organización, priorización y verificación Objetivo. Realizar el levantamiento de requerimientos para un proyecto de BI. Contexto. Para conocer los requerimientos de BI de la empresa en necesario realizar una serie de entrevistas para así familiarizarse con el negocio conocer cuales son los procesos de negocio mas importantes para obtener valor en su negocio , e identificar posibles patrocinadores para el proyecto . Cada entrevista se debe resumir agrupando cada requerimiento individual en un tema analítico común a toda la empresa.. 1. Estudiar documentación: a. Lea las páginas 25-36 del primer capítulo de [1]. b. Visite la página: http://sistemas.uniandes.edu.co/~isis3301/dokuwiki/doku.php?id=enlaces: awc#recursos. En la sección Recursos encontrará información complementaria acerca del caso de estudio. En la sub-sección 28.

(29) Levantamiento de Requerimientos lea los resúmenes de las entrevistas realizadas ne la empresa 2. Completar la plantilla de requerimeintos. a. Descargue del sitio del proyecto el documentos de EXCEL con el nombre Requerimientos.XLSX b. De las entrevistas leídas extraiga los temas analíticos pertinentes, análisis requeridos directa o indirectamente por ellos y las dimensiones que se puedan necesitar para ellos, documentándolos en la primera hoja del documento de EXCEL. c. Complementa la matriz de la bodega de datos con los nuevos procesos y dimensiones identificadas en las entrevistas(página 2 EXCEL) d. Realice la priorización de los procesos de negocio. Teniendo en cuenta la factibilidad de los análisis descritos anteriormente, identifique que tablas podrían soportar cada tipo de análisis (página 3 EXCEL) e. Con base a los resultados de la priorización escoja el primer proceso de negocio que se debe implementar sobre la bodega de datos Entregables Documento de Excel diligenciado: 1. Temas Analíticos y análisis requeridos e inferidos (25%) 2. Matriz de la bodega de datos (45%) 3. Priorización de los procesos de negocio (25%) 4. Redacción y Ortografía (5%). Solucion La solución se adjunta a este proyecto como Requerimientos(solucion).xsl 6.2.4. Proyecto 3 Enunciado Modelado Multidimensional Objetivo. Realizar un ejercicio de modelado Multidimensional bajo la metodología Kimball Contexto. Siguiendo la metodología Kimball, en el proyecto anterior se realizo un levantamiento de requerimientos global a partir de las entrevistas , además de la priorización de los procesos a implementar. Para propósito de este proyecto se selecciona el proceso de. 29.

(30) Mercadeo asumiendo que el Data Mart de proceso de ordenes ya fue implementado usted debe realizar el ejercicio de modelado sobre el proceso de mercadeo. Además se tiene el modelo genérico para negocios de Retail (ubicado en http://sistemas.uniandes.edu.co/~isis3301/dokuwiki/doku.php?id=enlaces:awc) como es el caso de MDLA el cual se utilizo con ciertas modificaciones para ajustarse al proceso de negocio de la empresa. Actividades. 1. Observando nuevamente la entrevistas concéntrese en analizar los requerimientos relacionados con el proceso de mercadeo 2. Concrete y documente los requerimientos de análisis relacionados con el procesos de Mercadeo 3. Realice un análisis de los datos disponibles 4. Revise el escenarios de Ventas y Marketing http://sistemas.uniandes.edu.co/~isis3301/dokuwiki/doku.php?id=enlaces:awc ) para obtener una descripción detallada del esquema de la base de datos que servirá de fuente para los Data Mart. 5. Utilizado como ejemplo el caso de procesos de ordenes del capitulo 2 de [1] hacer el proceso de modelado multidimensional del proceso mercadeo. La especificación del modelo debe incluir: a. Especificar para cada estrella el nivel de granularidad de la tabla de hechos b. Para todas las dimensiones explicar como se agrega cada una de las medidas(suma, promedio, máximo, etc.). c. Listar para todos los atributos en las diferentes dimensiones, su definición que valores puede tomar cuando estos no sean obvios d. Para cada atributo de las dimensiones de variación lenta (Slowly Changing Dimension), especificar el tipo de manejo (1,2, 3). e. Realizar una representación grafica del Modelo Multidimensional que detalle las dimensiones con sus atributos y los Fact tables con sus medidas y demás. f. Enunciar y justificar as técnicas que empleo en el diseño(ejm: junk dimensions, slowly changing dimensions, degenerate dimensions, heterogeneous products, outtriggers, etc.) , por que las uso, y una pequeña descripción de proceso. Entregables. 1. 2. 3. 4.. Documentación de los requerimientos para el proceso de mercadeo (15%) Documentación del proceso de modelado multidimensional (50%) Diagramas de estrella con su documentación (Diagramas entendibles ) (30%) Redacción y Ortografía (5%). 30.

(31) Solucion Órdenes de Venta por región y por promoción: En las entrevistas mencionan que les interesa conocer el impacto que tienen las promociones en las ventas en cada sector, cuales tienen un mayor impacto, apara si poder implementarlas en otras zonas con características similares ya que además estas promociones le permiten eliminar inventarios que nos halla movido .. Observaciones –Análisis de los datos. Person.Contact tiene los datos básicos del individuo como nombres, correo electrónico, teléfono y dirección La columna EmailPromotionen Person.Contact indica si la persona esta dispuesta a recibir información de promociones por correo electrónico . Sales.Customer contiene la información de los clientes como su numero de identificación dentro del sistema y el territorio de ventas al que pertenece La columna CustomerType en Person.Contact indica si el cliente es una tienda minorista o individuo Sales.SalesOrderHeader contiene la características de al orden al igual que el estado de la misma y el canal por el cual se solicito La columna Status Sales.SalesOrderDetail describe el producto que se vende y la promociona a la que pertenece Sales.Individual contiene un XML con toda la información demográfica del cliente Sales.Store tiene la información demográfica pertinente a las tiendas Sales.Contact muestra la relación entre los representantes de las tiendas y los representantes de MDLA Solución propuesta Para satisfacer los requerimientos de análisis se proponen 2 tablas de hecho diferente con respecto a las ordenes de venta . 1. Un Fact Table cada fila indica una nueva venta realizada, esta pobla a aparitir de Sales.SalesOrderDetail de y es de carácter transaccional. 2. El segundo consiste en un Periodic Snapshot para tender un resumen a nivel de orden de venta. 31.

(32) Ordenes de Venta - Transacción. Figure 3 Estrella FactSalesOrderDetail. Granularidad: Orden de Venta 32.

(33) Dimensiones asociadas Date Employee Promotion Territory Product Customer. Resumen de Órdenes de Venta – SnapshotPeriódico. Figure 4 Estrella FactSalesOrdersSummary. 33.

(34) Granularidad: Orden de Venta Dimensiones asociadas Date Promotion Territory Product. La solución se complementa con el adjunto MDWToolkit Datamodel 31.5_UA.xslx 6.2.5. Proyecto 4. Enunciado Diseño físico y ETL Objetivos Realizar un ejercicio de diseño físico de Bodegas de Datos en un a plataforma específica. Diseñar e implementar el proceso de ETL en un contexto restringido Contexto El modelo dimensional para el proceso de manufactura que se desea imp lantar en la bodega de datos es el siguiente:. 34.

(35) Figure 5 Estrella Enunciado. Actualmente sobre el esquema MDLADW ya se encuentran creadas y pobladas las tablas DimDate , DimTerritory y DimProduct. Para terminar la creación del data mart es necesario incluir las tablas DimPromotion y FactSalesOrdersSummary Diseño físico La primera tarea consiste en el diseño físico de las nuevas tablas que se incorporarán en la bodega de datos. Para su trabajo puede guiarse en el ejemplo del capítulo 4 de [1] para realizar su diseño. Allí se plantean las siguientes consideraciones:. Consideraciones del sistema o o o o. Volúmenes de datos Tipos y complejidad de uso de la BD Número de usuarios simultáneos Recursos que el sistema necesita (memoria, sistema de almacenamiento, capacidad de procesamiento). 35.

(36) Diseño físico de las tablas relacionales o o o o o o o o o o o. Defina las llaves sustitutas (tipo y tamaño de los campos) de las tablas Para cada campo, si es de tipo String, especifique el tamaño adecuado de acuerdo a lo encontrado en la fuente de datos. Decida un tratamiento de los campos nulos (miembros desconocidos ) Defina las propiedades extendidas de las tablas y columnas Defina las columnas de mantenimiento (housekeeping columns) Cree los índices y las restricciones sobre las columnas de las tablas Cree las vistas necesarias Defina las particiones de las tablas (si es el caso) Defina las tablas de agregados (si es el caso) Determine la necesidad de usar Staging tables Defina los metadatos que se van a manejar sobre la bodega de datos. Para esta tarea y la siguiente puede ayudarse con el formato de Excel adjunto al enu nciado. No olvide llenar los campos faltantes .. Diseño de ETL Antes de realizar la implementación del proceso de ETL se debe crear un documento en el que se especifique su diseño. El documento debe contener los resultados de las siguientes actividades: Perfilamiento de los datos Definición de correspondencia entre los datos de origen y destino Definición de la frecuencia de carga de la bodega Definición de la cantidad de historia que se desea guardar Especificación del manejo que se realizará a las particiones (si es el caso) Desarrollo de estrategias de extracción de los datos - Diagrama de flujo del ETL para cada tabla nueva que se cargará a la bodega de datos. Planteamiento de una estrategia de distribución de las dimensiones (si es el caso) Definición de convenciones usadas. Implementación del proceso de ETL Para la implementación del proceso de ETL puede usar la herramienta Microsoft SQL Server Integration Services 2008 que se encuentra en la máquina virtual del taller. Para la implementación siga las siguientes líneas generales: Cree un proyecto de Integration Services desde Business Intellgence Deve lopment Studio. Cree un paquete aparte para cada tabla nueva que cargará a la bodega de dato s.. en el documento de diseño de ETL ejm: Dimensiones de variación lenta, miembros desconocidos, etc.). 36.

(37) Entregables Documento de diseño físico (25%) Documento de diseño de ETL (20%) Plantilla de Excel completa (10%) Proyecto de Integration Services con los paquetes de carga de la dimensión Promotion y la tabla de hechos FactSalesOrdersSummary (40%) Redacción y ortografía (5%). Solución Consideraciones del sistema Volúmenes de datos: El sistema OLTP de MDLA tiene un total de 121.317 órdenes de venta (SaleOrderDetail); sin embargo, sólo existen 31.465 entradas en la tabla SaleOrderHeader. Por lo tanto podemos asumir que la tabla de hechos no tendr mas de 121.317*100 Bytes(tamaño promedio de una fila comun)= 1.21Gigas con informacion de 3 años y asumiendo un crecimiento de 30% anual alcabo de 7 años no crecera mas de cuatro giagas asi que es fativle almacenar una ventana de 10 años El tamaño de las dimensiones es generalmente despreciable en comparación con el de la tabla de hechos. Los volúmenes de datos en este caso son bastante bajos.. Tipos y complejidad de uso de la bodega de datos:. Aunque es difícil estimar los tipos de uso y la complejidad de los análisis que se desean realizar sobre la bodega de datos, se puede esperar que alrededor del 60% de los análisis sean de complejidad simple a través de reportes, con un 30% de operaciones de complejidad mediana y no más de un 10% de uso exigente. Número de usuarios simultáneos MDLA cuenta con pocos empleados que asu ves no es muy probable que utilicen el sistema simultaneamente Requerimientos de disponibilidad del sistema El sistema debe estar disponible las 24 horas ya que MDLA tiene oficinas en diferentes continentes. Ademas de las ventas por internet .. 37.

(38) Recursos del sistema A partir de los análisis anteriores se puede concluir que el sistema es relativamente pequeño. La bodega de datos puede implementarse en hardware tipo commoditysiguiendo una arquitectura “todo en 1”.. Figure 6 Arquitecutra All-in-One. Memoria: 16 Gb Procesador: (Quad Core 64 bits) Almacenamiento en disco: Arreglo de discos SATA 500Gb a 7200 rpm. Diseño físico de las tablas. Uso de llaves sustitutas Para las dimensiones Product, Territory y Promotion se emplearán llaves sustitutas autogeneradas. Para la dimensión Date se creará una llave entera de la forma (AAAAMMDD) siguiendo las recomendaciones del grupo Kimball. Tamaño de los campos. Adjunto en el Archivo de Excel. 38.

(39) Tratamiento de campos nulos Para evitar problemas con los valores nulos del sistema fuente, se decidió crear una entrada en cada dimensión con el identificador -1 para representar las entradas desconocidas. Propiedades extendidas de las tablas y columnas. Para cada tabla definida en SQL Server 2008 se agregará la propiedad extendida de la tabla llamada @description en la que se incluirá la descripción del negocio para la tabla. De la misma manera, para cada columna de las dimensiones se crearán dos propiedades extendidas @description y @sourceSystem. Por último, para cada columna no llave se creará la propiedad extendida @SCDType para documentar el tipo de cambio. Columnas de mantenimiento Para las dimensiones se emplearan las siguientes columnas de mantenimiento (housekeeping columns): RowStartDate RowEndDate RowIsCurrent Las columnas anteriores sirven para llevar un control de los atributos de variación lenta. Índices y restricciones sobre las columnas. DimProduct Se creará un clustered primary key sobre ProductKey Se establecerá un índice sobre la llave de de negocio BKProductID DimTerritory Se creará un clustered primary key sobre LocationKey En este caso no es necesario incluir un índice sobre la llave de negocio porque la tabla es pequeña DimPromotion Se creará un clustered primary key sobre PromotionKey Se establecerá un índice sobre la llave de de negocio BKPromotionID FactSalesOrdersSummary. 39.

(40) Se creará un clustered index sobre una de las llaves foráneas a la dimensión Date. Aunque en principio el índice debe ser sobre el campo más usado, en este caso las columnas ScheduledStartDateFK, ScheduledEndDateFK pueden ser usados con la misma frecuencia en las consultas a la base de datos. Para cada campo sobre el cual se ha definido una restricción de llave foránea se definirá un índice. Las columnas anteriores son las siguientes: ScheduledStartDateFK, ScheduledEndDateFK, ActualStartDateFK, ActualEndDateFK, ProductFK, PromotionFK y TerritoryFK. Se crearán restricciones de llaves foráneas para los campos que referencian a las dimensiones, pero estas restricciones se deshabilitarán durante el proceso de carga de la bodega. Con lo anterior, se evitan problemas de rendimiento en el momento en el que se estén insertando los registros en la tabla. Una vez terminada la carga, se habilitarán las restricciones para asegurar que no se haya afectado la integridad referencial de la bodega de datos. Diseño de ETL Perfilamiento de los datos Usando Integration Services se realizó un perfilamiento automático de las tablas del sistema fuente (ver paquete Profiles.dtsx del proyecto adjunto). Tras revisar los resultados no se encontraron novedades con respecto a los descubrimientos que se habían realizado en los ejercicios de perfilamiento anteriores. En este ejercicio se pudo verificar que no existen problemas de columnas con valores desconocidos (NULL). También se pudo encontrar la longitud mínima y máxima de cada uno de los campos de las tablas para establecer el tamaño adecuado de las columnas en las bodegas de datos. Definición de correspondencia entre los datos de origen y destino La correspondencia entre los datos de origen y destino se encuentra en el documento de Excel adjunto. Definición de la cantidad de historia que se desea guardar Dado el volumen reducido de datos manejado actualmente por la empresa, se calcula que es factible guardar la historia de los últimos 10 años sin ninguna complicación. Manejo de particiones No se requiere manejo de particiones Diagramas de flujo para la carga de las tablas. DimPromotion. 40.

(41) Sales.SpecialOffer. + Sales.SpecialOffer Product. Row auditing metadata SCD (2,1). DimPromotio n. Figure 7 Flujo ETL DimPromotion. 41.

(42) FactSalesOrdersSummary. Sales.SalesOrderDetail. + Sales.SalesOrderHeader. Surrogate key pipeline: for Promotion. Surrogate key pipeline: for Product. Surrogate key pipeline: for Territory. Surrogate key pipeline: for Date (ScheduledStartDate, ScheduledEndDate, ActualStartDate, ActualEndDate). Row auditing metadata. FactSalesOrdersSummary. Figure 8 Flujo ETL FactSalesOrdersSummary. 42.

(43) Estrategia de distribución de las dimensiones No hay nescesidad de realizar distribución de las dimensiones. Convenciones Las dimensiones tienen el prefijo Dim. Las tablas de hechos tienen el prefijo Fact. Las llaves primarias tienen el sufijo Key. Las llaves de negocio tienen el prefijo BK. Se adjunta la implementación del ETL. 6.2.6. Proyecto 5 Enunciado Proyecto 5 Consultas MDX. Objetivos Trabajar con una base de datos dimensional. Familiarizarse con los conceptos básicos de MDX.. Ejercicio para resolver y entregar en el enlace de Sicua Las consultas MDX se realizarán con la herramienta SQL Server Management Studio disponible en la máquina virtual del curso. En la wiki del cursoencontrará un video que muestra cómo configurar el ambiente de trabajo para el desarrollo del proyecto.. Escriba las instrucciones en MDX para resolver cada una de los siguientes consultas1:. 1. 2. 3. 4. 5.. Cantidad de ventas totales en el territorio de Bogotá(id=5) Ventas por territorio de productos de color rojo Precio por unidad de las zonas que aplican la promoción(id=3) Unidades vendidas por promoción La Cantidad de ventas por promoción. Entregables. 43.

(44) Archivo de Word en el que se muestre la consulta MDX que resuelve la pregunta de cada punto y el resultado obtenido. (20% cada consulta). Solución 1. SELECT [Measures].[Quantity] ONCOLUMNS, [Dim Territory].[Name] ONROWS FROM [MDLADW] where[Dim Territory].[Territory Key].&[5]. Figure 9 Resultado Consulta MDX. 2. Select [Measures].[Subtotal]onrows, [Dim Territory].[Name].membersonColumns FROM [MDLADW] where [Dim Product].[Color].&[Rojo]. Figure 10 Resultado Consulta MDX. 3. SELECT [Measures].[Unit Price]ONCOLUMNS, [Dim Territory].[Name].MEMBERSONROWS FROM [MDLADW] where [Dim Promotion].[BK Special Offer ID].&[3]. 44.

(45) Figure 11 Resultado Consulta MDX. 4. Select [Measures].[Quantity] oncolumns, [Dim Promotion].[Promotion Name].membersonrows FROM [MDLADW]. 45.

(46) Figure 12 Resultado Consulta MDX. 5. Select [Measures].[Recuento Fact Sales Orders Summary] oncolumns,[Dim Promotion].[Promotion Name].membersonrows FROM [MDLADW]. Figure 13 Resultado Consulta MDX. 6.2.7. Proyecto 6. Enunciado Proyecto 6 Creación de cubos. Objetivos Trabajar con KPI y acciones como instrumentos de análisis para satisfacer requerimientos de negocio Contexto. 46.

(47) En proyectos anteriores se venia trabajando con un data mart para el proeceso de mercadeo para MDLA. Figure 14 Estrella enunciado Proyecto 6. En esta ocasión teniendo en cuenta las entrevistas relacionadas con departamento comercial en particular el tema analítico de conocer los efectos de las promociones en las ventas total y en rotación de inventario Las tareas a realizar son: 1.. Cree un cubo para el data mart relacional de Manufactura.. 2.. Proponga al menos 2 KPIs que le permitan al gerente de control de producción monitorear el comportamiento de los costos.. 47.

(48) 3.. Proponga acciones que permitan profundizar en el análisis del comportamiento de los KPIs.. 4.. Defina para el cubo los KPIs y las acciones de los puntos 2 y 3. 5. Verifique el funcionamiento de los KPIs y las acciones con los datos del cubo.. Ayuda Instrucciones para instalar el ambiente de trabajo. l data mart de manufactura. La base de datos relacional se encuentra en los archivos adjuntos a la tarea en Sicua. Para instalar el ambiente de trabajo del proyecto siga las siguientes instrucciones: 1. Descargue de Sicua el archivo de la base de datos llamado MDLADW-Bkp. Cargue la base de datos en SQL Server siguiendo las instrucciones de http://msdn.microsoft.com/es-co/library/ms177429.aspx . Para esta tarea use la herramienta SQL Server Management Studio. 2. Descargue de Sicua el proyecto de Analysis Service cubo de manufactura (archivo Proyecto6.zip). Las dimensiones, grupos de medida, KPIs y acciones se deben crear sobre este cubo. Entregables Documento en el que se especifican los KPIs propuestos, indicando para cada uno la fórmula para el valor, el objetivo, la tendencia, etc y las acciones (30%). Proyecto de Analysis Services con el cubo completo y los KPIs y las acciones implementadas (70%). Solución KPIs propuestos SalesQuantity Valor:Toma su valor de la medida Quantity. Objetivo: Un 10% MAS que el año anterior. Estado: Si la razón entre el objetivo y el valor actual es mayor que 1, el estado es bueno. Si el valor de la razón está entre 0.75 y 1, el estado es aceptable. En caso contrario el estado del KPI es malo. Ganacias totales Valor: toma su valor de subtotal 48.

(49) Objetivo:Un 15% MaS que en el periodo anterior. Estado: Si la razón entre el objetivo y el valor actual es mayor que 1, el estado es bueno. Si el valor de la razón está entre 0.8 y 1, el estado es aceptable. En caso contrario el estado del KPI es malo. Acciones propuestas View basic Selling metrics Permite ver la información de costos básica para un producto fabricado. View territory metrics Permite ver la información detallada del comportamiento de un teirritorio de ventas. SalesQuantity. Expresion de valor [Measures].[Quantity] Expresion objetivo 1.10*([Actual End Date].[Jerarquía].PrevMember,[Measures].[Quantity]) Expresion Estado Case WhenKpiValue( "SalesQuantity" ) = 0 Then 1 WhenKpiValue( "SalesQuantity") /KpiGoal ( "SalesQuantity" ) >= 1.10 Then 1 WhenKpiValue( "SalesQuantity" ) / KpiGoal ( "SalesQuantity" ) < 0.75 Then -1 Else 0 End Expresion de Tendencia. ([Measures].[Quantity]/[Measures].[Quantity])-([Actual Date].[Jerarquía].PrevMember,[Measures].[Quantity]). End. Ganacias totales. [Measures].[Subtotal] Expresion objetivo 1.15*([Actual End Date].[Jerarquía].PrevMember,[Measures].[Subtotal]). 49.