27 diciembre 2009

Aplicando BI a mi infraestructura de IT: Monitoreando mi Servidor Web (IIS)

Hace algunas semanas mi buen amigo Isvet Laclaustra me invitó a preparar un tema que permita a la gente de infraestructura entender un poquito de lo que es Business Intelligence y de cómo se pueden aplicar sus conceptos a tareas que ellos realizan cada dia. Para ello entonces se me ocurrió el crear nuestra propia versión de Google Analytics para procesar los archivos de log que puede generar un servidor de internet como el Internet Information Services (IIS), para de este modo poder interpretar cómo se está desempeñando nuestro website: quiénes son mis visitantes, de dónde son, qué páginas miran, cuántos son, qué navegador de internet usan, qué sistema operativo usan, etc.

Lo importante del concepto es que además lo puedan aplicar no sólo a IIS, sino también a otras herramientas que generen archivos de log en archivos de texto con la misma lógica que mostraremos a continuación, permitiendo así un mayor y mejor monitoreo proactivo que ayude a tomar mejores decisiones con respecto a la demanda o uso de nuestros servicios.

Entrando entonces al caso que nos toca, lo primero que debemos entender es que existen varias formas a cómo IIS puede guardar los archivos de log de acuerdo a la versión del mismo. Pueden ver más detalles acerca de los tipos de log en MSDN aquí. Es importante recalcar que todos estos formatos dividen las columnas del archivo en base al caracter de espacio o “en blanco”. Particularmente he creado este post basado en la versión IIS Log File File Format de mi IIS versión 7. Uno de los archivos se ve así:

image

De acuerdo al mismo encabezado del archivo, los campos que contiene este formato son los siguientes: date time; s-ip; cs-method; cs-uri-stem; cs-uri-query; s-port; cs-username; c-ip; cs(User-Agent); sc-status; sc-substatus; sc-win32-status; time-taken.

Como se ve, el archivo presenta un encabezado que ocupa las primeras 4 líneas del archivo comenzando con el caracter “#”:

#Software: Microsoft Internet Information Services 7.5
#Version: 1.0
#Date: 2009-07-30 17:14:40
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query
s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus
sc-win32-status time-taken

Lo curioso del caso, es que por algún motivo, IIS incluye estas mismas 4 líneas además de al comienzo, en cualquier parte del cuerpo del archivo, lo cuál hace nuestro ejercicio más interesante. Una de las líneas de datos se ve así:

2009-07-30 17:14:40 ::1 GET /reports - 80 - ::1 
Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+6.1;+WOW64;+Trident/4.0;
+SLCC2;+.NET+CLR+2.0.50727;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729)
404 0 2 495

IIS crea uno o varios archivos de acuerdo a unas variables del tamaño de cada uno de los archivos. En mi caso tengo los archivos de varios días de datos en un folder en particular:


image


Para lograr nuestro objetivo he creado un nuevo proyecto de Integration Services con un nuevo paquete, el cual tendrá por objetivo cargar e interpretar cada uno de los archivos de log IIS para luego escribir sus datos en nuestra base de datos de SQL.


Una vez en nuestro paquete primero crearemos una variable “FileName” la cual almacenará el “path” del archivo log que estemos procesando, podemos inicializarla con el “path” de alguno de nuestros archivos:


image


Ahora agregamos un control “ForEach Loop Container”, el cuál será el responsable de recorrer nuestro folder con los archivos de log y seleccionar uno a la vez para ser procesado por nuestro Data Flow que será agregado posteriormente:


image


En la configuración del ForEach, debemos asegurarnos que en la propiedad “Enumerator“ esté seleccionado el valor “Foreach File Enumerator”. Como podrán ver, hay muchas otras opciones para los cuales nos puede servir este control:


image


Ahora nos toca configurar el “path” o ruta del folder donde el control deberá recorrer los archivos. Así mismo aquí tenemos la opción de indicar qué tipo de archivos debe leer el control, en nuestro caso serían los *.log. Finalmente, indicamos también de que queremos el “path” completo del archivo (“Fully qualified”), ya que este valor será usado posteriormente para leer cada uno de los archivos":


image


En la sección de Variable Mappings, mapearemos la salida de nuestro control ForEach hacia nuestra variable “FileName” creada en el primer paso:


image


Ahora ya tenemos lista la parte del flujo de control de nuestra solución para recorrer cada uno de los archivos del log de IIS. Ahora agregamos un componente “Data Flow” dentro de nuestro ForEach para trabajar con el contenido de cada log e insertarlo a nuestra tabla en SQL Server:


image


Como mencioné anteriormente, ya en nuestro Data Flow, lo primero que necesitamos es leer el contenido de nuestros archivos, por ello agregamos un componente “Flat File Source” de la sección de “Data Flow Sources”:


image


Ahora debemos realizar la configuración para que lea cada uno de los archivos de log. Para ello creamos una nueva conexión de tipo “Flat File” desde nuestro control, presionando el botón “New”:


image


En la ventana de configuración de la conexión, seleccionamos alguno de nuestro archivo de log a fin de que el control pueda “entender” el formato del mismo: 


image


Adicionalmente en la ventana anterior le estamos indicando al control que no lea las primeros 4 filas del archivo (“Header rows to skip”), con esto logramos evitar las primeras lineas de control que tienen todos los archivos de log, pero no hemos resuelto el problema si luego dentro del contenido encontramos otras líneas de control de manera inesperada.


En la seccion “Columns”, por defecto podremos ver una sola columna con el contenido de cada fila de nuestro archivo de log, lo cual dejaremos de esta misma manera. Es decir, no crearemos en este punto una columna para cada campo del archivo, esto lo haremos más adelante para tener más control del manejo del contenido del mismo:


image


Lo que sí debemos hacer es aumentar el tamaño por defecto de la columna a 1000 caracteres para que no se nos trunque nada del contenido del archivo:


image


Ahora está listo nuestro control de lectura de los archivos de log de IIS:


image


Aquí viene la magia. Para hacer que nuestro componente “Flat File”, lea cada uno de los archivos que nos traiga el “ForEach”, debemos configurar la propiedad “Expressions” de nuestra “Flat File Connection”:


image


Seleccionamos la propiedad “ConnectionString” y le damos click a los 3 botoncitos e incluimos ahi la variable que alimentará la ruta de cada archivo que procese el loop:


image 


image


Como comenté al inicio de este artículo, los logs de IIS presentan un formato algo irregular, ya que en cualquier parte del contenido del archivo podemos recibir alguna de las lineas de control del archivo, que incluyen la descripción de los campos del archivo y alguna otra “meta data”. Adicionalmente el formato del archivo incluye un caracter en blanco o espacio como separador de las columnas de datos, lo cual no es soportado por defecto con el control. Por estos motivos, no podemos dejar todo el control del archivo a nuestro componente “Flat File”, ya que si configuramos los campos en el mismo, en cuanto el archivo presente alguna de esas filas inesperadas, el componente arrojará un error de interpretación o “parsing”. Para resolver este pequeño problemita, usaremos un control “Script Component”,  el cual nos permite incluir código de .NET y por tanto nos abre las puertas para incluir cualquier código de validación que necesitemos como en este caso:


image 


Lo renombramos:


image


Configuramos como columna de entrada, la columna saliente de nuestro componente “Flat File”:


image


Y como salida (“output”), configuramos los campos que vamos a procesar o “parsear” de nuestro log de acuerdo a la descripción de los campos que se incluye dentro del mismo archivo:


image


Ya dentro de la sección de Script de nuestro control y específicamente dentro del procedimiento “Sub ProcessInputRows” vamos a crear el código que nos permitirá convertir la columna entrante en todas las columnas de salida que necesitamos. ProcessInputRows se ejecuta cada vez que exista una nueva fila en el control Data Flow.


Primero declaramos algunas variables para la lectura de cada una de las filas del archivo (strRow). También incluimos una variable que usaremos para separar cada una de las columnas del archivo usando un espacio en blanco (strColSepartor), recibiendo las columnas resultantes en un array de tipo cadena (rowValues()):

Dim strRow As String
Dim strColSeperator As String = " "
Dim rowValues As String()






Leemos la linea de datos:

strRow = Row.Line.ToString()



Ahora podemos validar de que sólo procesemos si la línea es una de nuestro interés. En nuestro caso, no nos interesan las filas que comienzan con el caracter de control “#”. Si ese fuese el caso, identificaremos a esa fila como inválida invalidando la fecha del evento (Event Date =  9999/12/31):

If strRow.StartsWith("#") = False Then

Else
Row.EventDate = "9999/12/31"
End If

Ahora que tenemos las filas que nos interesan, procedemos a aplicar la función Split de .NET para convertir la fila que recibimos como entrada en columnas:

rowValues = Row.Line.Split(CChar(strColSeperator))

Para obtener cada una de los campos de datos, accedemos a la posición específica de acuerdo a las especificaciones del log de IIS usando nuestro array. Por ejemplo para obtener el campo con la fecha en que ocurrió la solicitud al servidor de IIS haremos lo siguiente:

Row.EventDate = rowValues.GetValue(0).ToString()

De igual manera, para leer por ejemplo el “browser” o navegador utilizado por el usuario en la posición 10, usamos:

Row.UserAgent = rowValues.GetValue(9).ToString()



Con esto ya tienen la idea de leer cada uno de los campos del log de IIS. También podemos incluir algunas validaciónes específicas o incluir código para el manejo de errores:

Try
Row.WindowsStatus = rowValues.GetValue(12).ToString()
Catch ex As Exception
Row.WindowsStatus = 0
End Try





Por ejemplo, algo más específico sería tratar de inferir el navegador  utilizado por el usuario usando el campo que contiene el “User Agent”:

'Browser
If rowValues.GetValue(9).ToString.Contains("Chrome") = True Then
Row.Browser = "Chrome"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+8") = True Then
Row.Browser = "Internet Explorer 8"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+7") = True Then
Row.Browser = "Internet Explorer 7"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+6") = True Then
Row.Browser = "Internet Explorer 6"
ElseIf rowValues.GetValue(9).ToString.Contains("Firefox") = True Then
Row.Browser = "Firefox"
ElseIf rowValues.GetValue(9).ToString.Contains("Safari") = True Then
Row.Browser = "Safari"
ElseIf rowValues.GetValue(9).ToString.Contains("BlackBerry") = True Then
Row.Browser = "BlackBerry"
Else
Row.Browser = "Other"
End If

De manera similar para inferir el sistema operativo utilizado por el usuario desde el mismo campo del “User Agent”:

'Windows
If rowValues.GetValue(9).ToString.Contains("Windows+6.1") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+NT+6.1") Then
Row.OS = "Windows 7"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+6.0") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+6.0") Then
Row.OS = "Windows Vista"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+2000") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.0") Then
Row.OS = "Windows 2000"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+XP") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.1") Then
Row.OS = "Windows XP"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+5.2") = True Then
Row.OS = "Windows Server 2003"
ElseIf rowValues.GetValue(9).ToString.Contains("Mac") = True Then
Row.OS = "Mac"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows") = True Then
Row.OS = "Other Windows"
Else
Row.OS = "Other OS"
End If

Después de terminar nuestro código .NET, nuestro Data Flow se vería así:


image


El código completo incluido en el “Script Component” lo pueden ver a continuación:









  1. Dim strRow As String

  2. Dim strColSeperator As String = " "

  3. Dim rowValues As String()

  4. strRow = Row.Line.ToString()

  5. 'If strRow.Contains(",") Then

  6. '    strColSeperator = (",")

  7. 'ElseIf strRow.Contains(";") Then

  8. '    strColSeperator = ";"

  9. 'End If

  10. If strRow.StartsWith("#") = False Then


  11.     'MessageBox.Show(Row.Line.ToString)


  12.     rowValues = Row.Line.Split(CChar(strColSeperator))

  13.     Row.EventDate = rowValues.GetValue(0).ToString()

  14.     Row.EventTime = rowValues.GetValue(1).ToString()

  15.     Row.ServerIP = rowValues.GetValue(2).ToString()

  16.     Row.CSMethod = rowValues.GetValue(3).ToString()

  17.     Row.URIStem = rowValues.GetValue(4).ToString()

  18.     Row.URIQuery = rowValues.GetValue(5).ToString()

  19.     Row.Port = rowValues.GetValue(6).ToString()

  20.     Row.UserName = rowValues.GetValue(7).ToString()

  21.     Row.ClientIP = rowValues.GetValue(8).ToString()

  22.     Row.UserAgent = rowValues.GetValue(9).ToString()

  23.     Row.ServiceStatus = rowValues.GetValue(10).ToString()

  24.     Row.ServiceSubStatus = rowValues.GetValue(11).ToString()

  25.     Try

  26.         Row.WindowsStatus = rowValues.GetValue(12).ToString()

  27.     Catch ex As Exception

  28.         Row.WindowsStatus = 0

  29.     End Try

  30.     Row.TimeTaken = rowValues.GetValue(13).ToString()




  31.     'Browser

  32.     If rowValues.GetValue(9).ToString.Contains("Chrome") = True Then

  33.         Row.Browser = "Chrome"

  34.     ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+8") = True Then

  35.         Row.Browser = "Internet Explorer 8"

  36.     ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+7") = True Then

  37.         Row.Browser = "Internet Explorer 7"

  38.     ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+6") = True Then

  39.         Row.Browser = "Internet Explorer 6"

  40.     ElseIf rowValues.GetValue(9).ToString.Contains("Firefox") = True Then

  41.         Row.Browser = "Firefox"

  42.     ElseIf rowValues.GetValue(9).ToString.Contains("Safari") = True Then

  43.         Row.Browser = "Safari"

  44.     ElseIf rowValues.GetValue(9).ToString.Contains("BlackBerry") = True Then

  45.         Row.Browser = "BlackBerry"

  46.     Else

  47.         Row.Browser = "Other"

  48.     End If


  49.     'Windows

  50.     If rowValues.GetValue(9).ToString.Contains("Windows+6.1") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+6.1") Then

  51.         Row.OS = "Windows 7"

  52.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+6.0") = True Or rowValues.GetValue(9).ToString.Contains("Windows+6.0") Then

  53.         Row.OS = "Windows Vista"

  54.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+2000") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.0") Then

  55.         Row.OS = "Windows 2000"

  56.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+XP") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.1") Then

  57.         Row.OS = "Windows XP"

  58.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+5.2") = True Then

  59.         Row.OS = "Windows Server 2003"

  60.     ElseIf rowValues.GetValue(9).ToString.Contains("Mac") = True Then

  61.         Row.OS = "Mac"

  62.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows") = True Then

  63.         Row.OS = "Other Windows"

  64.     Else

  65.         Row.OS = "Other OS"

  66.     End If


  67. Else

  68.     Row.EventDate = "9999/12/31"

  69. End If







Ya pasamos la parte más “complicada” de nuestro paquete. Ahora agregamos un componente “Conditional Split” para poder discernir entre las filas correctas o incorrectas de nuestro archivo:


image


La pregunta que debemos hacer de acuerdo a la lógica de nuestro Script Component sería de que si la fecha del evento es igual a “9999/12/31” de la siguiente manera:

EventDate == (DT_DBTIMESTAMP)"9999/12/31"



image


Por último agregamos un componente “OLE DB Destination” para que escriba en nuestra base de datos de SQL las filas válidas:


image


Seleccionamos o creamos la conexión a nuestra base de datos (en mi caso IISLogDataMart):


image


Y para la tabla, ya que no existe aún, la crearemos usando el botón “New”, el cuál presentará el script de acuerdo a la metadata de las columnas existentes en nuestro paquete:


image


Presionamos “OK” y la tabla se crea:


image


Ahora sólo nos queda mapear los campos de nuestro paquete a los de nuestra tabla. En este caso el “mapping” es automático, ya que los nombres de las columnas de la tabla destino son los mismos a los del paquete:


image


Finalmente, así se ve nuestro “Data Flow” terminado”


image


Luego de ejecutar nuestro paquete y de “subir” todos los archivos de log en nuestro folder a nuestra tabla, ya estamos listos para darle “vida” a nuestros datos y develar lo que está pasando en términos de visitas con nuestro website. Para ello, podemos usar la herramienta Report Builder 2.0, una herramienta dirigida al usuario final (no al desarrollador) que es parte de SQL Server 2008, pero que se debe descargar por separado. Pueden referirse al post que publiqué anteriormente para saber más sobre el producto:


image


Ya en Report Builder 2.0 creamos nuestra conexión a la fuente de datos en SQL Server:


image


Ingresamos los datos de la conexión:


image


Y ahora de manera fácil, usando nuestras capacidades de escribir consultas en SQL, vamos a crear una primera gráfica para mostrar la candidad de “hits” recibidos a lo largo del tiempo:

SELECT  EventDate, COUNT(*)
FROM IISLogInput
GROUP BY EventDate
ORDER BY EventDate
image

Con estos datos vamos a crear una gráfica sencilla incluendo el contador en la zona de “Data Fields” y la fecha del evento en la zona de las categorías:

image

De igual manera, ahora como dicen por ahí, el cielo es el límite. Podemos crear un sin número de gráficas o tablas que nos brinden información interesante acerca de las visitas recibidas en nuestro servidor de IIS:

image

Y por tanto de forma fácil, y no necesariamente usando Analysis Services, puedo crear un dashboard de lo más interesante y automatizado que muestre información mucho más interesante que el mirar los archivos de log aburridos de IIS. Nuestro dashboard quedó así:

image 

Espero que haya servidor el tour de cómo aplicar Inteligencia de Negocios a sus procesos operacionales de una manera relativamente fácil. Lo que espero que hayan podido aprender en esta guia es:



  • Utilizar el componente ForEach Loop para leer objetos del sistema de archivos
  • Procesar archivos de texto

    • Procesar archivos de texto con formato irregular

  • Utilizar el “Script Component” para crear lógicas personalizadas que no hagan otros controles o de manera más fácil
  • Cargar los datos de un archivo de texto hacia una tabla de SQL Server
  • Crear dashboards atractivos usando Report Builder 2.0

Un ejemplo de archivo de log, el paquete de SSIS utilizado y el reporte generado en este demo lo pueden bajar de mi Skydrive:



Hasta la próxima,


Alan


****UPDATE 1/22/2010
Recibí una pregunta mencionando problemas para poder habilitar el ingreso de los datos del File Enumerator en el For Each component.
Hay un problema de la interface, pues cuando uno agrega un nuevo For Each y edita sus propiedades por primera vez, notarán que no aparecen los controles para poder configurar el File Enumerator:


image


La solución consiste en cambiar el valor del combobox del Enumerator a cualquier otro y luego volverlo al “Foreach File Enumerator” para que por arte de magia aparescan los controles perdidos:


image


Espero que esto les permita continuar.





Alan.

15 diciembre 2009

PRPASS December Event!

clip_image001


WHEN:

Wednesday, December 16, 2009 (6:00 PM)

WHERE:

Microsoft Puerto Rico - Borikén Room - Microsoft Building, Metro Office Park Street 1 Lot 18 Suite 5000 Guaynabo PR

(To see a reference map click here)

TOPICS:

SQL Server 2008 Management Data Warehouse (MDW)  – Elizabeth Córdova (Nagnoi, Inc.)
Level: 200 (Intermediate)
Audience: IT Professionals

As a way to improve our visibility of what is happening with our server and databases in order to get the best response times and high availability, a great improvement of SQL Server 2008 is the ability to collect performance statistics in an automated way. The management data warehouse (MDW) is a relational database that contains the data that is collected from a server that is a data collection target. This data is used to generate the reports for the System Data collection sets, and can also be used to create custom reports.

Speaker Profile:    Elizabeth Córdova is a Business Intelligence Consultant at Nagnoi, Inc. Elizabeth has 9 year of industry experience and more than 4 years working on Data Warehouse projects and Business Intelligence, including deep experience on Extraction, Transformation and Loading (ETL) components using SSIS, OLAP, Data Mining and Enterprise Reporting.

SQL Server Performance Best Practices on Installation & Configuration  – Jorge Sanchez (Rock Solid Technologies)

Level: 300-400 (Advance)
Audience: Data Base Administrators

This seminar will focus on the checklist every DBA should have around performance best practices for server (hardware & software) preparation, SQL Server installation, and final configuration.

Speaker Profile: Jorge Sánchez brings 22 years of experience in the IT industry, currently working for Rock Solid Technologies as Senior BI Developer. Previously, Mr. Sánchez worked 8 years in Microsoft as SQL Server Regional Specialist for Caribbean and Central America with direct focus in Puerto Rico, Costa Rica, Dominican Republic, and Trinidad. Before that, 14 years as Senior Consultant, Developer, and SQL Server Certified Instructor for Microsoft Technologies within the Microsoft Partners Ecosystem. Mr. Sánchez is currently certified in SQL Server versions: 2008, 2005, and 2000, 7.0, and 6.5.

The Architecture Journal: Llevando a BI más allá de los analistas de negocios

Me acaba de llegar la versión digital del último número del Architecture Journal, publicación de Microsoft pensada para formar  mejores arquitectos de soluciones. Si no la han revisado antes, les recomiendo suscribirse ahora mismo(http://www.architecturejournal.net).

La edición de hoy me da particular emoción porque el tema de la revista es Business Intelligence. Aún no la he leído, pero los contenidos se ven bastante prometedores.

 

The Architecture Journal

Taking Business Intelligence beyond the Business Analyst

Pueden acceder al contenido on line desde aquí: http://msdn.microsoft.com/en-us/architecture/bb380180.aspx, también se incluye videos acerca de los temas en discusión. Adicionalmente pueden descargar la versión PDF del documento para leerla off-line desde aquí.

Alan

23 noviembre 2009

Evento de la comunidad PR .NET

Nuestros amigos de la comunidad de Puerto Rico .NET (PR .NET) nos invitan a participar de su próximo evento presencial. Aquí los datos del mismo:

Lugar: New Horizons of Puerto Rico
City View Plaza Suite 500 (Frente al Nuevo Día)
Guaynabo (787) 999-1000

Agenda:

- Microsoft Solution Framework 4.0 and Foundational Principles (Javier Miranda)
- All about MCSD Certification (New Horizons)
- Windows Mobile SDK Inside - Out (Eduardo Sobrino)

Alan

19 noviembre 2009

SQL Server 2008 R2 November CTP Download

Hoy Microsoft acaba de liberar un nuevo Community Technology Preview (November CTP) de SQL Server 2008 R2. Y lo mejor que está disponible para que cualquiera la pueda descargar. Lo pueden bajar desde aquí.

A continuación el email oficial del lanzamiento con los detalles de las novedades. Ya estaremos discutiendo muchas de ellas próximamente.

“The SQL Server Team is excited to announce the release of the SQL Server 2008 R2 November Community Technology Preview (CTP), available for download and installation on test systems today! This is the latest version of SQL Server, with an anticipated ship date in the first half of 2010.

Designed to help you reduce data management costs and increase efficiencies, SQL Server 2008 R2 provides an even more scalable data platform with comprehensive tools to help with managing databases and applications, improving the quality of your data, and empowering your users to build rich analyses and reports using familiar tools.

Download the November CTP and PowerPivot today!

The November CTP is “feature complete” so we encourage you to try out the new features in this CTP release which include:

- Support for Windows Server® 2008 R2, including Hyper-V with Live Migration
- Enhanced data compression with support for Unicode UCS-2
- PowerPivot for Microsoft® Excel 2010
- Report Builder 3.0 with Report Part Gallery and new visualizations (sparklines, databars)
- Master Data Services (MDS) with Master Data Hub and Stewardship Portal

Enhancements to the capabilities delivered in the August CTP:

- A Control Point Explorer in SQL Server Management Studio for central multi-instance and data-tier application management
- Dashboard viewpoints for quick insight into application and instance utilization
- StreamInsight for complex event processing
- SQL Server System Preparation (sysprep)
- Support for up to 256 logical processors

Introducing PowerPivot for Excel

PowerPivot for Excel (formerly known as Project “Gemini”), is a data analysis tool that delivers unmatched computational power directly within Microsoft Excel. Utilizing familiar Excel functions, you can manage and transform enormous quantities of data from virtually any source with incredible speed – enabling you to create, analyze and share meaningful business information and get the answers you need in seconds.

IT managers can use the new PowerPivot for SharePoint features (delivered through SQL Server 2008 R2 in concert with SharePoint 2010), to enable users to easily publish and securely share their Microsoft Excel BI workbooks – via a rich browser-based experience that offers performance and features just as you’d find in the native Excel environment. PowerPivot for SharePoint also gives IT departments a way to monitor operational efficiency of their reporting server through the PowerPivot Management Dashboard.”

Alan

Más pensamientos sobre Planning

Hace unos días un amigo me preguntó mi opinión acerca de las posibilidades de mercado para una nueva herramienta para soportar los procesos de planificación o “Planning” después de que Microsoft anunciara en Marzo pasado la discontinuidad de PerformancePoint Planning. Comparto con ustedes extractos del email de respuesta que le envié.

Bueno, el tema de Planning es de lo más interesante. Desde mi punto de vista, hablando de necesidad del mercado te diría que SI la hay, y que la habrá más. Creo que es un proceso de madurez, muchas compañías ya están entrando al mundo de BI, y no sé si sabrás, pero BI es la prioridad # 1 de los CIOs de las top empresas del mundo (creo que fueron 2700 CIOs de las principales empresas del mundo). Claro, aunque la mayoría llama BI a la parte de Analytics & Reporting, definitivamente el siguiente paso para muchos será el de Planning, el tratar de inducir o proyectar lo que mi empresa quiere logras. Así que como te mencioné al inicio, la demanda está en crecimiento.

“Hablando de la estrategia de Microsoft, creo que ellos dejaron Planning porque se dieron cuenta de que la gente “común y silvestre” de IT, que son las personas que principalmente trabajan con su tecnología, no podrían tener mucho éxito implementando proyectos de este tipo. Planning es un animal de distinto tipo comparado a cualquiera de los servidores o herramientas que tiene Microsoft – a excepción de Office tal vez, aquí hay que entender mucho más que la herramienta técnica sino un poco de negocios, finanzas o de procesos de planificación. Así que creo que Microsoft optó por lo más fácil y deshizo Planning para que otros lo hagan con el “expertise” y cuidado que requiere este tipo de implementaciones.

Cuando Microsoft deshizo Planning, semanas más tarde aparecieron algunos comentarios acerca de cómo iban a redistribuir las principales funcionalidades de  PerformancePoint Planning en distintas herramientas ya establecidas en el mercado. Por ejemplo, se hablaba ya lo del support del Write-back en Excel y Analysis Services, Business Rules en Excel y Sharepoint, y otras capacidades con más “avanzandas” en Visual Studio. Específicamente con Excel 2010 y sus capacidades de What-If (Write-back), fácilmente creo que reemplaza y supera a las matrices que el Add-In de PeformancePoint para Excel creaba para los formularios de entrada de datos (experiencia muy dolorosa). Al menos en el CTP se ve muy bien y se debe poner mejor más adelante. Así que desde este punto de vista creo que puedes basarte en Excel, SSAS y Sharepoint para crear tu versión de Planning. Microsoft va a dejar que los Partners se especialicen en este tipo de soluciones creando como la plataforma o todos los elementos básicos. Y para acelerar este propósito, incluso es que liberaron el código fuente de PPS Planning llamando Financial Planning Accelerator. Esta estrategia es similar a lo que han hecho con los Workflows y Sharepoint, compañías como Global 360, K2, Kaldera; tienen soluciones bien específicas que trabajan utilizando el poder de Sharepoint para crear proyectos de Automatización de Procesos o de BPM por sus iniciales en inglés. Lo mismo debe pasar con Planning.

Probablemente has escuchado de ellos, pero hay un par de partners fuertes de Microsoft que también tienen su solución de Planning desde hace algunos años: Clarity y Prophix. Microsoft prácticamente le refirió todos sus actuales clientes de PPS Planning a Clarity. Por tanto creo que hablando más de estrategia y de las posibilidades de negocio para una nueva herramienta de Planning, debes analizar profundamente a estos competidores y buscar cómo mejorar lo que ellos ya ofrecen.

Con respecto al acceso a Sharepoint 2010, no sé si ya te registrarte para acceder a los betas en el site de Connect (http://connect.microsoft.com), aunque creo que hay una lista de espera, pero igual te debes de registrar ahí para que puedan experimentar con las nuevas características.”

Quiero agregar que por si no lo han visto aún, oficialmente el día de ayer se liberó el primer beta público de Office 2010, donde cualquier persona lo puede descargar y comenzar a jugar con la herramienta. Recuerden que esto es un beta aún y que propablemente necesitarán desinstalarlo si quieren instalar una nueva versión o la versión final. Lo pueden bajar desde este link: http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx.

Por otro lado, para los que no vieron a lo que me refiero líneas arriba con respecto al soporte de write-back en Excel 2010 sobre los cubos de Analysis Services. Lo pueden ver aquí: Excel 2010 Technical Preview – Parte 1- “What-If Analysis in Pivot Tables”.

Alan

14 noviembre 2009

Actualización del DataMart y de los Cubos

Para beneficio de otras personas publico la pregunta que mi amigo Maurico Gómez me envió hace unos días:

“Cómo administra usted un Data Mart...???, por ejemplo:

En Project REAL estan las ETL(para ejecutar las ETL_Dimensiones, ETL_Facts,y ejecutar el generar cubo con la herramienta ascmd.exe a partir de otra ETL, ahi baje unos ejemplos pero son bastante complicados para mi de configurar con respecto a la herramienta ascmd.exe y el como generar los archivos xmla). “

Aquí mi respuesta:

“Normalmente el procesamiento de las dimensiones / cubos, no los incluyo como parte de los paquetes de SSIS (a diferencia de como lo hacen en Project REAL).
Estos paquetes son invocados por un Job configurado en el SQL Agent. En el mismo job incluyo un paso adicional para procesar las dimensiones y cubos usando XMLA. Por tanto cada vez que se ejecuta el job, se corren los paquetes y se procesa el cubo.

En mi caso, no es que no recomiende usar los controles de SSIS para procesar las dimensiones / cubos, pero en mi caso he encontrado que es más práctico, utilizando el XMLA de manera externa, en este caso a través de un Job del SQL Agent.”

Alan

10 noviembre 2009

Presentación: “Data Integration and Reporting for IT-Pros”

Continuando con la evangelización de SQL Server a los estudiantes y por invitación del IT Community Staff, este jueves 12 de noviembre, estaré presentando el tema de Data Integration and Reporting for IT-Pros en el Huertas College de Caguas aquí en Puerto Rico. Mi presentación está pautada para las 7pm con duración de 1 hora.

El demo principal del evento, mostrará cómo los “IT-Pros” o personal de sistemas, puede integrar a su día día capacidades básicas de SQL Server Integration Services para diversos casos como el procesar los logs de visitas de nuestros websites en Internet Information Services (IIS) para luego crear un dashboard usando Report Builder 2.0 que les ayude a monitorear los mismos.

Más detalles del evento, aquí.

02 noviembre 2009

Puerto Rico PASS (PRPASS) November Meeting

WHEN:

Thursday, November 5, 2009 (6:00 p.m.)

WHERE:

Microsoft Puerto Rico - Borikén Room - Microsoft Building, Metro Office Park Street 1 Lot 18 Suite 5000 Guaynabo PR

(To see a reference map click here)

TOPICS:

SharePoint (The Business Tool for Business People) Introduction – By Alberto Lugo
Level: 100 (Introductory)
Audience: IT Professionals

Microsoft Office SharePoint Server 2007 is an integrated suite of server capabilities that can help improve organizational effectiveness by providing comprehensive content management and enterprise search, accelerating shared business processes, and facilitating information-sharing across boundaries for better business insight. Additionally, this collaboration and content management server provides IT professionals and developers with the platform and tools they need for server administration, application extensibility, and interoperability. Join us to see how SharePoint streamline your business processes.

Speaker Profile: Alberto Lugo is the President of Internet Vision Development (INVID) Corp. (www.invidpr.com), an organization dedicated to provide IT consulting, software services and SharePoint services. Alberto had worked with SharePoint for the past 10 years and has extensive knowledge on the different versions of the product.  

SQL CLR Basics – By Carlos Bercero

Level: 200 (Intermediate)
Audience: Developers

Many people know SQL 2005/2008 allows you to create Stored Procedures using .NET languages, but not many people know how to actually do it. This session will provide real live examples and all the information you need to know to get started creating your own CLR Stored Procedures.

Speaker Profile: Carlos Bercero is a BI Consultant for one of the leading BI firm in Puerto Rico, Nagnoi, Inc. (www.nagnoi.com). His professional IT career started in 1995 playing many different roles in the IT industry. He is now specialized in  Business Intelligence Solutions and Custom Application Development.

We need your donation!

Could you donate $5?

clip_image001

Thanks for supporting PRPASS!

Register Here

You can join find us in Facebook

clip_image003

13 octubre 2009

Manejando hechos que (aún) no tenemos su dimensión (early arriving facts) en SSIS

Probablemente esto les ha pasado antes o les puede pasar cuando creen sus paquetes de Integration Services para cargar sus tabla de facts: tienen que procesar unos hechos (facts) que han llegado antes de que la dimensión (o mejor dicho el miembro) que generó el hecho exista en su tabla dimensión. Por ejemplo, tienen una factura para un cliente que por algún motivo no ha sido ingresado (aún) a su dimensión de Productos.

Básicamente hay 2 opciones para manejar estos “early arriving facts”. La primera es (tal como lo hace Project REAL), implementar un Script Component que inserte el valor que no se encuentre en la dimensión (que no haga “match”) y que lo integre al “cache” del Lookup Component original usando código de .NET. La siguiente extraída de la documentación del proyecto (Project REAL ETL Design Practices), muestra el diseño de esta estrategia:

clip_image002

 

La segunda forma (que llamó mi atención por ser más simple y efectiva), es la de usar un segundo Lookup Component para los valores que no se encuentren en el primero, llamando a un Stored Procedure para que haga el trabajo y lo inserte al cache de este segundo Lookup (configurando para que se ejecute con Partial Cache). Todo este detalle lo encuentran en este excelente post del Customer Advisory Team (CAT) de SQL (en inglés). Aquí algunas imágenes de ese artículo:

clip_image002_2 

clip_image004_thumb

 clip_image006_2

CREATE PROCEDURE Generate_SK_A
  @NK_A CHAR(10) /* The key to find a surrogate for */
AS
SET NOCOUNT ON

/* Prevent race conditions */
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

/* Check if we already have the key (procedure is idempotent) */
DECLARE @SK_A INT
SELECT @SK_A = SK_A
FROM Dim_A
WHERE NK_A = @NK_A

/* The natural key was not found, generate a new one */
IF @SK_A IS NULL BEGIN
INSERT Dim_A (NK_A) VALUES (@NK_A)
SET @SK_A = SCOPE_IDENTITY()
END

/* Return the result. 
  IMPORTANT: must return same format is the SELECT statement we replaced */
SELECT @SK_A AS SK_A, @NK_A AS NK_A

Ambas opciones son muy buenas para solucionar nuestro problema con los “early arriving facts”, cuál escogen?

Alan

11 octubre 2009

Excel 2010 Technical Preview - Parte 7: Mejoras al Conditional Formatting

Después de unos días muy intensivos por los proyectos que estoy trabajando, ya estoy de vuelta para seguir con la parte 7 de nuestra serie acerca de las novedades en Excel 2010 para Business Intelligence. Hoy nos toca presentar un resumen de las mejoras a la capacidad de aplicar formato condicional (Conditional Formatting) a nuestros datos ya sea dentro o fuera de un Pivot Table.

Si no han tenido la oportunidad de usar esta capacidad de Excel 2007, les puedo decir que el Conditional Formatting es una gran ayuda para visualizar nuestros datos de una manera gráfica, que nos ayuda a entender de más rápida y directa lo que tenemos en nuestros números. A continuación algunas imágenes de lo que se puede hacer desde ya con la versión 2007:

image

image image

Ya que todos tenemos ya una idea básica de cómo podemos usar estos Conditional Formatting, voy ahora a listar y comentar las mejoras o novedades que hasta ahora aparecen en el Technical Preview liberado en Julio de la versión de Excel 2010. Cabe resaltar que no voy a ahondar mucho en cada punto ya que revisando la web encontre estos excelentes post que equipo de producto de Excel acaba de publicar (en inglés): More Conditional Formating Features in Excel 2010 y Data Bar Improvements en Excel 2010.

Y las novedades son:

1. Soporte para referenciar celdas en hojas (sheets) distintas a la actual (Cross-Sheet References): Hasta ahora, en Excel 2007, nos debemos de limitar a que cuando queremos crear nuestras reglas para la asignación de íconos dentro de un formato condicional basándose en los datos de otras celdas, esos valores deben de existir en la misma hoja de Excel. Pues ahora esta limitación ha sido removida y podemos hacer referencias a otras hojas (worksheets) dentro de nuestro archivo (workbook):

image image

image image

2. Mejoras en el manejo de errores: Este punto se refiere a que en Excel 2007, algunos errores de cálculo en nuestras celdas, pueden hacer que nuestro Conditional Formatting deje de funcionar (por ejemplo un #N/A o un #DIV/0). En esta nueva versión,, un error de este tipo sera totalmente ignorado por el Conditional Formatting sin afectar a las demás celdas con cálculos correctos:

Excel 2007 vs Excel 2010:

image  image

3. Mejoras en el desempeño: Esta es una parte muy importante, principalmente si tenemos aplicado el Conditional Formatting a una o varias hojas con muchos datos. La primera mejora, se basa en que para el caso del formato aplicado sobre celdas producto de fórmulas o de otras celdas, el formato condicional sólo se re-evaluará, si alguna de las dependencias de la fórmula cambia. La segunda mejora con respecto en el desempeño consiste en que el motor guarda un cache del formato condicional aplicado, para que no tenga que ser re-calculado o re-aplicado en el caso de que el usuario simplemente esté navegando por la hoja (scrolling) sin cambiar ningún valor.

4. Mejoras en los Data Bar – Tamaños proporcionales: En Excel 2007, cuando aplicamos un Conditional Formatting de tipo Data Bar, si bien obtenemos el resultado general esperado dependiendo de los números que estemos analizando (pintando un relleno con mayor intensidad para el valor más alto y con un relleno menos intenso para el valor más bajo. O al revés si los números que analizamos tienen esta naturaleza, como los costos por ejemplo), la forma de relleno de las celdas no se pinta proporcionalmente al número que se está representando en comparación con los demás Valores, lo cual puede llevar a un entendimiento incorrecto de nuestros datos. En Excel 2010 ha sido superado, de manera que ahora los rellenos de las celdas participantes, son pintados proporcionalmente a los números que existan en nuestro dataset.

Data Bars en Excel 2007 vs Excel 2010:

image image

Más ejemplos en Excel 2010:

image image image

5. Mejoras en los Data Bar – Opciones de formato (rellenos y bordes): Al usar los Data Bars, muchas veces perdemos visibilidad de nuestro datos en las celdas, aunque el efecto de “gradient” o degradado de los colores tratan de minimizar esa pérdida de visibilidad. Por otro lado muchas veces cuando tenemos Data Bars en valores muy similares, no es muy fácil por el mismo efecto de degradado, no es muy fácil darnos cuenta de cuál de las celdas tiene un mayor o menor valor. Para mejorar la visibilidad ahora se agregan nuevas opciones para presentar los Data Bar.

La primera consiste en hacer que el relleno de los mismos no sea de tipo degradado sino de tipo completo.

image

Ventana de diálogo “More Rules…” en Excel 2007 vs Excel 2010

image  image 

Data Bars con degradado (gradient) vs relleno sólido (solid fill)

image  image

Como pueden ver en las dos imágenes anteriores (degradado vs relleno sólido), en el primer caso, si nos fijamos sólamente en el relleno del Data Bar y no en los números, es difícil notar si el año 1996 o el 1997 es el que más nacimientos tuvo. En cambio si el relleno lo configuramos de tipo sólido la diferencia es mucho más clara.

La segunda de las mejoras en los Data Bars, nos permite mantener el relleno de tipo degradado o sólido pero incluyendo un borde para resaltar sus límites::

image

6. Mejoras en los Data Bar – Valores negativos: Esta mejora es algo que estaba siendo pedido a gritos en vario de los forums de Excel, y se refiere a mostrar los Data Bars de manera “correcta” para los valores negativos. Una opción es mostrando el Data Bar del valor negativo en dirección contraria a los valores negativos, y la otra es mostrar los negativos en la misma dirección de los positivos pero con otro color.

Negativos en Excel 2007 vs Excel 2010:

image image

image

Bueno, con estas mejoras en los Data Bars, dejamos momentáneamente las serie de Excel 2010 y sus mejoras para este mundito de Business Intelligence. Nos queda pendiente presentar cómo Excel 2010 manejará las Medidas Calculadas (Calculated Measures), ya que hasta el momento de escribir este post, aún no está habilitada la opción que posibilitará esta posibilidad.

Hasta la próxima.

Alan