Análisis de inventario LEGO
Descripción del proyecto
Analizar los datos históricos de inventario de la compañía Lego. Los datos están distribuídos en 12 tablas almacenadas en 12 archivos .csv diferentes. El objetivo es importar todos los datos en una base de datos de SQL Server para poder ejecutar consultas que respondan a las preguntas de negocio que tenemos que resolver. Utilizaremos SQL Server Managment Studio y SSIS en Visual Studio para importar los datos y posteriormente analizarlos.
- ¿Cuál es el número total de piezas por cada temática?
- ¿Cuál es el número total de piezas fabricadas por año?
- ¿Cuántos sets de Lego se han creado por cada siglo?
- ¿Qué porcentaje de los sets realizados en el siglo 21 tenían como trenes como temática?
- ¿Cuál fue la temática más popular de cada año del siglo 21 en términos de sets lanzados?
- ¿Cuál es el color más producido en términos de cantidad de piezas de ese color lanzadas?
- Crear una base de datos en SQL Server
- Importar datos a la BBDD mediante 2 métodos diferentes:
- Mediante el SQL Sever Managment Studio Wizard
- Mediante SQL Server Integration Services (SSIS) en Visual Studio
- SQL JOINs de múltiples tablas
- Funciones de ventana en SQL
- CTEs (Common Table Expression) en SQL
Crear BBDD y importar datos
Crear base de datos
No tiene mucho misterio, en SQL Server Managment Studio, una vez nos hemos conectado al servidor, le damos click derecho a la carpeta «Databases», clickamos en «New Database», le asignamos el nombre y le damos a «Ok». Base de datos creada.
Importar datos
En este proyecto he utilizado 2 métodos diferentes para importar los datos de las 12 tablas almacenadas en 12 archivos .csv diferentes:
1. Mediante el Wizard de SQL Server Managment Studio
Es la forma más simple de importar archivos planos como los archivos .csv pero también da bastantes errores de incompatibilidad de formatos. Pasos a seguir:
Paso 1: Click derecho en la BBDD en la que quieres importar los datos > «Tasks» > «Import Flat File…»
Paso 2: Se despliega la interfaz del wizard y le indicamos la ruta del archivo que queremos importar
Paso 3: Previsualizamos los datos y modificamos las columnas que sean necesarias
Paso 4: Comprobamos que todo esta bien configurado y importamos los datos
Importamos todos los archivos con este método menos 2 («parts.csv» y «inventory_parts.csv»), que producen errores que imposibilitan su importación. Con estos 2 archivos utilizaremos el siguiente método.
2. Mediante SQl Server Integration Services en Visual Studio
Microsoft SQL Server Integration Services (SSIS) es una plataforma que permite generar soluciones de integración de datos de alto rendimiento, entre las que se incluyen paquetes de extracción, transformación y carga de datos (ETL) para el almacenamiento de datos. Usaremos SSIS en Visual Studio para importar 2 de los 12 archivos que dan errores de formato al intentar importarlos con el wizard:
Abrimos Visual Studio y creamos un nuevo proyecto de de Integration Services desde la ruta en la que se encuentran almacenados nuestros archivos
A continuación creamos un flujo de datos, le añadimos un origen de datos de archivo plano, cargamos el archivo que queremos importar, en este caso «parts.csv» (tabla con los datos de las piezas de Lego) y configuramos el formato de las columnas.
Ya con el flujo de datos creado y con las columnas configuradas simplemente nos queda añadir un destino para nuestros datos. Añadiremos un destino OLE DB y lo conectamos con nuestro origen de datos ya creado, a continuación conectamos nuestro destino OLE DB a nuestro servidor de SQL Server y creamos la nueva tabla en la BBDD
Por último, para importar los datos en la base de datos, ejecutamos la tarea y comprobamos tanto en Visual Studio como en SQL Server que todo haya funcionado correctamente
Repetimos el mismo proceso con el archivo «inventary_parts.csv» y ya tenemos todas las tablas importadas en la base de datos par empezar el análisis.
Análisis de los datos
Para poder responder a las preguntas de negocio lo primero que haremos será crear una vista a la que llamaremos «vista_analítica_main» que se construye a partir de un left join de la tabla «sets» con la tabla «themes» mediante la columna «theme_id» de la tabla «sets» y la columna «id» de la tabla «themes».
Además crearemos también un join interno en la tabla «themes» para relacionar la columna «id_parent_theme» con la columna «id» y poder así saber el nombre de temática principal que tiene cada una de las temáticas
Una vez que tenemos la vista creada con las tablas relacionadas podemos empezar el análisis
Para saber el número total de piezas por cada una de las temáticas consultamos el sumatorio de la columna «num_parts» de la vista anteriormente creada que indica el número de piezas, agrupada por la columna «theme_name», que indica el nombre de la temática
Esta vez tenemos un output muy similar al anterior pero la primera columna en vez de indicar la temática indica el año
En todas las consultas añadiremos el condicional where comentado que filtra los resultados únicamente cuando la temática principal tiene un valor diferente de NULL.
Para saber el número total de piezas por cada uno de los años, como ya hemos hecho en la anterior question, consultamos el sumatorio de la columna «num_parts», que indica el número de piezas, agrupada, esta vez ,por la columna «year», que indica el año de lanzamiento de cada set de Legos.
Podemos apreciar como output y, por lo tanto, como respuesta a nuestra primera pregunta esta tabla que indica la temática y el número total de piezas de cada una de las temáticas en orden descendiente.
Para saber el número total de sets de Lego lanzados por cada siglo empezaremos la consulta creando una columna «Siglo» a partir de los dos primeros dígitos de la columna «year» utilizando el método LEFT() y sumándole al valor extraído 1 (Año 1978 > 19 + 1 > Siglo 20). Además añadiremos también a esta consulta el «Número_total_de_sets_lanzados» utilizando el método COUNT() de la columna «set_name» para contabilizar cada uno de los sets de Lego lanzados
Esta vez tenemos también como output una tabla con una 2 columnas, la primera indica el siglo y la segunda indica el número de sets que se han lanzado en cada siglo
Para saber el porcentaje de los sets realizados en el siglo 21 que tenían trenes como temática principal utilizaremos un CTE de la consulta «siglo21_porTematica», en la que filtraremos los datos únicamente del siglo 21 y agruparemos el «Número_total_de_sets_lanzados» por su «Temática» como ya hemos visto anteriormente; para añadir una tabla de Totales que nos servirá para calcular el porcentaje en el siguiente CTE en el que filtraremos únicamente la temática de Trenes.
Por lo tanto en este output tendremos una tabla con un único registro que indica el «Número_total_de_sets_lanzados» y el «Porcentaje_del_total» únicamente de la temática «Train»
Para saber cúal fue la temática más popular de cada año del siglo 21 utilizaremos un CTE de una primera consulta que, básicamente, devuelve el número de sets lanzados por cada temática y por cada año pero con una nueva columna creada con el método ROW_NUMBER() que crea un ranking de temáticas por cada año en el siglo 21. En el CTE filtraremos simplemente el top 1 de la columna «Ranking» para que nos devuelva la temática con más sets lanzados de cada año
Tenemos como output final una tabla con los años del siglo 21 en orden descendente y su temática más popular en términos de número de sets lanzados
Para responder a esta pregunta utilizaremos de nuevo un CTE pero esta vez con una serie de tablas relacionadas entre sí para calcular la «Cantidad_de_piezas_lanzadas» de cada color.
Como output de esta última questión tenemos una tabla con una columna con cada uno de los colores y otra que indica la cantidad de piezas lanzadas de cada color en orden descendente
