{"id":8013,"date":"2015-05-25T11:11:51","date_gmt":"2015-05-25T15:11:51","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=8013"},"modified":"2024-05-20T13:08:06","modified_gmt":"2024-05-20T17:08:06","slug":"scd-type-2","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/","title":{"rendered":"SCD Type 2"},"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 <a href=\"http:\/\/www.iri.com\/products\/cosort\" target=\"_blank\" rel=\"noopener\">IRI CoSort<\/a>. In the <a href=\"http:\/\/www.iri.com\/products\/voracity\" target=\"_blank\" rel=\"noopener\">IRI Voracity<\/a> data integration (<a href=\"https:\/\/www.iri.com\/solutions\/data-integration\/etl\">ETL<\/a>) and data management platform <a href=\"https:\/\/www.iri.com\/products\/workbench\/voracity-gui\">GUI<\/a>, there is a fit-for-purpose SCD job creation wizard (see below) that builds the SortCL script(s) you need.<\/p>\n<p>Most SCD types use a full outer join to match records from the original data source with records in the update source based on equating a key from each. Records with matches need to be updated in the master. \u00a0Records 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 using SCD Type 2 where I am maintaining product costs. \u00a0The update is accomplished by joining with respect to field <em>ProductCode<\/em>.<\/p>\n<p><strong>Type 2 SCD<\/strong><\/p>\n<p>In this model, the current and the historical records are kept in the same file. In an active database you would likely have a surrogate key to use as the primary key for linking to the fact tables in addition to the <em>ProductCode<\/em> key which is used for the updating process. Because the current records and the historical records are contained in the same file, it is necessary to have a field that indicates if the record is the current record for the <em>ProductCode<\/em> and we need a field to indicate when the cost for the <em>ProductCode<\/em> is no longer effective. In this example we have:<\/p>\n<ul>\n<li><em>ProductCode<\/em> : This is the identifier key field.<\/li>\n<li><em>Cost<\/em>: Cost that became effective on the <em>StartDate<\/em> for the record.<\/li>\n<li><em>StartDate<\/em>: this is the date at which the cost for the record became effective.<\/li>\n<li><em>EndDate<\/em>: This is the date when the <em>Cost<\/em> in the record is no longer effective. If the Cost is still effective, then the EndDate will be set to 99991231. This is to avoid a null value in this field.<\/li>\n<li><em>Current<\/em>: Y if the cost is still in effect, N if it is not.<\/li>\n<\/ul>\n<p>The starting table already has 3 history records. They are the ones that have the value N for the field <em>Current<\/em>. The master source is called master2.dat and it contains the below data:<\/p>\n\n<table id=\"tablepress-13\" class=\"tablepress tablepress-id-13\">\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><th class=\"column-4\">EndDate<\/th><th class=\"column-5\">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\">20110228<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">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\">20120101<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">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\">20110930<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<tr class=\"row-5 odd\">\n\t<td class=\"column-1\">G101<\/td><td class=\"column-2\">21.25<\/td><td class=\"column-3\">20110501<\/td><td class=\"column-4\">20110930<\/td><td class=\"column-5\">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\">20110430<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<tr class=\"row-7 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">425.25<\/td><td class=\"column-3\">20101001<\/td><td class=\"column-4\">20110430<\/td><td class=\"column-5\">N<\/td>\n<\/tr>\n<tr class=\"row-8 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">385.25<\/td><td class=\"column-3\">20100215<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">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\">20110515<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-13 from cache -->\n<p>The update records all have the same <em>StartDate<\/em>. The update.dat source contains records with the following field 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 IRI Workbench, there is a Voracity wizard to assist in the creation of 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 2, 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-1.png\" rel=\"attachment wp-att-8685\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8685 size-full\" src=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image001-1-e1450815882945.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> will map to the <em>Master2.cost<\/em> and the update. <em>StartDate<\/em> will map to the <em>master2.StartDate<\/em>. The <strong>Flag Field<\/strong> is the field that is used to determine the record with the active Cost. That is, whether the values in the record are active or historical. In this case, the <strong>Flag Field<\/strong> is the field <em>Current<\/em>. The <strong>Flag Positive Value<\/strong> is the value in <em>Current<\/em> that determines if the <em>Cost<\/em> is the current <em>Cost<\/em>; the value is \u201cY\u201d in our example and the <strong>Flag Negative Value<\/strong> is \u201cN\u201d. End Field 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 contains the current values for a <em>ProductCode<\/em>.<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png\" rel=\"attachment wp-att-8686\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8686\" src=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.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 <strong>NOT_SORTED<\/strong> in the dropdown for <strong>Sort Order Option<\/strong> under the data source that needs to be sorted.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image005.png\" rel=\"attachment wp-att-8687\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8687 size-full\" src=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image005-e1450815981502.png\" alt=\"image005\" width=\"600\" height=\"710\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Here is the job script:<\/p>\n<pre>\/INFILE=C:\/IRI\/CoSort95\/workbench.orig\/workspace\/SCD\/SCD2\/master2.dat\r\n  \/PROCESS=DELIMITED\r\n  \/ALIAS=master2\r\n  \/FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(ENDDATE, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(CURRENT, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"')\r\n\/INFILE=C:\/IRI\/CoSort95\/workbench.orig\/workspace\/SCD\/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 master2 update WHERE MASTER2.PRODUCTCODE == UPDATE.PRODUCTCODE\r\n\r\n\/OUTFILE=master2.dat\r\n# Include only records that are being updated\r\n# Use the Cost and StartDate from the Update file \r\n  \/PROCESS=DELIMITED\r\n  \/FIELD=(MASTER2.PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(UPDATE.COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(UPDATE.STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(MASTER2.ENDDATE, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(MASTER2.CURRENT, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/INCLUDE WHERE MASTER2.PRODUCTCODE == UPDATE.PRODUCTCODE AND MASTER2.CURRENT EQ \u201cY\"\r\n\/OUTFILE=master2.dat\r\n# Change any current records that are being updated to history records\r\n# by giving the EndDate as the StartDate from the update record \r\n# and changing the field Current to N\r\n  \/PROCESS=DELIMITED\r\n  \/CONDITION=(MATCH, TEST=(MASTER2.PRODUCTCODE == UPDATE.PRODUCTCODE AND MASTER2.CURRENT EQ \"Y\"))\r\n  \/FIELD=(MASTER2.PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(MASTER2.COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(MASTER2.STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(ENDDATE_NEW, TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"', IF MATCH THEN UPDATE.STARTDATE ELSE MASTER2.ENDDATE)\r\n  \/FIELD=(CURRENT_NEW, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"', IF MATCH THEN \"N\" ELSE MASTER2.CURRENT)\r\n  \/OMIT WHERE MASTER2.PRODUCTCODE EQ \"\"\r\n\/OUTFILE=master2.dat\r\n# Add new records\r\n  \/PROCESS=DELIMITED\r\n  \/FIELD=(PRODUCTCODE, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(UPDATE.COST, TYPE=NUMERIC, POSITION=2, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(UPDATE.STARTDATE, TYPE=ASCII, POSITION=3, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(ENDDATE_NEW=\"99991231\", TYPE=ASCII, POSITION=4, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/FIELD=(CURRENT_NEW=\"Y\", TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME='\\\"')\r\n  \/INCLUDE WHERE UPDATE.PRODUCTCODE NE \"\" AND MASTER2.PRODUCTCODE EQ \"\"<\/pre>\n<p>The new master file will have these values:<\/p>\n\n<table id=\"tablepress-14\" class=\"tablepress tablepress-id-14\">\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><th class=\"column-4\">EndDate<\/th><th class=\"column-5\">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\">20110228<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">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\">20120701<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<tr class=\"row-4 even\">\n\t<td class=\"column-1\">F112<\/td><td class=\"column-2\">2365.00<\/td><td class=\"column-3\">20120101<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">N<\/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\">20110930<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<tr class=\"row-6 even\">\n\t<td class=\"column-1\">G101<\/td><td class=\"column-2\">21.25<\/td><td class=\"column-3\">20110501<\/td><td class=\"column-4\">20110930<\/td><td class=\"column-5\">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\">20120701<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/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\">20110430<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">N<\/td>\n<\/tr>\n<tr class=\"row-9 odd\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">425.25<\/td><td class=\"column-3\">20101001<\/td><td class=\"column-4\">20110430<\/td><td class=\"column-5\">N<\/td>\n<\/tr>\n<tr class=\"row-10 even\">\n\t<td class=\"column-1\">J245<\/td><td class=\"column-2\">385.25<\/td><td class=\"column-3\">20100215<\/td><td class=\"column-4\">20101001<\/td><td class=\"column-5\">N<\/td>\n<\/tr>\n<tr class=\"row-11 odd\">\n\t<td class=\"column-1\">M447<\/td><td class=\"column-2\">101.75<\/td><td class=\"column-3\">20120701<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<tr class=\"row-12 even\">\n\t<td class=\"column-1\">S022<\/td><td class=\"column-2\">101.75<\/td><td class=\"column-3\">20120701<\/td><td class=\"column-4\">99991231<\/td><td class=\"column-5\">Y<\/td>\n<\/tr>\n<tr class=\"row-13 odd\">\n\t<td class=\"column-1\">S022<\/td><td class=\"column-2\">98.75<\/td><td class=\"column-3\">20110515<\/td><td class=\"column-4\">20120701<\/td><td class=\"column-5\">N<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-14 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\/vldb-operations\/scd-type-2\/\" title=\"SCD Type 2\">Read More<\/a><\/div>\n","protected":false},"author":10,"featured_media":8686,"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,776,3],"tags":[944,370,922,107,328,101,1741,927,46,924,925,546,789,926,921,928,87,68,923],"class_list":["post-8013","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-etl","category-vldb-operations","tag-aggregates","tag-aggregation","tag-categorical-variable","tag-data-integration","tag-data-management","tag-data-warehouse","tag-database-join","tag-dimensional-data","tag-etl-tools","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 2 - 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\/vldb-operations\/scd-type-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SCD Type 2\" \/>\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\/vldb-operations\/scd-type-2\/\" \/>\n<meta property=\"og:site_name\" content=\"IRI\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-25T15:11:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-05-20T17:08:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png\" \/>\n\t<meta property=\"og:image:width\" content=\"600\" \/>\n\t<meta property=\"og:image:height\" content=\"713\" \/>\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=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/\"},\"author\":{\"name\":\"Susan Gegner\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/87be5da567628ab9396ca81170f36d63\"},\"headline\":\"SCD Type 2\",\"datePublished\":\"2015-05-25T15:11:51+00:00\",\"dateModified\":\"2024-05-20T17:08:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/\"},\"wordCount\":733,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png\",\"keywords\":[\"aggregates\",\"aggregation\",\"categorical variable\",\"data integration\",\"data management\",\"data warehouse\",\"database join\",\"dimensional data\",\"ETL tools\",\"filtering\",\"grouping\",\"IRI CoSort\",\"IRI Voracity\",\"labeling\",\"SCD\",\"scripts\",\"slowly changing dimensions\",\"SortCL\",\"statistics\"],\"articleSection\":[\"Business Intelligence (BI&#041;\",\"ETL\",\"VLDB\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/\",\"url\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/\",\"name\":\"SCD Type 2 - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png\",\"datePublished\":\"2015-05-25T15:11:51+00:00\",\"dateModified\":\"2024-05-20T17:08:06+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png\",\"width\":600,\"height\":713},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SCD Type 2\"}]},{\"@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 2 - 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\/vldb-operations\/scd-type-2\/","og_locale":"en_US","og_type":"article","og_title":"SCD Type 2","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\/vldb-operations\/scd-type-2\/","og_site_name":"IRI","article_published_time":"2015-05-25T15:11:51+00:00","article_modified_time":"2024-05-20T17:08:06+00:00","og_image":[{"width":600,"height":713,"url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png","type":"image\/png"}],"author":"Susan Gegner","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Susan Gegner","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#article","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/"},"author":{"name":"Susan Gegner","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/87be5da567628ab9396ca81170f36d63"},"headline":"SCD Type 2","datePublished":"2015-05-25T15:11:51+00:00","dateModified":"2024-05-20T17:08:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/"},"wordCount":733,"commentCount":0,"publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png","keywords":["aggregates","aggregation","categorical variable","data integration","data management","data warehouse","database join","dimensional data","ETL tools","filtering","grouping","IRI CoSort","IRI Voracity","labeling","SCD","scripts","slowly changing dimensions","SortCL","statistics"],"articleSection":["Business Intelligence (BI&#041;","ETL","VLDB"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/","url":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/","name":"SCD Type 2 - IRI","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage"},"image":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png","datePublished":"2015-05-25T15:11:51+00:00","dateModified":"2024-05-20T17:08:06+00:00","breadcrumb":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#primaryimage","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2015\/05\/image003-1-e1450815909362.png","width":600,"height":713},{"@type":"BreadcrumbList","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/scd-type-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SCD Type 2"}]},{"@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\/image003-1-e1450815909362.png","_links":{"self":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/8013"}],"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=8013"}],"version-history":[{"count":22,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/8013\/revisions"}],"predecessor-version":[{"id":17444,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/8013\/revisions\/17444"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media\/8686"}],"wp:attachment":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=8013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=8013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=8013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}