Freitag, 24. Januar 2014

Speicherprobleme in PowerPivot verstehen und beheben

PowerPivot erlaubt das blitzschnelle Analysieren von Tabellen mit vielen Millionen Zeilen.
Die Aussage trifft auf jeden Fall zu, wenn Sie die 64-Bit Version von Excel einsetzen und der Rechner über 8GB oder mehr Arbeitsspeicher verfügt. Allerdings sind viele Anwender auf die 32-Bit Version von Excel beschränkt. Ich arbeite gerade in so einer Umgebung, wo auch noch die 32-Bit Version von Windows 7 mit 4GB RAM im Einsatz ist. Unter diesen Umständen kann PowerPivot nach meinen Beobachtungen nur maximal ca. 700MB Arbeitsspeicher verwenden – unabhängig davon, mit wie viel RAM der Rechner ausgestattet ist. Daher wird der Import immer wieder mal mit einer Fehlermeldung wie dieser abgebrochen:
  • "Memory error: Allocation failure : Not enough storage is available to process this command. . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine."
    (Excel 2010, englisch)
  • "Die Verbindung konnte nicht aktualisiert werden. Rufen Sie die vorhandenen Verbindungen auf, und überprüfen Sie, ob eine Verbindung mit der Datei bzw. dem Server besteht."
    (Excel 2013, deutsch)
Der größte Bedarf an Speicherplatz tritt während des Aktualisierens einer bestehenden Tabelle auf. Dann benötigt Excel ausreichend Arbeitsspeicher
  • für die bestehenden Daten
  • und für alle neu zu importierenden Daten
  • und für das Erstellen der neuen Indexe
Dadurch steigt der Speicherbedarf während des Imports etwa auf das Dreifache an. In 32-Bit Umgebungen ergeben sich daraus schon bei relativ kleinen Datenmengen (je nach Art der Daten ca. 1 Million Zeilen) Probleme.
 

Gewusst wie: den Speicherbedarf verringern


Die beiden nachfolgenden Regeln helfen, den Speicherbedarf von PowerPoint Modellen zu reduzieren. Das ist nicht nur in 32-Bit Umgebungen wichtig; auch wenn in 64-Bit Umgebungen sehr viel mehr Speicher zur Verfügung steht, ist dieser nicht unbegrenzt.


Regel 1: Nur die erforderlichen Spalten aus den Quelltabellen laden


Spalten, deren Inhalt in keiner Auswertung verwendet wird, sollten Sie aus dem PowerPivot Modell löschen. Falls Sie zu einem späteren Zeitpunkt eine gelöschte Spalte wieder hinzufügen möchten, ist das ohne Probleme möglich. Auch aus Gründen der Übersichtlichkeit empfiehlt es sich sowieso, nur mit den nötigsten Spalten zu beginnen und erst bei Bedarf weitere hinzuzufügen.


Regel 2: Keine bzw. wenige Nachkommastellen laden


Für den Speicherbedarf ist die Anzahl unterschiedlicher Werte in einer Spalte entscheidend. PowerPivot erstellt automatisch einen Index auf jede Spalte, der einen Eintrag für jeden unterschiedlichen Wert enthält. Zwei Zeilen, die in einer Spalte  die Werte 12,300 und  12,301 enthalten, brauchen also mehr Speicher als zwei Zeilen, die in dieser Spalte jeweils die Zahl 12,30 enthalten. Da für Auswertungen über große Datenmengen oft nur wenige oder gar keine Nachkommastellen benötigt werden, sollten Sie Fließkommazahlen bereits beim Import (bei einem SQL Befehl z.B. mit der Funktion ROUND) runden.


Ein Experiment


Um den Speicherbedarf unterschiedlicher Daten besser zu verstehen, habe ich einmal eine Tabelle mit 500.000 Datensätzen erzeugt, die folgende Spalten enthält  (SQL Code am Ende des Artikels):

iAufsteigende Ganzzahlen
KonstanterIntimmer 0
Werte0bis9Nur Werte zwischen 0 und 9
KonstanterStringimmer 'abc'
ZufallswerteIntzufällig erzeugte Ganzzahlen
ZufallswerteFloatzufällig erzeugte Fließkommazahlen

Um die Auswirkungen der unterschiedlichen Zahlenverteilung zu sehen, habe ich nach dem Import  mit folgender DAX Abfrage den Speicherbedarf der einzelnen Spalten ermittelt:

select Attribute_Name, Dictionary_Size
from $system.DISCOVER_STORAGE_TABLE_COLUMNS 
where Dictionary_Size > 0

Eine Anleitung, wo Sie diese Abfrage eingeben können, finden Sie hier.

Und das ist das interessante Ergebnis dieses Experiments. Die Tabelle zeigt für jede Spalte den Speicherbedarf im PowerPivot Modell:


Der Speicherbedarf der Spalten ist demnach sehr unterschiedlich:
  • Die Spalten "i" und "RowNumber" (eine interne Spalte) belegen mit 64 Bytes fast gar keinen Speicher – wie auch immer PowerPivot das bewerkstelligt.
  • Wie zu erwarten, belegt die Spalte "KonstanterInt" nur sehr wenig Speicherplatz.
  • Direkt darauf folgt die Spalte "Werte0bis9", die nur 10 unterschiedliche Werte enthält und deren Inhalt daher sehr stark komprimiert werden kann.
  • Die Spalte "KonstanterString" belegt mit etwa 1 MB überraschend viel Platz. Da alle Spalten denselben Wert enthalten, hätte ich mit weniger gerechnet. Anscheinend benötigt PowerPivot für die Verwaltung von Zeichenketten viel mehr Platz als für die Verwaltung von Integer-Zahlen. Dafür verändert sich jedoch der Platzbedarf auch nicht, wenn die Zeichenkette erheblich länger wird.
  • Erheblich mehr Speicher benötigt die Spalte mit den vielen unterschiedlichen Integer-Werten "ZufallsWerteInt", nämlich etwa 10.000-mal so viel Platz wie die Spalte "Werte0bis9", die nur 10 unterschiedliche Werte enthält.
  • Und die Spalte "ZufallswerteFloat" belegt aufgrund der Nachkommastellen noch einmal doppelt so viel Platz wie die zufälligen Integer-Werte.
Kurz gesagt, hat die Anzahl unterschiedlicher Werte in einer Spalte einen erheblich größeren Einfluss auf den Speicherbedarf als der Datentyp.
 

Der SQL Code zum Erzeugen der Tabelle


CREATE TABLE TestData (
       i INT NOT NULL
       ,ZufallsWerteInt INT
       ,ZufallswerteFloat FLOAT
       ,KonstanterInt INT 
       ,KonstanterString VARCHAR(1000)
       ,Werte0bis9 INT
       )
GO

-- Füge 1 Zeile ein
INSERT TestData (i, KonstanterInt, ZufallsWerteInt, ZufallswerteFloat, KonstanterString, Werte0bis9)
VALUES (1, 0, 0, 0.0, 'abc', 0)
GO

-- Füge 2^19 Zeilen ein
INSERT TestData (i, KonstanterInt, ZufallsWerteInt, ZufallswerteFloat, KonstanterString, Werte0bis9)
SELECT (
             SELECT max(i)
             FROM TestData
             ) + row_number() OVER (
             ORDER BY current_timestamp
             ), 0, checksum(newid()), checksum(newid()) / 3.0, 'abc', ABS(checksum(newid())) % 10
FROM TestData
GO 19


Weitere Links zu diesem Thema

http://www.sqlbi.com/articles/checklist-for-memory-optimizations-in-powerpivot-and-tabular-models

http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx

http://denglishbi.wordpress.com/2010/06/21/powerpivot-memory-error-%E2%80%93-not-enough-storage/