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)


THE CODE


References


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


            ProcessCubeCustomisation(e.DbName);


        }


 


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();


timeToProcess.Start();


bool success = false;


string msg = string.Empty;


 


try


{


    // ——————————————–


    // 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();


    asServer.Connect(connectionString);


 


    // ——————————————–


    // 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


    p12Database.Update(UpdateOptions.ExpandFull);


    p12Database.Process(ProcessType.ProcessFull);


 


    success = true;


}


catch (Exception e)


{


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


}


 


timeToProcess.Stop();


if (success)


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


else


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


  }


 


DEPLOYMENT


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


Event


4.      Enter the following information and click Save:



















Name


Contoso Task Name Hierarchy


Description


Custom AMO code that aggregates tasks by name


Assembly Name


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


Classe Name


MicrosoftFrance.MCS.P12.Events.ContosoAMO.CubeProcessedEvent


Order


1




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


CustomEvent



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:


Assembly



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>


TESTING


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:


PortfolioAnalyzer



Specifically:


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:




































Time


2007


Values


Row Labels


Actual Work


Work


Work the show


0


160


Boat Show


0


96


Consumer Technology Show


0


64


Grand Total


0


160



That’s it!


LESSON LEARNED


¾  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 AMO.zip

Comments (1)

  1. MSPLearner says:

    Chris,

    Is it possible to view Project Server cubes’ solution file within BIDS? Where does Project Server create and save SSAS definitions?

    Dinesh