{"id":8019,"date":"2015-05-25T11:13:00","date_gmt":"2015-05-25T15:13:00","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=8019"},"modified":"2024-05-20T13:20:19","modified_gmt":"2024-05-20T17:20:19","slug":"scd-type-6","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/","title":{"rendered":"SCD Type 6"},"content":{"rendered":"<p>Dimensional data that change slowly or unpredictably are captured in <a href=\"http:\/\/www.iri.com\/blog\/data-transformation2\/introduction-to-slowly-changing-dimensions-scd\/\" target=\"_blank\" rel=\"noopener\">Slowly Changing Dimensions<\/a> (SCD) analyses. In a data warehouse environment, a dimension table has a primary key that uniquely identifies each record and other pieces of information that are known as the dimensional data.<\/p>\n<p>All the update methods for the different SCD types can be accomplished using the <a href=\"http:\/\/www.iri.com\/products\/cosort\/sortcl\" target=\"_blank\" rel=\"noopener\">SortCL<\/a> program in the <a href=\"http:\/\/www.iri.com\/products\/cosort\" target=\"_blank\" rel=\"noopener\">IRI CoSort<\/a> data transformation product. In the full <a href=\"http:\/\/www.iri.com\/products\/voracity\" target=\"_blank\" rel=\"noopener\">IRI Voracity<\/a> ETL platform, there is a graphical wizard in its <a href=\"https:\/\/www.iri.com\/products\/workbench\">IRI Workbench<\/a> GUI to automate the creation of each SCD job script SortCL will run to produce the report; see the example below. Click <a href=\"https:\/\/www.iri.com\/products\/voracity\/technical-details#capabilities\">here<\/a> to learn more about Voracity data integration capabilities in general.<\/p>\n<p>Most SCD variations use a full outer join to match records from the original data source with records in the updated source based on equating a key from each. Records in the update source that do not have a match need to be added to the master.<\/p>\n<p>Here is an overview of how to update a dimensional file or table using SCD Type 6 where I am maintaining product costs. \u00a0The update is accomplished by joining with respect to the field <em>ProductCode<\/em>.<\/p>\n<p><strong>Type 6 SCD<\/strong><\/p>\n<p>Type 6 is a hybrid that is a combination of <a href=\"http:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-1\/\" target=\"_blank\" rel=\"noopener\">Type 1<\/a>, <a href=\"http:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/\" target=\"_blank\" rel=\"noopener\">Type 2<\/a>, and <a href=\"http:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-3\/\" target=\"_blank\" rel=\"noopener\">Type 3<\/a>. It is called Type 6 because 1+2+3=6. Each record will have will have the following fields:<\/p>\n<ul>\n<li><em>ProductCode<\/em>:\u00a0\u00a0This is the identifier key field.<\/li>\n<li><em>Cost<\/em>: Current cost of the product.<\/li>\n<li><em>HistoricalCost<\/em>: Cost that became effective on the StartDate for that record.<\/li>\n<li><em>StartDate<\/em>: Date on which the HistoricalCost became effective.<\/li>\n<li><em>EndDate<\/em>: Date on which the HistoricalCost for the record was no longer the current CurrentCost. If the HistoricalCost is still the current Cost, then the EndDate is 99991231.<\/li>\n<li><em>Current<\/em>: Y if Cost is still current, N if it is not<\/li>\n<\/ul>\n<p>For now let\u2019s just look at the records for <em>ProductCode<\/em> J245. Let\u2019s start when the earliest <em>HistoricalCost<\/em> was the current Cost. The HistoricalCost is the same as the Cost. There is then one record for the ProductCode J245 and it has the values shown below:<\/p>\n\n<table id=\"tablepress-20\" class=\"tablepress tablepress-id-20\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">HistoricalCost<\/th><th class=\"column-4\">StartDate<\/th><th class=\"column-5\">EndDate<\/th><th class=\"column-6\">Current<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">385.25<\/td><td class=\"column-3\">385.25<\/td><td class=\"column-4\">20100215<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-20 from cache -->\n<p>When a new Cost needs to be implemented, the values in the update record are used to add a new current record. The <em>CurrentCost<\/em> in all records with the Product Code J245 will be changed to the <em>Cost<\/em> value from the update record while the <em>HistoricalCost<\/em> stays the same for the existing records. In addition, the <em>EndDate<\/em> for the old current record is changed to the <em>StartDate<\/em> for the new current record. In the records with <em>ProductCode<\/em> J245, the <em>CurrentCost<\/em> field in the updated Master file now has the below values:<\/p>\n\n<table id=\"tablepress-21\" class=\"tablepress tablepress-id-21\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">HistoricalCost<\/th><th class=\"column-4\">StartDate<\/th><th class=\"column-5\">EndDate<\/th><th class=\"column-6\">Current<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">425.25<\/td><td class=\"column-3\">425.25<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-3 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">425.25<\/td><td class=\"column-3\">385.25<\/td><td class=\"column-4\">20100215<\/td><td class=\"column-5\">20101001<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-21 from cache -->\n<p>Now we will update with the next new cost and get the following values for the J245 records:<\/p>\n\n<table id=\"tablepress-22\" class=\"tablepress tablepress-id-22\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">HistoricalCost<\/th><th class=\"column-4\">StartDate<\/th><th class=\"column-5\">EndDate<\/th><th class=\"column-6\">Current<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">450.50<\/td><td class=\"column-3\">450.50<\/td><td class=\"column-4\">20110430<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-3 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">450.50<\/td><td class=\"column-3\">425.25<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">20110430<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-4 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">450.50<\/td><td class=\"column-3\">385.25<\/td><td class=\"column-4\">20100215<\/td><td class=\"column-5\">20101001<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-22 from cache -->\n<p>The final update creates records that have these values:<\/p>\n\n<table id=\"tablepress-23\" class=\"tablepress tablepress-id-23\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">HistoricalCost<\/th><th class=\"column-4\">StartDate<\/th><th class=\"column-5\">EndDate<\/th><th class=\"column-6\">Current<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">550.50<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-3 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">450.50<\/td><td class=\"column-4\">20110430<\/td><td class=\"column-5\">20120701<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-4 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">425.25<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">20110430<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-5 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">385.25<\/td><td class=\"column-4\">20100215<\/td><td class=\"column-5\">20101001<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-23 from cache -->\n<p>Here are the values in the master file prior to updating:<\/p>\n\n<table id=\"tablepress-24\" class=\"tablepress tablepress-id-24\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">HistoricalCost<\/th><th class=\"column-4\">StartDate<\/th><th class=\"column-5\">EndDate<\/th><th class=\"column-6\">Current<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">C123<\/td><td class=\"column-2\">125.50<\/td><td class=\"column-3\">125.50<\/td><td class=\"column-4\">20110228<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-3 odd\">\n\t<td class=\"column-1\">F112<\/td><td class=\"column-2\">2365.00<\/td><td class=\"column-3\">2365.00<\/td><td class=\"column-4\">20120101<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-4 even\">\n\t<td class=\"column-1\">G101<\/td><td class=\"column-2\">19.25<\/td><td class=\"column-3\">19.25<\/td><td class=\"column-4\">20110930<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-5 odd\">\n\t<td class=\"column-1\">G101<\/td><td class=\"column-2\">19.25<\/td><td class=\"column-3\">21.25<\/td><td class=\"column-4\">20110501<\/td><td class=\"column-5\">20110930<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-6 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">450.50<\/td><td class=\"column-3\">450.50<\/td><td class=\"column-4\">20110430<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-7 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">450.50<\/td><td class=\"column-3\">425.25<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">20110430<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-8 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">450.50<\/td><td class=\"column-3\">385.25<\/td><td class=\"column-4\">20100215<\/td><td class=\"column-5\">20101001<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-9 odd\">\n\t<td class=\"column-1\">S022<\/td><td class=\"column-2\">98.75<\/td><td class=\"column-3\">98.75<\/td><td class=\"column-4\">20110515<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-24 from cache -->\n<p>The update data will all have the same <em>StartDate<\/em>. The update.dat source contains the following values:<\/p>\n\n<table id=\"tablepress-11\" class=\"tablepress tablepress-id-11\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">StartDate<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">F112<\/td><td class=\"column-2\">2425.00<\/td><td class=\"column-3\">20120701<\/td>\n<\/tr>\n<tr class=\"row-3 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">20120701<\/td>\n<\/tr>\n<tr class=\"row-4 even\">\n\t<td class=\"column-1\">M447<\/td><td class=\"column-2\">101.75<\/td><td class=\"column-3\">20120701<\/td>\n<\/tr>\n<tr class=\"row-5 odd\">\n\t<td class=\"column-1\">S022<\/td><td class=\"column-2\">101.75<\/td><td class=\"column-3\">20120701<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-11 from cache -->\n<p>In the IRI Workbench <a href=\"https:\/\/www.iri.com\/products\/workbench\/voracity-gui\">GUI for Voracity<\/a>, there is a fit-for-purpose wizard to help you create the job scripts for updating Dimensional files and tables. This wizard is located in the Voracity dropdown on the navigation bar. First you pick the SCD type. Then the window where you select the sources that are used for processing the update is displayed. For Type 6, the target is normally the original master file or table.<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image001-4.png\" rel=\"attachment wp-att-8706\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8706 size-full\" src=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image001-4-e1450820354432.png\" alt=\"image001\" width=\"600\" height=\"589\" \/><\/a><\/p>\n<p>With the next screen, you determine how the update data is mapped and how other field or column values are set. The <em>update.Cost<\/em> field will map to <em>master6.CurrentCost<\/em> and the <em>update.StartDate<\/em> field will map to <em>master6.StartDate<\/em>. The <strong>Flag Field<\/strong> is the field that is used to determine whether the record contains the most recent field values.<\/p>\n<p>In this case that field is <em>master6.current<\/em>. If it is the most current record, then the <em>CurrentCost<\/em> field and the <em>HistoryCost<\/em> field will have the same values. The <strong>Flag Positive Value<\/strong> is the value in <em>master6.Current<\/em> that determines that the field values are the most recent values for a <em>ProductCode<\/em>. When that is the case, the value is \u201cY\u201d in our example and the <strong>Flag Negative Value<\/strong> is \u201cN\u201d.<\/p>\n<p><strong>End Field<\/strong> contains the name of the field that holds the value used to determine when the Cost for the record is no longer effective and <em>Master.EndDate<\/em> holds that value. <strong>End Value<\/strong> is used as the value for <strong>End Field<\/strong> when the record is current.<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-4.png\" rel=\"attachment wp-att-8707\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8707 size-full\" src=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-4-e1450820512908.png\" alt=\"image003\" width=\"600\" height=\"713\" \/><\/a><\/p>\n<p>The next screen is for defining the join performed with the master and update sources. You should note that both sources must be ordered with respect to <em>ProductCode<\/em>. If they are not, then you need to select NOT_SORTED in the dropdown for <strong>Sort Order Option<\/strong> under the data source that needs to be sorted.<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image007.png\" rel=\"attachment wp-att-8709\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8709 size-full\" src=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image007-e1450820635148.png\" alt=\"image007\" width=\"600\" height=\"713\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Here is the first job script:<\/p>\n<pre>\/INFILE=master6.dat\r\n    \/PROCESS=DELIMITED\r\n    \/ALIAS=master6\r\n    \/FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(CURRENTCOST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(HISTORICALCOST, TYPE=NUMERIC, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(STARTDATE, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(ENDDATE, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(CURRENT, TYPE=ASCII, POSITION=6, SEPARATOR=\",\", FRAME='\\\"')\r\n\/INFILE=update.dat\r\n    \/PROCESS=DELIMITED\r\n    \/ALIAS=update\r\n    \/FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(COST, TYPE=ASCII, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n\r\n\/JOIN FULL_OUTER NOT_SORTED master6 update WHERE MASTER6.PRODUCTCODE == UPDATE.PRODUCTCODE\r\n\r\n\/OUTFILE=master6.dat\r\n# Make changes to records that have the same ProductCode\r\n# as records in the update file \r\n    \/PROCESS=DELIMITED\r\n    \/FIELD=(MASTER6.PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(UPDATE.COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.HISTORICALCOST, TYPE=NUMERIC, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.STARTDATE, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(ENDDATE_NEW, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"', IF MASTER6.CURRENT EQ \"Y\" THEN UPDATE.STARTDATE ELSE MASTER6.ENDDATE)\r\n    \/FIELD=(CURRENT_NEW=\"N\", TYPE=ASCII, POSITION=6, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/INCLUDE WHERE MASTER6.PRODUCTCODE == UPDATE.PRODUCTCODE\r\n\r\n\/OUTFILE=master6.dat\r\n# Keep the records that have no updates \r\n    \/PROCESS=DELIMITED\r\n    \/FIELD=(MASTER6.PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.CURRENTCOST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.HISTORICALCOST, TYPE=NUMERIC, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.STARTDATE, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.ENDDATE, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(MASTER6.CURRENT, TYPE=ASCII, POSITION=6, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/OMIT WHERE MASTER6.PRODUCTCODE == UPDATE.PRODUCTCODE\r\n    \/OMIT WHERE MASTER6.PRODUCTCODE EQ \"\"\r\n\r\n\/OUTFILE=master6.dat\r\n# Add the records with new product codes\r\n    \/PROCESS=DELIMITED\r\n    \/FIELD=(UPDATE.PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(UPDATE.COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(UPDATE.COST, TYPE=NUMERIC, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(UPDATE.STARTDATE, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(ENDDATE_NEW=\"99991231\", TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/FIELD=(CURRENT_NEW=\"Y\", TYPE=ASCII, POSITION=6, SEPARATOR=\",\", FRAME='\\\"')\r\n    \/OMIT WHERE UPDATE.PRODUCTCODE EQ \"\"\r\n    \/INCLUDE WHERE UPDATE.PRODUCTCODE<\/pre>\n<p>To review,<\/p>\n<ul>\n<li>The value for <em>CurrentCost<\/em> will be the same for all records that have a common <em>ProductCode<\/em><\/li>\n<li><em>StartDate<\/em> is the date when the <em>HistoricalCost<\/em> became effective<\/li>\n<li>For the most current record of a <em>ProductCode,<\/em>\n<ul>\n<li>the values for <em>CurrentCost<\/em> and <em>HistoricalCost<\/em> are the same<\/li>\n<li>the\u00a0<em>EndDate<\/em> is 99991231<\/li>\n<li>The value in <em>Current<\/em> is Y<\/li>\n<\/ul>\n<\/li>\n<li>For the records that are not the current record\n<ul>\n<li><em>EndDate<\/em> is the date when the next more recent <em>Cost<\/em> became effective<\/li>\n<li>The field <em>Current<\/em> has N for the value<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>The new master file will not be sorted because the new current master records were added to the bottom of the master file which will have values like this:<\/p>\n\n<table id=\"tablepress-25\" class=\"tablepress tablepress-id-25\">\n<thead>\n<tr class=\"row-1 odd\">\n\t<th class=\"column-1\">ProductCode<\/th><th class=\"column-2\">Cost<\/th><th class=\"column-3\">HistoricalCost<\/th><th class=\"column-4\">StartDate<\/th><th class=\"column-5\">EndDate<\/th><th class=\"column-6\">Current<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-hover\">\n<tr class=\"row-2 even\">\n\t<td class=\"column-1\">C123<\/td><td class=\"column-2\">125.50<\/td><td class=\"column-3\">125.50<\/td><td class=\"column-4\">20110228<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-3 odd\">\n\t<td class=\"column-1\">F112<\/td><td class=\"column-2\">2425.00<\/td><td class=\"column-3\">2365.00<\/td><td class=\"column-4\">20120101<\/td><td class=\"column-5\">20120701<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-4 even\">\n\t<td class=\"column-1\">G101<\/td><td class=\"column-2\">19.25<\/td><td class=\"column-3\">19.25<\/td><td class=\"column-4\">20110930<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-5 odd\">\n\t<td class=\"column-1\">G101<\/td><td class=\"column-2\">19.25<\/td><td class=\"column-3\">21.25<\/td><td class=\"column-4\">20110501<\/td><td class=\"column-5\">20110930<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-6 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">450.50<\/td><td class=\"column-4\">20110430<\/td><td class=\"column-5\">20120701<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-7 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">425.25<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">20110430<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-8 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">385.25<\/td><td class=\"column-4\">20100215<\/td><td class=\"column-5\">20101001<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-9 odd\">\n\t<td class=\"column-1\">S022<\/td><td class=\"column-2\">101.75<\/td><td class=\"column-3\">98.75<\/td><td class=\"column-4\">20110515<\/td><td class=\"column-5\">20120701<\/td><td class=\"column-6\">N<\/td>\n<\/tr>\n<tr class=\"row-10 even\">\n\t<td class=\"column-1\">F112<\/td><td class=\"column-2\">2425.00<\/td><td class=\"column-3\">2425.00<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-11 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">550.50<\/td><td class=\"column-3\">550.50<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-12 even\">\n\t<td class=\"column-1\">M447<\/td><td class=\"column-2\">139.25<\/td><td class=\"column-3\">139.25<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<tr class=\"row-13 odd\">\n\t<td class=\"column-1\">S022<\/td><td class=\"column-2\">101.75<\/td><td class=\"column-3\">101.75<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">99991231<\/td><td class=\"column-6\">Y<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-25 from cache -->\n","protected":false},"excerpt":{"rendered":"<p>Dimensional data that change slowly or unpredictably are captured in Slowly Changing Dimensions (SCD) analyses. In a data warehouse environment, a dimension table has a primary key that uniquely identifies each record and other pieces of information that are known as the dimensional data. All the update methods for the different SCD types can be<\/p>\n<div><a class=\"btn-filled btn\" href=\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\" title=\"SCD Type 6\">Read More<\/a><\/div>\n","protected":false},"author":10,"featured_media":11675,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[32,1,776,3],"tags":[922,328,101,927,924,925,546,789,926,921,928,87,68,923],"class_list":["post-8019","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-data-transformation2","category-etl","category-vldb-operations","tag-categorical-variable","tag-data-management","tag-data-warehouse","tag-dimensional-data","tag-filtering","tag-grouping","tag-iri-cosort","tag-iri-voracity","tag-labeling","tag-scd","tag-scripts","tag-slowly-changing-dimensions","tag-sortcl","tag-statistics"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v23.4 (Yoast SEO v23.4) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SCD Type 6 - IRI<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SCD Type 6\" \/>\n<meta property=\"og:description\" content=\"Dimensional data that change slowly or unpredictably are captured in Slowly Changing Dimensions (SCD) analyses. In a data warehouse environment, a dimension table has a primary key that uniquely identifies each record and other pieces of information that are known as the dimensional data. All the update methods for the different SCD types can beRead More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\" \/>\n<meta property=\"og:site_name\" content=\"IRI\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-25T15:13:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-05-20T17:20:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png\" \/>\n\t<meta property=\"og:image:width\" content=\"664\" \/>\n\t<meta property=\"og:image:height\" content=\"789\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Susan Gegner\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Susan Gegner\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\"},\"author\":{\"name\":\"Susan Gegner\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/87be5da567628ab9396ca81170f36d63\"},\"headline\":\"SCD Type 6\",\"datePublished\":\"2015-05-25T15:13:00+00:00\",\"dateModified\":\"2024-05-20T17:20:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\"},\"wordCount\":928,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png\",\"keywords\":[\"categorical variable\",\"data management\",\"data warehouse\",\"dimensional data\",\"filtering\",\"grouping\",\"IRI CoSort\",\"IRI Voracity\",\"labeling\",\"SCD\",\"scripts\",\"slowly changing dimensions\",\"SortCL\",\"statistics\"],\"articleSection\":[\"Business Intelligence (BI&#041;\",\"Data Transformation\",\"ETL\",\"VLDB\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\",\"url\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\",\"name\":\"SCD Type 6 - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png\",\"datePublished\":\"2015-05-25T15:13:00+00:00\",\"dateModified\":\"2024-05-20T17:20:19+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png\",\"width\":664,\"height\":789},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SCD Type 6\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.iri.com\/blog\/#website\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"name\":\"IRI\",\"description\":\"Total Data Management Blog\",\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.iri.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\",\"name\":\"IRI\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"width\":750,\"height\":206,\"caption\":\"IRI\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/87be5da567628ab9396ca81170f36d63\",\"name\":\"Susan Gegner\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/2b1ca5592a65d44483351292cf1ae00a?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/2b1ca5592a65d44483351292cf1ae00a?s=96&d=blank&r=g\",\"caption\":\"Susan Gegner\"},\"url\":\"https:\/\/www.iri.com\/blog\/author\/susang\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SCD Type 6 - IRI","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/","og_locale":"en_US","og_type":"article","og_title":"SCD Type 6","og_description":"Dimensional data that change slowly or unpredictably are captured in Slowly Changing Dimensions (SCD) analyses. In a data warehouse environment, a dimension table has a primary key that uniquely identifies each record and other pieces of information that are known as the dimensional data. All the update methods for the different SCD types can beRead More","og_url":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/","og_site_name":"IRI","article_published_time":"2015-05-25T15:13:00+00:00","article_modified_time":"2024-05-20T17:20:19+00:00","og_image":[{"width":664,"height":789,"url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png","type":"image\/png"}],"author":"Susan Gegner","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Susan Gegner","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#article","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/"},"author":{"name":"Susan Gegner","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/87be5da567628ab9396ca81170f36d63"},"headline":"SCD Type 6","datePublished":"2015-05-25T15:13:00+00:00","dateModified":"2024-05-20T17:20:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/"},"wordCount":928,"commentCount":0,"publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png","keywords":["categorical variable","data management","data warehouse","dimensional data","filtering","grouping","IRI CoSort","IRI Voracity","labeling","SCD","scripts","slowly changing dimensions","SortCL","statistics"],"articleSection":["Business Intelligence (BI&#041;","Data Transformation","ETL","VLDB"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/","url":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/","name":"SCD Type 6 - IRI","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage"},"image":{"@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png","datePublished":"2015-05-25T15:13:00+00:00","dateModified":"2024-05-20T17:20:19+00:00","breadcrumb":{"@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#primaryimage","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png","width":664,"height":789},{"@type":"BreadcrumbList","@id":"https:\/\/www.iri.com\/blog\/data-transformation2\/scd-type-6\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SCD Type 6"}]},{"@type":"WebSite","@id":"https:\/\/www.iri.com\/blog\/#website","url":"https:\/\/www.iri.com\/blog\/","name":"IRI","description":"Total Data Management Blog","publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.iri.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.iri.com\/blog\/#organization","name":"IRI","url":"https:\/\/www.iri.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","width":750,"height":206,"caption":"IRI"},"image":{"@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/87be5da567628ab9396ca81170f36d63","name":"Susan Gegner","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/2b1ca5592a65d44483351292cf1ae00a?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/2b1ca5592a65d44483351292cf1ae00a?s=96&d=blank&r=g","caption":"Susan Gegner"},"url":"https:\/\/www.iri.com\/blog\/author\/susang\/"}]}},"jetpack_featured_media_url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/scd6.png","_links":{"self":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/8019"}],"collection":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/comments?post=8019"}],"version-history":[{"count":20,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/8019\/revisions"}],"predecessor-version":[{"id":17450,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/8019\/revisions\/17450"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media\/11675"}],"wp:attachment":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=8019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=8019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=8019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}