Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Dynamic reconcilliation framework

Answered Dynamic reconcilliation framework

  • Friday, December 07, 2012 12:51 AM
     
     
    Sign In to Vote
    0

    Hi,
     
        I need to design a reconcilliation framework. That does the below:

        1) Compares the data between the target and the source and obtain the delta i.e. data present in source and not in target. and also data present in target and not in sournce.
     
        2) There might be a few records that exist in both the systems but where there is a column mismatch between the records. i.e.. the primary key value matches but the values in some of the other columns do not match. In that case i need to report the mismatched row along with the the differing value in source as well as the target(columns). This i need to do for all the columns that do not match.
     
         Also for point 2,i need to provide a functionality to turn off and turn on the columns that can be matched.

     

All Replies

  • Monday, December 10, 2012 4:54 PM
     
     
    Sign In to Vote
    0
    which bit are you stuck on / need help with?
     
  • Wednesday, December 12, 2012 1:11 AM
     
     
    Sign In to Vote
    0

    How do i selectively turn on or off the attributes that need to be compared?

     
  • Wednesday, December 12, 2012 10:57 AM
     
     Answered
    Sign In to Vote
    0

    Software exists to perform such a task such as RedGate. 

    If you want to create your own framework for the task, the outline steps are

    • build up a framework of data types (date, varchar, int, double etc) with a definition table to detail the reconcilliations possible for each.  For example, date & dateTime can have datediff; Int and floats, can be numerically matched; strings can be compares as lists
    • build up a table defining your data source (tables / columns).  You can extract this from SQL and tie this up with your data types.
    • provide your user an interface (c# app or direct table access) to flag those columns that should be reconcilled
    • write code (c# or sql) to parse each data type individually and report the result in a set of output tables
    • you may want to include a set of tables to further enhance your recs with min / max values or lengths.  e.g. dates should be greater than 2010-01-01 and less than today; employee titles should be at least two char long or compared to a predetermined list etc

    It is not a trivial task to build such a framework, buit definately possible.  If you have not done it before and don't need any specific bespoke feature, consder buying off the shelf.

     
  • Thursday, December 13, 2012 3:25 PM
     
     
    Sign In to Vote
    0

    In SQL 2008 or later, you can leverage the MERGE statement.