Creating SQL Server data mining reports

SQL Server Reporting Services allows you to create custom data mining reports containing text and graphics that can be distributed via HTML, email or in print or Microsoft Office documents.

Another way to access data mining query results and to distribute those results is to use SQL Server Reporting Services. Reporting Services provides a mechanism for creating custom reports containing text and graphics that can be distributed via HTML, email or in print or Microsoft Office documents. Web-based reports can be made interactive by adding report parameters that modify the report contents. Reporting Services has options to run reports periodically and cache the results to expedite report retrieval, and you can even specify queries to control report distribution. The product documentation has extensive details on Reporting Services functionality, and there are several books on how to create and manage reports, so I will only discuss how data mining features are integrated.

To use Reporting Services with data mining, you simply create a Reporting Services project with the BI Dev Studio and specify an Analysis Services database as your data source. Depending on the contents of the database, you will be presented with either an OLAP or Data Mining query builder as in Figure 3.23. You can always switch between the two modes by clicking the Switch

Command Type button indicated in the figure. The Set Parameters button allows you to create parameters that can be set by Reporting Services. The Switch Query Mode button allows you to switch between Design mode and SQLmode so that you can create queries not supported by the Query Builder.

Tip: You can create reports from user defined functions using the CALL syntax. To do this, you must be in the DMX Query Builder and switch to the SQL mode where you can directly type in your CALL statement.

When creating a report from a data mining query, you have all of the functionality of Reporting Services to work with. For example, DMX does not support grouping operations, but Reporting Services does. You can use the grouping functionality of Reporting Services against the query described in Figure 3.23 to create a report that is the equivalent of the classification matrix described previously. Figure 3.24 shows the result of such a report.

Figure 3.23 Data Mining Report Designer indicating important controls

Figure 3.24 Classification matrix report

Click here to return to the complete list of book excerpts from Chapter 3, 'Using SQL Server 2005 data mining,' from the book Data Mining with SQL Server 2005.

Dig Deeper on SQL Server Business Intelligence Strategies