Power Query vs Power Pivot – Tabla dinámica para Modelo tabular

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

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

colocultas

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.

campostd

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.

colcalcu  nombreyapeoculto

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

colrelaciones

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

relaccliente

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

coldescartada

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

colniveles

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.

colsproductos

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.

diagramamodelo

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

todastablas   activastablas

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.

mostraroculto  nomostraroculto

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

diagramamodelo2

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

visiblesorigen

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

calculadas

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.

columnasvisiblestd

 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.

resumirpor

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:

panelfiltrotd

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íneaOtro 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.

agregado

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 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.