Einleitung
PowerPivot für Excel ermöglicht Anwendern, relevante Unternehmensdaten aus unterschiedlichen Datenquellen zu importieren, miteinander in Beziehung zu setzen und auszuwerten. Dies ist im Bereich der Business Intelligence sehr wichtig. Hierfür verwendet man häufig ein PowerPivot Modell, welches die gesamten Daten der letzten Jahre bereitstellt. Meistens möchte der Anwender jedoch nur einen kleinen Zeitraum (<= 18 Monate) betrachten. Hierfür schränkt er die große Datenmenge mit Hilfe von Filtern ein. Performanter wäre es, wenn man nur die benötigten Daten ins PowerPivot Modell lädt. Der Anwender könnte das SQL Statement der Verbindung zum SQL Server anpassen. Dies geht aber auch ohne SQL Kenntnisse seitens der Anwender und etwas eleganter. In diesem Blogeintrag zeige ich Euch, wie man mit Hilfe einer Stored Procedure und einem kleinen Excel-Makro das PowerPivot Modell dynamisch aktualisieren kann. Hierzu verwende ich Excel 2016 und den SQL Server 2012. Parametrisierte PowerPivot in Excel ganz einfach aktualisieren!
Der Vorgang
Es öffnet sich der Tabellenimport-Assistent, wo wir zuerst die Verbindungsdaten zur AdventureWorksDW2012 Datenbank angeben:
In meinem Beispiel wird die alt bekannte AdventureWorksDW2012 Datenbank (kostenloser Download unter CodePlex) verwendet.
Für die Tabelle FactInternetSales erstellen wir eine Prozedur, mit 2 Parametern Startdatum und Enddatum. Die Prozedur liefert alle Datensätze dieser Tabelle zurück, deren Bestelldatum (OrderDate) zwischen dem angegebenen Start- und Enddatum liegen.
Hier das Skript für die entsprechende Prozedur:
create procedure dbo.p_get_FactInternetSales @startdatum datetime, @enddatum datetime as begin select * from dbo.FactInternetSales where OrderDate between @startdatum and @enddatum end
Im Menüband von Excel wählen wir die Registerkarte „Power Pivot“ aus und gehen auf „Verwalten“. Zu Beginn fügen wir eine Tabelle dem Power Pivot Modell wie folgt hinzu:
In der Gruppe „Externe Daten abrufen“ wählen wir „Aus Datenbank“ und danach „Aus SQL Server“ aus:
Der Import
Es öffnet sich der Tabellenimport-Assistent, wo wir zuerst die Verbindungsdaten zur AdventureWorksDW2012 Datenbank angeben:
Auf der nächsten Seite des Assistenten wählen wir „Abfrage zur Angabe der zu importierenden Daten schreiben“ aus und tragen unseren Prozeduraufruf, wie folgt ein:
exec p_get_FactInternetSales ‚20050701‘, ‚20060701‘
Mit dem Klick auf “Fertig stellen” werden nun die Daten in dem Zeitraum vom 01.07.2005 bis zum 01.07.2006 abgerufen:
Die Verbindung herstellen
Die Daten wurden ins Power Pivot Modell übertragen. Nun kann man sie betrachten.
Als Nächstes fügen wir eine Verbindung hinzu. Hierzu schließen wir das Power Pivot Fenster (Datei → Schließen) und wählen in der Registerkarte „Daten“ „Verbindungen“ aus.
Problematisch ist, dass man die Verbindung nicht bearbeiten kann. Somit können wir auch keine Änderungen per Makro vornehmen.
Wir schließen die Verbindungseigenschaften und fügen eine neue Verbindung zum Datenmodell hinzu:
und wählen in dem Reiter „Tabellen“ unsere Tablle (hier: „Abfrage“) aus:
Schaut man jetzt in die Eigenschaft unter Definition nach, so wird man feststellen, dass u.a. die Felder „Verbindungszeichenfolge“ und „Befehlstext“ editiert werden können. Dies ermöglicht auch eine Änderung per Makro.
Vorsicht: Der Name „AdventureWorksDW2012 – Abfrage“ der neu hinzugefügten Tabelle darf nicht umbenannt werden. Es dürfen auch keine Spalten umbenannt werden, da sich sonst die Verbindungseigenschaften nicht mehr editieren lassen und somit das Makro, welches wir nachher erstellen werden nicht mehr funktioniert. Soll dem Anwender ein anderer Spaltenname angezeigt werden, z.B. soll anstelle „SalesAmount“ nur der Spaltenname „Amount“ erscheinen, geht man wie folgt vor: Man erstellt eine neue Spalte und nennt diese „Amount“ und fügt dieser die DAX-Formel: =[SalesAmount] hinzu. Die ursprüngliche „SalesAmount“ Spalte kann per rechter Maustaste und Auswahl von „Aus Clienttools ausblenden“ für den Anwender ausgeblendet werden. Somit wird derselbe Spalteninhalt unter anderem Namen dem Anwender zur Verfügung gestellt.
Nun erstellen wir für den Anwender ein Excel-Sheet, indem er das gewünschte Start- und Enddatum eintragen kann und die Datenaktualisierung starten kann:
Nun fügen wir noch eine Schaltfläche hinzu. Hierzu benötigen wir die „Entwicklertools“. Sofern dieser Eintrag noch nicht im Menüband erscheint, so muss dieser erst unter Datei -> Optionen -> „Menüband anpassen“ aktiviert werden.
Im Kontextmenü der Schaltflächte (rechte Maustaste) wählen wir die Eigenschaften aus und vergeben eine sinnvolle Beschriftung (z.B. „Daten aktualisieren“).
Makroprogrammierung
Mit einem Doppelklick auf die Schaltfläche oder der Tastenkombination ALT + F11 gelangen wir zur Makroprogrammierung.
Hier tragen wir folgenden Code ein:
Private Sub CommandButton1_Click() If IsDate(Range(„A2“).Value) And IsDate(Range(„B2“).Value) Then
ActiveWorkbook.Connections(„Connection1“).OLEDBConnection.CommandText = „exec [dbo].[p_get_FactInternetSales] ‚“ & Format(Range(„A2“).Value, „yyyymmdd“) & „‚, ‚“ & Format(Range(„B2“).Value, „yyyymmdd“) & „‚“
ActiveWorkbook.Connections(„Connection1“).Refresh MsgBox „Die Daten vom “ & Range(„A2″).Value & “ bis zum “ & Range(„B2″).Value & “ wurden aktualisiert“, vbInformation, „Erfolgreiche Aktualisierung“ Else MsgBox („Kein gültigen Zeitraum eingetragen“) End If End Sub
Hier wird zuerst überprüft, ob in den Zellen A2 und B2 jeweils ein korrektes Datum eingetragen ist. Bei einer falschen Eingabe wird ein Hinweis „Kein gültigen Zeitraum eingetragen“ ausgegeben . Falls 2 korrekte Datumangaben vorhanden sind, wird die zuvor erstellte Prozedur p_get_FactInternetSales mit diesen beiden Datumsangaben als Parameter aufgerufen. Hier verwenden wir die editierbare Verbindung „Connection1“, die wir als letztes eingerichtet haben. Durch den Befehl Refresh, werden die Daten dann ins Power Pivot Modell geladen. Zum Schluß erhält der Anwender einen Hinweis über über die erfolgreiche Aktualisierung.
Fazit
Mit diesem vorgestellten Workaround kann der Anwender die Daten sehr einfach auf einen bestimmten Zeitraum einschränken und ohne SQL-Modifikationen ins Power Pivot Modell laden. Andere Filtermöglichkeiten wären hier natürlich auch denkbar. Unschön bleibt jedoch die Tatsache, dass dieser Workaround nicht mehr funktioniert, wenn man den Tabellennamen ändert (siehe oben).
