Over the course of my career I have had to query, extract, and analyze data sets from all sorts of databases. Microsoft Excel is probably the number one tool for this job, but there are limitations to excel in consuming and analyzing normalized databases. That is were someone like me comes in to build a query to extract and de-normalize the data set to make it easy for end users to run their analysis. The type of analysis I’m talking about here is the quick ad-hoc analysis, someone asked a question and we are going to answer it once; Excel has some pretty slick tooling for analyzing normalized databases but it generally isn’t worth setting that up to just answer a question.
During my career I have been both the query writer and extractor, and the analyzer (working with a dataset in Excel to answer a question.) My favorite tool for the first job is LinqPad. With LinqPad, you are querying in C# and not SQL. My favorite use case with LinqPad over any other SQL IDE is advanced joining and aggregations over multiple queries. Producing a single dataset to work with can often be completed quicker (IMO) by creating multiple small queries and combining the results rather than trying to write one large SQL query with several joins. I also recently discovered that LinqPad can display WCF and WinForms in its output window. I figured way not build and display a .NET chart object? It works! So why stop with just displaying a single chart that I build once, way not be able to display a chart of any IEnumberable?
I created a extension method for LinqPad called “ToChart” that converts any .NET IEnumerable (i.e. the results of a query in LinqPad) into a .NET chart object. Using reflection this proved to be pretty straight forward. The object you want to display as data points in the chart have some naming conventions for their properties. For example, you must have a property called “Key” to be the X Axis point. Any numeric properties in the object are then made into the Y axis points, or Series. You can also customize the properties of each series by appending the property name to a new property in you object, i.e. color could be controlled like so: Value_Color = Color.Red. In the below screen shot you can see where I’m setting the Color and Tooltip of each point in the “Hours” series.
Why did I do this? Number one, because it was fun and because I could. Secondly it allowed me to stay out of excel and keep me closer to the data when trying to answer a question. Finally, it helps me answer the question faster. Excel is a great tool but if I can remove one step from the question answering process then that is great. Like I said earlier, I use this for the quick ad-hoc question answering, not for detailed a recurring reporting. For more robust or recurring reporting I would look to SSRS or Excel to build a solution.