Mostrando entradas con la etiqueta Datos Externos. Mostrar todas las entradas
Mostrando entradas con la etiqueta Datos Externos. Mostrar todas las entradas

miércoles, 30 de septiembre de 2015

Obtener datos externos - Desde una base de datos SQL Server

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


Podemos importar datos desde una base de datos de una instancia de MS SQL Server usando los objetos Tablas y Vistas de la base de datos.

En el ejemplo utilizaremos la instancia de SQL Server Diana-PC y la base de datos Demo Database Nav (8-0).



Para crear la consulta desde de la cinta de opciones Power Query seleccionaremos la opción Obtener datos externos >> Desde una base de datos de SQL Server. 



Cuando escogemos esta opción se muestra la ventana Base de datos de SQL Server donde debemos específicar el nombre del servidor, el nombre de la base de datos (es opcional) y podemos especificar los datos que necesitamos a través de una instrucción SQL (tambien es opcional).





Al hacer clic en Aceptar, si es la primera vez que accedemos a la instancia de MS SQL Server, se abre la ventana Acceder a una Base de datos de SQL Server que nos solicitará las credenciales para conectarse al servidor.




Al presionar el botón conectar Power Query intenta conectarse al servidor, el proceso puede demorar unos minutos.



 
Una vez establecida la conexión al servidor si no hemos específicado una instrucción SQL se abre la ventana del Navegador y podemos escoger uno o varios elementos de la base de datos. Si en la ventana Base de datos de SQL Server no escribimos un nombre base de datos el Navegador mostrará la lista con las bases de datos contenidas en el servidor.


Si en la ventana Base de datos de SQL Server escribimos  una instrucción SQL se cargarán los datos sin necesidad de realizar ningún otro paso.



Cuando seleccionamos una tabla o vista en el panel de la izquierda del Navegador, su contenido se muestra en el panel de la derecha. 

Podemos seleccionar varias tablas y vistas de la base de datos marcando la casilla Seleccionar varios elementos y a continuación seleccionando la casilla delante de cada tabla o vista. 

Una vez que hemos seleccionado todos los elementos debemos decidir donde queremos ver los datos. Desplegamos la lista Cargar que se encuentra en la parte de inferior del panel de la derecha y escogemos la opción Cargar en.. Se abre la ventana Cargar donde podemos configurar como deseamos ver los datos.

Para este ejemplo dejaremos los valores por defecto. Los datos se cargarán en forma de tabla en una hoja de cálculo nueva.



 
Al presionar el botón Cargar se mostrarán los resultados en  hojas de cálculo. Se crearán tantas hojas nuevas como elementos de la base de datos se hayan seleccionado.

 

Si iniciamos el Editor de Consultas de Power Query podemos ver en el navegador las consultas que se han creado (una por cada elemento seleccionado). Al hacer clic en cualquiera de las consultas se cargarán los datos en el panel de resultados lo que nos permitirá realizar las transformaciones sobre los mismos.



 
En el Editor avanzado podremos ver las fórmulas aplicadas para obtener dichos resultados.





Podemos obtener los mismos resultados manualmente desde el Editor avanzado.


Construir la consulta Manualmente



Para ello en la cinta de opciones Power Query seleccionemos la opción Obtener datos externos >> Desde otros orígenes >> Consulta en blanco.



Se inicia el Editor de Consultas de Power Query. 



Abrimos el Editor avanzado.



Sustituimos el valor de Origen por:

Origen = Sql.Database("Diana-PC", "AdventureWorks2012")



 Al hacer clic en Listo se ejecutará la consulta y en el Editor de consultas tendremos una tabla conteniendo las tablas y vistas de la base de datos.



En el Editor avanzado añadiremos una nueva fórmula para indicar que usaremos la tabla Departament del esquema HumanResource.

Departamento = AdventureWorks2012{[Schema="HumanResources",Item="Department"]}[Data]



 Al presionar Listo en el panel de resultados del Editor de consultas tendremos el contenido de la tabla Departament.



Por último iniciemos el Editor avanzado y sustituyamos las fórmulas que aparecen por las siguientes:

 let
 Origen = Sql.Database("Diana-PC", "AdventureWorks2012", [Query="select       [NationalIDNumber], [FirstName], [MiddleName], [LastName], [JobTitle], [BirthDate] from [HumanResources].[Employee] E inner join [Person].[Person] P on P.BusinessEntityID = E.BusinessEntityID"])
in
    Origen 


En lugar de la función Sql.Databases usaremos sql.Database. Esta función admite tres parámetros: el primero es el nombre del servidor, el segundo el nombre de la base de datos y el tercero una consulta SQL.

En el ejemplo hemos seleccionado algunos campos de las tablas Employee y Person.




Al hacer clic en el botón Listo se mostrará la ventana Consulta de base de datos nativa, advirtiendonos que estamos a punto de ejecutar una consulta que puede modificar la base de datos.




Al presionar el botón Ejecutar se muestran los resultados en el Editor de consultas.


 

lunes, 28 de septiembre de 2015

Obtener datos externos - Desde una base de datos de Access

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


Dentro del grupo Obtener datos externos de la cinta de opciones Power Query, está la opción Desde una base de datos.




Esta opción nos permite importar datos desde diversos tipos de bases de datos como: Microsoft Access, Microsoft SQL Server, Microsoft Analysis Services, Oracle, MySql, PostgreSQL, etc.





Importar datos desde una base de datos Access

La opción Desde una base de datos de Access nos permite utilizar las tablas y consultas de una base de datos Access. Por cada tabla o consulta seleccionada obtendremos una consulta Power Query. 


Para el ejemplo usaremos la base de datos: ContosoSales.accdb que podemos descargar desde el siguiente enlace
 
http://powerpivotsdr.codeplex.com/ 

Una vez que hemos descargado el fichero ContosoV2.zip debemos descompactarlo. 




Seleccionamos la opción Obtener datos externos >> Desde una base de datos >> Desde una base de datos Access de la cinta de opciones Power Query.



 
A continuación escogemos la base de datos con la que vamos a trabajar, en el ejemplo ContosoSales.accdb



Power Query se conecta a la base de datos. Esta operación puede demorar unos minutos dependiendo del tamaño de la base de datos.



Se abre la ventana del navegador y seleccionar los elementos a importar. En el panel de la izquierda podemos seleccionar las tablas y/o consultas que utilizaremos. Podemos seleccionar varios elementos. 

Cuando seleccionamos un elemento del panel de la izquierda su contenido se muestra en el panel de la derecha.



Una vez que hemos escogido los datos debemos específicar donde queremos cargarlos. En la parte inferior del panel de la derecha desplegaremos el menú Cargar y escogeremos la opción Cargar en..



La opción por defecto es Tabla que mostrará los datos en forma de tablas en hojas de datos Excel. Podemos cambiar y escoger Crear solo conexión, de esta manera los datos no se almacenarán en hojas de datos Excel. También podemos seleccionar Agregar estos datos al Modelo de datos lo que nos permitirá usarlos posteriormente en Power Pivot.



En el ejemplo hemos escogido cargar los datos en tabla por lo que se mostrará cada conjunto de resultados en una hoja de cálculo diferente.



Si iniciamos el Editor de consultas de Power Query podemos observar en el navegador que se han creado dos consultas, cada consulta se refiere a cada una de las tablas seleccionadas.


Si hacemos clic sobre DimProduct en el panel de resultados se mostrará el contenido de la tabla. En el panel de Configuración de consulta podemos observar que se han aplicado dos pasos: Origen y Navegación.


Si abrimos el Editor avanzado veremos las fórmulas correspondientes a dichos pasos.



En la primera fórmula a Origen se le ha asignado el resultado de evaluar la función Access.Database que devuelve como resultado una tabla con la lista de tablas y consultas de la base de datos. A la función se le ha pasado como parámetro la función File.Contents. La función File.Contents recibe como parámetro la ubicación física de un fichero, en este caso ContosoSales.accdb y retorna el contenido binario del fichero. Ambas funciones pertenecen a la categoría de funciones Accessing data.

La segunda fórmula devuelve el contenido de  la tabla seleccionada, en este caso DimProducto.

Otra forma de obtener estos resultados es construir la consulta manualmente desde el Editor avanzado.

Para ello 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 inicia el Editor de Consultas de Power Query.




Abrimos el Editor avanzado.





Sustituimos el valor de origen por:

Origen = Access.Database(File.Contents("C:\soft\AdventureWorks DW 2012.accdb"))



Al presionar el botón Listo podemos ver en el editor de consultas el contenido de la base de datos (27 tablas y 1 vista)



En el Editor avanzado escribiremos una nueva fórmula para escoger la vista Products.

Products = Origen {["Schema = "", Item = "Products"]}[Data]




Al hacer clic en Listo se mostrará en el Editor de consultas el resultado de ejecutar la vista.



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.