Modelo de datos de Power BI para informes empresariales con grandes volúmenes de datos de Oracle -Writeup

¿Quién dijo que Power BI no es una herramienta de BI adecuada para las necesidades de informes empresariales?

Jueves, 9 de mayo de 2019

(PD: no revise la ortografía y la gramática todavía, lo editaré tan pronto como tenga tiempo)

Desafortunadamente, fui ‘yo’ quien dijo que inicialmente con mi Gerente en algún lugar alrededor de finales de año, 2018. Sin embargo, durante los últimos 5-6 meses, pude ver que ‘Power BI’ estaba pasando por una metamorfosis notable y está casi listo ahora para Enterprise BI Reporting y se llama ‘Enterprise BI’ con sus nuevas características Premium. Me gustaría mencionar que la mayoría de sus características Premium son necesarias para publicar el modelo de datos relacionales (similar al modelo tabular de SSRS) como conjuntos de datos en Powerbi.com / Premium Workspace, mientras que podríamos construirlos en Power BI Desktop Pro y habilitar las características requeridas.

Fondo:

­­

Comencé a explorar esta herramienta desde que se anunció que Power BI sería la herramienta de desarrollo de BI empresarial recomendada en MMS BI en el futuro.

En ese proceso, me sorprendió bastante descubrir que esta herramienta ya estaba siendo ampliamente utilizada en nuestro edificio de oficinas de MMS en Richmond por algunos de nuestros usuarios comerciales durante años. Vi que la mayoría de estos usuarios comerciales actualmente están desarrollando sus informes y paneles utilizando SQL nativo (Oracle) / Power BI y ya habían desarrollado algunos paneles excelentes.

No se recomienda crear informes de Power BI que se basen solo en código SQL. Estos informes no se pueden reutilizar siempre que se requieran cambios leves, como agregar nuevas medidas / filtros por parte de los usuarios finales.

(Si estos informes / paneles no se obtienen del almacén de datos MMS EDW, entonces no encuentro ningún defecto en ellos aquí😊). Si se construyen a partir de un modelo de datos de Power BI sólido y bien definido, siempre se pueden editar y los informes Adhoc serán fáciles y amigables para todos los usuarios finales.

También he visto que otros departamentos (los llamo ‘Negocios’ a menudo porque nosotros (TI) trabajamos para sus necesidades de informes) han contratado nuevos desarrolladores que están tratando de extraer SQL de sus informes SAP BusinessObjects anteriores. También están adaptando el SQL para sus necesidades de tablero en Power BI y han configurado sus propios espacios de trabajo premium.

Uno de los requisitos de BI importantes que tenemos (Equipo de Inteligencia Comercial de TI) es reconstruir las Áreas Temáticas Adhoc de OBIEE existentes en conjuntos de datos (modelos) de Power BI para que puedan usarse para las necesidades de informes Adhoc.

Actualmente, hemos construido estas áreas temáticas (mercados de datos) utilizando la ‘herramienta Oracle Business Intelligence’. Todas estas áreas temáticas ad hoc se crean a partir del almacén de datos de Oracle EDW de MMS. Yo diría que tenemos un uso moderado de estas áreas temáticas, excepto para ‘Actividad de operaciones ad hoc’ y ‘Ventas ad hoc’, que son muy consultadas. El tamaño de su modelo también ha aumentado durante el período de tiempo a medida que introdujimos nuevos hechos / dimensiones.

Veo que ni siquiera podría caber en la pantalla de mi monitor externo actualmente ☹ y no sé si todavía es correcto llamarlos modelo dimensional de almacenamiento de datos.

Supongo que debemos dividirlos en modelos más pequeños cuando los reconstruyamos en Power BI en el futuro.

Encuentre el uso actual en las capturas de pantalla a continuación que le darían una idea. Las imágenes no son claras aquí, pero las mostrarían en una demostración.

Mi experiencia frustrante inicial:

Comencé a experimentar con una de las áreas temáticas más pequeñas de OBIEE llamada «Inventario». Vi que solo se consultaron 1300 veces a partir de hoy después de su construcción.

Al principio, no pude encontrar los controladores adecuados para importar tablas de Oracle a Power BI en mi computadora portátil. Inicialmente busqué en Google e instalé componentes ODAC. Sin embargo, tuve que volver a formatear virtualmente mi computadora portátil eliminando mis controladores de cliente de Oracle de 32 bits y las versiones de Toad e instalar todas las versiones de 64 bits.

En primer lugar, tomó horas ‘importar’ cada tabla de dimensión en Power BI y casi 1-2 días para algunas tablas de hechos de tamaño mediano con más de 20-40 millones de filas. Por supuesto, la pantalla estaba congelada y no pude descargarlos por completo, así que compré algo de RAM adicional de BestBuy y lo instalé en mi computadora portátil, pero no funcionó. Nuestro líder de desarrollo, que maneja servidores virtuales, le dio acceso a uno de sus servidores Windows durante una semana y aumentó su tamaño de RAM a 32 GB. Entonces finalmente pude importar algunas tablas.

Requisito:

A partir del día siguiente, comencé a buscar una única solución: cómo manejar grandes cantidades de datos y crear un modelo de BI empresarial a partir de ellos.

Incluso hoy, no encontré ni un ápice de información en ninguna parte de la Red. Si no me cree, «búsquelo» usted mismo. Lo prometo, no encontrará la información necesaria para construir un ‘Modelo de datos complejo usando Oracle’.

Fue entonces cuando comencé a leer el informe técnico de Marco Russo y Alberto Ferrari (SQL Server MVP) (SQL Server Analysis Services, consulte el documento técnico que detalla DirectQuery en SQL Server Analysis Services 2016), He desarrollado algo de esperanza en esta herramienta.

Al usar DirectQuery, la base de datos se comporta como un modelo semántico que traduce las consultas entrantes a la fuente de datos, sin mantener una copia de los datos en una base de datos en memoria (motor VertiPaq).

Comencé a leer blogs relacionados de varios MVP y videos de YouTube (como Curbal, Guy in the Cube, Avi Sigh, Enterprise DNA, RezaCad, Pragmatic Works y muchos más).

Con frecuencia comencé a visitar Edx.org, Courseera.org para cursos de Power BI y descargué suficientes libros de Amazon y exploré casi todos los cursos de Power BI Udemy. Casi me fallan varios motores de búsqueda. 😊

Incluso instalé SQL Server 2016 y Power BI Report Server en mi computadora portátil débil y comencé a explorar varias opciones para tener una experiencia ‘local’ con modelos tabulares.

No necesito hacer ninguna transformación de datos para mi requisito actual, por lo que no se requiere ‘Power Query’ o M ‘aquí.

Realizo consultas en el almacén de datos de EDW Oracle, que es un modelo de base de datos relacional muy bien construido.

Como dije anteriormente, Power BI comenzó a transformarse en una ‘BI empresarial’. Veo algunas ‘funciones de vista previa’ nuevas cada mes que luego se hacen ‘disponibles’ el mes siguiente.

‘Power BI Desktop / Power BI online’ publica sus actualizaciones de software todos los meses. También me encanta su concepto MVP (donde «expertos en tecnología que comparten apasionadamente sus conocimientos con la comunidad»).

Mi búsqueda terminó tan pronto como encontré las soluciones para todos mis requisitos: como manejar grandes cantidades de datos y construir un modelo de BI empresarial. Muy recientemente, Power BI comenzó a concentrarse en Powerbi.com (power Bi online) y mejoró sus características Premium. Trabajé con nuestros líderes de desarrollo de Microsoft y obtuve nuestro grupo Premium Workspace- MMS Power BI hace solo un par de semanas y lo nombré como MMSBI por ahora.

Estas son algunas de las características importantes que se utilizan / se utilizarían en nuestro modelo de datos relacionales, que se crean a partir de ‘datos de Oracle de gran volumen’. Necesitaríamos PREMIUM Workspace aquí, ya que PRO no puede manejar grandes conjuntos de datos.

Característica 1: modelos compuestos :

La primera característica que me llamó la atención fue la Modo compuesto (Modo mezclado) https://docs.microsoft.com/en-us/power-bi/desktop-composite-models

Modo de almacenamiento: Dual, Import y DirectQuery

He utilizado el modo compuesto para crear este modelo de «Área de tema de inventario». Eso significa que he ‘importado’ tablas pequeñas como DIM_PERIOD (6576 filas), DIM_VELOCITY, DIM_BUYER (174 filas), y usé ‘Modelo dual’ para tablas de tamaño mediano como Dim Distribution Center (2368 filas) y ‘Modo de consulta directa’ para tablas grandes como Item Dist Center y Inventory Balance. He utilizado la función COUNTROWS Dax para recuperar el número de filas que se encuentran en la siguiente captura de pantalla.

Para comprender mejor esta función, mire cualquier video de YouTube de Curbal, Radacad o Avi Singh o espere mi demostración 😊

Característica 2: DAX:

Necesitamos usar DAX para crear nuestras tablas y medidas calculadas

DAX significa Expresiones de análisis de datos y es el lenguaje de fórmulas que se usa en Power BI.

Una medida de DAX de ejemplo simple que he usado en mi modelo :

Monto del pedido pendiente =

SUMX (

‘Balance de inventario’,

‘Balance de inventario'[BKORDR_QTY]

* RELACIONADO (‘dItem Dist Center'[DSTRBTN_CNTR_ACQ_COST_AMT] )

)

Podríamos usar el formateador DAX studio / Dax para documentar nuestro modelo: pago https://youtu.be/UX7WYFX8828

Escuché que Power BI pronto lanzará su propio estudio DAX integrado.

Prefiero los libros, ‘Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence’ y ‘Super Charge Power BI: Power BI Is Better When You Learn to Write DAX’, de Amazon para comenzar el viaje de DAX.

Es posible que no necesitemos ‘Habilidades avanzadas de DAX’ para nuestro modelo dimensional aquí, pero si desea verificarlo: –https://www.sqlbi.com/guides/dax/ y sus libros también.

Característica 3: Agregaciones:

Necesitaríamos usar la nueva característica recientemente lanzada de Power BI llamada Agregaciones.

‘Christian Wade’ de Microsoft desbloqueó conjuntos de datos a escala de petabytes en Azure con característica de agregaciones en Power BI podrías ver su video https: //powerbi.microsoft.com/de-de/blog/aggregation-for-petabyte-scale-bi-available-in-the-power-b … aquí.

Si ves este video de Alberto ( https://www.sqlbi.com/tv/aggregation-in-power-bi/), verás que logró esta función. 😊 y dijo que todavía es inmaduro mostrando un pequeño agujero en él. Con suerte, pronto estará cubierto por el equipo de Producto.

Las ‘agregaciones’ pueden reducir drásticamente el costo de desbloquear grandes conjuntos de datos para la toma de decisiones.

No he usado esta función en mi modelo actual de Inventario de Power BI, ya que ya tenemos una ‘Tabla agregada’ creada en el backend (SUM_INVTRY_BAL -E1- cantidades de artículos en cada centro de distribución, agregadas por artículo y días individuales del centro de distribución). probablemente usaría esta característica en todos los demás modelos de datos de Power BI más adelante para manejar un mejor rendimiento de las consultas.

Tenga en cuenta que estas tablas agregadas deben estar en modo ‘importado’ mientras que la tabla de ‘hechos’ relacionada está en ‘modo directo’.

Cuando los ‘Usuarios comerciales’ consultan las agregaciones en su informe, Power BI comprueba primero las ‘agregaciones’ definidas y, si las encuentra, las devolverá.

Es importante que debamos utilizar la opción de actualización de datos optimizada para obtener los datos más recientes para estas agregaciones.

También podría mostrar esta función en mi demostración.

Característica 4: Políticas de actualización de datos incrementales: Actualización incremental en Power BI Premium

La actualización incremental permite conjuntos de datos muy grandes en el servicio Power BI Premium con los siguientes beneficios:

• Las actualizaciones son más rápidas – Solo los datos que han cambiado deben actualizarse. Por ejemplo, actualice solo los últimos cinco días de un conjunto de datos de diez años.
• Las actualizaciones son más confiables – Ya no es necesario mantener conexiones de larga duración a sistemas fuente volátiles.
• Se reduce el consumo de recursos – Menos datos para actualizar reduce el consumo general de memoria y otros recursos.

Tenemos que definir parámetros (parámetros RangeStart y RangeEnd) y definir una política de actualización. Es posible que no habilitemos esta función para tablas que no se importan.

Característica 5: Flujos de datos (está disponible en la versión Premium): Preparación de datos de autoservicio en Power BI:

Esta es una de las características importantes que disminuiría el tamaño del modelo al eliminar la parte ETL del modelo / conjunto de datos. Su Power Query En línea.

Los datos se almacenan como entidades en el Modelo de datos común en Azure Data Lake Storage Gen2. Los flujos de datos se crean y administran en áreas de trabajo de aplicaciones mediante el servicio Power BI

Curbal Video explica claramente el Libro Blanco aquí en este video: https://youtu.be/bkFG8s_9sGE

Todavía estoy esperando esta función en nuestro espacio de trabajo Premium ☹. Actualmente no está habilitado para nosotros

Estos son los pasos de alto nivel para crear un modelo de datos de Power BI desde cero utilizando las nuevas características premium de Power BI mencionadas anteriormente.

Obtener datos

Crear tablas calculadas: https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables

Columnas calculadas creadas: https://docs.microsoft.com/en-us/power-bi/desktop-calculated-columns

Crear relaciones

Crear medidas: https://docs.microsoft.com/en-us/power-bi/desktop-measures

Crear indicadores clave de rendimiento

Crea perspectivas

Crear jerarquías

Seguridad de nivel de fila (RLS) con Power BI Desktop: defina roles y reglas en Power BI Desktop

Publíquelo en Premium Workspace