Analysis Services Personalization Extensions

** Update 2010-11-02 - In order for personalization extensions to work, the assembly must be registered at the instance level (NOT at the database level).




     So my favorite feature of Analysis Services (this week) is peronalization extensions.  This is a very powerful feature that can have many different uses.  In a nutshell it allows you to write .net code that hooks into AS events - SessionOpen, SessionClose, CubeOpen and CubeClose.  Although there are an unlimited number of uses, I find it very handy in this scenario :
Imagine you have a cube that you deploy to xx number of clients.  The datamart it is built upon has user defined fields, say "UserDebit1", "UserDebit2" ... "UserDebitn".  Each client calls the fields whatever they want in the source app, and wants their own copy of the cube to have the name they choose show up in their browsing tool.  Choices are 1) have a different cube for each client (no way!), or use PE to dynamically expose out the named calculation.  Another very useful application of this is each client calculates something a little different - say "Revenue".  You can build in a member into your cube that is custom tailored to each client, with no one-off's.  The way to do it is:

• Include the UserDebitx measures in the cube.  Make them not visible
• Use PE to build a calculated member on either SessionOpen or CubeOpen event.  I chose CubeOpen as I don't want all sort of stuff going into cubes they don't open.
• On CubeClose event drop the calculated members.

Since the events are dynamic in nature, I chose to put the member definitions in a database - that way there is no need to redeploy the assembly when things change.  The code is as follows (I used the code found on CodePlex as the base)

using System; using System.Collections.Generic; using System.Text; using Microsoft.AnalysisServices.AdomdServer;  [assembly: CLSCompliant(true)] namespace PE.ASClientExtensions {     [PlugInAttribute]     public class ASClientExtension     {         [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1804:RemoveUnusedLocals", MessageId = "environment")]         public ASClientExtension()         {             Context.Server.SessionOpened += new EventHandler(this.SessionOpened);             Context.Server.SessionClosing += new EventHandler(this.SessionClosing);         }          [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2109:ReviewVisibleEventHandlers"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1804:RemoveUnusedLocals", MessageId = "session")]         public void SessionOpened(object sender, EventArgs e)         {             SessionMgr mgr = new SessionMgr();         }          [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2109:ReviewVisibleEventHandlers")]         public void SessionClosing(object sender, EventArgs e)         {             // Any Session close code goes here.         }     } }

And this is where the magic happens...

using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.AnalysisServices.AdomdServer; using System.Data; using System.Data.OleDb; using System.Xml; using System.IO; namespace PE.ASClientExtensions {     public class SessionMgr     {         private List<CalculatedMember> CalculatedMembers;         class CalculatedMember         {             private int _id;             private string _database = string.Empty;             private string _cube = string.Empty;             private string _dimension = string.Empty;             private string _attribute = string.Empty;             private string _definition = string.Empty;             public int Id { get { return _id; } }             public string Database { get { return _database; } }             public string Cube { get { return _cube; } }             public string Dimension { get { return _dimension; } }             public string Attribute { get { return _attribute; } }             public string Definition { get { return _definition; } }             public CalculatedMember(int Id, string Database, string Cube, string Dimension, string Attribute, string Definition)             {                 _id = Id;                 _database = Database;                 _cube = Cube;                 _dimension = Dimension;                 _attribute = Attribute;                 _definition = Definition;             }         }          private void LoadAll()         {             // Load connection string from global package configurations             //  This allows us to reuse the same pacakge configs as ssis             XmlDocument xml = new XmlDocument();             xml.Load(@"Package Configuration Path\Db.dtsConfig");             string connectionString = xml.SelectSingleNode("/DTSConfiguration[1]/Configuration[1]/ConfiguredValue[1]").InnerText;             System.Data.OleDb.OleDbConnectionStringBuilder ocon = new OleDbConnectionStringBuilder(connectionString);             using (OleDbConnection con = new OleDbConnection(ocon.ConnectionString))             {                 using (OleDbCommand cmd = new OleDbCommand("select * from dbo.ASCalculatedMember", con))                 {                     con.Open();                     OleDbDataReader reader = cmd.ExecuteReader();                     while (reader.Read())                     {                         CalculatedMembers.Add(                             new CalculatedMember(                                 Convert.ToInt32(reader["id"]),                                 reader["Database"].ToString(),                                 reader["Cube"].ToString(),                                 reader["Dimension"].ToString(),                                 reader["Attribute"].ToString(),                                 reader["Definition"].ToString()));                     }                 }                 con.Close();             }         }         public SessionMgr()         {             Context.CurrentConnection.CubeOpened += new EventHandler(CubeOpened);             Context.CurrentConnection.CubeClosing += new EventHandler(CubeClosing);             CalculatedMembers = new List<CalculatedMember>();             LoadAll();         }         [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2109:ReviewVisibleEventHandlers")]         public void CubeOpened(object sender, EventArgs e)         {             foreach (CalculatedMember _cm in CalculatedMembers)             {                 if (Context.CurrentDatabaseName.ToLower() == _cm.Database.ToLower() &&                     Context.CurrentCube.Name.ToLower() == _cm.Cube.ToLower())                 {                     // This will subscribe to the events.                     SessionMgr session = new SessionMgr();                     string command = string.Format("alter cube [{0}] create member [{1}].[{2}] as {3}",                                                     _cm.Cube, _cm.Dimension, _cm.Attribute, _cm.Definition);                     AdomdCommand cmd = new AdomdCommand(command);                     // Ideally check to make sure it exists.                     //  Also if a definition is incorrect, put it in a try/catch block                     //  so only the bad one fails.                     try                     {                         cmd.ExecuteNonQuery();                     }                     catch                     {                     }                     finally                     {                         cmd.Dispose();                     }                 }             }         }         [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2109:ReviewVisibleEventHandlers")]         public void CubeClosing(object sender, EventArgs e)         {             try             {                 foreach (CalculatedMember _cm in CalculatedMembers)                 {                     if (Context.CurrentCube.Name.ToLower() == _cm.Cube.ToLower())                     {                         // This will subscribe to the events.                         SessionMgr session = new SessionMgr();                         string command = string.Format("alter cube [{0}] drop [{1}].[{2}]",                                                         _cm.Cube, _cm.Dimension, _cm.Attribute);                         AdomdCommand cmd = new AdomdCommand(command);                         // Putting this inside of a try catch - when one connects to it via                         //  management studio it will open several connections for the session (metadata manager, etc.)                         try                         {                             cmd.ExecuteNonQuery();                         }                         catch                         {                         }                         finally                         {                             cmd.Dispose();                         }                     }                 }             }             catch (AdomdException ex)             {             }         }      } }

And a 3rd class to supress messages (following the example on codeplex)

// This file is used by Code Analysis to maintain SuppressMessage // attributes that are applied to this project. // Project-level suppressions either have no target or are given // a specific target and scoped to a namespace, type, member, etc. // // To add a suppression to this file, right-click the message in the // Error List, point to "Suppress Message(s)", and click // "In Project Suppression File". // You do not need to add suppressions to this file manually. [assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Aspe")]

And the place to store it...

CREATE TABLE [dbo].[ASCalculatedMember](       [Id] [int] IDENTITY(1,1) NOT NULL,       [Database] [varchar](100) NOT NULL,       [Cube] [varchar](100) NOT NULL,       [Dimension] [varchar](100) NOT NULL,       [Attribute] [varchar](100) NOT NULL,       [Definition] [varchar](max) NOT NULL,  CONSTRAINT [PK_ASCalculatedMember] PRIMARY KEY CLUSTERED (       [Id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]