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 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
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).
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:
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:
Specifically:
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
96
Consumer Technology Show
64
Grand Total
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
Chris,
Is it possible to view Project Server cubes' solution file within BIDS? Where does Project Server create and save SSAS definitions?
Dinesh