Sql - search for column comments

Technical reference - an easy way to query the system tables to search for column comments.

select *

from sys.extended_properties

where class = 1 

  and major_id = object_id(N'<<schema>>.<<table>>') 

  and name = 'MS_Description'

Reporting Services 2008 over SSL - Certificates

(If you are trying to run RS over SSL and want to cut to the chase, skip this paragraph of frustration)

Disclaimer – I am not that savvy with administration – better at the nuts & bolts of programming, so most of this was learning.

Today I needed to experiment with the LogonUser method of the web service – the only way to call it is over SSL.  OK, easy enough, just tell Reporting Services to use SSL – just a check box, right?  Nope.  Do a little reading – all signs point to HttpCfg.exe.  Unless using Vista.  Then it’s netsh.exe.  Need a certificate – must generate one.  Use MakeCert.  OK, got all my parameters in check – generated.  What’s the hash?  Use certutil to get the hash (thumbprint), guid (app id).  Register it with netsh http add …..   ok, everything all lined up.

Now let’s go back into Reporting Services Configuration Manager and instruct it to use the certificate.  Piece of cake, select it from the drop down and fire it up.  Error?  Certificate is not trusted?  Oh good gracious.  Keep on researching, find out just how easy it is to do in XP.  Nothing about how easy it is to do in Vista, just lots of people complaining about how incredibly hard it is.

Oh, what’s this?  IIS 7 has an easy way to do it?  Oh YEAH!  Here are the steps to quickly create a test certificate you can use seamlessly:

1. Open IIS 7, click on the computer you wish to create the certificate for.  In the Content View double click on Server Certificates.

2.    2. The top right has an “Actions” menu.  Click on “Create Certificate Request” – could it really be this easy?

3. Type in a name for your certificate (yes, it really is this easy)

4.  Launch Reporting Services Configuration Manager, go to “Web Service URL”, select your newly created SSL certificate from the list and click apply.  It is just that easy!

And now you can do all of your development testing over SSL locally.

One big cube or multiple cubes?

Found a good article by a Chris Webb (SQL Server MVP) who knows quite a bit about analysis services.  This answers the question of "One cube or Multiple?".   Good reading for bigger more complex AS setups.


http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!7728.entry

Speeding up scalar functions in SQL Server

Our warehouse uses a UDF to check for dates outside of the acceptable range and assign them the designated “unknown dates”.  Why do it in SQL you ask (vs SSIS)?  Because it is an easy way to have control in one central place.  I made a change that pushed the date ranges from hardcoded in a function to table driven so it can be shared across multiple clients and allow them to retain their custom settings during deployments.  It was like someone hit the breaks – sure the queries were very light (7 rows in the table), but calling it several times in each ETL sproc blew it out to 20-30 million times in a single ETL job. Sure the cache hit rate was 100%, but it still added almost 50% to the total ETL time.  What to do… what to do…

I ran across a great article by Itzik Ben-Gan (http://www.sqlmag.com/article/sql-server/inline-scalar-functions.aspx) on how to make your functions perform extremely fast – by simply changing them from scalar to table.  He goes into the details of why it works – I will simply state the benchmarks:

This is calling the function as a scalar with the syntax of:

[DateField] = dbo.fn_CleanDate([column])

CPU	Reads	Writes	Duration
491,918	62,832,254	1	493,181,940

Those are some big numbers…  so if we rewrite the function so that it returns a table with a single field, the changes resulting:

[DateField] = (select value from dbo.fn_SelectCleandate([column]))

CPU	Reads	Writes	Duration
330,176	41,901,480	0	330,524,577

Now for the kicker.  If you select your date values higher up in the sproc and pass them into the function so there is no data access required in the sub-select it performs.  Blazing fast.  Yeah, it might clutter up the syntax a bit, but it is definitely worth it!

[DateField] = (select value from dbo.fn_SelectCleanDate([column], @minDate, @maxDate,@minUnknownDate, @maxUnknownDate)),

CPU	Reads	Writes	Duration
1,670	4,578	0	308

That deserves restating.  308 milliseconds vs. 493 thousand.

Execute SQL on linked server (pass-through)

So living with one foot in Oracle land is helping me learn some of the lesser-known features of SQL.  For my 2nd post in one day here is another thing I found too good to not spread; executing SQL on a linked server withough SQL interpreting anything (a.k.a. Pass-Through).  While tinkering with using openquery to insert and update, delete was just too darn slow and I knew there had to be a better way.  Turns out the execute function allows you to specify a linked server as a target.  Too cool...

execute ('delete from table_name1' ) at <<linkedServer>> execute ('delete from table_name2' ) at <<linkedServer>> execute ('delete from table_name3' ) at <<linkedServer>> execute ('delete from table_name4' ) at <<linkedServer>> execute ('delete from table_name5' ) at <<linkedServer>>

In the case above there were foreign key references between the tables - in my example I knew the relationships (thus the order) to clean out the table to avoid FK errors - but with a little probing of the tables in oracle via openquery I'm sure you could produce the necessary deletes in the correct order.


Another way to do it (non pass through) is the following - which I recommend only for selects, as updates/inserts/deletes will be evaluated SQL server side and take too long.

select *

from openquery(<<linkedserver>>, 'select * from table_name1')

Composite Foreign Keys

You learn something new every day...  Today I found a table that nobody could figure out - turns out it's sole purpose in life is to maintain foreign key relationships (via data) so the objects knew how to verify referential integrity before inserting.  After a little googling I found out that it is possible to have a composite foreign key.  Crazy I thought...  but it works.  If anyone has any (negative) experience in this area I would love to hear about it.

(Note I am now living with one foot in SQL Server land and the other in Oracle Land - hence the semicolons)

create table tmp1 (   f1  char(1),   f2  char(1),   primary key (     f1, f2) );   create table tmp2 (   f1  char(1),   f2  char(1)   , constraint FK_composite foreign key (f1, f2) references tmp1 (f1, f2) ); insert into tmp1 (f1, f2) values ('1', '1'); insert into tmp1 (f1, f2) values ('2', '2'); -- succeeds insert into tmp2 (f1, f2) values ('1', '1'); insert into tmp2 (f1, f2) values ('2', '2'); -- fails insert into tmp2 (f1, f2) values ('1', '2'); insert into tmp2 (f1, f2) values ('2', '1'); select * from tmp1; select * from tmp2; drop table tmp2; drop table tmp1;

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]