Persist SQL Server Analysis Services Roles settings Solution Starter on CodePlex

A new solution starter that demonstrates how to persist SQL Server Analysis Services (SSAS) Roles settings in the Project Server OLAP database was released today on CodePlex.

For instance you might require to create specific Roles in your OLAP database if you have non Project Server users that need to access the database, for instance PerformancePoint users.

During the standard Cube Building process in Project Server 2007, any manually added OLAP Roles in an OLAP database are deleted. The Cube building process creates a default Role ProjectServerViewOlapDataRole that automatically adds all the Project Server users to this Role. This customization saves the configuration of the existing OLAP Roles (except for ProjectServerViewOlapDataRole) in a text file, before they are deleted but the standard cube building process, using the OnCubeBuilding event. The saved configuration (for the OLAP Roles) is then restored using the following event OnCubeProcessed.
The following configuration is saved, for each Role:

  • The list of Members
  • The database permissions of the Role
  • The dimensions permissions of the Role
  • The cubes permissions of the Role

206 2008-04-10, 14_18_59

This solution starter requires Project Server 2007, SQL Analysis Services 2005, and Visual Studio 2005.

Thank you to Steven Haden (WW EPM COE, MCS France) for building/testing and sharing this solution starter.

About CodePlex

CodePlex is Microsoft's open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on this site and provide feedback. A word about Microsoft’s role: Microsoft does not control, review, revise, endorse or distribute the third party projects on this site. Microsoft is hosting the CodePlex site solely as a web storage site as a service to the developer community.


Q: Is the tool supported?
A: There is no support in terms of CSS/PSS. We expect the support being a CodePlex community effort. Please note that the customization code uses standard supported web service calls available out of the box in EPM2007.
Q: Is the Project free?
A: Yes.
Q: Can I distribute the tool and the source code to customers and partners?
A: Customers and Partners can use both. Please point them to CodePlex as they have to agree on the license terms
Q: Can a partner distribute the tool and code as is?
A: No, but he can point his customer to the website to download it, so he makes sure that the customer agrees with the license terms.
Q: Can a customer install the customization and use it?
A: Yes the customer can, but he/she is responsible for testing it and running it.
Q: Will this tool be distributed in other ways (i.e. DVDs)?
A: No.
Q: What skills do I need to modify or change the tool?
A: C#, Project Server Interface, and a good understanding of the EPM 2007 data schema.
Q: I’m trying to modify the code and do have questions. Who do I ask?
A: Go to the Discussions forums on CodePlex.
Q: What are all the EPM projects released on CodePlex?
A. Check this: