Understanding DSO in Analysis Services

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.

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.

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 Changements essentiels apportés aux fonctionnalités Analysis Services dans SQL Server 2008 R2, Changements de comportement des fonctionnalités Analysis Services dans SQL Server 2008 R2, Fonctionnalités Analysis Services supprimées dans SQL Server 2008 R2, and Fonctionnalités d'Analysis Services déconseillées dans SQL Server 2008 R2.

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

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"

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

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!"