Anteriormente, en esta serie, vimos Power Query vs Power Pivot – Metadatos y funciones

Cuando accedemos a datos externos se produce una lectura de los metadatos de origen. Cada herramienta hace un uso diferente de estos metadatos brindándonos funcionalidades muy interesantes y que podríamos pasar por alto. Hoy vamos a hablar de lo que ocurre cuando importamos con Power Query y Power Pivot tablas o vistas, que es lo mismo a efectos de importar datos, así que para abreviar, pensemos que son todo tablas.

Obtener datos desde Base de datos SQL Server a Power Query

Al conectar a la Base de datos aparece el panel Navegador con la lista de Bases de datos del servidor y para cada una sus tablas, vistas y funciones. Vamos a las tablas, al mover el ratón tenemos una vista preliminar de las columnas y los datos que se alojan en la tabla. Las barras de desplazamiento nos dejarán movernos para obtener más información.

Como no podía ser de otra forma, y para que podamos reproducir los ejemplos, en esta serie utilizaré la Base de datos de ejemplo de Microsoft AdventureWorksDW2012 que se puede descargar en Codeplex

Hasta ahora no hemos importado nada, los datos permanecen en el servidor o contenedor original. Vamos a seleccionar la tabla FactInternetSales, que seguro, será conocida por muchos por ser una de las tablas de hecho, más importantes dentro del datawarehouse modelo por Microsoft para este ejemplo.

Seleccionamos la tabla, hacemos doble clic sobre ella o seleccionamos el comando Editar. se abre el Editor de Consultas de Power Query, en el que volvemos a tener la vista preliminar de los datos y sus columnas. Seguimos sin importar nada, los datos permanecen en el servidor o contenedor original.

Hasta ahora, todo normal, lo que se espera; pero hay más !!! 🙂

Más columnas que las columnas de la tabla original

Lo interesante en este caso, viene cuando desplazamos la barra horizontal más allá de las columnas de la tabla, y tenemos más columnas.

Pero veamos de cerca, se trata de columnas adicionales para cada tabla, en el modelo relacional subyacente, que está relacionada con la tabla que estamos explorando. En nuestro caso, vemos DimCustomer, DimCurrency, DimDate, tres veces porque hay 3 relaciones, y el resto de tablas.

Es decir, nosotros hemos escogimos una única tabla, FactInternetSales, que son las Ventas, y se trae todas sus columnas y además, todas sus tablas relacionadas. Esto es de una utilidad extraordinaria y más sencillo, imposible.

Hay dos formas de trabajar con estas tablas, traer todas las columnas para el registro actual, o seleccionar qué columnas, o todas, queremos visualizar en esta tabla como si fueran propias, veamos.

Value

Desde la columna DimCustomer, que contiene los datos de la dimensión Clientes, hacemos doble clic sobre el hipervínculo Value y nos devuelve únicamente un registro y la información de todas las columnas de la tabla relacionada. Como podemos leer en el código M, se va a la tabla Clientes, que es la tabla relacionada, la tabla de búsqueda, y se trae todos los datos del CustomerKey=21768, que se corresponde con la venta actual, el número de orden SO43697. CustomerKey es la columna por la que se establece la relación.

En el Editor de consultas se activa una ficha nueva, Registro, que nos permite convertir en tabla esta lista de pares formados por Propiedad o campo y Valor. El conjunto de datos obtenido sustituye al anterior, es decir, si dejamos aquí la consulta esto sería el resultado que exportaríamos a hoja Excel y/o Modelo de datos.

Otra curiosidad es que, desde este resultado, podemos regresar a la tabla Ventas, porque entre las propiedades que se trae, trae nuevamente las tablas relacionadas, indicando en cada caso: Table si la relación es de 1:M o Record si la relación es 1:1

De tal manera que si ahora, lo que queremos son todas las Ventas de este cliente con CustomerKey=21768, no tenemos más que seleccionar Table junto a FactInternetSales y ya está !!

Y aun en este momento, seguimos sin haber importado nada, en realidad estamos trabajando siempre con vistas preliminares. Al cerrar el editor de consulta es que el motor Power Query, conecta directamente al origen de datos y trae los datos a Excel, ya sea a la hoja o al modelo de datos Power Pivot.

La segunda funcionalidad habilitada para la lectura de metadatos de tablas relacionadas es mediante el botón para “filtrar” las columnas deseadas y agregarlas al conjunto de datos actual.

Selección de columnas

Volvemos al inicio, estamos viendo el conjunto de datos de la tabla FactInternetSales, la columna DimCustomer y seleccionamos el botón que habitualmente utilizamos para filtrar.

En este caso, más que filtrar, lo que haremos es expandir, agregar a los datos actuales la información de las columnas que seleccionemos.

Nos quedamos con un par de columnas, sólo por la demo, daría un poco igual la que elijamos.

Podemos traer las columnas seleccionadas con o sin el nombre de la tabla de origen.

Si traemos el nombre,

  

Si no lo traemos,

El resultado es que para cada una de las líneas de nuestra tabla Ventas ahora tendremos información directa del género y estado civil del cliente que hizo la compra. En la próxima entrada veremos un par de casos en los que encuentro muy beneficiosa la funcionalidad de expandir columnas desde una tabla relacionada.

Power Pivot

Por su parte Power Pivot, una vez que se conecta al servidor, muestra la lista de tablas y vistas, como vimos antes, nada de funciones.

Al seleccionar una tabla, sólo trae esa tabla, nada más. Es cierto que podemos filtrar por filas y columnas y seleccionar tablas relacionadas; pero nada comparable con lo que ofrece Power Query.

Desde el modelo de datos podemos ver la lista de columnas traídas para esta tabla, sólo sus columnas, sin ninguna referencia a ninguna columna de ninguna otra tabla. Eso sí, los registros se los trajo todos, todos, creando un duplicado en  el modelo de datos Power Pivot dentro de Excel.

Tablas relacionadas

Otro día volveremos a hablar de los metadatos y las relaciones entre tablas, ahora veamos qué ocurre si tras seleccionar una tabla, activamos el comando Seleccionar tablas relacionadas.

El motor Power Pivot consulta los metadatos de origen e identifica las tablas relacionadas, que se seleccionan y están disponibles para importar.

Para cada una de estas tablas se puede filtrar por filas y/o columnas; pero en cualquier caso, al seleccionar el comando Finalizar, se importarán todos los registros de todas las tablas seleccionadas.

En el modelo veremos una ficha para cada tabla, todas las columnas y todas las filas importadas.

Son diferentes 🙂

Como vemos, Power Query y Power Pivot son muy diferentes en cuanto a capacidad de consumir metadatos.

En la próxima entrada veremos un par de casos en los que es muy beneficioso crear las consultas con Power Query y luego cargarlas al modelo de datos Power Pivot.

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