Cuando nos enfrentamos al reto de desarrollar un nuevo sistema Business Intelligence, una de las principales decisiones a tomar pasa por la elección de la solución tecnológica que emplearemos en su creación, la cual, en el ejemplo que vamos a construir durante el presente y próximos artículos de esta serie, estará basada en varias herramientas integrantes de la plataforma Microsoft BI: SQL Server como motor de datos; SQL Server Integration Services (SSIS) para los procesos de ETL (Extracción, Transformación y Carga); SQL Server Analysis Services Tabular (SSAS Tabular) para el diseño del modelo de análisis (dimensiones e indicadores); y finalmente, Power BI Desktop para la presentación de resultados al usuario.
El ejemplo consistirá en la creación de un modelo tabular, mediante el cual analizaremos los datos de ventas de los productos de una compañía, pero con la particularidad de que la tabla de hechos estará formada por varios millones de registros, lo que nos permitirá explorar y evaluar la respuesta del motor de SSAS Tabular en el manejo de una fuente de datos de gran volumen.
Tablas de dimensión. Clientes y productos de AdventureWorksDW2016
La base de datos, a partir de la cual crearemos nuestro modelo tabular, estará parcialmente basada en AdventureWorksDW2016, ya que de la misma utilizaremos las tablas DimCustomer, DimProduct, DimProductSubcategory y DimProductCategory, para crear las dimensiones Cliente y Producto de nuestro modelo.
Si tenemos en cuenta que el requerimiento principal en el desarrollo del ejemplo pasa por disponer de una tabla de hechos de gran volumen, al revisar las tablas de ventas de esta base de datos: FactInternetSales, FactResellerSales, etc., comprobaremos que no cumplen con las expectativas en lo que a cantidad de registros se refiere, lo que nos obliga a buscar una fuente de datos alternativa.
Tabla de hechos. Padrón de población
Ante la problemática con el tamaño de la tabla de hechos, para poder crear la misma, de forma que contenga un volumen de varios millones de registros, al igual que ya hicimos en los artículos publicados en este mismo blog sobre generación de fechas aleatorias y creación de un modelo poblacional con Power BI, volveremos a recurrir a los Ficheros de microdatos, publicados por el Instituto Nacional de Estadística (INE), dentro de la sección de Estadística del Padrón continuo de habitantes.
Descargando todos los archivos disponibles, que en el momento de publicar este artículo abarcan el periodo comprendido entre los años 2008 a 2018, tendremos a nuestra disposición una cifra ligeramente superior a los 500 millones de registros.
VentasAnuales. La base de datos del modelo
El siguiente paso consistirá en crear, desde SQL Server Management Studio (SSMS), una nueva base de datos en SQL Server, a la que daremos el nombre VentasAnuales.
En el cuadro de diálogo de creación de la base de datos, dentro del apartado Options, configuraremos la propiedad Recovery model como Simple, para evitar que durante la carga de los archivos de datos en las tablas, se produzca un crecimiento excesivo del registro de transacciones.
A continuación, ejecutaremos el siguiente script para crear las tablas de la base de datos.
CREATE TABLE Cliente ( ClienteID int NOT NULL, Nombre varchar(50) NULL, Apellido varchar(50) NULL, EstadoCivil varchar(20) NULL, Sexo varchar(20) NULL, CONSTRAINT PK_Cliente PRIMARY KEY (ClienteID) ) WITH (DATA_COMPRESSION = PAGE); CREATE TABLE Producto ( ProductoFilaID int IDENTITY(1,1) NOT NULL, CategoriaID int NULL, Categoria varchar(50) NULL, SubCategoriaID int NULL, SubCategoria varchar(50) NULL, ProductoID int NULL, Producto varchar(50) NULL, Precio money NULL, CONSTRAINT PK_Producto PRIMARY KEY (ProductoFilaID) ) WITH (DATA_COMPRESSION = PAGE); CREATE TABLE Fecha ( Fecha date NOT NULL, AnualidadID int NULL, Anualidad varchar(4) NULL, MesID int NULL, Mes varchar(2) NULL, MesNombre varchar(20) NULL, CONSTRAINT PK_Fecha PRIMARY KEY (Fecha) ) WITH (DATA_COMPRESSION = PAGE); CREATE TABLE PadronOrigen ( Datos varchar(21) NULL ) WITH (DATA_COMPRESSION = PAGE); CREATE TABLE Ventas ( ClienteID int NULL, ProductoFilaID int NULL, Fecha date NULL, Importe money NULL ) WITH (DATA_COMPRESSION = PAGE); CREATE CLUSTERED COLUMNSTORE INDEX ix_Ventas ON Ventas;
Carga y transformación de datos con SSIS. Creación del proyecto
Tras la creación de la base de datos y sus tablas, la siguiente operación pasa por cargar en estas últimas los datos procedentes de las fuentes de origen, utilizando para ello un paquete SSIS. Una vez iniciado SQL Server Data Tools para Visual Studio 2017 (SSDT – Visual Studio), crearemos un proyecto de tipo Integration Services con el nombre VentasAnualesCargarDatos.
El proyecto recién creado incluye, de manera predeterminada, un paquete que renombraremos como pckImportarDatos, haciendo clic derecho en el mismo y seleccionando la opción Rename.
Creación de conexiones a orígenes de datos en SSIS
En primer lugar, crearemos las conexiones a los orígenes entre los cuales realizaremos la transferencia de datos, y que serán utilizadas por las tareas que posteriormente definamos en el paquete.
En el panel Connection Managers, situado en la parte inferior del diseñador del paquete, haremos clic derecho y seleccionaremos la opción New OLE DB Connection.
Seguidamente se abrirá la ventana Configure OLE DB Connection Manager, cuya finalidad es la administración de las conexiones a los diferentes orígenes de datos existentes. En este ejemplo aparece vacía, ya que todavía no se ha creado ninguna conexión.
Haciendo clic en New, abriremos la ventana para crear una nueva conexión, en la que introduciremos tanto el nombre del servidor\instancia en la que se encuentra la instalación de SQL Server, como la base de datos AdventureWorksDW2016. Para comprobar que todos los valores son adecuados haremos clic en Test Connection.
Aceptando los valores de esta ventana, la conexión será creada con un nombre asignado automáticamente por Visual Studio, que cambiaremos en la ventana de propiedades por cnAdventureWorksDW2016.
Repitiendo los anteriores pasos crearemos otra conexión, esta vez para la base de datos VentasAnuales.
Preparación preliminar de las tablas con SSIS
Como siguiente paso, necesitamos inicializar ciertas tablas de la base de datos mediante un truncado, por si fuera necesario repetir desde cero el proceso de carga. Emplearemos para ello una tarea de tipo Ejecutar SQL (Execute SQL Task), que arrastraremos desde la caja de herramientas (SSIS Toolbox) hasta el área de diseño del paquete.
Después de cambiar el nombre de esta tarea por sql_TruncarTablas, haremos clic derecho en la misma, eligiendo la opción de menú Edit; con ello, abriremos la ventana Execute SQL Task Editor, para editar sus propiedades.
En la propiedad Connection seleccionaremos la conexión cnVentasAnuales creada recientemente; mientras que en SQLStatement haremos clic en el botón de puntos suspensivos, situado en un extremo, para abrir la ventana Enter SQL Query, donde escribiremos las siguientes sentencias SQL.
TRUNCATE TABLE Cliente TRUNCATE TABLE Producto TRUNCATE TABLE Fecha TRUNCATE TABLE Ventas
Guardaremos los cambios aceptando estas ventanas de edición, y ejecutaremos la tarea para comprobar su correcto funcionamiento, haciendo clic derecho en la misma y seleccionando la opción Execute Task. El diseñador entrará en modo de ejecución, mostrando el progreso y resultado en las pestañas Control Flow y Progress.
Finalizada la ejecución, saldremos de la misma mediante el menú Debug | Stop Debugging de Visual Studio, para volver al modo de diseño y continuar el desarrollo del proyecto SSIS; pero dejaremos esta parte para la siguiente entrega, en la cual abordaremos la carga de la tabla de dimensión dedicada a contener los datos de los clientes a los que se realizan las ventas de productos.
Deja un comentario