Crear un modelo de datos eficiente en cuanto a memoria con Excel y el complemento de PowerPivot

Nota:  Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.

En Excel 2013 o versiones posteriores, puede crear modelos de datos que contiene millones de filas y, a continuación, realizar análisis de datos con estos modelos. Modelos de datos pueden crearse con o sin el complemento Power Pivot para admitir cualquier número de tablas dinámicas, gráficos y visualizaciones de Power View en el mismo libro.

Nota: En este artículo se describe los modelos de datos en Excel 2013. Sin embargo, las características de PowerPivot que se introdujo en Excel 2013 y modelado de datos mismos también aplicarán a Excel 2016. Hay un modo eficaz poca diferencia 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 Office 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 dedica el tiempo necesario para conocer las prácticas recomendadas para el diseño de modelos eficientes, su trabajo se verá recompensado cuando cree y use cualquier modelo en el futuro, con independencia de si lo visualiza en Excel 2013, en Office 365 SharePoint Online, en Office Online Server o en SharePoint 2013.

Tenga en cuenta también ejecuta el optimizador de tamaños. Analiza el libro de Excel y si es posible, los comprime aún más. Descargue el optimizador de tamaños.

En este artículo

Razones de compresión y motor de análisis en memoria

No hay nada mejor para consumir poca memoria que las columnas inexistentes

Dos ejemplos de columnas que siempre se deben excluir

Cómo excluir las columnas innecesarias

¿Se pueden filtrar solamente las filas necesarias?

¿Qué ocurre si se necesita la columna? ¿Se puede hacer algo más para reducir el espacio consumido?

Modificación de columnas Datetime

Modificación de la consulta SQL

Uso de medidas calculadas de DAX en lugar de columnas

¿Qué dos columnas debe conservar?

Conclusión

Vínculos relacionados

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, los modelos de datos suelen ser entre 7 y 10 veces más pequeños que los mismos datos en el punto de origen. Por ejemplo, si va a importar 7 MB de datos de una base de datos de SQL Server, el modelo de datos podría ocupar 1 MB o menos en Excel. El grado de compresión conseguido en realidad depende principalmente del número de valores únicos de cada columna (cuantos más valores únicos haya, más memoria será necesaria para almacenarlos).

¿Por qué estamos hablando acerca de compresión y valores únicos? Porque, para crear un modelo eficiente que minimice el uso de la memoria, la clave reside en maximizar la compresión. La forma más sencilla de conseguirlo consiste en deshacerse de las columnas que no necesite realmente, especialmente, si dichas columnas incluyen una gran cantidad 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, se suele hacer referencia a la tablas de gran tamaño compuestas principalmente por datos numéricos como “tablas de hechos”. Las tablas de hechos suelen incluir datos sobre transacciones o rendimiento comercial, como puntos de datos sobre costes y ventas agregados y adaptados a las unidades organizativas, productos, segmentos de mercado, regiones geográficas, etc. Todas las columnas de las tablas de hechos que contienen datos empresariales o que se pueden usar para hacer referencias cruzadas a los datos almacenados en otras tablas deben estar incluidas en el modelo para que admitan el análisis de datos. La columna que desea excluir es la columna de clave principal de la tabla de hechos, que consiste en una serie de valores únicos que solamente existe en la tabla de hechos. Debido al inmenso tamaño de las tablas de hechos, algunos de los principales beneficios de la eficiencia del modelo proceden de la exclusión de filas o columnas de las 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.

Asistente para la importación de tablas en el complemento PowerPivot

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.

Panel de vista previa en el Asistente para la importación de tablas

¿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.

Panel de filtro en el Asistente para la importación de tablas

¿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 misma parte a nivel de horas, minutos, segundos y 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.

Cinta en la ventana PowerPivot con el comando Propiedades de la tabla

En Propiedades de la tabla, seleccione Editor de consultas.

Abra el Editor de consultas en el cuadro de diálogo Propiedades de la tabla.

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.

Consulta SQL usada para recuperar los datos

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:

Consulta SQL con la sintaxis predeterminada (más breve)

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.

  1. 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.

  2. 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]) “

  3. 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.

  4. 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]

  5. 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, piense una columna que almacena decimales en la que ha aplicado un formato para ocultarlos todos. El redondeo puede resultar muy eficaz para reducir el tamaño de las columnas numéricas.

Ahora que ha hecho lo que puede para reducir el tamaño del libro, considere la posibilidad de ejecutar también el optimizador de tamaños. Analiza el libro de Excel y si es posible, los comprime aún más. Descargue el optimizador de tamaños.

Vínculos relacionados

Especificación y límites del modelo de datos

Descarga de Optimizador de tamaños

PowerPivot: análisis y modelado de datos eficaces en Excel

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×