Wer Microsoft SQL Server in produktiven Planungsanwendungen einsetzt, kennt solche Fragestellungen:
- Werden gerade Plandaten eingegeben? Wenn ja, von wem?
- Wie viele Datensätze werden je Dateneingabe erzeugt?
- Wie groß sind momentan die Rückschreibetabellen?
- Funktioniert der Rückverdichtungsprozess fehlerfrei?
- Wie groß sind die Archiv- und die Rückverdichtungstabellen?
In diesem Blogbeitrag wird gezeigt, wie in einer DeltaMaster-Anwendung, die relational auf eine Systemtabelle des SQL-Servers zugreift und diese archiviert, diese Fragen beantwortet werden können.
In Planungsprojekten findet man häufig folgendes Szenario: Die Rückschreibtabellen sind – am besten in einer eigenständigen Planungsdatenbank – angelegt, der Rückverdichtungsprozess wurde automatisch mit DeltaMaster Modeler eingerichtet, die Planungsanwendung wurde ausgiebig getestet und erste Planer fangen an, Werte in DeltaMaster zu erfassen. Der Planungsfortschritt ist für den Fachanwender daran zu erkennen, dass sich die Planerfassungsmasken füllen. Wie solche Prozesse ablaufen können, wurde bereits im Einmaleins der Planung beschrieben.
Damit in der Anwendung alles rund läuft, muss der Datenbankadministrator im Hintergrund die Prozesse in der Datenbank überwachen. Da die Anzahl der zurück geschriebenen Daten die Geschwindigkeit der Planungsanwendung beeinträchtigt, ist es vor allem am Anfang von Planungsprojekten wichtig, die Größe der Tabellen zu kennen. Das kann man natürlich mit Einzelabfragen im Microsoft SQL Management Studio bewerkstelligen. Diese Abfrage muss aber immer angepasst und neu ausgeführt werden und liefert keine historischen Stände. Eleganter ist es, in einer Anwendung eine Übersicht über alle planungsrelevanten Tabellen (Fakten-, Rückschreib- und Archivtabelle) und deren historischen Datenvolumina vorzuhalten und in DeltaMaster zu berichten.
Die Anwendung ist in zehn Minuten gebaut. Man benötigt:
- eine Archivtabelle, um die Historie der Eingaben zu speichern,
- eine Befüllungsprozedur, die in bestimmten Abständen die Daten aufzeichnet und
- eine Controllinganwendung (DeltaMaster), um die Planeingabetabellen über eine Faktensicht zu überwachen.
Archivtabelle anlegen
Die Archivtabelle wird am besten direkt in der Planeingabedatenbank angelegt. In ihr soll der Tabellenname sowie die Anzahl der Datensätze zu einem bestimmten Zeitpunkt gespeichert werden (vgl. Abbildung 1).
Abb. 1: Ansicht der Archivtabelle
Folgendes SQL-Statement legt die benötigte Tabelle an:
CREATE TABLE T_DataEntry_Archive (
[Name] [sysname] NOT NULL,
[Rows] [int] NULL,
[Timestamp] [datetime] NOT NULL
) ON [PRIMARY]
Befüllungsprozedur einrichten
In der SQL-Systemtabelle „sysindexes“ ist die Anzahl der Zeilen für jedes Datenbankobjekt gespeichert. Wir benötigen jedoch nur die Information für die planungsrelevanten Tabellen in der Datenbank, daher lautet die Abfrage:
SELECT
so.Name
, si.Rows
, GETDATE() AS Timestamp
FROM
sysobjects so INNER JOIN sysindexes si
ON so.id = si.id
WHERE
so.type = 'U' --only tables
AND si.indid < 2 --only main index
AND so.name LIKE 'T_W%' --only relevant tables
Sie liefert beispielhaft dieses Ergebnis:
Name Rows Timestamp
T_WriteBack_FACT_35_Fokus_Plan_Status 609 2010-12-30 07:53:13.243
T_WriteTable_Fokus_Plan_Status_Archiv 897 2010-12-30 07:53:13.243
T_WriteBack_FACT_26_GuV_Plan_Status 1625 2010-12-30 07:53:13.243
T_WriteBack_FACT_37_ÖA_Plan_Einheit 3516 2010-12-30 07:53:13.243
T_WriteTable_GuV_Plan_Status_Archiv 3989 2010-12-30 07:53:13.243.
Mit dieser Abfrage richten wir einen Job ein, der z.B. stündlich die unter 1. angelegte Archivtabelle befüllt (siehe auch Abbildung 2).
Abb. 2: Job für den SQL Server Agent einrichten
Controllinganwendung bauen
Im Blogbeitrag Aufbau eines Datenerfassungssystems für relationale Datenbanken wurde bereits gezeigt, wie relationale Daten an DeltaMaster angebunden werden können. Wir benötigen hier keine Datenerfassung sondern wollen lediglich auf eine mit ein paar Zusatzinformationen angereicherte Faktensicht zugreifen.
Die Sicht besteht auf der in 2. befüllten Archivtabelle und wird erweitert
- um eine Tabellengruppierungsspalte, welche es erlaubt, die Tabellen nach Archiv-, Fakten- und Rückschreibtabellen leichter zu unterscheiden
- um diverse Periodenhierarchiespalten für den Aufbau einer Datumsdimension.
Im Einzelnen sieht die Sicht folgendermaßen aus:
CREATE VIEW [dbo].[V_Fact_DataEntry_Archive] as
SELECT
CASE WHEN PATINDEX('%Archiv%', Name) > 0 THEN 'Archive'
WHEN PATINDEX('%FACT%', Name) > 0 THEN 'Fact'
ELSE 'Write' END AS Tab_Group,
Name ,
Rows ,
CONVERT(VARCHAR, Timestamp,112) AS TIMESTAMP,
DATEPART (yyyy, timestamp) AS Jahr,
DATEPART (mm, timestamp) AS Monat,
DATEPART(hh, Timestamp) HOURS,
DATENAME(dw, Timestamp) Day
FROM dbo.T_DataEntry_Archive AS tdea
UNION ALL --add actual rowcount
SELECT
CASE WHEN PATINDEX('%Archiv%', so.Name) > 0 THEN 'Archive'
WHEN PATINDEX('%FACT%', so.Name) > 0 THEN 'Fact'
ELSE 'Write' END AS Gruppe,
so.Name
, si.Rows
, CONVERT(VARCHAR, GETDATE (),112) TIMESTAMP,
DATEPART (yyyy, GETDATE()) AS Jahr,
DATEPART (mm, GETDATE()) AS Monat,
DATEPART(hh, GETDATE())+1 HOURS,
DATENAME(dw, GETDATE()) Day
FROM
sysobjects so INNER JOIN sysindexes si
ON so.id = si.id
WHERE
type = 'U'
AND si.IndId < 2
AND so.name LIKE 'T_W%'
Über die untere Abfrage wird wie in b) beschrieben die aktuelle Zeilenanzahl je Tabelle hinzugefügt. So erhält man neben der Historie die aktuellen Werte der Tabellen.
Abbildung 3 zeigt die eingebundene Sicht in DeltaMaster mit den daraus erzeugten Dimensionen und dem Analysewert „Rows“.
Abb. 3: Beispielsberichte in DeltaMaster
Aus diesem Modell lassen sich nun verschiedene Berichte bauen. Beispielhaft zeigt Abbildung 4 die Eingabetätigkeit eines Tages (17.12.2010). Um 6 Uhr sind die Daten des Vortages noch in den Rückschreibtabellen. Zwischen sechs und sieben Uhr erfolgen der Rückverdichtungsprozess und der Neuaufbau des Würfels. Alle Daten sind nun rückverdichtet (Fact) und im Archiv gesichert. Ab 9 Uhr beginnt die Dateneingabe der Planer, zuerst in GuV_Plan und GuV_Plan_Status, später in der ÖA-Planung. Über den ganzen Tag werden verschiedene Planungsprozesse befüllt, am stärksten die Tabelle T_Writetable_GuV_Plan, die bis 18 Uhr auf 3.807 Datensätze anwächst…
Abb. 4: Beispielberichte in DeltaMaster
Das Modell kann beliebig erweitert werden, z.B. durch die Einbindung der Rückschreibtabellen oder der Archivtabellen selbst.
Material
Das Zusatzmaterial finden Sie zum Download auf der Blog-Seite.
