Cómo optimizar el uso de auditoría Dataverse con Power BI
Cómo optimizar el uso de auditoría Dataverse con Power BI

Cómo optimizar el uso de auditoría Dataverse con Power BI

Pues si, arrancamos 2023 por todo lo grande! Con un (terrible) desbordamiento de la capacidad de almacenamiento de auditoría del tenant y, teniendo en cuenta la poca capacidad de análisis de capacidad de auditoría del centro de administración de Power Platform, se presenta un reto de lo más divertido. Es por ello que me decidí a explicarte en este post cómo optimizar el uso de auditoría de Dataverse con Power BI.

Recordamos que en Dataverse tenemos tres tipos de almacenamiento:

  • Base de datos (la mayoría de las tablas)
  • Archivo (logs, ficheros que se almacenan en base de datos..)
  • Registro (principalmente auditorías)

Pero no nos vayamos del hilo porque Microsoft esto te lo cuenta aquí muchísimo mejor de lo que te pueda contar yo. Si no sabes qué es la auditoría y has llegado hasta aquí te recomiendo que veas algún artículo de auditoría en dataverse y descubrirás que me refiero a la auditoría de tablas ya que, dataverse tiene la capacidad de registrar quién ha hecho y qué se ha hecho en las tablas que queramos.

El problema de análisis de auditoría en dataverse

Nosotros tenemos la capacidad de elegir las tablas que auditan y las que no y, en este caso, estamos auditando tablas de más. Podemos hacer un extracto de las tablas que tenemos la auditoría activada mediante un plugin de XRMToolbox llamado MetadataBrowser que nos sacará un excel y, a partir de ahí, vamos a poder filtrar tablas de auditan o no.

El problema principal es que Microsoft no nos cuenta desde el centro de administración de Power Platform en qué estamos gastando nuestra capacidad de auditoría porque nos indica muy bien los gigas y el crecimiento (desbordante) de esta capacidad, pero no en qué tablas lo gastamos. Dentro de este centro de administración encontraréis esta información:

Como veis ya hemos intentado reducir parte, pero seguimos con la capacidad desbordada.. Cuando vamos a borrar la auditoría nos da estas opciones:

Ahora debemos pasar a la acción y debemos desactivar aquellas tablas/campos que provocan este desbordamiento pero no sabemos cuáles. Podemos dar palos de ciego y empezar a desactivar/eliminar auditoría hasta que acertemos o podemos ir un paso más y preparar un análisis y en función de eso decidimos qué hacemos. Está claro que analizar es de sabios y mientras reflexionas cómo podemos analizar los datos de auditoría te dejo este divertido meme:

The Power BI Desktop for first timers | by Elina Dangol | Medium

Planteamiento de la solución de extracción de auditoría

La auditoría que reside en dataverse no es algo a lo que tengamos acceso de una manera sencilla (y sino prueba a utilizar la búsqueda avanzada de dataverse para encontrarlo). Tampoco lo encontramos si nos metemos al conector de dataverse de Power BI… ¿Y entonces? ¿Tentado de borrar tablas a ciegas? 😅🔥

Revisé varios post y este me fue muy útil pero apenas profundiza en lo que debo hacer en Kingwaysoft así que kingwaysoft y yo tuvimos una charla de dos días donde terminé llegando a un acuerdo con él. En el punto en el que estamos es:

  • No puedo leer la auditoría desde el conector Dataverse de Power BI
  • No puedo leer la auditoría mediante la búsqueda avanzada
  • Puedo conectarme desde Kingwaysoft pero no sé cómo.

Lo que vamos a hacer es trasladar la información de auditoría a un blob storage (cuenta de almacenamiento de azure, vaya) y del blob storage vamos a leerlo en Power BI así que vamos a ello!

Let Go GIFs | Tenor

Requisitos previos

Aplicando la solución

Hace mil que no utilizaba kingwaysoft pero seguí los pasos de su página oficial y enseguida di con el resultado. Vamos a crear dos flujos de datos: en uno vamos a extraer el nombre de las tablas cuya auditoría está habilitada en dataverse y después recorreremos todas estas tablas para extraer los datos de auditoría (se nota que ha mejorado desde visual studio 2012 😅). Esta es la vista general de nuestro flujo:

Flujo de datos ‘Extract LogicalName Tables’:

Dentro del dataflow (flujo de datos) de “extract LogicalName Tables” vamos a tener la siguiente estructura:

Venga, vamos a revisar cada uno de estos pasos detenidamente:

1. Extracción de los metadatos de nombre de tabla

Seleccionamos un componente de origen (source) de Dynamics CRM y es importante seleccionar el tipo de origen “Metadata”:

En este caso hemos seleccionado los campos IsAuditEnabled, LogicalName, ObjectTypeCode:

2. Filtramos los datos de los metadatos extraídos

Insertamos dentro del flujo una división condicional y vamos a filtrar aquellas tablas cuyo parámetro “Audit” esté habilitado:

3. Mediante un script guardamos este listado de tablas en una variable

Este quizá es el paso más complicado de este primer flujo de datos ya que, debemos escribir este listado en una variable dentro del paquete de integración para posteriormente recorrerla con una iteracción foreach y, me vas a disculpar, porque quizá hay un método más moderno y mejorado para hacer esto pero la realidad fue que la clave de la solución lo encontré en este post escrito el 1 de enero de 2011 (ojo! que fue escrito un sábado 1 de enero!).

Trato de resumírtelo para que no tengas que pasar por todo esto tu también: Lo primero es la configuración así que busca un icono parecido a esto (Busca un 1 en la figura) en la parte superior derecha de la ventana del dataflow. Después vamos a crear una variable que yo la he llamado “ListEntities”:

Creamos un componente de tipo script y lo conectamos con el paso anterior. Ahora vamos a añadir la variable que hemos creado dentro de una propiedad personalizada del script tal y como te indico aquí:

Y dentro de las columnas de entrada (Input Columns) añade el objectTypeCode:

Ahora toca hacer el copy-paste más grande de tu vida (tranquilo, yo te dejo) porque vamos a pulsar en el botón de “edit script” y nos abrirá otra ventana de Visual studio. El objetivo: copiar la lista de elementos que recibimos en la entrada del script a la variable del paquete y para ello vamos a modificar los dos últimos métodos (PostExecute y un método que se llama algo parecido a Entrada0_processInputRow). En mi caso utilicé el siguiente código:

 List<string> Entities = new List<string>();
    public override void Entrada0_ProcessInputRow(Entrada0Buffer Row)
    {
        /*
         * Add your code here
         */
        
        Entities.Add(Row.ObjectTypeCode.ToString());
    }

Aquí guardo cada objecto en una lista y posteriormente en el método PostExecute guardo esa lista en la variable no antes, sin ordenarlo un poquito claro:

 public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
        Entities.Sort();
        ReadWriteVariables["User::ListEntities"].Value = Entities.ToArray();
    }

¿A que no ha sido tan tan complicado?

Segunda parte del flujo: Guardar la información en un archivo

Salimos del flujo de datos de ‘Extract LogicalName Tables’ y volvemos a la vista de control de flujo. Aquí vamos a insertar un bucle foreach y vamos a conectar el flujo de datos con este bucle:

Bucle foreach

Insertamos un bloque foreach y vamos a configurarlo con la siguiente parametrización:

Y en el apartado Variable Mappings vamos a mapear cada objeto de esta iteración en otra variable llamada ObjectTypeCode.

Dentro del bucle foreach vamos a crear otro dataflow que, en mi caso, lo he llamado “Save Audit To File”. El objetivo es extraer los datos de auditoría de Dataverse e insertarlos en un fichero de blob storage:

Flujo de datos para guardar en el fichero

Este flujo prometo que es más sencillo que el anterior porque constará de dos pasos: uno para extraer los datos de auditoría de Dataverse y otro para insertarlos en el fichero. Puedes verlo en la siguiente imagen:

1. Extraer datos de Dataverse

De igual modo que en el anterior flujo lo que vamos a utilizar es kingwaysoft y esta vez vamos a definir un FetchXML para consultar la auditoría de Dataverse que será el que sigue:

<fetch no-lock="true">
  <entity name="audit">
    <attribute name="auditid" />
    <attribute name="createdon" />
    <attribute name="action" />
    <attribute name="operation" />
     <attribute name="objecttypecode" />
     <attribute name="changedata" />
    <filter>
      <condition attribute="objecttypecode" operator="eq" value="@[User::ObjectTypeCode]" />
    </filter>
  </entity>
</fetch>

Donde el parámetro @[User::ObjectTypeCode] será la variable que hemos definido en el paso anterior (así que si has llegado hasta aquí leyendo en diagonal vuelve 😆):

2. Insertar los datos de auditoría en el fichero:

Esto ya está chupado! Buscamos de nuevo en el cuadro de herramientas de SSIS (SSIS Toolbox) y seleccionamos “premium flat file destination” y debemos configurar una conexión para nuestro blob storage. Yo, personalmente, he seleccionado punto y coma como delimitador y he configurado que el nombre del archivo sea dinámico.. ¿Y qué es esto? Muy sencillo que, por cada tabla de la cual vayamos a extraer la auditoría, cree un archivo en el blob storage. Esto es la configuración de mi paso de destino:

Para indicar el nombre del archivo dinámico debes hacer click sobre el simbolito Fx, aquí he indicado que el campo “Destination File Path” sea equivalente a la siguiente expresión:

Por último mapeamos las columnas que nos vienen. Yo he seleccionado las siguientes porque creo que me aportan información:

Deberíamos poder arrancar el paquete y la extracción debería ser correcta. En caso de que no lo primero es que intentes solucionarlo por tu cuenta y sino estaré encantado de responder a tu comentario en la parte inferior de este post.

La guinda del pastel: Análisis en Power BI.

Una vez todos nuestros datos se encuentran en el blob storage es momento de crujir esos dedos y ponernos a extraer resultados. Para ello utilizaremos Power Query que nos ayudará a transformar esos datos. Yo tampoco estoy muy familiarizado con Power BI, sin embargo este curso de Ana Bisbé me ayudó mucho: Aprende Power BI. Es corto y en muy poco tiempo créeme que sacarás rendimiento. Nos iremos a Power BI Desktop y vamos a añadir blob Storage como fuente de datos y, en mi caso, selecciono la carpeta donde están todos los archivos del blob storage:

Hacemos click en “transformar datos” y nos sale Power Query. Vamos a hacer click sobre esas dos flechas de la primera columna para expandir las columnas:

A partir de aquí debemos realizar las transformaciones en Power Query que necesitemos porque ya hemos expandido los datos:

Esas columnas se corresponden con estas que hemos asignado en el paquete de integración:

Aquí ya en función de lo que necesites explotar. Yo por ejemplo necesitaba saber qué tablas ocupaban más espacio (objecttypecode) y qué campos de esas tablas estaban presentes en la auditoría (changedata) y me he hecho unas visualizaciones tal que así:

Ahora ya sé cuál es la tabla que ocupa más espacio en mi auditoría y por donde debo empezar a atajar el problema así que comienza el borrado de datos de auditoría que no necesito y entonces…

La auditoría desciende de esos 3 gb. Además es posible que dentro de poco podamos prescindir de ese gigabyte extra que el cliente está pagando de auditoría.

Espero que te sirva para solucionar tus problemas con auditorías, te invito a que dejes un comentario y me lo cuentes 😊

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.