Excelling at Dashbaords

SQL Server MVP Deep Dives 2, Chapter 58

Excel as a BI frontend tool – by Pedro Perfeito (Blog|Twitter)

No matter how good and sophisticated a BI tool is, business users will invariably export data to Excel. – Chapter 58, SQL Server MVP Deep Dives V2

Continue reading


Reinventing the SQL Monitor

SQL Server MVP Deep Dives 2, Chapter 12

Build your own SQL Server 2008 performance dashboard – by Pawel Potasinski (Blog|Twitter)

For this chapter Pawel Potasinski has done exactly what the title suggests. He lays out the steps to create a simple performance dashboard for SQL Server. He specified the version in his chapter title, but he clarifies that these steps can be “implemented on SQL Server 2005 and later (where CLR, DMV’s and SSRS are available).” That one sentence sort of gives away his whole strategy. He shows how to use a Common Language Runtime (CLR) function to get at operating system performance counters. He then combines this with SQL Server Performance counters from the dm_os_perfornamce_coutners DMV and uses a SSRS report to display them both.

Continue reading

My Lack of Insight

SQL Server MVP Deep Dives 2, Chapter 59

Real-time BI with StreamInsight – by Allan Mitchell (Blog|Twitter)

This was a hard one. I had to remind myself a couple of times while reading this chapter that I am writing these reviews so that I can understand these topics better.

Before I read this chapter I had never heard of StreamInsight and it took me a while to wrap my head around what exactly this tool is. Finally after doing some Binging around on the topic and reading the chapter for the sixth time these lines jumped out at me:

StreamInsight is a complex event-processing engine

Once the events leave the input adapter, they enter the engine.

The engine is where all the query logic is introduced and from where the intelligence is derived.

Continue reading

Here I Come to Save the Data

SQL Server MVP Deep Dives 2, Chapter 11

Will the real Mr. Smith please stand up? – by John Magnabosco (B|T)

This chapter is a high level, easy to read explanation of PID (Personally Identifiable Data) and why it is our job as the DBA to protect that information. If you have not come across this concept yet, PID is typically things like Social Security Number, Date of Birth, Driver’s license number, and images of fingerprints. Continue reading

Business Intelligence 201

SQL Server MVP Deep Dives 2, Chapter 60

BI Solution Development Design Considerations– by Siddharth Mehta (B|T)

This is the first chapter in Deep Dives V2 that I read which felt like I was reading a textbook. I really wanted this chapter to be something I could hand to a manager, DBA, or developer who hadn’t worked on a Business Intelligence project and they would come away with a good understanding of what it was and all of the pieces needed to build it. Essentially I wanted something the me from 3 years ago could have read and not have to learn everything on the job.

Figure 1 Typical BI design for an OLTP-to-dashboard solution from Chapter 60 of SQL Server MVP Deep Dives V2

Continue reading

Fun with SQL, Excel, and Powershell

SQL Server MVP Deep Dives 2, Chapter 10

Discover your servers with Powershell and SMO – by Joe Webb (B|T)

I’m finally back to my chapter reviews and I restarted with a fun one. I know enough to be dangerous with powershell, but I don’t use it enough to keep it fresh in my brain. Right off I liked the idea of this chapter because I got to play with scripting and do something a little different (at least for me). I know there are other ways of gathering this type of data us ing T-SQL (Brent Ozar’s sp_blitz being the first one that comes to mind), but I haven’t done it with powershell before and I certainly never used powershell with Excel before.

Continue reading

Reaching Capacity

SQL Server MVP Deep Dives 2, Chapter 9

Capacity Planning – by Greg Larsen (B|T)

I can file this chapter with many of the others under, “Things I wish I had read earlier in my career”. A good part of that career was at a small company and we were able to gauge our database capacity by the number of clients we had with pretty good results. This worked well because as we added more clients we had capital to add more database resources. Until…we wanted to add a very large number of clients all at once. People came to me for estimates on what we would need to handle the sudden jump and I could give pretty good estimates for disk space, but I had not been tracking our capacity for other things like memory, CPU and I/O.I knew the  infrastructure we had would not be up to the task, but I didn’t have the information to back it up. Greg Larsen’s chapter in Deep Dives not only explains why you need to have these metrics, but he provides examples on which specific counters you should start looking at and how to gather them.

You won’t believe how quickly management will authorize the purchasing of a new machine when you produce a nice colorful graph showing how the various performance metrics are getting close to reaching your machine’s theoretical hardware limits. – Chapter 9, SQL Server MVP Deep Dives 2  Continue reading