Mostrando entradas con la etiqueta Power Pivot. Mostrar todas las entradas
Mostrando entradas con la etiqueta Power Pivot. Mostrar todas las entradas

lunes, 5 de octubre de 2015

Obtener datos externos - Desde una base de datos de SQL Server Analysis Services

Para ver una versión actualizada de esta entrada visita el blog de mi nuevo sitio www.dataXbi.com


La opción Desde una base de datos de SQL Server Analysis Services la encontramos en el grupo Desde una base de datos de la cinta de opciones Power Query. 

Esta opción nos permite conectarnos a un servidor de Analysis Services y crear una consulta a partir de los elementos de un cubo: dimensiones, medidas y filtros. El resultado será una tabla que contendrá como columnas los elementos seleccionados.




Cuando escogemos esta opción debemos proporcionar el nombre del servidor al que nos queremos conectar.



Si es la primera vez que accedemos al servidor desde Excel debemos proporcionar las credenciales para conectarnos al mismo.





La conexión al servidor puede demorar unos minutos y una vez que está hecha se abre el navegador que muestra organizado en una estructura de árbol los elementos disponibles: las bases de datos, cubos, dimesiones, medidas y filtros que podemos utilizar para crear la consulta. En este caso seleccionaremos el subcubo Sales.



Se muestra el Editor de consultas con una pestaña nueva Administrar que nos permite seleccionar los elementos con los que trabajaremos en la consulta.



La cinta de opciones Administrar solo contiene el grupo Modificar. La primera de sus opciones es Agregar elementos, donde podemos ver y seleccionar los elementos del cubo: dimensiones, medidas y filtros.



Al escoger esta opción se abre una ventana Agregar elementos que muestra el contenido del cubo seleccionado organizado en estructura arbórea. En la raíz de la jerarquía se encuentra el cubo seleccionado (Sales, en el ejemplo). Le siguen las carpetas que contienen las medidas y debajo de las carpetas están las dimensiones.




Seleccionamos las dimensiones con las que trabajaremos. En el ejemplo usaremos la dimensión Product.



En el panel de resultados del Editor de consultas se cargarán todos los atributos de la dimensión. Podemos eliminar los que no nos interese usar en la consulta.



A continuación agregaremos las medidas, seleccionaremos para el ejemplo las medidas Sales Amount y Sales Total Cost del grupo Sales.




En el panel de resultados del Editor de consultas se añaden dos nuevas columnas correspondientes a las medidas seleccionadas.



Una vez seleccionados todos los elementos que contendrá la consulta podemos realizar transformaciones para añadir, modificar o eliminar columnas. En el ejemplo añadiremos la columna Ganancias como resultado de restar la columna Sales Total Cost a la columna Sales Amount.



La columna Ganancias se añadirá en el panel de resultados del Editor de consultas, al final de la tabla.



La opción Contraer columnas del grupò Modificar  permite eliminar las columnas seleccionadas. En este caso eliminaremos todas las columnas excepto Product Category Name, Product Subcategory Name, Product name, Sales Total Cost, Sales Amount y Ganancias.



Después de realizar las transformaciones podemos cargar los datos en una hoja de calculo Excel o solo establecer la conexión a los datos seleccionando la opción Cerrar y cargar en..



Podemos marcar la opción Agregar estos datos al Modelo de datos para que estén disponibles en Power Pivot.



Al cargar los datos en una hoja de cálculo se crerá una hoja nueva que contendrá una tabla con los resultados de la consulta.



Si abrimos el Editor avanzado podemos ver las fórmulas empleadas para obtener este resultado.



Este mismo resultado podemos obtenerlo si hacemos todo el proceso manualmente en el Editor avanzado. Para ello necesitamos conocer que funciones del lenguaje debemos emplear y que parámetros son necesarios pasarles a cada función.


Hagamos todo el proceso manualmente

En la cinta de opciones Power Query, dentro del grupo Obtener datos externos, seleccionemos la opción Consulta en blanco en Desde otros orígenes.


Se abrirá la ventana del Editor de consultas
 


Abriremos el Editor avanzado y sustituiremos el valor de Origen por:

Origen = AnalysisServices.Databases("nombre_instancia_sql_server")




La función AnalysisServices.Databases pertenece a la categoría Accessing data y devuelve como resultado una tabla con las bases de datos del servidor de Analysis Services especifícado como parámetro.

 En el ejemplo el servidor solo contiene la base de datos Contoso_Retail.


Añadiremos la fórmula

Contoso_Retail = Origen{[Name="Contoso_Retail"]}[Data]

Las fórmulas se separan unas de otras utilizando la coma (,)




La consulta devolverá una tabla con los cubos contenidos en la base de dato.

En el ejemplo: Operation y Strategy Plan.




 A continuación añadiremos la fórmula

Operation = Contoso_Retail{[Id="Operation"]}[Data]
 


El resultado será una tabla que contiene los subcubos Inventory, IT Machine y Sales, del cubo Operation.




Si adicionamos la fórmula

Sales1 = Operation{[Id="Sales"]}[Data]


estaremos cargando los elementos del subcubo Sales para trabajar en el Editor de consultas. 




En la ventana del Editor de Consultas se mostrará la pestaña Administrar y podremos agregar a la consulta las medidas, dimensiones y filtros del subcubo Sales que hemos seleccionado.



Para específicar dichos elementos usaremos la función  Cube.Transform  que aplica la lista de funciones de transformación en el cubo especificado. 
 
En el ejemplo el cubo es Sales1 y añadiremos la dimensión Product a la consulta.
La fórmula quedaría así:

 #"Elementos agregados" = Cube.Transform(#"Sales1", {{Cube.AddAndExpandDimensionColumn, "[Product]", {"[Product].[Product Available For Sale Date].[Product Available For Sale Date]", "[Product].[Product Brand Name].[Product Brand Name]", "[Product].[Product Category Description].[Product Category Description]", "[Product].[Product Category Name].[Product Category Name]", "[Product].[Product Class].[Product Class]", "[Product].[Product Color].[Product Color]", "[Product].[Product Description].[Product Description]", "[Product].[Product Image URL].[Product Image URL]", "[Product].[Product Label].[Product Label]", "[Product].[Product Manufacturer].[Product Manufacturer]", "[Product].[Product Name].[Product Name]", "[Product].[Product Size Range].[Product Size Range]", "[Product].[Product Size Unit Measure].[Product Size Unit Measure]", "[Product].[Product Status].[Product Status]", "[Product].[Product Stock Type].[Product Stock Type]", "[Product].[Product Stop Sale Date].[Product Stop Sale Date]", "[Product].[Product Style].[Product Style]", "[Product].[Product Subcategory Description].[Product Subcategory Description]", "[Product].[Product Subcategory Label].[Product Subcategory Label]", "[Product].[Product Subcategory Name].[Product Subcategory Name]", "[Product].[Product Unit Of Measure].[Product Unit Of Measure]", "[Product].[Product URL].[Product URL]", "[Product].[Product Weight Unit Measure].[Product Weight Unit Measure]", "[Product].[Product].[Product Category Name]", "[Product].[Product].[Product Subcategory Name]", "[Product].[Product].[Product Name]"}, {"Product.Product Available For Sale Date", "Product.Product Brand Name", "Product.Product Category Description", "Product.Product Category Name", "Product.Product Class", "Product.Product Color", "Product.Product Description", "Product.Product Image URL", "Product.Product Label", "Product.Product Manufacturer", "Product.Product Name", "Product.Product Size Range", "Product.Product Size Unit Measure", "Product.Product Status", "Product.Product Stock Type", "Product.Product Stop Sale Date", "Product.Product Style", "Product.Product Subcategory Description", "Product.Product Subcategory Label", "Product.Product Subcategory Name", "Product.Product Unit Of Measure", "Product.Product URL", "Product.Product Weight Unit Measure", "Product.Product Category Name.1", "Product.Product Subcategory Name.1", "Product.Product Name.1"}}}) 


En este caso hemos seleccionado todos los atributos de la dimensión pero podimos haber escogido solo los que nos interesen.




En el Editor de consultas se muestra una tabla cuyas columnas son los atributos de la dimensión Pruduct: 




Usando la misma función, Cube.Transform, añadiremos las medidas con las que trabajaremos en la consulta: 

 #"Elementos agregados1" = Cube.Transform(#"Elementos agregados", {{Cube.AddMeasureColumn, "Sales Total Cost", "[Measures].[Sales Total Cost]"}, {Cube.AddMeasureColumn, "Sales Amount", "[Measures].[Sales Amount]"}})
 
En el ejemplo: Sales Amount y Sales Total Cost.




En el panel de resultados del Editor de consulta se mostrarán además de las dimensiones las medidas seleccionadas.




Finalmente cargamos los datos en una hoja de cálculo y obtenemos una tabla como la siguiente:
 

jueves, 3 de septiembre de 2015

Editor de Consulta III

Para ver una versión actualizada de esta entrada visita el blog de mi nuevo sitio www.dataXbi.com


La pestaña Agregar Columna

Permite añadir nuevas columnas a nuestra tabla. Las columnas pueden ser creadas usando las fórmulas del lenguaje, transformando los valores de otras columnas, duplicando una columna, etc.

La cinta de opciones de la pestaña Agregar Columna

Se divide en cuatro grupos: General, De texto, De número  y De fecha y hora.



Grupo General



Permite añadir nuevas columnas personalizadas,  de índice y duplicadas.





Opción Agregar Columna Personalizada:

Esta opción nos permite añadir una nueva columna usando las columnas existentes y el lenguaje de Power Query que nos ofrece una amplia variedad de fórmulas que podemos utilizar para construir expresiones complejas.



En el ejemplo tenemos una tabla con los datos de los equipos de futbol entre los que se encuentra el año de fundación. Vamos a añadir una columna, Años de fundado, que obtendremos calculando la diferencia en años entre la fecha actual y el año de fundación. Para conocer la fecha de hoy utilizaremos la función DateTime.LocalNow() y para  el año la función Date.Year().




La nueva columna aparece al final, pero podemos moverla para que se muestre a continuación de la columna Año fundación.



Opción Agregar columna de índice:

Permite crear una columna de índice, de tipo entero, que puede comenzar desde el valor 0, desde el valor 1 o desde cualquier otro valor.



En el ejemplo tenemos una tabla con la lista de jugadores de futbol, la tabla no tiene una columna de índice, la crearemos con la opción Agregar columna de índice | Desde 1.



La columna índice se muestra al final de la lista.



Podemos crear un índice personalizado seleccionando la opción Agregar columna índice | Personalizado...



Debemos especificar el valor inicial y el incremento para el índice.



Opción Columna duplicada:

Permite crear una nueva columna a partir de otra seleccionada previamente.




El ejemplo muestrala tabla de resultados de la segunda jornada de la liga BBVA 2015 - 2016. Seleccionaremos la columna Column1 que contiene el día y el mes del partido y la duplicaremos.



La columna creada se muestra al final de la lista y como nombre toma el de la columna original con el sufijo " - Copia"





Grupo De Texto

Permite crear y transformar columnas de tipo texto.



Opción Extraer:

A partir de una columna de texto podemos crear  una nueva columna.

En el ejemplo seleccionaremos la columna duplicada Column1 - Copia y crearemos dos nuevas columnas.

Una con los 2 primeros caracteres para el día del partido.




y otra con los 2 últimos caracteres para el mes del partido.



En ambos casos debemos especificar el número de caracteres a conservar.





Finalmente renombramos las columnas: Día y Mes



Opción Combinar columnas

Permite crear una columna a partir de dos columnas existentes.

En el ejemplo seleccionaremos las columnas Día y Mes y las combinaremos



Debemos escoger un separador y un nombre para la nueva columna. El separador es opcional y el nombre de columna por defecto es Combinada.



La columna se muestra al final de la tabla.



Opción Formato

Permite crear una nueva columna aplicando transformaciones a la columna original.
En el ejemplo seleccionaremos la columna Equipo H y seleccionaremos la opción Formato | MAYÚSCULAS



La columa resultante se llamará Uppercase y será la última de la tabla.



Opción Analizar

Permite crear una nueva columna a partir de otra seleccionada previamente y cuyos datos estén en formato XML o JSON.

En nuestro ejemplo seleccionaremos Columna2 que los datos que contiene están en formato JSON y analizaremos el contenido.



Se creará una nueva columna, Json, que debemos expandir





Al expandir la columna se mostrarán 2 columnas  firstName y lastName.



Grupo De número


Permite crear columnas de tipo númerico a partir de otras columnas del mismo tipo haciendo transformaciones.



Opción Información 

Permite crear una columna a partir de la información obtenida del valor de la columna original, si es par o no, si es impar o no y el signo del número.

En el ejemplo seleccionamos de la tabla de equipos la columna Capacidad del estadio que es de tipo entero y analizaremos si es par.



Como resultado obtenemos la columna IsEven de tipo booleano.



Opción Estadísticas

Permite formar nuevas columnas a partir de otras utilizando las funciones estadísticas.
En el ejemplo seleccionaremos las columnas Jornada y DurationDays y la opción Estadísticas | Suma.



Como resultado se obtiene la columna Sum con el reultado de la suma.



La opción Científico permite obtener nuevas columnas a partir de utilizar funcioes matemáticas como logaritmo, factorial, etc.

En el ejemplo utilizaremos la columna Jornada (por ser de tipo entero) y la opción Científico | Factorial.



El resultado que se obtiene es el factorial del valor correspondiente de la jornada y se guarda en la columna Factorial.



La opción Estándar permite crear nuevas columnas utilizando las funciones matemáticas básicas de suma, resta, multiplicación, etc.

En el ejemplo utilizaremos la columna DurationDays y la opción Estándar | Modulo.



Obtenemos la columna Módulo insertado.



Grupo De Fecha y hora 


Permite crear columnas de tipo Fecha y hora.



Opción Fecha  Permite obtener nuevas columnas con información sobre la fecha como el año, el mes el día, etc.

En el ejemplo  se muestra una tabla con el calendario del Barça para la temporada 2015 - 2016. Seleccionamos la columna Fecha y a continuacion la opción  Fecha | Año.




Obtenemos la columna Year que contiene el año correspondiente al partido.




También podemos crear una nueva columna que contendrá el tiempo transcurrido (o por transcurrir) entre la fecha contenida en una columna y la fecha actual.

En el ejemplo seleccionamos nuevamente la columna Fecha y la opción Fecha | Antigüedad



Obtenemos la columna AgeFromDate que nos devuelve un valor de tipo Duración, positivo si la fecha ya ha transcurrido o un negativo si está por transcurrir.

En el ejemplo, en la primera fila la fecha es 23/08/2015 y la fecha en que se realizó el calculo es 02/09/2015 han transcurrido 10 días desde la fecha como se puede apreciar en la columna AgeFromDate.



Opción Hora Permite crear una columna de tipo Hora a partir de una columna.

En el ejemplo la columna Fecha es de tipo Cualquiera, la seleccionamos y a continuación la opción Hora | Analizar.



Como resultado obtenemos la columna ParseTime que nos devuelve un valor de tipo Hora. 



Si seleccionamos la columna ParseTime y la opción Hora | Minuto



Obtendremos la columna Minute con los minutos de la hora correspondiente.




Opción Duración devuelve el tiempo transcurrido en una sola unidad de tiempo: días, horas, minutos, segundos.

En el ejemplo seleccionamos la columna AgeFromDate y la opción Duración | Días.



Se crea entonces la columna DurationDays mostrando la Duración en días.