Customizing Analysis Services Cube Using Analysis Management Objects (AMO)

This post describes the steps required to perform SQL Analysis Services 2005 Project Server 2007 Cube customization using Analysis Management Objects (AMO).

(sample code below)



To be able to override an EPM 2007 Event Handler and do AMO customization you will have to add the following references to the C# Class Library solution:

¾ C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.dll

¾ C:\Program Files\Microsoft Office Servers\12.0\Bin\Microsoft.Office.Project.Server.Events.Receivers.dll

¾ C:\Program Files\Microsoft Office Servers\12.0\Bin\Microsoft.Office.Project.Server.Library.dll

Event Handler

The CubeProcecessedEvent is overridden to launch our Analysis Services Cube customization once the standard EPM 2007 cube has been built:

    // Override CBS event to call custom AMO code

    public class CubeProcessedEvent : CubeAdminEventReceiver


        #region Public Methods

        public override void OnCubeProcessed(PSContextInfo contextInfo, CubeAdminPostCubeProcessEventArgs e)


            base.OnCubeProcessed(contextInfo, e);

            // Process Analysis Services customization



Retrieving AS DB Connection Strings

// --------------------------------------------

// STEP 0: Retrieve Analysis Services server configuration from

// Microsoft.Office.Project.Server.Eventing.exe.config

// --------------------------------------------

string connectionString = ConfigurationManager.AppSettings["ASConnectionString"];

The CONFIG file is located under: C:\Program Files\Microsoft Office Servers\12.0\Bin

Analysis Services Cube Customization

This is the main method doing all the Analysis Services customization (using AMO):

private void ProcessCubeCustomisation(string databaseName)


Stopwatch timeToProcess = new Stopwatch();


bool success = false;

string msg = string.Empty;



    // --------------------------------------------

    // STEP 0: Retrieve Analysis Services server configuration from

    // Microsoft.Office.Project.Server.Eventing.exe.config

    // --------------------------------------------

    string connectionString = ConfigurationManager.AppSettings["ASConnectionString"];

    // --------------------------------------------

    // STEP 1: Connect to Analysis Services server.

    // --------------------------------------------

    Server asServer = new Server();


    // --------------------------------------------

    // STEP 2: Locate the necessary OLAP objects.

    // --------------------------------------------

    Database p12Database = asServer.Databases.GetByName(databaseName); // this throws exception if the database is not found

    Cube mspPortfolioAnalyzer = p12Database.Cubes.GetByName("MSP_Portfolio_Analyzer");

    Dimension taskListDimension = p12Database.Dimensions.GetByName("Task List"); // this throws exception if the dimension is not found

    // --------------------------------------------

    // STEP 3: Perform OLAP customisation

    // --------------------------------------------

    DimensionAttribute tnDimAtt = taskListDimension.Attributes.Add("Task Name");

    tnDimAtt.Usage = AttributeUsage.Regular;

    tnDimAtt.Type = AttributeType.Regular;

    tnDimAtt.OrderBy = OrderBy.Name;

    tnDimAtt.KeyColumns.Add(CreateDataItem(p12Database.DataSourceViews[0], "MSP_EpmTask_OlapView_Dimension", "TaskName"));

    tnDimAtt.NameColumn = CreateDataItem(p12Database.DataSourceViews[0], "MSP_EpmTask_OlapView_Dimension", "TaskName");

    // Create Task Name Hierarchy

    Hierarchy tnHierarchy = taskListDimension.Hierarchies.Add("TaskNameHierarchy");

    DimensionAttribute tlAttribute = taskListDimension.Attributes.GetByName("Task List attribute");

    tlAttribute.AttributeRelationships.Add("Task Name");

    tnHierarchy.Levels.Add(tnDimAtt.Name).SourceAttributeID = tnDimAtt.Name;

    tnHierarchy.Levels.Add(tlAttribute.Name).SourceAttributeID = tlAttribute.Name;

    ValidationErrorCollection errorCol = new ValidationErrorCollection();

    taskListDimension.Validate(errorCol, true);

    // --------------------------------------------

    // STEP 4: Process updates

    // --------------------------------------------

    // Process dimension update



    success = true;


catch (Exception e)


    msg = "FAILED to process AS customisation, exception: " + e.Message;



if (success)

    WriteEvent("SUCCESSFULY processed AS customisation, Time= " + timeToProcess.Elapsed.ToString(), EventLogEntryType.Information, 9999);


    WriteEvent(msg + " , Time= " + timeToProcess.Elapsed.ToString(), EventLogEntryType.Error, 9999);



Event Handler with Custom AMO Code

The procedure to deploy custom Event Handler (calling custom AMO code) is as follow:

Register Event Handler

1. Connect to the PWA server and go to Server Settings

2. Select Operational Policies -> Server-Side Event Handler Configuration

3. Select Cube Admin -> Cube Processed


4. Enter the following information and click Save:


Contoso Task Name Hierarchy


Custom AMO code that aggregates tasks by name

Assembly Name

MicrosoftFrance.MCS.EPM2007.Events.ContosoAMO, Version=, Culture=Neutral, PublicKeyToken=9af84715c1210e08

Classe Name




Once the assembly has been deployed it should appear as shown below (takes a few minutes to take effect).


Deploy Custom Assembly

1. Copy compiled assembly MicrosoftFrance.MCS.P12.Events.ContosoAMOEvent.dll from proper BIN directory to C:\WINDOWS\assembly (Drag & Drop)

2. Assembly should now appear in the folder as shown below:


Add Eventing Config Parameters

1. Copy the EPM Eventing config file located under C:\Program Files\Microsoft Office Servers\12.0\Bin\Microsoft.Office.Project.Server.Eventing.exe.config to have a backup and open the file using an XML editor

2. Add the following parameter: ASConnectionString

3. <configuration>

4.    <runtime>

5.       <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

6.          <probing privatePath="ProjectServerEventHandlers"/>

7.       </assemblyBinding>

8.    </runtime>

9.     <appSettings>

10.           <add key="ASConnectionString" value="CHRISFIE03\SQL" />

11.     </appSettings>

12. </configuration>


First you will have to the build the cube with the newly deployed customization.

The CBS (Cube Building Process) will look like this:

===== Initiating cube build process =====

[2/27/2007 8:45 AM] Cube build request message has been added to the Project Server queue

===== Verifying and running pre-build server event handler =====

[2/27/2007 8:46 AM] Verifying and running pre-build server event handler

===== Determining database and cube structure =====

[2/27/2007 8:46 AM] Cube build initialization started

[2/27/2007 8:46 AM] OLAP database and cube structure was determined successfully

===== Building database and cubes =====

[2/27/2007 8:46 AM] Cube build session started

[2/27/2007 8:46 AM] Analysis Services session started

[2/27/2007 8:47 AM] 26 of the NT accounts that correspond to users that have 'View Olap Data' permission could not be added to the Project Server default OLAP role 'ProjectServerViewOlapDataRole'

[2/27/2007 8:47 AM] Analysis Services session completed successfully

===== Verifying and running post-build server event handler =====

[2/27/2007 8:47 AM] Verifying and running post-build server event handler

===== Processing OLAP database =====

[2/27/2007 8:47 AM] Process OLAP database session started

[2/27/2007 8:47 AM] Analysis Services session started

[2/27/2007 8:49 AM] Analysis Services session completed successfully

===== Verifying and running post-process server event handler =====

[2/27/2007 8:49 AM] Verifying and running post-process server event handler

===== Process Completed =====

[2/27/2007 8:51 AM] Cube build request completed successfully.

Ensure that the newly added hierarchy has been added to the cube using Visual Studio:



New Hierarchy

Use Excel’s Pivot Table to validate change, for instance:

Work the show is a task that appears in both the Boat Show and Consumer Technology Show projects:




Row Labels

Actual Work


Work the show



Boat Show



Consumer Technology Show



Grand Total



That’s it!


¾ All EPM 2007 cube customization should be tested extensively prior to any deployment into a Production environment.

¾ Adding custom customization at the end of the Cube Building process will also increase the time it takes to refresh the cube.

¾ Figuring out the proper UpdateOptions & ProcessType could be tricky at times and depending on what option you choose it will have a impact on performance

¾ To figure out the order in which the AMO needs to be made, open the AS db using Visual Studio, do the changes by hand, script the full AS database and check the XML changes.

¾ Test, test, test J

EPM2007 - Customizing AS Cubes Using