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?)

The Same, But Different

Okay...  A short while ago I posted a little application I wrote called the MIIS Delta View Creator.  It's neat and clean and does what it says it does.  But I also have another method up my sleeve.  (Well, it might be up my sleeve if I kept my sleeve in the hard drive bay...)  Anyway, here's another version implemented as a SQL Management Studio template.

Create the template:

• In SSMS, enable the Template Explorer (Ctrl + Alt +T)
• If desired (and it is desired) create a new template folder.
  • Right click on the SQL Server Templates root folder and select "New" and then, yes...  "Folder"
  • Type the new name for the folder.  I suggest either "Rosencrantz" or "MIIS Custom", but this is all you.
• Right click on your new template folder and create a new template.
  • I suggest any name other than, "New SQL Server Template".
  • I called mine, "Create Basic MIIS Delta View Components" and I have a reference to that in the comments of the script.
• Now, right click your new template and select "Edit".
• Paste this code into the query editor window and save the template.

Use the template:

• In SSMS, double click the template.
  • A copy of it will open in a new query editor window.
  • If you want to edit the template itself, right click it and select "Edit".
• With the new query editor window as the active window, press Ctrl + Shift + M.
• Enter the appropriate value for each parameter and click "OK".
  • You now have a script that, when run, will create the necessary delta view components.

Okay...  enough with the bullets.

• Maybe just one more...

The resulting script will create a basic delta view setup.  All that's necessary to get started is to have one existing table/view.  This table should represent the "Current" view of the data.  The "Original" table and the delta view will be created.  If there are objects with the specified names already in the database, they will be dropped!

The script also creates a couple of additional components:  A table copy stored procedure and a post process stored procedure.  These components are useful for the way we implement a lot of the MIIS functionality.  Read through the code and all will be revealed.  If you have questions, comments or suggestions, please feel free to post them here.

All I ask is: if my readers (yes, both of you) use this code, please note where you got it from in any altered versions that you mangle (create).

Cider, Workflows and Just Enough Knowledge...

As I recall, it was out in the country somewhere...  The kind of place you can't find without having been there before.  The sweet, musty scent of a single room log cabin that hasn't seen a carbon based life form larger than a raccoon for well over 20 years.  The only light coming from the soft blue glow of the power L.E.D from the wireless access point... Hmm...  Wait a second. I'm not remembering quite right...  Might have been a room at the DoubleTree in San Jose.

On the road and armed with Peanut M&M's, a case of Hornsby's Hard Apple Cider, and some book we'd just purchased from the local techno-geek bookshop, Mr. Turner and I decided to implement our first Windows Workflow Based solution at a client.  Start simple, was our motto.  So we did.  We took one of simplest concepts we could come up with and decided to implement it in the most complex and convoluted way possible.  All in the name of progress.  We had chosen to build a workflow based delayed events Management Agent for MIIS.

Brad's knowledge of the inner workings of MIIS is intense, and I have the ability to write code so concise that a popular compression algorithm, in a fit of jealousy and frustration, locked itself in the inner sanctum of my display driver and refuses to come out.  (The proof is in the dead pixel in the middle of the screen on my laptop.)  And while the intricacies of marrying these two skill sets have evoked solicitations from the Hollywood screenwriting elite, that's not what this blog is about.  That's just a bit of background.  The history.  A peek behind the wizard's curtain, if you will...

What I gathered you all here to talk about is this:  Never drop a goldfish into a glass of vodka.  Okay?  No, not even if it's the good vodka like the kind that comes in the fancy frosted bottle with that guy's face that you can see through the small patch of clear glass on the inside of the other side of the bottle.  (How do they get that guy in there?)

And speaking of goldfish, here's a little story on how I was blind-sided by a .Net assembly versioning conflict...

I did build that workflow engine.  'Twas my first foray into the world of Windows Workflow Foundation and I had just enough knowledge to make it all work, but not enough to understand what I was doing.  The solution consisted of four projects: The workflow engine service, the workflows assembly, a utility project and a setup project.  The workflows, themselves, were pretty simple: Delay, Notify, Delay.  Short and sweet.  The workflow engine was implemented as a service and contains the standard SQL based persistence service, the standard tracking service and an External Data Exchange service to allow the workflows to notify the host of certain events.  All of the versioning was set to auto increment the build and revision.

After creating the workflows and getting the service built and running and everything tested to a point of satisfaction, the service was rolled out and a production WorkflowMA was born.  And it was good.  But not good enough.  Had to tweak the service a bit and ended up having to deploy a couple of replacement versions.

These were long running workflows, delaying for up to 90 days at a time.  So it was quite a while before I was enlightened unto the error of my ways.  After a while we realized that things were not processing as they did in testing.  (You see, in testing, I used 90 seconds, not 90 days.  Deadlines, you know...)  So I'll skip past the details here and get to the stuff that matters...

I used strong named assemblies.  That's important.  If I hadn't, I might not have had any of these issues.  But then I couldn't have deployed signed code, either.

What was happening was that workflows were getting stuck in the persistence service.  While I didn't make changes to the workflows, I had made updates to the service.  But I always redeployed the full setup.  And when I did a full solution recompile, I inadvertently changed the version numbers of the workflows.  So when the persistence service tried to re-hydrate them, it failed as the appropriately versioned workflow classes were not available.  .Net will not automatically use a newer version of a strong named assembly.  They just sat there in the persistence store.  Orphans.

How to fix it?  Well, my first attempt at a workaround was to use a binding redirect in the app.config:

Code

<configuration>   <runtime>     <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">       <dependentAssembly>         <assemblyIdentity name="MIISWorkflowLib" publicKeyToken="123abc456def7890" culture="neutral" />         <bindingRedirect oldVersion="1.0.0.0-1.0.9999.0" newVersion="1.1.0.0" />       </dependentAssembly>     </assemblyBinding>   </runtime> </configuration>

Just specify a version range big enough to cover all the previous versions and redirect them to the one new and latest version.

Awesome!  The old workflows rehydrated using the new assembly.  But then the tracking service complained:

Value cannot be null. Parameter name: profile

There was a mismatch in the profile information in the serialized workflow and the tracking database records.  Another exception.  It was like the parents had come back to claim their orphaned little workflow, but they didn't have proper ID.  Couldn't prove they were they rightful owners, so the WWF authorities intercepted the happy reunion, again leaving the workflow cold and naked in the persistence store.  (Why naked, you ask?  More dramatic.) 

How to fix it?  Well, you can look at the tracking database (examine the WorkflowDefinition column of the Workflow table) and see the version numbers of the workflows that it's cataloged.  (If the WorkflowTypeID doesn't match any records in the WorkflowInstance table, you can probably skip that version.  No workflows were actually created from that assembly.)  Recompile a workflow assembly for each version, updating the AssemblyVersion before compile and copying the compiled assembly to a subfolder structure under the host's startup folder.  Then use codebase hints in the app.config file to tell the host where each version of the assembly lives.  (For some reason, I didn't use the GAC.  If you do, you can just dump each version there and be done with it.  But my solution required more typing, so it must be better.)

Folder Hierarchy:

 

app.config:

Code

<configuration>   <runtime>     <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">         <dependentAssembly>             <assemblyIdentity name="MIISWorkflowLib" publicKeyToken="123abc456def7890" culture="neutral" version="1.0.2701.23729" />             <codeBase version="1.0.2701.23729" href="Workflows\1_0_2701_23729\MIISWorkflowLib.dll" />         </dependentAssembly>         <dependentAssembly>             <assemblyIdentity name="MIISWorkflowLib" publicKeyToken="123abc456def7890" culture="neutral" version="1.0.0.0" />             <codeBase version="1.0.0.0" href="Workflows\1_0_0_0\MIISWorkflowLib.dll" />         </dependentAssembly>     </assemblyBinding>   </runtime> </configuration>

Awesomer!  The workflows can re-hydrate and that finicky tracking service is no longer complaining.  But then one more little demon wielded its ugly head.  The ExternalDataExchange service.  See, I was using that to allow the workflows to chat with the host application.  When I put this project together I only had one workflow assembly.  I added the required interface definition to that project and just referenced it from the workflow host application.  In Visual Studio, I set a reference to the workflow project in the host application project.  This allowed me access to that interface.  But post-fix there were multiple versions of the workflow assembly.  You can't easily reference more than one assembly with the same name.  (With Reflection, all things are possible...  Well, many things.)  And I needed a reference to an interface my class would implement, not a class already defined in the other assembly.

How to fix it?  Well, this took me while to figure out.  Not because it's an especially difficult problem, really, but because I expected it to be.  And if you're not looking for a simple solution, I can promise that you won't find one.  All I had to do here was separate the interface definition from the workflow definition.  I couldn't add the interface to the host app because the host app already had a reference to the workflow app.  If I put the interface there, the workflow app would need a reference to the host app.  That's what they call a circular dependency and, in some states, that's a felony.  (Well, Visual Studio won't let you do it, anyway.)  So enter project number five, consisting of only the interface definition.  Reference the new project from both the host application and the workflow assembly and... Viola! A complete and working solution.  (Awesomest!)

Now...  The real lesson here is not how to fix this situation - it's that you should avoid it all to begin with.  I was so focused on messing with the new workflow gizmos that I just didn't think through the peripheral .Net stuff.  Lesson learned and In the solution, now, the workflow assembly project no longer auto-increments it's version.  Yeah...  It would have been that simple.

Delta Dawn...

Hey, there...  How'ya doin'?  Good...  Good...  Been a while, I know, but I finally have something new to cast off into the blogoshpere.  Here's my first attempt at shining some light on creating MIIS delta views.  Well, perhaps "shining some light on" is not the proper metaphor, but it does tie in nicely with the title and, yes... the theme music.  (That's good old fashioned Barbershop harmony, that is.  If you're into that sort of thing, there's a lot more of it here.  Don't be shy, gentlemen, seek out your local chapter of the Barbershop Harmony Society today!)

Introducing the MIIS Delta View Creation Wizard!  (version 0.9 - beta type software)  A handy little utility to automate the creation of standard delta views.  The interface is pretty straight forward:

 

Download it.  Try it.  Give it as a unique holiday gift.  And by all means, please post your comments and suggestions.

"When there's darkness on the delta..." use the MDVC and brighten up your day!  (If you don't get that, you didn't listen to the theme music.)