{"id":1268,"date":"2011-11-02T22:50:16","date_gmt":"2011-11-03T03:50:16","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1268"},"modified":"2015-07-25T06:07:18","modified_gmt":"2015-07-25T11:07:18","slug":"column-impact-analysis","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/","title":{"rendered":"Column impact analysis"},"content":{"rendered":"<h3><a href=\"http:\/\/www.sqlparser.com\/livedemo.php?demo_id=columnImpact\">Click to Try Live Demo<\/a><\/h3>\n<p>This demo illustrates how to use general sql parser to analyze column level dataflow in a complex SQL query with join clause and nested subquery. You can modify this demo to fit your own need, or just <a href=\"mailto:info@sqlparser.com\">contact us<\/a> for an improved demo to meet your requirement.<\/p>\n<p>This is also an answer to the question in this post:<br \/>\n<a href=\"http:\/\/www.sqlparser.com\/letTalk\/viewtopic.php?f=1&#038;t=16736\" title=\"general sql parser questions\" target=\"_blank\">http:\/\/www.sqlparser.com\/letTalk\/viewtopic.php?f=1&#038;t=16736<\/a><\/p>\n<blockquote><p>&#8221;<br \/>\nI wonder whether sqlparser provides the capability to match source and target columns to extract the column-level dataflow (lineage), even if the sources are in a subquery. This would be very useful to provide impact analysis feature to ETL mappings in data warehouses and marts.<br \/>\n&#8220;<\/p><\/blockquote>\n<p>Let&#8217;s take this SQL for example:<\/p>\n<pre>\r\nSELECT a.deptno \"Department\", \r\n       a.num_emp\/b.total_count \"Employees\", \r\n       a.sal_sum\/b.total_sal \"Salary\"\r\n  FROM\r\n(SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum\r\n    FROM scott.emp\r\n    GROUP BY deptno) a,\r\n(SELECT COUNT(*) total_count, SUM(sal) total_sal\r\n    FROM scott.emp) b\r\n<\/pre>\n<p>Our demo can trace the relationship between aliases in top-level select clause and table\/view columns in bottom levels, And this demo will let you know that some of the target columns are influenced by a source table itself, for example by the number of records in previous sample SQL as well.<\/p>\n<pre>\r\nSearch a.deptno\r\n --> scott.emp.deptno\r\n\r\nSearch a.num_emp\r\n --> num_emp(alias)\r\n --> aggregate function COUNT(* ) \r\n --> table scott.emp \r\n --> emp.deptno(group by)\r\n --> scott.emp.deptno\r\n\r\nSearch b.total_count\r\n --> total_count(alias)\r\n --> aggregate function COUNT(* ) \r\n --> table scott.emp \r\n\r\nSearch a.sal_sum\r\n --> sal_sum(alias)\r\n --> aggregate function SUM(SAL) \r\n --> table scott.emp \r\n --> scott.emp.SAL\r\n --> emp.deptno(group by)\r\n --> scott.emp.deptno\r\n\r\nSearch b.total_sal\r\n --> total_sal(alias)\r\n --> aggregate function SUM(sal) \r\n --> table scott.emp \r\n --> scott.emp.sal\r\n<\/pre>\n<p>This demo can trace data lineage in views as well. For each column in a view it traces the name or names of the source columns and the expression used (if any). Take this SQL for example:<\/p>\n<pre>\r\nCREATE VIEW vIMWhseBinID AS \r\nSELECT DISTINCT timWhseBin.WhseBinKey,\r\ntimWhseBin.WhseKey,\r\ntimWarehouse.WhseKey AS WhseKey2\r\nFROM timWhseBin\r\nJOIN timWarehouse ON timWhseBin.WhseKey = timWarehouse.WhseKey\r\n<\/pre>\n<p>run this demo like this:<\/p>\n<pre>\r\nJava version\uff1a java columnImpact test.sql \/c\r\nC# version\uff1a columnImpact test.sql \/v\r\n<\/pre>\n<p>RESULT(Output):<\/p>\n<pre>\r\nview=vIMWhseBinID    column=WhseBinKey     source=timWhseBin.WhseBinKey   expression=\r\nview=vIMWhseBinID    column=WhseKey        source=timWhseBin.WhseKey      expression=\r\nview=vIMWhseBinID    column=WhseKey2       source=timWarehouse.WhseKey      expression=\r\n<\/pre>\n<p>Download this demo: <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/columnImpact\/columnImpact.zip\">C# version<\/a>, <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/java\/columnImpact\/ColumnImpact.zip\">Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Click to Try Live Demo This demo illustrates how to use general sql parser to analyze column level dataflow in a complex SQL query with join clause and nested subquery. You can modify this demo to fit your own need, or just contact us for an improved demo to meet your requirement. This is also an answer to the question in this post: http:\/\/www.sqlparser.com\/letTalk\/viewtopic.php?f=1&#038;t=16736 &#8221; I wonder whether sqlparser provides the capability to match source and target columns to extract the column-level dataflow (lineage), even if the sources are in a subquery. This would be very useful to provide impact\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Column impact analysis for ETL mappings in data warehouses and marts<\/title>\n<meta name=\"description\" content=\"Column impact analysis for ETL mappings in data warehouses and marts\" \/>\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.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Column impact analysis for ETL mappings in data warehouses and marts\" \/>\n<meta property=\"og:description\" content=\"Column impact analysis for ETL mappings in data warehouses and marts\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2015-07-25T11:07:18+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/\",\"name\":\"Column impact analysis for ETL mappings in data warehouses and marts\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2011-11-03T03:50:16+00:00\",\"dateModified\":\"2015-07-25T11:07:18+00:00\",\"description\":\"Column impact analysis for ETL mappings in data warehouses and marts\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Column impact analysis\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Column impact analysis for ETL mappings in data warehouses and marts","description":"Column impact analysis for ETL mappings in data warehouses and marts","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.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/","og_locale":"en_US","og_type":"article","og_title":"Column impact analysis for ETL mappings in data warehouses and marts","og_description":"Column impact analysis for ETL mappings in data warehouses and marts","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/","og_site_name":"SQL and Data Blog","article_modified_time":"2015-07-25T11:07:18+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/","name":"Column impact analysis for ETL mappings in data warehouses and marts","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2011-11-03T03:50:16+00:00","dateModified":"2015-07-25T11:07:18+00:00","description":"Column impact analysis for ETL mappings in data warehouses and marts","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/column-impact-analysis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Column impact analysis"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1268"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=1268"}],"version-history":[{"count":18,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1268\/revisions"}],"predecessor-version":[{"id":1702,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1268\/revisions\/1702"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=1268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}