Understanding DSO in Analysis Services
To use Decision Support Objects (DSO) it is essential that you first understand the concepts behind migrating Microsoft SQL Server 2000 Analysis Services databases to SQL Server Analysis Services databases. When a user runs a DSO 8.0 application for SQL Server 2000 Analysis Services in SQL Server Analysis Services, whenever the application processes an object, that object is migrated from the SQL Server 2000 Analysis Services repository to a SQL Server Analysis Services database and processed. However, all other actions in the application that affect the repository have no impact in SQL Server Analysis Services database.
All users who have legacy applications that are running on SQL Server Analysis Services have to know how to set up DSO in SQL Server Analysis Services. This topic is intended for Analysis Services database administrators and OLAP developers with experience administering, developing, and using SQL Server 2000 Analysis Services. For more information, see Setting Up Decision Support Objects (DSO) in Analysis Services, and How to: Set Up DSO in Analysis Services.
DSO Conceptual Model
DSO 8.5 in SQL Server Analysis Services, formerly known as DSO9 in pre-release documentation, is an upgrade to the DSO libraries that were included with SQL Server 2000 Analysis Services and the service packs of that product. The new DSO files in the SQL Server release version have the version number 8.5.0.1054.
The following illustration shows the conceptual model of the new DSO 8.5, compared to DSO 8.0 in SQL Server 2000 Analysis Services.
The new model is composed of:
Metadata manager, which is the same object model that was provided with SQL Server 2000 Analysis Services service pack 4.
Process/Migration Manager, which replaces the Instance Manager in DSO 8.0.
As shown in the previous illustration, DSO user applications that connect to DSO 8.5 are connecting to the same DSO 8.0 interface. After establishing a connection to DSO, all DSO applications actions are directed towards the SQL Server 2000 Analysis Services repository database that was installed when you set up your SQL Server Analysis Services instance to work with DSO. Unless a process method, in the DSO application, is invoked, there is no interaction with data in the SQL Server Analysis Services database.
When a process method is invoked on any object, the Process/Migration Manager reads the SQL Server 2000 Analysis Services repository. After it reads the repository, the Process/Migration Manager uses the SQL Server Analysis Server Migration Object to migrate all objects that are necessary to process the object on which the process method was invoked. The Migration Object uses AMO 9.0 to access the SQL Server Analysis Services instance. The Migration Object creates the database, if it is required, and creates all other objects that are necessary to process the one on which the process method was invoked. As soon as all necessary objects are created, DS0 starts processing the objects by using their default processing option. DSO continues processing until it reaches the invoking object, which is processed by using the option requested in the method.
DSO Application Object Usage Effect
Object usage can be classified into three types:
Read operations, which look for the values of properties of the object.
Modification operations, which change the metadata in the repository.
Processing operations, which use the Process method of the object causing the service to process the object.
Note
In order for you to run the VBS script samples in the next sections, you must have a testing environment that includes databases from SQL Server 2000 Analysis Services that were migrated to SQL Server Analysis Services. The sample database FoodMart 2000 must be part of the SQL Server 2000 Analysis Services repository for some of the scripts to work. The testing environment must have at least one server that has SQL Server 2000 Analysis Services installed on it and one server that has SQL Server Analysis Services installed on it. DSO 8.5 must already be installed and must be set up on the server that is running SQL Server.
Reading an Object's Properties
All DSO read operations to any object's properties are resolved from the SQL Server 2000 Analysis Services repository and never use the SQL Server Analysis Services instance to obtain object properties.
A mixed environment in SQL Server Analysis Services with DSO enabled is defined as an environment in which there are SQL Server 2000 Analysis Services databases that have been migrated to SQL Server Analysis Services as well as other new databases that were created by using SQL Server Analysis Services. Then running a DSO application that lists all databases in a server will produce a list that contains all databases that are defined in the SQL Server 2000 Analysis Services repository, regardless of whether or not they have been migrated to SQL Server Analysis Services. However, the list will not contain any newly created SQL Server Analysis Services databases. You can try this by running the sample DSO_ListDB.vbs and comparing the produced log file against what you see in SQL Server Management Studio in SQL Server.
If repositories are the same on both the server that is running SQL Server and the server that is running SQL Server 2000, then the script produces the same results when it is run on both servers.
Creating, Modifying, or Dropping Objects
Using DSO to modify objects does not change them in SQL Server Analysis Services, it only changes them in the SQL Server 2000 Analysis Services repository. When your DSO application issues any command to change the structure of the existing objects in Analysis Services databases, that command connects to the SQL Server 2000 Analysis Services repository and changes the object in the repository, but the existing structure in the SQL Server Analysis Services instance is not modified.
For example, if you use DSO to create a new Sales cube in the FoodMart 2000 database and then read from the SQL Server 2000 Analysis Services repository, you will see the new cube there. However, when you review the existing objects in SQL Server Analysis Services by using SQL Server Management Studio, you will see no new Sales cube created, because nothing has been processed in DSO.
You can try this concept by first running the script DSOCreateNewSimplifiedSalesCube.vbs, then using SQL Server Management Studio to review the existing cubes in the FoodMart 2000 database on the SQL Server Analysis Services instance, You will not find the new Sales cube in it yet. However, if you read from the SQL Server 2000 Analysis Services repository running the DSOListDimensionsAndCubesProperties.vbs script, you will find that the new cube was created.
Processing Objects
The only moment that DSO 8.5 connects to SQL Server Analysis Services is when a process method from an object is invoked. At that time, the migration logic starts to work, reading the repository and using AMO to create objects in SQL Server Analysis Services. Next, the minimum required objects are processed, in order to process the object upon which the process method was invoked.
If you have been following the previous explanations and running the scripts, then by now you will have created the New Sales cube in the SQL Server 2000 Analysis Services repository, though it is not yet visible in SQL Server Analysis Services instance. If you have not followed the previous steps, you should go back to Creating, modifying or dropping objects and run the scripts described there.
Running the DSOProcessFull_NewSalesCube.vbs script executes a full process on the NewSales cube, which makes the NewSales cube visible to you in SQL Server Management Studio. If you use SQL Server Management Studio to look at the NewSales cube that was created by the script, it appears no different from any other cube that you created in SQL Server 2000 Analysis Services; it is just a regular cube with two shared dimensions. To understand how DSO is related to migration, drop the FoodMart 2000 database from SQL Server Analysis Services using SQL Server Management Studio and run again DSOProcessFull_NewSalesCube.vbs. After you run the script, you will find that all FoodMart 2000 has been migrated to SQL Server Analysis Services; only the customers and product dimensions, and the NewSales cube are processed. Now, compare the virtual cubes 'Warehouse and Sales' and 'Trained Cube' in SQL Server 2000 Analysis Services to the real cubes created in SQL Server Analysis Services.You can see that the cubes in SQL Server Analysis Services have linked measure groups defined rather than being virtual cubes, because virtual cubes no longer exist in SQL Server Analysis Services. Look for the location in SQL Server Analysis Services of the virtual dimensions 'Store Size in SQFT' and 'Store Type' from SQL Server 2000 Analysis Services. You will find that they were converted to hierarchies in the 'Store' dimension, to discover this you have either to process the 'Store' dimension using SQL Server Management Studio, or to script out the dimension as a create statement and review the XML for Analysis (XMLA) script.
As you have seen in the previous sample, every time that the process method of an object is invoked in a DSO application, the migration logic is used to create corresponding objects in SQL Server Analysis Services. For more information about the known considerations for migrating databases from SQL Server 2000 Analysis Services to SQL Server Analysis Services, see Breaking Changes to Analysis Services Features in SQL Server 2008 R2, Behavior Changes to Analysis Services Features in SQL Server 2008 R2, Discontinued Analysis Services Functionality in SQL Server 2008 R2, and Deprecated Analysis Services Functionality in SQL Server 2008 R2.
VBS Scripts
The following are the VBS scripts referenced in DSO application object usage effect.
DSO_ListDB.vbs
To run the script, you can save the script as DSO_ListDB.vbs file and, at a command prompt, run the following line in the same folder where the script was saved:
cscript DSO_ListDB.vbs <OLAP_ServerName>
The following code creates a log file that lists all Analysis Services databases in a SQL Server 2000 Analysis Services repository. The log file is named after the server name that is provided as argument.
Dim dsoServer, strServerName
Dim dsoDB, strDBName
Dim dsoCube, strCubeName
Dim dsoPartition, strPartitionState
Dim oArguments
Dim fsoOutput, fileOutput, strOutputFileName, strOutputLine
set oArguments = wscript.Arguments
if oArguments.Count < 1 then
msgbox "DSOListDB <Server Name>",,"DSO List DataBases: Sintaxis"
err.Raise 100,"DSOListDB", "Wrong Number of Arguments!."
End if
strServerName = oArguments(0)
strOutputFileName = "DSOListDB_" & strServerName & "_" & cstr(year(date)) & right("0" & cstr(month(date)),2) & right("0" & cstr(day(date)),2) & "_" & right("0" & cstr(hour(time)),2) & "_" & right("0" & cstr(minute(time)),2) & ".txt"
set dsoServer = CreateObject("DSO.Server")
set fsoOutput = CreateObject("Scripting.FileSystemObject")
Set fileOutput = fsoOutput.OpenTextFile(strOutputFileName, 8, True)
' Connect to the server.
dsoServer.Connect strServerName
fileOutput.WriteLine "Server Name: " & vbTab & dsoServer.Name
' List databases:
fileOutput.WriteLine "Database Info"
for each dsoDB in dsoServer.MDStores
fileOutput.WriteLine vbTab & "Database Name: " & dsoDB.Name
next' dsoDB
' Close connection to server.
dsoServer.CloseServer
DSOCreateNewSimplifiedSalesCube.vbs
The following code assumes that you are running it on the server that is running SQL Server Analysis Services, with DSO enabled, and with FoodMart 2000 in the SQL Server 2000 Analysis Services repository.
The following code creates a new, simplified sales cube in the FoodMart 2000 database without processing it. You can save the code in a file named DSOCreateNewSimplifiedSalesCube.vbs and run it directly from the icon in Windows Explorer.
Dim dsoServer
Dim dsoDB
' Initialize server.
Set dsoServer = CreateObject("DSO.Server")
dsoServer.Connect "localhost"
' Get to Work with FoodMart 2000 database
set dsoDB = dsoServer.MDStores("FoodMart 2000")
' Add the New Sales cubes, check for existence first
if dsoDB.MDStores.Find("NewSales") Then
dsoDB.MDStores.Remove("NewSales")
end if'dsoDB...Find
' Add the cube to the database.
Set dsoCube = dsoDB.MDStores.AddNew("NewSales")
dsoDB.Update
' Further define the cube.
' Set Description for new cube
dsoCube.Description = "simplified sales cube"
' Provide the data source for the cube.
dsoCube.DataSources.AddNew dsoDB.DataSources("FoodMart").Name
' Set the quoting characters from the datasource.
sLQuote = dsoCube.DataSources(1).OpenQuoteChar
sRQuote = dsoCube.DataSources(1).CloseQuoteChar
' Provide the fact table for the cube.
strSourceTable = sLQuote & "sales_fact_1997" & sRQuote
dsoCube.SourceTable = strSourceTable
' Set the estimated number of rows from the fact table
dsoCube.EstimatedRows = 86837
' Add the "product" dimension.
Set dsoProductCubeDim = dsoCube.Dimensions.AddNew("product")
' Add the "customer" dimension.
Set dsoCustomerCubeDim = dsoCube.Dimensions.AddNew("customers")
' Set the list of all tables used in this cube
' this list includes the fact table and the dimension tables
strFromClause = strSourceTable & ", " & dsoProductCubeDim.FromClause & ", " & dsoCustomerCubeDim.Fromclause
dsoCube.FromClause = strFromClause
' Define the joins between tables used by the cube.
' First, define the join between the fact table and the Customer table.
strJoinClause = "(" & strSourceTable & "." & sLQuote & "customer_id" & sRQuote & _
" = " & sLQuote & "customer" & sRQuote & "." & sLQuote & "customer_id" & sRQuote & ")"
' Define the join between the fact table and the Product table
strJoinClause = strJoinClause _
& " AND (" & strSourceTable & "." & sLQuote & "product_id" & sRQuote & _
" = " & sLQuote & "product" & sRQuote & "." & sLQuote & "product_id" & sRQuote & ")" _
& " AND (" & sLQuote & "product" & sRQuote & "." & sLQuote & "product_class_id" & sRQuote & _
" = " & sLQuote & "product_class" & sRQuote & "." & sLQuote & "product_class_id" & sRQuote & ")"
' Assign to .JoinClause
dsoCube.JoinClause = strJoinClause
Set dsoMeasure = dsoCube.Measures.AddNew("Store_Sales")
dsoMeasure.Description = "Store Sales"
dsoMeasure.SourceColumn = dsoCube.SourceTable & ".""Store_Sales"""
dsoMeasure.SourceColumnType = 3'ADODB adInteger
dsoMeasure.AggregateFunction = 0' aggSum
dsoMeasure.FormatString = "#,###"
' Update the repository.
dsoCube.Update
' Close connection to server.
dsoServer.CloseServer
msgbox "Done creating new simplified sales cube"
DSOListDimensionsAndCubesProperties.vbs
The following code lists all dimensions and all cubes in the database. For each of the dimensions, the list includes all the levels. For each cube, the list includes all dimensions, measures, and partitions in the cube. The list is saved in a log file named after the arguments provided by the server and database.
You can save the code in a file named DSOListDimensionsAndCubesProperties.vbs. At a command prompt, run the file in the same directory where it was saved, with the following syntax:
cscript DSOListDimensionsAndCubesProperties.vbs <ServerName | localhost> <DatabaseName | "FoodMart 2000">
The script code starts here:
Dim dsoServer, strServerName
Dim dsoDB, strDBName
Dim dsoCube, strCubeName
Dim dsoPartition, strPartitionState
Dim oArguments
Dim fsoOutput, fileOutput, strOutputFileName, strOutputLine
set oArguments = wscript.Arguments
if oArguments.Count < 2 then
msgbox "DSOListDimensionsAndCubeProperties <Server Name> <OLAP DB> ",," DSO List Dimensions And Cube Properties: Sintaxis"
err.Raise 100," DSOListDimensionsAndCubeProperties ", "Wrong Number of Arguments!."
End if
strServerName = oArguments(0)
strDBName = oArguments(1)
strOutputFileName = "DSOListDimensionsAndCubesProperties_" & strServerName & "_" & strDBName & "_" & cstr(year(date)) & right("0" & cstr(month(date)),2) & right("0" & cstr(day(date)),2) & "_" & right("0" & cstr(hour(time)),2) & "_" & right("0" & cstr(minute(time)),2) & ".txt"
set dsoServer = CreateObject("DSO.Server")
set fsoOutput = CreateObject("Scripting.FileSystemObject")
Set fileOutput = fsoOutput.OpenTextFile(strOutputFileName, 8, True)
' Connect to the server.
dsoServer.Connect strServerName
' Set up the MDStore objects:
' database, cube, and partition.
Set dsoDB = dsoServer.MDStores.Item(strDBName)
fileOutput.WriteLine "Server Name: " & vbTab & dsoServer.Name
fileOutput.WriteLine "Database Name: " & vbTab & dsoDB.Name
fileOutput.WriteLine
fileOutput.WriteLine
fileOutput.WriteLine "Dimensions Info"
fileOutput.WriteLine
for each dsoDimension in dsoDB.Dimensions
fileOutput.WriteLine "Dimension: " & dsoDimension.Name
for each dsoLevel in dsoDimension.Levels
fileOutput.WriteLine vbTab &"Level: " & vbTab & dsoLevel.Name
next' dsoLevel
fileOutput.WriteLine
fileOutput.WriteLine
next' dsoDimension
fileOutput.WriteLine "Cubes Summary"
fileOutput.WriteLine
for each dsoCube in dsoDB.MDStores
fileOutput.WriteLine "Cube: " & dsoCube.Name
fileOutput.WriteLine vbTab & "# of Dimensions: " & cstr( dsoCube.Dimensions.count)
fileOutput.WriteLine vbTab & "# of Measures: " & cstr( dsoCube.Measures.count)
fileOutput.WriteLine vbTab & "# of Partitions: " & cstr( dsoCube.MDStores.count)
fileOutput.WriteLine
fileOutput.WriteLine
next' dsoCube
fileOutput.WriteLine "Cubes Info"
fileOutput.WriteLine
for each dsoCube in dsoDB.MDStores
fileOutput.WriteLine "Cube: " & dsoCube.Name
for each dsoDimension in dsoCube.Dimensions
fileOutput.WriteLine vbTab & "Dimension: " & dsoDimension.Name
for each dsoLevel in dsoDimension.Levels
fileOutput.WriteLine vbTab & vbTab &"Level: " & vbTab & dsoLevel.Name
next' dsoLevel
fileOutput.WriteLine
fileOutput.WriteLine
next' dsoDimension
for each dsoMeasure in dsoCube.Measures
fileOutput.WriteLine vbTab &"Measure: " & vbTab & dsoMeasure.Name
next' dsoPartition
fileOutput.WriteLine
fileOutput.WriteLine
for each dsoPartition in dsoCube.MDStores
fileOutput.WriteLine vbTab &"Partition: " & vbTab & dsoPartition.Name
next' dsoPartition
fileOutput.WriteLine
fileOutput.WriteLine
next' dsoCube
' Close connection to server.
dsoServer.CloseServer
DSOProcessFull_NewSalesCube.vbs
The following code sample fully processes the NewSales cube in the FoodMart 2000 database. You can save the code in a file named DSOProcessFull_NewSalesCube.vbs and run it directly from the icon in Windows Explorer.
The code assumes that you are running it on the server that is running SQL Server Analysis Services, with DSO enabled, and FoodMart 2000 in the SQL Server 2000 Analysis Services repository. The NewSales cube can be created by running DSOCreateNewSimplifiedSalesCube.vbs.
Dim dsoServer, strServerName
Dim dsoDB, strDBName
Dim dsoCube, strCubeName
Dim dsoPartition, strPartitionState
Dim oArguments
Dim fsoOutput, fileOutput, strOutputFileName, strOutputLine
strServerName = "localhost"
strDBName = "FoodMart 2000"
strCubeName = "NewSales"
' Setup log file
strOutputFileName = "DSOProcessFull_NewSalesCube_" & strServerName & "_" & strDBName & "_" & cstr(year(date)) & right("0" & cstr(month(date)),2) & right("0" & cstr(day(date)),2) & "_" & right("0" & cstr(hour(time)),2) & "_" & right("0" & cstr(minute(time)),2) & ".txt"
set fsoOutput = CreateObject("Scripting.FileSystemObject")
Set fileOutput = fsoOutput.OpenTextFile(strOutputFileName, 8, True)
fileOutput.WriteLine "Starting Job @ " & cstr(Time)
' Connect to the server.
set dsoServer = CreateObject("DSO.Server")
dsoServer.Connect strServerName
' Set up the MDStore objects:
' database, cube, and partition.
Set dsoDB = dsoServer.MDStores.Item(strDBName)
fileOutput.WriteLine "Server Name: " & vbTab & dsoServer.Name
fileOutput.WriteLine "Database Name: " & vbTab & dsoDB.Name
fileOutput.WriteLine
fileOutput.WriteLine
Set dsoCube = dsoDB.MDStores( strCubeName)
fileOutput.WriteLine "Starting to process @" & cstr(Time) & ": " & strCubeName
dsoCube.Process 1' processFull
fileOutput.WriteLine "Finished processing @" & cstr(Time) & ": " & strCubeName
msgbox "New Sales cube processed!"
See Also