April 6, 2011 at 12:30 pm
Hey guys. I'm looking for some recommendations on ways to easily deal with crappy data. I have some solutions in mind but I've got a significantly short window in which to implement some form of corrective process. Yes it's a wallbanger and I've got contradictory requirements.
I've got a vendor system that stores *every-damned-thing* in floats. Lazy gits. It's a trading platform. That's all well and good until some typo puts something rediculous into place. This is quickly found and corrected, but I have a timing problem. I pickup data for a second system between the imports and the human QC on the data later that morning.
This second system cannot handle floats. It's a proprietary reporting system that has limitations on the size of numbers that are pumped into it. Why? I don't know. It's a software that's about as complex as learning VBA from scratch. I'm stuck with that, too. I'm the bridge.
Well, of course, during final testing I run into worst case scenario and hit a number that will not move. I've got business wanting to show any error in one system in the other one. Implementing QC before the load is not an option, and even then, my audit tables still need to be able to process this problem (I use the audits to do delta moves because I can't handle a straight load).
I've got some ideas in mind, both social rule changes and technical workarounds for portions of it, but I'm wondering if others have ran into wallbangers like this and how you handled it. I realize there is no complete solution to the issue. Something has to break here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 6, 2011 at 12:52 pm
Craig Farrell (4/6/2011)
Hey guys. I'm looking for some recommendations on ways to easily deal with crappy data. I have some solutions in mind but I've got a significantly short window in which to implement some form of corrective process. Yes it's a wallbanger and I've got contradictory requirements.I've got a vendor system that stores *every-damned-thing* in floats. Lazy gits. It's a trading platform. That's all well and good until some typo puts something rediculous into place. This is quickly found and corrected, but I have a timing problem. I pickup data for a second system between the imports and the human QC on the data later that morning.
This second system cannot handle floats. It's a proprietary reporting system that has limitations on the size of numbers that are pumped into it. Why? I don't know. It's a software that's about as complex as learning VBA from scratch. I'm stuck with that, too. I'm the bridge.
Well, of course, during final testing I run into worst case scenario and hit a number that will not move. I've got business wanting to show any error in one system in the other one. Implementing QC before the load is not an option, and even then, my audit tables still need to be able to process this problem (I use the audits to do delta moves because I can't handle a straight load).
I've got some ideas in mind, both social rule changes and technical workarounds for portions of it, but I'm wondering if others have ran into wallbangers like this and how you handled it. I realize there is no complete solution to the issue. Something has to break here.
I had a similar situation recently, but from reading your post I think I probably have more flexibility than you do. I came across the data size problem while performing the initial ETL, but a modified version might work for you if you have the flexibility to impliment it.
When the move process finds a number that won't fit your destination table, move that record instead into a third table that only contains "bad" records and fire off an email alerting you and any other pertinant people that there is a new record or records in that table. That allows the rest of the load to proceed and brings in human intervention when that's the only thing that can help.
If you can't not insert the record, still shunt it off to a third table with identifying information as before and insert the truncated version into the destination table. This will at least allow you to revisit the problem asap and apply any fixes that might be possible.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 6, 2011 at 1:19 pm
Stephen's suggestions is what I've done in the past for similar situations, though usually for me it's been diff char sizes that exploded things.
In SSIS, you can get a "bad" output stream and process that separately in a package. I might dump that into a separate table, maybe with a job that pings people when the load is done and the count(*) > 0 in that table so they can go process things that broke.
April 6, 2011 at 1:25 pm
My advice to you is to drink heavily!!
I found my self in an almost identical situation. In this case, the floats were a result of the PM using the import wizard to connect to a Lotus Notes database to create a portion of the database for the new application. The design phase was behind schedule, his reason for this approach was that it would take too long to complete the database design process! When the tests of the old data in the new system were run, few of the numbers matched!
My approach was threefold:
1. I changed the things that needed to be changed when I could get away with it undetected. Nobody knew what was going on, or understood databases, so this was very easy. It saved my sanity, and a significant amount of work. Would I recommend this or do it again? Maybe. But I have been able to prevent these situations, so far, since then.
2. For the things that simply would not work, I spent a significant amount of time documenting the issue, the solution to the issue, and the cost/times to complete the correction. Basic stuff, but I went out of my way to provide specific examples of before/after. This was where I had to be a teacher first, and a DBA second.
3. I took essentially the same approach as Stefan, and "logged" the failures. I used the steps from #2 to produce a daily "report", which got me some traction toward doing it right.
Ultimately I was not around to see the finished product. As costs mounted, they let the high priced help go in favor of outsourced labor. The portion of the system created by the import wizard was never put into production.
Good luck!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 6, 2011 at 2:16 pm
Thanks for the comments guys. I was pursuing something similar but time is against me. I've got a few more tricks up my sleeves and I'll share them after. I'll repost later and let you know how things worked out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 5:29 am
Craig,
If you still need assistance, what's the problem with the problem number?
I'd go for the error table & email, if I had time to work it. But if you don't, can you at least grab the LEFT(,X) numbers or RIGHT (,X) numbers then reconvert it back to your destination datatype? Since floats aren't absolutes anyway, that may or may not work for your system.
April 7, 2011 at 6:43 am
Are there any known limits for the data so you can remove the human element?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2011 at 11:19 am
Brandie: It's about 6 quadrillion larger then the target system can handle, especially in its rollup calculations.
Jeff Moden (4/7/2011)
Are there any known limits for the data so you can remove the human element?
Theoretical, yes, but they want an error in one displayed in both, but a human decides it's wrong, not a formula.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 4:32 pm
Craig Farrell (4/7/2011)
Brandie: It's about 6 quadrillion larger then the target system can handle, especially in its rollup calculations.Jeff Moden (4/7/2011)
Are there any known limits for the data so you can remove the human element?Theoretical, yes, but they want an error in one displayed in both, but a human decides it's wrong, not a formula.
Slight variation on the log and email approach: as well as logging in a list of errors and emailing to let people know stick a null value into the target table and make the log entry point to the affected row. If the target uses ansi nulls this will ensure that errors turn up appropriately in the target system. Do the logging and null insertion before you do the email if this helps on the time pressure - because those nulls mean the error will be spotted and known about even without the email.
Tom
April 7, 2011 at 5:36 pm
Tom.Thomson (4/7/2011)
Craig Farrell (4/7/2011)
Brandie: It's about 6 quadrillion larger then the target system can handle, especially in its rollup calculations.Jeff Moden (4/7/2011)
Are there any known limits for the data so you can remove the human element?Theoretical, yes, but they want an error in one displayed in both, but a human decides it's wrong, not a formula.
Slight variation on the log and email approach: as well as logging in a list of errors and emailing to let people know stick a null value into the target table and make the log entry point to the affected row. If the target uses ansi nulls this will ensure that errors turn up appropriately in the target system. Do the logging and null insertion before you do the email if this helps on the time pressure - because those nulls mean the error will be spotted and known about even without the email.
Requires a coding trap. Not impossible to do, but impossible in my current timespan. Not because of a single field, but I have found out there are ooooh... 50 of these or so that can cause me havoc. Individual trap and converts are out of my current time availability, thus why I went looking for alternatives.
Right now I'm debating on a two step process to deal with 'impossible' errors, while widening the scope of allowable data on the target system. We're currently proofing out the target to find out exactly where it can go against a decimal. Then I just will have to chew through the utterly rediculous repairs. What I'm debating on doing is creating a single trap for a row that any conversion error will trigger a single corrective item, forcing all possible columns to a dead value.
I wouldn't use NULL here because the error could end up hidden, especially in summary reports. Once I find the max, I'll set any outlying failure row to have 300 9's (or whatever it is at max) to cause silliness at the reporting end, but that's going to depend on the final POC tests against the system. We're breaking the engineering/official specs... maybe.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 11, 2011 at 9:12 am
I like the 300 9s as a marker, as long as people aren't rolling up values in aggregates. In that case, you might cause all sorts of people to get upset. If there are that many 9s, perhaps any summary queries should ignore them? Filter them with a WHERE?
April 11, 2011 at 11:38 am
Steve Jones - SSC Editor (4/11/2011)
I like the 300 9s as a marker, as long as people aren't rolling up values in aggregates. In that case, you might cause all sorts of people to get upset. If there are that many 9s, perhaps any summary queries should ignore them? Filter them with a WHERE?
Actually, that's the point of the 300 9's idea, to make the summary (aggregate) reports utterly useless if I have unusual erroneous data. This is a financial system, so if it's wrong, I don't want you to simply know it's wrong... I want a 5 year old trying to do his math homework and learn subtraction to have his first instinct be "Hey, I saw this on Sesame Street! One of these things is not like the others!"
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 11, 2011 at 2:45 pm
I might make sure you circulate the issue widely than so that people are aware that something with a huge number isn't correct. I'd hate to think that someone copies the number and just goes "hey, that's really large, we must be doing good" (or bad).
April 11, 2011 at 2:50 pm
Steve Jones - SSC Editor (4/11/2011)
I might make sure you circulate the issue widely than so that people are aware that something with a huge number isn't correct. I'd hate to think that someone copies the number and just goes "hey, that's really large, we must be doing good" (or bad).
Oh, definately. Part of the issue here is that if we do misreport a number business wants it represented to the client (this is a web report). So, for the insane numbers or misfits, it fits right in with the existing plan. They shouldn't exist longer then a day anyway, it's more to make sure someone sees it and corrects it back at SOR so the next day I'll get a correction delta, and so any reports we might have produced from the SOR in the meanwhile will have the exact same error.
Business/client interaction decision, not my call. Just doing the best I can to work with it. Besides that, if someone starts thinking they're doing business at the level of the national debt, I'm going to be rather worried. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply