Una forma de analizar grándes volúmenes de información estática de forma óptima es mediante el motor PowerPivot, el famoso plugin gratuito para Microsoft Office que apareció con Office 2010.No vamos a entrar en detalles de lo espectacular de su motor multidimensional en memoria, sino en cómo podemos aprovecharlo para analizar grándes volúmenes de información. Y cuando digo grandes volúmenes de información me refiero a que por ejemplo, analizar 15 dias de capturas de performance monitor de windows puede suponer 127 Millones de filas…y vas a poder hacer análisis de toda esa cantidad de información en segundos.
[box type=”info”] NOTA: En SolidQ hemos ido bastantes pasos mas allá y disponemos de nuestra solución SQL2Cloud que unifica todo esto y muchísmo mas.[/box]
En este post vamos a ver lo facil que resulta analizar toda la información proveniente de nuestros ficheros .blg mediante PowerPivot. Usaremos además la herramienta “Relog” para convertir nuestros ficheros
Para ello imagina que tienes una captura .blg que lleva dias funcionando y queremos analizar. Puede que conozcas la herramienta PAL, la cual es bastante interesante y automatiza muchas tareas, pero seguramente si te dedicas como yo al “grano fino” necesitas ir mas alla. Para estos casos es para los que te vendrá bien conocer la importación de datos y análisis mediante PowerPivot.
Importación de datos a SQL Server
Vale, tengo mis datos en el fichero o ficheros .blg en cuestión y ahora llega el momento de analizarlos. Puedo generar un único fichero o procesar partes de ellos.
Generar un único fichero .blg a partir de “n” ficheros
El parámetro –f nos sirve para concatenar en un único fichero de salida nuestro fichero final a analizar. Obviamente podemos no hacerlo y podemos realizar múltiples cargas o una única especificando varios ficheros .blg, pero igual para ti esto puede resultarte útil.
relog “tusficheros.blg” –f bin -o tufichero_consolidado.blg
¿Qué contadores deseo analizar?
Puede que pienses que es un poco absurdo limitarme la carga a información de unos pocos contadores, pero piensa que aqui hablamos de mucha información e igual no necesitas para el estudio actual, analizar todos los contadores; igual solo necesitas revisar todos los contadores de tu replica de bbdd, pero no te interesan para nada los relativos a tu instancia de IIS.
Para saber qué contadores incorpora tu fichero .blg puedes usar el parámetro -q
relog –q “tu fichero.blg” > counterlist.txt
Ahora puedes editar ese fichero de salida (counterlist.txt en el ejemplo usado) y dejar solo los contadores a analizar. Esto es opcional, recuerda.
Crear tu DSN
Para poder incorporar a SQL Server la información necesitas primero necesitas crear un DSN de sistema.
Creamos nuestro DSN de sistema apuntando a nuestra instancia SQL Server y a una BBDD que designemos para incorporar la información.
[box type=”warning”] NOTA: Yo he usado tempdb en este ejemplo, tú no lo hagas o al reiniciar la instancia perderás los datos[/box]
Exportar datos a SQL Server
Para exportar los datos a SQL Server debes hacer uso del parámetro –f indicando el valor SQL de salida así como el DSN de sistema creado y un identificador para tu carga
El proceso de exportación creará las 3 tablas necesarias en caso de no existir o utilizará las tablas en caso de sí hacerlo.
relog perfmon_consolidado.blg -f SQL -o SQL:PerfmonDB!MiIdentificadorDeCarga
Una vez finalizado tendremos la información en nuestra BBDD importada en estas 3 tablas cuyo nombre es bastante descriptivo de lo que contienen
Importar datos a PowerPivot
Una vez llegado a este punto, ya tenemos los datos incorporados a nuestra BBDD y llega el momento de importarlos a PowerPivot para su posterior análisis
Activar Plugin PowerPivot
Si tienes Office 2013 debes ir directamente al gestor de COM-Addins y activarlo
Luego simplemente vas a plugins COM y activas el componente
NOTA: Si usas Office 2010 o quieres más información http://office.microsoft.com/en-us/excel/download-power-pivot-HA101959985.aspx
Creación del modelo y carga de datos a PowerPivot
Ahora solo tienes que cargar los datos y para ello abrimos PowerPivot
Seleccionamos nuestro origen de datos (en este caso están en SQL Server)
Rellenamos los datos relativos a su ubicación y finalmente seleccionamos las tablas con información deseada
[box type=”info”] NOTA: No tengas miedo del nº de filas a importar, PowerPivot puede con ello. En este caso hemos importado “solo” 2.7 Millones de filas de datos pero yo en mi dia a dia muevo 130 Millones sin problemas.[/box]
Ahora hay que hacer una pequeña modificación al modelo para relacionar los datos. Para ello, vete a la vista de diagrama de modelo
Crear la relación entre CounterDetails y CounterData
Esto es muy importante. Debes crear la relación por la columna CounterID para que despues puedas añadirte tus PivotTable y PivotChart para analizar la información coherentemente. Para ello basta con arrastrar desde “CounterData” hacia “CounterDetails” pinchando con el ratón sobre la columna “CounterID”.
Y ya estamos listos para jugar con los datos:
Conclusión
PowerPivot para Excel es una opción más que interesante para analizar rápidamente información de performance mónitor.
Si lo que necesitas es tener un historificado dinámico que no solo contenga información de performance monitor, sino del estado de salud de tu SQL Server incluyendo información de trazas de profiler, DMVs configuración de sistema, alertas proactivas…de cientos de servidores de tu infraestructura y además que todo eso esté consolidado y accesible desde tu web corporativa, ponte en contacto con nosotros y preguntanos sobre el proyecto SQL2Cloud
