En Excel 2013 o posterior, puede crear modelos de datos que contengan millones de filas y, a continuación, realizar análisis de datos eficaces con estos modelos. Los modelos de datos se pueden crear con o sin el complemento Power Pivot para que admitan cualquier número de tablas dinámicas, gráficos y visualizaciones de Power View en el mismo libro.
Nota: En este artículo se describen los modelos de datos Excel 2013. Sin embargo, las mismas características de modelado de datos y Power Pivot introducidas en Excel 2013 también se aplican a Excel 2016. De hecho, hay pocas diferencias entre estas versiones de Excel.
Aunque se pueden crear enormes modelos de datos en Excel de forma sencilla, es recomendable no hacerlo por diferentes motivos. En primer lugar, 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. En segundo lugar, los modelos grandes consumen memoria necesaria, lo que afecta de forma negativa a otras aplicaciones e informes que comparten los mismos recursos del sistema. Por último, en Microsoft 365, tanto SharePoint Online como Excel Web App limitan el tamaño de un archivo Excel a 10 MB. En los modelos de datos de libros que contienen millones de filas, el límite de 10 MB se alcanzará rápidamente. Consulte Especificación y límites de modelos de datos.
En este artículo, le explicaremos cómo crear un modelo de construcción firme con el que sea fácil trabajar y que emplee menos memoria. Tomarse el tiempo para aprender los procedimientos recomendados en el diseño de modelos eficientes dará sus frutos en cualquier modelo que cree y use, ya sea que lo vea en Excel 2013, Microsoft 365 SharePoint Online, en un servidor de Office Online o en SharePoint 2013.
También puede usar la herramienta Workbook Size Optimizer. Esta herramienta analiza el libro de Excel y, si es posible, comprime aún más su tamaño. Descargue el Optimizador de tamaño del libro.
En este artículo
Razones de compresión y motor de análisis en memoria
Los modelos de datos de Excel usan el motor de análisis en memoria para almacenar datos en la memoria. El motor implementa potentes técnicas de compresión para reducir los requisitos de almacenamiento, lo que contrae el conjunto de resultados hasta que ocupa una fracción de su tamaño original.
En promedio, puede esperar que un modelo de datos sea de 7 a 10 veces menor que los mismos datos en su punto de origen. Por ejemplo, si va a importar 7 MB de datos desde una base de datos de SQL Server, el modelo de datos de Excel puede ser fácilmente de 1 MB o menos. El grado de compresión realmente alcanzado depende principalmente del número de valores únicos en cada columna. Entre más valores únicos, más memoria se requiere para almacenarlos.
¿Por qué hablamos de compresión y valores únicos? Como crear un modelo eficiente que minimice el uso de memoria se trata de maximizar la compresión, y la forma más sencilla de hacerlo es deshacerse de las columnas que realmente no necesite, especialmente si esas columnas incluyen un gran número de valores únicos.
Nota: Las diferencias en los requisitos de almacenamiento de las columnas individuales pueden ser inmensas. En algunos casos, es mejor tener varias columnas con un reducido número de valores únicos. En la sección donde se explican las optimizaciones de Datetime, se muestra esta técnica detalladamente.
No hay nada mejor para consumir poca memoria que las columnas inexistentes
La columna más eficiente en cuanto a memoria es aquella que nunca llegó a importarse. Si desea crear un modelo eficiente, mire cada columna y pregúntese si contribuye positivamente al análisis que desea realizar. Si no lo hace o no está seguro, no la incluya. Posteriormente, puede agregar columnas nuevas si las necesita.
Dos ejemplos de columnas que siempre se deben excluir
El primer ejemplo está relacionado con los datos procedentes de un almacén de datos. En los almacenes de datos, es frecuente 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.
El segundo ejemplo implica la omisión de la columna de cable principal al importar una tabla de hechos.
Muchas tablas, incluidas las tablas de hechos, tienen claves principales. Para la mayoría de las tablas, como las que contienen datos sobre clientes, empleados o ventas, es probable que desee la clave principal de la tabla 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 incluya 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.
Nota: En almacenes de datos y bases de datos multidimensionales, las tablas grandes compuestas principalmente de datos numéricos se denominan "tablas de hechos". Las tablas de hechos suelen incluir datos de rendimiento empresarial o transacciones, como puntos de datos de ventas y costos que se agregan y se alinean con unidades organizativas, productos, segmentos de mercado, regiones geográficas, y así sucesivamente. Todas las columnas de una tabla de hechos que contienen datos empresariales o que se pueden usar para hacer referencia cruzada a los datos almacenados en otras tablas deben incluirse en el modelo para admitir el análisis de datos. La columna que desea excluir es la columna de clave principal de la tabla de hechos, que consiste en valores únicos que solo existen en la tabla de hechos y en ningún otro lugar. Como las tablas de hechos son tan enormes, algunas de las mayores ganancias en la eficiencia del modelo se derivan de la exclusión de filas o columnas de tablas de hechos.
Cómo excluir las columnas innecesarias
Los modelos eficientes solamente contienen las columnas que son realmente necesarias en el libro. Si quiere controlar las columnas que se incluyen en el modelo, deberá usar el Asistente para la importación de tablas en el complemento Power Pivot para importar los datos en lugar del cuadro de diálogo "Importar datos" de Excel.
Al iniciar el Asistente para la importación de tablas, seleccione las tablas que desee importar.
Para cada tabla, puede hacer clic en el botón de vista previa y filtro y seleccionar las partes de la tabla que realmente necesite. Le recomendamos que anule la selección de todas las columnas en primer lugar y después marque las columnas que desee una vez que valore si son necesarias para el análisis.
¿Se pueden filtrar solamente las filas necesarias?
Muchas de las tablas de bases de datos corporativas y almacenes de datos contienen datos históricos acumulados durante largos períodos de tiempo. Asimismo, es posible que descubra que las tablas que le interesan contienen información sobre áreas de negocio no necesaria para el análisis específico.
Al usar el Asistente para la importación de tablas, puede filtrar los datos históricos o no relacionados, lo que le ahorraría una gran cantidad de espacio en el modelo. En la siguiente imagen, se usa un filtro de datos para recuperar únicamente las filas que contienen datos para el año en curso, excluyendo los datos históricos innecesarios.
¿Qué ocurre si se necesita la columna? ¿Se puede hacer algo más para reducir el espacio consumido?
Hay algunas técnicas adicionales que puede aplicar para convertir una columna en un candidato más adecuado para la compresión. Recuerde que la única características de la columna que afecta a la compresión es el número de valores únicos. En esta sección, descubrirá cómo puede modificar algunas columnas para reducir el número de valores únicos.
Modificación de columnas Datetime
En muchos casos, las columnas Datetime ocupan una gran cantidad de espacio. Por suerte, se pueden reducir los requisitos de almacenamiento para este tipo de datos de diferentes formas. Las técnicas variarán en función de cómo utilice la columna, así como de lo cómodo que le resulte crear consultas SQL.
Las columnas Datetime incluyen una parte de fecha y una hora. Cuando se pregunte si necesita una columna, hágase la misma pregunta varias veces para las columnas Datetime:
-
¿Necesito la misma parte?
-
¿Necesito la parte de tiempo en el nivel de horas? , ¿minutos? , ¿Segundos? , ¿milisegundos?
-
¿Tengo varias columnas Datetime porque deseo calcular la diferencia entre ellas o solamente para agregar la fecha por año, mes, trimestre, etc.?
La respuesta a cada una de estas preguntas determinará sus opciones para abordar la columna Datetime.
Todas estas soluciones requieren la modificación de una consulta SQL. Para simplificar la modificación de las consultas, debería filtrar al menos una columna de cada tabla. Al filtrar una columna, se modifica la estructura de la consulta de un formato abreviado (SELECT *) a una instrucción SELECT que incluye nombres de columnas completos, que se pueden modificar más fácilmente.
Echemos un vistazo a las consultas creadas para usted. En el cuadro de diálogo Propiedades de la tabla, puede abrir al Editor de consultas y ver la consulta SQL actual de cada tabla.
En Propiedades de la tabla, seleccione Editor de consultas.
El Editor de consultas muestra la consulta SQL usada para rellenar la tabla. Si se ha filtrado cualquier columna durante la importación, la consulta incluirá los nombres de columnas completos.
|
En cambio, si se ha importado una tabla completa (sin anular la selección de ninguna columna ni agregar filtros), aparecerá la consulta como “Select * from ”, cuya modificación resultará más compleja:
|
Modificación de la consulta SQL
Ahora que sabe buscar la consulta, puede modificarla para reducir todavía más el tamaño del modelo.
-
En aquellas columnas que contengan datos sobre divisas o decimales, si no se necesitan los decimales, utilice la siguiente sintaxis para deshacerse de estos:
“SELECT ROUND([Decimal_column_name],0)… .”
Si necesita los céntimos pero no las fracciones de céntimos, sustituya el 0 por un 2. Si está usando números negativos, puede redondear las unidades, decenas, centenas, etc.
-
Si tiene una columna Datetime llamada dbo.Bigtable.[Date Time] y no necesita la parte de hora, utilice la siguiente sintaxis para deshacerse de esta:
“SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “
-
Si tiene una columna Datetime llamada dbo.Bigtable.[Date Time] y necesita tanto las partes de fecha como de hora, use varias columnas en la consulta SQL en lugar de una sola columna Datetime:
“SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],
datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],
datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],
datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],
datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”
Use todas las columnas que necesite para almacenar cada parte en columnas separadas.
-
Si necesita usar horas y minutos, y prefiere agruparlos en una columna de hora, puede usar la siguiente sintaxis:
Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]
-
Si tiene dos columnas Datetime, como [Start Time] y [End Time], y lo que realmente necesita es la diferencia de tiempo en segundos en una columna llamada [Duration], quite ambas columnas de la lista y agregue:
“datediff(ss,[Start Date],[End Date]) as [Duration]”
Al usar la palabra clave ms en lugar de ss, recibirá la duración en milisegundos.
Uso de medidas calculadas de DAX en lugar de columnas
Si ya ha trabajado con el lenguaje de expresión de DAX, posible que sepa que 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 u otro informe.
Una técnica para ahorrar memoria consiste en sustituir las columnas normales o calculadas por medidas calculadas. Los ejemplos clásicos son Unit Price, Quantity y Total. Si tiene las tres, puede ahorrar espacio si mantiene solo dos y calcula la tercera con DAX.
¿Qué dos columnas debe conservar?
En el ejemplo anterior, conserve Quantity y Unit Price. Estas dos contienen menos valores que Total. Para calcular Total, agregue una medida calculada como la siguiente:
“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])”
Las columnas calculadas son iguales que las consultas normales en el sentido de que ambas ocupan espacio en el modelo. Sin embargo, las medidas calculadas se obtienen sobre la marcha y no ocupan espacio.
Conclusión
En este artículo, hemos hablado acerca de los diferentes enfoques que pueden ayudarle a crear un modelo más eficiente en cuanto a memoria. Para reducir el tamaño del archivo y los requisitos de memoria de un modelo de datos, reduzca tanto el número total de columnas y filas, como el de valores únicos que aparecen en cada columna. A continuación se enumeran algunas de las técnicas explicadas:
-
La eliminación de columnas es, por supuesto, la mejor forma de ahorrar espacio. Decida qué columnas necesita realmente.
-
En ocasiones, puede eliminar una columna y sustituirla por una medida calculada en la tabla.
-
Es posible que no necesite todas las filas de una tabla. Puede filtrar las filas en el Asistente para la importación de tablas.
-
Por lo general, la división de una única columna en varias partes diferentes es una buena forma de reducir el número de valores únicos de la columna. Cada una de las partes contendrá una pequeña cantidad de valores únicos y el total combinado será inferior que la columna unificada original.
-
En muchos casos, también se necesitan las diferentes partes como rebanadores en los informes. Cuando corresponda, puede crear jerarquías a partir de partes como Hours, Minutes y Seconds.
-
En muchas ocasiones, las columnas contienen más información de la necesaria. Por ejemplo, supongamos que una columna almacena decimales, pero ha aplicado formato para ocultar todos los decimales. El redondeo puede resultar muy eficaz para reducir el tamaño de las columnas numéricas.
Ahora que ya ha hecho todo lo posible para reducir el tamaño del libro, puede también ejecutar la herramienta Workbook Size Optimizer. Esta herramienta analiza el libro de Excel y, si es posible, comprime aún más su tamaño. Descargue el Optimizador de tamaño del libro.
Vínculos relacionados
Especificación y límites del modelo de datos
Optimizador de tamaño del libro
Power Pivot: análisis de datos y modelado de datos eficaz en Excel
