This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.
For a Requirements Document Template for a Reporting Project see my article here.
I've been doing SSIS development off and on for over ten years now, have experienced the best and worst clients, business analysts, executive sponsors, and my own shining and less-than-shining moments I have seen many developers turn into ... the DEV Nazi!!
So to make sure that doesn't happen to you, here's a template for your ETL projects.
In scope for this article
Out of scope for this article
So here we go..
A 'who changed what when' chronology of all changes, either using Word change tracking or lines like '8/1/15 Bob's changes per mutual agreement'. Keeps everyone honest when there are lots of changes, especially when there is disagreement as to what is a requirement and what is not, and if you’re ever in any situations where changes are coming fast and furious this is invaluable in managing an approved set of requirements. I’ve known many a business analyst and developer who became quickly overwhelmed by conflict between others on requirement demands.
Also known as project objective, business goals, business problem statement, and various other terms. A simple 'Here's why we're doing this' paragraph. The target audience is those that are likely to only read this paragraph, but this also gives the developer some design decision guidance.
In Scope / Out of Scope
Everybody LOVES this section! Okay, developers LOVE this section. In Scope is a summary of what's in the requirements. Out of Scope is usually a Top 10 list of things that are close but not in, and answers the often asked question 'Are we also getting this too?' This is a developer’s best line of defense against uncontrolled additions and changes in project deliverables, also known as scope creep, by false or unstated expectations. I've known more than a couple of clients that will negotiate effort, cost, and time, and then scope creep the hell out of a project in order to make themselves look better. Been there, dealt with that.
Just because person X told person Y a month ago that it’s in requirements, or this email two months ago said it’s in, or was assumed in an elevator conversation last week, or the client was told it can be handled even though we had no idea when we said that, or was mentioned on the golf course last year during preliminary negotiations doesn't mean that it’s in. If it was discussed and approved in a requirements meeting then it's in, otherwise it's out of scope.
"But wait, we're a really small operation, and this isn't a big deal" you say? Fine, as long as you can roll with that, but the moment somebody has an requirement expectation that wasn't delivered that can change, forcing you to function as the gatekeeper of requirements in a more formal way.
Service Level Agreement (SLA)
Is there a guarantee of performance that the company has negotiated with the client? If yes, then an initial design assessment needs to take place on whether this is a realistic expectation, as management will often negotiate revenue for performance and penalties for non-performance, and there could be considerable effect on scope and time in order to hit an SLA. This will also drive design as to feed reporting, as many large companies will need feedback on their SLA performance.
I’ve been in a few situations where SLAs where negotiated such that processes would be completed by a time that was either not possible, or not possible given certain requirements, and needed to be handled in the design estimate.
What data / processes / events is this project dependent on to be successful? I’ve seen plenty of integration projects put on hold or cancelled because…
These expectations need to be identified and managed early in the project. Also some of these dependencies may not be known to a business analyst and need to be handled in design.
All data that goes into an ETL package:
Data Destinations (Target)
All data that goes out of an ETL package:
Source to Target Mapping
The heart of the ETL requirements document. This spells out the schema of the source(s) and destination(s) in the data feed: sections such as header and footer, how EVERY column maps between source and target, data types, calculated columns, hard-coded columns, business rule validations / acceptable values (greater than zero, date no earlier/later than, NULL values).
Actions for rows
There are some business analysts that cannot provide a source to target mapping, especially if they don’t have access to the data source, which means the developer has to figure this out themselves. If this is your situation then make sure that you’re communicating that you’re doing requirements gathering as well as development, and that some investigation will need to take place before you can give a reliable estimate. You also may have to state various assumptions in your requirements document on details that were not provided.
Business Rule Validation
One client I had wanted to make sure that if patient data came in unless there was a signed contract with the health plan, which meant there was a related row in a HEALTH_PLAN table. This is not an error, but a business rule validation that is handed to catch this data (and possibly stage it somewhere for later use), and then message various business units that this happened so that they can negotiate with that health plan.
Another client was an airline and wanted to know if there was ever a flight that after eight hours of the flight leaving there was no data on it landing. Again not an error, but an event of interest to the business.
If data fails a business rule validation, what action does the ETL take? Accept, accept with default values, not accept? Also be careful in that management and users will tend to call things errors that are not, so you'll need to be very specific as to if something is a true error, data the ETL feed never expected, or something the SSIS package was not designed to handle.
Timing / Scheduling
After the feed runs, who should receive a message, and what is in the message, if…
The end. Thank you for reading my article, and please click the 'Good Article' button if it helped you.
The material in this article was presented at SQL Saturday #682, St. Paul Minnesota, 2017.
|SSIS Performance considerations||4,341|
|How to load multiple sheets of an Excel File in SSIS||14,388|
|How to load data from Web Service APIs||12,370|
|How to load and archive multiple files in SSIS||2,934|