Freitag, 28. Februar 2014

Beziehungen zwischen Tabellen dokumentieren

"Herzlichen Glückwunsch - Sie haben eine Datenbank gewonnen!" Mit diesen Worten bekam ich vor einigen Jahren anlässlich der Verabschiedung eines Kollegen die Verantwortung für den Betrieb und die Weiterentwicklung einer Datenbank überreicht. Wie Sie sich vorstellen können, hatte ich daraufhin erst einmal einiges an Arbeit, um mir einen Überblick über Art und Umfang des "Geschenks" zu verschaffen.

Beziehungen automatisch dokumentieren


Insbesondere um die existierenden Abfragen gegen die nicht so gut dokumentierte Datenbank zu verstehen und weiterentwickeln zu können, brauchte ich schnell eine Zusammenstellung der Beziehungen zwischen den Tabellen:
  • von Tabelle
  • nach Tabelle
  • Namen der Primär- und Fremdschlüsselspalten
Die folgende Abfrage auf Basis der Sichten in INFORMATION_SCHEMA hat mir dabei sehr geholfen.


SELECT FKT.TABLE_NAME AS FK_Table -- Fremdschlüssel-Tabelle
,CU.COLUMN_NAME AS FK_Column -- Fremdschlüssel-Spalte
,PKT.TABLE_NAME AS PK_Table -- Primärschlüssel-Tabelle
,PKT1.COLUMN_NAME AS PK_Column -- Primärschlüssel-Spalte
,C.CONSTRAINT_NAME AS Constraint_Name -- Name des Constraints
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FKT
  ON C.CONSTRAINT_NAME = FKT.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PKT
  ON C.UNIQUE_CONSTRAINT_NAME = PKT.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
  SELECT TCI.TABLE_NAME
    ,CUI.COLUMN_NAME
    ,CUI.ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCI
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CUI
    ON TCI.CONSTRAINT_NAME = CUI.CONSTRAINT_NAME
  WHERE TCI.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ) PKT1
  ON PKT1.TABLE_NAME = PKT.TABLE_NAME
    AND PKT1.ORDINAL_POSITION = CU.ORDINAL_POSITION

Aus SQL-Sicht ist daran interessant, dass die Tabellen TABLE_CONSTRAINTS und KEY_COLUMN_USAGE mehrfach vorkommen, aber in unterschiedlichen Rollen. Durch Vergabe unterschiedlicher Alias-Namen für dieselbe Tabelle (FKT, PKT, TCI bzw. CU, CUI) wird das unterschieden. So, als handle es sich jedes Mal um eine andere Tabelle. Eine Technik, die Sie immer dann anwenden können, wenn dieselbe Tabelle in einer Abfrage unterschiedliche Rollen einnimmt.

Mittwoch, 19. Februar 2014

Entwurfsmuster für DAX Formeln

Vielen DAX Formeln, die wir in Analysen und Berichten benötigen, liegen immer wieder typische Aufgabenstellungen zugrunde. Zum Beispiel laufende Summen, Lagerbestände, Buchungen und Gegenbuchungen.
Um nicht jedes Mal das Rad neu zu erfinden, ist die Verwendung von Entwurfsmustern ("pattern") eine gute Sache. Sie helfen uns in der jeweiligen Situation schnell einen bewährten Lösungsweg zu finden. Für die noch junge Sprache DAX gab es bisher keine Sammlung solcher Entwurfsmuster. Da ist es ausgesprochen hilfreich, dass die erfahrenen Vorreiter Alberto Ferrari und Marco Russo jetzt diesen Mangel abstellen. Sie stellen unter http://www.daxpatterns.com/  genau solche Muster zur Verfügung.


http://www.daxpatterns.com/
Die Website ist ein Vorbild an Übersichtlichkeit


Die Website ist ausgesprochen übersichtlich, die Texte verständlich geschrieben und mit praxisgerechten Beispielen erklärt.

Fazit: Leider nur auf Englisch verfügbar, aber eine unschätzbare Hilfe!

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/



Freitag, 20. Dezember 2013

PowerPivot Formeln blitzschnell dokumentieren

Für einen komplexen Bericht habe ich etliche Measures erstellt. Bei der Suche nach einer einfachen Möglichkeit, diese "auf Knopfdruck" zu dokumentieren, erwiesen sich die Systemsichten von PowerPivot als ein guter Weg.

Die Lösung im Detail


Grundlage ist ein Excel Arbeitsblatt, das als Datenquelle eine DAX Abfrage an PowerPivot schickt. Wie das geht, habe ich in diesem Beitrag beschrieben.
Die DAX Abfrage zum Auflisten aller Measures sieht so aus:

SELECT *  FROM $SYSTEM.MDSCHEMA_MEASURES

Die DAX Abfrage zum Ermitteln der erstellten Measures in PowerPivot

Das Ergebnis ist eine Liste aller Measures mit ihrer DAX Formel. In dieser Form lässt sich auch eine große Anzahl von Formeln übersichtlich darstellen und überprüfen.

Ausschnitt aus den dokumentierten Formeln


Hintergrundinformationen


Mit Hilfe der $SYSTEM-Views von PowerPivot lassen sich noch viele weitere interessante interne Informationen ermitteln. Die folgende Abfrage etwa listet alle Tabellen (benutzerdefinierte Tabellen und Systemtabellen) auf:

SELECT *  FROM $SYSTEM.DBSCHEMA_TABLES

Liste einiger interessanter Systemtabellen
Durch entsprechende Abfragen auf diese Tabellen lasen sich viele Meta-Informationen ermitteln und stets aktuell in Excel Arbeitsblättern dokumentieren.



Samstag, 30. November 2013

Videos: PowerPivot und Power View einsetzen

Auf Basis eines kleinen Anwendungsbeispiels habe ich zwei Videos erstellt, die den Einsatz von PowerPivot und Power View in Excel 2013 veranschaulichen. Ich hatte eine Excel Liste mit Personen, die eine bestimmte Prüfung abgelegt haben. Dabei handelt es sich um die öffentlich zugängliche Datei der CBAPs, welche die IIBA hier veröffentlicht. Mit diesen Daten wollte ich den jährlichen Zuwachs der zertifizierten Personen und ihre Verteilung auf der Welt darstellen. Herausgekommen ist dabei diese Grafik:


Weil ich für die Erstellung dieser Grafik nur wenige Minuten benötigt habe, hielt ich dies für ein schönes Beispiel des Einsatzes von PowerPivot und Power View.

Das erste Video zeigt folgende Schritte:
  • Erstellen eines einfachen PowerPivot Modells
  • Auswerten der Daten in einer Pivot Tabelle
  • Auswerten der Daten in Power View
Das zweite Video zeigt:
  • Ausblenden nicht benötigter Spalten
  • Hinzufügen eines weiteren berechneten Feldes
  • Hinzufügen einer zweiten Tabelle, um die Verteilung nach Regionen darstellen zu können
Viel Freude beim Ansehen!