Configuring Analysis Services 2005 for AX

For use Analysis Services 2005 with Dynamics AX, we need to ensure that DSO (Decision Support Objects) is well configured. Take a look to the following post for more information (NOTE: this post refers to Project 2007 but it is applicable to AX):

http://www.pptspaces.com/msprojectreporterblog/Lists/Posts/Post.aspx?ID=25

Important steps:

1. Ensure you have installed the following components:
  - Microsoft SQL Server Native Client
  - Microsoft SQL Server 2005 Management Objects Collection
  - Microsoft SQL Server 2005 Backward Compatibility Components

2. Create the old style shared folder (MSOLAPRepository$), normally in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9 for example.

  - Share this folder as MSOLAPRepository$.
  - Se permissions for EveryOne or any OLAP users group as Change (they need permissions to modify folder's content).
  - Set Full control to SQLServer2005MSOLAPUser$SERVERNAME$MSSQLSERVER. This user can be located in the ServerName instance.

3. Copy msmdrep.mdb to this location (DSO9). You can get a copy of this file from http://www.pptspaces.com/msprojectreporterblog/Shared%20Files/msmdrep.zip

4. Configure data access connection and locks folders: (Open the Analysis Services's properties dialog using the SQL Server Mananagement studio and selection the option "Show Advance (All) properties")

  - Configure the DSO\LocksDirectory property with the correct path, ex. C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9

  - Configure the DSO\ RemoteLocksDirectory property as \\ServerName\MSOLAPRepository$\. Here is the tip different from the original post.

  - Configure the DSO\ RemoteRepositoryConnectionString selecting the Microsoft Jet 4.0 OLE DB Provider but setting the path as \\ServerName\MSOLAPRepository$\msmdrep.mdb. Here is another difference from the original post.

  - Configure the DSO\ RepositoryConnectionString property but in this case, use the full local path for the msmdrep.mdb file, ex. C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\DSO9\msmdrep.mdb

Thats all, try it, transfer and process some cubes.

NOTE: Remember define use'r permissions (for cubes) and administrator's permissions (for server/system).