10 diciembre 2012

Introducción a Data Analysis Expressions–Parte 2

Como lo prometido es deuda, a continuación incluyo el video de la segunda presentación de esta serie que presenté el último jueves de Noviembre, como parte de las reuniones mensuales del Capítulo de Usuarios de SQL Server en Puerto Rico (PRPASS).

En esta oportunidad hice un rápido recorrido por la primera parte de la presentación, para que los que no pudieron ver la primera parte no estén tan desubicados, pero la recomendación para todo aquel que esté leyendo este post por internet, es que vea el video de la primera parte.

Alguno de los temas discutidos en esta segunda parte son los siguientes:

  • Medidas (Measures) vs Columnas Calculadas (Calculated Columns)
  • RELATED y RELATEDTABLE
  • Row Context vs Filter Context
  • Uso de VALUES
  • Algunos ejemplos con CALCULATE
  • Time Intelligence

Los dejo con el video:

Alan

12 noviembre 2012

Introducción a Data Analysis Expressions (DAX) – Parte 1

Desde hace algunas semanas tenía pendiente publicar la grabación de mi última presentación en nuestro Capítulo local de SQL Server – Puerto Rico PASS. El tema presentado fue el que lleva por título este post. Por temas de tiempo (algo que sin duda, debo mejorar), tuve que partir la sesión en dos partes. Así que la que presento a continuación es la primera parte y espero presentar la segunda durante este mes de Noviembre.

Esta presentación asume que la audiencia ya tiene los conceptos básicos de PowerPivot o de Analysis Services en modo Tabular y cómo crear sus primeros modelos, aunque se hace una breve introducción y se aclaran los conceptos principales durante los primeros 15 minutos. Esta presentación es una variación a las que ya he hecho en oportunidades anteriores pero incluyendo nuevos detalles o ejercicios producto de la evolución.

Listo a continuación los temas tratados durante la sesión:

  • Revisión de los sabores de Analysis Services
  • Acerca de DAX
  • DAX vs MDX
  • Creación de un modelo en PowerPivot / SSAS Tabular Mode
  • Columnas Calculadas vs Medidas (Funciones en Excel 2013)

A continuación los “slides” de la presentación:

Ahora sí, la grabación de la sesión (no olviden ajustar la calidad del video según su preferencia):

Ahora si me despido, no sin antes dejar la promesa para la parte 2 de por lo menos los siguientes tópicos:

  • Agregaciones básicas en DAX
  • Manejo de relaciones entre tablas (RELATED y RELATEDTABLE)
  • Filter Context
  • Funciones para “Time Intelligence”
  • Caso: Conteo de clientes

Nos vemos.

Alan

08 septiembre 2012

Strange Error Loading Excel Files (xlsx) using SQL Server Integration Services

After some months without blogging (too many things happening lately), I’m coming back because I tried to get some answer to my problem in the Internet without any success. Thus, here I am sharing this in order to save you some waste time debugging.

Some days ago, I spent almost 6 hours trying to find “a simple” error (and the solution) related to a SSIS 2008R2 package that loads data from an Excel file and writes it to a SQL Server table without any mayor transformation. The strange thing here was that the package was working correctly in our development environment and “erratically” in our test environment.

The situation was:

  • The SSIS package witch loads the Excel file shown below (Classicare Rx Network 2012 Q2.xlsx), runs correctly in BIDS and when executing it through a SQL Server Agent Job using the current user identity (Proxy Account). The package is using the 32bits version of the ACE Provider.

image

image

  • The SQL Server Agent Job fails when running the SQL Agent Job using the identity of our ETLAccount (a standard domain account created for the SSIS packages execution). The error message indicates that the Excel file cannot be reached as you can see below:

image  image image image

 
“Message Executed as user: NAGNOILAP030-W8\ETLSystem_SQLAgentProxy. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  12:10:44 AM  Error: 2012-09-06 00:10:46.53     Code: 0xC0202009     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  End Error  Error: 2012-09-06 00:10:46.53     Code: 0xC02020E8     Source: Data Flow Task Excel Source [1]     Description: Opening a rowset for "RxNetwork$" failed. Check that the object exists in the database.  End Error  Error: 2012-09-06 00:10:48.16     Code: 0xC004706B     Source: Data Flow Task SSIS.Pipeline     Description: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2012-09-06 00:10:48.16     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-09-06 00:10:48.16     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:10:44 AM  Finished: 12:10:48 AM  Elapsed:  3.261 seconds.  The package execution failed.  The step failed.”

  • However, the strange thing here was that we have many other packages working correctly (to load some manual dimensions without an existing formal data source), including when the SQL Job is configured to use the ETLAccount credentials. For example, the AdjudicationStatus_Medical.xlsx file shown below is one of those files:

image

image

  • The second, strange  thing was that the failing package executed successfully using a local administrator account:

image image

I reviewed several post about using the Excel OLEDB 12 provider (including those about how to use it in 32bits), without getting a solution to my problem. I also found this very interesting post from Dougbert’s Blog entitled “Excel driver now supported on server for use by SSIS”. Beyond the Microsoft official support for Excel running on SSIS servers, the most important part for me was: “…ACE uses the impersonated user’s Windows temp folder to read-write its data. Therefore if your application is using impersonation with an account that does not have a profile on the server (not an uncommon situation), then ACE will not be able to create its temp files.”

So, according to this post, the ACE driver (used by Excel and Access) requires that a user profile exists in the server for the user (identity) running the Excel packages, but in this case we don’t have a user profile for the ETLAccount. At this point I was more confused: how my other packages work correctly and the one for the Classicare..xlsx don’t?

To understand more the mystery, I did try and error tests to check what was causing the package to fail when it was running using the SQL Server Agent Job and the ETLAccount credentials. I created several Excel files with different number of columns and rows.

Several hours later, I noticed that the problem was not related to the file format nor the number of rows (directly). As you can see in the table below (file with only 3 columns), I was able to reproduced the error when my files were more than 1.7Mb in size approximately:

Rows Size (MB) Job Execution Status
65,000 3.60 Failed
35,000 (and more) 1.79 Failed
33,000 1.69 Failed
32,801 1.68 Failed
32,601 1.67 Failed
32,511 1.66 Success
32,501 1.66 Success
32,000 1.64 Success

image

To validate my hypothesis, I deleted some random rows to my original file (Classicare…xlsx) to cut if down to 1.66Mbs and it worked correctly! Having 1.67Mb or more, the Job was failing as expected.

After this findings, I reproduced the server environment in my own laptop just to test if creating the profile for the user account running the SQL Jobs works, but unfortunately it didn’t. Maybe an additional requirement is needed for this to work, but I got only this work, granting local administrator privileges to the user account.

Some days later, I found this interesting post entitled Excel Imports to SQL Server using Linked Servers, where the author states that any 32bit OLE DB Provider require access permissions to the SQL Server temp folder for the user account who run the query. Thus, I granted permissions to my temp folder (C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp) to my ETL account. Again, no good results.

After all my (unsuccessful) tries, my conclusions at this point are:

  • If the account (credential) running your packages has not local admin permissions, you are limited to work with relative small files in size (1.66Mb maximum in my case). The maximum file size could be different in your system.
  • Running the same tests in my laptop, I needed to reduce the file size to 1.28Mb to get it work. This tells me that the exact maximum file size could vary depending on your hardware configuration.
  • After exceeding the size threshold in an Excel file, the ACE Provider needs some permission to write/read some temporary data (it looks it is more than a user profile). If this criteria is not met, you will get strange error messages about problems connecting to the file.
  • My package was failing because it was more than 1.66Mb (12Mb) and the SSIS package execution account was not a local administrator of the machine.
  • Even Microsoft Access is not supported in an enterprise environment (PowerPivot for Sharepoint cannot automate data refresh jobs from Access). I think this should be the same reason for what Excel 2010 is so picky for data loading automation.

My recommendation is:

  • Avoid loading Excel files in a production / automated environment as possible, try comma separated files (CSV) or any other text file format.

Back in my project, we finally “solved” the problem, saving the file as CVS and changing the data source provider of my package from Excel to Flat File. Everything worked correctly this time even running in 64bits.

If you want to share any other information from your experience working with Excel files, feel free to comment.

Alan

19 julio 2012

Puerto Rico PASS Monthly Session–July

When: 2012/07/26 @ 6:15 pm - 9:00 pm
Where:  Microsoft Puerto Rico, Guaynabo, PR 0068
Cost: Free
More information: http://www.prpass.org/calendar/

 

SESSION TOPICS:

Look who’s Blocking
Level: 200 (Intermediate)
Audience: IT Professionals / DBAs

Have you ever faced the situation of trying to retrieve some data and the query keeps waiting on. Why this simple query is taking so long? There is a good chance that you are encountering locking/blocking problems. In this session, we will demonstrate how to recognize, diagnose and troubleshoot common locking and blocking scenarios. We will discuss transactions basics, lock types, escalation, and the infamous deadlock, among others.

 

Speaker: Jose L. Rivera
Abarca Health

Jose Luis has over 9 years of experience developing Data Analytics, Data Warehousing and Business Intelligence solutions using SQL Server technologies, among others. In addition, he shares his knowledge and perspectives on database technologies as PRPASS Community Leader and college professor. He holds the MCTIP in Database Administration, Database Development and Business Intelligence for SQL Server 2008.

 

Some Ideas to Keep in Mind while Designing your Dashboards
Level: 100 (Begginer)
Audience: BI Developers / Information Workers

As part of the new PRPASS’ Executive Series and Soft Skills Development, in this session we will discuss some important ideas and recommendations for effective visualizations and dashboard design according to Stephen Few, a recognized author and thought leader in the field of Business Intelligence. We will show suggestions about what to do and NOT to do while designing a dashboard.
Important note: This session will be totally focused on design and best practices, not in the development or implementation.

 

Speaker: Edwin Torres
Nagnoi, Inc.

Edwin Torres is a Business Intelligence Consultant at Nagnoi. Mr. Torres has been involved in Business Intelligence projects for more than 5 years, covering different industries or verticals such as tourism and healthcare among others. He is also founder of Mi Windows Blog (www.miwindowsblog.com), one of the most important blogs in Latinamerica and Spain wich covers topics around Windows Phone and Windows 8.

13 junio 2012

SQL Saturday Puerto Rico 2012: Un Recuento

El pasado 26 de Mayo se llevó a cabo aquí en la Isla del Encanto el evento SQL Saturday Puerto Rico (#106) gracias a PASS y Puerto Rico PASS. Después de poco más de dos semanas de aquel magistral día, recuerdo con gran nostalgia y alegría lo que allí sucedió, pues congregamos a casi 200 personas (197 para ser exactos) en un evento presencial sin precedentes alrededor de SQL Server. Para nuestra comunidad de usuarios esto es significa un gran logro, por tal motivo no podía hacer menos sino que un recuento de cómo llegamos hasta aquí y que a su vez sirva de inspiración a otras personas que están en la misma aventura en alguna parte del mundo.

image

SQL Saturday by PASS

imageSQL Saturday es un evento creado y auspiciado completamente por PASS (Professional Association for SQL Server), la organización matriz a la cuál pertenece nuestra comunidad PRPASS.  Al momento de escribir este artículo, PASS viene organizando ya 166 eventos SQL Saturday en Estados Unidos y en las principales ciudades del mundo (y sigue creciendo!!!). Si dirigen o pertenecen a un grupo de usuarios afiliado a PASS y están interesados en llevar este evento a su ciudad para darle su propio “sabor”, no tienen que hacer más que visitar www.sqlsaturday.com y reservar la fecha.

 

SQL Saturday Puerto Rico

SQL Saturday Puerto Rico presentó a lo largo de todo un día, 15 sesiones de SQL Server divididas, divididas en 3 tracks (DBA, BI y Application Development) para un total de 19.5 horas de entrenamiento gratuito. El keynote de apertura del evento fue presentado por quien escribe representando a PRPASS, Karla Landrum (t @KarlaKay22) Community Evangelist de PASS y de Mario Ochoa, VicePresidente de Aplicaciones de Plataforma de Microsoft Latinoamérica. Karla hizo un breve pero importante resumen de lo que es PASS y los eventos SQL Saturday. Mario presentó un excelente resumen de los pilares sobre los cuales se basa la estrategia de SQL Server 2012 para manejar datos en distintas perspectivas.

IMG_2118IMG_2123IMG_2112IMG_2124IMG_2146IMG_2085

Érase una vez…

La idea de traer SQL Saturday a Puerto Rico, surgió durante la visita de José Luis Rivera (PRPASS Leader), Guillermo Caicedo (Vice-Presidente de PRPASS) y de este servidor (Presidente de PRPASS) a la conferencia anual de PASS, el PASS Summit 2011 en Noviembre pasado. Luego de regresar a Puerto Rico, solicitamos la fecha a través de la página web. Unos días después comenzó la aventura al recibir la llamada de la gran Karla Landrum. Como mencioné anteriormente, Karla es la coordinadora y responsable de brindar todo el apoyo necesario a todo aquel que quiera realizar un evento SQL Saturday. Sin duda alguna, Karla fue nuestra mejor guía y soporte durante toda la planeación e incluso el mismo día del evento en el que nos honró con su presencia. Un millón de gracias Karla!!!

En aquel noviembre, la fecha de mayo 26 se veía bastante lejos, teníamos 5 largos meses para planificar todo pero debo confesar que a partir de aquel día el tiempo se aceleró. La fecha del 26 de mayo fue una decisión de sentimientos encontrados, pues ese sábado era parte de un fin de semana largo, ya que el lunes 28 era feriado en Estados Unidos (y por ende en Puerto Rico). Para Guillermo y para mi, la idea de realizar el evento un fin de semana de ese tipo era pensando en atraer a la mayor cantidad de presentadores de Estados Unidos, ya que ese podía ser un buen atractivo para que no sólo vengan a dar una charla, sino que aprovechen también en tomarse unos días de vacaciones aquí en nuestra isla. El riesgo de esta decisión era sin embargo a que muy pocas personas participen finalmente del evento, pues durante esos días del verano en Puerto Rico, muchos aprovechan en tomarse el descanso merecido y muy pocos querrían saber de SQL en un fin de semana como ese. Pues como ven, nos la jugamos y por suerte o causa del destino, todo marchó de la forma esperada.

WP_000468La segunda decisión difícil fue escoger el lugar del evento, pues en nuestro pensamiento de atraer a la mayor cantidad de personas, seleccionamos el Centro de Convenciones de Puerto Rico (CCPR) como el lugar apropiado, y es que sin duda, si de bellezas arquitectónicas se trata, el CCPR debe ser una de ellas. Como toda decisión, esta también tenía sus dos lados de la moneda. La parte más riesgosa de elegir un lugar tan “fancy” como este, era sin duda el presupuesto requerido para contar con el local pues es bastante costoso relativamente hablando, y más para una comunidad de usuarios sin fines de lucro y que está acostumbrada a hacer sus eventos en las oficinas de Microsoft sin costo alguno.

Call for Speakers

Lo meses pasaban y se iba corriendo la voz, el Call for Speakers estaba abierto y comenzamos a recibir los primeras propuestas con mucha ilusión. Todo esto nos motivaba cada vez más. Al cierre del Call for Speakers recibimos más de 50 sesiones en total donde la gran mayoría provenía de personas basadas en Estados Unidos lo cuál era muy alentador.

Luego del proceso de selección, hicimos un balance requerido para minimizar el riesgo de contar con muchos presentadores foráneos que puedan cancelarnos, así que finalmente las sesiones finales fueron impartidas por 7 presentadores locales (Jose Luis Rivera, Jorge Sánchez, Elizabeth Córdova, Eduardo Sobrino, Ramón Ponte, Guillermo Caicedo y Alan Koo), y 6 presentadores foráneos que gentilmente nos visitaron desde Estados Unidos (Joseph D’Antonhy, Pam Shaw, Bill Pearson III, Max Trinidad y Janis Griffin).

El día antes del evento, tuvimos una reunión de confraternización y para conocerlos en persona en un restaurante de comida típica local. Realmente pasamos un muy buen rato:

WP_000467WP_000466

Gracias a todos nuestros presentadores! principalmente por las ganas de compartir su conocimiento y experiencia de forma desinteresada lo cuál es de mucho valor; y para los que vinieron de afuera, adicionalmente el gastar de su propio dinero (lo cuál no fue poca cosa $$$) para venir y quedarse en Puerto Rico.

Todos nuestros presentadores hicieron un excelente trabajo y mostraron gran disponibilidad para adaptarse a cualquier situación. Así mismo un reconocimiento a parte lo tiene Daniel Lewandosky por acceder a ser nuestro presentador “backup” por si alguno de los “titulares” tenía algún percance inesperado de último minuto. Por suerte no fue necesario recurrir al plan Lewandosky, pero sin duda Daniel se lleva un reconocimiento especial por su humildad para aceptar tan importante rol sin problema alguno.

IMG_2147IMG_2183IMG_2184IMG_2196IMG_2199IMG_2202IMG_2264IMG_2407IMG_2376IMG_2382

Voluntarios

Este evento no hubiese sido posible si no es por los voluntarios que nos apoyaron durante el día del evento y los días anteriores en los preparativos. Dos días antes del evento, nuestros voluntarios nos ayudaron en diversos roles: doblar camisas, preparar las bolsas de los participantes (era 225 bolsas!!!); cargar, subir y bajar cajas, sacar fotocopias, etc.

Durante el día del evento nuestros voluntarios apoyaron en distintos roles, siendo alguno de los principales el registro, la distribución de encuestas en cada sesión, sorteos y manejo del tráfico. La gran mayoría se reportó desde las 6:30am al CCPR, un gran castigo para ser un sábado, pero lo sobrellevaron como unos valientes guerreros!

Espero no dejar de mencionar a ninguno, todos fueron pieza clave para que el evento fluyera como un reloj: Melissa (Esposa de José Rivera), Sugey Távara (mi esposa), Miriel Cardona (Microsoft), Moraima Valle (Correctional Health), Edwin Torres (nuestro fotografo oficial – Nagnoi), Fernando Cuadrado (Nagnoi), Frank Hernandez (Nagnoi), Néstor Santana (Microsoft), Alejandro Ochoa (Microsoft), John Arroyo, Néstor Carmona (SSS Vida), Ricardo Cabrera (Nagnoi), Charles Rodriguez (Nagnoi) y hasta Carlos Juan (roomate de Fernan quien se especializó en doblar t-shirts)!

IMG_1964IMG_1977IMG_2016IMG_2018IMG_2059IMG_2253IMG_2273IMG_2043

El Presupuesto

imageSin duda alguna, esta fue la parte más riesgosa y algo complicada de la planeación del evento (recuerden que ni Guillermo ni yo habíamos hecho un evento de esta naturaleza en nuestra corta vida). No teníamos ni un sólo dólar en nuestra caja y como se estila en cada SQL Saturday, los gastos de este evento debían ser cubiertos vendiendo paquetes de auspicio. Nuestro presupuesto inicial (mal tasado), iba por 8mil dólares. Ilusos nosotros, pues lo que no habíamos estimado correctamente eran las facilidades del Centro de Convenciones. El presupuesto total del evento estuvo alrededor de los 22mil dólares (imagínense qué pequeño desvío).

Después de un arduo trabajo moviendo nuestros contactos conseguimos 1 auspiciador Diamond, 4 Platinum, 2 Gold, 5 Silver y 2 Bronze. Adicionalmente conseguimos 3 auspiciadores de libros y otros regalos (swags). Gracias a todos nuestros auspiciadores por la confianza y el apoyo recibido!.

Gracias especiales a Microsoft Puerto Rico (Maripura y Miriel, esto es por ustedes!), por el gran apoyo recibido no sólo a nivel financiero, sino también con la coordinación y logística del evento. Gracias a Luis Puerta, quien fue nuestro coordinador oficial (auspiciado por Microsoft), quien corrió el evento behind the scenes y se encargó de que todo fluyera como en los grandes eventos que se hacen en Estados Unidos.

El Registro

El registro fue creciendo de manera tímida mes tras mes. Creo que para Enero teníamos menos de 100 personas registradas, lo cual empezaba a ponernos un poco nerviosos pues teníamos una meta interna de convocar a 300 personas como mínimo. Para correr la voz y aumentar el registro usamos distintos canales: Twitter, Facebook, nuestro eventos mensuales presenciales y cualquier otro evento en el que nos dieran 5 minutos de espacio para promocionar nuestro evento (Guillermo, quien además se graduó como artista gráfico, diseñó unos volantes – flyers – para distribuirlos por todos lados).

IMG_2034Ya para el mes de abril teniamos alrededor de 250 personas, y seguían aumentando diariamente a un mayor ritmo. Los primeros días de mayo teníamos cerca de 270 registrados. Para poder contribuir al costeo del evento, requerimos a los asistentes una cuota para subvencionar su almuerzo (lunch fee) de $10. Esto también lo usamos como un buen indicador para estimar cuántas personas finalmente llegarían al evento (el que pagaba los $10 tenía muy alta probabilidad de llegar versus el que no lo hacía).

Luego de cuadrar los números de acuerdo a los auspicios recibidos, tuvimos que limitar la capacidad del evento a no más de 225 personas (el website del evento ayuda a controlar esto), pues el CCPR nos cargaba más de $40 por cada persona, lo cuál fue la razón para no poder atraer una mayor cantidad de personas (estábamos seguros de llegar a más de 300 personas). Debido a esto tuvimos que ir “afinando” la lista final de registrados, quedándonos sólo con las personas que hayan pagado su derecho de almuerzo. Con esto llegamos a tener una lista total de 225 personas.

Como mencioné al inicio de este artículo, el día del evento recibimos finalmente 197 asistentes quienes comenzaron a llegar al CCPR desde las 7:30 de la mañana. Para hacer el registro utilizamos la funcionalidad de Speed Pass que provee el website de SQL Saturday, con el cual el participante imprime todo su material (rifas e identificación) desde la comodidad de su casa teniendo así un registro rápido el día del evento. De todas maneras tuvimos también toda esta papelería pre-impresa en caso de que la persona no los haya traído. Lo importante es que con esta estrategia nos evitamos tener que imprimir en la misma mesa de registro y evitar la congestión. Sólo había que buscar el nombre de la persona, darle su bolsa del evento (incluido la camiseta oficial) y el material pre-impreso. Cuatro estaciones (laptops) para el registro durante la hora de apertura fue necesario para evitar mayores complicaciones:

IMG_1969WP_000450

El Cierre

IMG_2405Fue toda una sorpresa y emoción total llegar al final del evento, aproximadamente a las 6:15pm, y ver que el auditorio seguía lleno, pues muchos creían que la gente no aguantaría un sábado hasta esa hora.

Más allá de las atractivas rifas preparadas por nuestros auspiciadores para el final del evento, creo que la mayoría se quedó porque disfrutó del mismo y encontró valor en cada una de las sesiones presentadas. Adicionalmente hubo varios “suertudos” que se llevaron un teléfono Windows Phone, un Kindle Fire, una licencia de Office 2010, una tarjeta de regalo de $100 y otros regalos para nada desdeñables.WP_000462

Sé que este post ha sido algo largo, pero espero que lo hayan disfrutado como yo. No me quedan palabras para agradecer nuevamente a las 197 personas que asistieron y a todas las demás que de una u otra forma participaron del evento.

Para nosotros fue una gran satisfacción ver nuestro sueño de SQL Saturday Puerto Rico hecho una realidad y que la gran mayoría lo haya disfrutado al máximo y sacara buen provecho alineado totalmente con los objetivos de nuestra comunidad Puerto Rico PASS: Aprender (Learn), Compartir (Share), Conectarse con otras personas (Networking), Mejorar (Improve) y Crecer (Grow)!!!

IMG_2092Gracias especiales a Guillermo por todo su apoyo incondicional y gran talento, Guillermo ha venido a inyectar a nuestro Capítulo sangre fresca y de la más alta calidad para beneficio de todos los miembros y crecimiento del mismo.

El pasado sábado 26 de Mayo terminamos casi desmayados pero sin duda alguna con todas las ganas al tope para comenzar a pensar en SQL Saturday Puerto Rico 2013!!!. Mientras tanto, si están en Puerto Rico, no dejen de visitarnos en nuestras sesiones mensuales en las oficinas de Microsoft, todos los jueves de fin de mes como es ya costumbre.

Si se quedaron con las ganas de más fotos (seguro que sí), pueden verlas todas en nuestra página de Facebook (www.fabook.com/prpass), aquí la última (de este post) para el recuerdo:

 image

Alan

24 abril 2012

Agregaciones personalizadas en Analysis Services: Eventos no agregables en el tiempo (Event Snapshots)

Probablemente alguna vez hayan tenido o tendrán con lidiar algún requerimiento de negocios que les pida algo parecido a:

  • Cuál es el número de miembros (socios / clientes / matrícula) por mes (los clientes se dar de alta o baja en cualquier momento o según un contrato).
  • Cuál es el monto de reclamaciones según su estatus (Ej. abiertas, en proceso, pagadas, etc.) por mes.

En todos estos casos, el usuario requiere medir un determinado evento dentro de un mes o frecuencia de tiempo requerida, cuyo resultado no es agregable (sumarizable) con otra instancia de tiempo. Por ejemplo, no podemos sumar la membresía (total de socios) de Enero con la de Febrero, ya que podríamos estar duplicando o contando incorrectamente, socios que estaban afiliados durante ambos meses o a quienes estuvieron sólo uno de los dos meses.

De igual manera, sucede mucho en la industria de seguros o en algún proceso que conlleve diferentes estados para una transacción o evento, donde se requiere contar la cantidad de eventos de acuerdo al estado. Por ejemplo, una pregunta típica de negocios en seguros de salud es saber cuál es el inventario de reclamaciones abiertas (que no han sido adjudicadas o pagadas) mes tras mes, ya que esto permite hacer una proyección del flujo de caja requerido para cubrir dichos compromisos en el futuro cercano.

Este tipo de preguntas (en donde los eventos no son agregables), es referido en algunos libros o documentación en inglés como “event snapshots” lo que sería como una foto en el tiempo de dicho evento.

El objetivo de este post es mostrarles cómo he manejado estas situaciones usando Analysis Services 2005, 2008 o 2012 (si usan SQL Server 2012, sería apropiado decir específicamente Analysis Services Multidimensional).

Analysis Services incluye “out-of-the-box” varios tipos de agregaciones para medidas que no pueden ser sumadas directamente (semi-additive measures), esto incluye Average of Children, LastNonEmpty, Distinct Count, etc., pero para nuestro caso, ninguna de las existentes nos resuelve la pregunta de negocios y es por ello que debemos recurrir a las medidas calculadas usando MDX.

Hay por lo menos hay dos opciones para obtener los resultados requeridos usando Analysis Services y MDX para cada mes (podría ser día, año o cualquier otra unidad de tiempo), basándonos en una tabla de hechos (fact table) previamente cargada en nuestro Data Warehouse (DW) relacional :

  1. Basado en una tabla de hechos que guarde las transacciones de matrícula o registro (FactMatricula o FactEnrollment):
    Esta tabla tendría algo muy similar a la transacción original que dio de alta a ese miembro (por ejemplo: [FechaDeRegistro], [MiembroID], [FechaDeBaja]).
  2. Basado en una tabla de snapshot (FactEnrollmentSnapshot
    Esta tabla guardaría la “foto” de los miembros existentes en cada mes.

Podemos discutir los pros y contras de cada una de las opciones anteriores, como por ejemplo:

  • La opción 1 consumiría menor espacio en el DW.
  • La opción 1 es solamente accesible desde herramientas que se puedan conectar a cubos de Analysis Services. (Claro, algún usuario intrépido, podría usar T-SQL para hacer consultas a nuestro DW directamente, pero requeriría el uso de BETWEEN en la sentencia).
  • La opción 2 puede ser más rápida para generar resultados ya que los valores persisten ya en disco (menos procesamiento).
  • La opción 2 puede ser más simple de implementar en MDX versus la opción 1.
  • La opción 2 no requiere una consulta muy sofisticada (uso de BETWEEN) si se usa alguna herramienta que conecte al DW.

En mi caso, les voy a presentar a continuación cómo solucionar el problema usando la opción 2, pues en la mayoría de los proyecto en los que he trabajado, el poder obtener los resultados desde los dos mundos (relacional y cubos / multidimensional) representa una gran ventaja. La segunda razón para seguir esta estrategia es que en el caso de soluciones de BI o Data Warehouse, el consumo de espacio en disco  puede ser de menor preocupación que los tiempos de respuesta (resultados rápidos).  Este último paradigma puede cambiar si usamos bases de datos en memoria como PowerPivot o Analysis Services en modo Tabular (pero eso es cuestión de otro post interesante).

El siguiente diagrama muestra el esquema relacional en mi DW, usando modelamiento dimensional:

image

Por tanto, en este ejemplo tenemos 4 dimensiones (DimMember, DimGroup, DimLineOfBusiness y DimDate), y un fact table que guarda un snapshot mensual de los miembros registrados por cada grupo y línea de negocios. Un extracto de los datos que guarda nuestro fact tables se muestra a continuación usando T-SQL (pueden ignorar las tres últimas columnas del query):

SELECT TOP 1000 [MonthUniqueID], [DimMemberID], [DimGroupID], [DimLineOfBusinessID]
                            ,[ETLExecutionLogID], [ETLBatchLogID],[ETLLastUpdate]
FROM [DW_DDS].[member].[FactEnrollmentSnapshot]

image

Como se puede apreciar en la imagen anterior, tenemos una línea para cada año-mes (yyyymm) en donde tuvimos miembros registrados (incluyendo el grupo al que pertenecían y la línea de negocios).

El siguiente paso sería agregar dichas tablas a mi proyecto de Analysis Services en forma de dimensiones y measure groups para los fact tables. No voy a profundizar en los detalles de cómo hacerlo, ya que esto es la parte básica de Analysis Services (pueden revisar el video de Fundamentos de Analysis Services si necesitan ayuda en este punto). Lo único importante de mencionar aquí, es que ya que estamos contando la cantidad de miembros existentes para cada mes, podemos agregar una columna calculada [_Enrollment Count] en nuestro Data Source View que guarde la cantidad de miembros en cada línea, es decir 1. Esto nos facilitará el conteo.

image

Luego de haber hecho todo el trabajo básico para agregar el fact table y la nueva medida a nuestro cubo, podemos examinar los resultados y veremos que por defecto Analysis Services agrega (suma) todos los registros sin “entender” que la membresía no se puede agregar por encima de año-mes (el número debe estar alrededor de 200 a 300 mil miembros en cada mes, trimestre o año):

image image

Si filtramos o expandimos los resultados a nivel de mes (año-mes), tenemos los resultados correctos en términos de negocios, pero no a nivel de Trimestre o Año :

image

Ya que nuestra consigna siempre es hacerle la vida más fácil para el usuario de negocios, vamos a usar un poco de MDX para hacer que los resultados siempre se muestren “correctamente”.

Traduciendo esto, debemos lograr: 1) que la membresía sólo se muestre si se selecciona el periodo de tiempo; 2) si el usuario pide la membresía en una frecuencia de tiempo mayor a mes (trimestre o año), se debe mostrar el resultado del último mes con respecto a dicho periodo de tiempo (membresía al cierre del periodo).

Para obtener la funcionalidad requerida, vamos ocultar la medida por defecto y en su reemplazo crear una medida calculada llamada [Enrollment Snapshot Count]:

image  image

La magia viene ahora, usando MDX para crear la medida calculada e inicializarla como vacía (NULL):

//*********** Member Snapshot Calculations ****************************//
CREATE MEMBER CURRENTCUBE.[Measures].[Enrollment Snapshot Count]
AS NULL,
FORMAT_STRING = "#,##0",
NON_EMPTY_BEHAVIOR = { [_Enrollment Count] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Enrollment Snapshot' ;



Luego en un segundo paso asignamos los valores requeridos (el conteo de miembros) usando la función SCOPE(mucho más eficiente que si lo hacemos usando la sentencia IIF):



//Assign last non-empty value for current member period (Year, Quarter or MonthPeriod)
SCOPE ([Month Period].[Calendar by Period].members ,[Measures].[Enrollment Snapshot Count]);
THIS = SUM(TAIL(DESCENDANTS([Month Period].[Calendar by Period],
[Month Period].[Calendar by Period].[Month Period])
,1)
,[Measures].[_Enrollment Count]);
END SCOPE;


Descomponiendo la asignación tenemos:




  1. Para todos los miembros existentes de mi jerarquía [Calendar by Period] en mi dimensión de tiempo [Month Period]


  2. …asignar valor a la medida calculada [Measures].[Enrollment Snapshot Count]


  3. …desde el último mes [Month Period] existente para el periodo de tiempo seleccionado (o el último mes si no ha sido seleccionado alguno)


  4. …igual a la suma de la cantidad de registros existente en la medida oculta (SUM(…, [measures].[_Enrollment Count])



La función DESCENDANTS nos trae los miembros del nivel deseado (en este caso al nivel [Month Period]), relativo a la posición actual dentro de la jerarquía de tiempo ([Calendar by Period]) . Pero como sólo queremos el último mes, usamos la funcion TAIL, la cual nos trae los últimos N miembros de la dimensión, en nuestro caso N es igual a 1.



Los resultados obtenidos con nuestra nueva medida calculada se muestran a continuación:



image



image



image



  image



Para concluir, hoy hemos visto cómo el uso de las técnicas de modelaje dimensional y el uso de MDX nos pueden ayudar a resolver preguntas en donde las agregaciones no sean una simple suma o alguna de las opciones que presenta por defecto Analysis Services en modo Multidimensional, buscando que la experiencia del usuario sea la más sencilla para ayudar a la usabilidad de las soluciones que creemos para ellos.



Alan