SQL: Converting Column Datatype from DateTime to Uniqueidentifier

Another day for weird annoying things.

I had a table on which I'd inadvertantly set a columns datatype to datetime when it should have been uniqueidentifier. The table was empty.

Resetting the datatype via script won't work due as SQL knows it can't implicitly convert the (nonexistant) data. So I tried using the GUI to change the datatype and I got incompatible operand errors. Tried doing an ALTER TABLE command and got the same error. Finally I tricked the little bugger by converting it to varchar first then to uniqueidentifier. Boom! Done. Sorted.

SSIS Doesn't Play Nice with Special Characters

Oh SSIS you cantankerous old thing, you! I spent an hour today adding cleansing tasks into several data flows to replace special characters with other values ie "" to "-" and "[" to "(" etc. Only to have the content of those dataflows disappear when I closed then reopened the saved package. When I say disappear, I mean empty. Completely. Devoid of all steps. An empty shell.

Only dataflows which had the new special character cleansing step were empty - the rest were fine. This placed the blame squarely on the new steps. I should have twigged when I attempted to copy the step from one dataflow to another and got an error - something about HEX values being invalid. Stupid me, blindly continued creating them manually in each dataflow. Once done, I saved and closed. Then when I reopened, those dataflows were completely empty.

Fortunately being a concientous backer-upper-er-er, I had the previous version stored elsewhere. Sans special character cleansing of course.

So first, why do I need to do cleansing at all? These characters have passed through SSIS into the staging database with no problems. They have also passed through SSIS into the datamart database with no hiccups. Well the problem comes when I process the dimensions which use the datamart tables as their source. Then I get errors.

So, for whatever reason, cleansing ahoy!
But SSIS won't do it, as proven by my frustrating waste of an hour.
I tried using them directly in my source query with the same result. So I created udf_CleanseSpecialCharacters to handle it.


/**********************************************************************************************************************************************
HEADER
/***********************************************************************************************************************************************
FN Name: dbo.udf_CleanseSpecialCharacters
Author: Kristen Hodges
Date: 14 October 2010

-------------------------------------------------------------------------------------------------------------------------------------------
Purpose:
--------------------------------------------------------------------------------------------------------------------------------------------
Return a given string, cleansed of special characters

--------------------------------------------------------------------------------------------------------------------------------------------
OVERVIEW
--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
NOTES
--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
ASSUMPTIONS AND BUSINESS RULES
--------------------------------------------------------------------------------------------------------------------------------------------
It may be desirable in certain contexts to retain some characters whereas in others it is not
eg comma stored in table but not as a ssas dimension member unique key
The @iOptional_CharactersToCleanseList is an optional parameter which can be used to pass in a string of characters which should be stripped and
any others will be retained

The @iStripCharacters is a true/false flag which will strip the characters if true
or if false, the value will be replaced with particular values eg [ becomes -

--------------------------------------------------------------------------------------------------------------------------------------------
USAGE
--------------------------------------------------------------------------------------------------------------------------------------------
Called by:

Sample USage:
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,0,NULL)
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,1,NULL)
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,1,'&')

--------------------------------------------------------------------------------------------------------------------------------------------
CHANGE LOG
--------------------------------------------------------------------------------------------------------------------------------------------
AUTHOR DATE CHANGE
KHODGES 2010-10-14 Created

************************************************************************************************************************************************/

************************************************************************/

ALTER FUNCTION [dbo].[udf_CleanseSpecialCharacters](
@iStringToCleanse varchar(500)
,@iStripCharacters bit
,@iStripAllSpaces bit
,@iOptional_CharactersToCleanseList varchar(50)
)
RETURNS varchar(500) AS BEGIN

DECLARE @return varchar(500), @CurrentChar char(1), @CurrentReplacement varchar(10), @posn int

SET @return = @iStringToCleanse

set @CurrentChar = CHAR(5) --
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(58) -- :
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(59) -- ;
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(91) -- [
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(93) -- ]
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(37) -- %
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(38) -- &
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' and '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(124)-- |

if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(44) -- ,
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = SPACE(1)
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(36) -- $
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(35) -- #
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = 'No.'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(35) -- #
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = 'No.'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(64) -- @
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' at '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(46) -- .
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' dot '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)


IF @iStripAllSpaces = 1 SET @return = REPLACE(@return,' ','')

RETURN @return
END

MDX: Calculated Measure: Opening & Closing Balance

The scenario is this:

Opening Balance =

• my previous closing balance

Closing Balance =

• Opening Balance
• plus new widgets received today
• less widgets completed today

Note how I said that my opening balance equals my previous closing balance? In practical terms, that means:

• count of all new widgets received up to today
• less the count of all widgets completed up to today
  
  
  

In my case a widget refers to a "request", aka an application from a customer for a new credit card. I want to measure how many requests were worked on by my staff during a period of time. That "period of time" gets passed in from a report parameter or query and should always use the 'ReceivedDate' as it's point of reference because we don't want our Opening Balances changing over time (duh!).

My fact table contains a Received Date, a Completed Date and an ItemCount column (there are more but that's all I need for my calculated members). We have an ItemCount column because one request can be for multiple cards. If there's only one card in the request, it contains "1".

There are a multitude of ways to achieve this and here is just one...

First I want to count how many requests were completed in the period. I know what my Received Date member is because the report gives it to me... but how do I know which member of the Completed Date dimension to use? Obviously there is only one physical Date dimension but my cube refers to it multiple times - two of which are Received Date and Completed Date.

I have a problem in that my completed date and my received date are in two different dimensions in my cube so I can't just use any of the MDX relative functions such as ancestor/cousin, sibling, parallelperiod because they aren't relatives at all! What they do have in common though is they share the same member key.

I know the member key from the Received Date so I can use that to find my Completed Date member. But wait, I have multiple levels in my hierarchy; so I need to consider that too.

CREATE MEMBER CURRENTCUBE.[MEASURES].CompletedInPeriod AS

(STRTOMEMBER("[CompletedDate].[Fiscal-Year-Month-Day].[" + [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Level.Name + "].&[" + [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Member_Key + "]")
,Measures.ItemCount),
VISIBLE = 1 ;

The above code shows a simple way to get the answer. Manufacture a string to get the completed date based on the received date and then get the item count for that completed date. It may not be elegant, but it works!

Now I want to get my opening balance. Easy.

CREATE MEMBER CURRENTCUBE.[MEASURES].OpeningBalance AS

IIF([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.FirstSibling IS [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember
,([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Parent, Measures.OpeningBalance)
,([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.PrevMember, Measures.ClosingBalance)
),
VISIBLE=1;

What this says is, if the current member is equal to the first member (at this level) then get the Opening Balance of my parent. Otherwise get the closing balance of the previous member. In practice that means, if I ask for June 1st, 2010 (the first day of the fiscal year) get the Opening Balance for June 2010. If I ask for June 2010 (the first month of the fiscal year) get the Opening Balance for 2010. If I ask for June 12th, 2010 then get me the Closing Balance for June 11th. If I ask for July 2010, then get me the Closing Balance for June 2010.

And yes, it's recursive ie it's constantly looping back on itself. Don't be scared. SSAS is pretty ok with it so you should be too!

Notice how the last portion refers to the Closing Balance of the previous member? Well I guess that means we need a Closing Balance calculation too! This ones' easy! Remember that the calculation is opening balance + new - completed. And if you think about that a bit you'll see that the opening balance is equal to the previous closing balance. So...

CREATE MEMBER CURRENTCUBE.[MEASURES].ClosingBalance AS
([ReceivedDate].[Fiscal-Year-Month-Day].PrevMember,Measures.ClosingBalance)
+ ([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember,Measures.ItemCount)
- Measures.CompletedInPeriod,
VISIBLE = 1 ;

It says, that the Closing Balance equals the Closing Balance of the previous member plus the ItemCount for this period less the ItemCount completed during this period (remember our first calculated member was "CompletedInPeriod". For example, If I ask for the closing balance for June 12th 2010, it will get the Closing Balance for June 11th 2010 ie my opening balance, then add the ItemCount for June 12 and minus the ItemCount completed on June 12.

And that's it. Process the cube and away you go.

Next up, I'm going to use this same calculated measure on different hierarchies in the date dimension. For example, I have a Fiscal-Year-Month-Day, a Fiscal-Year-Month, a Calendar-Year-Month-Day and a Calendar-Year-Month... and many more when you look at Quarters, weeks and semesters! But that's for another day...

Visual Studio 2010 Trial: SQL Project Limitation: Refactor/Rename

Please, somebody correct me if I'm wrong...

** UPDATED **

Yep I'm an idiot. Somehow I installed Professional edition not Ultimate. And professional does not support schema compare or refactoring. Duh!

The Visual Studio 2010 trial claims to be functionally unlimited - just time-limited (90 days). BUT - from what I can tell, object name refactoring (renaming) is unavailable.

According to MSDN it can be done by "right-click the object in Schema view, point to Refactor, and then click Rename'.... or from the Data menu, point to Refactor etc...

However, there is no Refactor option in either location.

MSDN does state that if there are build errors, the menu option is not available. I double-check that, and build successfully it did.

Stumped!

Visual Studio 2010: Enabling Older DotNet Frameworks (or how I learnt to love multi-targeting)

I've been told that SQL 2005 doesn't support CLR functions created with DotNet Framework 4.0 - I haven't tested it cos When attempting to create a new SQL 2005 CLR project using Visual Studio 2010, I found myself at a loss to work out how to create a .NET Framework 3.0 project ie using the new and improved multi-targeting feature.

The only option available to me in the 'New Project' dialog box was a 4.0 Framework. Plus a handy 'More Frameworks" option which took me to the download page for various dotnet frameworks. I already had 3.0, 2.0 and 1.1 installed on the machine so the need to redownload and reinstall them seemed irritatingly redundant.

So after much googling, I found the solution.

You have to install .NET Framework 3.5.

Ummm... that's it.

Close VS, install 3.5 framework, open VS and wacko!

Contrary to the installer instructions, I did not have to reboot.

SSAS: Vague Processing Errors About Connection Strings Are Not My Friend

Oh Visual Studio, you crazy cat, you! I've just spent the past hour or so going stark raving mad. courtesy of a DBA who's less than 'expert' and a teeny little Visual Studio property I neglected to set properly.


When deploying my Analysis Services database, the processing kept failing with a vague error along the lines of "connection string is invalid" and "login timeout". Now, I know there's nothing wrong with the connection string cos it works fine in VS, right? Wrong!


When deploying a cube to the Integration environment, my DBA told me that I couldn't just deploy it (outrageous suggestion), no I had to give them the project, they would deploy it, then I had to reimport it from the new environment into Visual Studio. WTF? Ok, I'll play. I'm new here after all so who am I to question their processes?


So when I later needed to redeploy some changes I'd since made, the DBA told me I had to do the same again. At this juncture, I put up a big red stop sign and start asking some questions. Turns out they do that so the Impersonation works correctly. Why wasn't it working as expected is the real question they neglected to ask and solve. Instead of fixing the problem, the treated a short-term workaround as the solution, and stopped there, purely because they didn't know any better.


Having rarely used impersonation before I didn't have an immediate answer. I've always just use the service account as my credentials because I'm lazy and frankly I can't really see why, on a DEV box mind you, you'd bother? Whatever, I don't care so much, if that's how they want to do it I'm easy.


So the problem is that after redeploying the project, it wouldn't reprocess. As I mentioned earlier, I was getting these vague errors about logins and connections. Turns out the solution was really freakin' simple.

See that little "Remove Passwords" property? Yeah that's the sucker. That cheeky tyke was set to TRUE. Set it to false and whaddya know? Everything works a treat.

Aaaah... Friday 4pm... and time for a brewskie.