Analysis Services Stored Procedure Best Practices

Whilst answering questions on the Microsoft Analysis Services forum the other day, I came across a question regarding recursive MDX. At first read I thought that an Analysis Services stored procedure might be quicker than recursive MDX, which was an incorrect assumption.

However, in the discussion Darren Gosbell posted a list of best practices with regards Analysis Services stored procedures that I hadn't seen before, so I figured I would post them here too.

So, the "best practices" for Analysis Services stored procedures (ASSP) are apparently as follows:

• for administrative functions
• for static sets, scopes, LHS of assignment and other early-binding things
• It is usually OK to use sprocs for query axes
• It is usually OK to use sprocs if they don’t need to get cell data (but only look at metadata and members)
• inside calculations if there is good NEB that can be defined on the calculation
• inside calculations without NEB if sproc is executed over small amount of cells, i.e. inside WHERE clause etc.

The thread where you can see all of this is here: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9feb1ec0-d5f4-4b8c-9d7c-0195d25d7adc

SQL Server Dynamic SQL

The use of dynamic SQL is unfortunately one of those necessary evils that plagues developers and DBAs alike.

Now I know that the use of the word "necessary" will irk some people but after developing BI systems for about 9 years I can assure you that it is. Sure, there are alternatives in a lot of cases, which I have learnt over time, but even so there are occasions where even those don't help. Things like synonyms can help as can linked views but there are still times when dynamic SQL is required.

Either way, I'm not aiming this post to discuss the merits or demerits of dynamic SQL but rather at giving some airtime to a friendly yet oft unused SQL Server function. First off, hands up me 'cos I very rarely use this function too though I do have a mitigating factor.

Essentially we all know (or at least we should) that it is good practice to use qualified object names and that one should delimit object names to prevent runtime errors with bad practice object names. Some column and table names I've seen in my time include names with spaces, dollar signs, quotation marks etc. all of which I regard as evil. Let's not also forget the use of reserved words as object names which cannot always be easily avoided.

So here's my mitigating factor. I have been lucky enough to be the technical lead on most projects where I am a bit of a hardcase on naming conventions so I have control over what objects are called and created. For those that aren't so lucky...

Enter from stage left: The QUOTENAME function.

Look it up in BOL, it's great. That little function will place the correct delimiters around a given string to present a well formed object name. This means you don't have to specify square brackets, or whatever delimiter you are using in your dynamic SQL. Obviously though, you will have that function scattered throughout your code unless you declare object names up front using the function, placing the results into parameters and then using those parameters in the string concatenation.

Whether or not you use the function is by the by I guess, the main thing is to always delimit your object names. Still, it's good to know that the functionality exists if you ever need it.

- Posted using BlogPress from my iPhone

SSIS Script Task Custom Events

So, an interesting observation regarding SSIS 2008, or should I say anoyance?

At the moment I am part of a team building a data warehouse for an investment bank. Part of this system uses SSIS 2008 (or DTS100 in object model speak) in order to load batches at regular intervals into the warehouse. To ease the monitoring and issue resolution aspects of systems of this nature, we are aiming to use NetAgent to poll the database for new events of interest. Events such as load starts, failures, completion and dimension and partition processing activities all need to trigger an email or something to notify support of the system state.

Well, now you might think, like me, that rather than just polling for "PackageStart" events or any of the other "standard" events we could create our own events that could be used to trigger alerts. Yup, now normally I'd say good idea, but alas this time I can't, I don't think...

See, now in SSIS you could add a script task and using either C# or, shudder, VB .net, use Dts.Events.FireCustomEvent, or a derivation thereof, depending on the language chosen, to fire an event. Using this approach you could quite nicely craft your own custom events, giving you the ability to uniqely name this event. (I would presume that doing this would provide a unique name in the [source] column in the [sysssislog] table such that the event would now be uniquely identifiable, though documentation around all of this appears sparse, at best). One snag though...

Now before I get to the proposed gotcha I guess I should 'fess up... I've only been using SSIS in any guise for about 9 months, so by no means am I a seasoned veteran of the product. Thus quite possibly there is a solution to this gotcha. Just maybe not an intuative one? (Hmmmm speaking of intuative, just don't get me started on case sensitive lookups. I know that they may well be quicker but really? WTF?)

Anyhoo, so now that you have your über cool custom event, here's the issue. How do you get the SSIS package to log it? If you edit the logging properties of the package, even specifically selecting the script task, you are only presented with the predefined events for logging. You may be able to "load" the custom events or perhaps edit the package XML but really?

So long story short, if someone knows of a way to get an SSIS package to log script task defined custom events, please don't be shy and let me know...



- Posted using BlogPress from my iPhone

Location:Somewhere on South West Trains