Way to create a re-usable base table definition in InfoSphere Data Architect

It's not impossible to compare two different schemas, it's a calculation of how confident you are in the result. I've essentially borrowed from Bank Reconciliation techniques

Important: This reconciliation isn't about making sure the destination exactly matches the source in it's data context (there's a reason you're migrating to a new system), but you do need to explain any discrepancies!

Basis:

1. Identify metrics that you will use to measure (ie: total number of users, sum of all their ages, list of user id's and their postcodes...) I try to use several metrics from: Totals, Averages & sample/detailed records
2. Dump this data into a common location (via views/reports/whatever is reasonable)
3. Compare your data and make sure any discrepancy can be explained

I've generally split the comparison into several methods (in particular for the detailed metrics):

• By Table: Usually using the Source Table as the definition, creating a set of views on the destination DB to try and reproduce the Source Table Data
• By Object: Depending on the DB and what you're storing it might make more sense to create a holistic view of the objects (ie: the user) across several tables, similar to above creating a set of views to return that object as a set of results
• By Reports: If the Source DB has a good comprehensive set of Management Reports, another technique is to reproduce those reports with the exact same formatting

Techniques:

No matter what method I've used to produce what will be compared, I end up with a set of files/views/dbs that hold the reconcilable Source and Destination data, then depending on the medium I can use one of the commonly available tools to compare them. My own preferences are:

1. File Comparison
   Create two different folders for SourceDB and DestinationDB (time/version stamped so I know when I did the reconciliation) and dump out the output of my metrics as relevantly named files, then use a comparison tool (such as CSDiff) to identify differences

2. Excel Comparison
   In particular when dealing with Management reports, I'll create a set of Excel of workbooks to compare the reports (in essence making use of VLookups and comparing totals)

3. Data Comparison
   Rather than outputting the reconciliation data to files or reports, output them to separate DBs, then use something like Redgate SQL Data Compare to compare the DBs

Other tools:

Not tried any of these, but a quick google search on 'Data reconciliation tools' gives:

• n-Tierfs Data Reconciliation Software
• Testing Performance Data Reconciliation

There's others, but those were the ones that looked reasonable

They can read/write in every schema because they have db_datareader and db_datawriter fixed roles assigned. These roles allow the user to read and write to any user table in the database ( http://msdn.microsoft.com/en-us/library/ms189121.aspx ).

(btw db_ddladmin also allows them to run ANY DDL commands in the database, which might cause you some problems if they start dropping/modifying each others objects)

If you revoke these role grants, you can then assign explicit schema permissions to a user, ie:

GRANT select, insert ON SCHEMA :: schema1 to user1;

More details at http://msdn.microsoft.com/en-us/library/ms187940.aspx

If you can't revoke these roles because they're in use elsewhere, then you'll need to explicitly deny permissions on the schema for the users you don't want to have access. (A deny at the schema level overrides the database level grant from the fixed role).