Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
PowerPivot for Excel is a great analysis tool for information workers. You can import data from any supported data source in your environment or from Azure DataMarket and then analyze that data with supercool Data Analysis Expressions (DAX), but who says PowerPivot is just for information workers? PowerPivot is for everybody! So, why not analyze computer information if you happen to be an IT pro?
If you are an IT pro, you might find it interesting to analyze the Windows Event Log. It’s a great source of valuable information. You might be able to find certain patterns of system errors or security incidents on your servers and workstations. So, let’s load event log data into PowerPivot. One way to accomplish this is to export the data into a CSV file and then import that CSV file into PowerPivot.
Here are the steps:
And after removing some invalid characters from the Level heading and renaming the F6 column to Event Details (just right-click the headings and select Rename Column), the data is ready for analysis:
Event Details (automatically changes to Count of Event Details)
Axis Fields (Categories):
Date and Time
Level, Event ID
Of course, this is a very basic solution, yet it might nevertheless be useful. Let me know if you find these IT-pro scenarios interesting. In my next blog post, I’m planning to show you how to analyze performance data in PowerPivot. Stay tuned!
Fantastic example. I am planning to use Perfmon data for analysis. :)
There is a problem in importing perfmon (CSV) file. The problem is in the header of the file. it has lot of "\" in the header name.
are you importing event log data or is this data gathered via performance monitor? Assuming it's the latter, don't use the csv file option for that. You'll run into a number of issues. I'll discuss this in my next post. Plan is to post it in a couple of weeks... still need to write it. :-)
Hi Kay, I believe i speak in the name of many when I say that we are using every possible solution to data-mine those extremelly long and ugly CSV log files.
It helped me exactly for the same purpose!
Thank you again,
is there a way to automate this? i'm doing it with log parser but looking for another way. importing the data into SQL and then working on it in SQL is pretty slow. looking at analysis services