Qualitätssicherung
Probleme
Nach dem Erzeugen von größeren, eventuell regelmäßigen Auswertungen steht man vor dem Problem, dass man auf die Schnelle die Kennwerte -wie Minimal-, Maximal-, Summenwert- bestimmen sollte, um sie z.B. auch mit den Werten vom Vormonat vergleichen zu können.
Das SQL-Script ist eventuell ja schnell geschrieben (aber vollständig?), aber wie speichert man die Werte jetzt ab? Kurz nach der Lösung wird ein Attribut hinzugefügt und entweder wird das SQL-Script nicht erweitert oder man bekommt nicht so schnell die Ergebnistabelle der Vormonate erweitert.
Anforderungsliste
Das Projekt OQS (Oracle Qualitäts-Sicherung) sollte
• für Attribute des Typ NUMBER und VARCHAR2 Statistikwerte berechnen
• die Statistikwerte sollen mit Zeitpunkt abgespeichert werden
• das Einfügen von neuen Tabellen soll einfach sein
Neben den Standardwünschen, gibt es dann noch einige Traumpunkte:
• die Werte sollen auch für einen Wert eines Attributes berechnet werden
können (z.B.: MONATS_ID)
• Unabhängig davon sollte auch weitere Ausprägungen, die Fachlichkeiten
erklären könnten, die Statistikwerte differenzieren können (z.B.:
GESCHLECHT)
• folgende Statistikwerte wären denkbar
| KPI_TYPE | VARCHAR2 | NUMBER | Erläuterung |
|---|---|---|---|
| COUNT | X | X | Anzahl-Werte |
| DISTINCT | X | X | |
| NULL | X | X | |
| MAX | X | X | Standardstatistik-Werte |
| MIN | X | X | |
| SUM | - | X | |
| MAX-LEN | X | - | Statistik-Werte über Stringlängen |
| MIN-LEN | X | - | |
| SUM-LEN | X | - |
Machbarkeit
Das Projekt umfasst 6 Scripte, die meisten werden nur selten benutzt.
| oqs_cre_tables.sql | Erzeugt die drei Tabellen für das Projekt:
• oqs_meta_data (beinhaltet die zu sammelnden KPI-Werte), • oqs_kpi_type (beinhaltet die SQL-Fragemente für die Statistikwerte) und • oqs_kpi_log_run (beinhaltet die Ergebnisse eines Laufes) |
|---|---|
| oqs_cre_procedure.sql | Erzeugt die Procedure Innerhalb der Procedure kann der Grad der Parallität für die Queryausführung geändert werden. Für die Testphase kann man auch die zahlreichen, auskommentierten dbms_output-Aufrufe aktivieren. |
| oqs_ins_table.sql | Trägt eine zu analysierende Tabelle in
oqs_meta_data ein, dabei werden die KPI-Namen aus Eigentümer, Tabelle und
Statistikfunktion gebildet werden. Eine Umbenennung in handliche Namen
ist sinnvoll.
Parameter: • &1. Eigentümer der Tabelle • &2. Name der Tabelle • &3. Attribut der Tabelle, die eine Gruppe bildet @oqs_ins_table user_gve tbl_vorname '' @oqs_ins_table owner_dwh lager_bestand monats_id |
| oqs_ins_table_where.sql | Differenziert Kennwerte nach
Attributausprägungen Parameter: • &1. Eigentümer der Tabelle • &2. Name der Tabelle • &3. Name des einschränkenden Attributes • &4. Wert des einschränkenden Attributes @oqs_ins_table_where user_gve tbl_vorname anrede H @oqs_ins_table_where user_gve tbl_vorname anrede F |
| oqs_toogle_kpi_meta.sql | Ermöglicht Aus- und Einschaltung von
Statistikwerten (falls z.B. das SQL-Statement zu lang wird, oder weil die Strings
immer die gleiche Länge haben, wenn sie nicht mit NULL gefüllt sind) Parameter: • &1. Name der KPI • &2. Attributtype für das KPI (VARCHAR2 oder NUMBER) Das Script gibt auch einige Statistiken über die verwendeten Statistik-Funktionen aus, rufen Sie es also mal mit einen nicht vorhanden KPI-Namen auf. @oqs_toogle_kpi_meta XXX Varchar2 Hinweis: Löschen von Einträgen sollte nicht erfolgen. |
| oqs_work.sql | Beispiel für einen Aufruf der Prozedure. Die Ausgabe des Serveroutput sollte eingeschaltet werden, damit die beiden vorhandenen Fehlermeldungen ggf. ausgegeben werden können: • OQS-00001: Unknown table • OQS-00002: Statement too long |
Tabelle oqs_meta_data
Da die Tabelle oqs_meta_data eine zentrale Tabelle für die Erstellung einer Qualitätsabfrage, soll sie an Hand von 2 Einträgen erläutert werden.| Owner | Table_Name | Column _Name | Column _Len |
Column _Type | KPI _Name | KPI _Type | KPI _Group |
KPIWH _Name | KPIWH _Type | KPIWH _Value | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| user_gve | tbl_umsatz | saldo | 22 | N | KPI-42 | SUM | MONATS_ID | ||||
| user_gve | tbl_umsatz | saldo | 22 | N | KPI-42 (Konto 4711) | SUM | MONATS_ID | KONTO | V | 4711 |
In der ersten Zeile wird Qualitätskennzahl "KPI-42" als die Summe des Attributes "saldo" der Tabelle "tbl_umsatz" des Eigentümer "user_gve" definiert und zwar jeweils über den Monat (Attribut "Monats_ID") gruppiert. Bitte beachten Sie, dass alle drei Attribute, die auf %_Type enden, in Großbuchstaben geschrieben sein müssen.
In der zweiten Zeile wird der gleiche Wert nur für ein Konto 4711 bestimmt, diese KPI erhält den Namen "KPI-42 (Konto 4711)".
Bei Aufruf der Procedure
SQL> begin
2 oqs_working('user_gve','tbl_umsatz','200703');
3 end;
4 /
wird das folgende SQL-Statement erzeugt:
SELECT /*+ PARALLEL (w,4) */sum(saldo), sum(case KONTO when '4711' then saldo
ELSE NULL END) FROM verweyen.tbl_umsatz w WHERE MONATS_ID=200703
Das Ergebnis der Abfrage finden Sie mit aktuellen Zeitstempel unter den aufgeführten KPI-Namen in der Tabelle oqs_kpi_log_run.
Datei(en)
Die Datei oqs_scripte.zip enthält die erwähnten Oracle-SQL-Scripte der "Machbarkeitsstudie".
Hinweis
Die Scripte sind längst noch nicht perfekt, es werden z.B. noch nicht alle Attributtypen untersucht und noch mehrere Statistikfunktionen sind denkbar. Auch die SQL-Statements sind nicht alle perfekt, das ist aber nicht Ziel dieser Untersuchung in meiner Freizeit gewesen.
Die Oracle-Fehlermeldung "ORA-00942: table or view does not exist" erhalten Sie, wenn Sie die Leserechte auf den untersuchten Tabellen nicht direkt zugeordnet bekommen haben sondern über eine Rolle. Hier hilft es die Daten temporär in eine temporär erstellte Tabelle zu kopieren.
Wenn Sie Fehler melden wollen, bedenken Sie bitte, dass ich einige Informationen benötige, ggf. auch die Tabellenstruktur der untersuchten Tabelle. Ein Anspruch auf das korrekte Funktionieren besteht nicht, aber ich bin vom Funktionieren überzeugt, sonst würde diese Machbarkeitstudie nicht veröffentlichen.
Die Anzahl der distincten Ausprägungen läßt auch manchmal die Frage offen, ob zwischen zwei Attributen einer Tabelle eine 1:n oder gar eine 1:1 Relation besteht.
/* Script zu Berechnung einer 1:n Relation
Parameter 1: Attribut 1 (Mächtigkeit n) der Tabelle verweyen.tbl_umsatz
Parameter 2: Attribut 2 (Mächtigkeit 1) der Tabelle verweyen.tbl_umsatz
*/
select max(anzahl) as "Wenn 1, dann 1:n-Relation"
from (select &1., count(*) as anzahl
from (select distinct &1., &2.
from verweyen.tbl_umsatz
)
group by &1.
)
;
/* Script zu Berechnung einer 1:1 Relation
Parameter 1: Attribut 1 der Tabelle verweyen.tbl_umsatz
Parameter 2: Attribut 2 der Tabelle verweyen.tbl_umsatz
*/
select count(*)as "Wenn 1, dann 1:1-Relation"
from (select count(*)
from (select distinct &1., &2.
from verweyen.tbl_umsatz
)
union
select count(*)
from (select distinct &1.
from verweyen.tbl_umsatz
)
union
select count(*)
from (select distinct &2.
from verweyen.tbl_umsatz
)
)
;
Tipp: Wenn Sie die beiden Scripte für verschiedene Tabellen benutzen wollen, ersetzen Sie doch die untersuchte Tabelle verweyen.tbl_umsatz mit view_work und erzeugen Sie sich eine entsprechende View auf die zu untersuchende Tabelle. Dies kann auch zur Einschränkung von Abhängigkeiten genutzt werden, z.B. bei Tabellen, die zeitliche Gültigkeit besitzen.
Kommentare dazu sind -wie immer- erwünscht. Diesmal möchte ich Sie auch aktiv auffordern, mir Ihre Erfolgsstories mitzuteilen.