OLAP-Datenbanken haben ihre besondere Stärke im Verdichten von Werten über Dimensionen, Hierarchien und Ebenen hinweg. Aufgrund dieser Stärke können wir sehr schnell den Umsatz über alle Produkte und alle Kunden ausweisen, obwohl er schlimmstenfalls in mehreren Millionen Einzelwerten “versteckt” ist. Alle zählbaren Größen, die sich über die Dimensionen des Datenmodells hinweg beschreiben lassen, können sehr leicht eingebaut und dann analysiert werden. Dazu ist ein Datenmodell aufzubauen, das überschneidungsfrei ist. Eine Besonderheit stellt in diesem Zusammenhang die Darstellung von Ereignissen im Zeitverlauf dar. Im Blogbeitrag Ereignisfolgen wurde bereits auf die zeitliche Abfolge von Ereignissen eingegangen.
Heute soll es um die Abbildung von sich zeitlich überlagernden Ereignissen gehen.
Stellen wir uns die Produktion eines Schrankes vor:
Unser Beispielschrank besteht aus einer Tür, einer Rückwand, zwei Seitenwänden, einem Deckel, einem Boden und schließlich aus vier Leisten, die den Fuß bilden. Wir benötigen für die effiziente Herstellung fünf Maschinen (Schema Maschinenpark).
Es stellt sich bei Maschinenparks immer die Frage nach der Verfügbarkeit bzw. der Ausfallzeit. Vier Maschinen können in unserem einfachen Beispiel parallel und unabhängig voneinander arbeiten. Nur die Maschine, die die Scharniere in die Tür einsetzt, muss auf die Tür aus Maschine drei warten. Fällt also Maschine drei aus, hat auch Maschine fünf ein Problem, das aber extern bedingt ist. Weiterhin sind verschiedene Ausfallursachen möglich, die sich auch noch überlagern können. So ist es beispielsweise möglich, dass der Park pausenbedingt stillsteht und gleichzeitig Wartungsarbeiten durchgeführt werden. All dies muss im Datenmodell beachtet werden.
Die Herausforderung besteht darin, die Zeiten so abzulegen, dass sie über Dimensionen und Hierarchien hinweg überschneidungsfrei sind. Gedanklich gibt es zwei Lösungsansätze:
Berechnung der Gesamtausfallzeit für jeden Hierarchieknoten auf jeder Dimension
Die relationale Abbildung erfolgt durch eine rekursive Vorgänger- und Nachfolgersuche:
alter function dbo.F_DatumVorgaenger (@Maschine int, @Start datetime, @Ende datetime) returns datetime as begin declare @tmpDatum datetime set @tmpDatum = @Start return case when (select Min(Start) from T_Maschinenausfaelle where Maschine = @Maschine and Start <= @Start and Ende >= @Start) <> @Start then dbo.F_DatumVorgaenger(@Maschine, (select Min(Start) from T_Maschinenausfaelle where Maschine = @Maschine and Start <= @Start and Ende >= @Start), @Ende) else @tmpDatum end end go alter function dbo.F_DatumNachfolger (@Maschine int, @Start datetime, @Ende datetime) returns datetime as begin declare @tmpDatum datetime set @tmpDatum = @Ende return case when (select Max(Ende) from T_Maschinenausfaelle where Maschine = @Maschine and Ende >= @Ende and Start <= @Ende) <> @Ende then dbo.F_DatumNachfolger(@Maschine, @Start, (select Max(Ende) from T_Maschinenausfaelle where Maschine = @Maschine and Ende >= @Ende and Start <= @Ende)) else @tmpDatum end end go select Maschine, dbo.F_DatumVorgaenger(Maschine, Start, Ende) StartVorgänger, dbo.F_DatumNachfolger(Maschine, Start, Ende) StartNachfolger from T_Maschinenausfaelle group by Maschine, dbo.F_DatumVorgaenger(Maschine, Start, Ende), dbo.F_DatumNachfolger(Maschine, Start, Ende)
Für jeden Datensatz muss immer ein mehrfacher Lauf über den Gesamtdatenbestand erfolgen. Die Funktionen suchen den jeweils direkten Vorgänger/Nachfolger. Erst wenn es keine zeitliche Überlappung mehr gibt, wird der Aufruf verlassen. Die Ermittlung muss für jede Dimensionskombination neu erfolgen, so dass die Verdichtungslogik der OLAP-Datenbank komplett ausgeschaltet wird.
Fazit:
Dieser Lösungsweg ist leider nicht einsetzbar. Es ergeben sich exorbitante Laufzeiten.
Ablegen von Zeitscheiben in der OLAP-Datenbank und Berechnung der Ausfallzeit
Die Meldung wird in definierte Zeitabschnitte (z.B. Minutenraster) zerlegt und mit einem Zählwert in die OLAP-DB gespeichert. Ist dieser Zählwert >= 1 wird der Wert “Anzahl Ausfallminuten” mit “1″ belegt, d.h. diese Zeiteinheit ist als fehlerhaft belegt.
Nachteil:
Alle Meldungen müssen in die gewünschten Zeiteinheiten zerlegt werden.
Vorteil:
Durch den normalen Lade- und Verdichtungsvorgang in der OLAP-Datenbank ist die Ausfallzeit auf allen Dimensionen und Hierarchien analysierbar. Die eingebaute Verdichtungslogik der Datenbank wird genutzt.
Zerlegung der Meldungen in Zeitscheiben
In der relationalen Datenbank sind Perioden und ein Zeitraster hinterlegt. In unserem Beispiel haben wir uns für eine Stückelung im Minutenraster entschieden.
Es wurden drei Tabellen angelegt:
- T_Perioden_manuell enthält die Tage für die Periodendimension
- T_S_Stunde enthält die Stunden (hier 0-24)
- T_S_Minute enthält die Minuten (hier 0-59)
Im Modell wurde eine Abfrage erstellt, die die Stunden und Minuten kombiniert:
ALTER view V_Zeit as
select 10000+StundeID*100+MinuteID as Zeitid,
stundeid,
right('0'+CONVERT(varchar,stundeid),2)+MinuteBEZ+':00' as ZeitBEZ
from T_S_Stunde,T_S_Minute
Eine zweite Abfrage kombiniert die Perioden mit der Zeit:
ALTER view V_Zeitscheiben as
SELECT
datepart(yyyy,[periode])*10000+datepart(mm,[periode])*100+datepart(dd,[periode]) PeriodeID,
[Periode],
convert(bigint,datepart(yyyy,[periode]))*100000000
+datepart(mm,[periode])*1000000+datepart(dd,[periode])*10000
+Zeitid PeriodTimeID,
ZeitId
FROM V_Zeit,T_Import_Periode_manuell
Wir können jetzt in einer dritten Abfrage diese Zeitscheiben gegen die Meldungen stellen und so die Meldungen “aufspalten”:
Alter View V_Import_Meldungen as select * from V_Meldungen inner join V_Zeitscheiben on date_start = PeriodeID and time_start <= ZeitId and time_ende >= ZeitId
Als Ergebnis erhalten wir einen Datensatz je Zeiteinheit. Diese Datensätzen werden in ein OLAP-Modell importiert. Man erhält eine Kennzahl, die uns die Anzahl Meldungen je Zeiteinheit zeigt. Ist diese Kennzahl ungleich “0″, so kann man in einer zweiten Kennzahl den entsprechenden Wert der Zeiteinheit (z.B. 60 Sekunden) via Formel oder Scope hinterlegen. Man erhält dann auf jeder Dimensionskombination und auf jeder Hierarchiebene die richtige (überschneidungsfreie) Zeit für die Analyse.
