T-SQL Tuesday #13 – Business Requirements

Comments: 2 Comments
Published on: December 13, 2010

Business Requirements

We have made it yet another month and to yet another episode in the continuing saga known as TSQL Tuesday.  This month we are being hosted by Steve Jones ( Blog | @Wayoutwest ) of SqlServerCentral fame.  Steve has asked us to speak a little bit about business requirements, interpreting those requirements, and some of the pitfalls that may or may not exist in the whole communication process of getting a project done.  Now, can somebody please explain the requirements for me?

What issues have you had in interacting with the business to get your job done?

I think a common area that is easily overlooked when it comes to requirements and interpretation of requirements is report creation.  A common problem is that there are no defined or written requirements for the creation of a report.  When there are inadequate requirements, it is easy to miss the intention of the report and thus frustrate the business group that requested the report.  Another problem that can arise is the perceived inaccuracy of a report – even when all business requirements are properly met and signed off by the requesting business group.  How can that be?  Let me explain.

Perceptions

A recent problem was brought to light that revolved around the creation of two similar reports.  The reports were to be used by different business groups and each was requested by a different group.  The values in the reports no longer match due to a change requested on ReportA by GroupA.  GroupB doesn’t think this is accurate and wants both reports to match.  Small problem is that both reports should not produce the exact same results based on requirements and usage.  The report for GroupA has some extra requirements and filters placed on it to prevent the users from seeing data about former employees.  The report for GroupB is different in that it should show an overall summary for all data, even data of former employees.

Re-Alignment

Managing business requirements is almost as much about managing the perceptions of the business users as it is understanding what the business truly wants.  It is important that you are able to help them understand what it is you will be doing, what you can accomplish, when you can do it, and what impact it may have on other business parts (if they are known).  When an issue arises, it is best to approach the business and try to understand what the disconnect is.  In the case of the example of two reports outlined, I had to research and test both reports to get a better understanding of what the problem was.  Once I did that, then it was a matter of explaining the findings to the business.  From there, a discussion between business analyst and the two departments for which the reports serve needs to take place and an agreement made.

Conclusion

This is a rather short and straight-forward entry for this month for TSQL Tuesday.  This is a delicate subject and I am sure many people will have many more tales to tell.  I think it is most important to approach the business and try to help them understand what you understand from the requirements and get it hammered out before too much development has been done.

Wait Stats resources

Comments: 1 Comment
Published on: December 13, 2010

Today I was out and about looking for past roundups on TSQLTuesday.  While doing that, I came across a post from Paul Randal (BlogTwitter) about wait stats.  The thing that strikes me about this post is the great query and the excellent references to more resources.  I recommend that you check out the article he has written on the topic.  Also, make sure you read the reference material he has provided.

You can find it here.

SSIS Job Execution

Tags: , ,
Comments: No Comments
Published on: December 13, 2010

While working on a process to refresh the QA environment (data purge, reload data and reapply changes made over time as parts of release cycles), I ran into self-imposed requirement.  I was attempting to create a process that would perform the entire process consistently.  Amongst the items that I needed to include in this process to “refresh” the QA environment was the need to run a couple of SSIS packages.  SSIS was not installed on this particular server where I was creating this process.  I needed to have a way of executing two SSIS packages without installing SSIS on the local server.

With that need, I decided to test a remote execution of a job that had SSIS steps.  The method I decided to use was to employ OSQL.  Here is what I did for that.

[codesyntax lang=”tsql”]

[/codesyntax]

I added that line of code to a SQL Agent Job step.  The job step type is Operating System (CMDExec).

This method works well for me since I had already tested the package on the remote server and knew that it worked.  There are several other methods as well, this happened to be implemented more quickly for me at this time.

SQL Confessions

Comments: 1 Comment
Published on: December 13, 2010

I had a brainstorm of an idea for a group of articles on my blog a couple of weeks ago and am finally getting around to putting it together.  The idea came about while I was struggling with a problem at work and trying to meet the requirements in the code.  As the title states, this group is going to be about things done in SQL server that I would normally avoid.

The first article came about while trying to find a solution to some business requirements.  The requirements had an average to be calculated and then a revised average if certain criteria were met.  Then it was required to move things from one group to another group depending on a series of criteria.  When I first looked at these requirements, I thought I would need several cursors.  As it turns out, I had to implement just one cursor.  I could find no way of getting away from using a cursor for this one requirement.  The essence of it was that I had two variables.  One variable was the number of items to move and the second variable was the person to whom the items needed to be moved.  Not only did it require those two variables, but there was the additional requirement that it be randomized.

First, let’s take a look at how I did the randomizing.  I took care of this requirement by dumping all of my prep data into a temp table that had a default on a column explicitly to hold  a random value.  Here is the code for that table.

[codesyntax lang=”tsql”]

[/codesyntax]

Using this table, I can just run a set-based insert to populate the table and thus end up with random values in the “Randomizer” column due to the default constraint.

[codesyntax lang=”tsql”]

[/codesyntax]

That piece of the puzzle was fixed rather easily.  Now for the next part – the confession part.  I had to write a cursor.  Next part of that confession – I had to look up how to write the cursor.  Without going into a lot of details, this is the code of how I used a cursor to solve this particular problem.

[codesyntax lang=”tsql”]

[/codesyntax]

Note that this cursor is a little hybrid.  I am using this cursor to perform some set based actions.  I am fine with that.  I am happy with the random distribution and the fact that this performs well.  This was done for a one off requirement that will not be used again (or at least not planned to be used again) and it met all of the requirements.  I was blocked on this particular requirement by the required variables and couldn’t get past how to do this in a 100% set-based fashion.  If you have any input, let me know.

Do you have any such stories to share?

page 1 of 1

Calendar
December 2010
M T W T F S S
« Nov   Jan »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Welcome , today is Tuesday, August 20, 2019