Setting Up Decision Support Objects (DSO) in Analysis Services

This topic describes how the DSO object model in SQL Server 2000 Analysis Services fits in the new Microsoft SQL Server Analysis Services architecture. This topic is intended for Analysis Services database administrators and OLAP developers with experience using SQL Server 2000 Analysis Services.

Because DSO applications were developed to administer SQL Server 2000 Analysis Services databases, those applications must be able to run on databases that are migrated to SQL Server Analysis Services. The DSO library is installed when SQL Server is installed; however server configuration is required in order to be able to use your earlier DSO applications.

Note

All new applications designed to administer SQL Server Analysis Services databases should be developed using the AMO object model. DSO is deprecated and might not be supported in future releases of SQL Server.

To run DSO applications from a client machine, you need to first install DSO. DSO can be installed as part of the Microsoft SQL Server Backward Compatibility Components, which is one of a set of redistributable packages included in SQL Server Feature Pack. To download the Microsoft SQL Server Backward Compatibility Components, go to Feature Pack for SQL Server.

Note

To install Microsoft SQL Server Backward Compatibility Components, you must have the following packages:

Microsoft Core XML Services (MSXML) 6.0

Microsoft SQL Server Native Client

Microsoft SQL Server Management Objects Collection

Working with DSO in SQL Server Analysis Services

DSO in SQL Server Analysis Services works by using a copy of the repository from SQL Server 2000 Analysis Services, as can be seen in the following illustration.

In SQL Server Analysis Services, the configuration properties for DSO point to the repository database that contains the metadata that is used by the DSO application. The repository that is pointed to is a working copy of the last active repository that was employed by the DSO application when it was running in SQL Server 2000 Analysis Services.

If several SQL Server 2000 Analysis Services databases from different servers must be centralized in one server running SQL Server Analysis Services, then there are multiple ways to set up DSO for those applications to run.

  • You can change the DSO properties individually to point to each repository, and then run the applications associated with the repository. This is the simplest way to run different DSO applications for different servers. The only drawback is that you cannot administer SQL Server 2000 Analysis Services databases at the same time if they were originally on different servers.

  • You can centralize databases in one SQL Server 2000 Analysis Services repository and configure DSO to point to a copy of this repository, and then run the applications whenever needed. This is a more complex approach because it requires centralization to be done beforehand on the SQL Server 2000 Analysis Services server. However, as soon as centralization is done, the DSO applications can be executed in parallel.

  • You can use a mixed technique from the first and second options, in which you have partially centralized repositories with alternating connection strings.

DSO Elements in the Analysis Services Configuration File

This section explains the different DSO-related elements in the SQL Server Analysis Services configuration file.

The configuration file can be edited either by opening the configuration file (msmdsrv.ini), usually found at C:\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Config, where n is a sequence installation number, or by opening the properties windows for an Analysis Services connection in SQL Server Management Studio.

Note

Connection strings in the configuration file are kept encrypted. If you change any connection string and save the file, then the next time that you open the file, the contents of the connection strings will be encrypted. To see the values of connection strings, use the properties window for an Analysis Services connection in SQL Server Management Studio.

DSO\RepositoryConnectionString

This is a standard OLEDB connection string that points to the SQL Server 2000 Analysis Services repository. If the repository is a Microsoft Access database (an .mdb file), then the path of the file is a local path.

RepositoryConnectionString is used by DSO when repository is locally available on the server. DSO checks the name of the server. If the name of the server is localhost or it is the name of the computer, then DSO uses RepositoryConnectionString, otherwise DSO uses RemoteRepositoryConnectionString..

DSO\RemoteRepositoryConnectionString

This is a standard OLEDB connection string that points to the SQL Server 2000 Analysis Services repository. If repository is a Microsoft Access database (an .mdb file), then the path of the file is a path of a shared folder.

RemoteRepositoryConnectionString is used by DSO when the repository is located on a remote server. DSO checks the name of the server. If the name of the server is localhost or it is the name of the computer, then DSO uses RepositoryConnectionString, otherwise DSO uses RemoteRepositoryConnectionString.

DSO\LocksDirectory

LocksDirectory is a folder where DSO keeps locking information. Locking information is created and deleted in this folder without any user intervention. Complete access to create, modify, and delete files is required in this folder for all DSO applications.

LocksDirectory contains the path of the folder.

DSO\RemoteLocksDirectory

RemoteLocksDirectory contains the path of a shared folder where locks are kept for DSO applications. See DSO\LocksDirectory for a description of a locks directory.

Note

You should enable security in the shared folder so that any DSO application can write, update, or delete the lock files.