{"id":78,"date":"2013-02-05T22:08:41","date_gmt":"2013-02-05T21:08:41","guid":{"rendered":"http:\/\/konsilium.de\/blog\/?p=78"},"modified":"2022-07-07T21:43:38","modified_gmt":"2022-07-07T19:43:38","slug":"sql-tipp-001-data-profiling","status":"publish","type":"post","link":"https:\/\/konsilium.de\/blog\/2013\/02\/05\/sql-tipp-001-data-profiling\/","title":{"rendered":"SQL Tipp #001 &#8211; Data Profiling"},"content":{"rendered":"\n<p>Eine typische T\u00e4tigkeit im Rahmen der fachlichen Konzeption ist <strong>auch<\/strong> die Systemanalyse &#8211; auch wenn viele Autoren oder Experten anderer Meinung sind und eher eine strikte Trennung zwischen Business Analyse und System Analyse f\u00fcr sinnvoll erachten. Meiner Meinung nach macht es keinen Sinn, eine scharfe Trennlinie zu ziehen. Bereits in der Phase der Anforderungsanalyse und der entsprechenden Dokumentation (Fachkonzept) ist es \u00e4u\u00dferst hilfreich und angebracht in Datenvorr\u00e4te &#8222;hineinzuschauen&#8220;, um bereits zu einem sehr fr\u00fchen Zeitpunkt die Machbarkeit zu pr\u00fcfen und gegebenenfalls darauf zu reagieren. So werden keine &#8222;Luftschl\u00f6sser&#8220; gebaut und falsche oder etwa \u00fcberzogene Erwartungshaltungen entstehen erst gar nicht. Nat\u00fcrlich sollte man in der Phase der Fachkonzeption die Aufw\u00e4nde f\u00fcr die Systemanalyse nicht ausufern lassen. Aber oftmals reicht ja bereits eine Stichprobe, um ein &#8222;Gef\u00fchl&#8220; f\u00fcr die Daten zu bekommen. Ideal ist es, wenn die Quellsystemdaten bereits in einem relationalen Datenbank-System vorliegen, so dass mit Standard SQL Abfragen darauf zugegriffen werden kann. Vielleicht steht ja auch noch ein komfortabler SQL Editor zur Verf\u00fcgung. In meinen Augen ein sehr wichtiges Werkzeug f\u00fcr jeden BI Business Analysten.<\/p>\n\n\n\n<p>Soweit so gut. Der Zugriff auf die Daten ist also gegeben. Jetzt geht es darum, sich schnell einen \u00dcberblick \u00fcber die Daten zu verschaffen. Typische Fragen hierbei sind:<\/p>\n\n\n\n<ul><li>Welche Entit\u00e4ten enthalten welche Datenstrukturen?<\/li><li>Was ist der fachlich eindeutige Schl\u00fcssel einer Entit\u00e4t (Business Key)<\/li><li>Wie stehen die Entit\u00e4ten in Beziehung zueinander?<\/li><li>Welche Daten (Auspr\u00e4gungen) enthalten die einzelnen Attribute (Tabellen-Spalten)?<\/li><li>Wie verteilen sich die einzelnen Auspr\u00e4gungen prozentual? (Auspr\u00e4gungskardinalit\u00e4t)<\/li><li>Welche Ausrei\u00dfer gibt es?<\/li><\/ul>\n\n\n\n<p>Das wissen um die Kardinalit\u00e4t ist bespielsweise sehr wichtig f\u00fcr eine korrekte und performante Datenmodellierung. Die obigen Fragen geh\u00f6ren zum Themengebiet des &#8222;<strong>Data Profiling<\/strong>&#8222;. Spezielle Tools hierf\u00fcr sind auf dem Software-Markt erh\u00e4ltlich. Informatica bietet zum Beispiel sein Produkt &#8222;Data Analyst&#8220; an, fr\u00fcher bekannt unter &#8222;Informatica Data Explorer&#8220; (IDE). Es bietet sich an, den Fachbereich in den Profiling Prozess direkt miteinzubinden bzw. auch ihm das Profiling Werkzeug zur Verf\u00fcgung zu stellen. Schlie\u00dflich kennt er die Daten in der Regel am besten. Und falls nicht, so lernt er sie durch das Profiling sehr gut kennen. Leider ist ein dediziertes Profiling Tool nicht immer vorhanden &#8211; und an dieser Stelle kommt der heutige SQL Tipp zum Zug. Das nachfolgende SQL Skript liefert einen schnellen \u00dcberblick \u00fcber die Daten &#8211; \u00e4hnlich wie ein professionelles Profiling Tool es tun w\u00fcrde.<\/p>\n\n\n\n<p>Nun zum Skript: Die Grundidee ist, das Data Dictionary der Datenbank zu verwenden, um sich das Profiling Skript dynamisch generieren zu lassen. D.h. die einzelnen Spalten einer Tabelle werden nicht manuell f\u00fcr jede zu untersuchende Tabelle angepasst, sondern aus dem Dictionary geholt.<\/p>\n\n\n\n<p><strong>Schritt 1<\/strong>: Dynamischer Aufbau des Profiling SQLs. Eingabe Parameter sind Schema Name (DM_ALL) und der Tabellenname (td_d_par_org_mm) in Zeile 4<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- dynamisch Query erzeugen\nWITH \n&nbsp;tmptable (tmp_schema, tmp_table, tmp_count) AS \n&nbsp;(VALUES('mySchema', 'myTable', (select count (*) from mySchema.myTable as tmp_count)))\nSELECT \n&nbsp;-- dynamischer Aufbau eines SQL SELECT Statements\n&nbsp;'select sub.*, CEILING((sub.Anzahl \/ CAST('|| sub.tmp_count ||\n&nbsp;' AS DECFLOAT)&nbsp; * 100))&nbsp; || ''%'' as Anteil from (select ' || \n&nbsp;sub.COLNAME || ', count(COALESCE(' || sub.COLNAME || ',-9)) as Anzahl from '|| \n&nbsp;sub.tmp_schema ||'.'|| sub.tmp_table ||' group by ' || sub.COLNAME || ') sub order by 2 desc;'\nFROM (\n&nbsp; SELECT \n&nbsp;&nbsp; colname, tmp_schema, tmp_table, tmp_count\n&nbsp; FROM \n&nbsp;&nbsp; syscat.columns, tmptable\n&nbsp; WHERE \n&nbsp;&nbsp; tabschema = tmp_schema and tabname = tmp_table\n&nbsp; ORDER BY \n&nbsp;&nbsp; colno &nbsp;\n) sub\n;<\/code><\/pre>\n\n\n\n<p><strong>Schritt 2: <\/strong>Pro Tabellenspalte wird ein SQL generiert. Nachfolgend ist das &nbsp;SQL Statement nur <strong>einer<\/strong> Spalte abgebildet (in dem Fall f\u00fcr die Spalte myColumn):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;-- so sieht die dynamisch generierte Query aus\nselect \n&nbsp;sub.*\n,CEILING((sub.Anzahl \/ CAST(266 AS DECFLOAT)&nbsp; * 100))&nbsp; || '%' as Anteil \nfrom (\n&nbsp;select \n&nbsp; myColumn\n&nbsp;,count(COALESCE(myColumn,-9)) as Anzahl \n&nbsp;from \n&nbsp; mySchema.myTable \n&nbsp;group by \n&nbsp; myColumn\n&nbsp;) sub \norder by \n&nbsp;2 desc\n;<\/code><\/pre>\n\n\n\n<p><strong>Schritt 3: <\/strong>Kopiert man nun die generierten SQL in den Editor und f\u00fchrt diese wiederum aus, so erh\u00e4lt man f\u00fcr jede einzelne Spalte einer Tabelle ein Profiling wie im nachfolgenden Screenshot dargestellt.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"http:\/\/konsilium.de\/blog\/wp-content\/uploads\/2013\/02\/profiling-result-neu.png\"><img decoding=\"async\" loading=\"lazy\" width=\"356\" height=\"349\" src=\"http:\/\/konsilium.de\/blog\/wp-content\/uploads\/2013\/02\/profiling-result-neu.png\" alt=\"\" class=\"wp-image-90\" title=\"profiling-result-neu\" srcset=\"https:\/\/konsilium.de\/blog\/wp-content\/uploads\/2013\/02\/profiling-result-neu.png 356w, https:\/\/konsilium.de\/blog\/wp-content\/uploads\/2013\/02\/profiling-result-neu-300x294.png 300w, https:\/\/konsilium.de\/blog\/wp-content\/uploads\/2013\/02\/profiling-result-neu-306x300.png 306w\" sizes=\"(max-width: 356px) 100vw, 356px\" \/><\/a><\/figure><\/div>\n\n\n\n<p>Das Skript wurde getestet mit IBM DB2 v9.7<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Eine typische T\u00e4tigkeit im Rahmen der fachlichen Konzeption ist auch die Systemanalyse &#8211; auch wenn viele Autoren oder Experten anderer Meinung sind und eher eine strikte Trennung zwischen Business Analyse und System Analyse f\u00fcr sinnvoll erachten. Meiner Meinung nach macht es keinen Sinn, eine scharfe Trennlinie zu ziehen. Bereits in der Phase der Anforderungsanalyse und&hellip;&nbsp;<a href=\"https:\/\/konsilium.de\/blog\/2013\/02\/05\/sql-tipp-001-data-profiling\/\" class=\"\" rel=\"bookmark\">Weiterlesen &raquo;<span class=\"screen-reader-text\">SQL Tipp #001 &#8211; Data Profiling<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":2336,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[10,9],"tags":[42,41,77],"_links":{"self":[{"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/posts\/78"}],"collection":[{"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/comments?post=78"}],"version-history":[{"count":10,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/posts\/78\/revisions"}],"predecessor-version":[{"id":2675,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/posts\/78\/revisions\/2675"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/media\/2336"}],"wp:attachment":[{"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/media?parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/categories?post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/konsilium.de\/blog\/wp-json\/wp\/v2\/tags?post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}