Transact-SQL and C#

The Data Mining Web Controls sample uses the SQL Server Analysis Services features listed in the following table.

Before you run this sample, make sure that the following software is installed:

• Microsoft SQL Server, which includes the following components:
  
  • SQL Server Database Engine
    
  • SQL Server Management Studio
    
  • Business Intelligence Development Studio
    
• Analysis Services
  
• Two sample databases—Adventure Works DW (data warehouse) and AdventureWorks (OLTP)
  
• Analysis Services Samples
  
• Adventure Works DW Analysis Services project, deployed to an instance of Analysis Services
  
• .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. For more information, see Installing the .NET Framework Documentation in SQL Server Books Online.
  

An updated version of the AdventureWorks database is also available for download on the SQL Server Developer Center Web page.

An updated version of the SQL Server Database Engine samples is also available for download on the SQL Server Developer Center Web page.

Note:
SQL Server Business Intelligence Development Studio is not supported on computers that run the Itanium-based platform; the 64-bit development environment is supported on x64-based platforms. SQL Server samples can be modified and run on computers that run either x86 or x64-based platforms when databases used by the samples are deployed on an Itanium-based platform.

Before you build the sample, make sure to install Analysis Services on the computer that is running the Web server. If you cannot install Analysis Services completely, at minimum you must install MSOLAP, which is the OLE DB provider for Analysis Services. MSOLAP provides client connectivity for the server, and the Data Mining Web Controls need this provider to connect to the server that is running Analysis Services and the data mining models.

To build the sample, you will have to do the following tasks:

• Install and deploy the Adventure Works DW sample Analysis Services project. This project contains the mining model which will be used in the project.
  
• Build and install the Data Mining Web Controls.
  

The following procedures show how to do each of these tasks for the installation.

 To deploy the mining model

1. In Windows Explorer, locate C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project, open the folder that corresponds to the edition of SQL Server that was installed, and then double-click the Adventure Works.sln file.

   The Adventure Works DW project opens in Business Intelligence Development Studio.

2. Press F5 to build and deploy the project.

   Note:
   Building and deploying the AdventureWorksAS project deploys the mining model to the server.

 To build and install the Data Mining Web Controls

1. In Windows Explorer, locate C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\DataMining\Data Mining Web Controls, and then double-click the WebControls.sln file.

   The WebControls solution opens in Visual Studio.

2. On the Build menu, click Configuration Manager.

3. In the Configuration Manager dialog box, select the Build check boxes for both the DMHTMLViewersSetup and WebControls projects (if not already selected).

4. On the Build menu, click Rebuild Solution.

5. In Windows Explorer, locate C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\DataMining\Data Mining Web Controls\WebControlsSetup\Debug, and then double-click the setup.exe file.

6. In the Microsoft SQL Server Data Mining HTML Controls Setup Wizard, click Next, and then Next again to start the installation of the controls.

7. After the controls have been installed, click Next and then Finish to complete the wizard.

   After the wizard finishes, the following folders will have been added to the server:

   • The C:\Program Files\Microsoft.AnalysisServices.DataMiningHtmlViewers folder that contains the library, the source code for the controls, and this document.
     
   • The C:\Inetpub\wwwroot\aspnet_client\microsoft_analysisservices_datamininghtmlviewers folder that contains client-side scripts and images used by the Data Mining Web Controls. This folder is also Web shared.
     

After building the sample, you can use the data mining controls. To use these controls, you must first create an ASP.NET Web application, add the controls that you want to the application, and then configure the controls that have been added.

 To add the data mining controls to a Web application

1. In Microsoft Visual Studio .NET, on the File menu, and click New Web Site.

2. In the New Web Site dialog box, select ASP.NET Web site, select Microsoft Visual Basic or Microsoft C# as the language, and then click OK.

3. If Toolbox is not currently visible, on the View menu, click Toolbox.

4. In Toolbox, expand the category to which you want the data mining controls (for example, expand Standard), right-click within that category, and then click Choose Items.

5. In the Choose Toolbox Items dialog box, locate and open the C:\Program Files\Microsoft.AnalysisServices.DataMiningHtmlViewers\Microsoft.AnalysisServices.DataMiningHTMLViewers.dll file.

   The three data mining controls appear in the list of components and their check boxes have automatically been selected.

6. Click OK.

   The three data mining controls should now appear in the selected category within Toolbox.

7. To add a data mining control to the application, drag the control from Toolbox onto the Web form design page.

   After you add a data mining control on the Web form page, there are some control properties that you must set and some that are optional. The next section describes how to set these properties.

A data mining Web control has two categories of properties:

• The Data Mining Content category contains properties that define the model to be displayed. Among these properties, there are properties that must be set to use the control. The following table lists the properties shared by all the Web controls and indicates which of these properties must be set for all controls.
  

  Property	Type	Default	Requires changes
  Server	string	Localhost	Yes
  Database	string	<empty>	Yes
  Model	string	<empty>	Yes
  Connection	System.Data.OleDb.OleDbConnection	(none)	No

  In addition to these common controls, the DMDecisionTreeViewer control has additional Data Mining Content properties that must be set. For more information about these additional properties, see the section that describes how to set the properties for the DMDecisionTreeViewer control.
  
• The Data Mining Layout category contains graphical properties for the controls. As the graphical representation for each data mining Web controls is different, the graphical properties associated with the controls vary correspondingly. You can use the default settings for these properties as is.
  

To set the properties for a control, you use can use the Properties window in Visual Studio. If you cannot see the Properties window, right-click the control, and then click Properties. Also, to easily access the properties of the control, in Properties, select the Categorized view.

Note:
Also, you can set these values programmatically in the code shown on the Source tab.

Setting DMClusterView Control Properties

You can use the DMClusterViewer control to display the content of models created using the Microsoft Clustering algorithm. Not as rich as the cluster viewer found in Business Intelligence Development Studio, the cluster viewer for this control only supports cluster characteristics and cluster discrimination.

The DMClusterViewer control has properties in the Data Mining Content category that must be set and optional properties in the Data Mining Layout category whose default settings can be used as is.

Setting the Data Mining Content Properties for the DMClusterView Control

The properties in the Data Mining Content category that must be set for the DMClusterView control are those properties shared by all the Web controls—Server, Database, and Model. If you want, you can also change the Connection property for this control.

 To set the properties required by the DMClusterView control

1. In Visual Studio, on the design view for the Web form, select the DMClusterView control.

2. In the Properties window, in the Data Mining Content category, set the following properties:

   • Set the Server property to the name of the computer on which Analysis Services is installed.
     
   • Set the Database property to the name of the catalog that contains the model to be displayed. For this sample, the name of the database that contains the mining models is Adventure Works DW.
     
   • Set the Model property to the mining model to be loaded in the viewer.
     
   • (Optional) Set the Connection property to an existing OLE DB connection (that is, a System.Data.OleDb.OleDbConnection object). This allows the control to share a connection with the application instead of creating a new one.
     

Setting the Data Mining Layout Properties for the DMClusterView Control

The following table lists the graphical properties associated with the DMClusterView control. Changing the default setting for these graphical properties is optional.

Property	Type	Default	Requires changes
BarColor	System.Drawing.Color	Navy	No
ViewerMode	GridViewerMode	Discrimination	No

 To set the graphical properties of the DMClusterView control

1. In Visual Studio, on the design view for the Web form, select the DMClusterView control.

2. In the Properties window, in the Data Mining Layout category, set the following properties:

Setting DMNaiveBayesViewer Control Properties

You can use the DMNaiveBayesViewer control to display the content of mining models created using the Microsoft Naive Bayes algorithm. The DMNaiveBayesViewer control only supports attribute characteristics and discrimination between attribute values.

Setting the Data Mining Content Properties for the DMNaiveBayesViewer Control

The properties in the Data Mining Content category that must be set for the DMNaiveBayesViewer control are those properties shared by all the Web controls—Server, Database, and Model. If you want, you can also change the Connection property for this control.

 To set the properties required by the DMNaiveBayesViewer control

1. In Visual Studio, on the design view for the Web form, select the DMNaiveBayesViewer control.

2. In the Properties window, in the Data Mining Content category, set the following properties:

   • Set the Server property to the name of the computer on which Analysis Services is installed.
     
   • Set the Database property to the name of the catalog that contains the model to be displayed. For this sample, the name of the database that contains the mining models is Adventure Works DW.
     
   • Set the Model property to the mining model to be loaded in the viewer.
     
   • (Optional) Set the Connection property to an existing OLE DB connection (that is, a System.Data.OleDb.OleDbConnection object). This allows the control to share a connection with the application instead of creating a new one.
     

Setting the Data Mining Layout Properties for the DMNaiveBayesViewer Control

The following table lists the graphical properties associated with the DMNaiveBayesViewer control. Changing the default setting for these graphical properties is optional.

Property	Type	Default	Requires changes
BarColor	System.Drawing.Color	Navy	No
ViewerMode	GridViewerMode	Discrimination	No

 To set the properties specific to the DMNaiveBayesViewer control

1. In Visual Studio, on the design view for the Web form, select the DMNaiveBayesViewer control.

2. In the Properties window, in the Data Mining Layout category, set the following properties:

Setting DMDecisionTreeViewer Control Properties

The DMDecisionTreeViewer control can be used to display the content of models created using the Microsoft Decision Trees algorithm. The DMDecisionTreesViewer control can work in following two modes:

Tree mode

Displays a complete decision tree and allows the following operations:

• Expanding and collapsing of nodes.
  
• Following the splits in decision trees.
  
• Shading based on support for a state of the predicted attribute.
  
• Inspecting, by way of a ToolTip, of the distributions for a specific node.
  

Node Details mode

Provides details about a specific node in the tree, identified by the NODE_UNIQUE_NAME field from the MINING_MODEL_CONTENT schema. The Node Details view is different if the attribute is discrete / discretized (classification) or continuous (regression).

Setting the Data Mining Content Properties for the DMDecisionTreeViewer Control

In addition to the properties in the Data Mining Content category that are shared by all the Web controls—Server, Database, and Model, you must set the properties listed in the following table for the DMDecisionTreeViewer control

Property	Type	Default	Requires changes
TargetNode	String	<empty>	yes
TargetTree	String	<empty>	yes

Note   Either the TargetTree property or the TargetNode property must be set. Both of these properties cannot be <empty>.

 To set the properties required by the DMDecisionTreeViewer control

1. In Visual Studio, on the design view for the Web form, select the DMDecisionTreeViewer control.

2. In the Properties window, in the Data Mining Content category, set the following properties:

   • Set the Server property to the name of the computer on which Analysis Services is installed.
     
   • Set the Database property to the name of the catalog that contains the model to be displayed. For this sample, the name of the database that contains the mining models is Adventure Works DW.
     
   • Set the Model property to the mining model to be loaded in the viewer.
     
   • (Optional) Set the Connection property to an existing OLE DB connection (that is, a System.Data.OleDb.OleDbConnection object). This allows the control to share a connection with the application instead of creating a new one.
     
   • Set the TargetTree property to the tree to be displayed in the control. You identify this tree by the name of the predictable attribute. To retrieve the names of predictable attributes in a model, use a query similar to the following one:
     
     "SELECT ATTRIBUTE_NAME FROM [Model].CONTENT WHERE NODE_TYPE=2"

     Note:
     If you use the DMDecisionTreeViewer control in Tree mode, you must set the TargetTree property.

   • Set the TargetNode property to the NODE_UNIQUE_NAME value for the node to be displayed. To obtain a list of nodes in the tree, use a query similar to the following one:
     
     "SELECT NODE_UNIQUE_NAME, ATTRIBUTE_NAME FROM [Model].CONTENT WHERE NODE_TYPE=3 OR NODE_TYPE=4"

     Note:
     If you use the DMDecisionTreeViewer control in Node Details mode, you must set the TargetNode property.

Setting the Data Mining Layout Properties for the DMDecisionTreeViewer Control

The following table lists the graphical properties associated with the DMDecisionTreeViewer control. Changing the default setting for these graphical properties is optional.

Property	Type	Default	Requires changes
ViewType	TreeViewType	Tree	No
ShowToolbar	Boolean	True	No
OverflowBehavior	TreeOverflowBehaviorType	Visible	No
NodeLowDensity	System.Drawing.Color	White	No
NodeHighDensity	System.Drawing.Color	Navy	No
MaxVisibleHistograms	int	6	No
LineStrokeWidth	int	1	No
LineStrokeColor	System.Drawing.Color	Black	No
HistogramTooltipEvent	TreeTooltipEvent	OnMouseOverEvent	No
HistogramText	System.Drawing.Color	Black	No
HistogramBackground	System.Drawing.Color	Beige	No

 To set the properties specific to the DMDecisionTreeViewer control

1. In Visual Studio, on the design view for the Web form, select the DMDecisionTreeViewer control.

2. In the Properties window, in the Data Mining Layout category, set the following properties:

When a user opens your page, the controls are instantiated. The controls use an OLE DB connection to access the server that is running Analysis Services and the data mining models. By default, ASP.NET applications run under the set of credentials of a specific user, <MACHINE\ASPNET>, which has limited permissions. This set of permissions is not enough for accessing the data mining models.

An easy and safe way to solve the limited permissions of the <MACHINE\ASPNET> user is to enforce impersonation of the Web user in the ASP.NET application. To enforce impersonation, you must to disable anonymous access for the application that you are developing. After you disable anonymous access, your Web application will impersonate the Web client’s user and access the Analysis Services instance using that user’s credentials. For more information about disabling anonymous access for ASP.NET applications, see the documentation that is included with Visual Studio and IIS.

There are alternative ways of solving the access problem caused by the <MACHINE\ASPNET> user:

• Impersonate a specific user for all the requests. (For more information,see the article on <identity> in web.config in the MSDN library that is included with Visual Studio .NET).
  
• Add data mining permissions for the <MACHINE\ASPNET> user in the Analysis Services instance. Permissions for Read access and ReadDefinition are required for the database, the mining structure, and the mining model to be displayed in the Web controls.
  

  Note:
  The controls will be unable to access the Analysis Services instance if the security permissions are not correctly configured using one of the previously described methods.

If an error occurs when rendering a control, the error message appears in red on the page.

Tasks

Other Resources

Help and Information