{"id":3175,"date":"2019-12-08T22:37:21","date_gmt":"2019-12-08T21:37:21","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3175"},"modified":"2019-12-08T22:37:21","modified_gmt":"2019-12-08T21:37:21","slug":"odi-and-scd","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3175","title":{"rendered":"ODI and SCD"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Oracle has a very nice ETL tool (or in Oracle parlance ELT tool). Its purpose is clear: make a easily maintainable set of workflows that show clearly the lineage from source to target.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To use ODI, we must download a zip file from Oracle. It can be found if we look for &#8220;download ODI&#8221;. We get a file (fmw_12.2.1.4.0_odi_Disk1_1of1.zip) that contains an executable whereby ODI can be installed. This is well explained in different youtube movies and on the Oracle website.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We must then install the master repository, followed by a work repository. This is also straight forward and well explained in multiple sources. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We then proceed by setting up the technical environment necessary for a SCD2 loading. We start with an input table that has four fields with one field being a key that will be used as the business key. The table is called DEPARTMENTS and looks like:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/Departments_Input-1-1.png\" alt=\"\" class=\"wp-image-3178\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The data will be written into a target table that contains the four fields from above with 4 additional technical fields: the surrogate key, a date_from and a date_to to indicate the validity period and a field that indicates whether a record is current or not. It looks like:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/Departments_Output-1-1.png\" alt=\"\" class=\"wp-image-3179\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The surrogate key now acts as a key, whereas the original business key is deprived of its role as key. this can be understood as we will collect multiple versions of a certain business key with different periods of validity. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We will use a sequence to generate values for the key.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/sequence-1-1.png\" alt=\"\" class=\"wp-image-3180\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">We must store these items in ODI. Under the heading models, the input table and the target table can be stored:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/Models-1-1.png\" alt=\"\" class=\"wp-image-3181\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">whereas the sequence is stored under projects:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/sequence-1ODI-1-1.png\" alt=\"\" class=\"wp-image-3182\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">We may then describe the properties of the target table:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/propTarget1-1-1.png\" alt=\"\" class=\"wp-image-3183\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">and defining the role of each field:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/propTarget2-1-1.png\" alt=\"\" class=\"wp-image-3184\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">We can then create a mapping:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/Mapping-1-1.png\" alt=\"\" class=\"wp-image-3185\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In a final step, we must define the knowledge modules:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/LKM-1-1.png\" alt=\"\" class=\"wp-image-3186\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">and the Integration Knowledge Module with the Check Knowledge Module:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2019\/12\/IKM-1-1.png\" alt=\"\" class=\"wp-image-3187\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Running this mapping provides the desired result.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle has a very nice ETL tool (or in Oracle parlance ELT tool). Its purpose is clear: make a easily maintainable set of workflows that show clearly the lineage from source to target. To use ODI, we must download a zip file from Oracle. It can be found if we look for &#8220;download ODI&#8221;. We [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3175","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3175"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3175\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3175"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}