Optimice la gestión de dimensiones, la carga de tablas de hechos y la agregación con el modo pushdown en Astera Centerprise

By | 2019-09-26T06:01:36+00:00 2nd septiembre, 2019|

La gran mayoría de las empresas dependen de sistemas transaccionales de registro para impulsar sus procesos comerciales. Los sistemas de procesamiento de transacciones en línea (OLTP) entregan datos actuales y predefinidos a los usuarios y, como tales, solo admiten un número limitado de funciones de procesamiento de datos que involucran actualizaciones, eliminaciones y solicitudes de inserción, que generalmente se aplican a nivel de registro.

Cuando se trata de recopilar información estratégica e inteligencia para la toma de decisiones, la minería de datos y la planificación, se requiere un sistema de procesamiento analítico más completo. Este es el papel que cumplen los almacenes de datos. Estos sistemas recopilan datos actuales, históricos y de archivo de fuentes transaccionales dispares y admiten una variedad de operaciones de consulta complejas y en constante cambio, la mayoría de las cuales se aplican a grandes volúmenes de transacciones.

Ingeniería de su EDW en Centerprise 8.0

Sin embargo, los almacenes normalizados rara vez se emplean en la práctica, ya que requieren docenas, si no cientos, de tablas para unir para proporcionar una visión comprensible de un proceso de negocio. Dicha arquitectura impacta significativamente el rendimiento de las consultas y, por lo tanto, no es adecuada para ninguna empresa basada en datos.

Como resultado, los almacenes de datos más modernos almacenan datos en forma de tablas desnormalizadas que muestran los hechos (valores medibles) y las dimensiones (contexto) de cada proceso de negocio en un modelo dimensional. El modelo dimensional agrupa la información del negocio en diferentes categorías, lo que facilita la recuperación de datos y, por lo tanto, el rendimiento de la consulta.

In Centerprise 8.0, hemos introducido una gama de características que permiten a los usuarios extraer y cargar datos de sus sistemas transaccionales en una arquitectura de almacenamiento de datos optimizada. En este blog, lo guiaremos paso a paso a través de estas capacidades y le mostraremos cómo puede integrarlas en sus operaciones.

Ingeniería inversa de un sistema fuente

El primer paso para construir una arquitectura de depósito de datos es crear un modelo OLTP que contenga todos los sistemas transaccionales relevantes. En Astera Centerprise, esto se logra a través de una función de ingeniería inversa que se utiliza para crear automáticamente una vista normalizada de todas las tablas seleccionadas en una base de datos de origen.

Higo. 1

Desormalizar el OLTP

Después de configurar las fuentes transaccionales, se pueden desnormalizar para mejorar la recuperación de datos y, en última instancia, el rendimiento de la consulta en el OLAP (sistema de procesamiento analítico en línea). Para hacerlo, arrastraremos y soltaremos tablas con teclas coincidentes entre sí.

En la siguiente captura de pantalla, la tabla CustomerCategories se fusiona con las tablas Clientes basadas en la clave CustomerCategoryID común. El contorno rojo indica que es posible una fusión.

Arrastrar y soltar fusionando en el OLTP

Una vez completada la desnormalización, podemos comenzar a construir el modelo OLAP que servirá como destino para estos datos transaccionales.

Modelo de datos relacionales normalizados (OLTP) completado

Construyendo el OLAP

Para comenzar a construir un OLAP, navegue a la ventana Explorador de proyectos y agregue un nuevo modelo de datos al almacén de datos.

Higo. 2

Nuevamente, use la función de ingeniería inversa para configurar la estructura para OLAP. En este caso, hemos seleccionado la tabla Stock_Item, Empleado y Cliente para importar al modelo de datos. También hemos creado una nueva tabla de ventas utilizando el generador de entidades en la caja de herramientas. Cada entidad también debe definirse como una tabla de hechos, una tabla de hechos agregados (aditivos) o una tabla de dimensiones, como se muestra a continuación.

Modelo dimensional que muestra el proceso de ventas (OLAP)

A continuación, las claves sustitutas (identifican de forma única cada versión del registro) y las claves comerciales (un valor de identificación que se asigna en los sistemas transaccionales generalmente basados ​​en la lógica comercial interna) se asignarán a los campos apropiados en el generador de diseño para cada entidad. Las claves sustitutas son generalmente valores enteros pequeños que son mucho más simples de escanear que las claves comerciales que pueden incluir caracteres o incluso una combinación de diferentes tipos de datos. Como resultado, estos valores numéricos contribuyen a un rendimiento de consulta más rápido.

Creador de diseño de dimensiones de artículos en stock

Implementación de dimensiones que cambian lentamente

En este punto, los usuarios deberán identificar cómo se tratan las actualizaciones, eliminaciones y adiciones en sus tablas de dimensiones.

Actualmente, todos los datos dentro de las tablas de dimensiones son de naturaleza actual, por lo que no habrá registro de los cambios realizados en los atributos clave dentro de las entidades. Si se requiere información histórica para estos atributos, se debe asignar un tipo apropiado de dimensión de cambio lento (SCD) a los campos relevantes en el generador de diseño.

Centerprise admite múltiples Técnicas de manejo de SCD, como se muestra en la tabla a continuación.

SCD Tipo 1Muestra solo el valor más actual para cada atributo. Los datos históricos se sobrescriben.
SCD Tipo 2Cualquier cambio en el atributo de origen se registra como una nueva fila en la tabla de destino. También se agregan a la tabla nuevos campos para la fecha de inicio efectiva y la fecha de vencimiento. Esto garantiza que los datos históricos para cada período de tiempo estén disponibles como referencia.
SCD Tipo 3Conserva el valor actual de cada atributo, así como el valor anterior en un campo adicional. Las tablas de tipo tres también pueden incluir un campo adicional para mostrar la fecha en que se asignó el valor actual.
SCD Tipo 6Se agrega una nueva fila para cada cambio en el atributo de origen. Las filas adicionales incluirán campos para la fecha de vigencia, la fecha de caducidad y un indicador para mostrar si un valor es actual o caducado.

Ingeniería avanzada de la estructura OLAP

Ahora, todos los cambios realizados en las entidades en el OLAP deben actualizarse en el OLTP para garantizar que los datos puedan fluir sin problemas entre las dos bases de datos. La función de ingeniero de avance se utiliza para realizar esta tarea.

Higo. 3

Con esta opción Centerprise convertirá el esquema OLTP y las relaciones entre entidades presentes en un script cuando se aplique, genera y suelta estas tablas en el OLAP.

Reenviar tablas de ingeniería a OLAP

Completar tablas de hechos y dimensiones en OLAP

Con la estructura OLAP en su lugar, el siguiente paso es completar las tablas de dimensiones y hechos en el almacén de datos. Para hacerlo, cada entidad en el OLAP deberá asignarse a una fuente transaccional correspondiente en el OLTP. Esto se realiza a través de la ventana de configuración de carga.

Ventana de configuración de carga en Astera Centerprise

En este caso, la tabla Cliente en OLTP se usa como la entidad de origen para la tabla Cliente en OLAP. Cada campo se asigna a un atributo en la tabla OLTP. Si hay un campo coincidente disponible, como con los campos Customer_ID o Customer, se puede usar la opción de mapeo directo. Sin embargo, en algunos casos, es posible que se deban unir y agregar múltiples campos en la tabla fuente para producir resultados relevantes para el OLAP. Estas transformaciones se pueden aplicar utilizando la opción de mapeo de expresiones. En la captura de pantalla anterior, el campo Bill_To_Customer en el OLAP se asigna a una tabla consolidada que muestra los nombres de los clientes para cada ID de facturación.

Una vez completada la configuración de una dimensión, el usuario puede verificar que todo el mapeo realizado sea correcto haciendo clic en el botón verde de verificación en la parte superior de la pantalla de configuración de carga.

Higo. 4

Una vez que se han asignado los campos obligatorios, se generan archivos de carga para cada entidad. Un archivo de carga es esencialmente un flujo de datos que contiene lógica de mapeo para una dimensión. Cuando se ejecutan estos flujos de datos, los datos se toman del OLTP y se procesan a través de las transformaciones necesarias antes de cargarse en la tabla de dimensiones relevante en el OLAP. Aquí, las claves sustitutas, las claves comerciales y los tipos SCD apropiados se asignarán a los campos de datos de entrada tal como se definieron durante la etapa de modelado.

Flujo de datos generado automáticamente para la dimensión del cliente

Pushdown Optimization en el Data Warehouse

Por su naturaleza, los proyectos de almacenamiento de datos implican la transferencia de grandes volúmenes de datos. Como tal, simplemente mover estos datos de una base de datos de origen a un servidor intermediario y luego un destino puede ser un proceso extremadamente intensivo en recursos. En este caso, también hemos agregado múltiples transformaciones ETL, que agregarán una carga significativa al servidor intermediario.

Cuando los datos existentes dentro del OLTP se modifican y los registros deben actualizarse en el OLAP, estos flujos de datos deberán ejecutarse nuevamente. Dependiendo de la escala de los cambios realizados, esto podría requerir que se concilien o sobrescriban miles de registros, mientras que se crean nuevas filas para los campos marcados SCD2 y SCD6. Una vez más, todas estas operaciones colocarán una carga sustancial en el servidor ETL.

In modo de optimización pushdown, Centerprise puede aprovechar el poder de procesamiento inherente de un RDBMS para realizar todas las extracciones, cargas y transformaciones requeridas en un arquitectura del almacén de datos. Cuando se despliega pushdown, estas tareas se convertirán en código SQL nativo de la base de datos que se ejecuta en el origen y el destino. Además, cualquier reconciliación, actualizaciones o inserciones de SCD se convertirán en declaraciones SQL consolidadas que se pueden realizar de manera mucho más eficiente en la base de datos.

Para habilitar el modo pushdown en los flujos de datos de configuración de carga, marque la opción adecuada en el menú contextual de la carpeta de flujos de datos.

Higo. 5

Como resultado, los tiempos de ejecución del flujo de datos mejorarán considerablemente, mientras que los requisitos de memoria y procesamiento asociados para el almacén de datos se reducirán significativamente.

Una vez que todas las tablas de dimensiones se han llenado y actualizado, y las claves sustitutas se han asignado a todos los registros dentro de la dimensión, la tabla de hechos finalmente se puede cargar.

Las tablas de hechos consisten en medidas y claves comerciales extraídas de diferentes partes de un proceso comercial (en este caso, Ventas). Cada vez que se necesita recuperar el contexto de un hecho, estas claves comerciales se comparan con sus claves sustitutas relacionadas en las tablas de dimensiones. Esto se lleva a cabo mediante una transformación de búsqueda de dimensiones.

Tabla de búsqueda de dimensiones para hechos de ventas

Como puede ver en la captura de pantalla anterior, la carga de tablas de hechos implica múltiples uniones entre tablas de dimensiones. Cuando considera que las tablas de hechos generalmente contienen millones de registros, el alto costo de realizar esta operación es claro. Por supuesto, todo el proceso también tendrá que repetirse de manera consistente ya que los datos transaccionales se actualizan constantemente.

Cada vez que se llena la tabla de hechos, la transformación de búsqueda también necesitará hacer una referencia cruzada de cada clave comercial con la tabla SCD relevante y convertirla en una clave sustituta. Si la tabla de dimensiones es particularmente grande, o se han realizado varios cambios en los registros de origen, la búsqueda puede llevar mucho tiempo y recursos. Si bien el almacenamiento en caché puede reducir el tiempo de carga para la transformación, colocará una carga adicional en los recursos del servidor.

Con esto en mente, ejecutar la carga de la tabla de hechos en el modo de optimización pushdown puede ser extremadamente ventajoso ya que todos estos procesos se convertirán en una sola declaración SQL que extrae y transforma los datos en la base de datos de origen antes de cargarlos en el OLAP.

Aplicación de tablas de hechos agregados a arquitecturas EDW complejas

La granularidad de un hecho se basa en el nivel de detalle que presenta sobre un proceso de negocio. Por ejemplo, la tabla de hechos en el ejemplo anterior muestra las fechas de entrega diariamente en lugar de las entregas totales agregadas durante un mes o un trimestre.

Si bien este nivel de detalle es suficiente para un sistema transaccional, donde los usuarios generalmente solo necesitan recuperar datos de un solo cliente o examinar información comercial en un solo día, en un entorno de depósito de datos, las consultas son mucho más complejas y requieren que se realicen cálculos en cientos o miles de conjuntos de registros. Si estas consultas se aplican a las tablas de hechos de grano bajo, tomarán una cantidad considerable de tiempo en ejecutarse, especialmente si el servidor ETL ya está bajo alta utilización.

Una tabla de hechos agregada optimiza los tiempos de ejecución de consultas al proporcionar resúmenes precalculados de los hechos de grano más bajo derivados de la tabla de hechos. En la tabla de ventas anterior, podemos agregar facturas de ventas individuales para cada fecha en un ventas totales sin impuestos cifra que devolverá muchos menos registros.

Sin embargo, este es un ejemplo relativamente básico. A medida que aumenta el número de dimensiones en el almacén de datos, también lo hará el número de posibles tablas agregadas que se pueden implementar. Además, a medida que aumenta el nivel de granularidad, se ocuparán más filas para cada consulta (escasez), lo que aumentará aún más los requisitos de almacenamiento en el servidor ETL.

El siguiente modelo de datos muestra un almacén de datos más complejo que presenta múltiples dimensiones vinculadas a una tabla de hechos de Venta.

Modelo OLAP complejo

En este caso, se ha creado una tabla de hechos SalesAggregate para proporcionar una vista resumida de la función de ventas derivada del sistema de procesamiento de Facturas. La vista calculada muestra una cuenta corriente de las ventas totales (incluyendo y excluyendo el impuesto a las ventas), así como un promedio móvil de artículos secos y congelados actualmente en stock.

Ventas totales agregadas y artículos en stock

Creador de diseño para tabla de hechos agregados

Estos datos agregados son relevantes para una variedad de cálculos llevados a cabo durante los informes y análisis. Al ejecutar agregaciones en la fuente en modo de optimización pushdown, los usuarios pueden reducir la carga en el servidor ETL y garantizar que los tiempos de ejecución de las consultas estén completamente optimizados.

Obtenga más información sobre el almacenamiento de datos en Centerprise

El próximo lanzamiento de CenterpriseEl módulo Data Warehouse Builder proporcionará a los usuarios empresariales una gama de herramientas para planificar, diseñar y completar sus propias arquitecturas de datos de toda la organización. Al integrar la optimización pushdown con estas características, nuestro objetivo es garantizar que nuestros clientes obtengan el mayor rendimiento posible durante estas operaciones de datos de alto volumen.

Para una visión más profunda de los beneficios de rendimiento de ELT, esté atento a nuestros próximos blogs de evaluación de desempeño o programe una visita en persona consulta con nuestro equipo de ventas