Av rating:
Total votes: 27
Total comments: 3
The Ultimate Excuse Database
15 January 2007
---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:
TEST DRIVE EXCUSE GENERATOR
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
AS
SELECT RAND() AS RandomNumber
GO
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
AS
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
GO
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.
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) AS
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
GO
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) AS
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
GO
So, invoking:
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')
*/
AS
SET nocount ON
CREATE TABLE #sentences (sentence VARCHAR(500),
theorder numeric(10,9))
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'Without %M %A, a project will eventually fail!
One of the reasons why IT projects suffer from
this to a greater degree than other industry
projects lies in %B.',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'A %L consequence of %H is either %I or %I.',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'An analogy of the link between %A, %A and %A is
the project management constraint triad --%D, %D and %D.
Changes to any one of these constraints affect
the others.',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'If any organization %F implements a new tool for %T
without considering the underlying %T processes or the
organizational structure required to operate and support
the technology,then failures can and more than likely
will occur: ',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT 'Business environments these days are characterized by %B,
and acceleration of everything from %J to %J.
IT has been one of the major drivers of this.',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT '%N %O %P %Q %R %S',RAND()
INSERT INTO #Sentences (sentence,theOrder)
SELECT '%N %K %A %R %S',RAND()
DECLARE @outputString VARCHAR(8000)
SELECT @OutputString=COALESCE(@OutPutString,'')
+dbo.ufsOneOf(' | | | ')
+ dbo.ufsWaffle(sentence
FROM #sentences ORDER BY theorder
SELECT @OutputString
GO
So, at the click of a mouse we generate random, dignified excuses that spread the blame evenly and safely, and away from any particular individual.
Start generating excuses…
Simple-Talk has provided a plug-an-play version of the bullshit…err…excuse generator. You can give it a whirl here:
http://www.simple-talk.com/apps/excuse
As with all code or prose, some editing and cleanup of the output is required, but it is easy to see how versatile and useful this tool can be.
The full source code is available from the CODE DOWNLOAD link (in the box below the star ratings at the top of this page).
Enjoy!
This article has been viewed 9243 times.