{"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":"

### Click to Try Live Demo<\/a><\/h3>\n

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<\/a> for an improved demo to meet your requirement.<\/p>\n

This is also an answer to the question in this post:
\nhttp:\/\/www.sqlparser.com\/letTalk\/viewtopic.php?f=1&t=16736<\/a><\/p>\n

> ”
> \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.
> \n“<\/p><\/blockquote>\n

> Let’s take this SQL for example:<\/p>\n

>     \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>\nOur 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\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>\nThis 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\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>\nrun this demo like this:<\/p>\n\r\nJava version\uff1a java columnImpact test.sql \/c\r\nC# version\uff1a columnImpact test.sql \/v\r\n<\/pre>\nRESULT(Output):<\/p>\n\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>\nDownload this demo: C# version<\/a>, Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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&t=16736 ” 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":"\nColumn impact analysis for ETL mappings in data warehouses and marts<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n