Datenpunkt-Tabellen und SQL für das KPI- und Verbrauchsdatenarchiv

Wie sieht die Datenbank für das Verbrauchszahlen-Archiv aus? Zuerst die Tabelle mit den eigentlichen Werten.

CREATE TABLE  TagValues
(
tagname varchar(128) NOT NULL, -- Name des Datenpunktes
time varchar(20) NOT NULL, -- Zeit als String
timetype varchar(10),  -- Zeitliche Auflösung
value varchar(50) NOT NULL, -- Wert
PRIMARY KEY  (tagname,time,timetype),
KEY tagvalue_time (time,timetype)
);

Alles Strings? Bei MySQL als Datenbank und PHP als Skriptsprache ja, denn da ist die Konvertierung einfach. Der Speicherplatz stellt bei der Anzahl der Zeilen auch kein gravierendes Problem dar. Man gewinnt aber eine Menge Komfort bei den Abfragen aus dem Report-Tool. Der Trick steckt ausserdem in der Kodierung der Zeit als ISO-8601-String, siehe [ISO-Zeitkodierung].

Die Felder im Einzelnen:

  • tagname – der Name des Datenpunktes. Wir genehmigen uns ausreichend Platz für sprechende Namen, auch wenn wir gleich noch weitere Ordnungsmerkmale einführen.
  • time – Zeitstempel in ISO-8601-Schreibweise.
  • timetype – Zeitintervall des Werts. Ich verwende einfach die Buchstaben der PHP date()-Funktion, also zum Beispiel d = Tageswert, m = Monatswert, H = Stundenwert, usw. Man braucht dieses Feld nicht unbedingt, wenn man einen Mustervergleich mit like auf das Feld time einfügt. Aber die Abfrage auf „timetype=’d'“ ist schneller und übersichtlicher.
  • value – der eigentliche Wert. Bei Fliesskommawerten muss auf Punkt statt Komma für die Dezimalstellen geachtet werden, dann kann MySQL mit den Werten sofort rechnen. Genauer: Ein Komma in einer Zahl beendet die Zahl. Für MySQL ist ‚2,5‘ = 2, aber ‚2.5‘ bleibt beim Rechnen zweieinhalb.

Wie groß kann die Wertetabelle werden? Das wurde in https://openmes.wordpress.com/2008/12/18/wieviel-reporting-braucht-der-mensch-teil-ii/ schon mal ausgerechnet: Bei 400 Datenpunkten mit den Auflösungen Tag, Woche, Monat, Jahr insgesamt 172.800 Werte pro Jahr. Für meine Experimente nehme ich fast das 40-fache: 1000 Datenpunkte, 3 Jahre. Macht: 432*1000*3 = 1.296.000 Zeilen. Durchschnittliche Zeilenlänge ist 52 Byte, also ist eine Gesamtgröße der Tabelle von ungefähr 67,4MB zu erwarten.

Die größten Kosten der Abfragen werden in der Selektion der gewünschten Werte liegen, weil wir ja die Werte aller Datenpunkte und ihrer Intervalle in eine Tabelle packen. Die Wochenwerte des vergangenen Jahres für 10 bestimmte Datenpunkte sind 53*10 = 530 Werte aus 1,3 Mio. Zeilen. Daher der Index, der timetype und time kombinieren.

Kritisierbar ist natürlich, bei jedem Wert den Tagname als String abzuspeichern, weil er meistens mehrfach mehr Speicherplatz benötigen wird als der eigentliche Wert. Normalerweise würde man eine Beschreibungstabelle für Datenpunkte erstellen und jedem Datenpunkt eine eindeutige ID zuordnen. Kann man machen, wenn man das will. Vorteil der String-Methode ist jedoch, dass die Tabelle auch für sich alleine funktioniert, zum Beispiel auch nach dem Export nach Excel oder CSV. Die Definitionstabelle machen wir jedoch trotzdem.

CREATE TABLE  TagDefs
(
tagid int(10) unsigned NOT NULL auto_increment,
tagname varchar(128) NOT NULL,
physunit varchar(25),
description varchar(25),
label varchar(128),
PRIMARY KEY  USING BTREE (tagid,tagname)
);

Hier spendieren wir eine Unique ID, schaden kann’s nicht. Ausserdem hinterlegen wir die physikalische Einheit der Messwerte und einen Beschreibungstext, der zum Beispiel den Ort des Messgebers enthält oder eine innerbetriebliche Referenznummer. Das Label hingegen soll der Text sein, der zu diesem Datenpunkt in Reports angezeigt wird.

Für kleinere Anwendungen dürften diese zusätzlichen Attribute zu Tags ausreichend sein. Wenn man aber die gleichen Messwerte aus unterschiedlichen Perspektiven zusammenstellen möchte braucht es meist mehr Ordnungskriterien, die man in den Berichten zur Selektion benutzen kann.

Exkurs:

Equipment und Properties. Die meisten MES-Pakete lehnen sich an die ISA-S95 an, die eine Produktionslandschaft in eine bestimmte Hierarchie aufteilt, genannt Enterprise / Site / Area / Cell / Unit. Eine vollständige Angabe von Enterprise bis Unit nennt sich Equipmentpath und bezeichnet eine einzelne Produktionseinheit. Equipmentpfade werden in der Regel durch ‚/‘ oder ‚.‘ getrennt. „Hummel-Honig/Werk_Hintersen/Bereich_Hinten/Abfüllung_1/Füller_3“ wäre ein vollständiger Equipmentpfad zu einem Objekt „Füller 3“.

Jedem so beschriebenen Objekt können Properties – Eigenschaften bzw Attribute – zugeordnet werden. Das gilt auch für Zwischenebenen wie eine Area oder Site. Properties werden dann mit realen Datenpunkten verknüpft. Zum Beispiel wäre die aktuelle Auftragsnummer am „Füller 3“ eine Property dieser Unit.

Ende Exkurs.

Energie- und Verbrauchsdaten stehen allerdings etwas quer zur S95-Struktur, weil viele ihrer Elemente nicht direkt Teil der Produktion sondern der Infrastruktur sind: Trafos, Lüfter, Brauchwasserleitungen, usw. Deshalb erlauben wir, dass jedem Datenpunkt mehrere Equipmentpfade und Properties zugeordnet werden können. Nur die Kombination Pfad+Property muss unique sein.

CREATE TABLE  TagEquipment
(
tagname varchar(128) NOT NULL,
eqpath varchar(255) NOT NULL,
eqproperty varchar(128) NOT NULL,
description varchar(255),
label varchar(128),
PRIMARY KEY  (eqpath,eqproperty)
);

Wie bei der Tagdefinition erlauben wir pro Satz je einen beschreibenden Text und ein Label für die Anzeige.

Jetzt können wir zusätzlich zu den durch die Produktion definierten Equipmentpfaden zusätzliche Ordnungen über unsere Fabrik legen. Zum Beispiel könnte „Hummel-Honig/Werk_Hintersen/Wasser/Entsorgung/Abwasser“ das Selektionskriterium für alle Abwassermessungen sein. Wir sind aber nicht an die Site/Area/Cell/Unit-Struktur gebunden, sondern können den Pfad beliebig verkürzen oder erweitern. Beispiel: „Wasser/Entsorgung/Abwasser“ wird als Prefix in allen Pfaden verwendet, die Abwasser messen. Beim Füller 3 von oben wird auch Abwasser gemessen. Der Equipmentpfad könnte also zum Beispiel „Wasser/Entsorgung/Abwasser/Abfüllung_1“ sein und die Property „Füller_3“. Ob man Enterprise und Site vorschaltet hängt davon ab, ob man tatsächlich Werte aus mehreren Werken in der gleichen Datenbank hat.

Welche Abfragen sind dadurch möglich?

Zum Beispiel „alle Abwasser-Messwerte im ganzen Werk“:

"select * from tagequipment where eqpath like 'Wasser/Entsorgung/Abwasser/%'"

Oder „alle Wasser-Messwerte von Füller 3“:

"select * from tagequipment where eqpath like 'Wasser/%' and property = 'Füller_3'"

Wie man sieht, muss man nur aufpassen, Füller 3 auch jedes Mal gleich zu benennen. Die Definitionen, Bezeichnungen, Pfade und so weiter für ein paar hundert Datenpunkte konsistent zu halten ist eine friemelige Arbeit, die häufig in großen Excel-Tabellen endet.

Wir können aber auch Konsistenzprüfungen auf unserer Datenbank durchführen. Zum Beispiel: Sind für alle Tags Definitionen verfügbar? "select distinct tagname from tagvalues where tagname not in (select tagname from tagdefs)"

Das geht genauso für Equipments.

Mit diesen Definitionen erzeugen wir eine Datenbank mit Testdaten. Ich nehme mir einfach meine echten Kundendaten, aber genauso gut kann man Zufallszahlen durch ein Skript erzeugen lassen.

Im nächsten Teil geht es dann endlich ums das Reporting selbst: Berichte für lau – Reporting mit BIRT

Eine Antwort zu Datenpunkt-Tabellen und SQL für das KPI- und Verbrauchsdatenarchiv

  1. […] Nächster Teil: Datenpunkt-Tabellen und SQL für das KPI- und Verbrauchsdatenarchiv […]

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: