7 Microsoft Clustering

Decision Support Objects (DSO) is a library of Component Object Model (COM) classes and interfaces that provide access to the functionality of the core Analysis Services engine insofar as the administrative tasks are concerned . The internal structure of the objects contained within Analysis Services is reflected in the object model exposed by DSO. This makes it easy to manage Analysis Services programmatically.

Installing DSO

To be able to access DSO from a computer where Analysis server is not installed, you may either install the client components of Analysis Services, which will automatically install the appropriate DLLs, or you may copy the following files to your computer:

  • ‚  Msmddo80.dll 1 ‚   The DSO library, version 8.0
  • ‚  Msmdso.rll ‚   The DSO resource file, version 8.0
  • ‚  Msmdnet.dll ‚   The Analysis Services network interface
  • ‚  Msmdlock.dll ‚   The Analysis Services lock manager 2

The DLLs are installed in the following location:


<drive>:\Program Files\Common Files\Microsoft Shared\DSO

The resource file, Msmdso.rll, does not need to be registered and is installed by default in the following location:


<drive>:\Program Files\Common Files\Microsoft Shared\DSO\Resources\1033

To register the DLL files, you should use Regsvr32.exe or use the DLLSelfRegister functions of the DLL files. Additionally, registry entries for each file should be made under the following registry key:

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion \SharedDLLs 

As we saw in the Analysis Manager in 3 ‚  Chapters 5 ‚  and ‚  6 ‚  , the objects in Analysis Services are organized in a hierarchy. First we have a server that contains connections, cubes, shared dimensions, and data-mining models. The cubes contain partitions and the data-mining model contains columns , data sources, and roles. Figure 9-1 shows this hierarchy.


Figure 9-1. DSO object model.

Scripting vs. Visual Basic 4

As I describe the functionality of each object, I'll provide code samples to illustrate their use in real applications. The following examples use Microsoft Visual Basic 6; you could also use Microsoft Visual C++. However, it's very probable that someone might use this technology on the Web or with Microsoft Windows Scripting Host, so the question is, can you use VBScript (or any other scripting language, for that matter) with DSO? The answer is yes, but you shouldn't. The reason for this relates to the way DSO was built. Each object in DSO exposes much of the functionality of the collection of its subobjects in the hierarchy through an interface named MDStores. In other words, if you want to scroll through the collection of databases in a Dso.Server object, you would find them in the collection, not of databases, but of MDStores. Each MDStore of the Server object happens to be a database object. Each database can contain a certain number of cubes, which are also contained within the MDStores interface. Each MDStore of the Database object is a cube.


Note

The mining models, as we'll soon see, are not represented by an MDStores collection because they are at the same level as the cubes in the hierarchy.


To be able to take advantage of these MDStores interfaces, the language must be able to create object data types so that you can write the following:

 Dim dsoServer as Dso.Server Set dsoServer = new Dso.Server 

The dsoServer object needs to be dimensioned as the object type for there to be a dsoServer.MDStores. If you were to use a scripting language, there is an undocumented Databases collection in DSO that can also be used as I'll show you here in plain VBScript (using Windows Scripting Host):

 Dim dsoServer Dim db Set dsoServer = wscript.CreateObject("dso.server") DsoServer.Connect "dataserver" For each db in dsoServer.Databases()     Wscript.echo db.name Next DsoServer.Close 

I have run into very few, if any, problems proceeding in this way. However, according to Microsoft, these undocumented collections are not supported and may or may not exist in future versions of DSO. The lack of support should be discouragement enough from using DSO directly from a scripting language. 5

If you wish to use DSO through script, I recommend that you create your own ActiveX DLL, which serves as a wrapper for the DSO component. This DLL is the one you can safely use in a scripting environment.

The Server Object

The Server object in the DSO hierarchy represents the root object in the model tree and handles the functionality related to the Analysis server. The Server object uses the Server interface, with a ClassType property of clsServer.

The Server object is used to

  • Connect to and disconnect from an Analysis server.
  • Start, pause, and stop the Analysis server service (MSSQLServerOLAPService) provider.
  • Provide detailed information, such as the version and edition, of an Analysis server. 6
  • Create other Service objects, such as databases, data sources, commands, dimensions, cubes, data-mining models, and roles.
  • Manage object locking in Analysis Services, controlling read/write access in a multiple user situation.
  • Provide access to DSO Database objects using the MDStores collection.

To connect to a server, just create an instance of the server like this:

 Dim dsoServer As New DSO.Server 

Then call the Connect method and supply the name of the Analysis server you wish to connect to:

 dsoServer.Connect "myServerName"   7   

Managing the service status of the Analysis server is only slightly more complex than most other interactions with other components of DSO only because of its need to communicate through the service management component of the Win32 API, which utilizes many cryptic-looking values that are simplified using the following constants:

 ' Constants regarding the current state of the service Const OLAP_SERVICE_ON = &H4 Const OLAP_SERVICE_PAUSED = &H7 Const OLAP_SERVICE_STOPPED = &H1 ' Return error constants Const SERVICE_CONTINUE_PENDING = &H5 Const SERVICE_PAUSE_PENDING = &H6 Const SERVICE_PAUSED = &H7 Const SERVICE_ON = &H4 Const SERVICE_START_PENDING = &H2 Const SERVICE_STOP_PENDING = &H3 Const SERVICE_STOPPED = &H1 Const SERVICE_ACCEPT_PAUSE_CONTINUE = &H2 Const SERVICE_ACCEPT_SHUTDOWN = &H4 Const SERVICE_ACCEPT_STOP = &H1 Const SERVICE_ACTIVE = &H1 Const SERVICE_CHANGE_CONFIG = &H2 Const SERVICE_CONTROL_CONTINUE = &H3 Const SERVICE_CONTROL_INTERROGATE = &H4 Const SERVICE_CONTROL_PAUSE = &H2 Const SERVICE_CONTROL_SHUTDOWN = &H5 Const SERVICE_CONTROL_STOP = &H1 Const SERVICE_ENUMERATE_DEPENDENTS = &H8 Const SERVICE_INACTIVE = &H2 Const SERVICE_INTERROGATE = &H80 Const SERVICE_NO_CHANGE = &HFFFF Const SERVICE_PAUSE_CONTINUE = &H40 Const SERVICE_QUERY_CONFIG = &H1 Const SERVICE_QUERY_STATUS = &H4 Const SERVICE_STATE_ALL = (SERVICE_ACTIVE or SERVICE_INACTIVE) Const SERVICE_USER_DEFINED_CONTROL = &H100 Const SERVICE_WAIT_MAX_SECONDS As Integer = 45 Friend Function AnalysisServiceManager(oServer As Object, _     ByVal iCmd As Integer, _     ByRef lngRunStatus As Long, _     ByRef lngError As Long) As Boolean Dim bReturnValue     As Boolean Dim lngServerStatus  As Long Dim lngControlCmd    As Long lngRunStatus = oServer.ServiceState bReturnValue = False lngControlCmd = icmd lngError = 0 On Error GoTo ManageError Select Case icmd     ' Run this when the request is to start the service.     Case SERVICE_ON     ' The status of the service must be checked in case     ' it's either running already or in the process of      ' starting.         Select Case lngRunStatus             ' If it's already running, then do nothing and             ' act as though it was started successfully and             ' return true.             Case SERVICE_ON                 bReturnValue = True             ' If the service is not running, then changing             ' the state of the service control property will change             ' the behavior of the service. In this case, changing             ' the state to a running state (SERVICE_ON) will              ' make the service attempt to start. Failure to do so             ' will invoke the error manager automatically.             Case SERVICE_PAUSED, SERVICE_STOPPED                 oServer.ServiceState = lngControlCmd                 bReturnValue = True         End Select     ' A request is being made to pause the server.     Case SERVICE_PAUSED             Select Case lngRunStatus             ' Again, if the service is already paused, there's             ' no need to make a fuss K             Case SERVICE_PAUSED                 bReturnValue = True             ' If the service is running, it gets paused             ' by simply changing the value of the state.             Case SERVICE_ON                 oServer.ServiceState = lngControlCmd                 bReturnValue = True             ' Trying to pause a stopped service, on the              ' other hand, is a problem. The user needs              ' to know that the attempt failed, so the              ' return value is false.             Case SERVICE_STOPPED                 bReturnValue = False         End Select     ' A request is made to stop a running server.     Case SERVICE_STOPPED              Select Case lngRunStatus             Case SERVICE_STOPPED                 bReturnValue = True             Case SERVICE_ON                 oServer.ServiceState = lngControlCmd                 bReturnValue = True             ' Stopping a paused server is a problem             ' and the caller needs to be notified via             ' a FALSE return value.             Case SERVICE_PAUSED                 bReturnValue = False         End Select End Select ' lngStatus holds the value of the current state of the  ' service. Since this is a variable passed by reference, ' the value will update the variable that was declared ' in the calling program. lngStatus = oServer.ServiceState OlapServiceControl = bReturnValue Exit Function ManageError: ' Manage any error caught by the server object, ' which generally will be due to making requests ' that are incompatible with the service's ability ' or from some server configuration error such as an ' invalid service login. ' Also can occur if the state change fails to take  ' effect within 60 seconds lngStatus = oServer.ServiceState lngError = Err.Number OlapServiceControl = False End Function 

The code would get called as follows :

 Dim dsoServer As New DSO.Server Dim lSuccess As Boolean ' Contains the return status of the function Dim lngStatus as Long ' Sent by reference and contains the status of                       ' the service after the function runs Dim lngErr as Long    ' Contains any error values that may have                        ' resulted. If the function returns FALSE,                       ' this variable may contain the error number                       ' needed to obtain a more complete description                       ' of the problem. dsoServer.Connect "dataserver" ' This is the function call lSuccess = AnalysisSrvcMgr(dsoServer, SERVICE_ON, lngStatus, lngErr) 

This code sample shows how to display the properties of a server:

 Public Sub displayProperties(oServer As DSO.Server)          ' For illustration sake, we'll show the output going to the     ' debug window.     With oServer         Debug.Print "Name:               " & .Name         Debug.Print "Description:        " & .Description         Debug.Print "Connection Timeout: " & .ConnectTimeout         Debug.Print "LockTimeout:        " & .LockTimeout         Debug.Print "Edition:            " & .Edition         Debug.Print "Version:            " & .Version         Debug.Print "Log File:           " & .ProcessingLogFileName     End With      End Sub 

Here is a code sample showing how to create the database: 8

 Public Sub CreateDatabase()     Dim dsoServer As New DSO.Server     Dim dsoDataBase As DSO.MDStore     Dim strDataBaseName As String     Dim strDescription As String     ' You need to define name of the Analysis server     ' where this will occur.     dsoServer.Connect "dataserver"          ' The name of the database is mandatory ...     strDataBaseName = Me.txtDbName.Text     ' ... but the description is optional     strDescription = "Database Created from DSO Code"     ' Check whether that database name has already been used.     ' The server has a collection which contains all the     ' databases that exist within. By scrolling through     ' the server's MDStores interface, you can see whether     ' the proposed name is in use already.     If dsoServer.MDStores.Find(strDataBaseName) Then         MsgBox "There is a database called " & strDataBaseName & _             ". Please try another name."         Exit Sub     End If     ' Simply by adding the new database to the collection     ' you've caused DSO to create the physical database     ' on the server.     Set dsoDataBase = dsoServer.MDStores.AddNew(strDataBaseName)     ' This is where the description gets set. If the string     ' is empty, it won't do anything.     dsoDataBase.Description = strDescription          ' The information you provided gets committed through     ' this update command.     dsoDataBase.Update      End Sub 

Before any other object can be created in a database, a connection to a data source needs to be made as is the case here, when we would like to connect to our mushrooms database:

 Private Sub AddDataSource()     Dim dsoServer As New DSO.Server     Dim dsoDB As DSO.MDStore     Dim dsoDS As DSO.DataSource     Dim strDBName As String     Dim strDSName As String     Dim strDSConnect As String     ' Initialize variables for the database name,     ' data source name, and the ConnectionString property     ' for the data source.     strDBName = "Mushrooms"     strDSName = "dataserver"     strDSConnect = "Provider=MSDASQL.1;User ID=sa;" & _         "Data Source=FoodMart;Connect Timeout=15"     ' Create a connection to the Analysis server.     dsoServer.Connect "dataserver"     ' Locate the database first.     If dsoServer.MDStores.Find(strDBName) Then         Set dsoDB = dsoServer.MDStores(strDBName)         ' Check to see whether the data source already exists.         If dsoDB.DataSources.Find(strDSName) Then             MsgBox "Data source " & strDSName & _                 " already exists for database " & strDBName         Else             ' Create a new data source.             Set dsoDS = dsoDB.DataSources.AddNew(strDSName)             ' Add the ConnectionString properties.               dsoDS.ConnectionString = strDSConnect             ' Update the data source.             dsoDS.Update             ' Inform the user.             MsgBox "Data source " & strDSName & _                 " has been added to database " & strDBName         End If     Else         MsgBox strDBName & " is missing."     End If End Sub 

While parameters of the server are being changed you may need to ensure that you're able to get a lock on the server to implement the changes:

 Public Sub LockServer(oServer As DSO.Server, _     ByVal iLockType As Integer) ' The values for iLockType can be of the following types: ' ---- OlapLockExtendedRead ---- ' Serves as a guarantee that the server whose properties ' are being read won't change in either the object itself ' or any dependent objects. Applying this to a server will ' prevent any databases belonging to that server from being processed. ' Note that multiple users can gain this read lock, but that ' no processing can occur until all the locks are released. ' ---- OlapLockProcess ---- ' The object's Process method can be started, and other applications ' have read-only capabilities on that object until the lock is ' released. Only one olapLockProcess lock can be applied to an ' object at a time, and other applications can only apply ' olapLockRead locks while the olapLockProcess lock is in place. ' ---- OlapLockRead ---- ' Allows read-only access to the properties of that object. Others ' may place this lock concurrently as well, but no user can issue ' a write lock until all the read locks have been released. ' ---- OlapLockWrite ---- ' The properties of the object can be modified using the Update ' method. No other lock can be placed on this object until this ' lock is released. Dim strDescr As String ' The description is useful for other applications that may need to ' see this description to describe the lock that was placed. strDescr = "Lock placed for testing purposes" oServer.LockObject iLockType, strDescr End Sub 

To unlock an object or a series of objects, it's enough to call one of these methods :

 ' This unlocks the server lock only dsoServer.UnlockObject  ' Unlocks the server and any objects within ' the same object model. This returns a Boolean value if  ' it's successful. lRetval = dsoServer.UnlockAllObjects   9   

The Database Object

The Database object represents a database in Analysis Services. The database contains cubes and data-mining models, both of which are located at the same level. As mentioned previously, the database objects are accessed through the MDStores collection of the DSO Server object, but only the cubes can be accessed through this interface. As we'll see soon, you need to explicitly name the data-mining collection to access the data-mining models from the database.

The Database object is used to

  • Create, edit, and delete commands, data sources, cubes, dimensions, data-mining models, and roles applicable to a database in Analysis Services.
  • Manage transactions involving objects that belong to the database, such as cubes, dimensions, and mining models.
  • Provide access to events, using the Database interface, which is used to supply client applications with progress information on currently executing database tasks.

When reading the code that follows, note that although the data-mining model objects are the same regardless of the algorithm chosen , there are properties that are of use only in the case of a given algorithm as opposed to another. Tables 9-1 and 9-2 detail the uses of each of the properties according to the algorithm. 10

Table 9-1. Properties of the Data-Mining Model Object
14
Property OLAP Relational
CaseDimension Defines the case dimension used by the data mining model. Not used. 11
CaseLevel Defines the case level within the case dimension used by the data- mining model. A read-only property, it identifies the lowest level in the dimension whose data-mining model column has its IsDisabled property set to False. Not used.
Description Contains a user-friendly description of the data-mining model. 12 ‚  
FromClause Not used. Defines the case table, in the form of a FROM clause, used by the data-mining model.
JoinClause 13 Not used. Defines any supporting tables, in the form of a JOIN clause, used by the data-mining model.
MiningAlgorithm Defines the data-mining algorithm provider, such as Microsoft_ Decision_Trees or Microsoft_Clustering, used by both types of data-mining models. ‚  
SourceCube Defines the OLAP cube used by the data-mining model for training data. Not used.
SubClassType Is set to sbclsOlap when the MiningModel object is created. Is set to sbclsRelational when the MiningModel object is created. 15
TrainingQuery Defines the Multidimensional Expressions (MDX) query used to insert training data into the data-mining model. In most instances, this property is left blank; DSO will construct an appropriate training query if this property is not used. ‚  
Table 9-2. Data-Mining Column Properties
17 20
Property OLAP Relational 16
DataType Defines the expected data type of the data-mining column. ‚  
Description Contains a user-friendly description of the data-mining model column. ‚  
ContentType Should contain a value from the SUPPORTED_CONTENT_TYPES column of the MINING_SERVICES schema rowset. For example, if the column contained text data that corresponded to income ranges for customers, the ContentType property would be set to DISCRETE to reflect the discrete valuations of the data. If, on the other hand, the column contained actual salaries, the property would be set to either CONTINUOUS or DISCRETIZED, depending on the capabilities of the data-mining algorithm provider. ‚  
IsKey Not used. This property is read- only and is automatically set to True for the lowest enabled level in the case dimension specified in the CaseDimension property of the mining model. 18 Defines the key columns for the data-mining model. Set to True to specify a key column in the case set.
IsInput Defines the Input columns for the data-mining model. For a set of re- lated columns, changing the IsInput property for one of the columns automatically changes the property for the other related columns. ‚  
IsPredictable 19 Defines the predictable columns for the data-mining model. A column can have both IsInput and IsPredictable set to True. For a set of related columns, changing the IsPredictable property for one of the columns automatically changes the property for the other related columns. ‚  
IsDisabled Defines the columns to be used in analysis for the data-mining model. ‚  
Distribution This property is used to optimize the mining model by giving the mining-model algorithm some indication of the statistical nature of the data in the column. The values for this property should come from the SUPPORTED_DISTRIBUTION_FLAGS of the MINING_SERVICES schema rowset. ‚  
SourceOlapObject The value of this property is an object within the OLAP cube. For instance, this property might contain a DSO level object or a DSO member property object. Not used. 21
SourceColumn Not used. The value of this property is the fully qualified name of a field in the case or supporting table for the data-mining model.

DSO lets us create either a Relational data-mining model or an OLAP data-mining model. We'll look at both, starting with the relational model.

Creating the Relational Data-Mining Model Using DSO

This code sample demonstrates the use of Visual Basic to create a relational data-mining model using DSO. Notice how the DSO object model relies on the MDStores interface until it begins to access data-mining objects, in which case the MiningModel object is used. This code accomplishes exactly the same thing you do when you create a data-mining model using the wizards in Analysis Manager. The data source connections are established, the data-mining model is defined, and the model is finally trained with the data from the relational database. 22

 Public Sub newRelationalDMM()     Dim dsoServer As New DSO.Server     Dim dsoDataBase As DSO.MDStore     Dim dsoDataSource As DSO.DataSource     Dim dsoDMM As DSO.MiningModel     Dim dsoCol As DSO.Column     Dim dsoRole As DSO.Role     Dim strLQ As String, strRQ As String     Dim strModelname As String     Dim strFrom As String     Dim strSrcName As String            ' Constants used for DataType property     ' of the DSO.Column object.     ' Note that these constants are identical to     ' those used in ADO in the DataTypeEnum enumeration.     Const adInteger = 3     Const adWChar = 130         strSrcName = "dataserver"     strModelname = "MushroomsRDBMS"         ' Connect to the server on this computer.     dsoServer.Connect "dataserver"               Set dsoDB = dsoServer.MDStores("Mushrooms")         ' Retrieve the open and close quote characters for     ' the mushrooms data source.     strLQuote = dsoDB.DataSources(strSrcName).OpenQuoteChar     strRQuote = dsoDB.DataSources(strSrcName).CloseQuoteChar         ' The mushrooms table is the fact table for this     ' relational data-mining model; this variable will     ' make it easier to understand the code that     ' follows.     strFrom = strLQ & "mushrooms" & strRQ         ' Check for the existence of the model on this computer.            If Not dsoDB.MiningModels(strModelname) Is Nothing Then         ' If this model exists, delete it.         dsoDB.MiningModels.Remove strModelname     End If         ' Create a new relational mining model     ' called CustSalesModelRel.     Set dsoDMM = dsoDB.MiningModels.AddNew(strModelname, _         sbclsRelational)         ' Create a new ALL USERS mining model role.     ' In this way, everyone can use and query the model.         Set dsoRole = dsoDMM.Roles.AddNew("All Users")            With dsoDMM         .DataSources.AddNew strSrcName, sbclsRegular                      .Description = "Analysis of Mushroom edibility"                ' Set the case table for the model to the         ' Mushrooms table.                .FromClause = strFrom                ' Select the algorithm.                .MiningAlgorithm = "Microsoft_Decision_Trees"                ' Let DSO define the training query. This gets         ' filled automatically by DSO based on the data         ' source and the definition of the model.                .TrainingQuery = ""                ' Save the existing structure to the repository.                .Update     End With                  ' Create the Mushroom table's ID column as a key column.         Set dsoCol = dsoDMM.Columns.AddNew("ID", _         sbclsRegular)         ' Set the column properties for the new column.         With dsoCol                ' Set the source field from the case table for         ' the column.                .SourceColumn = strFrom & "." & strLQ & _             "id" & strRQ         .DataType = adInteger         .IsKey = True         .IsDisabled = False     End With                ' The next several columns are the attribute columns     ' used by the model to make predictions.          Set dsoCol = dsoDMM.Columns.AddNew("Cap Shape", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "cap_shape" & strRQ         .IsInput = True         .IsPredictable = False         .DataType = adWChar         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Cap Surface", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Cap_Surface" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Cap Color", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Cap_Color" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Bruises", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _            "Bruises" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Odor", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Odor" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Gill Attachment", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Gill_Attachment" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Gill Spacing", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Gill_Spacing" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Gill Size", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Gill_Size" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Gill Color", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Gill_Color" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Stalk Shape", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Stalk_Shape" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Stalk Root", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Stalk_Root" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Stalk Surface Above Ring", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Stalk_Surface_Above_Ring" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Stalk Surface Below Ring", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Stalk_Surface_Below_Ring" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Stalk Color Above Ring", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Stalk_Color_Above_Ring" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Stalk Color Below Ring", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Stalk_Color_Below_Ring" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Veil Type", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Veil_Type" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Veil Color", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Veil_Color" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Ring Number", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Ring_Number" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Ring Type", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Ring_Type" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Spore Print Color", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Spore_Print_Color" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Population", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Population" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         Set dsoCol = dsoDMM.Columns.AddNew("Habitat", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _             "Habitat" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = False         .IsDisabled = False     End With         ' This is the Predictable column. That way, our mushroom     ' users can use the model to find the value of this column.     Set dsoCol = dsoDMM.Columns.AddNew("Edibility", _         sbclsRegular)     With dsoCol         .ContentType = "DISCRETE"         .SourceColumn = strFrom & "." & strLQ & _            "Edibility" & strRQ         .DataType = adWChar         .IsInput = True         .IsPredictable = True         .IsDisabled = False     End With         ' Save the data-mining model.         With dsoDMM                ' The LastUpdated property of the data-mining model         ' needs to be set programmatically - it's not an         ' automatic function.                .LastUpdated = Now                ' Save the metadata of the data-mining model.                .Update            End With         ' Train (process) the data-mining model.         With dsoDMM                ' The model needs to be locked for processing to occur.                .LockObject olapLockProcess, _             "Processing mushroom mining model"                ' It's normal for this to take a while depending on the         ' number of cases in the data source and the number         ' of attributes in the model.                .Process processFull                ' Unlock the model after the processing is done.           .UnlockObject     End With            Set dsoRole = Nothing     Set dsoCol = Nothing     Set dsoDMM = Nothing         dsoServer.CloseServer         Set dsoServer = Nothing     End Sub 

Creating the OLAP Data-Mining Model Using DSO

This Visual Basic sample demonstrates the creation and training of a new data-mining model based on an OLAP data source. Initially, it's not much different from the previous example with the relational data source. The columns still must be defined and created in exactly the same way. This example uses the Sales cube in the FoodMart 2000 database.

 Public Sub newOlapDMM()     Dim dsoServer As New DSO.Server     Dim dsoDB As DSO.MDStore     Dim dsoDMM As DSO.MiningModel     Dim dsoCol As DSO.Column     Dim dsoRole As DSO.Role     ' Constants used for DataType property     ' of the DSO.Column object.     ' Notice that these constants are identical to     ' those used in ADO in the DataTypeEnum enumeration.     Const adInteger = 3     Const adWChar = 130         ' Connect to the server on this computer.     dsoServer.Connect "dataserver"         ' Select the FoodMart 2000 database.     Set dsoDB = dsoServer.MDStores("FoodMart 2000")         ' Check for the existence of the model on this computer.     If Not dsoDB.MiningModels("CustSalesModelOLAP") Is Nothing Then         ' If this model exists, delete it.         dsoDB.MiningModels.Remove "CustSalesModelOLAP"     End If         ' Create a new OLAP mining model     ' called CustSalesModelOLAP.     Set dsoDMM = dsoDB.MiningModels.AddNew("CustSalesModelOLAP", _         sbclsOlap)         ' Create a new mining model role called All Users.     Set dsoRole = dsoDMM.Roles.AddNew("All Users")         ' Set the needed properties for the new mining model.     With dsoDMM         .DataSources.AddNew "FoodMart", sbclsRegular         ' Set the description of the model.         .Description = "Analyzes the salaries " & _             "of customers"         ' Select the algorithm provider for the model.         .MiningAlgorithm = "Microsoft_Decision_Trees"         ' Set the source cube for the model to the Sales cube.         .SourceCube = "Sales"         ' Set the case dimension for the model to the         ' Customers shared dimension.         .CaseDimension = "Customers"         ' Let DSO define the training query.         .TrainingQuery = ""         ' Let DSO add the cube structure to the         ' data-mining model structure, automatically         ' creating needed data-mining model columns.         .Update     End With         ' Set the column properties pertinent to the new model.     ' Notice that when columns are automatically added to     ' the model in this fashion, they are disabled. You     ' must choose which columns are to be enabled     ' before you can process the model, and at least     ' one column must be enabled, or an error will result.         ' Enable the Name column. As this column is the     ' lowest enabled level on the Customers case dimension,     ' it becomes the case level for the data-mining model.     Set dsoCol = dsoDMM.Columns("Name")     dsoCol.IsDisabled = False         ' Enable the Gender column as an Input column.     Set dsoCol = dsoDMM.Columns("Gender")     dsoCol.IsInput = True     dsoCol.IsDisabled = False         ' Enable the Marital Status column as an Input column.     Set dsoCol = dsoDMM.Columns("Marital Status")     dsoCol.IsInput = True     dsoCol.IsDisabled = False         ' Enable the Education column as an Input column.     Set dsoCol = dsoDMM.Columns("Education")     dsoCol.IsInput = True     dsoCol.IsDisabled = False     ' Enable the Unit Sales column as a Predictable column.     Set dsoCol = dsoDMM.Columns("Yearly Income")     dsoCol.IsPredictable = True     dsoCol.IsDisabled = False         ' Save the data-mining model.     With dsoDMM         ' Set the LastUpdated property of the new mining model         ' to the present date and time.         .LastUpdated = Now         ' Save the model definition.         .Update     End With         ' Process the data-mining model.     With dsoDMM         ' Lock the mining model for processing.         .LockObject olapLockProcess, _             "Processing the data-mining model in sample code"         ' Fully process the new mining model.         ' This may take up to several minutes.         .Process processFull         ' Unlock the model after processing is complete.         .UnlockObject     End With         ' Clean up objects and close server connection.     Set dsoRole = Nothing     Set dsoCol = Nothing     Set dsoDMM = Nothing         dsoServer.CloseServer     Set dsoServer = Nothing     End Sub 

The DataSource Object

The DataSource object provides access to the data sources associated with a database, a cube, a partition, an aggregation, or a data-mining model source for case data in SQL Server 2000 Analysis Services, under the DataSources collection for each DSO object.

The DataSource object is used to 23

  • Retrieve data source-specific information such as connection strings and quote characters for use by client applications.
  • Determine various states, such as connection state, of the data source in Analysis Services.

Data-Mining Model (Decision Support Objects)

The MiningModel object provides support for data-mining models in Analysis Services. To access data-mining models, the MiningModels collection of the DSO Database object is used instead of the MDStores interface. The MiningModel object is used to

  • Provide access to data-mining columns for a Relational or OLAP data-mining model.
  • Construct and modify Relational or OLAP data-mining models.
  • Process a Relational or OLAP data-mining model. 24
  • Provide access to data-mining model roles.

Adding a New Data Source

 Private Sub newDataSource()     Dim dsoServer As New DSO.Server     Dim dsoDB As DSO.MDStore     Dim dsoDS As DSO.DataSource     Dim strDBName As String     Dim strDSName As String     Dim strDSConnect As String     ' Initialize variables for the database name,     ' data source name, and the ConnectionString property     ' for the data source.     strDBName = "DataMiner"     strDSName = "Dataserver"     strDSConnect = "Provider=MSDASQL.1;User ID=sa;" & _         "Data Source=dataserver;Connect Timeout=15"     ' Create a connection to the Analysis server.     dsoServer.Connect "dataserver"     ' Locate the database first.     If dsoServer.MDStores.Find(strDBName) Then         Set dsoDB = dsoServer.MDStores(strDBName)         ' Check to see whether the data source already exists.         If dsoDB.DataSources.Find(strDSName) Then             MsgBox "Data source " & strDSName & _                 " already exists for database " & strDBName         Else             ' Create a new data source.             Set dsoDS = dsoDB.DataSources.AddNew(strDSName)             ' Add the ConnectionString properties             dsoDS.ConnectionString = strDSConnect             ' Update the data source.             dsoDS.Update             ' Inform the user             MsgBox "Data source " & strDSName & _                 " has been added to database " & strDBName         End If     Else         MsgBox strDBName & " is missing."     End If End Sub 

A Word About the Repository and Metadata

You have surely noticed by now that some of the code has an Update method to apply changes made to the data-mining model. Where do these changes get stored? The answer for the data-mining model and all other objects in Analysis Services is the Microsoft Repository. The properties of the Analysis server objects, such as the names of the databases, the data sources, and the columns of the mining models are referred to as metadata, or data about data. Although a full description of the repository is well beyond the scope of this book, I think it's important to know that the Microsoft Repository is a specialized database designed to store metadata in such a way that it can be automatically retrieved and applied to objects when instantiated again.

The repository can be stored in either SQL Server's MSDB database, a Jet database, or even in a structured COM file. In the case of Analysis Services, it's stored in a Jet database in ..\OLAP Services\Bin\msmdrep.mdb. If you want, you can assign SQL Server to be the storage mechanism for the repository database by migrating it to a SQL Server. To do this, right-click on the Analysis server, and choose Migrate Repository from the menu. The wizard will then direct you to take the steps needed to migrate the repository. Because it requires that SQL Server always be running and available when working with Analysis Services, you really should refrain from migrating the repository to the SQL Server unless you expect to be managing 50 or more databases. In that case, SQL Server is a more robust and reliable storage facility for your metadata.

Analysis Server Roles 25

A database role is used to assign rights to objects in a single database while they are connected to it from a client application, and it can map to a Microsoft Windows NT or Windows 2000 user account or group . These apply only to client application use and not to administrative use.

Once the database roles are created, they are assigned to cubes or data-mining models. Each assignment grants access to the cube or mining model and creates a cube or mining model role with the same name as the database role. It's important to notice that the database roles apply only to that database, so for a different database, a new set of roles would need to be defined.

Data-Mining Model Roles

Database roles are first created and then assigned to the data-mining models. Each database role grants access to the model and automatically creates a mining model role with the same name as the database role. The database role provides default membership for the mining model role, but users can be added or deleted from the default membership of the mining model role. Any changes to role membership in a mining model role cause the database role of the same name to receive the same changes.

Adding a New Data-Mining Model Role

 Private Sub newMiningModelRole()     Dim dsoServer As New DSO.Server     Dim dsoDB As DSO.MDStore     Dim dsoDMM As DSO.MiningModel     Dim dsoRole As DSO.Role         ' Connect to the local server.         dsoServer.Connect "dataserver"     ' Connect to the Mushrooms database.         Set dsoDB = dsoServer.MDStores.Item("Mushrooms")         ' Connect to the Mushrooms RDBMS mining model.         Set dsoDMM = dsoDB.MiningModels("MushroomsRDBMS")         ' Because the mining model is about to be changed, lock the     ' mining model.         dsoDMM.LockObject olapLockProcess, "Adding Mining Model Role"         ' Create a new mining model role named MushroomRole, based on     ' the database role named MushroomRole.         Set dsoRole = dsoDMM.Roles.AddNew("MushroomRole", sbclsRegular)         ' All of the other properties are propagated, such as the     ' user list, from the TestRole database role.         ' Confirm the changes by saving them to the metadata repository.     dsoDMM.Update         ' Unlock the mining model.     dsoDMM.UnlockObject        End Sub 

Summary 26

Decision support objects were created to provide an interface for programmers for developing applications that perform administrative tasks with Analysis Services. This functionality is important for all of us who want to automate and schedule administrative tasks such as training data-mining models with new cases or reporting on the status of various data-mining objects. Since DSO covers every object available in Analysis Services, it can even be used to create an application similar to Analysis Manager.



Data Mining with Microsoft[r] SQL Server[tm] 2000 Technical Reference
Data Mining with Microsoft[r] SQL Server[tm] 2000 Technical Reference
ISBN: B007EMTPI0
EAN: N/A
Year: 2001
Pages: 16

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net