Uncovering the MIIStery of Attribute Level Deltas (In Holiday Verse)

'Twas the night before Christmas in The Keys rental house,
A blog must be written, so I warmed up my mouse.

Wrote some new code in the SQL software,
in the hopes that our deltas will be processed with flair.

No longer all changes, attributes instead
will be processed, as needed, as each row is read.

Now sync'ing the data will be quick as a snap.
Clients will smile, they might even clap.

Between data and metaverse we'll eliminate chatter.
('Though the view of the delta might get a bit fatter.)

The results, very pleasing - make it known in a flash.
Must post on my blog. On the keyboard I'll mash.

This is knowledge the field might be keen to know.
Visitor count on my site might even grow.

So what are the details? You're eager to hear?
I'll tell you right now. (First a sip of my beer.)

To process this way, had to think of a trick.
It had to be clever and it had to run quick.

Keep it simple to implement, that was the game.
So that admins could run it and not go insane.

"Now, WHERE clause! Now, LEFT JOIN! Now CASE WHEN and IF IN
On, INSERT! On, UNION!" (Some sweet code I'm mixin')

To the top of the set: ADDs, DELETEs, yes, list all.
It's the modified ones that are different, ya'll.

For each change in the row we must now specify
A new row in the table.  I gave it a try.

With all of the data, list the attribute, too.
That new little column is really a clue.

This really streamlines the process, to tell you the truth.
Now ILM can focus on just what is new.

Temp tables and queries in SQL abound,
'Til each little change in the data is found.

(In the original poem, here, this line ends with "foot".
But to rhyme it in context...  Couldn't think what to put...)

On the code and technique I continued to hack
when finally all the results were on track.

So I said to myself, "Good job, there, Jerry."
Stored procedure is where you must now, this code, bury.

Input param'ters would be apropos
We must tell the script, after all, where to go.

(Now here's another hard rhyme. This one, "teeth"
I'll fake and rhyme this line with the word "beef".)

Okay, I admit it, that line was just silly.
But all of the lines in the poem I must filly.

Alright, back on track, now, I tell myself,
This quality work won't just finish itself.

The parameters, yes, this proc must be fed.
After all it can't read what's in your head.

Provide the table and view names for this thing to work.
Then the code takes your data and just goes berserk.

Nearing the end, whew, soon I can type prose.
Need to wrap this up cleanly, then, I suppose.

If you have thoughts or comments, just give me a whistle.
To the comments you leave, I'll respond like <something that rhymes with whistle>.

If this earns MVP for me, that's out of sight!
Time to end this in rhyme, so to all a good night.

Yes, yes...  'tis a bit silly, but you read it all, didn't you?  So stop your whining and let's get down to the gory details:

The stored procedure is called spCreateAttributeLevelDeltaTable.  Take that code and paste it into a new query window in SQL Management studio.  (It'll look better once it's pasted there, too.)  Run it to create the stored procedure.

The proc takes four parameters:

• KeyColumn: The column you intend to use as the key for matching rows between the current and original data sources.
• CurrentTable: The data source for the current version of the data.  Table or view, doesn't matter.
• OriginalTable: The data source for the original version of the data.  Table or view, doesn't matter.
• DeltaTable: The name of the table that will be created and filled with the delta information.

The delta table that you specify will be dropped and re-created each time the procedure is run, so don't get too attached to it.  Add a call to this guy in the pre-processing stage of your delta sync script.  And make sure that you drop (or at least truncate) the delta table after a successful delta sync to eliminate any redundant change processing.  Here's what the call should look like:

Call Syntax: spCreateAttributeLevelDeltaTable

EXEC [dbo].[spCreateAttributeLevelDeltaTable]      @KeyColumn = N'UniqueID',      @CurrentTable = N'tDataCurrent',      @OriginalTable = N'tDataOriginal,      @DeltaTable = N'tDataAttrDelta'

Obviously - well if it's not obvious, then this is all above your head, anyway - you can peruse the SQL code and get a feel for the technique I employed.  You can also change the delta type keywords (ADD, DELETE, etc...) and column names to match your standard naming conventions.  Then a few changes to the MA delta configuration through the MIIS UI and you're good to go.

This is my <Insert Holiday Name Here> gift to you.  Use it well and please let me know how it goes.  (A little feedback wouldn't kill you, you know?)