April 7, 2010 at 8:44 am
For the record, I checked with the user, and of the two 2/29/2007's in there, one was supposed to be 3/29/2007 (typo), and the other should have been 12/29/2007 (the leading 1 got chopped off somewhere).
More seriously, never assume. Don't change data without authorization/request/whatever in writing from the user. Do maintain an audit trail. Do expect that sometimes the "fix" is going to be, in and of itself, wrong; either the original data was correct and necessary (perhaps it is a "we must record whatever the paper form said, whether it makes any sense or not" field), or the new data is just as bad (or worse) than the previous bad data.
Do also realize that even this simple date "fix" could have significant repercussions; for instance, in health insurance, a claim that was previously paid may now be denied, or retro billing may take place.
April 7, 2010 at 8:46 am
Just to muddy the waters, I used to program at a company that did financial calculations for loan companies. I've use what we called 360 day calendar.....every month had 30 days. So not only was there a 2/29/2007, but a 2/30/2007.
Someone said check with the end user and thats about all you can do.
ps. Someone once told me that any date calculation involving calendars will fail in every computer language ....at some point. I call it job security !
April 7, 2010 at 9:06 am
I am a member of the 'maintain' but do not change group, but have a similar dilemma:
One of the systems that I work with is the county prosecutor's case management system. Part of the system involves storing audio and video recordings which are evidence in a particular proceeding. Some of these files are HUGE, so the planning decision was to store these recordings on CDs, not in the database. Of course, the case management system has thousands of these files, bordering on thousands of gigs.....all from different policing agencies...who all use differing types of cameras and recording devices, which of course, all use proprietary methods of storing the audio or video. And...since the data is evidence, it must be left in it's original format or else the pandora's box of "changing the evidence" arises because the data was compressed, or translated into a standard format.
What happens to this evidence stored on CDs after several years? And, if court requirements need that audio/video file for a new trial five years down the road, who is going to find the proper software and/or drivers to read the CD correctly? Who's responsibility is it to be able to read the CD? Prosecutor? Opposing counsel? Our department? We just store current case data, not the data on the CDs.
I'm all for leaving the data issues to the data owner, but some "owners" have no concept of what it takes to make data available 5, 8 ,12 years from now. Proper planning needs to take these things into consideration. Using CDs might work for now, but I am not certain it is the "best" way to handle long term data storage.
April 7, 2010 at 9:18 am
In a perfect world not changing anything until somebody makes a command decision on the business end of things and in the original article, I suspect the report could have waiting until the next day after the date issue was resolved.
Now, let me give you a situation and think about how you would handle it.
You have a large enterprise system that is going through a major upgrade, users are being kept out of the system for a couple of days and are jonsing to get back into the system so they can sell and put money in their pockets. You have gates every two hours and have just gone through one. Business contacts are spread out over the world\country and contact is via confernece call. All is going well and then, boom! your upgrade fails because of bad data (in a revenue field) and your business contacts have gone dark.
Do you sit for two hours until you can get hold of them and they organize a conference call to discuss and start over or do you 'modify' the data to get over the hump? Tough call to make when the pressure is on. Your thoughts?
Me personally, I'd be dialing madly until I hear from somebody, (Project Manager, etc..)
On a final note for those in the US \ Canada and follow college hockey,
GO RIT Tigers!
April 7, 2010 at 9:25 am
" I think mistakes should be made painful for users so that they are more careful next time. "
I completely agree that proactive data cleanup should not be the job of the DBA. At most they should be responsible for implementation of changes and should not be relied upon to monitor the content of the database.
Through my various experience, I have seen this done correctly, with data ownership by the business, and IT data stewards to help the business understand how to properly store and cleanse the data. This however is not the norm in businesses where I have worked. In a smaller business it often falls to the developer/DBA (a combined role at my current client) not only to keep the database up and running clean, but also to detect, document, and correct data issues. In fact it may be considered a failure of that individual when a user finds the problem first--even a data input issue!
Regardless of the level of responsibility delegated to the DBA, it is essential that scripting/backups be used to make the audit trail and ensure reversibility of any changes and that is absolutely the DBA's job. Also, consultation with the business on such changes should be automatic. This is why it is so important to have a rapport with the users and know who has authority over what data. In this respect it is not necessarily pain and/or punishment we want to inflict on them, but rather guidance and education, right? :ermm:
😎 Kate The Great :w00t:
If you don't have time to do it right the first time, where will you find time to do it again?
April 7, 2010 at 10:05 am
" I think mistakes should be made painful for users so that they are more careful next time. "
In this respect it is not necessarily pain and/or punishment we want to inflict on them, but rather guidance and education, right?
Sortof, it's not exactly about pain, but it isn't about guidance either. If I, as the DBA, do the work to research the data and figure out what it should be then two things happen:
1. I get behind on my real job.
2. The cause of the problem never gets addressed.
However, if I hand it off to the supervisor responsible for it then it takes up that person's time and they have an incentive (and the authority) to address the cause.
--
JimFive
April 7, 2010 at 11:37 am
As a DBA and falling under Sarbanes-Oxley audit standards I never change Production data based on some Manager just coming up and telling me to do so that is outside of the overlaying application and has no audit or paper trail with it, particularly with financial data. That has to be cleared with the Director of Database Operations first, no exceptions, and I can almost with 99.5% certainty tell you what his answer would be as well. "No raw updates to back-end financial data. Use the application to change or remove the data you desire. That is what it is there for. Otherwise, put in a trouble ticket and document this in the system! Good day sir!" 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
April 7, 2010 at 11:40 am
ben.mcintyre (4/7/2010)
While I agree with the sentiment of this article, I think the most important thing is: if you DO change any data, KEEP AN AUDIT TRAIL ! You MUST be able to undo ALL your changes later........
Ben
+1 for that man. I was coming here to make that exact same point.
For the love of $deity, do NOT make any change that you cannot reverse.
You could start off just wanting to help and end up being held responsible for massive errors, even if the original mistake was not yours.
April 7, 2010 at 12:13 pm
Check with someone that is responsible for the data, or its use, and confirm with them that you are making the correct changes.
Sound advice. Even though I am responsible for the data, I always have somebody from the business verify for accuracy as they would expect it. They know how it correlates best to their work duties and the business model.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 12:14 pm
WayneS (4/6/2010)
Rafael A. Colon (4/6/2010)
... In our Company we have a separate Data Management team that deals with all the issues related to data...Must be nice. Really nice. 🙂
We have that department too. That department reports to me. Wait - I am the department:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 12:15 pm
ben.mcintyre (4/7/2010)
While I agree with the sentiment of this article, I think the most important thing is: if you DO change any data, KEEP AN AUDIT TRAIL ! You MUST be able to undo ALL your changes later.
In addition to the audit trail you could go another step. Before making any changes, make sure you have a backup of the data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 12:20 pm
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
Paul White NZ (4/7/2010)
David Fulton-420388 (4/7/2010)
I'm in the camp that the DBA should maintain the data entered by the users. If data looks incorrect, then bring it to the users' attention and let them research it and make the appropriate changes.Is there a 'not' missing from that first paragraph...? :unsure:
No, there isn't. My position is that the DBA should make sure the data is secured, backed up, etc., but that they should not be actively validating and changing data. If they see data that appears to be inconsistent, then they should report it to the users and let them check it out and fix it.
Well now I look silly. Sorry, George and David.
Rollback Transaction 🙂
DBCC Timewarp
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 12:23 pm
Whil;e I agree that data should not be changed without careful consideration, I also have to deal with the very problem discussed in the editorial and yet I am required to insert the "date" into a Datetime datatype column in SQL Server. Thus the connundrum regarding, if I can't insert the incorrect date, what do I insert?
We get data fed from an AS400 that is programmed in COBOL with a DB/2 database. Much of the data is originally fed from outside clients, as well. Since COBOL tends to treat data as either Alphanumeric (thus allowing non-numeric characters) or numeric (restricting to 0-9), there is no protection against invalid dates as long as they have numbers and no letters. However, since the "dates" are actually used as dates in our SQL Server world, we need to receive valid dates . . . but it is often nearly (if not totally) impossible to get our Big Brothers to filter and validate the dates or even to get a "business decision" as to what Feb 29, 2007 or Nov 31 of any year means.
So, our choices boil down to:
1) Reject the record entirely (which is not acceptable to the business);
2) Change all of our date columns to varchars (which I don't find acceptable);
3) Turn the invalid dates to NULL (which gives the business heartburn);
4) Adjust the day part of the date so that it reflects the last day of the indicated month;
5) Adjust month part of the date so that it reflects the next month and the day part of the date so that it represents the excess over the last day of the previous month.
None of those answers are "good"; however, without a decision from the business sector, someone has to do something.
So, what do y'all recommend? 😀 :crying:
Ralph D. Wilson II
Development DBA
"Give me 6 hours to chop down a tree and I will spend the first 4 sharpening the ax."
A. Lincoln
April 7, 2010 at 12:31 pm
RalphWilson (4/7/2010)
Whil;e I agree that data should not be changed without careful consideration, I also have to deal with the very problem discussed in the editorial and yet I am required to insert the "date" into a Datetime datatype column in SQL Server. Thus the connundrum regarding, if I can't insert the incorrect date, what do I insert?We get data fed from an AS400 that is programmed in COBOL with a DB/2 database. Much of the data is originally fed from outside clients, as well. Since COBOL tends to treat data as either Alphanumeric (thus allowing non-numeric characters) or numeric (restricting to 0-9), there is no protection against invalid dates as long as they have numbers and no letters. However, since the "dates" are actually used as dates in our SQL Server world, we need to receive valid dates . . . but it is often nearly (if not totally) impossible to get our Big Brothers to filter and validate the dates or even to get a "business decision" as to what Feb 29, 2007 or Nov 31 of any year means.
So, our choices boil down to:
1) Reject the record entirely (which is not acceptable to the business);
2) Change all of our date columns to varchars (which I don't find acceptable);
3) Turn the invalid dates to NULL (which gives the business heartburn);
4) Adjust the day part of the date so that it reflects the last day of the indicated month;
5) Adjust month part of the date so that it reflects the next month and the day part of the date so that it represents the excess over the last day of the previous month.
None of those answers are "good"; however, without a decision from the business sector, someone has to do something.
So, what do y'all recommend? 😀 :crying:
...is it fear or reality that sometimes the business owners expect you to figure out answers to 1 through 5 as well? After all, it is a technical issue and you are the technical person.
April 7, 2010 at 12:31 pm
Take the time (and bite the bullet) to write a preprocessing module to edit the batch coming into your database and edit the darn field. Any record not passing normal, standard date edits, gets written to a file that you transmit BACK to the AS400 system. Print out an edit report and send the edit report to your boss and the admin in charge of the AS400. Start screaming that something needs to be done.....
....otherwise, YOU are going to end up monitoring this issue for-e-v-e-r.:cool:
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply