Die Probleme der Datenqualität sind bei allen BI-Tools dieselben. Daher sorgen wir bei unserem ETL dafür, dass auf diese geachtet wird.
Unser Self-Service-BI hat daher eine andere Qualität.
Der SQL-Ist relativ simpel:
Ca. 10 - 30 Tabellen verjoint via Derived Tables, alle mit Primary Key, eine Where-Klausel beliebiger Tiefe, teilweise Security-Where auf den einzelnen Tabellen.
Je nach Anfrage zwischen 5 und 30 Group By Feldern sowie 5 - 30 oder mehr Aggregate;-). Das Ergebnis sind dann Resultsets mit bis zu 1 Mio Zeilen.
Einen Key für Group By habe ich auch sein gelassen, da der Key für Where effektiver ist.
Das mit den 1-Feld-Schlüsseln habe ich aufgegeben, da diese bei komplexen Where-Bedingungen nicht schneller als ein Table-Scan sind.
Das Batch-API können wir nicht nutzen, da wir auch hier die Daten nicht einfach nur hochladen sondern auch per Multi-Feld-Primary Key Update/Inserts betreiben, was jedoch mit unserem BulkLoad (mehrere parametrierte Update or Inserts in einem Execute Block) kein Problem darstellt.
Bei Interesse können wir das gerne präsentieren.
Also falls es interessiert hier der SQL.
Queryzeit parallel, also bis zum 1. Satz 197 Sekunden, Downloadzeit 85 Sekunden, Result 675.490 Zeilen.
Queryzeit einzeln 51 Sekunden, Downloadzeit 48 Sekunden.
SQL:
SELECT COALESCE( T89_LEVEL1, '') AS F56, COALESCE( T89_LEVEL13, '') AS F102, COALESCE( T89_LEVEL9, '') AS F104, COALESCE( T89_LEVEL11, '') AS F106, T88_LEVEL8 AS F48, COALESCE( T89_LEVEL3, '') AS F58, T88_LEVEL6 AS F49, COALESCE( T89_LEVEL4, '') AS F60, T88_LEVEL7 AS F75, T88_INFO3 AS F100, COALESCE( T91_LEVEL18, '') AS F61, COALESCE( T91_LEVEL25, '') AS F63, COALESCE( T91_LEVEL19, '') AS F64, COALESCE( T91_LEVEL28, '') AS F117, COALESCE( T91_LEVEL27, '') AS F65, T88_LEVEL5 AS F50, COALESCE( T91_LEVEL32, '') AS F121, COALESCE( T91_LEVEL22, '') AS F67, COALESCE( T89_LEVEL14, '') AS F110, T88_LEVEL2 AS F51, T88_LEVELDATE AS F47, T88_LEVEL0 AS F52, T88_LEVEL10 AS F83, T88_LEVEL11 AS F84, T88_LEVEL12 AS F85, SUM(T88_VALUE30) AS F53, SUM(T88_VALUE29) AS F54, SUM(T88_VALUE35) AS F55, COUNT('*') AS "##RowCount", MIN(F48T) AS F48T, MIN(F49T) AS F49T, MIN(F75T) AS F75T, MIN(F50T) AS F50T, MIN(F51T) AS F51T, MIN(F83T) AS F83T, MIN(F84T) AS F84T, MIN(F85T) AS F85T, MIN(F102T) AS F102T, MIN(F58T) AS F58T, MIN(F60T) AS F60T, MIN(F110T) AS F110T, MIN(F121T) AS F121T, MIN(F67T) AS F67T FROM (SELECT COALESCE(XT117_TEXT, '') AS F48T, COALESCE(XT118_TEXT, '') AS F49T, COALESCE(XT119_TEXT, '') AS F75T, COALESCE(XT120_TEXT, '') AS F50T, COALESCE(XT121_TEXT, '') AS F51T, COALESCE(XT122_TEXT, '') AS F83T, COALESCE(XT123_TEXT, '') AS F84T, COALESCE(XT124_TEXT, '') AS F85T, COALESCE(XT125_TEXT, '') AS F102T, COALESCE(XT126_TEXT, '') AS F58T, COALESCE(XT127_TEXT, '') AS F60T, COALESCE(XT128_TEXT, '') AS F110T, COALESCE(XT129_TEXT, '') AS F121T, COALESCE(XT130_TEXT, '') AS F67T, T88.*, T89.*, T91.* FROM (SELECT T88.LEVEL0 AS T88_LEVEL0, T88.LEVEL1 AS T88_LEVEL1, T88.LEVEL2 AS T88_LEVEL2, T88.LEVEL3 AS T88_LEVEL3, T88.LEVEL16 AS T88_LEVEL16, T88.LEVEL17 AS T88_LEVEL17, T88.LEVEL18 AS T88_LEVEL18, T88.LEVEL15 AS T88_LEVEL15, T88.LEVEL4 AS T88_LEVEL4, T88.LEVEL5 AS T88_LEVEL5, T88.LEVEL6 AS T88_LEVEL6, T88.LEVEL7 AS T88_LEVEL7, T88.LEVEL8 AS T88_LEVEL8, T88.VALUE29 AS T88_VALUE29, T88.VALUE30 AS T88_VALUE30, T88.VALUE28 AS T88_VALUE28, T88.LEVEL9 AS T88_LEVEL9, T88.VALUE31 AS T88_VALUE31, T88.VALUE27 AS T88_VALUE27, T88.VALUE32 AS T88_VALUE32, T88.VALUE33 AS T88_VALUE33, T88.VALUE34 AS T88_VALUE34, T88.VALUE35 AS T88_VALUE35, T88.VALUE19 AS T88_VALUE19, T88.LEVEL19 AS T88_LEVEL19, T88.LEVEL10 AS T88_LEVEL10, T88.LEVEL11 AS T88_LEVEL11, T88.LEVEL12 AS T88_LEVEL12, T88.INFO0 AS T88_INFO0, T88.INFO1 AS T88_INFO1, T88.LEVEL13 AS T88_LEVEL13, T88.INFO2 AS T88_INFO2, T88.LEVEL14 AS T88_LEVEL14, T88.INFO3 AS T88_INFO3, T88.LEVELDATE AS T88_LEVELDATE, T88.LEVELWEEK AS T88_LEVELWEEK, T88.LEVELMONTH AS T88_LEVELMONTH, T88.LEVELYEAR AS T88_LEVELYEAR, T88.INFO4 AS T88_INFO4, T88.INFO5 AS T88_INFO5, T88.VALUE5 AS T88_VALUE5, T88.VALUE6 AS T88_VALUE6, T88.VALUE17 AS T88_VALUE17, T88.VALUE0 AS T88_VALUE0, T88.VALUE18 AS T88_VALUE18, T88.VALUE1 AS T88_VALUE1, T88.VALUE2 AS T88_VALUE2, T88.VALUE3 AS T88_VALUE3, T88.VALUE4 AS T88_VALUE4, T88.IMPORTID AS T88_IMPORTID FROM FTISDATA000162 T88) T88 LEFT JOIN (SELECT T89.LEVEL0 AS T89_LEVEL0, T89.LEVEL1 AS T89_LEVEL1, T89.LEVEL2 AS T89_LEVEL2, T89.LEVEL3 AS T89_LEVEL3, T89.LEVEL4 AS T89_LEVEL4, T89.LEVEL5 AS T89_LEVEL5, T89.LEVEL6 AS T89_LEVEL6, T89.LEVEL7 AS T89_LEVEL7, T89.LEVEL8 AS T89_LEVEL8, T89.LEVEL9 AS T89_LEVEL9, T89.VALUE0 AS T89_VALUE0, T89.VALUE1 AS T89_VALUE1, T89.VALUE2 AS T89_VALUE2, T89.LEVEL10 AS T89_LEVEL10, T89.LEVEL11 AS T89_LEVEL11, T89.LEVEL12 AS T89_LEVEL12, T89.LEVEL13 AS T89_LEVEL13, T89.LEVEL14 AS T89_LEVEL14, T89.VALUE3 AS T89_VALUE3, T89.LEVEL15 AS T89_LEVEL15, T89.IMPORTID AS T89_IMPORTID FROM FTISDATA000168 T89) T89 ON T88_LEVEL6 = T89_LEVEL0 LEFT JOIN (SELECT T91.LEVEL21 AS T91_LEVEL21, T91.LEVEL18 AS T91_LEVEL18, T91.LEVEL23 AS T91_LEVEL23, T91.LEVEL24 AS T91_LEVEL24, T91.LEVEL25 AS T91_LEVEL25, T91.LEVEL26 AS T91_LEVEL26, T91.LEVEL27 AS T91_LEVEL27, T91.LEVEL28 AS T91_LEVEL28, T91.LEVEL22 AS T91_LEVEL22, T91.LEVEL20 AS T91_LEVEL20, T91.LEVEL29 AS T91_LEVEL29, T91.LEVEL30 AS T91_LEVEL30, T91.LEVEL16 AS T91_LEVEL16, T91.LEVEL32 AS T91_LEVEL32, T91.LEVEL37 AS T91_LEVEL37, T91.LEVEL31 AS T91_LEVEL31, T91.LEVEL19 AS T91_LEVEL19, T91.LEVEL38 AS T91_LEVEL38, T91.LEVEL39 AS T91_LEVEL39, T91.LEVEL17 AS T91_LEVEL17, T91.LEVEL40 AS T91_LEVEL40, T91.IMPORTID AS T91_IMPORTID FROM FTISDATA000212 T91) T91 ON T88_LEVEL5 = T91_LEVEL21 LEFT JOIN (SELECT XT117.LEVEL1 AS XT117_TEXT, XT117.LEVEL0 AS XT117_LEVEL0 FROM FTISDATA000004 XT117) XT117 ON XT117_LEVEL0 = T88_LEVEL8 LEFT JOIN (SELECT XT118.LEVEL1 AS XT118_TEXT, XT118.LEVEL0 AS XT118_LEVEL0 FROM FTISDATA000010 XT118) XT118 ON XT118_LEVEL0 = T88_LEVEL6 LEFT JOIN (SELECT XT119.LEVEL0 AS XT119_TEXT, XT119.LEVEL2 AS XT119_LEVEL2 FROM FTISDATA000061 XT119) XT119 ON XT119_LEVEL2 = T88_LEVEL7 LEFT JOIN (SELECT XT120.LEVEL1 AS XT120_TEXT, XT120.LEVEL0 AS XT120_LEVEL0 FROM FTISDATA000008 XT120) XT120 ON XT120_LEVEL0 = T88_LEVEL5 LEFT JOIN (SELECT XT121.LEVEL0 AS XT121_TEXT, XT121.LEVEL1 AS XT121_LEVEL1 FROM FTISDATA000006 XT121) XT121 ON XT121_LEVEL1 = T88_LEVEL2 LEFT JOIN (SELECT XT122.LEVEL0 AS XT122_TEXT, XT122.LEVEL1 AS XT122_LEVEL1 FROM FTISDATA000079 XT122) XT122 ON XT122_LEVEL1 = T88_LEVEL10 LEFT JOIN (SELECT XT123.LEVEL0 AS XT123_TEXT, XT123.LEVEL1 AS XT123_LEVEL1 FROM FTISDATA000079 XT123) XT123 ON XT123_LEVEL1 = T88_LEVEL11 LEFT JOIN (SELECT XT124.LEVEL0 AS XT124_TEXT, XT124.LEVEL1 AS XT124_LEVEL1 FROM FTISDATA000079 XT124) XT124 ON XT124_LEVEL1 = T88_LEVEL12 LEFT JOIN (SELECT XT125.LEVEL1 AS XT125_TEXT, XT125.LEVEL0 AS XT125_LEVEL0 FROM FTISDATA000015 XT125) XT125 ON XT125_LEVEL0 = T89_LEVEL13 LEFT JOIN (SELECT XT126.LEVEL0 AS XT126_TEXT, XT126.LEVEL1 AS XT126_LEVEL1 FROM FTISDATA000011 XT126) XT126 ON XT126_LEVEL1 = T89_LEVEL3 LEFT JOIN (SELECT XT127.LEVEL1 AS XT127_TEXT, XT127.LEVEL0 AS XT127_LEVEL0 FROM FTISDATA000014 XT127) XT127 ON XT127_LEVEL0 = T89_LEVEL4 LEFT JOIN (SELECT XT128.LEVEL0 AS XT128_TEXT, XT128.LEVEL1 AS XT128_LEVEL1 FROM FTISDATA000018 XT128) XT128 ON XT128_LEVEL1 = T89_LEVEL14 LEFT JOIN (SELECT XT129.LEVEL1 AS XT129_TEXT, XT129.LEVEL0 AS XT129_LEVEL0 FROM FTISDATA000008 XT129) XT129 ON XT129_LEVEL0 = T91_LEVEL32 LEFT JOIN (SELECT XT130.LEVEL0 AS XT130_TEXT, XT130.LEVEL1 AS XT130_LEVEL1 FROM FTISDATA000021 XT130) XT130 ON XT130_LEVEL1 = T91_LEVEL22) T88 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25
Plan:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (T88 T88 T88 NATURAL, T88 T89 T89 INDEX (PK_FTISDATA000168)), T88 T91 T91 INDEX (PK_FTISDATA000212)), T88 XT117 XT117 INDEX (PK_FTISDATA000004)), T88 XT118 XT118 INDEX (PK_FTISDATA000010)), T88 XT119 XT119 INDEX (PK_FTISDATA000061)), T88 XT120 XT120 INDEX (PK_FTISDATA000008)), T88 XT121 XT121 INDEX (PK_FTISDATA000006)), T88 XT122 XT122 INDEX (PK_FTISDATA000079)), T88 XT123 XT123 INDEX (PK_FTISDATA000079)), T88 XT124 XT124 INDEX (PK_FTISDATA000079)), T88 XT125 XT125 INDEX (PK_FTISDATA000015)), T88 XT126 XT126 INDEX (PK_FTISDATA000011)), T88 XT127 XT127 INDEX (PK_FTISDATA000014)), T88 XT128 XT128 INDEX (PK_FTISDATA000018)), T88 XT129 XT129 INDEX (PK_FTISDATA000008)), T88 XT130 XT130 INDEX (PK_FTISDATA000021)))