Einführung

Wenn ich morgens meine Excel-Pivot-Tabellen öffne und frische Verkaufszahlen aus unserem SSAS-Cube ziehe, merke ich immer wieder, wie kraftvoll diese Kombination aus SQL Server Analysis Services (SSAS) und Microsoft Excel ist. In meinem Arbeitsalltag als BI-Analyst habe ich zahlreiche Berichte und Dashboards gebaut, die erst durch den Mix aus serverseitiger Logik und vertrauter Excel-Oberfläche richtig zum Leben erwachen.

Ich erinnere mich an ein Projekt, bei dem ich hektisch Daten aus verschiedenen Cubes zusammenführen musste – dank einer Live-Verbindung waren die aktuellen Zahlen sofort verfügbar, während ich parallel via Import-Modus eigene Berechnungen im Datenmodell anlegte. Dieses Zusammenspiel spart Zeit und sorgt dafür, dass die Ergebnisse immer aktuell und nachvollziehbar bleiben.

In diesem Artikel teile ich meine Erfahrungen und zeige Schritt für Schritt, wie Sie:

  1. Eine Live-Verbindung zu einem SSAS-Cube einrichten und nutzen
  2. Den Import-Modus in Excel verstehen und effektiv einsetzen
  3. Ihre Datenaktualisierung gezielt steuern und das automatische Refresh beim Öffnen deaktivieren

1. Direkte Verbindung mit SSAS

Die Live-Verbindung (DirectQuery) nutzt SSAS-Leistung in Echtzeit:

  1. Daten > Aus Datenbank > Aus Analysis Services
  2. Server, Instanz und Datenbank wählen
  3. Authentifizierung (Windows/SQL) festlegen
  4. Gewünschten Cube oder Tabular-Modell auswählen
  5. PivotTable als Ausgabe wählen

Vorteile

  • Aggregationen und Berechnungen laufen auf dem SSAS-Server
  • Excel lädt nur Ergebnisse, nicht Rohdaten
  • Daten sind stets aktuell

SSAS Multidimensional vs. Tabular und Abfragesprachen

  • SSAS Multidimensional: Nutzt die klassische OLAP-Engine und wird über MDX (Multidimensional Expressions) abgefragt. MDX-Abfragen definieren Kerndimensionen und Achsen (z. B. SELECT {[Measures].[Sales]} ON COLUMNS, {[Date].[Calendar].[Calendar Year].Members} ON ROWS FROM [SalesCube]).
  • SSAS Tabular: Setzt auf die In-Memory VertiPaq-Engine und verwendet DAX (Data Analysis Expressions) für Abfragen, z. B. EVALUATE SUMMARIZECOLUMNS('Date'[Year], "TotalSales", SUM('Sales'[Amount])).

Excel passt den Abfragetyp automatisch an den verbundenen Modus an: Bei einer Live-Verbindung zu SSAS Multidimensional generiert Excel MDX-Queries basierend auf Slicern und Pivot-Einstellungen, bei einer Tabular-Verbindung sendet es entsprechende DAX-Abfragen an den Server.

2. Import-Modus: So funktioniert Excel intern

Im Import-Modus („In-Memory“) lädt Excel ausgewählte Daten in sein lokales Tabular-Modell (Power Pivot). Hinter den Kulissen arbeiten dabei diese Kernkomponenten:

KomponenteAufgabe
Power QueryExtrahiert, transformiert und lädt Daten aus Quellen
VertiPaq-EngineKomprimiert und speichert Daten spaltenorientiert im RAM
DAX-EngineFührt Berechnungen und Measures auf dem lokalen Modell aus
Power PivotOberfläche zum Definieren von Beziehungen, Hierarchien, DAX

2.1. Import mit Power Query

  1. Daten > Abfragen und Verbindungen > Neue Abfrage > Aus Analysis Services
  2. Server und Datenbank angeben
  3. Navigator: Gewünschte Tabellen/Views auswählen
  4. Daten transformieren öffnet den Power Query-Editor:
    • In diesem Schritt können Sie Zeilen filtern, Spalten entfernen oder umbenennen, Datentypen ändern, Spalten zusammenführen, Pivotierungen/Unpivotierungen durchführen und berechnete Spalten hinzufügen.
    • Außerdem lassen sich komplexe Transformationen durchführen, z. B. Gruppierungen, Splits, bedingte Spalten und M-Skripte.
  5. Schließen & laden in… → Als Datenmodell laden

Beispiel einer Power Query-Transformation:

let
    Quelle = AnalysisServices.Database("Servername\Instanz", "Datenbank"),
    Sales = Quelle{[Schema="dbo",Item="FactSales"]}[Data],
    Gefiltert = Table.SelectRows(Sales, each [Verkaufsdatum] >= #date(2020, 1, 1)),
    Hinzugefügt = Table.AddColumn(Gefiltert, "UmsatzEUR", each [Menge] * [Preis])
in
    Hinzugefügt

2.2. Import mit Power Pivot

Turn on Power Query / Power Pivot (in 90secs) in Microsoft Office Excel 365 
  1. Power Pivot-Reiter aktivieren (Excel-Optionen > Add-Ins)
  2. Aus Datenbank > Aus Analysis Services wählen
  3. Tabellen für den Import markieren
  4. Modellaufbau und Laden starten

siehe dazu:

2.3. Datenkompression & Speicher

  • Spaltenorientierte Speicherung: Jede Spalte wird separat komprimiert
  • Dictionary-Encoding: Wiederkehrende Werte werden durch kurze Codes ersetzt
  • Speichereffizienz: Oft 10–100× bessere Kompression als relationale Tabellenspeicherung
  • In-Memory Processing: Alle Abfragen und DAX-Berechnungen laufen vollständig im RAM
  • Excel-Architektur und Limits:
    • 32‑bit vs. 64‑bit Excel: 32‑bit Excel ist auf 2 GB Prozessspeicher begrenzt; 64‑bit Excel kann deutlich mehr nutzen, abhängig vom Betriebssystem.
    • Maximaler Datenmodell‑Speicher: In Excel Power Pivot ist das Datenmodell auf circa 2 GB begrenzt, egal ob 32‑ oder 64‑bit, um die Integrität zu gewährleisten.

2.4. Modellaufbau und Beziehungen Modellaufbau und Beziehungen

  • Nach dem Import öffnen Sie das Power Pivot-Fenster und wechseln in die Diagrammansicht
  • Dimensionstabellen (z. B. Kunden, Produkte, Datum) verbinden Sie über Schlüsselspalten (1:Many) mit Faktentabellen (z. B. Sales, Transaktionen)
  • Die definierten Beziehungen steuern, wie Filter und Aggregationen durch das Modell propagiert werden
  • Sie können DAX-Measures, berechnete Spalten und Hierarchien direkt in Excel anlegen

2.5. DAX-Berechnungen

  • Measures: Dynamische Kennzahlen wie SUMX, CALCULATE, TIME-INTELLIGENCE-Funktionen
  • Berechnete Spalten: Spalten, die beim Laden einmal berechnet und im Modell gespeichert werden
  • Lokale Ausführung: Keine Roundtrips zum Server, volle DAX-Funktionalität

2.6. Offline-Verfügbarkeit

  • Einmal importierte Daten stehen ohne Serververbindung zur Verfügung
  • Ideal für Reisen, Home-Office oder Workshops ohne Zugriff auf SSAS

3. Datenaktualisierung (Refresh)

FeatureLive-Verbindung (DirectQuery)Import-Modus
Automatisches Refresh beim Öffnennicht vorhanden (kein konfigurierbares Opening-Refresh)optional konfigurierbar über Verbindungseigenschaften („Aktualisieren beim Öffnen“)
Manuelles Refresherfolgt durch PivotTable-Aktualisierung (Rechtsklick → Aktualisieren) oder Ctrl+Alt+F5durch PivotTable-/Abfrage-Aktualisierung oder „Alle aktualisieren“
Automatische Abfrage bei Filteränderungjede Slicer- oder Filter-Aktion löst eine neue MDX-/DAX-Query ausFilter-/Slicer-Änderungen wirken nur auf lokale Daten; keine neuen Serverabfragen
Hintergrundaktualisierungnicht unterstützt (jede Abfrage ist interaktiv und on-demand)unterstützt (Excel läd im Hintergrund, während Sie arbeiten — aktiviert über Verbindungseigenschaften)
Offline-Verfügbarkeitnicht möglich (stetige Serververbindung erforderlich)verfügbar (modellbasiert im RAM, auch ohne Serverzugriff)
DAX-/MDX-Funktionalitätserverseitige Abfragen (MDX bei Multidimensional, DAX bei Tabular)voller lokaler DAX-Funktionsumfang (inkl. Time-Intelligence und komplexer Measures)

3.1 Refresh bei direkter Verbindung

  • Bei einer Live-Verbindung (Direktverbindung zu einem SSAS-Cube) werden Daten nicht automatisch beim Öffnen der Datei aktualisiert. Abfragen laufen on-demand: Excel sendet MDX-/DAX-Anfragen erst, wenn Sie die PivotTable manuell aktualisieren oder ein VBA-/Makro-Skript ausführen. Eine Einstellung „Aktualisieren beim Öffnen“ existiert hier nicht.
  • Manuelle Aktualisierung:
    • Rechtsklick auf PivotTable → Aktualisieren
    • Reiter Daten > Alle aktualisieren (oder Ctrl + Alt + F5) lädt Ergebnisse neu.
  • Interaktive Abfragen bei Filteränderungen: Jedes Mal, wenn Sie in einer Live-Verbindung (z. B. über einen Slicer, Filter oder eine Änderung in einer PivotTable) eine Auswahl treffen, sendet Excel automatisch eine neue MDX-/DAX-Abfrage an den SSAS-Cube. Dadurch erhalten Sie unmittelbar aktualisierte Ergebnisse für genau diese Filterbedingungen.
  • Keine automatische Aktualisierung bei Inaktivität: Wenn Sie jedoch keine Slicer- oder Filteränderungen durchführen, bleiben die bereits abgefragten Daten unverändert, selbst wenn sich der Cube zwischenzeitlich geändert hat. In diesem Fall müssen Sie manuell Aktualisieren klicken, um die neuesten Cube-Daten abzurufen.

3.2 Refresh bei Import

  • Manuelle Aktualisierung:
    • Rechtsklick auf PivotTable oder Abfrage → Aktualisieren
    • Daten > Alle aktualisieren
  • Automatisches Aktualisieren beim Öffnen:
  • Daten > Abfragen und Verbindungen
    • Rechtsklick auf Verbindung → Eigenschaften
    • In PivotTable: PivotTable-Optionen → Daten → „Daten beim Öffnen aktualisieren“
  • Hintergrundaktualisierung:
    • Erlaubt Excel, im Hintergrund Daten zu laden, während Sie arbeiten.
    • Vorteil: Oberfläche bleibt reaktionsschnell.
    • Nachteil: Ergebnisse erscheinen ggf. verzögert.
  • Gezieltes Deaktivieren:
    • Verbindungseigenschaften → Häkchen bei „Aktualisieren beim Öffnen“ entfernen
    • PivotTable-Optionen → „Daten beim Öffnen aktualisieren“ deaktivieren
    • VBA-Workaround:
Private Sub Workbook_Open()
	Application.EnableEvents = False
	'Optional: ThisWorkbook.RefreshAll 
	Application.EnableEvents = True 
End Sub
  • Makros: Nur signierte Makros zulassen

4. Power BI Semantic Model als Basis

3.1 Refresh bei direkter Verbindung

  • Bei einer Live-Verbindung (Direktverbindung zu einem SSAS-Cube) werden Daten nicht automatisch beim Öffnen der Datei aktualisiert. Abfragen laufen on-demand: Excel sendet MDX-/DAX-Anfragen erst, wenn Sie die PivotTable manuell aktualisieren oder ein VBA-/Makro-Skript ausführen. Eine Einstellung „Aktualisieren beim Öffnen“ existiert hier nicht.
  • Manuelle Aktualisierung (siehe oben)
  • Interaktive Abfragen bei Filteränderungen: Jedes Mal, wenn Sie in einer Live-Verbindung (z. B. über einen Slicer, Filter oder eine Änderung in einer PivotTable) eine Auswahl treffen, sendet Excel automatisch eine neue MDX-/DAX-Abfrage an den SSAS-Cube. Dadurch erhalten Sie unmittelbar aktualisierte Ergebnisse für genau diese Filterbedingungen.
  • Keine automatische Aktualisierung bei Inaktivität: Wenn Sie jedoch keine Slicer- oder Filteränderungen durchführen, bleiben die bereits abgefragten Daten unverändert, selbst wenn sich der Cube zwischenzeitlich geändert hat. In diesem Fall müssen Sie manuell Aktualisieren klicken, um die neuesten Cube-Daten abzurufen.
  • Reiter Daten > Alle aktualisieren (oder Ctrl + Alt + F5) lädt Ergebnisse neu.

3.2 Refresh bei Import

  • Manuelle Aktualisierung:
    • Rechtsklick auf PivotTable oder Abfrage → Aktualisieren
    • Daten > Alle aktualisieren
  • Automatisches Aktualisieren beim Öffnen:
    • Daten > Abfragen und Verbindungen: „Aktualisieren beim Öffnen der Datei“ → Häkchen setzen/entfernen„Hintergrundaktualisierung zulassen“ → je nach Bedarf
    • In PivotTable: PivotTable-Optionen → Daten → „Daten beim Öffnen aktualisieren“
  • Hintergrundaktualisierung:
    • Erlaubt Excel, im Hintergrund Daten zu laden, während Sie arbeiten.
    • Vorteil: Oberfläche bleibt reaktionsschnell.
    • Nachteil: Ergebnisse erscheinen ggf. verzögert.
  • Gezieltes Deaktivieren:
    • Verbindungseigenschaften → Häkchen bei „Aktualisieren beim Öffnen“ entfernen
    • PivotTable-Optionen → „Daten beim Öffnen aktualisieren“ deaktivieren
    • VBA-Workaround siehe oben.

4. Power BI Semantic Model als Basis

Ein Power BI Semantic Model ist technisch gesehen ein Tabular-Modell, genau wie SSAS Tabular. Excel verhält sich bei einer Verbindung zu einem Power BI Dataset identisch zu einer SSAS-Tabular-Instanz:

  • Live-Verbindung (DirectQuery): Excel sendet DAX-Abfragen per DirectQuery an das Power BI Dataset (via XMLA-Endpunkt oder Power BI Service), wie bei SSAS-Cubes. Filteränderungen (Slicer, Pivot) lösen sofort neue Queries aus.
  • Import-Modus: Wenn Sie Daten aus einem Power BI Dataset importieren (z. B. über einen XMLA-Emulator), lädt Excel dieselben Tabellen in sein lokale VertiPaq-Modell. Kompression, DAX-Engine und Power Pivot funktionieren unverändert.
  • Authentifizierung: Verbindung zu Power BI Datasets erfolgt über Azure AD-Credentials, im Unternehmensumfeld oftmals Single Sign-On.

Fazit: Ob SSAS Tabular oder Power BI Semantic Model – für Excel stellt beides dasselbe Tabular-Backend bereit. Es gibt keine zusätzlichen „Meta“-Unterschiede; lediglich die Authentifizierungs- und Bereitstellungswege unterscheiden sich.

5. Automatische Aktualisierung in SharePoint

5.1 SharePoint Server (On-Premises)

Auf SharePoint Server (2013/2016/2019) stehen Excel Services bzw. das Power Pivot for SharePoint-Feature zur Verfügung. Damit können Sie Workbooks mit eingebetteten Power Pivot-Modellen oder SSAS-Verbindungen zeitgesteuert aktualisieren:

  • Geplanter Daten-Refresh: In der Dokumentbibliothek registrieren Sie Ihr Workbook, konfigurieren das Unattended Service Account oder Secure Store Service und legen anschließend im Power Pivot-Menü einen Refresh-Zeitplan fest.
  • Authentifizierung: Excel Services nutzt zentral verwaltete Service-Accounts, um sich gegenüber SSAS-Tabular, SSAS-Multidimensional oder relationalen Datenquellen anzumelden.
  • Browser-Aktualisierung: In Excel Web Access (Excel Online Webpart) können Endanwender über die Aktualisieren-Schaltflächen manuell aktualisieren oder den geplanten Zeitplan abwarten.
  • Einschränkungen: Power Query-Verbindungen und neuere Datenquellen werden in Excel Services nicht unterstützt. Nur Direktverbindungen zu SSAS oder Power Pivot-Modelle sind planbar.

5.2 SharePoint Online (Office 365)

In SharePoint Online (Excel Online) existiert kein Excel Services mehr im klassischen Sinne.

  • Interactive Refresh: Verbindungen zu SSAS-Tabular oder Power BI Semantic Models können im Browser manuell aktualisiert werden (Schaltflächen in der Excel-Oberfläche), sofern das Modell über den XMLA-Endpunkt bereitgestellt ist.
  • Kein geplanter Workbook-Refresh: Eine unbeaufsichtigte oder zeitgesteuerte Aktualisierung von Excel-Dateien auf SharePoint Online ist nicht nativ verfügbar.
  • Alternativen:
    • Power Automate: Mit einem Flow und ggf. einem PowerShell-Skript (über Azure Automation) lässt sich eine funktionale Automatisierung realisieren, die Workbooks per Office 365 API oder COM-API öffnet und aktualisiert.
    • Power BI: Laden Sie Ihr Excel-Modell in Power BI (import oder live connect) und nutzen Sie die dort verfügbaren zeitgesteuerten Refresh-Optionen.

Zusammenfassung: SharePoint Online bietet nur interaktive Browser-Refreshes in Excel Online. Für geplantes, unattended Data Refresh müssen Sie auf Power Automate, Azure Automation oder Power BI zurückgreifen.

6. Best Practices

  • Gezielter Datenimport: Laden Sie nur erforderliche Spalten/Zeilen (Filter im Power Query)
  • Beziehungen sauber definieren: Kardinalitäten korrekt einstellen (1:Many, Many:1)
  • Datenmodell-Optimierung:
    • Wählen Sie geeignete Datentypen (z. B. Integer statt String für Schlüssel)
    • Verwenden Sie Calculated Tables nur bei Bedarf
  • Versionierung: Speichern Sie Vorlagen ohne Daten (nur Modell-Definition), um Speicherplatz zu sparen
  • Dokumentation: Pflegen Sie in einem separaten Blatt Kurzbeschreibungen zu Abfragen, Tabellen und Measures

Fazit

Im Import-Modus wirkt Excel als leichter Power BI-Client:

  • Power Query holt und transformiert Daten
  • VertiPaq komprimiert und hält alles im RAM
  • DAX erlaubt flexible Berechnungen lokal
  • Power Pivot bietet die Modell- und Beziehungsverwaltung

So entstehen leistungsfähige, offline nutzbare Datenmodelle direkt in Excel, bei gleichzeitig hoher Performance und voller DAX-Funktionalität. Die Steuerung der Aktualisierung – manuell, beim Öffnen oder im Hintergrund – gibt Ihnen die nötige Kontrolle über Datenfrische und Arbeitsablauf.

weiterführende Links

1. Live-Verbindung zu SSAS

Related posts:

Default ThumbnailPivot-Punkte Default ThumbnailPowerBI Default ThumbnailAnalyse eines SSAS-Cubes Code ausführen beim Aktivieren & Verlassen einer Excel Arbeitsmappe & Tabellenblattes