Is Everything OK?

Is it just me or is there still a bit of a lack of an overall SQL Server BI ecosystem? Don't get me wrong, I am a big proponent of SQL Server BI stack and there are a lot of nice integration points but I think there is still a lot of work to be done to tie it all together.

One of my biggest gripes is related to where to look when there seems to be an issue with a BI solution that uses SSIS, SSAS, and SSRS. And what if you just want to know if things have been humming along. We can implement all the email notifications in the world but what if your email system is unreliable (as it was with a recent client). Hence the question, "Is everything OK?"

I'm pretty well versed in exception handling and logging and can probably set my brain to autopilot and find an error message in a log on one of 10 servers without actually thinking about it but what about everyone else? There are other stakeholders such as more junior developers or even senior level people that are unfamiliar with SQL Server. How about the business? An analyst might see something fishy in the data and want to know if last night's load was successful. Do they have to call me or someone else on the development team? In many cases yes. But why? That kind of a dependency is unhealthy.

That's where the SQL Server BI Health Monitor Dashboard comes in. The SQL Server Health what? Don't fret if you haven't heard of this. I'd be suprised, no maybe disturbed, if you had. The SQL Server Health Monitor Dashboard was something I recently put together for a client that was unable to obtain a seasoned SQL Server BI developer but needed to continue to support the solution I built for them without having to rely on getting a hold of me.

The dashboard uses all out-of-the-box data sources including some system tables, the SSIS log table created by the SQL Server log provider, and the SSRS tables found in the ReportServer database to tie together a majority of the data needed to determine where in the SQL Server BI ecosystem your problem lives thus helping fix the problem that much faster.

I've posted the source code on Codeplex. You can find it @ http://bihealthdashboard.codeplex.com/. It's not exactly plug and play at this point. Connection strings need to be modifed and you might have to create the DimDate table if you don't already have a table of dates accessible. It's also geared towards a BI solution that has a single SQL Server Agent job step that calls a master SSIS package that in turn calls all other SSIS packages and a single SQL Server Agent job step that process your SSAS database. If that doesn't sound like your system, the solution can still be valuable in that it shows you how to extract very useful process metadata from your SQL Server BI Solution.

Which leads me to my next endeavor  ->  The SQL Server Metadata Manager . . .