Buscar este blog

Cargando...

viernes, 20 de noviembre de 2015

Excel 2010: Como cargar datos de Power Query en el modelo Power Pivot

Si tenemos la suerte de poder utilizar versiones más recientes de Excel que la 2010 es posible que esta entrada pueda resultaros poco interesante. Sin embargo, hay organizaciones en las que la frecuencia de actualización del software es una dimensión lentamente cambiante :)

Podeis descargaros la última versión de Power Query, que es perfectamente compatible con Excel 2010, para trabajar con modelado de datos “de escritorio” o ETL self-service. Encontraremos que, la hora de cargar el resultado de nuestro script M, no disponemos de un botón que automatice dicha tarea como si lo hay en Excel 2013 o superior:

image

Entonces, ¿cómo hacer para llevar a modelos Power Pivot datos cargados a través de Power Query directamente? sin pasar por una tabla Excel (con sus consiguientes limitaciones)

El truco es muy fácil:  en lugar de marcar una opción en el cuadro de diálogo de Power Query debemos hacer un proceso manual de 3 pasos:

1.

Una vez terminemos el tratamiento de los datos en Power Query guardamos los cambios y seleccionamos la opción de ‘Crear conexión de datos’, sin llevarlo a una tabla Excel. En el ejemplo con el que vamos a desarrollar esta entrada, la consulta Power Quey a integrar en el modelo Power Pivot es CTL_Processes. Como veréis en la siguiente imagen, en el libro Excel dispongo de dos consultas creadas como conexión:

image

2.

En la cinta de Datos, vamos a la lista de conexiones existentes en el libro y accedemos a las propiedades de la consulta Power Query en cuestión

image

En el cuadro de dialogo de propiedades de la conexión, seleccionamos la cadena de conexión y la copiamos, tendrá una composición similar al siguiente

image

Si os pasa como a mi y no entendéis nada de lo que figura como valor en el atributo Properties de la cadena de conexión no pasa nada, podemos continuar. Lo importante es que os fijéis en el Provider: Microsoft.MashUp.OleDb.1, que viene a ser el provedor Power Query y más importante aún es que el proveedor es OleDB.

Por tanto, finalizamos este paso seleccionando la cadena de conexión completa (todo lo marcado con fondo rojo) y copiándola.

3.

Tercer y último paso. Abrimos el modelo PowerPivot y seleccionamos un nuevo origen de datos de tipo ODBC/OLEDB

image

Para la cadena de conexión suministraremos la copiada en el paso anterior, además de un nombre para la conexión:

image

 

A partir de aquí podemos escribir la consulta o seleccionar la query asociada con esa cadena de conexión. Muy fácil cuando se sabe, pero resulta poco intuitivo si no se conoce el método.

viernes, 13 de noviembre de 2015

PowerShell y SQL BI: Extraer MDXScript de cubos SSAS

En esta entrada continuamos en la misma línea que en mi artículo anterior, con el que iniciamos esta serie de scripts PowerShell para resolver tareas frecuentes en los despliegues de soluciones de BI.

Extraer el script MDX de un cubo puede resultarnos útil para revisar el código de medidas y miembros calculados, scopes, traducciones, etc.. con motivo de realizar pruebas sobre métricas o simplemente, mover el código completo de un entorno a otro sin necesidad de reprocesar el cubo de destino. Para este último caso podéis pensar ‘eso se hace en un plis con Visual Studio’ y tenéis toda la razón, sin embargo, recordad que el uno de los objetivos de esta serie de artículos es lograr la máxima automatización posible para evitar realizar tareas manuales y repetitivas. Aun así, seguro habrá formas alternativas de lograr la mismo, aqui nos centraremos en cómo hacerlo con PowerShell

Seguir leyendo…

jueves, 5 de noviembre de 2015

PowerShell y SQL BI: Ejecucion de comandos SQL

 

La ejecución de sentencias T-SQL a través de PowerShell puede resultarnos útil en diversos escenarios, en mi caso las encuentro una forma eficiente de realizar tareas de despliegue como crear bases de datos, tablas, procedimientos, logins, jobs.

Con esta entrada se inicia una serie con la idea de compartir scripts PowerShell para la automatización de esas tareas despliegue de elementos de BI: objetos SQL relacional, paquetes o proyectos Integration Services, informes de Reporting Services y todo lo que se os pueda ocurrir sobre bases de datos OLAP.

Seguir leyendo…

lunes, 21 de julio de 2014

Error instalando SSDT BI para Visual Studio 2012–Rule “Same architecture installation” failed

 

Esta es una entrada breve para compartir una incidencia con la que me he cruzado recientemente.

Escenario

En una máquina (Azure VM, pero da igual para el caso) con una instalación de SQL Server 2012 SP1 (x64) sobre Windows Server 2012 R2. Al intentar instalar las plantillas de proyectos de BI para VS2012 (http://www.microsoft.com/en-us/download/details.aspx?id=36843) nos encontramos con el siguiente mensaje:

image

Solución

La raíz del problema está en la página “Installation type” del asistente de instalación, originalmente había seleccionado “Add features to an existing instance of SQL Server 2012

image

De forma que cuando llegamos a la página “Installation Configuration Rules”, nos muestra el mensaje anterior. Para solucionar esta incidencia debemos volver atrás en el asistente y seleccionar “Perform a new installation of SQL Server 2012

image

Con esto nos permitirá completar la instalación de las plantillas de proyectos de BI para Visual Studio 2012

image

Espero que esta entrada os ayude a ahorrar el tiempo que tuve que emplear en encontrar esta info navegando por los foros MSDN Guiño

martes, 5 de noviembre de 2013

SSIS Básico: Bucles ForEach para ejecutar paquetes en FileSystem

Orchestrator1

 

Es bastante común que entre los paquetes ETL que generamos para realizar una serie de procesos diseñemos un ‘Orquestador’, esto es un paquete en el que a través de tareas Ejecutar Paquete (Execute package task) establezcamos un orden de ejecución pudiendo asignar lógica variables, etc… El termino orquestador define bastante bien el propósito de este tipo de paquetes. Imaginaros un director de orquesta que da pie a cada grupo de instrumentos acompasando los ritmos, adecuando intensidad, etc… En SSIS los grupos de instrumentos serían los distintos paquetes ETL a ejecutar y el director el paquete "Orquestador”.

Es habitual como digo, pero siempre hay una primera vez para todos así que esta entrada esta dedicada a los usuarios del foro de SSIS en castellano

Que vamos a hacer?

Resolver la siguiente cuestión de Pachecora:

“Buenas tardes tengo la siguiente consulta estoy usando SQL Server 2008 R2 y lo que necesito es poder ejecutar varios package que estarán en una carpeta por medio de un Job lo que hice fue crear varios Steps para este Job que apuntan a determinado package, pero lo que necesito ahora es ejecutar todos los package dentro de una carpeta. Sera posible hacerlo?”

Antes de empezar…

Entre los distintos componentes nativos que nos ofrece Integration Services desdForEach loop containere la versión 2005 se encuentran los bucles ForEach que son de gran utilidad para realizar iteraciones utilizando distintos enumeradores: Sistema de archivos, un dataset ADO, etc.. Para ser más claro, recorre una colección y en cada vuelta devuelve el elemento actual (algo así como un cursor en TSQL, por buscar un símil)

Escenario

Vamos a desarrollar un proyecto de SSDT2012 con plantilla de Integration Services, aunque se podría realizar con la versión BIDS 2005 y posteriores sin ningún tipo de problema.

Este proyecto consta de un solo paquete: el orquestador. Los paquetes que va a ejecutar este orquestador están desplegados en una carpeta del sistema de archivos. Realmente no nos interesa que hacen esos paquetes (en mi ejemplo están vacíos), nos vamos a ocupar de como ejecutar todos ellos a través de un punto único.

Solución

Siempre hay más de una solución a un problema, por lo que la siguiente no es sólo una de ellas.

1) Crear una variable para la ruta del paquete: Desde el proyecto de SSDT, con el paquete vacío (que he renombrado como ‘Maestro.dtsx’) que agrega la plantilla, añadimos una variable de tipo string que llamaré RutaPaquete. He añadido un valor por defecto a la variable.

string variable

El valor de esta variable será establecido en cada iteración del bucle ForEach, ahora veremos como…

2) Añadir un bucle ForEach al Control Flow, y una vez en el área de diseño editamos sus propiedades haciendo doble click. En la sección Colección debemos asegurarnos de que el enumerador seleccionado es el de archivos (Foreach File Enumerator). Cada enumerador lleva una configuración específica. En el de archivos, debemos establecer que carpeta vamos a recorrer y que tipo de archivos. La siguiente imagen muestra la configuración que he utilizado para recorrer la carpeta que contiene los archivos con extensión dtsx:

ForEach loop File Enum configuration 01

Fijaros que he seleccionado la opción “Fully qualified” como nombre de archivo. Esto me devolverá la ruta completa de cada fichero.

3) Asignar el valor a una variable. Aquí es donde entra en juego por primera vez la variable que hemos creado anteriormente (RutaPaquete). En la configuración de mapeo de variables (Variable Mappings) del bucle ForEach asignamos la variable User::RutaPaquete al resultado de cada iteración. Como este tipo de bucle sólo devuelve un resultado (el nombre del archivo) la vinculamos con el índice 0:

ForEach loop File Enum configuration 02

Y hasta aqui la configuración del bucle ForEach. Ahora vamos con la tarea Ejecutar Paquete.

4) Añadir tarea Ejecutar Paquete dentro del bucle. Hacemos doble click para editarlo y en la sección Paquete cambiamos la configuración del componente.

a) En tipo de referencia establecemos External Reference (antes de 2012 y los modelos de proyecto de despliegue de proyecto, esta parte no existía).

b) En la localización cambiamos a sistema de archivos (File System)

c) En conexión desplegamos el menú, seleccionamos nueva conexión y seleccionamos cualquier archivo. Este parámetro será sustituido por el valor de la variable, pero por ahora lo dejamos así.

Execute Package task configuration 01

Y ya estaría lista esta tarea.

Existen más opciones de configuración en esta tarea como el password de los paquetes, el mapeo de variables, si se debe ejecutar con una nueva instancia de proceso,etc.. pero para este ejemplo no necesitamos nada más. Os invito a leeros el BOL sobre esta tarea para obtener más información http://technet.microsoft.com/es-es/library/ms137609.aspx

5) Crear un expresión para la conexión. Seleccionando el Administrador de conexión de fichero que creamos en el paso 4.C y pulsando la tecla F4 accedemos al panel de propiedades. Buscamos la propiedad expresiones (Expressions) y establecemos la propiedad ConnectionString asignando la variable @[User::RutaPaquete]

image

Esto provocará que en cada ejecución de la tarea, la conexión tome la ruta del fichero actual dentro de la iteración del bucle.

Si ejecutamos este paquete desde el proyecto podemos comprobar que se ejecuta cada uno de los paquetes que existen en la carpeta que hemos configurado en el bucle ForEach (c:\ETL Packages). He añadido un tarea ejecutar Script con el código c# para lanzar un evento de tipo información con la finalidad de mostrar la ruta del fichero actual en cada iteración:

image

Ya solo quedaría configurar un Job del Agente SQL con un único paso que ejecute este paquete maestro para automatizar la ejecución de todos los contenidos en la carpeta.

Podéis descargar este paquete desde Skydrive

Espero que os haya sido de utilidad!

Saludos.

martes, 25 de junio de 2013

SQL Server 2014 disponible en CTP1

 

SQL Server logo

Hoy se ha hecho pública la Community Technology Preview 1 de la nueva versión de SQL Server 2014.

Este lanzamiento de este producto fue anunciado en el Tech Ed que se realiza en Estados Unidos, junto a otras novedades como la versión R2 de Windows Server 2012, que también se encuentra disponible en versión Preview.

Las principales novedades de SQL Server 2014 son las siguientes:

  • Bases de datos en memoria, o como se le conoce por su nombre clave Hekaton. ¿Qué es esto? Microsoft ha unido los esfuerzos de la división de investigación (Microsoft Research) con los del equipo de producto SQL Server. El propósito de esta nueva vía de desarrollo en SQL Server fue proporcionar un sistema sin bloqueos, que son el principal motivo cuellos de botella en sistemas multiprocesador, limitando la utilización de las capacidades de CPU. Hekaton es capaz de mantener los datos íntegros, sin corrupción, sin realizar bloqueos en las estructuras (filas, páginas, tablas). El resultado es un motor relacional en memoria que ofrece un rendimiento hasta 100 veces superior al motor tradicional. Además, y al contrario que otros fabricantes (SAP, Oracle) que ya cuentan con sistemas similares, Hekaton se ofrece como una característica integrada del motor de bases de datos.

    Más info en http://research.microsoft.com/en-us/news/features/hekaton-122012.aspx y http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/11/how-fast-is-project-codenamed-hekaton-it-s-wicked-fast.aspx
  • xVelocity ColumnStore, o almacenamiento por columnas con tecnología xVelocity. A estas alturas xVelocity ya debería resultarnos familiar, hasta Excel cuenta con ella. Comenzó llamandose VertiPaq y es la piedra filosofal de las bases de datos de Analysis Services de modelo tabular. Provee de una compresión espectacular a nivel de columnas y en la versión anterior, SQL Server 2012, ya se incluía en el motor relacional para los índices columnares. En SQL Server 2014 también se utiliza para el almacenamiento por columnas. Una de las mayores ventajas con respecto a la versión anterior es que los índices permiten lectura y escritura. Es un gran avance para las cargas y consultas sobre los data marts/warehouses.

    Más info en ttp://blogs.technet.com/b/dataplatforminsider/archive/2012/03/08/introducing-xvelocity-in-memory-technologies-in-sql-server-2012-for-10-100x-performance.aspx
  • Uso de discos SSD como memoria
  • Mejoras en alta disponibilidad

Evolución de SQL Server (imagen de http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx)

WhitePaper

Durante las próximas semanas tendré la oportunidad de montar un entorno para poner a prueba este flamante SQL Server 2014 y descubrir más detalles acerca de sus capacidades. Mantente en línea si quieres estar informado
Sonrisa

Pero no esperes más Descárgate ya tu copia de SQL Server 2014 CTP1 y comparte tu experiencia!! Además, si te gusta jugar en las nubes también está disponible una imagen para máquinas virtuales de Azure que monta esta CTP1 de SQL Server 2014.

También, y desde hace ya algunos días, se encuentra disponible para descarga la Guía de producto SQL Server 2013 CTP1. Ayer día 24-06-2013 se realizó la última actualización de este paquete de documentación a la versión 3.0.

Os dejo algunos enlaces de interés referentes a las novedades de SQL Server 2014:

Centro de recursos SQL Server 2014 

Bloggers:

viernes, 31 de mayo de 2013

GeoFlow Preview para Excel 2013

Excel se está consolidando como una poderosa herramienta para consumo y análisis de datos, especialmente en el ámbito del Business Intelligence personal o Self-Service BI. En la versión 2013 ya tiene integradas de forma nativa la tecnología xVelocity, el add-in PowerPivot y Power View. Pero además  Microsoft ha hecho pública la preview de un nuevo complemento que nos permitirá representar nuestros datos posicionándolos geográficamente, en un entorno de visualización tridimensional con un interfaz de usuario que se enmarca perfectamente dentro del estilo Office. Estamos hablando de GeoFlow, aunque quizás este nombre cambie más adelante.

Este complemento utiliza mapas de Bing y renderiza los datos que pueda ubicar a través de coordenadas o nombres de ciudades estados, provincias y países y nos permitirá realizar análisis, detectar patrones, obtener una vista en forma de secuencia cinematográfica a través del tiempo, etc..

Ya puedes descargarte e instalar esta versión

Llevaba algún tiempo queriendo conocer más acerca de este producto, ya que se han ido publicando algunas cosas (http://sqlserverbiblog.wordpress.com/2013/03/05/sql-server-community-world-tour-with-geoflow/) en la red antes de que se hiciera pública esta preview.

Por fin he tenido oportunidad de instalarlo y probarlo, y ciertamente es muy sencillo de manejar para crear presentaciones realmente impresionantes.El unico requisito fundamental es contar con un rango de datos dentro de Excel que contengan información que permita posicionar los datos en un mapa, como mencionamos arriba, a través de coordenadas o nombre de la ubicación. Para hacer mis primeras pruebas con GeoFlow he utilizado un pequeño conjunto de datos con los Tweets que se han publicado desde el día 14/03/2013  y que contienen las palabras @SolidQ, @SolidQEs, @SolidQIT, #SQSummit13 y #SolidQ.

Cuando pensé en utilizar este conjunto de datos no esperaba unos resultados positivos, ya que la mayoría de los tweets no tienen una localización muy clara. Aunque muchos usuarios tienen activado el uso de la geolocalización al publicar los tweets, esta información no siempre se encuentra registrada en el tweet. De forma que la unica forma viable que encontré de poder posicionar los datos fue por la propia localización del usuario, aquella que se configura en el perfil…. y en dónde se puede poner lo que uno quiera:

image

Como se puede comprobar en las columnas TweetLatitude y TweetLongitude encontramos un hermoso 0, aunque la columna UserGeoEnabled tenga un valor TRUE. Por tanto nos quedamos con la columna UserLocation y vamos a ver que es capaz de hacer GeoFlow con esa información.

Una vez hemos descargado e instalado el complemento, en la cinta de Insertar  de Excel aparece el grupo GeoFlow, que contiene un solo botón con dos comandos: Iniciar GeoFlow y Añadir datos seleccionados a GeoFlow, e inicialmente solo aparece activado el primero:

image

 

Si lanzamos GeoFlow nos aparecerá una ventana con cuatro areas.

  • En la parte superior disponemos de la cinta Inicio, con las opciones disponibles: cambiar el tema del mapa pudiendo seleccionar un mapa geográfico o político con distintas iluminaciones, insertar gráficos o cajas de texto, añadir capas, buscar geolocalizaciones, capturar la vista mostrada, etc..
  • El panel de escenas del “tour”, situado a la izquierda. A través de este panel podemos seleccionar y configurar las distintas escenas que componen nuestro “tour”
  • El panel de tareas, desde el que podremos configurar el elemento seleccionado, ya sean las propiedades de las escenas o las distintas capas visuales que podemos añadir al mapa.
  • Y por fin el área central, dónde figura el globo terraqueo y en el que podemos visualizar los datos geo posicionados y animaciones a través del tiemp que vayamos configurando en las distintas capas.

 

image

En una misma escena podemos tener varias capas y en cada capa configurar una visualización de datos. Para mis pruebas he utilizado una sola escena con dos capas, en la primera capa muestro columnas agrupadas con número de tweets que se van produciendo a lo largo del tiempo, y para crear los grupos utilizo la palabra asociada (@SolidQ, @SolidQEs, @SolidQIT, #SQSummit13 y #SolidQ). En la segunda capa mostraré el número de usuarios que están publicando esos tweets, y el lenguaje que tienen configurado.

Por defecto nos aparecerá una capa, Layer1, a la que podemos cambiar el nombre a través del panel de tareas pulsando el botón image que encontramos en la parte superior del panel. Desde aqui podemos elegir si se muestra o no cada capa, configurarla o eliminarla. La siguiente imagen muestra una breve descripción de la función de cada botón que encontramos en el panel de tareas cuando seleccionamos la vista de capas:

 

image

 

Una vez tenemos establecido el nombre de la capa, accedemos al botón 'Configuración de datos de la capa’ y veremos la lista de campos disponibles. Esta lista de campos depende de las tablas que existan en el modelo de datos del libro Excel y que hayamos añadido a GeoFlow (desde el botón de la cinta Insertar).

Llegados a este punto debemos definir como se van a localizar los datos. Para ello en la parte inferior de la lista de campos vemos una sección con el título Geography. Es en esta sección dónde debemos arrastrar y solar los campos que demarcan ubicación. Para este ejemplo recordad que utilizaremos la columna UserLocation y la vamos a configurar de tipo City, y solo queda pulsar el botón Map it para poder continuar.

image

A continuación la lista de campos toma otro aspecto, en la parte superior (bajo el desplegable de selección de capas) aparece una sección con el nombre de Map by UserLocation (county) junto a un botón Edit y justo abajo un porcentaje:

image

Ese 25% siginifica que ha sido capaz de ubicar correctamente la cuarta parte de los registros utilizando el contenido de la columna UserLocation como identificadores para regiones de tipo City. No es mucho, así que me he creado una columna para insertar el país y asi subir el porcentaje de localizaciones de tipo ciudad hasta un 56%…

image

 

Lo siguiente es configurar la representación gráfica de datos en el mapa. Podemos elegir para cada capa entre gráficos de tipo columnas (agrupadas o apiladas), burbúja o mapa de calor, pero antes vamos a añadir datos a las distintas secciones: Height (peso), Category (categoría) y Time (tiempo).

  • Height: Asignamos la columna TweetID y se cambia la función de agregación por Count() en lugar de Sum(). Esto hará que las columnas crezcan según el número de tweets.
  • Category: Arrastrar la columna TokenWord para que por cada una de las palabras clave cree una columna.
  • Time: En esta sección añado la columna Created_at que representa la fecha de publicación del tweet. Una vez añadimos el campo a la sección aparece un menú desplegable que nos permite cambiar la unidad en que se mostrará la linea de tiempo. Además podemos cambiar la configuración de tiempo en Time Settings haciendo posible que cuando se visualicen los datos a través de la línea de tiempo, se representen de distintas formas
    • Instant: Se mostrarán los datos correspondientes a cada momento del tiempo según se avance.
    • Time Accumulation: Se mostrará un crecimiento paulatino acumulando los valores segun avance el tiempo.
    • Persist the Last:  Muestra el último valor para el periodo y avance.

image

 

Si pulsamos el botón de reproducción en la línea de tiempo, podemos observar como se representan los datos según la configuración que hayamos puesto para el tiempo. Además se puede controlar el ritmo al que avanza esta linea de tiempo y acotar los margenes entre el rango de fechas existente, a través del botón de configuración que encontramos en la mismo control de reproducción del tiempo:

image

 

Echo en falta la posibilidad de exportar los “Tour” creados en algun formato (video, PowerPoint) o incrustar escenas en una hoja de Excel junto a otras presentaciones gráficas, además de la posibilidad de profundizar en los datos… pero aún estamos con la versión Preview Sonrisa

Entradas populares