Project Server 2007, Analysis Services 2008 and Building a Cube

May 28, 2009 Leave a comment

A long while ago I put together an article after having a hell of a time getting Project 2007 and SQL Analysis Services 2005 to play nicely together to build a cube, My original post can be found here . The original article got quite a few hits, so I assume that it is something that many people have looked for assistance with.

Now there looks to be an easier way, I am very happy to report that with SQL Server 2008 the process has become amazingly easier, although still a relatively lengthy process. The following Technet article let me get this working in under 30 minutes : . I recommend following this article to the letter, and then trying to build a cube. It worked for me after my own process from my original article did not. 🙂

I cannot recommend enough if you are thinking about going down the road of Project Server 2007 to use SQL Server 2008 rather than SQL Server 2005.

Advertisement

Filed under SQL Tagged with , , ,

Grooming and Operation Manager

December 16, 2008 Leave a comment

Sorry for the disgusting lack of posts recently, I have not really come across anything interesting to post about with Sharepoint or CRM, and I have been quite busy working with other products such as Data Protection Manager and Operations Manager.

So this update is just a quick one to point out a fantastic blog on Operations Manager by Jonathon Almquist : http://blogs.technet.com/jonathanalmquist/ 

Last night I had an issue with Ops Manager where no alerts were being generated, and attempting to resolve any of the alerts via the GUI were not working.  After a few minutes on Google I came across Jonathons site and got a few ideas on what my Ops Manager problem could be.  Using the power shell commands listed on his blog, I was able to immediately see that grooming was not working, and quite quickly I was able to get the server back to an operating state.  Thanks Jonathon!

For anyone curious the following command showed me just how many objects outside of the grooming threshold there were :

>$Threshold = (Get-Date).AddDays(-(get-defaultsetting)[42].Value).Date;Get-Alert | Where {$_.TimeResolved -and $_.TimeResolved.Date -lt $Threshold} | Measure-Object

Filed under System Center Tagged with ,

Project Server 2007, Analysis Services2005 and Building a Cube

November 12, 2008 Leave a comment

By far and away the most irritating long term issue I have had is in regards to building a cube with Project 2007 and Analysis Services 2005.  I was unable to find any documentation on what I had to do to get this working and had a client who really needed it working yesterday.  After weeks of puzzling over it I have finally worked through some problems and gotten it going!In retrospect it was really simple, but some basic knowledge was holding me back.  Essentially the problem was that AS2005 did not have the correct repository setup, and the entire answer was in this link : http://support.microsoft.com/default.aspx?scid=kb;EN-US;921116In particular the following step : On the computer that is running SQL Server 2005 Analysis Services, follow these steps:

Note This includes SQL Server 2005 Analysis Services that are running remotely in a 3-tier configuration.

  1. Create a subfolder that is named DSO9 in the following folder: C:Program FilesMicrosoft Sql ServerMssql.NumberOLAPNote Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
  2. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
  3. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
  4. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.
  5. The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.
    6. Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:
<DSO>


    <RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ComputerNameMSOLAPRepository$msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
    

    <RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\ComputerNameMSOLAPRepositorymce_markerlt;/RemoteLocksDirectory>
<LocksDirectory>C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9</LocksDirectory>
    
</DSO>
  6. Note The Msmdsrv.ini file is located in the PathConfig folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.
  7. The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the “ConnectionString” information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.
  8. Stop and then restart the SQL Server 2005 Analysis Services service.
  9. Create a subfolder that is named DSO9 in the following folder: C:Program FilesMicrosoft Sql ServerMssql.NumberOLAPNote Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
  10. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
  11. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
  12. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.
    13. Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:
<DSO>


    <RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ComputerNameMSOLAPRepository$msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
<RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\ComputerNameMSOLAPRepositorymce_markerlt;/RemoteLocksDirectory>
<LocksDirectory>C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9</LocksDirectory>
    
</DSO>
Note The Msmdsrv.ini file is located in the PathConfig folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the "ConnectionString" information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.
  13. Stop and then restart the SQL Server 2005 Analysis Services service.


So why did I ignore this for so long?  Purely because the file mentioned there does not exist in AS2005!  It is a legacy file from AS2000.  I do not know why it was not included, and I?m sure there are better ways to create a repository, but this method definitely works.

You can download a blank fresh copy of mdmdrep.mdb from http://www.msexperts.org/downloads/msmdrep.zip, follow these steps, and make sure that you give the SSP users and the SQL database appropriate permission to be able to access C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9. Please also ensure that you replace the sections in bold in the config file with the appropriate values, or you will continue to get errors.

I really wish I had found and tried this earlier.  Don?t ignore this because msmdrep.dll doesn’t exist, or it looks like it is unrelated due to the AS2000 references, it will definitely help.

Filed under Project Server Tagged with , , ,