Anteriormente, en esta serie, vimos Power Query vs Power Pivot – Uso del modelo tabular

En esta entrada vimos tres herramientas para el consumo del modelo tabular desde MS Office Excel 2013.

Estas herramientas son: tabla dinámica, Power View y Power Map. Estamos en la serie Power Query vs Power Pivot analizando que desde Power Query no es posible acceder a herramientas clientes de visualización de modelo de datos, sino que necesitamos Power Pivot como herramienta de diseño del modelo tabular que luego expondremos. Esta es una gran diferencia y estamos dedicando varias entradas de blog por la importancia que tienen todas estas funcionalidades dentro de MS Office Excel.

Hoy nos quedamos con tablas dinámicas.

Columnas de tabla dinámica y Modelo de datos

Veamos, desde  la vista de datos del modelo de datos Power Pivot, un subconjunto de las columnas de la tabla Clientes. Se aprecian diferentes tonalidades del color de fuente y de fondo. Estas tonalidades tienen su significado y se reflejan en  la tabla dinámica, por eso nos detendremos hoy.

En todos los casos, tratamos con columnas de sólo lectura (RO). No es posible acceder a las celdas de las tablas del modelo de datos tabular.

• Columnas ocultas

En el modelo datos es posible mantener columnas ocultas, las que no serán vistas por las herramientas cliente.

Las primeras columnas Nombre, Apellido y BirthDate están ocultas a las herramientas cliente, a todas, por ello tienen un color gris constante, sin intercalar las filas.

Veamos la lista de campos disponibles para la tabla Clientes en la tabla dinámica. No aparece ninguna de las columnas ocultas, ese es el objetivo.

Si no van a aparecer en las herramientas de visualización de modelos, ¿por qué necesitamos las columnas ocultas?

Pues las necesitamos porque:

• son columnas temporales, que participan en expresiones DAX que dan origen a otras columnas. Las columnas Nombre y Apellidos están ocultas y se utilizan en una expresión DAX que concatena ambas cadenas.

  

• son columnas que garantizan las relaciones entre tablas, como CustomerKey de la tabla Cliente, véase el indicador y la información sobre la relación con la tabla Ventas.

Y en esta imagen, desde la vista diagrama, la relación entre ambas tablas.

• son columnas que no estamos descartando en el modelo; pero que en el momento actual no deseamos exponer, así es que solamente las ocultamos, y la preservamos, en caso de necesitarla en un futuro.

• son columnas que han sido agrupadas en niveles de jerarquías y no son necesarias como columnas individuales. No hay ningún caso en la tabla Cliente en nuestro modelo tabular, así es que vamos a ver un ejemplo en la tabla Productos. Las columnas Tamaño y RangoTamaño sólo son necesarias como parte de la jerarquía, por lo que las ocultamos.

En la lista de la izquierda vemos parte de las columnas y jerarquías de la tabla, visibles y no visibles, y en la columna del centro tenemos las columnas visibles.

En una misma jerarquía se ve que hay dos columnas ocultas que dan origen a dos niveles, mientras que la otra columna, Producto, está visible como columna y como nivel. Tenemos que tener en cuenta, que es incorrecto mantener visibles columnas con tanta granularidad como puede ser en este caso Producto, la idea es mostrar apenas el ejemplo de que se pueden combinar, en realidad, debemos ocultar las tres columnas, que es lo que vemos en la tercera columna.

• Ocultar tablas

Es posible ocultar tablas, por las mismas razones. Si ocultamos las tablas Categoría y Subcategoría en la vista diagrama del modelo de datos.

En la lista de campos de la tabla dinámica se ve el efecto de ocultar las tablas.

   

Mostrar o no los elementos ocultos

Es posible ocultar todos los elementos no visibles también en la ventana Modelo de datos, en ambas fichas. Desde el menú, ficha Inicio, grupo Ver. Cuando aparece sombreado en gris, en la imagen de la izquierda, significa que podemos ver los ocultos y en caso contrario, en la imagen de la derecha, vemos un modelo más despejado.

  

Veamos lo cómodo que resulta ahora ver casi todo el contenido del modelo desde la vista diagrama.

• Columnas visibles desde el origen

Hay columnas que vienen del origen de datos y están visibles en la herramienta cliente. Estas son las que utilizamos tal cual vienen.

• Columnas calculadas

Las columnas calculadas se definen mediante el lenguaje de expresiones DAX. Las expresiones DAX se evalúan para cada una de las líneas de la tabla, las que a su vez se rellenan con el resultado. Físicamente afecta al tamaño del modelo y también al consumo de recursos. Existen muchas razones para crear columnas calculadas, no son válidas para todo; pero usadas correctamente son enormemente efectivas.

Las columnas calculadas pueden quedar ocultas de la herramienta cliente, en ese caso su comportamiento y coloración es exactamente igual al de las columnas de origen de datos que están ocultas, no aparecen en la tabla dinámica.

Las columnas calculadas visibles, se muestran en la tabla dinámica, donde no hay ninguna información sobre si la columna es original o calculada, y no es necesario. En la siguiente imagen, las columnas en azul son originales y en rojo son calculadas. No hay diferencia, a efectos de aplicación cliente todas son columnas disponibles.

 Agregados

Las tablas dinámicas muestran los valores agregados, de forma predeterminada la función es Suma. Es posible cambiar la función de agregación en el menú, ficha Avanzada, grupo Resumir por.

Es importante recordar, todo lo que en la tabla dinámica va al área Valores, todo todo se agrega. Aún cuando arrastremos un texto, se agrega aplicando la función Recuento de la cantidad de apariciones de ese texto según los campos que aparezcan en las áreas de filas y columnas de la tabla dinámica.

Tenemos los siguientes campos que se están mostrando en las áreas de la tabla dinámica:

Hay dos campos en el área Valores: Ventas y Todos ambos de la tabla Ventas. De ellos, en la imagen aparecen resaltado en rojo y azul los valores correspondientes a Ventas correspondientes a la Línea: Otro para cada año.

Hay un campo en el área Filas, es Línea Agrupada, de la tabla Productos, en la imagen aparece en verde  la información correspondiente a la Línea Agrupada: Montaña.

Y en el área Columnas, hay tres elementos, que a su vez son cuatro: dos columnas descriptivas: Año y Semestre de la tabla Fechas y Valores, lo que significa que todas las columnas que aparezcan en el área Valores, en nuestro caso: Ventas y Todos se mostrarán en columnas.

En la próxima entrada continuaremos analizando la tabla dinámica y sus componentes como herramienta cliente que consume el modelo de datos Power Pivot, en concreto, veremos las distintas opciones de filtrado en tablas dinámicas.

No te pierdas el resto de entradas de la serie Power Query vs Power Pivot