none
Complex decoding in data transformations

All replies

  • I general, I go with a something that can be easily cached, like a dictionary in a static class, or for more complex scenarios, something that already caches, like the BRE.

    On the HIPAA code tables specifically, I've used a caching helper class since forever and haven't notice any adverse affects.

    And don't forget Cross Referencing.

    Sunday, January 12, 2014 2:25 PM
  • @boatseller: Thank you for discussion!

    The EDI documents are famous for the related data placed in unnested records. That is one of the main problem which makes the standard Map Editor almost useless. It was one of the main reason why I started to use this "SQL Decoding" method. Is that what you have meant as "Cross Referencing"?

    I also use caching helper classes. The "SQL Decoding" was more clear and elegant for the most complex cases. For myself the most complex cases are where the mapping takes >2 days of development.


    Leonid Ganeline [BizTalk MVP] BizTalk Development Architecture

    Sunday, January 12, 2014 4:31 PM
  • Boatseller may be refering to the BizTalk Functionality (data import application and functoids) available for the same use. Have a look @http://msdn.microsoft.com/en-us/library/ee277422(v=bts.10).aspx which details this functionality (yes, the article is for 2004 but the functionality is available in 2010 too).

    The problem being, IMHO, it is not very widely used (could be because of the lack/refencibility of the documentation).

    Regards.

    Monday, January 13, 2014 5:48 AM
  • Boatseller may be refering to the BizTalk Functionality (data import application and functoids) available for the same use. Have a look @http://msdn.microsoft.com/en-us/library/ee277422(v=bts.10).aspx which details this functionality (yes, the article is for 2004 but the functionality is available in 2010 too).

    The problem being, IMHO, it is not very widely used (could be because of the lack/refencibility of the documentation).

    Regards.

    There is the same article for BizTalk 2013. 

    I stopped use of the database functoids years ago. They complicate things for me. I prefer to use caching helper classes because of the better handling and performance.

    But the whole idea to make decoding in SQL is to use SQL set operations, which much-much more performant and clear then using maps. One query could execute decoding for many fields simultaneously and fast, no more loops through xml records.


    Leonid Ganeline [BizTalk MVP] BizTalk Development Architecture

    Monday, January 13, 2014 12:49 PM
  • Yep, BizTalk Cross Referencing as part of the Database Functoids.

    Monday, January 13, 2014 1:09 PM