Como importar datos desde una base a otra… y no morir en el intento (Espero…)

Esto nace de in challenge para un puesto de Data Engineer que participe, me parecio excelente compartirlo ya que es un ejemplo básico y que abarca mucho, no se olviden que podemos agregar algo que ya vimos , que es importar desde EXCEL

Este es el enlace al post de los excel: Importar EXCEL a tu Datawarehouse

Antes de empezar veamos que necesitamos:

Software:

Base de datos:

Para efectos practicos, podemos descargar una base de ejemplo de este link: Adventure Works

Una base de datos propia, yo la llame DataEstrella: en el Repositorio de GIT que agrego mas adelante estan todos los archivos.

Aca podes acceder al GIT

Basicamente lo que vamos a hacer es llevar datos, filtrados de un origen a nuestro datawarehouse y entender un poco el proceso y como hacerlo.

Nuestro modelo de datos es el siguiente:

Aca por diagrama:

Tablas Dimensionales:

SubCategoria

Cateogria

Producto

Clientes

Ahora ¿que es una tabla dimensional?, una tabla DIMENSIONAL es aquella que tiene datos descriptivos de una entidad del negocio; luego va a ser referenciada en una tabla de Hechos por su clave primaria como una clave foranea.

Tabla de Hechos:

Orden Venta

Una tabla de Hechos es aquella que tiene los valores númericos medibles de un proceso de negocio, ademas se conecta con las tablas dimensionales para dar contexto a estos valores númericos.

Una vez que tenemos nuestra base de datos, vamos a crear los Stored Procedures, para empezar a mover los datos, lo ideal es siempre usar una misma convención de escritura:

Podemos usar notación camello, donde las palabras se unen sin espacios y cada palabra expeto la primera empieza con mayúsculas por ejemplo: nombreCliente

PERO!! a mi me gusta empezar siempre con mayúsculas (la mayoría de la veces, HAZ LO QUE YO DIGO NO LO QUE YO HAGO! jajaaj).

En este caso lo que hacemos en nuestros procesos es algo que aprendi. Vamos a utilizar una FechaHasta para validar vigencia del registro, donde el registro en el cual no hay una fecha es el vigente, y la fecha cargada nos indica hasta cuando fuer valido el registros y nos sirve tambien para validar la fecha de actualizacion. Y podemos además tener un historico de cambios.

Esta es una estructura de proceso de ejemplo:

USE [BaseDatos] -- aca marcamos la base de datos donde vamos a crear el proceso
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Sentencia para crear el procedimiento
CREATE PROCEDURE [dbo].[NombreDelProcedimiento]
AS
BEGIN
    SET NOCOUNT ON;
    --Control de errores
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Actualización de registros (cerrar vigencia si cambió algo)
        UPDATE d
           SET d.fechahasta = GETDATE()
        FROM tablaDestino d
        INNER JOIN (
         SELECT Columna1, columna2 .... ColumnaN
  FROM tablaOrigen
        ) X ON d.CampoClave = X.CampoClave
        WHERE d.Columna1Destino <> X.Columna1Origen -- aca busco que columna cambio
        and d.fechahasta is null; -- valido que se trabaje sobre el registro activo

        -- Inserta los datos nuevos
        INSERT INTO tablaDestino (
            Column1Destino,
            Columna2Destino,
            ColumnaNDestino...
            FechaHasta
        )
        SELECT DISTINCT
          Columna1Origen,
          Columna2Origen,
          ColumnaNOrigen...
          ,NULL as FechaHasta -- Aca Forzamos que la fechaHasta sea nula
        FROM tablaOrigen X
        WHERE NOT EXISTS ( -- busco que no exista en la tabla de destino
            SELECT 1
            FROM tablaDestino Z
            WHERE Z.CampoClave = X.[CampoClave]
        );

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;

Veamos como armar un proceso de DTSX para SSIS que podamos usar para gestionar los Stored Procedures que armamos:

Aca es importante el orden, dado que algunas tablas tienen referencias a otras tablas (Foreign Key), y esto debe armar una secuencia de carga, donde empezamos con las tablas dimensionales mas bajas y vamos subiendo hasta llegar a las tablas de hechos finales.

Esta sería una configuración básica:

Luego lo que debemos hacer es desplegar nuestro proyecto:

Paso 1

Paso 2: Seleccionamos SSIS en SQL Server, para trabajar con nuestro servidor ON PREMISE

Paso 3: aca seleccionamos el servidor, y en la ruta conjunto de carpetas del SSIS y apretamos el boton IMPLEMENTAR

OJO CON ALGO!!!! para funcione usemos la autenticacion de windows, SSIS funciona mejora y sin mucho lio con Autenticación de Windows

De esta manera ya tenemos nuestro proceso de integración terminado y desplegado…

Para planificar nuestras ejecuciones podemos usar el SQL Server Agent Job:

Paso 1: Nuevo Job

Paso 2: Creamos el paso a paso a ejecutarse, en este caso llamamos en el paquete el DTSX que hemos creado…

Paso 3: Armamos la planificación, donde indicamos los días y los horarios en donde queremos que se ejecuten nuestros procesos

Una vez que terminamos este paso, nuestro proceso pasara a ejecutarse en el periodo que asi lo definimos.

Asi sin más!! me despido atentamente….