Linearis Excel Add-In “TABLE.Manager”

Grundsätzlich werden sogenannte Excel Query Tables – also eine Standardfunktion von Excel – verwendet, um die Daten aus SQL Server in das TABLE.Manager Dokument einzulesen.

Die Leistung des TABLE.Manager liegt darin, über das Excel Add-In die Datenverbindung zum SQL Server dynamisch aufzubauen und zu aktualisieren. Weiters wird die Abfrage der Datentabellen mit zahlreichen Metadaten wie beispielsweise den Feldtypen angereichert und eine selektive Abfrage (= Filter auf eine Spalte der SQL Tabelle) ermöglicht.

Eine Rückschreibefunktion gibt es in den Excel Query Tables nicht, daher wird das Rückschreiben vollständig über das Excel Add-In realisiert. Es stehen zwei Modi zur Verfügung:

1. Replace Modus
   Hier wird die SQL Tabelle zuerst (ganz oder selektiv) gelöscht (= Delete) und dann die Datensätze aus dem TABLE.Manager in die SQL Tabelle als neue Datensätze geschrieben (= Insert). Der Prozess ist sehr performant.
   
   
2. Update Modus
   Diese Option steht nur zur Verfügung, wenn die SQL Tabelle genau ein Primary Key Feld hat. Anhand dieses Feldes wird jeder Datensatz aus dem TABLE.Manager in der SQL Tabelle gesucht und mit den Werten aus Excel aktualisiert (= Update). Datensätze in der SQL Tabelle, die es in Excel nicht mehr gibt, werden aus der SQL Tabelle gelöscht. Datensätze aus Excel, die es in der SQL Tabelle noch nicht gibt, werden in der SQL Tabelle eingefügt (= Insert). Der Prozess ist deutlich weniger performant als der Replace Modus und sollte daher nur mit kleineren Tabellen / Teildatenbeständen verwendet werden.

Da das Rückschreiben natürlich auch Risken birgt, empfehlen wir dringend, die SQL Tabelle vor dem Rückschreiben sichern zu lassen. Zur weiteren Absicherung wird vor der Ausführung des Rückschreibens von TABLE.Manager nachgefragt, ob der Prozess tatsächlich gestartet werden soll.

Der technische Zugriff auf den SQL Server erfolgt aus VBA über das sogenannte ADODB-Objekt (Microsoft ActiveX Data Objects).

Ja, das ist auch ein großer Vorteil der Lösung. Sowohl die READ- als auch die WRITE-Funktion aus dem Excel Add-in können auch in eigenen Excel Dateien verwendet werden. Hier ein Blogbeitrag, wie eine Wechselkursabfrage aus Power Query mit dem TABLE.Manager Add-In direkt nach SQL Server geschrieben werden kann. Beachten Sie, daß in diesen Szenario nicht die Sourcecode-Option lizenziert werden muß, da nur die Konfigurationen von TABLE.Manager verwendet werden (und nicht der VBA-Code verändert wird).

Allgemeine Beschränkungen:

• SQL Tabellen / Views mit mehr als 1.048.576 können aufgrund des Excel Zeilenlimits nicht vollständig eingelesen werden.
• SQL Feldtypen, die von Excel Query Tables nicht unterstützt werden (bspw. uniqueidentifier, varbinary) können daher mit dem TABLE.Manager weder ausgelesen noch beschrieben werden.
• Aus Performancegründen empfehlen wir, mit dem TABLE.Manager Tabellen bis etwa 100.000 Zeilen zu bearbeiten. Beim Rückschreiben differiert die Performance sehr stark zwischem dem Replace-Modus (= Delete + Insert in SQL, schnell) und dem Update-Modus (= Update in SQL, langsam). Wir empfehlen, die Autofilter-Option zu verwenden, um die zu aktualisierende Datenmenge im Update-Modus auf die tatsächlich veränderten Datensätze zu reduzieren.
• Der Zugriff auf SQL Azure in der Cloud ist naturgemäß langsamer als der Zugriff auf eine on-premise SQL Server Instanz.

Besonderheiten bei Views:

• Für Views wird die Anzahl der Datensätze im TABLE.Manager nicht ermittelt, da SQL Server keine Systemtabellen dafür bereitstellt und die Abfrage für alle Views einer Datenbank sehr lange dauern könnte.
• Views sind SQL seitig nur unter bestimmten Bedingungen beschreibbar. TABLE.Manager prüft die Beschreibbarkeit eines Views nicht, dies zu prüfen liegt in der Verantwortung des Benutzers. Weiters können bei Views die rückschreibe-relevanten Eigenschaften eines Feldes (bspw. Primary Key Eigenschaft) nicht immer automatisch festgestellt werden,  diese müssen dann vom Benutzer manuell im TABLE.Manager konfiguriert werden. Weiters kann nur der Replace-Modus zum Rückschreiben verwendet werden, nicht aber der Update-Modus.
• Bei den sogenannten selektiven Abfragen (= Filter auf ein Feld einer meist großen Tabelle) gibt es Einschränkungen beim Einlesen eines Views mit dem TABLE.Manager.

Der Zugriff ist auf SQL Server ab Version 2012 und auf SQL Azure getestet. Der Benutzer benötigt Leserechte auf zumindest eine Tabelle einer SQL Datenbank auf einer SQL Instanz, für das Rückschreiben auch Schreibrechte.

Für den Lese-Prozess werden im TABLE.Manager Dokument (xlsx) mehrere Excel Query Tables – also Excel Standardfunktionalität – eingesetzt, die über das Add-In dynamisch aktualisiert werden. Für den Schreib-Prozesswird im Linearis Excel Add-In (xlam) das ADODB-Objekt (Microsoft ActiveX Data Objects) verwendet.

Das Excel Add-In ermöglicht die Hinterlegung der Credentials (Login & Paßwörter) für den Zugriff auf eine SQL Server Instanz. Diese Daten werden unverschlüsselt in einer XML-Datei am Client verspeichert und können möglicherweise von Excel bei Fehlermeldungen im Klartext angezeigt werden. Es ist daher mit den hausinternen Governance-Regeln abzuklären, inwieweit diese Funktion genutzt werden darf.