Cargando Tablas de Dimensión y Tabla de Hechos

Cargando Tablas de Dimensión

La transformación y carga de una dimensión es acerca del rastreo del actual y algunas veces histórico de atributos asociados en una tabla de dimensión. La figura muestra las dimensiones relacionadas a la tabla Sales Quota Fact en la base de datos AdventureWorksDW2014 (llamado FactSalesQuota). El objetivo es procesar los datos de las tablas de origen en las tablas de dimensión.

 

En este ejemplo, observe que cada dimensión (DimEmployee, DimSalesTerritory y DimDate) tiene una llave sustituta llamada Dimension Key, así también como una llave candidata llamada Dimension AlternateKey. La llave sustituta es el concepto más importante en el data warehousing, porque habilita el rastreo del historial de cambios y optimiza las estructuras para el rendimiento. Las llaves sustitutas son a menudo columnas identity auto incrementales que son contenidas en la tabla de dimensión.

La ETL de dimensiones tiene varios objetivos, cada uno de los cuales es revisado en los pasos de este post para cargar las tablas DimSalesTerritory y DimEmployee, incluyendo los siguientes:

Ø  Identificar las llaves de origen que identifican únicamente un registro de origen y que se asignarán a la Alternate Key

Ø  Realizar cualquier Transformación de Datos para alinear los datos de origen a las estructuras de dimensión

Ø  Manejar los diferentes tipos de cambios para cada columna de origen y agregar o actualizar los registros de dimensión

SSIS incluye una transformación integrada llamada la Slowly Changing Dimension (SCD) para asistir en el proceso. Esta no es la única transformación que puede utilizar para cargar una tabla de dimensión, pero la utilizará en estos pasos del post para lograr la carga de la dimensión.

Cargando una Tabla de Dimensión Simple

Muchas tablas de dimensión son como la dimensión Sales Territory porque contienen sólo unas pocas columnas, y el rastreo del historial no es necesario para alguno de los atributos. En este ejemplo, la tabla DimSalesTerritory es originado de la tabla [Sales].[SalesTerritory], y cualquier cambio del origen a cualquiera de las tres columnas será actualizada en la tabla de dimensión. Estas columnas son referidas a cómo cambiar atributos de dimensión, porque los valores pueden cambiar.

1.    Para comenzar a crear el ETL para la tabla DimSalesTerritory, crear un nuevo proyecto Integration Services, luego de cargarlo, renombre el paquete por defecto a ETL_DimSalesTerritory.dtsx.

2.   Para que sean extraídos los datos de la base de datos AdventureWorks2014 y se carguen los datos en la base de datos AdventureWorksDW2014, crear dos conexiones de proyecto OLE DB a estas bases de datos llamadas AdventureWorks2014 y AdventureWorksDW2014, respectivamente.

3.    Arrastre una nueva tarea Data Flow de la Toolbox SSIS en el Control Flow y navegue hasta el diseñador Data Flow.

4.    Arrastre un componente Origen de OLE DB en el Data Flow y haga doble clic en el nuevo origen para abrir el editor. Configure la lista desplegable OLE DB Connection Manager para utilizar la base de datos AdventureWorks2014 y dejar la  selección Data Access Modes como "Table or View". En la lista desplegable "Name of the Table or the View", seleccione [Sales].[SalesTerritory], como es mostrada en la Figura.

5.    En la página de propiedades Columns (ver figura), cambie el valor Output Column para la columna TerritoryID a SalesTerritoryAlternateKey, cambie la columna Name a SalesTerritoryRegion, y cambie la Output Column para la columna Group a SalesTerritoryGroup. También, desactive todas las columnas bajo SalesTerritoryGroup porque no son necesarios para la tabla DimSalesTerritory.

     

6.    Haga clic en OK para guardar los cambios y luego arrastre una transformación Lookup en el Data Flow y conecte la ruta de datos azul desde el origen OLE DB sobre la Lookup.

7.   En la página de propiedades General, mostrada en la figura, edite la transformación Lookup como sigue: deje el ajuste Cache Mode en Full Cache, y deje el ajuste Connection Type a OLE DB Connection Manager.

 

8.  En la página de propiedades Connection, establezca la lista desplegable OLE DB Connection Manager a la conexión AdventureWorks. Cambie la lista desplegable "Use a Table or a View" a [Person].[CountryRegion].

9.  En la página de propiedades Columns, arrastre la CountryRegionCode de la lista Available Input Columns a la columna correspondiente en la lista Available Lookup Columns, luego, seleccione la casilla de verificación al lado a la columna Name en la misma lista de columnas. Renombre la columna Output Alias de la columna Name a SalesTerritoryCountry, como se muestra en la figura.

 

10.  Seleccione OK en el Lookup Transformation Editor para guardar los cambios. En este punto del proceso, ha realizado algunos pasos iniciales simples para alinear los datos de origen con la tabla de dimensión de destino. Los próximos pasos son el núcleo del procesamiento de la dimensión y se utiliza la transformación SCD.

11.  Arrastre una transformación Dimensión de Variación Lenta de la Toolbox SSIS en el Data Flow y conecte la salida de la ruta de datos azul de la Lookup a la transformación Dimensión de Variación Lenta. Cuando suelta la ruta sobre la transformación SCD, se le pedirá que seleccione la salida de Lookup. Seleccione Salida de Entradas Coincidentes de Búsqueda de la lista desplegable y luego haga clic en OK.

12.  Para invocar el asistente de SCD, haga doble clic en la transformación, que abrirá una pantalla de bienvenida del asistente. Proceder a la segunda pantalla, haga clic en Next.

13.  La primera entrada del asistente requiere identificar la tabla de dimensión a la que los datos de origen se refieren. Por lo tanto, escoja AdventureWorksDW2014 como la Connection Manager y luego elija [dbo].[DimSalesTerritory] como la Table or View, que mostrará automáticamente las columnas de la tabla de dimensión en la lista, como es mostrada en la figura. Para el SalesTerritoryAlternateKey, cambie el Key Type a Business key. Dos propósitos son servidos aquí:

Ø  Uno, se identifica la llave candidata (o business key) de la tabla de dimensión y que columna de entrada coincide. Esto será utilizado para identificar las coincidencias de la fila entre el origen y el destino.

Ø  Dos, las columnas son enlazadas del origen a los atributos en la tabla de dimensión, que será utilizada en la siguiente pantalla del asistente para identificar el tipo de rastreo de cambios. Observe que las columnas son automáticamente coincididas entre la entrada de origen y la dimensión de destino, porque tienen el mismo nombre y tipo de datos. En otras situaciones, puede que tenga que realizar manualmente las coincidencias.

 

14.  En la siguiente pantalla del asistente SCD, necesita identificar qué tipo de cambio es identificado para cada columna correspondiente. Ya se ha mencionado que todas las columnas son atributos cambiantes para la dimensión DimSalesTerritory; por lo tanto, seleccionar todas las columnas y escoger la opción "Changing Attribute" de Change Type de las listas desplegables, como es mostrado en la figura.

 

Existen tres opciones para el Change Type: Changing Attribute, Historical Attribute, y Fixed Attribute. Un Change Attribute es actualizado si el valor de origen cambia. Para el Historical Attribute, cuando un cambio ocurre, un nuevo registro es generado, y el viejo registro preserva el histórico del cambio. Por último, un Fixed Attribute significa que no deben ocurrir cambios, y el ETL debe ignorar el cambio.

15.  En la siguiente pantalla, titulada "Fixed and Changing Attribute Options", se le pide que elija qué registros desea actualizar cuando un valor de origen cambia. La opción "Fixed Attributes" está en gris, porque no fueron seleccionados atributos fijos en la pantalla anterior. Bajo la opción "Changing Attributes", puede escoger por actualizar la columna Changing Atrribute para todos los registros que coinciden con la misma llave candidata, o puede optar por actualizar sólo el más reciente. No importa en este caso porque, sólo habrá un registro por valor de llave candidata, ya que no hay atributos históricos que podrían causar un nuevo registro. Deja esta casilla desmarcada y pasa a la siguiente pantalla.

16.  La pantalla "Inferred Dimension Members" es acerca del manejo de los registros de los marcadores de posición que fueron agregados durante la carga de tabla de hechos, debido a que un miembro de dimensión no existía cuando la carga de la fact fue ejecutada. Desmarque la casilla de verificación Enabled Inferred Dimension Members y pase a la siguiente pantalla. Los miembros inferidos son cubiertos en la ETL de la dimensión DimEmployee, más adelante en este post.

17.  Dada la simplicidad de la dimensión Sales Territory, esto concluye el asistente, y en la última pantalla simplemente confirma los ajustes que ha configurado. Seleccione Finish para completar el asistente.

El resultado neto del asistente SCD es que generará automáticamente varias transformaciones posteriores, preconfiguradas para manejar los tipos de cambio basados en las llaves candidatas seleccionadas. La figura muestra el Data Flow completo con la Transformación SCD.

Desde que esta dimensión es simple, sólo hay dos salidas. Una salida es llamada Nueva Salida, que insertará nuevos registros de dimensión si la llave candidata identificada del origen no tiene una coincidencia en la dimensión. La segunda salida, llamada Salida de Actualizaciones de Atributos Variables, es utilizada cuando tiene una coincidencia a través de las llaves candidatas y uno o más de los atributos cambiantes no coinciden entre la entrada de origen y la tabla de dimensión. Este comando OLE DB utiliza una sentencia UPDATE para realizar la operación.

Cargando una Tabla de Dimensión Compleja

La ETL de una dimensión a menudo requiere una lógica complicada que causa que las tareas del proyecto de dimensión tomen mayor cantidad de tiempo para el diseño, desarrollo y prueba. Esto es debido al cambio de requerimientos para varios atributos dentro de una dimensión así como el rastreo del histórico, la actualización de registros de miembros inferidos, y así sucesivamente. Por otra parte, con dimensiones más grandes o más complicadas, las tareas de preparación de datos a menudo requieren más lógica y más transformaciones antes que el histórico sea aún manejado en la tabla de dimensiones.

Preparando los Datos

Como un ejemplo de un proceso ETL de dimensión más complicada, en esta sección creará un paquete para la tabla DimEmployee. Este paquete tratará con algunos datos faltantes:

1.    En el proyecto SSIS, crear un nuevo paquete llamado ETL_DimEmployee.dtsx. Puesto que ya ha creado las conexiones de proyectos para AdventureWorks2014 y AdventureWorksDW2014, no necesita agregarlos para el nuevo paquete DimEmployee SSIS.

2.    Crear una tarea Data Flow y agregar un componente de Origen de OLE DB al Data Flow.

3. Configurar el componente Origen de OLE DB para conectar a la conexión AdventureWorks2014 y cambiar el Data Access Mode a SQL Command. Luego, ingrese el siguiente código SQL en la ventana de texto SQL Command (ver figura):

SELECT

e.NationalIDNumber as EmployeeNationalIDAlternateKey

, manager.NationalIDNumber as ParentEmployeeNationalIDAlternateKey

, s.FirstName, s.LastName, s.MiddleName, e.JobTitle as Title

, e.HireDate, e.BirthDate, e.LoginID, s.EmailAddress

, s.PhoneNumber as Phone, e.MaritalStatus, e.SalariedFlag

, e.Gender, e.VacationHours, e.SickLeaveHours, e.CurrentFlag

, s.CountryRegionName as SalesTerritoryCountry

, s.TerritoryGroup as SalesTerritoryGroup

, s.TerritoryName as SalesTerritoryRegion

, s.StateProvinceName

FROM [Sales].[vSalesPerson] s

INNER JOIN [HumanResources].[Employee] e

ON e.[BusinessEntityID] = s.[BusinessEntityID]

LEFT OUTER JOIN HumanResources.Employee manager

ON (e.OrganizationNode.GetAncestor(1)) = manager.[OrganizationNode]

 

4.    Haga clic en OK para guardar los cambios en el componente Origen de OLE DB.

5.    Arrastre una transformación Lookup al Data Flow y conectar la salida de ruta de datos azul desde el Origen de OLE DB a la Lookup. Nombre el Lookup Sales Territory.

6.    Haga doble clic en la transformación Lookup para abrir el Lookup Transformation Editor. En la página General, cambie el menú desplegable llamado "Specify How to Handle Rows with no Matching entries" a "Redirect Rows to No Match Output". Deje el Cache Mode como Full Cache y la Connection Type como OLE DB Connection Manager.

7.    En la página de propiedades Connection, cambie la OLE DB Connection Manager a AdventureWorksDW2014 y luego seleccione [dbo].[DimSalesTerritory] en la lista desplegable de abajo llamada "Use a Table or a View".

8.  En la página de propiedades Columns, una las columnas SalesTerritoryCountry, SalesTerritoryGroup y SalesTerritoryRegion entre las columnas de entrada y las columnas de búsqueda, como se muestra en la figura. Además, seleccione la casilla de verificación junto a SalesTerritoryKey en las columnas de búsqueda para retornar esta columna al Data Flow.

Algunas de las columnas de territorio de ventas en el origen tienen NULLs. TerritoryGroup y TerritoryName tienen una relación funcional de uno-a-muchos. De hecho, suponga que ha conferenciado con los usuarios del negocio, y confirmaron que se puede ver la StateProvinceName y CountryRegionName, y si otro vendedor tiene la misma combinación de valores, puede utilizar su información de SalesTerritory.

 

9.    Para manejar los SalesTerritories faltantes con los requisitos anteriores, agregue una segunda transformación Lookup en el Data Flow, y nómbrelo Get Missing Territories. Luego, conecte la salida de ruta azul de la búsqueda Sales Territory a esta nueva búsqueda. Se le pedirá que elija Output; seleccione Salida de Entradas no Coincidentes de Búsqueda de la lista desplegable, como se muestra en la figura.

10.  Editar la nueva Lookup y configure el componente Origen de OLE DB para conectarse a la conexión AdventureWorks2014. Luego, escoja la opción Use Results of an SQL Query. Escriba el siguiente código SQL en la ventana de texto de SQL Command:

 

SELECT DISTINCT

CountryRegionName as SalesTerritoryCountry

, TerritoryGroup as SalesTerritoryGroup

, TerritoryName as SalesTerritoryRegion

, StateProvinceName

FROM [Sales].[vSalesPerson]

WHERE TerritoryName IS NOT NULL

11. En la página de propiedades Columns, una la SalesTerritoryCountry y StateProvinceName entre la lista de columnas de entrada y de búsqueda y, luego, active las casillas de verificación junto a SalesTerritoryGroup y SalesTerritoryRegion en la lista de búsqueda. Adjunte la palabra "New" a los OutputAlias, como se muestra en la figura.

 

Luego, recreará la búsqueda SalesTerritory de los pasos anteriores para obtener la Sales TerritoryKey para los registros que originalmente tenían datos faltantes.

12.  Añadir una nueva Lookup al Data Flow llamado Readquirir SalesTerritory y conecte la salida de la búsqueda Get Missing Territories (use la Salida de Entradas Coincidentes de Búsqueda cuando se le solicite). En la ficha General, edite la Lookup de la siguiente manera: deje la Cache Mode como Full Cache y Connection Type como OLE DB Connection Manager.

13.  En la página Connection, especifique el administrador de conexión AdventureWorksDW y cambie la opción "Use a Table or a View" en [dbo].[DimSalesTerritory].

14.  En la página de propiedades Columns (mostrada en la figura siguiente), coincida las columnas entre la tabla input y lookup, asegurando que utiliza la columna Region y Group “New”. Coincide a través  de SalesTerritoryCountry, SalesTerritoryGroupNew y SalesTerritoryRegionNew. También retorna la SalesTerritory Key y nombre su Output Alias a SalesTerritoryKeyNew.

15. Haga clic en OK para guardar los cambios de la Lookup y luego arrastre una transformación Union All sobre el Data Flow. Conecte dos entradas en la transformación Union All:

Ø  La Salida de Entradas Coincidentes de Búsqueda de la búsqueda Sales Territory

ØLa Salida de Entradas Coincidentes de Búsqueda de la búsqueda Reacquirir SalesTerritory

16. Editar la transformación Union All de la siguiente manera: localizar la columna SalesTerritoryKey y cambiar el valor <ignore> en el menú desplegable para la entrada que viene de la segunda búsqueda para utilizar la columna SalesTerritoryKeyNew. Esto se muestra en la figura siguiente.

17.  Haga clic en OK para guardar los cambios en la Union All. En este punto, el Data Flow debe ser similar a la que aparece en la figura.

Estos pasos describen cómo manejar una tarea de preparación de datos. Cuando comienza a preparar los datos para su dimensión, es muy probable que tendrá que realizar varios pasos para tenerlo listo para los cambios en los datos de dimensión.

Puede utilizar otras transformaciones SSIS para este fin. Un par de ejemplos podrían incluir utilizar Derived Column para convertir NULLs a UnKnowns y Fuzzy Lookup y Fuzzy Group para la limpieza de datos sucios. También puede utilizar los Data Quality Services de SQL Server 2014 para ayudar a la limpieza de datos.

 

Manipular Cambios de Dimensión Complicados con la Transformación SCD

Ahora está listo para utilizar de nuevo el Asistente SCD, pero para la tabla DimEmployee, necesita manejar diferentes tipos de cambios y miembros inferidos:

1.    Continuar el desarrollo agregando una transformación Slowly Changing Dimension al Data Flow y conectando la salida de ruta de datos de la Unión All a la Transformación SCD. Luego haga doble clic en la transformación SCD para iniciar el Asistente SCD, en la página de Bienvenida dar clic en Next.

2.    En la página Select a Dimension Table and Keys, seleccione el Administrador de conexiones AdventureWorksDW2014 y la tabla [dbo].[DimEmployee].

a. En este ejemplo, no todas las columnas han sido extraídos del origen, y otras columnas de destino están relacionadas a la gestión de cambio de dimensión, que es identificada en el paso siguiente. Por lo tanto, no todas las columnas serán automáticamente emparejadas entre las columnas de entrada y las columnas de dimensión.

b.   Encuentra la EmployeeNationalIDAlternateKey y cambie el Key Type a Business Key.

c.    Seleccione Next.

3.    En la página Slowly Changing Dimension Columns, haga las siguientes designaciones de Change Type, como es mostrado en la figura siguiente:

a.       Fixed Attributes: BirthDate, HireDate

b.  Changing Attributes: CurrentFlag, EmailAddress, FirstName, Gender, LastName, LoginID, MaritalStatus, MiddleName, Phone, SickLeaveHours, Title, VacationHours

c. Historical Attributes: ParentEmployeeNationalIDAlternateKey, SalariedFlag, SalesTerritoryKey

4.    En la página Fixed and Changing Attribute Options, desactive la casilla de verificación bajo la etiqueta Fixed Attributes. El resultado de esto es que cuando un valor cambia para una columna identificada como Fixed Attribute, el cambio será ignorado, y el valor antiguo en la dimensión no será actualizado. Si había marcado esta casilla, el paquete podría fallar.

5.    En la misma página, marque la casilla bajo Changing Attributes. Como se describió anteriormente, esto asegura que todos los registros (actuales e históricos) serán  actualizados cuando un cambio ocurre a un Changing Attribute.

6.    Ahora se le pedirá que configure la Historical Attribute Options, como es mostrada en la figura siguiente. La transformación SCD necesita saber cómo identificar el registro actual cuando una sola Business Key tiene varios valores (recordar que cuando un Historical Attribute cambia, una nueva copia del registro es creada). Hay dos opciones disponibles. Uno de ellos, una sola columna es utilizada para identificar el registro. La mejor opción es utilizar una fecha de inicio y de fin. La tabla DimEmployee tiene una columna StartDate y EndDate; por lo tanto, utilice el botón de opción de la segunda configuración y ajuste la "Start Date Column" a StartDate y la "End Date Column" a EndDate. Por último, establezca la lista desplegable "Variable to Set Date Values" a System::StartTime.

7.    Suponga por este ejemplo que es posible que tenga registros de dimensión faltantes cuando procesa la tabla de hechos. En este caso, un nuevo miembro inferido es agregada a la dimensión. Por lo tanto, en la página Inferred Dimension Members, deje la opción "Enable Inferred Member Support" marcada. La transformación SCD necesita saber cuándo un miembro de dimensión es un miembro inferido. La mejor opción es tener una columna que identifique el registro como inferido; sin embargo, la tabla DimEmployee no tiene una columna para este propósito. Por lo tanto, deje la opción "All Columns with a Change Type are Null" seleccionada.

8.    Con esto concluye la configuración del asistente. Haga clic en Finish para que la transformación SCD puede construir las transformaciones posteriores necesarias en base a las configuraciones. Su Data Flow ahora será similar a la mostrada en la figura siguiente.

Cargando Tabla de Hechos

La carga de la Tabla de hechos es a menudo más sencilla que la ETL de dimensiones, porque una tabla de hechos usualmente implica solo inserciones y, ocasionalmente, actualizaciones. Cuando trata con grandes volúmenes, es posible que necesite manejar inserciones particionadas y hacer frente a actualizaciones en una manera diferente.

En general, la carga de la tabla de hechos implica algunas tareas comunes:

Ø  Preparar los orígenes de datos para tener la misma granularidad como su tabla de hechos, incluyendo tener las llaves de negocio de dimensión y medidas en los datos de origen

Ø  Adquirir las llaves sustitutas de dimensión para cualquier dimensión relacionada

Ø Identificar los nuevos registros para la tabla de hechos (y potencialmente actualizaciones)

La tabla de hechos Sales Quota es relativamente sencilla y le dará un buen comienzo hacia el desarrollo de un ETL de una tabla de hechos:

1.  En su proyecto SSIS para este capítulo, crear un nuevo paquete y renómbrelo ETL_FactSalesQuota.dtsx.

2.    Al igual que los otros paquetes que desarrolló en este post, utilizará dos administradores de conexiones, una para AdventureWorks2014, y el otro para AdventureWorksDW2014, que ya fueron creados anteriormente.

3.    Crear una nueva tarea Data Flow y añadir un componente Origen de OLE DB. Nómbrela Sales Quota Source. Configure el componente Origen de OLE DB para conectarse con el administrador de conexión AdventureWorks2014, y cambie el Data Access Mode a SQL Command, como es mostrado en la figura siguiente. Agregue el código siguiente a la ventana SQL Command Text:

SELECT QuotaDate, SalesQuota, NationalIDNumber as

EmployeeNationalIDAlternateKey FROM Sales.SalesPersonQuotaHistory

INNER JOIN HumanResources.Employee

ON SalesPersonQuotaHistory.BusinessEntityID = Employee.BusinessEntityID

4.   Para adquirir las llaves sustitutas de las tablas de dimensión, utilizará una transformación Lookup. Arrastre una transformación Lookup al Data Flow y conecte la salida de ruta de datos azul del componente Origen de OLE DB sobre la transformación Lookup. Renombre la Lookup a Employee Key.

5.    Haga doble clic en la transformación Employee Key para abrir el Lookup Transformation Editor. En la página de propiedades General, deje el Cache Mode establecido a Full Cache y el Connection Type establecido a OLE DB Connection Manager.

6.    En la página de propiedades Connection, cambie el desplegable OLE DB Connection Manager a AdventureWorksDW2014 e introduzca el siguiente código:

SELECT EmployeeKey, EmployeeNationalIDAlternateKey

FROM DimEmployee

WHERE EndDate IS NULL

Incluyendo el filtro EndDate IS NULL asegura que la llave sustituta del registro de dimensión más actual es adquirida en la Lookup.

7. Cambie a la página de propiedades Columns y asigne el EmployeeNationalIDAlternateKey de las columnas input a las columnas lookup. Luego, seleccione la casilla de verificación junto a la EmployeeKey de la Lookup, como es mostrada en la figura siguiente.

8.    Haga clic en OK para guardar los cambios a la transformación Lookup.

9.    Para el DateKey, una Lookup no es necesaria porque el DateKey es una "llave inteligente", es decir, la llave es un valor entero basado en la misma fecha en formato YYYYMMDD. Por lo tanto, utilizará una Derived Columnda para calcular la DateKey para la tabla de hechos. Añadir una transformación Derived Column al Data Flow y conectar la salida de ruta de datos azul de la Employee Lookup a la transformación Derived Column. Cuando se le solicite, seleccione Salida de Entradas Coincidentes de Búsqueda. Nombre la Derived Column como Date Keys.

10.  Haga doble clic en la transformación Derived Column y agregue las siguientes tres nuevas columnas Derived Column y sus expresiones asociadas, como se muestra en la figura siguiente:

DateKey: YEAR([QuotaDate]) *10000 + MONTH([QuotaDate]) *100 + DAY([QuotaDate])

CalendarYear: (DT_I2) YEAR([QuotaDate])

CalendarQuarter: (DT_UI1) DATEPART(“q”,[QuotaDate])

En este punto de su Data Flow, los datos están listos para la tabla de hechos. Si los datos ya han sido extraídos incrementalmente, por lo que está obteniendo sólo las filas nuevas, puede utilizar un Destino de OLE DB para insertarlo justo a la tabla de hechos. Suponga para este tutorial necesita identificar qué registros son nuevos y que registros son actualizaciones y manejarlos adecuadamente. El resto de los pasos realizan las actualizaciones e inserciones de la fact.

Una Merge Join será utilizada para coincidir registros de entrada de origen a los registros de tabla de hecho actual, pero antes de agregar la Merge Join, necesita agregar una transformación Sort a los registros de origen (un requisito de la Merge Join) y extraer los datos de hecho en el Data Flow.

11.  Agregar una transformación Sort al Data Flow y conectar la salida de ruta de datos azul de la transformación Derived Column a la transformación Sort. Haga doble clic en la transformación Sort para abrir el Sort Transformation Editor y ordenar los datos de entrada por las siguientes columnas: EmployeeKey, CalendarYear y CalendarQuarter, como es mostrado en la figura siguiente. El CalendarYear y CalendarQuarter son columnas importantes para esta tabla de hechos porque identifican el grano de fecha, el nivel de detalle con el que la tabla de hechos es asociada con la dimensión Date. Como regla general, la transformación Sort es una transformación muy poderosa, siempre y cuando esté trabajando con tamaños de datos manejables, en miles y millones, pero no en decenas o cientos de millones (si tiene una gran cantidad de memoria, puede escalar hacia arriba también). Una alternativa al Sort es descrita más adelante.

La figura muestra lo que su Data Flow debe mostrar en este punto.

 

12.  Agregar un nuevo componente Origen de OLE DB al Data Flow y nómbrelo Sales Quota Fact. Configure el Origen de OLE DB para utilizar el administrador de conexión AdventureWorksDW2014 y utilizar el siguiente SQL Command:

SELECT EmployeeKey, CalendarYear

, CalendarQuarter, SalesAmountQuota

FROM dbo.FactSalesQuota

ORDER BY 1,2,3

13.  Debido a que está utilizando una sentencia ORDER BY en la consulta (ordenando por las tres primeras columnas en orden), necesita configurar el componente Origen de OLE DB para saber qué datos están entrando al Data Flow ordenados. En primer lugar, haga clic en OK para guardar los cambios al Origen de OLE DB y luego haga clic derecho en el componente Sales Quota Fact y seleccione Show Advanced Editor.

14.  En la pestaña Input and Output Properties, haga clic en el objeto Salida de Origen de OLE DB en la ventana izquierda; en la ventana derecha, cambie la propiedad IsSorted a True, como es mostrada en la figura siguiente.

15.  Expanda la Salida de Origen de OLE DB de la izquierda y luego expanda la carpeta Output Columns. Realice los siguientes cambios en las propiedades Output Column:

a.       a. Seleccione la columna EmployeeKey y cambie su SortKeyPosition a 1, como es mostrado en la figura siguiente. (Si el orden de sort es descendente, debería escribir un -1 en la SortKeyPosition).

b.    Seleccione la columna CalendarYear y cambie su SortKeyPosition a 2.

c.                  c.     Seleccione la columna CalendarQuarter y cambie su SortKeyPosition a 3.

d.    Haga clic en OK para guardar los cambios en las propiedades avanzadas.

16.  Agregar una transformación Merge Join al Data Flow. En primer lugar, conecte la salida de ruta de datos azul de la transformación Sort sobre la Merge Join. Cuando se le solicite, seleccione la opción input llamada Entrada Izquierda de Combinacion de Mezcla. A continuación, conecte la salida de ruta de datos azul del origen Sales Quota Fact a la Merge Join.

17.  Haga doble clic en la transformación Merge Join para abrir su editor. Verá que las columnas EmployeeKey, CalendarYear y CalendarQuarter ya están unidas entre las entradas. Realice los siguientes cambios, como es mostrado en la figura:

   a.    Cambiar la lista desplegable Join Type a Left Outer Join.

b.    b. Marque las columnas SalesQuota, EmployeeKey, DateKey, CalendarYear, CalendarQuarter y QuotaDate de la lista de entrada Sort y cambie el alias de salida para QuotaDate a Date.

c.   Marque el SalesAmountQuota de la lista de columnas Sales Quota Fact y luego cambie la Output Alias para esta columna a SalesAmountQuota_Fact.

18.  Haga clic en OK para guardar su configuración Merge Join.

19. Su próximo objetivo es identificar cuales registros son nuevas cuotas y cuales son cuotas de ventas cambiadas. Una Conditional Split será utilizada para realizar esta tarea; por lo tanto, arrastre una transformación Conditional Split sobre el Data Flow y conecte la salida de ruta de datos azul de la transformación Merge Join a la Conditional Split. Renombrar la Conditional Split a Identify Inserts and Updates.

20. Haga doble clic en la Conditional Split para abrir el editor y hacer los siguientes cambios, como se muestra en la figura:

a.    Agregar una nueva condición llamada New Fact Records con la siguiente condición: ISNULL([SalesAmountQuota_Fact]). Si la medida del fact es null, indica que el registro del fact no existe por combinar el empleado y la fecha.

b.    Añadir una segunda condición llamada Fact Updates con la siguiente condición: [SalesQuota] != [SalesAmountQuota_Fact].

c.    Cambiar el nombre de salida por defecto a No Changes.

21.  Haga clic en OK para guardar los cambios en la Conditional Split.

22. Agregar un componente Destino de OLE DB al Data Flow y nómbrelo como Fact Inserts. Arrastre la salida de ruta de datos azul de la transformación Conditional Split al Destino de OLE DB. Cuando se le pide que elija una salida del Condicional Split, elija la salida New Fact Records.

23.  Haga doble clic en el destino Fact Inserts y cambie el OLE DB Connection Manager a AdventureWorksDW2014. En la lista desplegable "Name of the Table or View ", seleccione la tabla [dbo].[FactSalesQuota].

24.  Cámbiese a la página de propiedades Mappings y enlazar la columna SalesQuota de la lista Available Input Columns a la SalesAmountQuota en la lista Available Destinations Columns, como se muestra en la figura. Las otras columnas (EmployeeKey, DateKey, CalendarYear y CalendarQuarter) deberían ya coincidir. Haga clic en OK para guardar los cambios en el Destino de OLE DB.

25.  Para manejar las actualizaciones de la tabla de datos, arrastre una transformación OLE DB Command al Data Flow y renómbrela Fact Updates. Arrastre la salida de ruta de datos azul de la Conditional Split sobre la transformación Fact Updates, y cuando se le solicite, seleccione la salida Fact Update de la Conditional Split.

26. Haga doble clic en la transformación OLE DB Command y cambie en la lista desplegable Connection Manager a AdventureWorksDW2014. En la ficha Component Properties, agregue el código siguiente a la propiedad SQLCommand (asegúrese de hacer clic en el botón de puntos suspensivos para abrir una ventana del editor):

UPDATE dbo.FactSalesQuota

SET SalesAmountQuota = ?

WHERE EmployeeKey = ?

AND CalendarYear = ?

AND CalendarQuarter = ?

27.  Cambie a la ficha Column Mappings y asigne la SalesQuota a Param_0, Employee_Key a Param_1, CalendarYear a Param_2 y CalendarQuarter a Param_3, como es mostrada en la figura.

28.  Haga clic en OK para guardar los cambios en la actualizada OLE DB Command. Su ETL de Tabla de Hechos para la FactSalesQuota está completa y debería ser similar a la figura.

Si se prueba este paquete, encontrará que las inserciones fallan. Esto se debe a que la dimensión fecha es poblada hasta el 2006, pero existen varias fechas de 2007 y 2008 que se necesitan para la tabla de hechos. A los efectos de este ejercicio, puede quitar la restricción de llave foránea en la tabla, lo que habilitará a su paquete FactSalesQuota a ejecutarse exitosamente. En realidad, como parte de su ETL, debería crear un script recurrente que pueble la tabla DateDim con nuevas fechas:

ALTER TABLE [dbo].[FactSalesQuota] DROP CONSTRAINT [FK_FactSalesQuota_DimDate]

Estas son algunas consideraciones finales para la ETL de la tabla de hechos:

Ø  Un Merge Join fue utilizado en este caso para ayudar a identificar qué registros fueron actualizaciones o insertados, basados en la coincidencia del origen a la tabla de hechos.

Ø  Para las inserciones y actualizaciones, es posible que desee aprovechar el motor relacional para manejar bien la inserción o la actualización al mismo tiempo. T-SQL en SQL Server soporta una sentencia MERGE que realizará ya sea una inserción o una actualización dependiendo de si el registro existe.

Ø  Otra alternativa a las actualizaciones de la tabla de hechos al OLE DB Command es utilizar una actualización basada en conjuntos. El OLE DB Command funciona bien y es fácil para pequeños volúmenes de datos; sin embargo, su situación puede no permitir actualizaciones por fila. Considere el staging de las actualizaciones a una tabla y luego realizar una actualización basada en conjuntos (a través de una sentencia UPDATE multifila de SQL) para unir la tabla staging a la tabla de hechos y actualizar la cuota de ventas de esa manera.

Ø  Las inserciones son otra área de consideraciones de mejora. Las Tablas de Hechos a menudo contienen millones de filas, por lo que debe buscar formas de optimizar las inserciones. Considere la eliminación de los índices, la carga de la tabla de hechos, y luego volver a crear los índices. Esto podría ser mucho más rápido.

Ø  Si tiene particiones en su lugar, puede insertar los datos en la tabla de hechos particionada; sin embargo, cuando está tratando con grandes volúmenes, la sobrecarga del motor relacional puede inhibir el rendimiento. En estas situaciones, considere cambiar la partición actual con el fin de cargarla por separado, entonces puede cambiar de nuevo en la tabla particionada.

Los miembros inferidos son otro reto para el ETL de tabla de hechos. ¿Cómo manejar una llave de dimensión faltante? Un enfoque incluye el escaneo del origen de la tabla de hechos para las llaves faltantes y la adición de registros de dimensión de miembro inferido antes que la ETL de la tabla de hechos se ejecute. Una alternativa es redirigir la fila faltante cuando la búsqueda no tiene una coincidencia, luego, agregar la llave de dimensión durante el ETL, seguido por traer la fila de regreso al ETL a través de un Union All. Una aproximación final es manejar los miembros inferidos después que la ETL de la tabla de hechos finalice. Necesitaría un stage de los registros que tienen llaves faltantes, agregar los miembros inferidos, y luego reprocesar los registros staged en la tabla de hechos.