Anbindung externer Daten in Excel 2016: Vereinheitlichung von Queries und Datenverbindungen

Publiziert von

Power Query wurde in Excel 2010/2013 als optional installierbares Add-In eingeführt und gehört seit Excel 2016 zum Standardumfang des Programms. Mit den (+/-) monatlichen Updates von Excel 2016 (unter Office 365) wurde die neue Power Query Funktionalität schrittweise mit der alten Datenverbindungsfunktionalität („Legacy“) in ein vereinheitlichtes User Interface überführt („Unified Get Data Experience“).

Power Query ist übrigens eine Komponente, die nicht nur in Excel sondern auch in Power BI und SQL Server 2017  auch im Tabular Model verbaut ist. Während sich in Power BI die Legacy-Thematik nicht stellt, sind auch in SQL Server Tabular Model 2017 die früheren Konnektoren zu Legacy-Verbindungen geworden (siehe dieser Blogbeitrag).

Im folgenden Blogbeitrag wird gezeigt, wie sich das Excel User Interface bezüglich der Dualität bei der Einbindung externer Daten entwickelt hat und wie die Anbindung einer relationalen SQL Server Tabelle sowie eines SQL Server Tabular Model im direkten Vergleich funktioniert.

1. Evolution der externen Datenverbindungen von Excel 2010 bis Excel 2016

In Excel 2010 und Excel 2013 gab es im Menü Daten die Bereiche Externe Daten abrufen und Verbindungen. Das (kostenlose) Add-In Power Query konnte hinzu installiert werden, die Programmfunktionen waren in dem separaten Ribbon Power Query zu finden:

Der Zugriff auf eine relationale SQL Server Instanz war ebenso wie der Zugriff auf eine SQL Server Analysis Services Instanz (= Multidimensional oder Tabular) im Menüpunkt Aus anderen Quellen zu finden:

Bestehende Verbindungen wurden im Dialog Arbeitsmappenverbindungen administriert:

In der ersten Version von Excel 2016 wurde die Power Query Funktionalität mit der Ribbongruppe Abrufen und transformieren („Get & Transform“) in die bestehende Menüstruktur im Ribbon Daten nur eingeschoben, ohne die beiden Komponenten („alt und neu“) tiefer zu integrieren:

In der aktuellen Version von Excel 2016 (Version 1811 = November 2018) sind die beiden Komponenten bereits weitgehend unter einem gemeinsamen UI im Ribbon Daten vereinheitlicht:

Dabei ist beispielsweise der neue Konnektor (auf Basis Power Query) für SQL Server unter Daten abrufen -> Aus Datenbank -> Aus SQL Server Datenbank zu finden:

Der alte Konnektor (auf Basis der „Datenverbindungen“) ist hingegen nicht mehr vorhanden, sondern muß zuerst über Optionen -> Daten -> Legacy Datenimport-Assistenten anzeigen reaktiviert werden:

Das Menü ist jetzt erweitert um die Legacy Konnektoren und der alte SQL Datenimport kann über Daten abrufen -> Legacy-Assistenten -> aus SQL Server (Legacy) weiterhin aufgerufen werden:

Den alten Arbeitsmappenverbindungen-Dialog (siehe Screenshot oben) gibt es nicht mehr, dafür gibt es eine Abfragen und Verbindungen-Pane, die optional am rechten Bildschirmrand sichtbar gemacht werden kann. Diese ist über einen Tab wiederum in die beiden Bereiche Abfragen und Verbindungen geteilt:

2. Vergleich der Anbindung einer SQL Server Tabelle als (Legacy) Datenverbindung und

als (Power) Query

2.1 Anbindung über den Legacy Konnektor („Datenverbindung“)

Die Anbindung einer relationalen SQL Server Tabelle über den alten Legacy Konnektor führt zu folgendem (vermutlich bekanntem) Dialog zur Selektion der gewünschten Quelltabelle …

… hin zum Dialog zur Auswahl der Ausgabeoption in Excel:

Die anschließend erstellte Datenverbindung kann jederzeit in der Abfragen und Verbindungen Pane eingesehen werden, das Kontextmenü der Verbindung

… führt zu den (vermutlich ebenfalls bekannten) Verbindungseigenschaften:

Beachten Sie bitte, daß hier der Treiber SQLOLEDB (= Legacy Konnektor) verwendet wird und mehrere Befehlstypen zur Auswahl stehen und die Abfragebefehle (auch über VBA) editierbar sind.

2.2 Anbindung über Power Query („Abfrage“)

Die Anbindung einer relationalen SQL Server Tabelle über den neuen Power Query Konnektor führt zum sogenannten Navigator. Hier kann neben der Selektion der gewünschten Quelltabelle mit dem Button Daten transformieren die eigentliche Query Komponente geöffnet werden und umfangreiche Transformationen an den Daten vorgenommen werden (das ist aber nicht der Fokus dieses Blogbeitrags, daher wird hier nicht näher auf diese Funktionalität eingegangen).

Der Dialog zur Auswahl der Ausgabeoption in Excel sieht aus (lediglich der Button Eigenschaften ist hier deaktiviert):

Die anschließend erstellte Datenverbindung kann jederzeit in der Abfragen und Verbindungen Pane eingesehen werden, das Kontextmenü der Abfrage ist wesentlich umfangreicher als jenes der Datenverbindungen:

Über den Kontextmenübefehl Eigenschaften kann wiederum der Dialog mit den Abfrageeigenschaften aufgerufen werden, im Reiter „Verwendung“ gibt es eine zusätzliche Option Schnelles Laden von Daten aktivieren:

Beachten Sie bitte, daß jetzt der Treiber Microsoft.Mashup.OleDb (= Power Query Konnektor) verwendet wird und weder der Befehlstyp noch der Abfragebefehl editierbar sind.

3. Anbindung eines SQL Server Tabular Model im Live Modus

Die Erstellung eines PivotTables auf ein SQL Server Tabular Model (oder auch einen SQL Server Cube) im sogenannten Live Modus (d.h. die Daten werden nicht nach Excel importiert sondern nur zur Anzeige im PivotTable abgerufen) erfolgt über den Befehl Daten abrufen -> Aus Datenbank -> Aus Analysis Services:

Dabei stellt sich heraus, daß dieser neue Menüpunkt lediglich den Legacy Konnektor für SSAS aufruft, da noch kein neuer Konnektor existiert. Daher führt der Aufruf über Daten abrufen -> Legacy-Assistenten -> Aus dem Datenverbindungs-Assistenten (Legacy)

… und dem Eintrag Microsoft SQL Server Analysis Services …

… zum gleichen Verbindungsdialog für die gewünschte SSAS Instanz:

Nach der Auswahl der SSAS Datenbank und einer ggfs. existierenden Perspektive …

… gelangen wir in den (vermutlich bekannten) Dialog für die Ausgabeoptionen in Excel:

Die Verbindung kann wiederum über die Abfragen und Verbindungen Pane eingesehen werden:

Hier ist zu sehen, daß der Treiber MSOLAP (= Legacy Konnektor) verwendet wird, die Verbindungsinformationen können hier (auch über VBA) editiert werden:

Fazit

Die neuen mit Power Query erstellten Abfragen sind sehr leistungsfähig und dann interessant, wenn Transformationen an den Quelldaten vorgenommen werden sollen. Die alten Datenverbindungen können unter Extras -> Optionen als Legacy Konnektoren reaktiviert werden und sind dann wichtig, wenn bestehende Arbeitsmappen mit diesen Verbindungen arbeiten und diese nicht umgestellt werden sollen (weil bspw. keine Transformationen benötigt werden).

Die Integration der beiden – sehr unterschiedlichen – Technologien zur Anbindung externer Daten unter einem UI ist in der aktuellen Version von Excel 2016 bereits sehr weit fortgeschritten.

Quellen

blog.crossjoin.co.uk/2017/03/04/power-queryget-transform-the-future-of-data-loading-in-excel

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Analyse, Dashboarding, Excel als BI Frontend, Reporting