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]

How to query for SQL text in rdl stored in reportserver catalog table

Ever make a change to your database and wonder what reports are dependant upon
it?  If you use stored procedures / views in your reports you can query the
system catalogs.  But if you are using inline SQL you have to root through
the RDL's.  The report content is an image field which means you have to
convert it to varbinary (max) then to Xml.  From there you can easily look
for your SQL.  For different versions of Reporting services use the
corresponding namespace  (found at the top of the RDL file).

with xmlnamespaces    ( 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS REP ) select * from (   select c.Path,      c.Name,      DataSetXML.value('@Name','varchar(MAX)') DataSourceName,      DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)') CommandText   from     (select ItemID,             CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML      from dbo.Catalog     ) ReportXML    CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML (DataSetXML)   INNER JOIN dbo.Catalog c ON ReportXML.ItemID = c.ItemID ) tbl where CommandText like '%search criteria goes here...%'

Query system catalogs for foreign key constraints

While
putting together my latest warehouse (on a very tight deadline) I noticed that
some ETL jobs were taking way longer than expected. I thought I had disabled all
referential constraints, but while digging through (there were quite a few of
them - and that little FK window is darn near useless) I found one that was
still enforcing RI. Rather than dig through all the tables, why not just hit the
system catalogs? Ran across this nifty little script... this will show you all
constraints sorted by enabled / disabled.

select [status] =          case           when objectproperty(constid, 'CnstIsDisabled') = 0 then 'Enabled'           else 'Disabled'          end,       [constraint_name] = object_name(constid),       [table_name] = object_name(fKeyId),       [column_name] = col_name(fKeyId, fKey),       [referenced_table_name] = object_name(rKeyId),       [referenced_column_name] = col_name(rKeyId, rKey) from sysforeignkeys where objectproperty(constid, 'CnstIsDisabled') = 0 order by 1

Execute stored proc into a temp table

Ever want to select the results from a sproc into a temp table? Why you might
ask? Sometimes I do not have access to the source (encrypted), or it is on a
production system and I am not able to crack it open to run it bit by bit, and
have only the option of running the sproc plain vanilla. Here is the code to
capture it into a temp table (no need to worry about declaring the table). Only
catch is openrowset settings need to be enabled.

SELECT * INTO #tmp FROM OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;',                'EXEC [database].[schema].[stored proc]')

Welcome

Welcome to my blog. For years I have used my yahoo account to keep track of the little technical tid-bits I have picked up over the years, and after fumbling around through emails I have decided enough! So this is starting out as my personal repository of tricks and code memos. If you ever have a question about databases, specifically SQL Server, feel free to post a comment and ask - I will most certainly help you!