En Excel, puede crear modelos de datos que contengan millones de filas y, a continuación, realizar un análisis de datos eficaz en 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.
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 de 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. Si se toma el tiempo necesario para aprender procedimientos recomendados en el diseño de modelos eficientes, todo el modelo que cree y use, independientemente de si lo está viendo en Excel, Microsoft 365 SharePoint Online, en un Office Online Server o en SharePoint.
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 de 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.
Como promedio, se puede esperar que un modelo de datos sea de 7 a 10 veces más pequeño 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 podría ser fácilmente de 1 MB o menos. El grado de compresión conseguido depende principalmente del número de valores únicos en cada columna. Cuantos más valores únicos, más memoria se necesita para almacenarlos.
¿Por qué estamos hablando de compresión y valores únicos? Porque crear un modelo eficiente que minimice el uso de memoria consiste en maximizar la compresión y la manera más sencilla de hacerlo es deshacerse de las columnas que realmente no necesita, especialmente si dichas 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 los almacenes de datos y las bases de datos multidimensionales, las tablas grandes que constan principalmente de datos numéricos se denominan a menudo "tablas de hechos". Las tablas de hechos suelen incluir datos de transacciones o rendimiento empresarial, como puntos de datos de ventas y costos que se agregan y se alinean con unidades organizativas, productos, segmentos de mercado, regiones geográficas, etc. Todas las columnas de una tabla de hechos que contienen datos profesionales o que se pueden usar para hacer referencias cruzadas a 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 está formada por valores únicos que solo existen en la tabla de hechos y en ningún otro lugar. Dado que las tablas de hechos son tan grandes, algunas de las mayores ganancias en 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 al nivel de horas? ¿acta? ¿Sobras? ¿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 de libro.
Vínculos relacionados
Especificación y límites del modelo de datos
Power Pivot: análisis de datos y modelado de datos eficaz en Excel
