How to: Set Up DSO in Analysis Services
The Decision Support Objects (DSO) library included with Microsoft SQL Server Analysis Services provides compatibility with SQL Server 2000 Analysis Services applications. The DSO library enables programs written for Analysis Services in SQL Server 2000 to work with Analysis Services in SQL Server.
DSO is installed by the typical SQL Server installation process when the Analysis Services option is selected. No special options are required.
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.
[!NOTA]
To install MicrosoftSQL 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
To be able to use DSO in SQL Server Analysis Services, you must do the following:
Have full access to a copy of the SQL Server 2000 Analysis Services repository for which the DSO application is being used. The repository must be an up-to-date copy. If DSO application creates the working database and all other objects then the repository could be any valid repository, even the sample repository that is included with SQL Server 2000 Analysis Services.
Edit the Configuration Settings for SQL Server Analysis Services so that the repository is configured to be used from DSO in SQL Server Analysis Services.
Migrate the database from SQL Server 2000 Analysis Services to SQL Server Analysis Services.
Obtaining Full Access to SQL Server 2000 Analysis Services Repository
The SQL Server 2000 Analysis Services repository can be either a Microsoft Access database (.mdb) or a SQL database (.sql). If the SQL Server 2000 Analysis Services repository is a Microsoft Access database, then it has to be copied to the server where SQL Server Analysis Services is running, as explained in the following procedure; if the SQL Server 2000 Analysis Services repository is a SQL database, see Editing Configuration Settings for SQL Server Analysis Services later in this topic.
To copy a Microsoft Access database repository
Locate the file Msmdrep.mdb in the existing program files (Bin) folder in SQL Server 2000 Analysis Services. The location of this folder depends on the installation path for the instance of SQL Server 2000 Analysis Services, but it is typically located as follows: C:\Program Files\Microsoft Analysis Services\ Bin
Be sure that no DSO applications, including Analysis Manager, are using the file before you copy the file.
If the Msmdrep.mdb file is open, the copy might fail or generate a corrupted file.
Copy Msmdrep.mdb in the existing program files (Bin) folder in SQL Server 2000 Analysis Services to the program files (Bin) folder for the instance of SQL Server Analysis Services to which you want to add DSO functionality.
Editing Configuration Settings for SQL Server Analysis Services
Change settings in the Analysis Services configuration file (Msmdsrv.ini) to enable DSO to work correctly in SQL Server Analysis Services.
To edit the Analysis Services configuration file
Open the Analysis Services configuration file (Msmdsrv.ini) in any text editor, such as Notepad. Depending on the installation path for SQL Server Analysis Services, this file is typically in the following location: C:\Program Files\Microsoft SQL Server\MSQL.n\OLAP\Config\Msmdsrv.ini, where n represents the installation sequence number.
Search for the <RepositoryConnectionString/> entry that is enclosed in <DSO> statement. Typically the structure will look like the following sample:
<DSO>
<RemoteRepositoryConnectionString/>
<RepositoryConnectionString/>
<RemoteLocksDirectory/>
<LocksDirectory/>
</DSO>
Add the appropriate connection string to the <ConfigSettings><DSO><RepositoryConnectionString/> entry. For example, for a .mdb file repository:
<RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program files\Microsoft SQL Server\MSSQL.n\OLAP\DSO\msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
For a .sql repository:
<RepositoryConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=AS2000_Repository;Data Source=sql-svr</RepositoryConnectionString>
Specify a value for the <ConfigSettings><DSO><LocksDirectory/> entry in the Msmdsrv.ini configuration file. For example:
<LocksDirectory>C:\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\DSO\LocksDirectory</LocksDirectory>
[!NOTA]
Parts of the file content are encrypted after the file is saved.
Enabling Remote Administration for SQL Server Analysis Services (Optionally)
This step is only necessary if you will use DSO to remotely administer your instance of SQL Server Analysis Services. If you plan to remotely manage the instance of Analysis Services, you have to create a share on the server pointing to the Bin folder (typically, C:\Program Files\Microsoft SQL Server\MSQL.n\OLAP\Bin). Follow these steps to create the share.
To create a share to enable remote administration using DSO
In Windows Explorer or My Computer, right-click the Bin folder and then click Properties.
In the Properties dialog box, on the Sharing tab, select Share this folder.
In the Share name box, type a name for the share, for example MSOLAPRepository$.
Click Permissions to specify share permissions for the shared folder.
.gif "Nota de seguridad") Nota de seguridad |
|---|
Opening a share creates an access risk. Provide full control option only to those users who will be administering the instance of Analysis Services. Other users should not have permission to access the share. |
After you create the share, change the Msmdsrv.ini configuration file to specify the remote connection string and lock directory values.
To change the configuration file to specify remote connection string
Add the appropriate connection string to the <ConfigSettings><DSO><RemoteRepositoryConnectionString/> entry in the Msmdsrv.ini configuration file. For example:
<RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\yourmachine\MsOLAPRepository$\msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
Specify a value for the <ConfigSettings><DSO><RemoteLocksDirectory/> entry in the Msmdsrv.ini configuration file. For example:
<RemoteLocksDirectory>\\yourmachine\MsOLAPRepository$</RemoteLocksDirectory>