Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TimmyC
Starting Member
10 Posts |
 Posted - 2007-11-12 : 09:51:35
|
| Hello!This is a new problem to me being a junior, and I'm quite sure this is a problem that will occur for more and more people as time goes on.My company has offices from around the world, and naturally there are databases all over the place. We have a main one which the sites run off here in the UK, otherwise we use SAP (like so many others) and Viper. There are also apparently Marketing databases which I have never even seen...There is a nightly import into the main databases, but its choking fairly regularly and the data we are getting out is outright filthy.Anyway, the boss wants a "data firewall" with a rules engine sitting on top controlling what is clean and what is not. Sounds great, but I don't really know where to start. Does anyone have any advice on a design for this? We are currently using SQL Server 2000 but should be moving to 2005 very shortly (not 2008 it seems :-( ).Is this something I can develop in a .net language or can it be done purely on a SQL Server basis? Keep in mind the rules engine which should have a useable interface for a techie who is not heavily trained in SQL Server (like a .net developer).Apologies for the long post and thank you very much for any inspiring (or not) ideas. :-)Timmy |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-12 : 10:07:00
|
| my suggestion does not fit the boss' requirements, but using SSIS as a ETL solution would be a great start. that requires someone to code into the package each new rule as opposed to non-SQL oriented techie adding scrub rules dynamically. if there is no possible deviation from the requirements, you should probably start looking for a third party tool because that solution would be quite the undertaking |
 |
|
|
TimmyC
Starting Member
10 Posts |
Posted - 2007-11-12 : 10:33:12
|
| Thanks for teh speedy reply, though I think my master wants the rules to be easily changed. I will have to drill him for more details when he comes back from holiday.Otherwise it is looking like I will need a .net app to save a set of rules for the updates. Rules for field validation and rules for record matching. I imagine I would need to go through each row being imported, check each field for its appropriate field validation rules, dumping bad records in a bin to be checked later.When thats all done I would have a temporary table of all clean records and I would dedupe using the record matching rules and try to match incomplete records in the bin with "more complete" ones that are acceptable. This is going to get so complicated... |
|
|
|
|
|
|
|
|
International databases and cleanliness