Parametrisierte PowerPivot Modell Aktualisierung in Excel

PowerPivot für Excel ermöglicht Anwendern, relevante Unternehmensdaten aus unterschiedlichen Datenquellen zu importieren, miteinander in Beziehung zu setzen und auszuwerten.

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 nur die Daten ins PowerPivot Modell geladen werden, die er wirklich benötigt. 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.

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:

Parametrisierte PowerPivot Excel 1

Es öffnet sich der Tabellenimport-Assistent, wo wir zuerst die Verbindungsdaten zur AdventureWorksDW2012 Datenbank angeben:

Parametrisierte PowerPivot Excel 2

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‘

Parametrisierte PowerPivot Excel 3

Mit dem Klick auf “Fertig stellen” werden nun die Daten in dem Zeitraum vom 01.07.2005 bis zum 01.07.2006 abgerufen:

Parametrisierte PowerPivot Excel 4

Die Daten wurden ins Power Pivot Modell übertragen und können betrachtet werden (zum Vergrößern anklicken):

Parametrisierte PowerPivot Excel 5

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:

Parametrisierte PowerPivot Excel 6

Hier sehen wir, dass die Verbindung nicht bearbeitet werden kann und wir somit auch keine Änderungen per Makro vornehmen können.

Wir schließen die Verbindungseigenschaften und fügen eine neue Verbindung zum Datenmodell hinzu:

Parametrisierte PowerPivot Excel 7

und wählen in dem Reiter „Tabellen“ unsere Tabelle (hier: „Abfrage“) aus:

Parametrisierte PowerPivot Excel 8

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:

Parametrisierte PowerPivot Excel 9

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.

Parametrisierte PowerPivot Excel 10

Im Kontextmenü der Schaltflächte (rechte Maustaste) wählen wir die Eigenschaften aus und vergeben eine sinnvolle Beschriftung (z.B. „Daten aktualisieren“).

Parametrisierte PowerPivot Excel 11

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 wurde. 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.

Mit diesem vorgestellten Workaround kann der Anwender die Daten sehr einfach auf ein 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).

Bei Rückfragen oder Anmerkungen können sie mich gerne unter alexander.wey@arelium.de kontaktieren.

mm
Alexander Wey
Alexander Wey hat einen Abschluss als Diplom-Informatiker und ist im BI-Umfeld tätig. Der erfahrende Berater beschäftigt sich mit dem vollständigen BI-Lebenszyklus basierend auf den Microsoft BI-Werkzeugen, von der Anforderungsaufnahme über die Datenmodellierung, die Beladung und das Reporting.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.