15 January 2007

28,528 views

33 0

The Ultimate Excuse Database

Is your IT project in trouble? Do you need to create a smokescreen so that no-one can pin the blame on you? No problem! Marvel at Phil Factor's versatile use of T-SQL functions in creating the "ultimate excuse database" and then start generating your excuses immediately!

—Editor’s Note—
This article, describing Phil’s “ultimate excuse generator”, was first published in December 2005. The plug-and-play version of the tool was recently removed from the Red-Gate.com website, but I couldn’t stand by and let it disappear for good, so it’s now hosted on Simple-Talk. This seemed like a good enough reason to give the article a polish and re-publish it. If you missed it the first time, you’re in for a treat!
—End Editor’s Note—

Failed Project? Need an excuse fast?

Recently, I received a desperate plea for help from a Simple-Talk reader, who cited the following dilemma:

Phil,

The project I’m working on is about to go belly-up. Can you come up with some plausible reasons for the failure that would not reflect poorly on my work?

Thanks in advance, Dave

This, of course, is not one of the classic SQL problems addressed in textbooks. It is quite difficult to give a solution that conforms to the SQL-92 standard.

There are many ways of approaching the problem, and I’ll go over one solution – my challenge to other readers to se if they can come up with something better! If you want to test drive the excuse generator now, simply click on the link below:

[Sadly the excuse generator finally broke down. We have no excuse for this.]

The versatility of T-SQL functions

Whatever its shortcomings my solution serves as a good way of highlighting the versatility of T-SQL functions. You just need to create a SQL Server database and into it load a few simple functions. These functions will provide facile and convincing arguments as to why a project has failed, thereby creating a smokescreen that will safely conceal the real culprits.

A T-SQL function for picking random data

We start with a function that will pick a random phrase or excuse from a list. This is a modification of a technique I use to populate a database with plausible data for checking indexing strategies, and for testing a fully populated database under load.

The first thing to remember about functions is that the use of the rand() operator is forbidden. This is for a very good reason: its use renders the function non-deterministic. We cheat by disguising it as a view.

NOTE:
This is not guaranteed to work in subsequent releases of SQL Server – though it works just fine on SQL 2000 and 2005.

CREATE VIEW vRandomNumber

SELECT RAND() AS RandomNumber

Now we provide the key function that picks a phrase from a list. This, by itself, is sufficient for simple tasks such as giving a weather forecast, doing a psychic reading, or selecting an appropriate IT architecture:

CREATE FUNCTION ufsOneOf

Description:

Picks one of a delimited list. Here we have a version which has '|'

hard-wired as the list delimiter.

test:

select dbo.ufsOneOf('')

select dbo.ufsOneOf('|||')

select dbo.ufsOneOf(null)

select dbo.ufsOneOf('one|two|three')

*/ ( @String VARCHAR(8000)--input string of a list of alternatives

)

RETURNS VARCHAR(8000)--the list item selected

BEGIN

DECLARE @ii INT;

DECLARE @Substring VARCHAR(255);

DECLARE @which INT;

DECLARE @Delimiter CHAR(1);

SELECT @Delimiter = '|';

--select a random integer between 1 and the number of list items

SELECT @which = ( SELECT RandomNumber

FROM vRandomNumber

) * ( LEN(@String) - LEN(REPLACE(@String, @Delimiter,

'')) + 1 ) + 1;

SELECT @ii = 1 ,

@Substring = '';

--And go to the item you want by iteration.

--This will please the procedural boys

WHILE @ii <= @which

BEGIN

--if the impossible has happened or he has passed a null string

IF ( @String IS NULL

OR @Delimiter IS NULL

)

BEGIN

SELECT @Substring = '';

BREAK;

END;

IF CHARINDEX(@Delimiter, @String) = 0

BEGIN

SELECT @Substring = @String;

SELECT @String = '';

END;

ELSE

BEGIN

SELECT @Substring = SUBSTRING(@String, 1,

CHARINDEX(@Delimiter,

@String) - 1);

SELECT @String = SUBSTRING(@String,

CHARINDEX(@Delimiter,

@String) + 1,

LEN(@String));

END;

SELECT @ii = @ii + 1;

END;

RETURN (@Substring);

END;

Examples of use include this weather forecaster which seems to be at least as accurate as the Met Office or the National Weather Service:

Select dbo.ufsOneOf('Rain|Mist over the hills, Clearing later|

Dry in the east, Rain spreading from the west

later|Rain heavy at times, becoming clearer

later|Generally dry|Showers, more organized

rain spreading from the west|Scattered showers|

Rain spreading from the east|Dry interludes|

Becoming overcast later')

Picking your excuse

Now we build this function into a collection of word banks and phrase banks that we can then use to provide the basis of an IT strategy document; in this case, plausible reasons for the failure of a project.

NOTE:
In order to control the length of the listing, some of the inputs, contraints etc were omitted. The full source code can be obtained from the code download bundle.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

CREATE FUNCTION dbo.ufsSelectRandomPhrase

Description

So here we have a function that returns, at random,

one of a series of words or phrases.

Currently these are set to:

A--conceptual processes--

B--excuses--

C--inputs--

D--constraints--

E--types of projects--

F--pejorative words

G--things that must be good

H--misconceptions--

I--unpleasant results

J--unpleasant things

K--negatives

L--neutral modifiers

M--positive modifiers

N - S --the six main parts of a sentence

T-- process

Note that the function can pass back a string containing

a placeholder of the format %x where x is one of the

characters A-T

Test

Select dbo.ufsSelectRandomPhrase('A')

Select dbo.ufsSelectRandomPhrase('I')

*/ ( @type CHAR(1) )

RETURNS VARCHAR(8000)

BEGIN

RETURN(

CASE @type --processes--

WHEN 'A' THEN

dbo.ufsOneOf('process|organization|technology|planning

|methodology|documenting|design

|implementation|contingency planning

|change control systems')

--excuses--

WHEN 'B' THEN

dbo.ufsOneOf('unclear objectives|changing objectives

|insufficient resources

|impossible schedules

|unrealistic expectations

|unclear roles and responsibilities

|corporate politics|poor communication

|personnel turnover|changing technology

|constraining rules and regulations

|lack of sponsorship|poor planning

|unclear goals and objectives')

--inputs--

WHEN 'C' THEN

dbo.ufsOneOf('management support

|senior management buy-in

|resource allocation

|project control|pre-planning')

--constraints--

WHEN 'D' THEN

dbo.ufsOneOf('scope|resources|schedule

|targets|requirements')

--types of projects--

WHEN 'E' THEN

dbo.ufsOneOf('software packaging and distribution

|CMS|accounting|managing workflow')

--pejorative words

WHEN 'F' THEN

dbo.ufsOneOf('blindly|foolishly|shortsightedly

|unthinkingly|incompetently')

--things that must be good

WHEN 'G' THEN

dbo.ufsOneOf('core services

|comprehensive solutions

|industry best-practice')

--misconceptions--

WHEN 'H' THEN

dbo.ufsOneOf('the over-marketing of product features

by software vendors

|the belief that a particular technology

is a "silver bullet"

|the reluctance to invest heavily in

an area that is a "cost center"

|the overambitious goals

|a lack of understanding of IT processes

|the belief that distributed computing is

technology, not process driven

|the belief that technological change

can be rapidly absorbed by an organization')

--unpleasant results

WHEN 'I' THEN

dbo.ufsOneOf('An inability to properly support or manage

the technology, resulting in increasing costs

|Inefficiencies in the service provided to

customers

|An inability to meet service-level agreements

|Disconnect among the staff due to sudden

introduction of change to their environment,

resulting in %J and/or %j')

--unpleasant things

WHEN 'J' THEN

dbo.ufsOneOf('complexity|resistance|attrition|confusion

|apathy|anxiety')

--negatives

WHEN 'K' THEN

dbo.ufsOneOf('superfluous|inappropriate|flawed

|insufficient|harmful|vague|unhelpful

|inefficient|poor|inadequate|unfocused

|under-resourced')

--neutral modifiers

WHEN 'L' THEN

dbo.ufsOneOf('main|major|subtle|key|contributing

|influential|high level|ameliorating

|inherent')

--positive modifier

WHEN 'M' THEN

dbo.ufsOneOf('sufficient|adequate|focused|beneficial')

--sentence components

WHEN 'N' THEN

dbo.ufsOneOf('|||As with many projects,

|Typically of projects of this scale,

|We are now realizing that|

|Surely|To be absolutely frank,

|Preliminary examinations reveal that

|An in-depth analysis suggests that

|I think it is fair to say that

|It is generally thought that|

|We must conclude that, finally')

WHEN 'O' THEN

dbo.ufsOneOf('one of the %L causes

|a %L factor which was responsible

|the blame|one of the %L reasons

|a %L factor

|the %L difficulties')

WHEN 'P' THEN

dbo.ufsOneOf('for the|for any of the

|for all the issues around the

|for what is usually termed the|

|for what is probably the

|of the avoidance of')

WHEN 'Q' THEN

dbo.ufsOneOf('lack of %M Change Control Systems

|well-defined architectures

|overrun of initial cost estimations

|%K resolve to follow the plans

|inadequate planning|project slip

|budget overrun|%K productivity

|scope-creep

|lack of support from senior management')

WHEN 'R' THEN

dbo.ufsOneOf('was because of|was due to|rests with

|cannot be attributed to

|may be linked with

|is undoubtedly due to

|should be seen in the context of')

WHEN 'S' THEN

dbo.ufsOneOf('changing requirements

|personality conflicts

|%K upper management

|restricted budget|restricted time

|power struggles

|%K elicitation and validation

of requirements

|commitment|overambitious goals

|incompetent staff

|a culture dependent on maintaining

the status quo

|a %K transition strategy')

--processes

WHEN 'T' THEN

dbo.ufsOneOf('business|payroll|accounting|group

|management|sales|marketing')

ELSE

'error -bad placeholder'

END

);

END;

Random excuses become plausible waffle

Now all we need to do is have a function that substitutes phrases in the right place. Normally, this would have a mundane use such as coming up with customer addresses for dummy data, but here we give it a more dignified purpose:

CREATE FUNCTION dbo.ufsWaffle

(

@formatString VARCHAR(8000)

)

Description:

Searches for the next placeholder repeatedly in a string,

and substitutes a random phrase until there are no more.

Requires:

dbo.ufsSelectRandomPhrase

Example:

select dbo.ufsWaffle('%N %O %P %Q %R %S')

RETURNS VARCHAR(8000)

BEGIN

DECLARE @Where INT;

WHILE 1 = 1

BEGIN

SELECT @Where = CHARINDEX('%', ISNULL(@formatString, ''));

--If we are out of % placeholders return the @formatString

IF @Where = 0

BREAK;

--If the delimiter is not in the Args list then do one last

--replacement

SELECT @formatString = STUFF(@formatString, @Where, 2,

dbo.ufsSelectRandomPhrase(SUBSTRING(@formatString,

@Where + 1, 1)));

END;

RETURN (@formatString);

END;

So, invoking:

1	SELECT dbo.ufsWaffle('%N %O %P %Q %R %S')

…repeatedly will supply as many plausible reasons as you wish.

A versatile bullshit generator

However, you will notice a certain repetitive sameness about the previous solution. A more subtle approach is to randomize the sentence structure.

CREATE PROCEDURE spCreateEssay

Description

This will produce a block of text based on picking the built-in

phrases at random and stringing them together.

Then the placeholders are substituted.

Example

Execute spCreateEssay 'Red Face Software Ltd'

Example output

Select dbo.ufsWaffle('%N %K %A %R %S')

SET nocount ON

CREATE TABLE #sentences (sentence VARCHAR(500),

theorder numeric(10,9))