Dynamics GP Attachments - 2 strange animals

We have been jumping into document attachment in GP.  Along the way we encountered some odd issues.  Part of this odd-ness comes from the fact that there are actually 2 separate document attachment processes in GP - one for the main GP dictionary and one for the HR dictionary.

As we have been doing most attachments in the HR/PR area I've quickly realized that this one is a bit under-documented.  For example there are plenty of citations to the main dictionary attachment tables, which range from CO00101 to CO00105.  However I can find few references to the HR attachment tables.

By using GP's Table Finder (on any GP window click Tools > Table Finder) you can find all kinds of juicy info about what you are looking at.

Notice that the box in the bottom left portion of the window shows the Tables involved in this window.  Also notice the tables for this window (HR Attachments) are HR tables.  Furthermore the "Product" is indicated to be Human Resources (HR.)  Normally I use the TableList.xls document in the GP SDK to find tables, but these are not listed there.  Oddly these tables are all in the DYNAMICS database.

HR_AttchRef (HRREF003)
HR_AttachMSTR (HRATT001)
HR_AttachStatus (HRSTA002)
HRAttachmentItems

So the Product being HR is a big clue that there are two separate processes.  This is due to the dictionary architecture of GP and the fact that HR is still a separate dictionary.  While I understand this is the easy way to do this I wonder how hard it would have been to stick with just one attachment process in the main dictionary?

This has interesting implications.  For example in GP under Administration > Setup > Company the Document Attach Setup window has some options to select related to attachments.  However these likely only apply to the main dictionary attachments.  We've not seen any of those options apply to attachments in the HR area.

Probably the worst feature of Doc Attach in GP is the removal.  It is virtually impossible to permanently delete a single attachment.  When you delete from the Attach window it moves to the Delete tab.  From there it can only be removed using the Delete Attachment Files, which is different for the main dictionary and HR.  Further that utility only allows removal with a Date cutoff.

So it's likely my next enhancement to GP will be to allow removal of individual attachments.

Using Hashbytes in ETL

We use Solver's BI360 for our internal data warehousing.  It works fairly well for us and is a nice hybrid solution to DW.  This is not a review.

As background I was trained by Solver tech's back in 2016 to configure the ETL that moves data into the BI360 DW.  They had pre-built SQL Server Integration Services (SSIS) ETL packages for our ERP solution (Dynamics GP) and we modified them to work in our environment.

One key element of the ETL was the use of the SQL BINARY_CHECKSUM (BC) function to detect changes in data.  It is not practical to compare every column in the DW to all the columns in the source data so it makes sense to hash them and compare the hashes instead.

As a Google search will quickly show BC is not wholly reliable in detecting certain types of changes.  For example I received an email from a user asking why an order line was not updated in our DW reports.  The order line was originally entered as quantity 1 for $2,900 each and was changed to quantity 10 at $290 each.  This change did not propagate to the DW and I traced it to the BC function.  BC clearly did not see the difference between these two values.

This was SERIOUSLY not acceptable so I turned again to Google to find alternatives.  While there may be others I settled on the HASHBYTES (HB) function.  It identified the difference above that BC did not.

It is common to use the CONCAT function with HB to build the hash value.  So I listed the columns inside CONCAT and used that as the input parameter to HB.  Further exploration of the use of HB revealed that blank column values might not be detected in HB.  For this reason it was recommended to use a field separator value - I chose | for this purpose.

There was a side-benefit to using this construct.  I suppose I could have tried to used CONCAT inside BC and seen if the results were different but I didn't.  CONCAT has the benefit of being able to easily compare the source and target strings.  This is important in determining if I've built the comparison correctly and was very difficult to track down with how I was using BC.

So now when my updates are not completing I can grab the source and target CONCAT strings and compare them to find where I've messed up.  This has worked great so far and I'm looking forward to using this moving forward.  All my ETL is now converted to HB and BC is no more.