Manage Learn to apply best practices and optimize your operations.

Using SQL Server Management Studio for data mining

SQL Server 2005 Management Studio replaces Enterprise Manager and Query Analyzer. Find out what tasks can be performed in Management Studio with regards to SQL Server data mining.

Management Studio in SQL Server 2005 is an environment familiar to DBAs -- it replaces Enterprise Manager and Query Analyzer from SQL Server 2000, which are the tools of the trade for many administrators. In SQL Server 2005, this environment has been expanded to cover all SQL Server technologies. One user interface can manage relational databases, OLAP cube, data mining models, Reporting Services, and more. The tasks that can be performed in the Management Studio that are pertinent to data mining are:

  • Server maintenance
  • Database creation and maintenance
  • Browse models
  • Build queries using Prediction Builder
  • Build queries using Query Editor
  • Process models and structures
  • Assign object permissions
  • Backup and restore databases

    To perform operations on an Analysis Services database in SQL Manager, you must have Database Administrator permissions on the database you are modifying. You do not need to be an administrator of the entire server.

    Note: If you want to set up a database for a nonserver administrator to create mining models, a user with server administrator privileges needs to create a new database in SQL Management Studio and make the nonserver administrator a database administrator for that database. The user will then use BI Dev Studio in immediate mode to create and edit mining models.

    Understanding the Management Studio User Interface

    The Management Studio interface has the same look and feel as the BI Dev Studio. Windows, menus, and toolbars all work the same way in both studios, so you can customize your layout in the Management Studio just as you can in the BI Dev Studio. The tool windows of most interest to data mining are the Registered Servers (Server Explorer) window, Object Explorer, and Template Explorer, as shown in Figure 3.25. The Server Explorer window allows quick access to a number of servers you interact with regularly. To access Analysis Services servers, you need to click the cube icon in the embedded toolbar, as shown in Figure 3.25. The Object Explorer window is where you will perform most of your work and is described in more detail in the next section. The Template Explorer is hidden by default and must be selecting from the View menu.

    The Template Explorer contains a set of syntax templates to make it easier to create queries. As in the Server Explorer, you need to select the indicated cube icon to access the templates specific to Analysis Services.

    Figure 3.25 Management Studio with Template Explorer shown and Analysis Services Icons indicated

    Using the Object Explorer

    The Object Explorer is where the majority of data mining management operations will occur. Most operations can be performed by expanding the tree structure to the object of interest and then right-clicking the object and selecting the desired operation. The most common operations you will perform here are processing, security management, and backing up and restoring databases. Security management is described in Chapter 13, and backing up and restoring are well documented in the product documentation. Looking at the object properties from this interface will provide you with creation date and last processing date. From the Object Explorer, you can also create XML for Analysis (XMLA) scripts that will create, alter, or delete the specified object. Selecting Script Mining Model as Create from the context menu will create an XMLA script to the clipboard, a file, or a Query Editor window. Using XMLA is a quick and convenient way to create objects that differ only slightly by scripting them, changing the object identifiers, and making other edits. XMLA is described in more detail in Chapter 13.

    Using the Query Editor

    The Query Editor is designed for advanced users with a solid understanding of DMX syntax. The editor is a freeform text editor with IntelliSense that allows you to type and execute ad hoc queries. You can then execute these queries directly from the interface and examine the results.

    There are two ways to create a DMX query: you can click theNew DMX Query button on the toolbar, or you can open a template from the Template Explorer. When you create a new query window, you will be prompted to connect to a server. The Connection dialog box does not ask you to provide the database name you to which you wish to connect, so after the editor window opens, you need to select the target database in the SQL Editor toolbar. At this point, you can start authoring your queries. In the editor, you have all of the standard Visual Studio editing features, such as search and replace, bookmarking, keyword color coding, and IntelliSense. You can use IntelliSense by beginning to type a keyword and pressing Ctrl+Space to show a menu of likely matches. You can have many DMX queries in the same file. To indicate which query to execute, simply select it before pressing the Execute button or pressing F5.

    Although the level of flexibility provided by the Query Editor demands quite a bit of DMX knowledge from the user, there are ways to jumpstart the query authoring process to make it easier. The first is to use the Template Explorer, which provides many syntax examples from which to choose. The other is to use the Query Builder described earlier in this chapter. You can launch the

    Query Builder by right-clicking on a mining model and selecting the Query Builder from the model's context menu. Once you have built enough of your query to get started, you can simply switch to SQL view and copy the text into a Query Editor window. This automatically creates any source data queries for prediction join statements and frees you from writing lengthy On clauses.

    Tip: You can also drag Template Explorer items into existing query windows to add the template contents to your current query.


    In this chapter, you learned about the breadth of tools available for data mining. The BI Dev Studio, with its wizards and editors, is where you will perform most of your data mining by creating and examining models either attached to a server or in an offline project. In this environment, you complete a data mining project by creating a data source, a data source view, and finally a mining structure with its contained mining models. All of these objects are fully editable through a combination of custom designers and the property grid. In addition, you can use the Data Mining Designer to view models with a variety of custom viewers and to test models with a variety of accuracy charts.

    SQL Management Studio is where models are managed throughout their lifetime. This environment provides tools for security, processing, backing up and restoring databases, and other management functions. The viewers, accuracy charts, and Prediction Builder that are available in the BI Dev Studio are available here as well. Additionally, there is the free-form Query Editor with a query template library that you can leverage to create ad hoc DMX queries.

    These two tools create the user interface suite for Analysis Services.

Dig Deeper on SQL Server Business Intelligence Strategies