Export (0) Print
Expand All
Automate Testing of Your Stored Procs
Streamline Your Database Setup Process with a Custom Installer
Stop SQL Injection Attacks Before They Stop You
Get a Lean, Mean Dev Machine with the Express Editions of Visual Basic and SQL Server 2005
Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
AMO Lets You Dig Deeper into Your Data from Your Own Applications
Make Sense of Your Web Feedback using SQL Server 2005
Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities
Unearth the New Data Mining Features of Analysis Services 2005
Using XQuery, New Large DataTypes, and More
Expand Minimize
2 out of 2 rated this helpful - Rate this topic

A Tutorial for Constructing a Plug-in Viewer

SQL Server 2005
 

Jesper Lind and Scott Oveson
Microsoft Corporation

August 2004

Applies To:
   Microsoft SQL Server 2005 Analysis Services

Summary: This article steps through the process of implementing a plug-in viewer and integrating the viewer into Analysis Services. Provides stub code to enable viewer developers to quickly write a plug-in viewer into Analysis Services. (21 printed pages)

Contents

Overview
Setting Up the Project
Implementing IMiningModelViewerControl
Assigning a Strong Name to the Assembly
Mechanisms to Discover Data Mining Models
Update the Plug-in Algorithm to Use the Plug-in Viewer
Adding UI Code to the Viewer

Overview

Microsoft Analysis Services Data Mining Viewer Plug-in Framework defines the necessary interfaces and registry information that third parties can implement, so that their algorithm viewers will be seamlessly integrated in the SQL Server Business Intelligence Development Studio Designer.

DM Algorithm Viewer Plug-in Framework is to allow third party algorithm providers to use their own viewers displaying the discovered patterns. Viewers are .NET WinForms UserControls which display patterns of the model. Viewers based on the Plug-in Framework will be integrated seamlessly in the DM Designer in both Project and Immediate modes.

Microsoft Analysis Services Data Mining Plug-in Framework supports both Algorithms plug-ins and Viewer plug-ins. A viewer may be used to view more than one type of algorithm. Likewise, several algorithms could use the same viewer.

In this tutorial we will describe how to write a viewer, and we will modify the Pairwise_Linerar_Regression model (see algorithm tutorial) to work with the new viewer.

Although a viewer could be implemented using a Winforms UserControl and any .NET language, or as an ActiveX control wrapped with a Winforms user control wrapper, the most convenient way to implement a viewer is using Winforms and the C# language. The language is perfectly suited for writing UI components and we will only give the reader of this tutorial a hint of the capabilities. The rest is left to creative UI people and imagination!

We render the output of the model as simple text strings in a RichTextBox object, but one can imagine the usage of more graphics.

Throughout the tutorial there are snippets of code. This code may be pasted directly into Microsoft Visual Studio to avoid typing (and typos). In some cases you will have to type in a custom string such as a public key token.

Setting Up the Project

  1. Create a new C# project.

    Start by creating a new C# project. Under the folder C# choose Class Library as the project type. In this document we will refer use MyCompanyPluginViewers as the name of the solution. We call the project PairwiseLinearRegressionViewer.

  2. Add a UserControl class.

    In the solution explorer, right-click on Class1 and select Delete. Then right-click the project and select Add Class. Select User Control as the class type, and give the class a name. We choose to call the class PairwiseLinearRegressionViewer. Your .cs file should now look like this.

    using System;
    using System.Collections;
    using System.ComponentModel;
    using System.Drawing;
    using System.Data;
    using System.Data.OleDb;
    using System.Windows.Forms;
    
    namespace PairwiseLinearRegressionViewer
    {
       /// <summary>
       /// Summary description for PairwiseLinearRegressionViewer.
       /// </summary>
       public class PairwiseLinearRegressionViewer :
    System.Windows.Forms.UserControl
       {
          /// <summary> 
          /// Required designer variable.
          /// </summary>
          private System.ComponentModel.Container components = null;
    
          public PairwiseLinearRegressionViewer()
          {
    // This call is required by the Windows.Forms Form
    // Designer.
             InitializeComponent();
    
    // TODO: Add any initialization after the 
    // InitializeComponent call
    
          }
    
          /// <summary> 
          /// Clean up any resources being used.
          /// </summary>
          protected override void Dispose( bool disposing )
          {
             if( disposing )
             {
                if(components != null)
                {
                   components.Dispose();
                }
             }
             base.Dispose( disposing );
          }
    
          #region Component Designer generated code
          /// <summary> 
          /// Required method for Designer support - do not modify 
          /// the contents of this method with the code editor.
          /// </summary>
          private void InitializeComponent()
          {
             components = new System.ComponentModel.Container();
          }
          #endregion
       }
    }
    
    

Implementing IMiningModelViewerControl

The next step in the process is to implement IMiningModelViewerControl. This is the interface that implements the viewer. Here is the interface that we need to implement for the object to be a plug-in viewer.

namespace Microsoft.DataWarehouse.Interfaces
{
   public interface IMiningModelViewerControl
   {
      IServiceProvider ServiceProvider { get; set; } 
      string ConnectionString { get; set; }
      string MiningModelName { get; set; }

      /// <summary>
      /// Initialize viewer with mining model content
      /// </summary>
      void LoadViewerData(object context);
      
      /// <summary>
      /// Notify viewer whether it's being activated or deactivated
      /// </summary>
      void ViewerActivated(bool isActivated);
   }
}

In addition there is a property called DisplayName that, if exposed, will provide the UI with a localizable string with which to populate the Viewer dropdown box. Note that if this string is not provided, the UI will still load, but the string will be populated using a registry string rather than the property of the plug-in viewer. We will describe the registry settings later in this document.

  1. Add a reference to Microsoft.DataWarehouse.Interfaces.

    You must be running the 2005 release of Microsoft Visual Studio (codenamed "Whidbey."):Right click on References and select Microsoft.Datawarehouse.Interfaces from the .NET tab. From the same .NET tab, also select System.Drawing.dll, System.Windows.Forms.dll, and System.Data. These references will be necessary as we start adding user controls to the project.

    Add the following using statements.

    using Microsoft.DataWarehouse.Interfaces;
    using System.Windows.Forms;
    using System.Drawing;
    using System.Data;
    using System.Data.OleDb;
    
    

    This will avoid unnecessary typing later on.

  2. Implement IminingModelViewerControl.

    Update the class definition to implement the IMiningModelViewerControl interface.

    public class PairwiseLinearRegressionViewer : 
       UserControl, IminingModelViewerControl
    
    
  3. Implement DisplayName.
    static public string DisplayName
    {
       get { return "MyCompany_PairwiseLinearRegressionViewer"; }
    }
    
    
  4. Add the following class members.
    private string connectionString = string.Empty;
    private string miningModelName = string.Empty;
    private IServiceProvider serviceProvider = null;
    private OleDbCommand command;
    private OleDbConnection connection;
    private OleDbDataReader dataReader;
    
    
  5. Implement ServiceProvider.
    public IServiceProvider ServiceProvider 
    {
       get { return this.serviceProvider; }
       set
       {
          if (this.serviceProvider != value)
             this.serviceProvider = value;
       }
    }      
    
    
  6. Implement ConnectionString.

    The system will call the viewer with the appropriate connection string! This specifies the connection string to the provider that has access to the Data Mining backend.

    public string ConnectionString
    {
       get { return this.connectionString; }
       set
       {
          if (this.connectionString != value)
             this.connectionString = value;
       }
    }      
    
    
  7. Implement MiningModelName.

    Prior to asking the viewer to render itself, it will be called with a mining model name. This enables us to create an OleDbConnection with which we will connect to the mining model.

    public string MiningModelName 
    {
       get { return this.miningModelName; }
       set
       {
          if (this.miningModelName != value)
             this.miningModelName = value;
       }
    }
    
    
  8. Implement LoadViewerData.

    LoadViewerData will be called when the viewer is asked to render the mining model. In this method we will:

    1. Connect to the server.
    2. Load the content.
    3. Render the content.

    For now we will just implement step a, and we will revisit this step later after we have registered the plug-in.

    public void LoadViewerData(object context)
    {
    // Open a new connection to the server.
       connection = new OleDbConnection(this.connectionString);         
       connection.Open();   
    
       // Check the status of our connection.
       if (dataReader != null)
       {
          if (!dataReader.IsClosed)               
             dataReader.Close();
       }
    
    // Create the OleDbCommand.
    string commandText = 
       string.Format("SELECT * FROM [{0}].CONTENT", this.MiningModelName);
       if (command == null)
       {
          command = new OleDbCommand();
       }
       command.Connection  = connection;
       command.CommandText = commandText;
    
    // Execute the command
    dataReader      = command.ExecuteReader();
    
       // Add code to extract information from the schema here.
    
       // Close the connection
    connection.Close();
    }
    
    
  9. Implement ViewerActived.

    If the viewer has another window open such as a legend, it may want to bring that window to the user's attention when the view is activated (that is, clicked on, or selected using the tabs in the viewer or by selecting it using the dropdown box). We won't use this feature in this plug-in.

    public void ViewerActivated(bool isActivated) 
    {
    
    }
    
    
  10. You should now be able to compile the project with no errors.

Assigning a Strong Name to the Assembly

In this section we will update the viewer with a Strong Name. The first step in this process is to create a public, private key pair. Then sign the Assembly by updating the AssemblyInfo.cs file in the project.

  1. Locate gacutil.exe.

    Locate gacutil.exe and sn.exe and make sure you can execute them.

  2. Create a key pair.

    Create a key pair by running sn –k sgKey.snk, which creates the file sgKey.snk containing both a private and a public key. Copy sgKey.snk to the project directory if it's not already located there.

    Here are the comments in the default AssemblyInfo.cs created for your project.

    //   (*) In order to create a KeyFile, you can use the sn.exe (Strong
    //       Name) utility.
    //       
    //       When specifying the KeyFile, the location of the KeyFile 
    //       should be relative to the project output directory which is
    //       %Project Directory%\obj\<configuration>. For example, if your 
    //       KeyFile is located in the project directory, you would specify
    //       the AssemblyKeyFile attribute as 
    //       [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
    
    
  3. Update the AssemblyInfo.cs file.

    Update the AssemblyInfo.cs file with the following line.

    [assembly: AssemblyKeyFile("..//..//sgKey.snk")]
    
    
  4. You should again be able to build the project with no errors.

Mechanisms to Discover Data Mining Models

At the time the DM Tools first fetches information about which algorithms are supported by querying the Mining_Services rowset (this may happen when the user first runs the DM Wizard or the first time they click on the Viewers view in the DM designer), it'll retrieve the information from the server about the suggested viewers associated with each algorithm and cache this metadata. When the user selects the viewer tab, a one time initialization occurs to walk the registry collecting information for the registered DM viewers on the client by inspecting the keys under the appropriate VS package. Additionally, on a unique-connection string basis, data structures are built up to merge the list of registered client viewer with the meta data from the server to come up with a list that prioritizes the list of viewers for a given algorithm by the order suggested by the server (for the given connection) if those viewers are registered, and next any other valid viewers for each algorithm. If an assembly is not found the entry will not show up in the list of viewers. For each viewer, reflection is used to inspect the class provided to find a public static property called DisplayName, which allows the viewer assembly to provide a localized display name for the UI. Note: at this point no viewer has been instantiated.

When there are multiple viewers associated with an algorithm, the default viewer (first in the dropdown) is the first one specified in the Mining_Service schema rowset that is registered on the client. The viewers are not listed alphabetically, instead they are listed in order of the list suggested in the mining services schema rowset first, followed then by those found on the clients that support the specific algorithm (in no specific order), and lastly by viewers supporting all algorithms via the wildcard (*) like the Generic Content Grid Viewer.

When we need to load a new viewer for a mining model (users click on the Viewers view in the Data Mining editor for the first time and by default the first model will be viewed, or they select a new mining model in the dropdown at the top of the viewer), the algorithm associated with the model is retrieved. A lookup against the cached algorithm\viewer information that is retrieved above is performed and the first viewer in the list that is registered on the client is invoked by instantiating the class in the assembly name provided using reflection. This class must be a WindowsForm UserControl and implement the IMiningModelViewerControl interface as defined in Microsoft.DataWarehouse.Interfaces. Note: the viewer may be implemented as an ActiveX control as long as it is wrapped with a WindowsForm UserControl.

If the class fails to instantiate, an error is shown, and the viewer is removed from the cached list so no future attempt to instantiate this viewer will be made for the remainder of the application session. Note that this is only in the case where the class could not be instantiated, not in cases where some error occurs after instantiating the class (like loading the data into the viewer). If the class failed to instantiate, then an attempt to instantiate the next viewer in the list is made. All algorithms will have one entry in the Viewers dropdown next to the Mining Model combo box in the Viewers view of the DM editor that will let users see the Generic Content Viewer.

Once the viewer class has successfully instantiated, methods on the IMiningModelViewerControl interface are used to instruct the viewer to load the data, and provide services that are useful to the viewer as described in the following section.

All viewers (including Microsoft Viewers) are responsible for having a setup program or similar piece of code that creates the necessary registry keys and entries for the viewer in the client machine registry. This may be done manually using regedt32, using simple .reg files, or using a more sophisticated setup program. The keys should be created under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\BIShell\Packages\{4a0c6509-bf90-43da-abee-0aba3a8527f1}\Settings\Designers\DataMiningViewers key if you want the viewer registered for the BI Development Studio, and under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\Packages\{4a0c6509-bf90-43da-abee-0aba3a8527f1}\Settings\Designers\DataMiningViewers key if you want them registered for use in the SQL Management Studio. This is a consistent granularity with how designers are registered (by package for a given AppID). It allows different viewers to be registered, for example, in the SQL Server Management Studio for administrators than in the BI Developement Studio shell.

Under the key mentioned above there is a key created for each new viewer, and under the key, several registry entries describe the information necessary to instantiate the viewer class.

Entry NameValue (type string)
AlgorithmsSemicolon delimited string that contains the list of supported algorithm names using the name of the algorithm as registered in the Mining Services schema rowset on the server. A wildcard * may be specified to register this viewer as supporting all algorithms.
AssemblyFully-qualified assembly name which may include version information as well as PublicKeyToken.
ClassFully-qualified class name of the WindowsForm UserControl implementing the IMiningModelViewerControl interface. (Namespace.ClassName).
DisplayNameFallback UI display name for the viewer dropdown if no localized display name property found for the control.
  1. Update the Registry.

    Run regedt32.exe, and navigate to the path described above. Add new string values with the following information:

    Algorithms = MyCompany_Pairwise_Linear_Regression

    Assembly = MyCompanyPluginViewers, PublicKeyToken=ca6cde3098d5be29

    Class = MyCompanyPluginViewers.PairwiseLinearRegressionViewer

    DisplayName = MyCompany Pairwise Linear Regression Viewer Fallback Name

    The regedt32.exe tool should look something like picture below.

  2. Update the PublicKeyToken.

    After you have updated the AssemblyInfo.cs file and recompiled you may extract the token from the dll by executing

    >Sn.exe -T MyCompanyPluginViewers.dll
    
    Microsoft (R) .NET Framework Strong Name Utility  Version 1.1.4322.573
    Copyright (C) Microsoft Corporation 1998-2002. All rights reserved.
    
    Public key token is 626bf92d9368a93d
    
    

    Update the Assembly field in the registry with the Public key token corresponding to your Assembly.

    ms345129.tutconpiv01(en-US,SQL.90).gif

    ms345129.tutconpiv02(en-US,SQL.90).gif

    Figure 1. The regedt32.exe tool

  3. Copy Assembly to the location of the viewer Assemblies and register it.

    The viewer assemblies are located in the following directory.

    \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\
    
    

    Copy MyCompanyPluginViewers.dll to the directory above.

  4. Register the Assembly.

    After copying the Assembly to the directory above, go to the same directory and register the Assembly by executing the command

    gacutil /i MyCompanyPluginViewers.dll
    
    

Update the Plug-in Algorithm to Use the Plug-in Viewer

Before we can check whether the viewer shows up we must update the plug-in algorithm. Each Mining Algorithm specifies a list of viewers with which it's compatible.

  1. Modify the plug-in algorithm to use the new viewer.

    Update the Plug-in algorithm to use the new viewer. Several viewers may be supported by listing them all using a semicolon separated list. This is done in ALGORITHM.IDMAlgorithmMetatdata.cpp: ALGORITHM::GetViewerType.

    static const LPWSTR szViewer = L"PairwiseLinearRegressionViewer";
    
    
  2. Copy MyCompanyPluginViewers.dll

    Copy MyCompanyPluginViewers.dll to C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\.

    Run gacutil /i MyCompanyPluginViewers.dll

    From now on when you update the plug-in you will need to copy the .dll to the directory above for the Assembly to be picked up by Analysis Services.

  3. Start Business Intelligence Development Studio.
  4. Verify that the viewer is properly registered.

    Create a project using the Plug-in algorithm, and go to viewers. If all the steps prior to this worked as expected you should see your viewer in the dropdown box. You may select it, but there won't be anything showing as we have not written any UI code yet. That is the purpose of the next and final section. If your viewer does not show up, then go back and double check every step. It will not work unless all the steps are completed properly.

Adding UI Code to the Viewer

In this last section we will update the viewer to show the regression formulas generated by the server. There are two ways of doing this. The first is to connect directly to the server and issue a SELECT * FROM [MiningModelName].CONTENT statement to the server. (You don't have to use * if you do not wish to receive the entire schema.)

The other way (which is more flexible) is to write one or more stored procedures that you will call to retrieve information from the server. These may be designed and implemented to return exactly what you need from the server. Writing a stored procedure will allow you to have more control over the data you return to the client, and in some cases this may provide a speedup.

For example, the select statement above will return all rows from the server. You may implement a stored procedure that calculates the information to be displayed in the viewer and returns this as a simple table to the viewer. In most cases that means less processing to be done on the client, and less data to be transferred as well.

  1. Add a RichTextBox.

    Add a RichRextBox to the client UI. In the solution explorer, right-click on the PairWiseLinReg.cs and select View Designer. Click on the Toolbox to the left to expand the Toolbox menu. Click on the RichTextBox and insert one into the design area. Modify the size so that the text box occupies most of the area. The designer should now look something like this:

    ms345129.tutconpiv03(en-US,SQL.90).gif

    Figure 2. The Visual Studio designer

    Right click on PairwiseLinReg.cs again in the designer and select View Code. There should now have been some code generated for you. Rename richTextBox1 to richTextBox.

  2. Initialize the member variables.
    public PairwiseLinearRegressionViewer()
    {
       // This call is required by the Windows.Forms Form Designer.
       InitializeComponent();
    
       // Initialize variables
       connectionString = string.Empty;
       miningModelName  = string.Empty;
       serviceProvider  = null;
    }
    
    
  3. Add code to retrieve the information from the content.

    Until now we have not written any algorithm-specific code. Now we're about to retrieve the mining model content. This step is probably the most crucial for a successful viewer implementation. Note that the code will have to match the schema of the mining model in order to operate correctly. First add the following code to the class member variables.

    private const int NODE_DISTRIBUTION = 16;
    private const int VALUE_TYPE = 5;
    private const int ATTRIBUTE_NAME = 0;
    private const int ATTRIBUTE_VALUE = 1;
    
    private const string TARGET_ATTRIBUTE = "1";
    private const string Y_INTERSECT = "3";
    private const string COEFFICIENT = "7";
    
    
  4. Implement LoadViewerData.

    Next, paste the following code into LoadViewerData() after the comment.

    // Add code to extract information from the schema here.
    
    

    The first part of the code that enumerated the schema may be omitted, but it's included for debugging purposes and to show how to access the metadata of the rowset. During development it may be useful to look at the actual data in the schema produced by the server. This is made easy by using the built-in GridViewer that by default supports all mining algorithms.

    this.richTextBox.Text = string.Empty;
    
    // Enumerate the schema – commented out – uncomment
    // to print the scema to the richTextBox.
    DataTable schema = dataReader.GetSchemaTable();
    
    int columnCount = schema.Columns.Count;
    int rowCount = schema.Rows.Count;
    for (int i = 0; i < rowCount; i++)
    {
       DataRow row = schema.Rows[i];
       for (int j = 0; j < columnCount; j++)
       {
          string columnName = row[j].ToString();
    
          // Print the column name
    // this.richTextBox.Text += columnName;
       }
    }
    
    this.richTextBox.Text += "\n\n";
    this.richTextBox.Text += "Model output\n------------------------------\n";
    
    
    // Enumerate the data
    while(dataReader.Read())
    {
       try
       {
          columnCount = dataReader.FieldCount;
          for (int i = 0; i < columnCount; i++)
          {
             if (i == NODE_DISTRIBUTION)
             {
    string formula = this.ExtractRegressionFormulaFromDist(
    (OleDbDataReader)dataReader.GetValue(i), false);
                
    // The first node generates an empty string.
    if (formula != string.Empty)
                {
                   this.richTextBox.Text += formula + "\n";
                }
             }
    
             string dataTypeName = dataReader.GetDataTypeName(i);
             object dataValue  = dataReader.GetValue(i);
          }
       }
       catch (Exception e)
       {
          Console.WriteLine(e.Message);
       }
    }
    
    
  5. Implement ExtractRegressionFormulaFromDist.

    Finally add, to the end of the file, the function ExtractRegressionFormulaFromDist that is used above. The function enumerates the Distribution column and builds a string representing a pair-wise linear regression formula.

    //------------------------------------------------
    // Helper functions
    //------------------------------------------------
    private string ExtractRegressionFormulaFromDist(
    OleDbDataReader distReader,
       bool fCorrCoeff)
    {
       string targetAttribute = string.Empty;
       string regressionFormula = string.Empty;
       double val = 0.0;
       
       // Enumerate the data
       while (distReader.Read())
       {
          try
          {
             // Get the value type
             object dataValue  = distReader.GetValue(VALUE_TYPE);
             if( dataValue.ToString() == TARGET_ATTRIBUTE )
             {
                // Get the target attribute name
                dataValue = distReader.GetValue(ATTRIBUTE_NAME);
                targetAttribute = dataValue.ToString();
                regressionFormula = targetAttribute + " = " +
                   regressionFormula;
             }
             else if (dataValue.ToString() == Y_INTERSECT )
             {   
                dataValue = distReader.GetValue(ATTRIBUTE_NAME);
                string attName = dataValue.ToString();
                if( attName == targetAttribute )
                {
                   // This is the sample average of the 
    // attribute.
                   dataValue = distReader.GetValue(ATTRIBUTE_VALUE);
                   val = Convert.ToDouble( dataValue );
                   regressionFormula += val.ToString(".###");
                }
                else if( attName == "" )
                {
                   // Do nothing
                }
                else
                {
                   // Do nothing
                }
             }
             else if( dataValue.ToString() == COEFFICIENT )
             {
                dataValue = distReader.GetValue(ATTRIBUTE_VALUE);
                if( Convert.ToDouble( dataValue ) > 0 )
                   regressionFormula += " + ";
                else
                   regressionFormula += " ";
                val = Convert.ToDouble( dataValue );
                regressionFormula += val.ToString(".###");
                regressionFormula += " * ";
                dataValue = distReader.GetValue(ATTRIBUTE_NAME);
                regressionFormula += dataValue.ToString();
             }
             else
             {
                // Do nothing
             }
          }
    catch (Exception e)
          {
             regressionFormula = e.Message;
          }
       }
       regressionFormula = regressionFormula.Trim();
       return regressionFormula;   
    }
    
    
  6. Verify that the viewer works.

    You should now be able to build the project and use the new viewer. Don't forget that you will have to copy the Assembly to the location where the other shell files reside (typically %Program Files%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE). The BI Development Studio UI should look similar to the image below.

    ms345129.tutconpiv04(en-US,SQL.90).gif

    Figure 3. The BI Development Studio

Conclusion

Hopefully this article has given you a good jumping-off point for implementing a custom algorithm viewer for use in SQL Server Business Intelligence Development Studio Designer. Good luck and enjoy using Microsoft Analysis Services.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.