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:
The DLLs are installed in the following location:
The resource file, Msmdso.rll, does not need to be registered and is installed by default in the following location:
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.
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
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
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 |
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 |
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
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
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.