11.02.2015
Robert Galetzka
Autor: Robert Galetzka

Ver­gleich von ACL™ Skrip­ten mit SQL (Teil 2)

Der folgende zweiteilige Blogbeitrag stammt von unserem Praktikanten Robert, der sich im Rahmen seines Hochschulpraktikums (er studiert Wirtschaftsinformatik an der Technischen Hochschule Deggendorf) mit einem Vergleich von ACL™ Skriptsprache und SQL auseinandergesetzt hat. Da wir mit unserem Extraktionstool dab:Exporter seit der Version 3 auch SQL als Zielformat anbieten, wollten wir die Gemeinsamkeiten und Unterschiede herausarbeiten. Dies ist Teil 2, den ersten Teil finden Sie hier.

Die Analysesoftware ACL™ bietet eine eigene (und ziemlich mächtige) Programmier- bzw. Skriptsprache, mit der wiederverwendbare Analysen erstellt werden können. Viele Firmen setzen nicht nur ACL™ sondern auch andere Software für Datenanalysen in ihrem Unternehmen ein. Oft liegen die Daten dabei in Form von SQL Datenbanken vor. Diese können zwar auf einfache Art und Weise in ACL™ importiert und dort analysiert werden. Doch wie sähe es aus, wenn man komplexe Analysen, die in ACL™ ohne weiteres möglich sind, in SQL umsetzen müsste?

Mit genau diesem Thema beschäftige ich mich im Rahmen meines Praktikums bei der dab:GmbH. In diesem Blog Post berichte ich über meine Erfahrungen und gebe einen Einblick in die Herangehensweise, praktische Umsetzung und Unterschiede zwischen SQL und ACL™.

Für meinen Vergleich der ACL™ Skriptsprache und SQL Abfragen habe ich mir eine bereits vorhanden, in ACL™ vorliegende Lösung vorgenommen und begonnen, diese nach SQL zu portieren. Hier finden Sie den zweiten Teil meines Projektberichtes mit den wichtigsten Erkenntnissen.

II. ACL™ vs. SQL - Die wich­tigs­ten Un­ter­schie­de im De­tail

Um das Ergebnis vorwegzunehmen ist es mir gelungen, beide Anforderungen zu erfüllen. Jedoch stellte sich heraus, dass eine 1:1 Umstellung der bestehenden ACL™ Skripte in SQL nicht immer möglich war. Die wichtigsten Unterschiede möchte ich hier kurz erklären:

  1. Arbeit mit mehreren Tabellen: ACL™ Relationen fehlen in SQL (siehe Teil 1)
  2. Trennung von technischen und beschreibenden Spaltentiteln in ACL™ (siehe Teil 1)
  3. Pivot-Aspekte in SQL vs. ACL™ mit „Kreuztabelle“
  4. Flexiblere Parametrisierung mittels ACL™ Dialogen

Nach der Schilderung der Unterschiede folgt ein abschließendes Fazit zur Abrundung.

Die Punkte 1 und 2 wurden bereits im ersten Teil erläutert, und in diesem Teil werden nun die Punkte 3 und 4 behandelt.

3. Pi­vot-As­pek­te in SQL vs. ACL mit „Kreuz­ta­bel­le“

Im ACL™ Skript wird der Befehl CROSSTAB verwendet um eine, aggregierte Pivot-Ansicht der Daten zu erhalten. Dabei können Felder als Kriterium für eine Zeile, aber lediglich ein Feld als zu aggregierende Spalte angegeben werden. Das Ergebnis enthält pro Kombination der Zeilenindikatoren alle vorhandenen Spaltenausprägungen.

Die Umsetzung des identischen Befehls in SQL gestaltet sich dabei etwas knifflig. Hier muss mit dynamischem SQL gearbeitet werden um dasselbe Resultat zu erreichen.

Hier ein Beispiel:

 

Kunde
c_Belegart
c_Betrag
c_Menge
1000A10010
1000B2505
2000B502
3000C30020
2000C5005

Man möchte nun für jeden Kunden den Gesamtbetrag je Belegart erfahren. Die Spaltenbezeichnungen sollen zwecks Unterscheidbarkeit um den einzelnen Wert der Belegart erweitert werden:

Belegart_X und Betrag_X

X steht dabei als Platzhalter für die jeweilige Belegart.

Das Ergebnis soll also wie folgt aussehen:

 

Kundec_Betrag_Ac_Menge_Ac_Belegart_Bc_Menge_Bc_Belegart_Cc_Menge_C
10001001025050 0
2000005025005
3000000030020

In ACL™ wird dies über das Kommando „CROSSTAB“ mittels einer einzigen Codezeile realisiert.

CROSSTAB ON Kunde COLUMNS Belegart Subtotal Betrag Menge to tKunde_Belegart_Crosstab COUNT OPEN

In SQL ist das Pendant zum CROSSTAB-Befehl die PIVOT Funktion, für die allerdings etwas mehr Aufwand notwendig ist. Zuerst musste ich die Spaltenbezeichungen basierend auf der Basistabelle erstellen, dann im nächsten Schritt diese mit Werten füllen.

Im folgenden Codeteil werden die einzelnen Spalten, die man im Auditobjekt haben möchte vordefiniert mitsamt der jeweiligen Belegart und anschließend in die @cols Variable geschrieben. Dabei entsteht ein einziger String mit den benötigten Spaltennamen. Sollte nun eine weitere Belegart hinzu kommen, dann muss diese ebenfalls in der Abfrage berücksichtig werden. Um dies zu erreichen, muss eine dynamische Abfrage anstatt einer statischen gewählt werden.

Select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(Belegart as varchar(4)))

from BasisTabelle

cross apply

(

select 'c_Belegart’, 0 union all

select 'c_Betrag’, 1 union all

select ‘c_Menge’,1 union all

) c (col, so)

group by col, so,Belegart

order by Belegart, so

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

Der Inhalt des Strings mit den Spaltentiteln ist nun wie folgt aufgebaut:

c_Belegart_A],[c_Betrag_A],[c_Menge_A],[c_Belegart_B],[c_Betrag_B],[c_Menge_B],[c_Belegart_C], [c_Betrag_C],[c_Menge_C]

Zu den benötigten Spalten wurden die jeweiligen vorhandenen Ausprägungen des Feldes „Belegart“ angehängt.

Nun folgt der zweite Schritt, nämlich die Zuordnung der Werte aus der präparierten Basistabelle zu der jeweiligen Spalte. Der Inhalt der @cols Variable wird an zwei Stellen verwendet. Erstens am Anfang des SELECT Statements, damit die gewünschten Spalten erstellt werden, und zweitens am Ende der Abfrage im IN Statement, wo die einzelnen Werte den jeweiligen Spalten zugeordnet werden. Da die Abfrage an sich als String in einer Variable gespeichert ist, muss sie nun noch mit dem Befehl „exec sp_executesql“ ausgeführt werden. Damit wird die Variable, welche die Abfrage als String beinhaltet, an die gespeicherte Prozedur übergeben und entsprechend ausgeführt.

set @query

= 'SELECT Kunde, ' + @cols + N'
from
(
select d.Kunde,
   new_col = c.orig_col + ''_'' + cast(d.Belegart as varchar(2)),
  c.value
  from
(
select Kunde , Belegart,

c_Belegart = cast(c_Belegart as varchar(50)),
c_Betrag = cast(sum(c_Betrag) over(partition by Kunde, Belegart) as varchar(50)),
c_Menge = cast(sum(c_Menge) over(partition by Kunde, Belegart)as varchar(59))

from BasisTabelle

) d
cross apply
(
  select ''c_Belegart, d.c_Belegart union all
  select ''c_Betrag'', d.c_Betrag union all
  select ''c_Menge'', d.c_Menge union all

) c (orig_col, value)

) x
pivot
(
  max(value)
  for new_col in (' + @cols + N')
) p'

exec sp_executesql @query;

Nachdem der Befehl abgesetzt wurde, erhält man schließlich das gewünschte Ergebnis als Pivot-Tabelle:

Abbildung 1 – Pivot-Tabelle in SQL (ACL™ CROSSTAB Pendant)

4. Fle­xi­ble­re Pa­ra­me­tri­sie­rung mit­tels ACL™ Dia­log­en

Bei der Erstellung des Audit Objekts sollte man die Möglichkeit haben, die Datensätze durch Filter einzugrenzen. Die Filterung soll allerdings dynamisch basierend auf Usereingaben erfolgen. Neben Buchungskreis-, Datums- und Sprachschlüsselfilter soll auch das Ein- oder Ausschließen von Intercompany-Lieferanten möglich sein. In meiner Aufgabenstellung gab es zusätzlich die Anforderung, dass auch die Eingabe eines bestimmten Kurstypen sowie der Konzernwährung möglich sein musste, um eine Währungsumrechung in enie Reportingwährung durchführen zu können. In ACL™ ist die Erstellung einer solchen Dialogbox ganz einfach, da dieses grafische Feature in die ACL™ Skriptsprache bereits integriert ist. Dialogeingaben werden automatisch in Variablen gespeichert und können im Skript angewendet werden. Dialogbox und Variablenzuweisung erfolgen in einer einzigen Codezeile.

Abbildung 2- Beispieldialog in ACL™ (eingebautes Feature)

Bei SQL hingegen ist dies so direkt nicht möglich. Die Skriptsprache selber beinhaltet keinerlei Befehle, die die Erstellung einer Dialogbox ermöglichen. Eine Filterung, etwa durch Setzen von Variablen, ist zwar problemlos möglich mittels der WHERE Bedingung von SELECT-Statements. Möchte man sich Datensätze mit einer bestimmten Einschränkung anzeigen wie beispielweise die Texte nur auf Deutsch aus der Tabelle DD07T mit dem DOMNAME VGABE dann kann man dies durch WHERE realisieren.

SELECT * FROM DD07T WHERE DD07T_DOMNAME = 'VGABE' AND DD07T_DDLANGUAGE = 'D'

Abbildung 3 - Ergebnis eines SELECTS mit statischer WHERE Klausel

Dies erfolgt allerdings mangels User-Dialog relativ statisch, und man neigt dazu, die Filterwerte hart zu codieren. Sobald man diese Filterparameter jedoch an mehreren Stellen verwendet, ist es ein großer Aufwand, den Filterwert an allen Stellen zu ändern, etwa wenn man sich bezogen auf unser Beispiel nun alles in Englisch anzeigen möchte. Hier bietet es sich an, mit Variablen zu arbeiten, bei denen der Wert einmalig eingangs deklariert wird und dann an jeder Stelle mittels der Variable verwendet wird. Mit DECLARE wird die Deklaration einer Variable in SQL eingeleitet, der Name muss mit @ beginnen. Danach gibt man an, welchen Datentyp und welche Länge die Variable hat. Mit SET legt man abschließend für die Variable den gewünschten Wert fest.

 

DECLARE @setLanguage AS VARCHAR(3);
SET @setLanguage = 'E'

 

Nun kann die Variable an mehreren Stellen verwendet werden, muss aber nur an einer geändert werden. Das Ganze kann dann wie in ACL™ dazu angewandt werden, um auch nach Buchungskreisen oder Datumsintervallen zu filtern.

Möchte man aber nicht auf eine Dialogbox verzichten um die Werte bequem eingeben zu können und somit nicht mit dem eigentlichen Skriptcode in Berührung zu kommen, muss man sich zusätzlich einer anderen Programmiersprache bedienen. Ich entschied mich dabei für C#.

Wie Eingangs erläutert hat man mit reinem SQL keine Möglichkeit, sich eine Dialogbox zu erstellen. Mittels C# ist diese möglich; zudem kann eine Verbindung mit der Datenbank – in der die aufzubereitenden Daten liegen – aufgebaut werden und in diesem Rahmen auch SQL Abfragen ausgeführt werden. Hierzu wird eine SQL Skriptdatei hinterlegt, geöffnet und in einen String ausgelesen.

 

Die Verbindung zur Datenbank mit Windows Authentifizierung wird wie folgt aufgebaut:

SqlConnection ConnectionString = new SqlConnection("Data Source=(local)\\SQLEXPRESS;" + "Initial Catalog=dabTest_DB_01;" + "Integrated Security=sspi");

 

Dann bedarf es einer Pfadangabe zur Skriptdatei, die die SQL Abfrage enthält:

FileInfo File_Query1= new FileInfo(@"C:\BeispielOrdner\Query1.sql");

 

Der nächste Schritt ist das Auslesen des Skriptinhalts in eine Stringvariable:

string Query1_string = File_Create.OpenText().ReadToEnd();

 

Mit ConnectionString.Open() wird die Verbindung zum SQL Server aufgebaut. Damit der ausgelesene String nun ausführbar gemacht werden kann, muss dieser innerhalb der aktiven Verbindung einem sogenannten „SQLCommand“ übergeben werden, welches dann ausgeführt wird. Das SqlCommand Objekt muss dazu im Vorfeld erstellt werden.

 

Verbindungsaufbau und Erstellung des SqlCommand Objekts passiert wie folgt:

ConnectionString.Open();
SqlCommand Command_Query1 = new SqlCommand(Query1_string, ConnectionString);

 

Das Objekt „Command_Query1“ beinhaltet nun den Text der ausgelesenen Datei als String und die Verbindungsdaten zur SQL Datenbank. Nun muss nur noch die Anweisung zur Ausführung des Skripts erfolgen:

Command_Query1.ExecuteNoQuery();

 

Nachdem das Skript ausgeführt wurde schließt man noch die aktive Verbindung:

ConnectionString.Close();

 

Mit Hilfe von Windows Forms kann nun noch eine Dialogbox gestaltet und implementiert werden. Beim Design habe ich mich an den ACL™ Dialogen orientiert:

Abbildung 4 – Mittels C# umgesetzter Dialog für SQL Parametrisierung

Damit ein Skript mit Variablen ausgeführt wird, müssen diese hinzugefügt werden. Dies erfolgt über das Objekt „SqlParameter“. Angenommen in unserem ausgelesenen Skript steht folgendes Code:

SELECT * FROM DD07T WHERE DD07T_DOMNAME = 'VGABE' AND DD07T_DDLANGUAGE = @Language

 

Die Variable @Language möchten wir per Benutzereingabe festlegen lassen. Dazu wird die Eingabe in einen String gespeichert und anschließend als Wert für die Variable übergeben.

string Language_string = textBoxLanguage.Text;

 

Um nun den Wert der Variable zuzuordnen wird ein neuer Parameter zum jeweiligen SqlCommand hinzugefügt:

Command_Query1.Parameters.Add(“@Language“, SqlDbType.VarChar,3).Value = Language_string;

 

Danach kann mit ExecuteNoQuery() das SqlCommand Objekt wieder ausgeführt – diesmal mit einem entsprechenden Parameter.

III. Fa­zit

Unterschiede der beiden Technologien sind natürlich vorhanden. Die ACL™ Skriptsprache ist jedoch mächtiger, als es auf den ersten Blick erscheinen mag. Meistens können die Ansätze der ACL™ Skriptsprache auch in SQL übertragen werden, aber dafür ist oft ein deutlicher Mehraufwand nötig, um Dinge, die in ACL™ sehr einfach möglich sind, 1:1 in SQL nachzubilden. Manchmal müssen auch Abstriche in SQL in Kauf genommen werden, etwa bei Sachverhalten wie Spaltenbeschriftungen oder der fehlenden Möglickeit, Views über Relationen abzubilden. Auch die Dialoge erscheinen zwar in SQL etwas flexibler, für die Umsetzung ist aber ein arbeitsaufwändiger Workaround vonnötigen. Folgende Tabelle zeigt die wesentlichen Unterschiede noch einmal kurz tabellarsich auf:

 

ThemaACL™ SkriptSQL ScriptProblemLösung in SQL
Arbeit mit mehreren TabellenACL bietet neben dem klassischen JOIN auch die Möglichkeit, mit „Relations“ zu arbeiten.SQL bietet nur die JOINS. Diese werden bei einer Vielzahl von Tabellen leicht unübersichtlich.ACL™ kann über Relations besser auf Werte aus anderen Tabellen zugreifen und bei Relations wird nur der erste Datensatz selektiert wohin SQL alle Verbindungen berücksichtigt.Soll nur der aktuellste Datensatz selektiert werden kann dies durch eine auf- oder absteigende Sortierung und anschließendem SELECT TOP 1 ermöglicht werden.
SpaltentitelACL™ bietet technische Spaltennamen und Spaltentitel.SQL hat nur die Möglichkeit einen Spaltennamen anzuzeigen.Technische Namen wie EKPO_EBELP sind nicht so verständlich wie in ACL™, denn in SQL fehlt der sprechende Spaltentitel, um die Bedeutung des Feldes transparenter und somit benutzerfreundlicher zu machen.Nachdem das Audit Objekt mittels SQL erstellt wurde, werden die Spalten mit den sprechenden Namen versehen und der technische Name in Klammern gesetzt.
Dynamische Erstellung von KreuztabellenUnter ACL™ wird mit Crosstab auf einer Ausprägung die Kreuztabelle automatisch erstellt.Per Pivot kann selbiges in SQL erstellt werden, allerdings weniger dynamisch.Es sollen – analog der ACL Kreuztabelle CROSSTAB – in SQL nur Spalten erstellt werden mit Werten welche in der Datenbank vorhanden sind und sich dynamisch an Veränderungen anpassen.Durch dynamisches SQL und Variablen kann man sich von statischen Abfragen lösen.
DialogACL™ stellt mit der eigenen Skriptsprache Elemente zur Erstellung eines Dialogs bereits.SQL beinhaltet keine Möglichkeit ein Dialogfenster aufzubauen.Möchte man nun Filtervariablen per Usereingaben befüllen ist in ACL(TM), nicht aber in SQL ohne weiteres möglich. Diese müssten in SQL dann im Code gesetzt werden.Mit C# kann eine Oberfläche erstellt werden welche die Usereingaben an das SQL Skript durchreicht und ausführt.

 

Für Fragen oder Kommentare können Sie sich gerne unter info@dab-gmbh.de an uns wenden.


Kommentare (0)
Sei der erste, der diesen Blog-Beitrag kommentiert.
Blog Anmeldung

Sie sind nicht angemeldet. Bitte melden Sie sich an um diesen Blogbeitrag zu kommentieren.

anmelden