28 diciembre 2008

Caso: Implementando relaciones muchos-a-muchos (many-to-many) y referenciadas (referenced) en SSAS

Después de varias semanas de ausencia (no estuve de vacaciones sino envuelto en 20 cosas) voy compartir un caso interesante para el diseño de un DataMart y su respectivo cubo en Analysis Services que me tocó trabajar en las últimas semanas. El caso se resume en crear un cubo en Analysis Services que brinde la información de ventas asignadas a los representantes de venta (vendedores) para el cálculo de sus comisiones de acuerdo al cliente y producto vendido.

Para llegar a la solución requerida fue necesario trabajar con relaciones muchos-a-muchos y referenciadas en el cubo. No voy a ahondar en qué trata cada uno de estos tipos de relaciones sino más bien me enfocaré en los detalles del problema de negocio y en el recorrido realizado para llegar a la solución. Pueden revisar la documentación de los BOL de SQL para mayor referencia: Many-to-Many Dimensions in Analysis Services 2005 y Defining a Referenced Relationship.

EL PROBLEMA

En este caso simularé el escenario real con un ejemplo simple de manera que pueda ser fácil de entender para todos. El problema de negocio consiste en calcular las comisiones de nuestros representantes de venta. Los clientes (podrían considerarse como distribuidores), están asignados a la cartera de uno o más representantes de venta.

Detalles importantes acerca del negocio/proceso:

  • Los productos se organizan en diversas categorías.
  • Los representantes de venta tienen una jerarquía de organización según las localizaciones que cubren.
  • Los productos en cuestión son altamente especializados por lo cual el negocio asigna al representante cuota sobre una o más categorías de producto (y por tanto de los productos dentro de las mismas). Además, estas categorías son asignadas de acuerdo a la localización y cliente.

Si modelamos la asignación de clientes y categorías de producto para los representantes de ventas en un diagrama de entidad relación (ER), tenemos:

clip_image002

Según se muestra hay una relación de muchos-a-muchos entre Representante ([SalesPerson]), Categoría ([Category]) y Cliente ([Customer]) que se materializa físicamente usando la tabla puente [CustomersAndCategoriesBySalesPerson]. Para abreviar el ejemplo la tabla de representante de ventas ([SalesPerson]) ha sido de-normalizada con respecto a los campos que guardan los datos de localización geográfica (territorio y distrito). Un proceso automático al inicio del periodo carga los datos en la tabla puente de acuerdo a las estrategias del departamento de ventas, por tanto esta tabla mantiene lo que cada representante de venta tiene asignado de acuerdo al cliente y por tanto las comisiones respectivas se asignarán según los datos de esta tabla.

Nuestra base de datos de ejemplo contiene los siguientes datos de prueba:

Clientes:

clip_image004

Categorías:

clip_image006

Productos:

clip_image008

Representantes de venta:

clip_image010

Tabla puente con la asignación de clientes y categorías para cada representante de venta:

clip_image012

Con respecto a las transacciones ocurridas, vamos a simular que en último mes se registraron las siguientes ventas (nuevamente para simplificar el ejemplo se han omitido varios campos como la fecha, número de transacción, etc.):

clip_image014

LÓGICA PARA LASIGNACION DE VENTAS:

Primera venta:

Para el primer registro de la tabla de ventas en la figura anterior, venta del Producto 1 por el Cliente 1, deberíamos tener la siguiente asignación según representante de venta:

Representante de ventas 1 = 10 unidades
(el cliente 1 y el producto 1 - perteneciente a la categoría 1 - están asignados a su cartera)

Representante de ventas 2 = 10 unidades
(el cliente 1 y el producto 1 - perteneciente a la categoría 1 - están asignados a su cartera)

A nivel de territorio tenemos (sin importar el representante):

Territorio 1 = 10 unidades del producto 1 (a pesar de que representante de venta 1 y representante de venta 2 están en el mismo territorio, sólo debemos ver la cantidad única de unidades vendidas y no duplicarlo)

Si analizamos según distrito debemos tener:

Distrito 1 = 10 unidades del producto 1 (de acuerdo a la misma lógica anterior)

Agregando la segunda venta:

Para el segundo registro de ventas, venta del Producto 2 por el Cliente 1 deberíamos tener la siguiente asignación según representante de ventas:

Representante de ventas 2 = 5 unidades
(el cliente 1 y el producto 2 - perteneciente a la categoría 2 - están asignados a su cartera)

Recapitulando, para los dos primeros registros de venta debemos tener como resultado:
Representante de ventas 1 = 10 unidades vendidas
Representante de ventas 2 = 10 + 5 = 15 unidades vendidas

Territorio 1 = 15 (10 del producto 1 y 5 del producto 2)
Distrito 1 = 15 (10 del producto 1 y 5 del producto 2)

Agregando la tercera venta:

Para el tercer registro de ventas, venta del Producto 2 por el Cliente 2 deberíamos tener la siguiente asignación según representante de ventas:

Representante de ventas 3 = 3 unidades
(el cliente 2 y el producto 2 - perteneciente a la categoría 2 - están asignados a su cartera)

Los resultados acumulados en este punto serían:

Representante de ventas 1 = 10 unidades vendidas
Representante de ventas 2 = 10 + 5 = 15 unidades vendidas
Representante de ventas 3 = 3 unidades vendidas

Territorio 1 = 15 (10 del producto 1 y 5 del producto 2)
Territorio 3 = 3 (3 del producto 2)
Distrito 1 = 18 (10 del producto 1, 5 del producto 2, 3 del producto 2)

Por tanto, las ventas agregadas a nivel de territorio y distrito deben ser de acuerdo a los valores únicos de la combinación cliente y producto para NO duplicar las ventas. Si se requiere analizar las ventas según representante de venta entonces debemos de generar los resultados de acuerdo a la relación de muchos a muchos (en este caso si puede haber duplicados de la misma transacción si en caso el cliente y producto han sido asignados a más de un representante).

CONSTRUYENDO LA SOLUCIÓN

El cubo resultante debe permitir responder preguntas de manera consistente a nivel de representante de venta, cliente y producto.

Al crear nuestro datamart básicamente tenemos las siguientes dimensiones:

clip_image016

A nivel del cubo creamos también las tres dimensiones:

Representante de venta:

clip_image018

clip_image020

Cliente:

clip_image022

clip_image024

Producto:

clip_image026

clip_image028

 

Revisemos las posibles opciones para solucionar nuestro problema:

(INTENTO DE) SOLUCION # 1:

La idea de mostrar esta primera opción es demostrar por qué no podemos tratar este caso de modelamiento de DataMart como uno más. En esta primera opción vamos a guardar las ventas asignadas a cada representante en el fact table. Para esto creamos nuestro modelo de estrellas de la siguiente manera:

clip_image030

Como mencionamos anteriormente, nuestro fact table está poblado con las asignaciones de ventas por cada representante de acuerdo a lo explicado en la primera parte de este post:

clip_image032

Si agregamos nuestro fact v1 a nuestro cubo tendríamos una única medida con la cantidad de venta (Quantity) y el tab de Dimension Usage configurado de la siguiente manera:

clip_image034

Exploramos nuestro nuevo cubo:

clip_image036

Según se muestra tenemos una cantidad total de 28 productos vendidos lo cual es erróneo porque sólo fueron 18 productos.

clip_image038

Asimismo al agregar la jerarquía del representante de venta notamos que los valores en el nivel más granular (representante de venta), los valores se muestran correctamente, pero al agregar por territorio tenemos 25 unidades para el Territorio 1 lo cual es incorrecto porque sólo fueron 15 unidades en total. Lo mismo para el total del Distrito 1, el cubo nos arroja un total de 28 cuando en total fueron 18 productos vendidos.

clip_image040

Vemos los mismos resultados incorrectos si agregamos la jerarquía de producto. El total de ventas para el Territorio 1, Producto 1 arroja 20 cuando debe ser 10.

Ya en este punto deben haberse dado cuenta de que si el fact table tiene todos los registros de ventas (tal como los tiene ahora), no hay forma de que los totales no nos arrojen como lo está haciendo ahora (sumando cada registro) que es forma usada para cualquier caso típico.

(INTENTO DE) SOLUCION # 2 - Implementando relación muchos-a-muchos (many-to-many):

En este caso ya que existe una relación de muchos-a-muchos entre nuestras tres dimensiones (representante de venta, cliente y producto) vamos a usar la funcionalidad de SSAS para manejar este tipo de relaciones. En este caso sólo necesitamos guardar en nuestra tabla de hechos las transacciones que realmente sucedieron sin importar la asignación a uno o más representantes de venta y vamos a dejar que la asignación de la venta al representante o representantes se resuelva usando la relación muchos a muchos.

Primero a modo de demostración sólo crearemos la tabla intermedia (Fact muchos-a-muchos) para relacionar el representante de venta con el cliente y probar cómo funciona el uso de la relación muchos-a-muchos en SSAS. Nuestro diagrama de estrellas quedaría de la siguiente manera:

clip_image042

SSAS trata a las tablas intermedias con relaciones muchos a muchos como Fact Tables y por tanto debemos agregarla como un nuevo Measure Group de nuestro cubo. El contenido de nuestra tabla FactManyToMayPersonAndCustomer se muestra a continuación:

clip_image044

Como mencionamos anteriormente, los registros ingresados en nuestra tabla fact (versión 2) son idénticos a como se registraron en la tabla de ventas de nuestro sistema fuente (sólo 3 registros, tal como sucedieron los hechos en la realidad):

clip_image046

Al crear el nuevo cubo versión 2 con el asistente de SSAS, seleccionamos los 2 fact tables conteniendo las ventas y la relación muchos a muchos. Automáticamente el asistente detecta que existe una relación de varios a varios entre el representante de venta y nuestro fact table principal con las ventas. En ese caso sólo nos queda cambiarle la propiedad Visible a False a la falsa medida creada para el fact de muchos a muchos ya que no queremos que se muestre al usuario final. El tab de Dimension Usage queda de la siguiente manera:

clip_image048

Los detalles de la relación muchos-a-muchos se muestra a continuación:

clip_image050

Luego de procesar nuestro nuevo cubo visualizamos los datos y obtenemos los siguientes resultados:

clip_image052

El total de productos se muestra correctamente con 18 unidades. Al agregar la dimensión de cliente y producto los resultados también son consistentes con lo esperado:

clip_image054

Ahora agregamos la jerarquía de representante de venta y notamos que los resultados son consistentes de acuerdo a la solución parcial que tenemos (sólo estamos considerando la relación entre cliente y representante en nuestra tabla muchos-a-muchos, nos falta agregar el producto o categoría):

clip_image056

Como podemos ver, los resultados se están agregando correctamente para el escenario planteado, es decir, la relación muchos-a-mucho sólo suma los valores únicos o distinct a nivel de cada territorio y distrito.

En teoría, para completar la solución sólo faltaría agregar a la tabla de muchos a muchos el campo categoría o en este caso, los productos pertenecientes a la categoría.

 

(INTENTO DE) SOLUCION # 3 - Implementando relación muchos-a-muchos entre tres tablas:

Según lo analizado en el caso anterior vamos a agregar los productos a la relación muchos-a-muchos:

clip_image058

Nuestro fact de muchos a muchos contiene los siguientes valores:

clip_image060

Nuestro fact principal de asignación de ventas se mantiene idéntico al que tuvimos en el intento de solución # 2, pero sí cambiamos las relaciones en el tab de Dimension Usage para indicarle a SSAS que representante de venta y producto se derivan de relaciones muchos a muchos:

clip_image062

Recuerden que el segundo measure group se agrega sólo con el propósito de crear las relaciones muchos-a-muchos, por tanto no existen medidas visibles para el usuario final provenientes de ese fact table.

Explorando los resultados de nuestro nuevo cubo tenemos:

clip_image064

Los valores a nivel de representante se muestran correctos, pero como se muestra a continuación al agregar la jerarquía de producto nuevamente se desvirtúan los resultados:

clip_image066

El problema radica en que a diferencia del modelo relacional, SSAS sólo entiende correctamente las relaciones muchos-a-muchos entre 2 tablas, pero en nuestro caso tenemos 3. A pesar de que el representante de venta 1 sólo vendió 10 unidades del producto 1, el cubo nos muestra 18, incluso nos muestra también ventas del producto 3 a pesar de que no existieron ventas para dicho producto. SSAS entiende de nuestra tabla muchos-a-muchos que hay una relación indirecta entre el representante 2 y el producto 3 a pesar de que no hay ventas para el mismo.

LA SOLUCION - Implementando relación muchos-a-muchos y referenciadas:

Después de darle muchas vueltas al problema y probar muchos intentos más de solución, encontramos lo que buscábamos, pero para no nos bastó con usar las relaciones muchos-a-muchos sino tuvimos que recurrir también a las relaciones referenciadas entre dimensiones (referenced relations). En este punto debo de declarar que nunca vi este tipo de modelaje de estrellas tan particular en los libros de Kimball y otros autores y que probablemente se sale de las bases tradicional de diseño ya que tal como en los casos anteriores nos ayudamos de pseudo-dimensiones o facts que deberán ser cargadas previamente con el mecanismo del ETL para preparar los datos como las necesitamos para obtener los resultados deseados. Aquí debo hacer un paréntesis para dar crédito a mi amigo José Miranda que contribuyó significativamente al análisis para llegar a esta solución.

Nuestro datamart incluye una relación muchos-a-muchos entre la dimensión cliente y producto. Esta dimensión nos obliga a repetir nuestro catálogo de productos para cada cliente, ya que en teoría cualquier cliente puede vender cualquier producto. Por lo mismo, probablemente el tamaño de esta dimensión sea bastante significativo con respecto a otras dimensiones, pero vamos a sacrificar algo de espacio en disco para poder obtener los resultados como los necesitamos.

clip_image068

Nuestra pseudo-dimensión tiene una clave primaria que identifica únicamente la combinación entre el cliente y producto para permitir la relación de esta tabla con nuestro fact muchos-a-muchos:

clip_image070

Por tanto de acuerdo a nuestras reglas de negocio, asignamos el representante de venta al cliente y producto respectivo:

clip_image072

Hemos creado nuestra pseudo-dimensión CustomerAndProduct.dim para que nos permita luego crear las relaciones tipo referenced con cliente y producto. La pseudo-dimensión queda de la siguiente manera:

clip_image074

Ahora la parte clave de nuestra solución, el tab Dimension Usage donde tenemos nuestra pseudo-dimensión con propiedad visible = false para que no se muestre al usuario final, la relación muchos-a-muchos con la dimensión representante de venta y relaciones referenced con cliente y producto:

clip_image076

Ahora exploramos los resultados:

clip_image078

Correctamente tenemos asignadas las cantidades según representante y su jerarquía. Ahora agregamos producto y cliente para tener todas las combinaciones:

clip_image080

Al finnnnnnn!!! Tal como se muestra ahora tenemos la asignación de las ventas por representante tal como se requiere.

Aquí tienen las bases de datos en SQL Server 2008 y proyecto de Analysis Services 2008 usado para la creación de este post. Deben hacer attach para agregar la base de datos Source y DataMart. No olviden cambiar el data source dentro del proyecto de SSAS para que apunte a su servidor.



Alan.

19 noviembre 2008

rsAccessDenied en Reporting Services Integrated Mode

Hola de nuevo. Durante las últimas semanas he estado trabajando en un proyecto de lo más interesante que busca aplicar el ciclo complemento de la Administración del Desempeño (Performance Management): Monitoreo, Análisis y Planeamiento.

Estamos utilizando SQL Server 2008, MOSS 2007 y PPS 2007. Nos hemos encontrado varios retos interesantes que hemos ido superando, uno de ellos es el que menciona el título de este post. Adicionalmente para maximizar la experiencia del usuario final estamos también usando Reporting Services 2008 (SSRS) integrado con Sharepoint.

Luego de revisar y aplicar la documentación de configuración de SSRS en modo integrado, todo parecía ir "viendo en popa" hasta que notamos que cuando configurabamos a los usuarios a nivel de Sharepoint sólo con permisos de "Visitor", "Contributor" o cualquier otra combinación de permisos que no sea Owner del website, el usuario podía llegar hasta cualquiera de los reportes para los que tenga acceso pero a pesar de ello no podía ver los resultados del mismo. El mensaje completo que se mostraba era:
"The permissions granted to user xxxx are insuficient for performing this operation (rsAccessDenied)".

Definitivamente el mensaje de error nos llevaba a todos lados menos a donde debía. Luego de un buen rato de indagación, mi talentoso amigo y compañero José Rivera (más conocido como Joe en el bajo mundo), detectó que el problema se debía a que los Shared Data Sources que son usados por los informes deben tener el Approval Status en "Approved" para que los reportes puedan funcionar correctamente y no mostrar el dichoso mensaje de error.

Por tanto tengan cuidado cuando publican sus Data Sources desde el Business Intelligence Development Studio o por medio de un upload ya que estos se crean por defecto con el Approval Status de "Pending" los cuales sólo permiten la ejecución correcta de los reportes a los usuarios con privilegios de "Owners" sobre el website de MOSS.

Los screenshots valen más que mil palabras, ahí les van:











Gracias Joe!

Alan.

14 noviembre 2008

SQL Server 2005 SP3 CTP

Hace unos días se publicó ya un CTP del SP3 para SQL 2005. La recomendación es no ponerlo sobre un entorno de producción. El SP3 tiene todos las actualizaciones hasta el "Cumulative Update 6" y algunos adicionales.

Pueden descargarlo desde aquí.

Alan.

01 noviembre 2008

Experiencias con el examen 70-452: Designing a Business Intelligence Solution

Hoy recibí el correo y con sorpresa recibí una carta de Prometric con los resultados del examen 70-452 que tomé como examen beta hace casi 2 meses (ya casi lo habia olvidado). Sin violar la confiabilidad del examen les puedo decir que para mi suerte lo aprobé con 865 de un mínimo de 700 como resultado. Me preparé aproximadamente durante unos 10 días antes del examen leyendo en la web y en los Books OnLine de SQL 2008 acerca de cada item que describe el Preparation Guide del examen. Si ustedes también trabajan en el día a día con BI creo que pueden lograr el mismo resultado ya que a mi parecer las preguntas se enfocan mucho en las tareas del día a día y no cosas tan rebuscadas o que nunca usaríamos en el mundo real de diseñar y desarrollar este tipo de soluciones.

Sólo un par de detalles más con respecto a mis resultados en el examen. De las 6 áreas de evaluación salí con menos puntaje en la de "Administering a BI Solution" y con "Strong" en la de "Designing Data Mining Models" y en "Designing an Analysis Services Database".

Según el web site de Microsoft, el examen estará disponible para el público en general este mes de noviembre. Suerte para todos los que lo tomen y no olviden estudiar un poquitín.

Alan.

12 octubre 2008

En vivo desde el BI Conference (día 3)

Si han estado siguiendo esta serie, Edwin Torres, mi compañero en Nagnoi, Inc. está colaborando con este blog para darnos sus impresiones de lo que fue el Microsoft BI Conference dado en Seatle la semana que pasó. Aqui la tercera entrega y final de esta serie. Los dejo con Edwin.

"El futuro de BI…

Que nos depara el futuro en BI. Bueno como ya hemos mencionado la democratización del BI es algo en el futuro cercano. ¿Pero, qué hay más allá? Pues probablemente en el futuro mediano comiencen a ser utilizadas en masa nuevas tecnologías de interacción con los datos. Por ejemplo, el último día del Microsoft Business Intelligence Conference 2008 hicieron una demostración de PerformacePoint usando el nuevo Microsoft Surface (microsoft.com/surface).

Para quienes no conozcan, el Microsoft Surface es un producto que le permite a una o varias personas mediante "Multi-touch screen" manipular los datos de forma natural utilizando las manos. Este producto usualmente luce como una mesa con una pantalla como tope. Los usuarios se sientan alrededor e interacción con el escritorio.

Este demo de PerformacePoint era con datos de la ciudad de Seattle como por ejemplo cuanta energía o agua está utilizando cada edificio. En la demostración utilizaron el Microsoft Virtual Earth para navegar la ciudad de Seattle en 3D, luego de tener el mapa de la ciudad encendieron las funcionalidades de BI y cuando la presentadora indicaba que quería ver el agua utilizada por los edificios de Seattle unas gráficas 3D de barras se presentaron en cada edificio indicando el agua consumida. Esta sin lugar a duda fue la presentación de visualización más impresionante de la conferencia. Una integración de tres tecnologías impresionantes como Surface, Virtual Earth y PerformancePoint.

Por otro lado, sin lugar a duda en el futuro a corto, mediano y largo plazo lo que nos espera son mejoras en el rendimientos y el almacenamiento de los servidores. Esto debido a que cada año que pasa la cantidad de datos que guardan las empresas son monumentales y en el futuro hablar de teras de datos será normal incluso para las pequeñas empresas por lo cual ya nos imaginamos como serán los grandes data warehouse.

Hay muchos retos y muchas oportunidades en el futuro de BI.

Edwin"

08 octubre 2008

En vivo desde el Microsoft BI Conference (día 2)

Seguimos las serie transmitiendo en directo desde el 2do Microsoft BI Conference. Estas son las notas de Edwin Torres, del segundo día de conferencia (Oct 7, 2008).

"Lo que más hemos escuchado

En estos días en el Microsoft BI Conference 2008 hemos escuchado mucho sobre “democratization of BI”, “self-services reporting”, “think bigger about BI”. Pero, ¿de qué se tratan estos conceptos?

En mucho de los modelos actuales se invierte mucho dinero y esfuerzo en el desarrollo de BI para una empresa u organización, pero al final quien utiliza estas nuevas herramientas son muy pocas personas; generalmente en altos niveles de jerarquía. Entonces existe un gran número de personas que no están obteniendo beneficios de la nueva inteligencia que ha ganado la empresa. Del mismo modo si la entrada de datos depende de esas personas que no ven el resultado final de su labor no sentirán una recompensa directa por su trabajo.

Lo que tenemos ahora son pocas personas beneficiándose y muchas que podrían estar beneficiándose pero el BI no ha llegado hasta ellos. Es por eso que Microsoft está empujando el concepto “democratization of BI” para que más personas de todo los niveles en la empresa comiencen a beneficiarse del BI.

El que todas las personas tengan más conciencia de la inteligencia de la organización contribuirá a que lo hagan parte de su cultura organizacional.

Acompañando a este modelo viene otro llamado self-services reporting, que busca facilitar a las personas que no están especializadas en IT el crear sus propios reportes, compartirlos y analizar los datos. Esto lo quieren alcanzar utilizando las mismas herramientas que los usuarios ya conocen: Excel 2007 y Share Point. Para cumplir con esta meta se están desarrollando los proyectos Gemini y Kilimanjaro que fueron anunciados en la conferencia de este año. Microsoft habla sobre ellos en esta nota de prensa (http://blogs.msdn.com/bi/default.aspx).

Algo muy interesante que encontramos en los “Breakout Sessions” de la conferencia fue un proyecto experimental en el que han implementado un “add-ins” de “data mining” para Excel 2007. Pudimos ver su funcionamiento y fue impresionante. El propósito de esta herramienta es lograr llevar hasta el usuario final la capacidad de ir más allá en sus análisis y poder ver tendencias que con las herramientas regulares no hubiera visto. Más información de este add-ins y una versión web del mismo se puede encontrar en su portal (http://www.sqlserverdatamining.com/ssdm/Default.aspx?tabid=102&Id=374).

Nuestra principal lección aprendida de esta conferencia es “Think Bigger About BI”. ¿Por qué? Pues usualmente tenemos unas pre-concepciones de BI que lo limitan en su alcance pero realmente BI es un campo, que al igual como el internet, puede alcanzar muchísimos campos fuera de las empresas y adentrarse en otros territorios como la educación, salud y no solo en organizaciones si no que en el futuro podremos estar utilizando BI en nutra vida diaria para nuestra propia salud, relaciones interpersonales y alimentación. Los límites lo impone nuestra imaginación."

Gracias Edwin!

En vivo desde el Microsoft BI Conference (día 1)

Hello,

En esta ocasión no tuve la oportunidad de asistir al BI Confence en Seattle - Washington, pero sí mis compañeros de Nagnoi Edwin Torres y Alexis, quienes me cuentan que el evento realmente está sobrepasando sus expectativas con expositores y contenido de primer nivel.

Edwin en una excelente iniciativa me está enviando sus notas con los puntos más saltantes de cada día de evento, asi que aquí va el reporte del primer día (Oct 09, 2008):

"En Seattle, Washington comenzó hoy el Microsoft Business Intelligence Conference 2008. Esta es la segunda conferencia que realiza Microsoft enfocada en Business Intelligence (BI). Ha sido un día intenso en el nos hemos enterados de muchas noticias muy emocionantes.

La primera fue la introducción de lo que llamarón las capacidades “self-service reporting” para el análisis, creación y publicación de reportes. Acompañándolo estuvo la demostración del Proyecto Gemini que es una de las herramientas principales en este nuevo concepto de “self-service”. El cual le dará al usuario final la capacidad de crear reportes, publicarlos, compartirlos y analizar datos para poder tomar mejores decisiones. El “Proyecto Gemini” que será lanzado con el nuevo servidor enfocado en BI (del que hablaremos en breve) es un addon para Excel que incrementa sus capacidades de BI. Esta nueva herramienta mejoras el performance de manejo de datos en Excel. En la presentación, Donald Farmer, hizo una demostración con unos 20 millones de records, los que pudo ordenar y filtrar sin que se viera lento en ningún momento.

El propósito del nuevo concepto “self-services reporting” es permitirles a los usuarios crear sus propias herramientas de BI y compartirlas. De este modo el personal de IT libera tiempo que pueden utilizar en asuntos más importantes. Además, la utilización de Excel como plataforma de entrada a este nuevo concepto permite que los usuarios tengan un ambiente familiar que no les parecerá para nada extraño, lo que ayudará a la rapidez con que se adaptan y la facilidad con que lo aceptan.

El segundo de los anuncio fue el lanzamiento del proyecto “Kilimanjaro” el primer trimestre del 2010. Este proyecto va aumentar la capacidad del SQL Server de hacer grandes deployments de BI (teras de datos) y soportar otras herramientas como el mencionado Proyecto Gemini. Dentro de poco tendremos la oportunidad de probar estas nuevas herramientas a través de la comunidad Microsoft.

Junto con estos dos emocionantes anuncios, se presentó también esta mañana la integración de DATAllegro al nuevo SQL Sever lo que permite, según pudimos ver, la creación de gigantescos data warehouses que se mueven a una gran velocidad. En la demostración se utilizó 150 trillones de records para la creación de reportes. Estos 150 trillones de records estaban distribuido en 24 instancias de SQL servers trabajando en paralelo.
Para más información puedes ver el comunicado de prensa de esta mañana que se encuentra en: http://www.microsoft.com/presspass/press/2008/oct08/10-06BI08PR.mspx
"


Excelente Edwin, gracias!

Alan.

30 septiembre 2008

Integración de datos con PerformancePoint Planning

Como les comenté anteriormente di mi primer webcast para MSDN hace unos días. Lamentablemente según problemas "logísticos" no grabaron los primeros 10 minutos de la sesión. Asi que para que tengan la primera parte también adjunto la PPT para que la puedan revisar antes de ver el video. La parte buena es que no se perderán ninguno de los demos.

Adicionalmente les recomiendo leer la documentación de PerformancePoint Planning para integración de datos dónde encontrarán todos los detalles de los pasos que se explican en el webcast, así como información específica con respecto a toda la estructura de la base de datos Stagging y de los Stored Procedures que se usan en la integración.

El proyecto de SSIS y los scrips usados en la demostración así como la configuración requerida pueden obtenerlos desde mi post anterior. Y si quieren mejor en mejor resolución lo pueden ver o descargar directamente desde el site de Microsoft.

Alan.

28 septiembre 2008

Código fuente (SSIS) del Webcast de Integración de Datos en PPS Planning

He publicado el proyecto con el paquete de SSIS que usaré en el webcast en mi SkyDrive, lo pueden bajar desde aquí:


Es importante resaltar que este paquete está basado en el que realizó Alejandro Leguízamo de Solid Quality Mentors para su presentación en el TechEd 2008 para el mismo tema (versión inglés). El paquete contiene los pasos detallados para poblar la dimensión de Product de nuestro modelo usando datos fuentes de la base de datos AdventureWorksDW.

Antes de correr este paquete necesitan hacer lo siguiente en el Planning Administration Console (PAC) y en el Planning Business Modeler (PBM) según aplique:
  1. Crear una aplicación de PPS Planning llamada "AdventureWorksCycles" con AdventureWorksCycles_App_DB como Application Database y AdventureWorksCycles_Stg_DB como Staging Database.
  2. Crear el Model Site por defecto con nombre "AWC".
  3. Crear una nueva dimensión con nombre "Product" y agregue las siguientes propiedades: ProductKey(int), ProductCategory (text), ProductSubCategory(text), Color (text), Size (text), ListPrice (money), Fingerprint (text).
  4. Assignar permisos de Data Administrator y de Modeler a su cuenta de usuario en PAC. Asignar permisos de Owner a su cuenta sobre la base de datos AdventureWorksCycles_Stg_DB.
  5. De ser necesario abrir el paquete CargarProductos.dtsx y actualizar los Data Connections para que apunten a su servidor ó instancia donde tengan las bases de PPS Planning.
  6. Actualizar las variables que apuntan a la localización (path) de los scripts LoadToApp y SyncToStg. Ambos archivos se incluyen en el folder Code del zip que estoy publicando aquí.

Enjoy!

Alan

17 septiembre 2008

Webcast: Integración de datos para los modelos de planificación de PerformancePoint Server 2007

Este será mi primer webcast a través de MSDN (espero que no sea el único) pautado para el 29 de septiembre a las 5pm (GTM-5 Bogotá, Perú) ó 6pm (GTM-4 Puerto Rico, La Paz). 

Lo que voy a estar presentado son algunos tips y mejores prácticas para cargar datos a los modelos que se crean con PerformancePoint Server (PPS), entre ellas el uso de archivos CSV, PPS Data Sources y SQL Integration Services (SSIS) como plato fuerte. El webcast no profundizará en los conceptos básicos de PPS Planning ni de cómo se crean los modelos, sino más bien se enfocará en la parte de carga de datos. Para obtener información introductoria de lo que es PPS Planning y sus componentes puede referirse a http://msdn.microsoft.com/es-es/library/bb795334.aspx.

Para acompañarme en el webcast inscríbanse en el siguiente link http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032386333&Culture=es-AR

Alan

 

E-Book "Introduction to Microsoft SQL Server 2008" - Gratis!

El último boletín de Microsoft Press ha publicado esta oferta gratuita para descarga, el autor principal es Peter DeBetta, MVP en SQL. El libro revisa las principales nuevas características de esta nueva versión de SQL sin llegar a mucho detalle pero lo suficiente para entender qué hay y cómo funciona.

 

Un extracto en inglés comentando el libro: DeBetta and his coauthors—Greg Low and Mark Whitehorn, who are also database experts, consultants, developers, and writers—divide their discussion of SQL Server 2008 into seven topics: Security and Administration, Performance, Type System Enhancements, Programmability, Storage, Enhancements for High Availability, and Business Intelligence Enhancements. In each of these chapters, the authors describe new or improved features. They could not address every additional capability of or improvement in SQL Server 2008, however, because their view (and thus their readers' view) into the product is an early one. Given the goals of the book—to introduce and explore the product in a preliminary way, both conceptually and practically (with sample code)—readers will find plenty to interest them” (Microsoft Press Newsletter, 09/17/08)

 

Se deben registrar para tener acceso al libro completo aquí http://csna01.libredigital.com/?urss1q2we6

 

Alan

16 septiembre 2008

PerformancePoint support for SQL 2008

Actualmente tenemos la versión de PPS 2007 que sólo trabaja con SQL 2005 SP2 tanto para la parte de Monitoreo & Analysis (M&A) como para Planning. La buena noticia es que según Peter Bull de Microsoft el SP2 de PPS incluirá el soporte para SQL 2008 y se espera su publicación durante el mes de Noviembre.

Alan.

12 septiembre 2008

Mis Top 10 características en SQL 2008

Actualmente, Microsoft le está dando la posibilidad a sus clientes de comprar una licencia de SQL 2008 a precio de SQL 2005, y aunque parecería natural comprar SQL 2008 ya que es la última versión del producto, existen aún muchas compañías en que los DBAs o por políticas a nivel de centro de cómputo, no se quieren “arriesgar” a poner SQL 2008 en producción hasta que pase algún tiempo y se “estabilice” o en otros casos debido a que ya tienen una inversión en 2005, así que porqué comprar una licencia adicional de 2008?

Por tanto, la pregunta del millón que nos hacen los clientes cuando vamos a proponer una solución de BI o Performance Management es qué gano si compro 2008?. Así que me puse a pensar en las top 10 características de mayor impacto para la operación o el día a día del usuario y que obviamente más me gustan de esta versión. Ojo, no es que tengan que migrar todos sus servidores, paquetes, cubos y reportes a 2005, sino analizar si las nuevas opciones ofrecidas “agregan valor” a lo que ya tengo o deseo construir. Algunas de estas características las he comentado en post anteriores, principalmente las relacionadas con BI.

Aquí la lista (no necesariamente en orden de importancia):

Motor relacional:

1. Resource Governor: permite administrar los recursos de CPU y memoria según usuario o aplicación.
2. Data Collector: Permite recoger estadísticas a nivel del rendimiento general del servidor, uso de espacio en disco y otros datos en un data warehouse incluyendo reportes para visualización.
3. Multiserver queries: Permite aplicar el mismo query sobre varias instancias a la vez.
4. Transparent encryption: En el caso que alguien se lleve sus files de datos de SQL (MDFs y similares), no podrá usarlos si los datos están encriptados. Para el usuario de las aplicaciones esto es transparente.
5. Intra-partition parallelism: Mejora sustancialmente el tiempo de respuesta cuando usamos particiones con varios procesadores. Antes sólo teníamos un thread asignado a cada partición, ahora podemos tener más de un thread en la misma partición.
6. Page recovery in mirroring: Cuando falla una página de la BD, ésta puede ser recuperada automáticamente desde la copia “mirror”.

Desarrollo:

7. Intellisense: Nos hace la vida más fácil al momento de crear scripts, tal como cuando programamos en Visual Basic, C#, Delphi, etc. Sencillo pero súper productivo.
8. Geographic data types: Excelente para construir una nueva generación de aplicaciones incluyendo características de posicionamiento a nuestros datos.

BI / DW:

9. Change Data Capture: Permite que el motor de SQL almacene los cambios (inserts, deletes y updates) a una o más tablas en una tabla especial de modo que un mecanismo de ETL podría consumir sólo los cambios desde esta tabla especial y no yendo a buscarlos en toda la tabla original que puede tener millones registros. Ver Novedades en SQL 2008 Parte 1.
10. SSIS Data Profiling: Ver Data Profiling en SSIS 2008.
11. SSIS Cache Lookup: Ver Novedades en SQL 2008 Parte 1.
12. SSAS scale-out: En lugar de tener un SAN compartiendo los datos para 2 instancias distintas de SSAS para tolerancia a fallos de cualquiera de los servers, ahora también podemos hacer que esas 2 instancias estén mirando a su propia copia de sólo lectura de los datos y que ambos atiendan las peticiones de los usuarios.
13. Mayor rendimiento de SSRS sin IIS: Ver Novedades en SQL 2008 – Reporting Services.
14. Mejoras de visualización en SSRS: La adquisición de Dundas Charts, Dundas Gauge, Tablix y las mejoras en el Report Desginer permiten crear reportes de manera más fácil y con excelente calidad gráfica.

Bueno dije top 10, pero me salieron 14, nadie es perfecto no? :)

Alan.

20 agosto 2008

Demo: Reporting Services 2008

Hola de nuevo,

Lo prometido es deuda, aqui les muestro un videito que hice para MSDN con el fin de mostrar algunas de las nuevas características gráficas de Reporting Services 2008 creando un informe con apariencia de Dashboard.

En el video se muestra el uso del nuevo control Gauge que nos permite mostrar indicadores en forma gráfica para dar énfasis a valores importantes del negocio. Luego se muestra la creación de una gráfica resaltando alguna de las nuevas capacidades como mayor flexibilidad para la personalización. Finalmente se muestra el uso del nuevo control Tablix, híbrido del control Table y Matrix que ya teníamos en la versión anterior. Tablix es mil veces más flexible que los dos controles anteriores ya que nos permite combinar en las columnas/filas más de una dimensión (por ejemplo Categoría de Productos y Trimestres). Para lograr un efecto similar en la versión anterior no nos quedaba otra opción que crear más de un objeto Table ó Matrix, ó preparar los datos en el dataset. Como se ve en la demostración ahora es cuestión de un simple Drag and Drop.

Este es mi primer videito para el blog, así que espero sepan disculpar la producción (ruiditos al final y otros efectos especiales). Espero sus comentarios.

UPDATE Sep 03, 2008:

Incluyo el proyecto completo del informe demo creado para este video que lo pueden descargar desde aqui:


Para que el informe les funcione deben de descargar la base de datos de ejemplo AdventureWorksDW de SQL 2008 que la pueden descargar desde CodePlex.

Alan.

07 agosto 2008

La espera terminó: SQL Server 2008 RTM ya está aqui!

Hola a todos allá afuera,

Ya que estoy de vacaciones :), he estado algo (bastante) desconectado de lo que está pasando en el mundo de Business Intelligence y otros males (y créanme que hace bien de vez en cuando).

Bueno aprovechando este momento de conexión quería comentarles que Microsoft publicó finalmente la versión RTM de SQL Server 2008 en TechNet y MSDN, la cual pueden descargar si tienen alguna de estas suscripciones. Y si no, no se desesperen, también publicaron una versión de evaluación de 180 días que pueden descargar desde aquí.

El lanzamiento oficial de esta nueva versión de SQL Server marca un hito para todos los que estamos en este mundo de bases de datos y soluciones de BI, a la vez que nos empuja a seguir aprendiendo y maximizando el uso de estas herramientas para agregar valor a nuestros clientes y así seguir siempre en la cresta de la ola.

Tengo listo ya listo un videito de las nuevas características gráficas de Reporting Services 2008 que estaré publicando muy pero muy pronto. Nos vemos.

Alan

10 julio 2008

MVPs de Latinoamérica

Fernando García Loera, MVP Lead para Latinoamérica, ha publicado una lista actualizada de los MVPs de la región para cada producto/tecnología. Además se incluye links a los blogs o páginas personales de cada uno de nosotros.

Pueden ver la lista en el siguiente link:

http://blogs.msdn.com/mvplead/archive/2008/07/09/mvp-blogs-de-latam.aspx

Alan.

01 julio 2008

Data Profiling Task en Integration Services 2008

Hola a todos, estoy de vuelta. En esta ocasión vamos a revisar las posibilidades que nos da Integration Services 2008 para hacer data profiling o analizar perfil de nuestros datos. La respuesta la encontramos en el componente del mismo nombre Data Profiling Task que se encuentra dentro de los componentes del Control Flow. Como vamos a ver más adelante, este nuevo componente nos permite de una manera fácil hacer un análisis completo de nuestros datos resumiéndose en su distribución y calidad.

El funcionamiento del Data Profiling Task es bastante sencillo:

1. Definimos la conexión, tabla y campo(s) de donde vamos a alimentar los datos que queremos analizar,
2. Definimos la conexión de salida en donde queremos que se guarden los resultados del análisis (un archivo XML en el sistema de archivos ó una variable),
3. Seleccionamos el tipo de análisis que queremos realizar y configuramos los parámetros requeridos (si alguno), y
4. Inspeccionamos los resultados con el Data Profiling Viewer ó leemos los resultados y tomamos alguna opción haciendo una interpretación (parsing) de los resultados de la variable XML.

El Data Profiling Task nos permite realizar los siguientes tipos de análisis (profile requests):
• Candidate Key Profile Request: Para determinar si un campo puede servir como identificador (key) mostrando el porcentaje de valores únicos.
• Column Lenght Distribution Profile Request: El mínimo y máximo tamaño de variables de tipo cadena. Muestra también la distribución de los valores según el tamaño de los mismos.
• Column Null Ratio Profile Request: Muestra el porcentaje de valores nulos de un campo.
• Column Pattern Profile Request: Para identificar la distribución de nuestro datos según patrones en su contenido.
• Column Statistics Profile Request:
• Column Value Distribution Profile Request: Para identificar la distribución de nuestros datos, número de valores únicos, cantidad y porcentaje de los valores.
• Functional Dependency Profile Request: Para identificar relaciones de dependencia entre distintos campos y detectar posibles violaciones. Por ejemplo la relación entre los campos País y Capital: Perú  Lima, España  Madrid, Puerto Rico  San Juan, si se detecta un campo que no siga la dependencia, por ejemplo Perú  Cusco, esto se mostrará como una violación y disminuirá el índice de dependencia entre estos 2 campos.
• Value Inclusion Profile Request: Para verificar si los valores del campo en una primera tabla (hija), están contenidos en una segunda tabla (padre). Esto también nos sirve para validar la calidad de nuestros datos como la existencia de cada producto vendido en la tabla de [Ventas] dentro de nuestra tabla de [Productos].

Vamos a ver paso a paso y gráficamente la configuración y resultados de cada uno de los tipos de análisis. Para realizar los ejemplos que se muestran a continuación he usado la base de datos AdventureWorksDW que viene con la instalación de SQL 2008 y principalmente de la vista llamada [vNewCustomers].
Cree un nuevo proyecto de SSIS:



Agregamos un DataProfiling Task desde el Toolbox al Control Flow:




Editamos las propiedades de conexión para la salida o output de los resultados del data profiling:




Indique la ruta a donde quiere guardar los resultados:



Ahora vamos a cerrar las propiedades del Data Profiling Task para crear un nuevo objeto conexión ADO .NET para indicar cuál será la fuente de donde obtendremos los datos para el análisis:




Ingrese los detalles de la conexión, en este caso apuntamos al servidor local y a la base de datos AdventureWorksDW:




Ahora que tenemos lista nuestra conexión de entrada y salida para nuestro profiling abrimos nuevamente el componente y en la pestaña de Profile Request veremos los tipos de análisis disponibles:



Candidate Key Profile Request: Vamos a comenzar por analizar cuál sería la posibilidad de usar el campo [Phone] como una clave que permita identificar a nuestros nuevos clientes. La configuración utilizada sería:





La propiedad ThresholdSetting nos permite indicarle al componente que queremos alcanzar un índice mínimo con respecto al potencial del campo seleccionado para ser un identificador único o clave primaria (donde 100% indicaría que es todos los registros son únicos). En este caso le estamos indicando que SÓLO queremos que escriba los resultados en el archivo XML de salida si el índice (threshold) es mayor o igual que 95%. Otros valores para la propiedad ThresholdSetting son “None” (no queremos restringir el output del análisis) ó “Exact” (sólo queremos output si es un key único). Finalmente, el MaxNumberOfViolations le dice al componente cuántos registros con violaciones como máximo queremos incluir en el archivo de salida de manera que podamos restringir esta cantidad si en caso tenemos cientos o miles de registros inválidos.




Ejecutamos el paquete:




Ahora veamos con el DataProfileViewer los resultados:



Una vez en el Data Profile Viewer abrimos el archivo XML que indicamos al componente como output. En este caso como se ve a continuación no tenemos resultados lo cual no es un error sino que el profiling no cumplió el índice de 95% o más y por tanto no escribió resultados:





Probemos ahora bajando el threshold a 60%:



Ejecutamos el paquete nuevamente y verificamos los resultados:







Como se ve, el Data Profile Viewer nos muestra la información de conexión en el lado izquierdo incluyendo el tipo de análisis que hemos aplicado a la tabla o campo, eso nos ayuda mucha en la navegación cuando tengamos más de un análisis aplicado a la misma tabla o campo.




Con respecto a los resultados en la imagen anterior vean que el Key Strength del campo Phone es sólo de 61.20%, lo cual quiere decir que tenemos números de teléfonos repetidos en nuestra tabla. Eso lo podemos constatar con los resultados en el panel Key Violations incluyendo información la cantidad de violaciones incurridas y un porcentaje con respecto al total de los datos.


Adicionalmente podríamos presionar el botón DrillDown o darle doble click a uno de los resultados para automáticamente conectarnos a los datos de la tabla y ver cuáles son los que produjeron la violación seleccionada como se ve a continuación:





Algo bastante interesante del archivo de output en XML es que podríamos utilizar su contenido para automatizar algún proceso leyendo directamente sus resultados, ya sea desde el archivo físico directamente o sino desde una variable. En la imagen que se muestra a continuación se muestra parte del XML del archivo con los resultados del Candidate Key Profile:


Ahora que ya tenemos las bases de cómo funcionan las cosas con el Data Profiling Task veremos los demás tipos de análisis usando el mismo componente.





Column Leght Distribution Profile: Aplicamos este análisis a la columna Phone:








En los resultados podrán notar que tenemos 2 tipos de teléfono, de 12 caracteres y de 19 caracteres.







Según se muestra los teléfonos de 19 caracteres son números internacionales:





Column Null Ratio Profile: Aplicamos este análisis a la columna MiddleName.










Como se muestra en la siguiente imagen, el 43.77% de los valores son nulos:







Column Pattern Profile: Este tipo de data profiling nos permite conocer los patrones de nuestros datos expresados en forma de Regular Expressions (RegEx) de manera que de una serie de datos podamos identificar si siguen o no determinado esquema (la expresión regular). Por ejemplo si tenemos un campo que guarde los números de tarjetas de créditos que se compone de 4 grupos de 4 dígitos separados o no por un guión (-), tendríamos un RegEx como el siguiente: \d{4}-?\d{4}-?\d{4}-?\d{4}. Si luego de aplicar el data profiling obtenemos RegEx distintos al esperado tenemos problemas con la calidad de nuestros datos.


Para más detalles con respecto a las propiedades de configuración de este tipo de profiling se pueden referir a http://technet.microsoft.com/en-us/library/bb934022(SQL.100).aspx. Para ver una librería de Regular Expressions se pueden referir a http://regexlib.com.




En nuestro demostrativo aplicaremos el análisis al campo teléfono Phone.









Los resultados que se muestran a continuación nos muestran los 2 RegEx encontrados, uno para números locales y otro para números internacionales.






Column Statistics Profile: Este análisis se puede aplicar sólo a variables numéricas y obtendremos algunas estadísticas como se muestra en la siguiente imagen:








Column Value Distribution Profile Request: Para conocer más acerca de los valores dentro del campo Educación aplicamos el siguiente análisis. Noten que también podemos configurar la propiedad FrequentValueThreshold para filtrar los valores resultantes que se repitan un porcentaje mayor o igual al ingresado. Para efectos demostrativos dejamos este valor por defecto.







Aquí los resultados. Además de la distribución de los valores obtenemos también la cantidad de valores distintos.







Functional Dependency Profile Request: Como mencioné en la parte inicial con este tipo de análisis podemos ver como se relaciona un campo con otro y detectar posibles violaciones. Un ejemplo adicional podría ser la relación entre el país de residencia de una persona y el código telefónico del país de su número telefónico fijo. En este caso si mi país de residencia es Perú mi número telefónico debería de comenzar con el número 51, si mi país es Argentina con el 54 y así. En el ejemplo que se muestra a continuación mostramos un análisis de dependencia entre la línea de un producto (ProductLine) y la SubCategoría (ProductSubCategoryKey). En un escenario hipotético, la relación entre ProductLine y el SubCategory debe ser de 1 a 1.









Según nuestro escenario hipotético vemos que hay algunas violaciones a la relación entre los campos indicados. Aún así podemos notar que la dependencia del campo ProductLine hacia SubCategory es de más de 92%.





Value Inclusion Profile Request: En este caso podemos referenciar a una tabla maestra (superset) para verificar si todos los valores de mi tabla de transacciones (subset) tiene una correspondencia válida en la primera, esto es perfecto para validar problemas de integridad referencial. Para la demostración se usó como tabla superset a tabla DimProductSubCategory y como subset a la tabla DimProduct; como campo de comparación se seleccionó ProductSubCategoryKey de ambas tablas:





En este caso vemos que todas las referencias en el subset se incluyen en el superset a excepción de los valores nulos. Dependiendo de mis reglas de negocios diríamos que este es o no un problema.





Ahora tenemos ya una idea de las potencialidades del DataProfilingTask de SSIS 2008.


Pueden descargar el proyecto completo usado para este demo desde este link SSISProfilingDemo.zip. El proyecto fue creado con el CTP de Febrero de SQL 2008, pero no deben tener problemas de ejecutarlo en el RC0.


Hasta la próxima,

Alan