Customizing Analysis Services Cube Using Analysis Management Objects (AMO)

Christophe Fiessinger's Blog

The latest news you need to know about Microsoft Enterprise Social & Project Portfolio Management

Customizing Analysis Services Cube Using Analysis Management Objects (AMO)

  • Comments 1

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

 

Attachment: EPM2007 - Customizing AS Cubes Using AMO.zip
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • Chris,

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

    Dinesh

Page 1 of 1 (1 items)