PowerPivot crear un modelo de datos eficiente en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010

En Excel podemos crear modelos de datos con millones de filas, para posteriormente, realizar análisis de datos con el modelo con PowerPivot para reportarlos sobre tablas dinámicas, gráficos, Power View, etc. en el libro, siendo importante conocer como desde PowerPivot crear un modelo de datos eficiente en Excel.

PowerPivot crear un modelo de datos eficiente en Excel

– Los modelos de gran tamaño que contienen muchas tablas y columnas resultan excesivos para la mayoría de los análisis y constituyen listas de campos difíciles de manipular.

– Los modelos de gran tamaño consumen memoria necesaria, lo que afecta de forma negativa a otras aplicaciones e informes que comparten los mismos recursos del sistema.

– Los archivos generado con modelo de gran tamaño tienen una restricción en cuanto al tamaño del archivo. Office 365, SharePoint Online, Excel Web App y el archivo Excel limita a 10 MB el tamaño máximo.

  • SÓLO LAS COLUMNAS NECESARIAS

Para un modelo eficiente solamente se incluyen las columnas que son realmente necesarias en el libro.

Iniciar el asistente para la importación de tablas, seleccionar las tablas que deseamos importar.

Asistente de importación de tablas PowerPivot

Para cada tabla, seleccionar las columnas deseadas. Posteriormente podremos añadir nuevas tablas o columnas.

Asistente de importación de tablas PowerPivot, vista previa de la tabla
  • FILTRAR LAS FILAS NECESARIAS

En muchas ocasiones las tablas de las bases de datos contienen datos históricos acumulados durante largos períodos de tiempo e incluso las tablas contienen información sobre áreas de negocio no necesaria para nuestro análisis.

Desde el asistente de importación de tablas podemos filtrar los datos históricos o no relacionados, permitiendo ahorrar una gran cantidad de espacio en el modelo de datos.

Venta de filtro de importación filtrando para un rango de fechas
  • INFORMACIÓN PROCESOS

– Caso 1: COLUMNA DE CAMPO CLAVE

En ocasiones las tablas incluyen una clave principal para crear con ella relaciones en el modelo.

Las tablas de hechos son diferentes. En estas, se utiliza la clave principal para identificar cada fila de forma exclusiva. Aunque es necesaria para la normalización, resulta menos útil en los modelos de datos en los que solamente desee usar dichas columnas para llevar a cabo análisis o establecer relaciones de tablas. Por este motivo, al llevar a cabo la importación desde una tabla de hechos, no incluir su clave principal.

Las claves principales en las tablas de hechos consumen una gran cantidad de espacio en el modelo y no ofrecen ningún beneficio, ya que no se pueden usar para crear relaciones.

– Caso 2: INSTRUMENTOS DE PROCESOS DE ETL

En los datos procedentes de un almacén de datos, es normal encontrar instrumentos de procesos de ETL que cargan y actualizan datos en el almacén. Al cargar datos, se crean columnas como "Fecha de creación", "Fecha de actualización" y "Ejecución de ETL". Ninguna de estas columnas es necesaria en el modelo, por lo que no deben seleccionarse al importar datos.

  • MEDIDAS CALCULADAS DAX EN LUGAR DE COLUMNAS

Las columnas calculadas se usan para derivar columnas nuevas basándose en otra columna del modelo. Aunque las medidas calculadas se definen una vez en el modelo, solamente se evalúan al usarse en una tabla dinámica, gráfico u otro informe.

Siempre que sea posible, sustituir las columnas normales o calculadas por medidas calculadas.

– Caso 1

Disponemos en el origen de datos las unidades de los productos, el importe y el precio total, importar las 3 columnas no es necesario para el modelo.

Importamos unidades e importe (contienen menos valores que el precio total). El precio total se puede calcular como:

PrecioTotal:=SUMX(Tabla_Ventas;Tabla_Ventas[Importe]*Tabla_Ventas[Unidades])
Ventana PowerPivot para el asistente de medidas
– Caso 2

Disponemos en el origen de datos la base imponible, el tipo de IVA y el precio total.

La base imponible anteriormente y el resto se puede calcular como:

Total_IVA:=SUMX(Tabla_Ventas;Tabla_Ventas[PrecioTotal]*0,21+Tabla_Ventas[PrecioTotal]
  • REDUCIR EL ESPACIO CONSUMIDO

Conviene optimizar la columna para su compresión, al reducir el número de valores únicos (La única característica de la columna que afecta a la compresión es el número de valores únicos).

– Caso 1: En números, quitar parte decimal o la parte de entero.
– Caso 2: En números, redondear las unidades, decenas, centenas, etc.
– Caso 3: Campos Datetime

Las columnas Datetime incluyen una parte de fecha y una hora, pero ¿realmente necesitamos toda esa información? horas, minutos, segundos, milisegundos, años, mes, trimestre, etc.

Modificar la consulta SQL y adaptarla según convenga:

a. Unir columnas de día, mes y año en una sola.

b. Unir columnas de horas, minutos y segundos en una sola.

c. Para trabajar con horas eliminar la parte de fechas.

d. Para trabajar con fechas eliminar la parte de horas, por ejemplo:

SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time])

e. Para dos columnas Datetime, [Hora Entrada] y [Hora Salida], sí necesitamos la diferencia de tiempo en minutos en una columna llamada [Duracion], quitar ambas columnas y agregar:

DateDiff(mm,[Hora Entrada],[Hora Salida]) as [Duracion]
Ventana PowerPivot con columna fecha DataDiff y sus valores desagrupados
  • OPTIMIZA Y COMPRIME EL LIBRO

Microsoft dispone de un complemento " Workbook Size Optimizer " que permite analizar el libro de Excel y, si es posible, comprime aún más su tamaño.

Reducir el tamaño de los archivos Excel 2013: Cinta de opciones, pestaña: Workbook Size Optimizer

Consulta el complemento para optimizar tus libros con modelos de datos.

Nivel de dificultad: Básico Excel Básico

Deja un comentario

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