1.1. Tab-Typ: Pivot Grid

Tabs vom Typ Pivot stellen Daten mit aggregierbaren Fakten zur Auswertung bereit. Dieser Artikel soll erklären, wie sie ohne SQL-Kenntnisse eine einfache Pivot-Übersicht ihrer Produkte (Faktentabellen) bauen können.

Version: Dieser Artikel ist mindestens gülitig ab der Version 5.5

1. Voraussetzungen

• Pivot-Tabs benötigen für eine Stored Prozedure eine Datenquelle. Es gibt folgende grundlegende Möglichkeiten:

  • OCT liefert bei der Installation Standardprozeduren aus, z.B. die “result.spPlanningProfit”.

  • Eine selbstgeschriebene Prozedur - diese benötigt aber SQL-Kenntnisse und ein SQL Server Management Studio.

• Die Daten, welche eine solche Prozedur zeigt, können aus OCT Products oder aus beliebigen berechneten / importieren Datenbeständen kommen.

• Ein Tab vom Typ Pivot kann auf mehreren Auswertungsebenen verwendet werden:

  • auf Ebene der Gesamtstruktur

  • auf Ebene der Factory

  • auf Ebene der Productline

2. Verwendungsanleitung

Der Prozess rund um die Verwendung eines Pivot-Tabs wird:

• mit einem einfachen Produkt des Typs “Mietvertrag” aus unserer Demo: https://www.saxess-software.de/loesungen/vertragsmanagement/#1637923776053-f28a538f-d73d und

• mit der Standardprozedur “result.spPlanningProfit” erklärt, welche mit jeder OCT-Installation ausgeliefert wird.

2.1. Datenarten

• Die zugrundeliegende Stored Procedure kann beliebige Daten ausgeben:

  • Alle Felder können als Zeilen / Spalten / Filter einer Pivot-Tabelle verwendet werden.

  • Nur numerische Werte können als Fakten verwendet werden.

• Eine Pivot-Tabelle kann nur aggregieren - daher sollten die Daten von der Prozedur vorzeichengerecht erzeugt werden - z.B. alle Erträge positiv, alle Kosten negativ.

• Die folgende Tabelle zeigt einige Standardprozeduren von OCT, die für Verwendung im Pivot geeignet sind:

• Effekte und die Eigenschaft “numerisch” können in den Wertreihen der Produktdatentabellen geändert werden: https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445645936/5.5.2.+Produktdatentabelle+PDT#Alle-Wertreihen-bearbeiten:

• Die Übersetzung mittels Faktoren erfolgt in der Datenbank über die Tabelle planning.tgValueEffects. Die hier verwendete Beispielprozedur “result.spPlanningProfit”:

  • importiert z.B. Zahlenwerte mit dem Effekt “Kosten=Auszahlg” und verrechnet diese mit einem Faktor = -1.

  • importiert z.B. Zahlenwerte mit dem Effekt “Aktivbestand” gar nicht.

Anwender mit Datenbankkenntnis und -zugriff können somit die Liste der Effekte anpassen.

• Bild 2 zeigt die Wertreihen und die eingetragenen Werte in unserem Beispielprodukt “Mietvertrag”:

• Die Summe aller Werte (für 2021/01) in Wertreihen mit dem Effekt “Kosten=Auszahlg” = 585 + 192 + 50 + 3 = 830.

• Die Wertreihe Kaution mit dem Effekt “Aktivbestand” hat den Wert = 1170.

2.2. Erstellung eines Pivot-Tabs

• Öffnen Sie das Untermenü https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445646128/5.8.+Tab-Typen#Tab-hinzuf%C3%BCgen auf der Ebene (Fabrik, Produktlinie,..) auf welcher der Auswertungs-Tab erstellt werden soll.

• Auswahl der Standardprozedur “result.spPlanningProfit” als Datenquelle.

• Anpassung des Layouts durch Anpassung der Felder in: https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445646139/5.8.1.+Pivot+Grid#Feldliste-umschalten und/oder Erstellung komplett neuer Aggregationsfelder in: https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445646139/5.8.1.+Pivot+Grid#Berechnete-Felder.

  • Bild 3 zeigt eine typische Standardauswahl an unserem Beispiel:

Die Reihenfolge der Felder in der Feldauswahl, z.B. in den Zeilenfeldern, zueinander ist entscheidend für das spätere Tab-Layout. Das oberste Feld (FactoryName) ist gleichzeitig die oberste ausklappbare Zeilenebene im Pivot-Layout.

• Sind alle Felder (Spalten und Zeilen) in der Pivot-Tabelle ausgeklappt, so ist auch das Produkt “Mietvertrag” in der Auswertung sichtbar.

  • Wir benötigen dafür mindestens die Produkte (z.B. ProductName), die Jahre/Monate (Year, Month) und die Werte (Value) um einen Vergleich, mit der beispielhaft berechneten Summe aus Bild 2 oben, anstellen zu können.

  • Vollständig ausgeklappt sieht es am Beispiel unserer Demo wie folgt aus:

• Die Summe aller Werte aus Wertreihen mit dem Effekt “Kosten=Auszahlg” sind hier korrekt dargestellt und mit dem Faktor = -1 verrechnet.

• Sie können schnell alle Zeilen / Spalten im Betrieb auf- und zuklappen. Für eine gute Ladeperformance bei großen Datenmengen sollten Sie alles zugeklappt speichern.

2.3. Darstellung von Differenzen

Differenzen sind nicht ganz so einfach darzstellen, da die Pivot Darstellung von Haus aus vor allem auf das Summieren ausgelegt ist.

Es gibt zwei Grundansätze für Differenzen:

Weg A: Positive - Negative Werte

• Mache das Budget negativ

• Belasse das IST postiv

• dann zeigt die Summe die Differenz

Weg B: Berechnete Felder

• Um berechnete Felder nutzen zu können, müssen Minuend und Subtrahend (https://www.matheretter.de/wiki/subtraktion ) eigene Spalten der zugrundeliegenden Prozedur sein.

• Dann kann man ein berechnetes Feld definieren: Differenz = Sum(SpalteA) - Sum(SpalteB).

• Man kann keine berechneten Felder erstellen, wenn eine Spalte verschiedene Ausprägungen hat.

2.4. Empfehlungen für gute Performance

2.4.1. Design der Abfrage

• Jede Spalte weniger steigert die Performance.

• Textspalten verbrauchen mehr Übertragungskapazität als numerische Spalten.

• Je größer die Datenmenge wird, umso stärker spielt die “zip/unzip”-Zeit bei der Webkomprimierung eine Rolle.

2.4.2. Design der Pivot-Tabelle

• Das Layout sollte zusammengeklappt gespeichert werden.

• Generell sollte man im Layout nicht zu viele Zeilen erzeugen - max. 1000 zusammengeklappte Zeilen.

• In einem Layout mit einer hoher Zeilenzahl, was max. 20 zusammengeklappten Zeilen entspricht, sollte man nicht zu viele Spalten erzeugen.

2.5. Einschränkungen

• Ein Datenfeld kann nicht mehrfach als Fakt verwendet werden (nicht zugleich Summe und Anzahl zeigen) - außer es wird ein https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445646139/5.8.1.+Pivot+Grid#Berechnete-Felder für das Zweite genutzt (Berechnung = Wert * 1).

• https://saxess-software.atlassian.net/wiki/spaces/OH/pages/445646139/5.8.1.+Pivot+Grid#Berechnete-Felder können nicht auf Basis von Feldern berechnet werden, die Sonderzeichen / Umlaute enthalten.

• Es sind keine feingranulare bedingte Formatierung (z.B. Werte größer als 100 = grün) möglich.

• Es sind keine In-Cell-Diagramme (Sparklines) möglich.

• Der Name des Factorytabs darf keine Sonderzeichen enthalten (z.B. "/" ).

2.6. Datenbankkopplung

• Neue Spalten in der Prozedur sind in der Auswahlliste verfügbar und stören nicht die konfigurierte Oberfläche.

• Gelöschte Spalten in der Prozedur verschwinden automatisch aus der Oberfläche.

3. Expertenbereich

Der Expertenbereich richtet sich an User mit fortgeschrittenem SQL-Wissen und Zugriff auf ein SQL Server Management Studio. Hier werden tiefergehende Hintergründe ausgeleuchtet.

3.1. Einrichtung der Tabs

• Die Datenquelle ist immer eine Stored Procedure.

• Schema und Name der Stored Procedure sind beliebig.

• Damit eine Stored Procedure dem User im Standard zur Auswahl angeboten wird, muss in den Metadaten die Eigenschaft “SX_UserHint” mit einem Text gefüllt sein.

• Die Stored Procedure muss abhängig von der Ebene, auf der sie aufgerufen wird, die folgenden Parameter haben (Die Parameter müssen nicht zwingend von der Prozedur verarbeitet werden):

  • Cluster - @Username, @ParamterJSON

  • Factory - @Username, @FactoryID, @ParameterJSON

  • Productline - @Username, @FactoryID, @ProductlineID, @ParameterJSON

• Der Parameter @ParameterJSON ist ein Universalparameter - er ist dafür ausgelegt, beliebige Parameterwerte im JSON Format an die Prozedur zu übertragen.

• Die Stored Procedure muss genau ein Resultset zurückgeben, nicht mehrere - immer SET NOCOUNT ON am Anfang der Stored Procedure setzen.

• Die Prozedur sollte eine Sicherheitsprüfung machen, durch Ermittlung des TransactUsernames und JOIN mit system.trUserRights.

• Die Prozedur sollte einen API-Logeintrag schreiben.

• Filter werden im Layout als EXCLUDE gespeichert - neue Elemente sind also automatisch enthalten.

  • Beispiel: Ein Landesfilter existiert mit den Werten "Deutschland" und "Spanien". Man filtert auf "Deutschland" und speichert die Konfiguration. Was passiert dann, wenn Frankreich in der Datenquelle auftaucht? In der Pivot-Tabelle sind dann Deutschland und Frankreich zu sehen, da die Konfiguration "nicht Spanien" gespeichert hat und alles andere zeigt. Das kann besonders dann zu unerwarteten Filtern führen, wenn man eine gefilterte Pivot aus einer MasterFactory ausrollt und die anderen Factorys in ihren Daten andere Werte im Filterfeld haben.

3.2. Fehlererkennung (mit DB-Zugriff)

3.2.1. FAQ

• Falls das Tab nicht in der Liste der vorgeschlagenen Prozeduren erscheint:

  • Wurde in den Metadaten die Eigenschaft SX_UserHint belegt ?

• Liefert die Prozedur wirklich Daten für den aktuellen User?

  • z.B. testen durch die Ausgabe eines statischen Ergebnisses ohne Rechteprüfung.

• Probleme mit der Spaltenbenennung in der Prozedur?

  • Die Prozedur darf nicht zwei Spalten mit gleichem Namen haben.

  • Die Spaltennamen dürfen keine Sonderzeichen enthalten.

  • Die Spalten dürfen keinen reservierten Namen haben - z.B. "Right" oder "Left".

• Die Prozedur lasst sich für den FactoryService nicht ausführen?

  • Als FactoryService am Managementstudio anmelden und testen.

3.2.2. Andere Probleme

• Fakten erscheinen nicht in der Liste für die Zahlenformatierung:

  • In der Datenbank mit CONVERT statt CAST die Konvertierung zu einer Zahl durchführen.

• Die Parameter FactoryID / ProductlineID wurden nicht als optional deklariert.

3.3. Hinweise zur Performance

Die Perfomanceaussagen sind immer ohne die Zeit für die Datenbankabfrage getroffen. Die Laufzeit der Datenbankprozedur kommt hinzu, liegt aber nicht in der Performanceverantwortung der Applikation. Lokale Performancetests erfolgten auf virtuellen Maschinen mit 8 GB RAM, 4x CPU (Xeon E3 v6 1505 3.0 GHz), NVMe SSD Platten.

3.3.1. Analyseschritte bei Performanceproblemen

• Lieber mehrere kleine Prozeduren in der Datenbank (pro Pivot eine), statt einer großen Prozedur in der Datenbank - deren Daten im Frontend gefiltert werden pro Tab.

• Laufzeit der Prozedur im SQL Server Management Studio (SSMS) testen (sollte < 10 Sekunden sein).

• Laufzeit der Prozedur in den Entwicklerwerkzeugen des Browsers anschauen (sollte max. 2-3x der der DB sein sein).

• Die Brutto-Datenmenge (unkomprimiert) sollte unter 250 MB liegen.

3.4. Testprozedur

Diese Prozedur kann in jeder Datenbank ausgeführt werden und als Quelle "result.spSamplePivot" für ein Pivot-Tab genutzt werden - sie braucht keine Tabelle, da sie ihre Daten selbst erzeugt.

Es kann in der Testprozedur folgendes konfiguriert werden:

• die Anzahl der zu erzeugenden Zeilen.

• ob diese ihre Daten materialisieren soll.

/*
Author:     Gerd Tautenhahn
Created:    2020/06
Summary:    Sampledata for Pivottables

For 500k records with @Static = 1
	- first execution needs 15 sek
	- second execution needs around 6 sec

For 200k records with @Static = 1
	- first execution needs 6 sek
	- second execution needs 2 sec
   
Testcall
EXEC result.spSamplePivot 'SQL',''
*/
   
DROP PROCEDURE IF EXISTS result.spSamplePivot;
GO
   
CREATE PROCEDURE result.spSamplePivot  (
                            @Username           NVARCHAR(255),
                            @FactoryID          NVARCHAR(255) = '', 
                            @ProductLineID      NVARCHAR(255) = '',
                            @ParameterJSON      NVARCHAR(MAX) = ''
                                    )
                            
AS
  
    BEGIN
        SET NOCOUNT ON
 
        DECLARE @Counter        INT = 200000; -- set number of records to create here
		DECLARE @Static			INT = 1;
        DECLARE @CreatedRows    INT = 1;

		-- if a static table already exists, deliver its content else create content
		IF @Static = 1 AND OBJECT_ID (N'tPivotsample', N'U') IS NOT NULL
			BEGIN
						SELECT * FROM dbo.tPivotsample
			END

		IF @Static = 0 OR OBJECT_ID (N'tPivotsample', N'U') IS NULL
			BEGIN
		
				DROP TABLE IF EXISTS #tmp;
				CREATE TABLE #tmp
					(
						 RowNumber      INTEGER                                 NULL
						,CompanyID		INTEGER                                 NULL
						,CostCenterID	INTEGER                                 NULL
						,AccountID		INTEGER									NULL
						,CompanyName	NVARCHAR(255) COLLATE DATABASE_DEFAULT  NULL
						,CostCenterName NVARCHAR(255) COLLATE DATABASE_DEFAULT  NULL
						,AccountName	NVARCHAR(255) COLLATE DATABASE_DEFAULT  NULL
						,BookingText	NVARCHAR(4000) COLLATE DATABASE_DEFAULT NULL
						,BookingMonth	INTEGER									NULL
						,BookingDate	DATE									NULL
						,ValueDebit     MONEY                                   NULL
						,ValueCredit    MONEY                                   NULL
						,ValueBalance   MONEY                                   NULL

						);
 
				WHILE @Counter + 1 > @CreatedRows
					BEGIN
						INSERT INTO #tmp
							SELECT
								 @CreatedRows													AS RowNumber
								,@CreatedRows/10000    +1										AS CompanyID
								,@CreatedRows/1000   +1											AS CostCenterID
								,@CreatedRows/10   + 1											AS AccountID
								,'Company ' + CAST(@CreatedRows/10000 + 1 AS NVARCHAR(255))		AS CompanyName
								,'CostCenter ' + CAST(@CreatedRows/1000 + 1 AS NVARCHAR(255))	AS CostCenterName
								,'Account ' + CAST(@CreatedRows/10 + 1 AS NVARCHAR(255))		AS AccountName
								,'Booking text about this booking item.'						AS BookingText
								,CAST (RAND ()*12+1 AS INTEGER)									AS BookingMonth
								,DATEFROMPARTS(2020,CAST (RAND ()*12+1 AS INTEGER),15)			AS BookingDate
								,RAND () * 100													AS ValueDebit
								,RAND () * 100													AS ValueCredit
								,RAND () * 1000													AS ValueBalance

							SET @CreatedRows = @CreatedRows +1;
					END
 
				SELECT * FROM #tmp;

				IF @Static = 1
					BEGIN
						SELECT * INTO tPivotSample FROM #tmp;
					END

				IF @Static = 0
					BEGIN
						DROP TABLE IF EXISTS dbo.tPivotsample;
					END
			END
  
    RETURN 200;
    END
GO
   
-- no GRANT, Factoryservice is already owner of the result schema

3.5. Technischer Hintergrund

Die der Pivot-Tabelle zugrunde liegende Komponente ist hier näher beschrieben. https://js.devexpress.com/Demos/WidgetsGallery/Demo/PivotGrid/FieldPanel/Angular/Light/

Nicht alle dort beschriebenen Funktionen sind in OCT implementiert - sollten Sie welche davon wünschen, sprechen Sie uns an.