Análisis de reportes Power BI
Descripción del proyecto
Analizar los datos históricos de informes de Power BI que ha subido una empresa. En este proyecto utilizaremos como dataset 4 archivos de excel que simularan el comportamiento de una base de datos en continua actualización. El objetivo es crear un dashboard en Power BI que plasme la información más útil para la empresa de sus informes. Para ello primero tendremos que limpiar y modelar los datos usando Power Query y DAX.
- Informes con fallos sin solucionar
- Historial de fallos en los informes
- Tiempo de ejecución media
- Porcentaje de reportes fallidos por reportes publicados
- Segmentaciones por fecha / espacio de trabajo / estado del informe / hora de subida…
- Importar y limpiar los datos
- Crear columnas calculadas
- Crear métricas
- Crear tabla calendario usando Power Query
- Crear un modelo de datos óptimo
- Diseñar visualizaciones que respondan las necesidades del cliente
Importar y procesar los datos
Importar los datos
En este caso importar los datos a Power Bi es muy sencillo, simplemente clickaremos en «Obtener datos» y elegiremos «Libro de excel» para importar cada una de las 4 tablas. Recordemos que este proyecto simula en libros de excel lo que sería una conexión con actualizaciones diarias a una base de datos SQL.
Repetimos el proceso con cada uno de los 4 archivos con las 4 tablas («PBI Refresh DS», «PBI Reports», «PBI Dataset» y «PBI Workspace»)
Limpieza y procesamiento de datos
Lo primero que haremos será cambiar el nombre a las tablas. A mi personalmente me gusta agregar el prefijo Dim a las tablas de dimensiones y el prefijo Fact a las tablas de hechos.
Ya con los nombres de las tablas cambiados, podemos apreciar que en la tabla DimReports las columnas no tienen nombre. Para solucionar este problema y para añadir algunas columnas que necesitaremos posteriormente abrimos el Power Query
Empezaremos utilizando el método «Usar la primera fila como encabezados» para corregir el problema de los nombres de la columna DimReports
Ahora nos iremos a la tabla FactRefreshDS para dividir las cada una de las columnas startTime y endTime que contienen la fecha y la hora de inicio y final de la ejecución en otras 2 columnas, startTime/endTime y startDate/endDate utilizando el espacio que delimita ambos datos y le adjudicaremos el formato correspondiente.
Tras comprobar que no hay ningún otro error ni ajuste necesario en el resto de tablas cerramos el Power Query aplicando los cambios y vamos a la vista modelo para adiministrar las relaciones entre tablas y crear el modelo de datos.
Ya con estas 4 tablas relacionadas lo único que nos queda para completar el modelo es crear una tabla calendario. Hay muchas formas de crear esta tabla, en este caso utilizaré un código de Power Query reciclado.
Copiamos el código en el editor avanzado de Power Query, le inicamos el rango de fechas que queremos generar, cambiamos el tipo de las columnas y lo relacionamos con el modelo de datos.
Ya con el modelo de datos organizado en este caso en un esquema de estrella, con la tabla de hechos en el centro y las dimensiones al rededor, podemos empezar a crear las columnas calculadas que servirán para dar formato a los datos de las visualizaciones del dashboard.
Columnas calculadas
La primera columna calculada que crearemos en la tabla FactRefreshDS será la columna «TiempoDeEjecución» a partir de una resta entre la columna «startDateTime» – «endDateTime» que tras cambiarle el tipo a «hora» nos dará como resultado el tiempo de ejecución.
La siguiente columna calculada que crearemos que ya será algo más compleja es la de «ÚltimoEstadoEjecución». La lógica de esta columna en resumen es que como la columna id es proporcional a la fecha, es decir, cuanto más actual es la fecha mayor es el número de id, utilizaremos el mayor número de id de cada uno de los DataSets (informes de Power BI) para indicar que ese es el último registro de cada uno de los informes.
En resumen, el último registro de cada uno de los informes se marcará como 1 (True) en la columna «UltimoEstadoEjecucion» y el resto se marcará como 0 (False)
Ahora crearemos una columna algo especial ya que será determinante hacer este paso bien para que la simulación que pretendemos hacer tenga sentido. Esta columna llamada «HacexDias» indica cuánto tiempo hace desde la fecha actual hasta la fecha de actualización de cada uno de los reportes.
La clave aquí es que en un entorno conectado a la base de datos en continua actualización el método que deberíamos utilizar para indicar la fecha actual es el método NOW() pero en este caso al tratarse de una simulación utilizaremos el método DATE() con la fecha 21/07/2021.
La última columna que crearemos es una columna bastante sencilla que indicará la hora redondeada a la que se ha actualizado cada reporte. La columna se llamará «HoraActualizacion» y simplemente concatenará las horas (hh:mm:ss) de la columna «startTime» con un string «:00»
Medidas y diseño del dashboard
Para empezar a diseñar el dashboard tenemos que previsualizar en nuestra mente que datos queremos representar en nuestro informe y en que tipo de visualización lo queremos plasmar. En este caso yo he creado este esquema conceptual para empezar a construir el informe:
- Título y segmentaciones en la parte superior
- Número de fallos sin resolver y nombre y fecha de publicación de esos fallos
- Recuento histórico de fallos y porcentaje de recuento de fallos por recuento de informes publicados
- Recuento de fallos por informe
- Gráfica con recuento de fallos y recuento de reportes publicados por mes y por hora
- Tabla con los datos más relevantes de cada registro
- Gráfica de barras con el promedio de tiempo de ejecución en minutos
Para saber el número de informes con errores sin solucionar crearemos una medida que muestre el recuento de registros en los que la columna «status» sea igual a «Failed» y la columna «UltimoEstadoEjecucion» sea igual a 1, es decir, todas los registros en los que el último estado de ejecución sea «Failed»,
Para ello crearemos primero una tabla de medidas.
Utilizaremos una tarjeta con la medida que acabamos de crear para representar el número total de fallos y una tabla con el nombre y la fecha de publicación del informe para ver qué informes tienen fallos sin solucionar con la medida como filtro de la visualización
El siguiente módulo que editaremos será el de Recuento de fallos y porcentaje de fallos por informes publicados. Para ello crearemos 3 medidas, «Reportes fallidos», «Reportes publicados» y «% Reportes fallidos»
Para representar estos datos utilizaremos un medidor con los Reportes publicados como valor máximo y los Reportes fallidos como valor y una tarjeta para indicar el porcentaje de reportes fallidos.
Para representar el número de reportes fallidos por informe importaremos una visualización gratuita de la AppSource llamada «Horizontal bar chart» con la medida de Reportes fallidos y el nombre de los informes como valores
La siguiente representación será un gráfico de columnas y líneas en el que por un lado, en el eje y, las columnas indicaran la cantidad de reportes fallidos y publicados y la línea indicará el porcentaje de reportes fallidos, mientras que el eje x indicará los meses. Además añadiremos a la derecha un segmentador por horas.
En el extremo inferior izquierdo añadiremos una tabla con el nombre del informe, la fecha de publicación, el estado, el tiempo de ejecución, el tipo de ejecución, y el usuario que lo ha publicado como campos y con la columna de «UltimoEstadoEjecucion» como filtro para mostrar únicamente la última actualización de cada uno de los informes
Y por último, para añadir el gráfico de columnas con el tiempo promedio de ejecución de cada uno de los informes en minutos, crearemos la medida «AVG Tiempo de ejecución (min)». Modificaremos también los colores parametrizando la medida de modo que si el tiempo promedio de ejecución supera los 20 min, el color sea rojo; si está entre los 10 y los 20 min, sea amarillo y si es inferior a los 10 min, sea verde.
Para finalizar añadimos 4 segmentadores por tiempo, por workspace, por dataset, por informe y por estado del informe y ya tenemos nuestro dashboard acabado.
