A certain customer was upgrading from 4.0b to ECC6, and wanted to check data integrity.  That is, check to make sure that certain critical configuration and master data tables have exactly the same records before and after the upgrade.

Normally that's a simple problem for LiveCompare, you just use the Synchronization templates that compare table data.

However this customer would not have both systems available at the same time.  Apparently they are taking away the 4.0B system before the ECC6 system becomes available.

That means they would have to take a snapshot of the data from the 4.0B system, and some time later compare it to the data from the ECC6 system.

Initially the customer thought of using Excel files to capture the table data.  They would use LiveCompare to read an SAP table from the 4.0b system and export it to Excel.  Then they would repeat that on the ECC6 system, upload the data from the two Excel files into LiveCompare and compare the results.

They needed to analyze between 50 and 60 tables.  About 40 tables have less than 1,000 records.  About 15 tables have less than 10,000 records, and about 5 tables have more than 10,000 records.

Our reaction to all this?

• There is no need to go through Excel on the ECC6 system.  Instead, they could read the snapshot as an XDS into a table dataset, read the SAP table from the ECC6 system, and use a Compare Tables action to do the compare.
• Excel is not a very good choice for preserving the snapshots.  Excel is a spreadsheet application, not a database, and it will do undesirable things to the data.  For example, if you have a table field that contains a numeric product code, Excel will turn that string of digits into a number -- perhaps into a floating-point value!  The snapshots should preserve the data types as well as the data values, as much as possible.
• Doing this for 50 to 60 tables would be cumbersome.  That's a lot of files (and external data sources) to deal with.

We proposed a two-phase solution with LiveCompare workflows, using the workspace itself as a repository for the snapshots.  In the first phase, you run a workflow that captures the SAP table data and saves it into a table in the workspace database.  In the second phase, a workflow reads the snapshot table, reads the SAP table on the new system, and uses a Compare Tables action to produce the compared results, and writes the results to an HTML report.

You should know that a workspace in LiveCompare is basically an SQL Server database, and that a table dataset is actually a table in the workspace database.  LiveCompare assigns the name of the SQL table based on the dataset's component ID and the workflow it belongs to (along with some other data that isn't really relevant here).  When you use an Execute SQL action to create or modify some table dataset, you use the Source1, Source2, and Result keywords in your SQL statements to refer to the corresponding table datasets that are linked to that action.  The LiveCompare engine takes care of mapping those keywords to the actual SQL tables before executing the SQL statements.  But you can also provide explicit table names in your SQL statements.  That's just what the phase 1 workflow does. 

The Capture Current Data workflow contains an action named "Copy to Named SQL Table" that does two things.  First, it uses the familiar option CopyRows Source1 Result to make a copy of the SAP data into a regular LiveCompare dataset.  It also executes SQL statements that create another table and copy the data into that table.  This other table has a name that is derived from the original SAP table name.  I call this other table a "named table" for lack of a better term.  The SQL statements are as follows:

    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'B4_String1') DROP TABLE B4_String1
    SELECT * INTO B4_String1 FROM Source1

Note that the SAP table name is input to the action in the String1 parameter.  So the LiveCompare engine replaces String1 in these statements with the SAP table name.  If the SAP table name is 'T002', the resulting named table is called 'B4_T002'.  The first statement removes the named table if it already exists, allowing you to run the action more than once without a problem.  The second statement creates the named table using all the fields from Source1, and copies all the rows in Source1 to the named table.

The second phase workflow uses a similar SQL statement to read the named table into a regular LiveCompare dataset:

    SELECT * INTO Result FROM B4_String1

Again, the SAP table name is provided in the action's String1 input parameter.

Next the workflow does a Read SAP Table to get the table data from the new SAP system.  Logically, the next step would be to use a Compare Tables action to do the comparison, but there's a bit of a problem:  the named table has no keys.  In order to use Compare Tables, the two table datasets being compared must have the same key fields.  Fortunately, the Read SAP Table produces a table dataset with the correct keys.  Also, the Execute SQL action options CopyRows and MakeTable set up the key fields when creating the table dataset.  So the workflow uses an Execute SQL action to make a table dataset from the SAP table dataset (giving us the key fields), and copies the rows from the named table into its result.  This result is then compared with the new SAP table data.

To capture and compare different SAP tables, all you have to do is change one string parameter containing the SAP table name.  Since the customer needed to compare so many tables, it made sense to wrap each of the phase 1 and 2 workflows in a processing loop driven by a table of SAP table names.

Here is a link to a zip file containing both the simple and loop versions of each workflow.

So, what could go wrong with this solution?

One potential pitfall is if a table's structure changed in the upgrade (e.g. a field was added), then the table compare won't work.  To fix that you'd need to add an Execute SQL action to unify the two table datasets -- either drop the new field from the "new" table, or add it to the "old" table.

Another pitfall is if there is some table that you don't want to compare in its entirety; maybe you want to select certain rows, or compare a subset of its columns.  That's not a big deal; you'd just have to apply the same row selections and/or column selections on the Read SAP Table action in both phases.