Customizing and Automating Spotlight on SQL Server Enterprise Reports
In my previous post, I wrote about configuring and executing reports using Spotlight on SQL Server Enterprise. The purpose of this entry is to describe, at a very high-level, what's needed to customize and automate the execution of these reports. I'll introduce the topics of Microsoft's Report Definition Language (RDL) and SQL Server Reporting Services (SSRS), but I will not discuss them at length.
Tell Me About Your Reports
As I mentioned previously, Spotlight on SQL Server Enterprise supplies numerous reports. The report files are located in a sub-directory within the installation path of the Spotlight console. Typically, the path is <install_dir>\Plug-ins\Trending\Reports. If you navigate to this directory, you will notice numerous files having a RDL file extension. Good news! All of Spotlight's reports use Microsoft's Report Definition Language!
I'll defer to Microsoft's documentation for more information about RDL. Fortunately, RDL allows us to extend existing reports, create new reports, and use SQL Server Reporting Services to automate their execution. <Spoiler Alert> In fact, when it comes to automating reports, we defer to the steps required by SQL Server Reporting Services for their execution. I'll expand slightly upon this later.
If you open a few different files, you'll start to get a sense of RDL syntax as well as the data model of the Spotlight Statistics Repository database that feeds the data used in reporting. However, you don't need to reverse engineer the data model. We document this within the Spotlight console. Navigate to the Help menu->Help Contents, and type "schema" into the search box:
This document will be THE source for understanding the schema of the Spotlight Statistics Repository, how data flows into the database, and how to query the data. Spotlight provides a number of stored procedures that make it easy to quickly understand the data contained in the dimensional tables. This knowledge will be used to build the custom T-SQL statements used by your RDL files.
Add Newly Created Reports to the Console
If you want your custom reports to appear in the Spotlight console, the easiest thing to do is copy your newly created RDL files into the reports directory on your Spotlight console machine. Remember, the directory is typically <install_dir>\Plug-Ins\Trending\Reports. You might need to restart the console in order for your newly added report to appear in the list:
**Bonus points are awarded to those of you that decide upon horrible naming conventions. I don't condone the behavior, mind you. I just seem to practice it well.
You can change the path Spotlight uses to search for reports. To do this, right-mouse click on any report in the list and choose "Settings" and select the relevant directory:
After restarting the Spotlight console, you'll see the RDL files in the directory you specified. This could be useful if you prefer creating your own reports and only having them visible within Spotlight. I think a better approach is to leverage what's already available and then to add your own using the method I described above. However, you know what they say about opinions.
Automate Report Execution
Do you remember when I mentioned that reports can be automated using SQL Server Reporting Services? You didn't? No worries. However, please know that's precisely how you automate report execution. Report scheduling and automation is available via a SSRS subscription.
Learn more or download a trial at Spotlight on SQL Server Enterprise.
