Power-Pivot ist eine Datenmodellierungstechnologie, mit der Sie Datenmodelle erstellen, Beziehungen herstellen und Berechnungen hinzufügen können. Mit Power Pivot können Sie mit großen Datensätzen arbeiten, detaillierte Beziehungen erstellen und komplexe (oder einfache) Berechnungen erstellen – alles in der vertrauten, leistungsstarken Excel-Umgebung.

Power Pivot ist eines von drei in Excel verfügbaren Datenanalysetools:

Power Pivot-Ressourcen

Wie bekomme ich Power Pivot?

Power Pivot kann als Excel-Add-In verwendet werden, das in wenigen einfachen Schritten aktiviert werden kann. Die zugrunde liegende Modellierungstechnologie von Power Pivot wird auch im Power BI Designer verwendet, der Teil des Power BI-Dienstes von Microsoft ist.

Erste Schritte mit Power Pivot

Wann Power Pivot-Add-In aktiviert ist, wird im Menüband eine Registerkarte angezeigt Power-Pivot, was im folgenden Bild dargestellt ist.

Auf der Registerkarte Multifunktionsleiste Power-Pivot„Artikel auswählen“ Kontrolle„im Abschnitt“ Datenmodell ".

Bei der Auswahl eines Artikels Kontrolle erscheint Power Pivot-Fenster Hier können Sie das Datenmodell anzeigen und bearbeiten, Berechnungen hinzufügen, Verbindungen herstellen und Elemente anzeigen Datenmodelle Power-Pivot. Datenmodell ist eine Sammlung von Tabellen oder anderen Daten, oft mit etablierten Beziehungen zwischen ihnen. Das folgende Bild zeigt Power Pivot-Fenster mit der angezeigten Tabelle.

IN Power Pivot-Fenster Es ist auch möglich, Beziehungen zwischen den im Modell enthaltenen Daten herzustellen und grafisch darzustellen. Klicken Sie auf das Symbol Diagrammdarstellung in der unteren rechten Ecke Power-Pivot-Fenster, sehen Sie die vorhandenen Beziehungen im Power Pivot-Datenmodell. Das Bild unten zeigt Power Pivot-Fenster V Präsentation des Diagramms.

Eine kurze Anleitung zur Verwendung von Power Pivot finden Sie im folgenden Artikel:

Zusätzlich zu diesem Leitfaden finden Sie unter folgendem Link erschöpfend Eine Sammlung von Links, Ressourcen und zusätzlichen Informationen zu Power Pivot:

In den folgenden Abschnitten werden zusätzliche Ressourcen und Tutorials aufgeführt, die detailliert beschreiben, wie Sie Power Pivot, auch in Kombination mit Power Query und Power View, verwenden, um komplexe, intuitive Business-Intelligence-Aufgaben in Excel selbst durchzuführen.

PowerPivot-Tutorials

Sehen Sie Power Pivot in Aktion, um die Funktionsweise zu verstehen und nützliche Anwendungsfälle zu sehen, die die Fähigkeiten von Power Pivot demonstrieren. Die folgenden Leitfäden helfen Ihnen beim Einstieg:

Erfahren Sie mehr über Power Pivot

Power Pivot ist einfach zu bedienen und schnell. Mit diesem Add-In können Sie umfangreiche und komplexe Berechnungen, Indikatoren und Formeln erstellen. Die folgenden Links helfen Ihnen dabei, viele der Aufgaben zu identifizieren, die Sie mit Power Pivot erledigen können.

Links zu Foren und verwandten Themen

Viele verschiedene Menschen nutzen Power Query und geben gerne weiter, was sie gelernt haben. Nutzen Sie die folgenden Ressourcen, um mit anderen Mitgliedern der Power Query-Community in Kontakt zu treten.

[im Zusammenhang mit der umstrittenen Übertragung von Teil 1 des Beitrags an Geektimes (trotz der Tatsache, dass der 2. Teil bei Habré verblieben ist) bringe ich den 1. Teil an seinen Platz zurück]

Wenn Sie im Bereich Analyse und Überwachung verschiedener BI-Tools arbeiten, stoßen Sie früher oder später auf eine Rezension oder Erwähnung des Power Pivot Excel-Add-Ins. In meinem Fall traf ich ihn auf der Microsoft Data Day-Konferenz.

Nach der Präsentation hinterließ das Tool keine besonderen Eindrücke: Ja, es ist kostenlos (unter der Office-Lizenz), ja – es gibt einige ETL-Funktionalitäten hinsichtlich des Abrufens von Daten aus unterschiedlichen Quellen (DB, CSV, XLS usw.). Durch die Verknüpfung dieser Quellen und die Einspeisung in den RAM werden Größenordnungen von mehr als 1 Million Zeilen in Excel erfasst. Kurz gesagt, ich habe nachgeschaut und es vergessen.

Und ich musste mich daran erinnern, wann die Notwendigkeit entstand, bestimmte Phänomene in den Daten zu identifizieren

Was mich dazu veranlasst hat, diesen Artikel zu schreiben, ist, dass es im RuNet nicht viele detaillierte Informationen zu diesem Tool über bestimmte Arbeitsmethoden gibt, hauptsächlich über die Sterne, daher habe ich beschlossen, diese Rezension zu schreiben, während ich dieses Tool studiert habe.

Tatsächlich lautet die Problemstellung (anhand eines unpersönlichen Beispiels) wie folgt:

In den Quelldaten der CSV-Datei:

Es gibt Einzelhandelsgeschäfte, die bis auf Rechnungszeilen detailliert sind, während Filialen mit demselben Namen nur dann unterschiedliche Adressen haben dürfen, wenn sie sich in verschiedenen Städten befinden, aber im ursprünglichen Datenfeld gibt es Punkte, die unterschiedliche Adressen in derselben Stadt haben trotz der Tatsache, dass die Namen der Punkte gleich sind (der Name der Verkaufsstelle ist eindeutig, d. h. es handelt sich um eine Einheit des Netzwerks oder einen separaten Punkt). Als Sonderfall in aggregierter Form:

Die Suche und Bereinigung von Daten mit Standard-Office-Tools wird durch folgende Umstände behindert:

Detaillierte Daten bis hin zu Rechnungszeilen
Anzahl der Datensätze in mehreren Millionen Zeilen
Mangel an SQL-Tools (Beispiel: Access – nicht im Lieferumfang enthalten)

Natürlich kann man jedes kostenlose DBMS (sei es eine Desktop-Version oder eine Server-Version) hochladen, dafür braucht man aber erstens Admin-Rechte und zweitens würde es in dem Artikel nicht mehr um Power Pivot gehen.

Aufgabe: Für jeden atomaren Datensatz ist ein zusätzliches berechnetes Feld erforderlich, das für jeden Namen der Filiale eine eindeutige Anzahl von Adressen innerhalb derselben Stadt berechnet. Dieses Feld ist erforderlich, um schnell alle Namen von Einzelhandelsgeschäften in einer Stadt zu finden, in der es mehr als eine Adresse gibt.

Ich denke, es ist am bequemsten, iterativ zu lösen und zu sagen, unter der Annahme, dass unser Wissen über DAX auf einem rudimentären Niveau ist.
Deshalb schlage ich vor, mich vorerst von der Aufgabe zu lösen und einige grundlegende Aspekte zu betrachten.

Schritt 1: Wie unterscheidet sich eine berechnete Spalte von einer berechneten Kennzahl?
Hier ist ein Beispiel einer berechneten Spalte zum Extrahieren der Mehrwertsteuer aus einem Mehrwertsteuer-Sendungsfeld mithilfe integrierter DAX-Formeln:

RUND([Versand mit MwSt.]*POWER(1.18,-1)*0.18,2)

Wie Sie dem Beispiel entnehmen können, funktioniert die berechnete Spalte (nennen wir sie Mehrwertsteuer) horizontal mit jedem atomaren Datensatz.
Fügen wir nun ein berechnetes Feld für den Stückpreis ohne Mehrwertsteuer hinzu:

RUND([Versand mit MwSt.]*POWER(1,18,-1)/[Versand Stück];2)

Fügen wir nun zum Vergleich noch die Berechnung des durchschnittlichen Stückpreises zu dem Maß hinzu:

Durchschnittspreis pro Stück ohne Mehrwertsteuer: =ROUND(AVERAGE([Field_Price pro Stück ohne Mehrwertsteuer]);2)

Wie aus der Formel hervorgeht, funktioniert die Kennzahl vertikal mit einer Spalte mit Quelldaten, daher sollte sie immer eine Art Funktion enthalten, die mit einer Menge funktioniert (Summe, Durchschnitt, Varianz usw.).

Bei der Rückkehr zur Excel-PivotTable sieht es so aus:

Bitte beachten Sie, dass, wenn das berechnete Mehrwertsteuerfeld auf jeder Datenebene (grüner Strich auf der Ebene einer Einzelhandelsfiliale, einer Stadt oder einer Tabellensumme) den Betrag anzeigt, der im Prinzip korrekt ist, dann die Summe der Preise des berechneten Felds „ „Preis pro Stück ohne Mehrwertsteuer“ (roter Strich) wirft Fragen auf.
Aber das berechnete Maß „Durchschnittspreis pro Stück ohne Mehrwertsteuer“ hat im Rahmen dieses Analysewürfels eine Lebensberechtigung.

Daraus schließen wir, dass das berechnete Feld „Preis pro Stück ohne Mehrwertsteuer“ ein Hilfsmittel zur Berechnung des Maßes „Durchschnittspreis pro Stück ohne Mehrwertsteuer“ ist und um den Benutzer nicht mit diesem Feld zu verwirren, werden wir es aus der Liste ausblenden der Kundentools, wobei das durchschnittliche Preismaß übrig bleibt.

Ein weiterer Unterschied zwischen einer Kennzahl und einer Spalte besteht darin, dass Sie damit eine Visualisierung hinzufügen können:

Lassen Sie uns beispielsweise einen KPI für den Grad der Preisstreuung mit einer Zielgrenze von 35 % erstellen, indem wir die Wurzel der Streuung durch das arithmetische Mittel dividieren.

K_var:=STDEV.P([Field_Price pro Stück ohne MwSt.])/AVERAGE([Field_Price pro Stück ohne MwSt.])

Als Ergebnis sehen wir in Excel folgende Tabelle (das berechnete Hilfspreisfeld befindet sich übrigens nicht mehr in der Liste der verfügbaren Felder rechts):

Ein Doppelklick auf die 80 %-Quote zeigt, dass die Preise tatsächlich um den Durchschnitt schwanken:

Stärker als mit einem Koeffizienten von 15 %:

In diesem Schritt haben wir uns die Hauptunterschiede zwischen Kennzahlen und Feldern in PowerPivot angesehen.

Schritt 2. Machen wir es komplizierter: Berechnen wir den Anteil jedes Eintrags am Gesamtumsatz.
Hier ist das erste Beispiel, das die Fensterfunktionsansätze von MS SQL Server und DAX vergleicht:

Es ist klar, dass dies im Rahmen von Pivot-Tabellen buchstäblich mit zwei Mausklicks erledigt wird, ohne die Tastatur zu berühren, aber um es zu verstehen, versuchen wir es direkt in PowerPivot mithilfe von Formeln.

In SQL würde ich es so schreiben (machen Sie mir nicht die Schuld für Fehler, denn Word prüft die SQL Server-Syntax nicht):

Beginnen Sie mit der Auswahl von „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs“, „ t1.Shipment with VAT“, „t1.Shipment, pcs“/sum(“t1.Shipment, pcs“) über () als Anteil aus Tabelle als t1 order by „t1.Shipment, pcs“/sum(“t1.Shipment , Stück") desc

Wie Sie sehen, öffnet sich hier ein Fenster mit allen Datensätzen im Datensatz. Versuchen wir etwas Ähnliches in PowerPivot:

=[Sendungsstück]/BERECHNEN(SUM([Sendungsstück]);ALL("Tabelle1"))

Konzentrieren wir uns auf den Nenner: Ich habe oben bereits erwähnt, dass der Hauptunterschied zwischen einem berechneten Feld und einer Kennzahl darin besteht, dass sie in einem Formelfeld horizontal zählen (innerhalb eines Datensatzes) und Kennzahlen vertikal zählen (innerhalb eines Attributs). Hier konnten wir die Feldeigenschaften und die Kennzahleneigenschaft durch die CALCULATE-Methode kreuzen. Und wenn wir die Breite des Fensters in SQL mit Over() angepasst haben, dann haben wir das hier mit All() gemacht.

Versuchen wir nun, mit dieser Fähigkeit etwas Nützliches aus unseren Daten zu machen. Denken wir beispielsweise daran, dass der Indikator der Preisstreuung um den Durchschnitt über einen weiten Bereich variiert. Versuchen wir, statistische Ausreißer bei den Preisen mithilfe der 3-Sigma-Regel zu identifizieren.

Fensterfunktionen in SQL sehen folgendermaßen aus:

Wählen Sie „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs“, „t1 .Versand mit Mehrwertsteuer“, „t1.Preis pro Stück ohne Mehrwertsteuer“, CASE WHEN ABS(“t1.Preis pro Stück ohne Mehrwertsteuer“ – AVG(“t1.Preis pro Stück ohne Mehrwertsteuer“) OVER()) > 3 * STDEV ("t1.Preis pro Stück ohne Mehrwertsteuer") OVER() THEN 1 ELSE 0 END as Outlier from Table as t1 Go

Und hier ist das Gleiche im DAX:

If(ABS([Field_Price pro Stück ohne MwSt.]-CALCULATE(AVERAGE([Field_Price pro Stück ohne MwSt.]);ALL("Table1")))>(3*CALCULATE(STDEV.P([Field_Price pro Stück ohne MwSt.] );all("Tabelle1")));1;0)

Wie Sie sehen, ist der Preis mit einem arithmetischen Durchschnitt von 40,03 Rubel etwas hoch.

Schritt 3. Verengen Sie die Fenster.
Versuchen wir nun, im berechneten Feld jedes Datensatzes die Gesamtzahl der Datensätze innerhalb der Stadt zu zählen, zu der dieser Datensatz gehört.
Auf MS SQL Server sehen Fensterfunktionen folgendermaßen aus:

Wählen Sie „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs“, „t1 .Versand mit Mehrwertsteuer", "t1.Preis pro Stück ohne Mehrwertsteuer", count("t1.*) OVER(partition by "t1.City") as cnt from Table as t1 Go

Im DAX:
=BERECHNEN(COUNTROWS("Tabelle1");ALLEXCEPT("Tabelle1","Tabelle1"[Stadt]))

Bitte beachten Sie den Unterschied in der Anzeige der Daten in der Tabelle. Ich habe speziell Adressen in den Maßnahmenbereich eingefügt, um deren Anzahl zu zählen und sie mit dem neuen Feld zu vergleichen, das ich in der Zeilenüberschrift nach dem Namen der Filiale platziert habe.

Der Unterschied ist deutlich sichtbar: Wenn die übliche Berechnung der Anzahl der Adressen für jeden Punkt in der Stadt durchgeführt wird und dann nur eine Zwischensumme für das Aggregat „Stadt“ angezeigt wird, dann ermöglicht die Verwendung von Fensterfunktionen die Zuordnung jedes Atoms Zeichnen Sie den Wert eines beliebigen Aggregats auf oder verwenden Sie ihn in einigen Zwischenberechnungen des berechneten Felds (wie oben gezeigt).

Zurück zum ursprünglichen Problem
Lassen Sie mich Sie an die erste Problemstellung erinnern: Für jeden atomaren Datensatz ist ein zusätzliches berechnetes Feld erforderlich, das für jeden Namen der Filiale eine eindeutige Anzahl von Adressen innerhalb derselben Stadt berechnet. Vergessen Sie nicht, dass unser Datensatz bis in die Rechnungszeilen detailliert ist. Bevor Sie also die Adressen im Fenster berechnen, müssen diese gruppiert werden.

Anfrage an SQL Server:

Mit a1 als (Wählen Sie „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs ", "t1.Shipment with VAT", "t1.Price per Piece without VAT", count(Distinct "t1.Address") OVER(partition by "t1.City", "t1.TT Name") als Adrcnt aus Tabelle als t1) Wählen Sie * aus a1, wobei adrcnt>1 ist

Nun hindert uns nichts mehr daran, dies im DAX zu tun:

CALCULATE(DISTINCTCOUNT("Table1"[Address]);ALLEXCEPT("Table1","Table1"[City],"Table1"[TT Name]))

Dadurch konnten wir verdächtige Datensätze auswählen, bei denen es mehr als eine Adresse am selben Punkt in derselben Stadt gab.

Natürlich wird beim Studium (bei einem Blick auf andere Formeln) klar, dass DAX in PowerPivot viel leistungsfähiger ist als in diesem Thema gezeigt, aber es ist definitiv nicht möglich, die Unermesslichkeit auf einmal zu erfassen.

Ich hoffe, es war interessant.
Fortsetzung des Artikels

[im Zusammenhang mit der umstrittenen Übertragung von Teil 1 des Beitrags an Geektimes (trotz der Tatsache, dass der 2. Teil bei Habré verblieben ist) bringe ich den 1. Teil an seinen Platz zurück]

Wenn Sie im Bereich Analyse und Überwachung verschiedener BI-Tools arbeiten, stoßen Sie früher oder später auf eine Rezension oder Erwähnung des Power Pivot Excel-Add-Ins. In meinem Fall traf ich ihn auf der Microsoft Data Day-Konferenz.

Nach der Präsentation hinterließ das Tool keine besonderen Eindrücke: Ja, es ist kostenlos (unter der Office-Lizenz), ja – es gibt einige ETL-Funktionalitäten hinsichtlich des Abrufens von Daten aus unterschiedlichen Quellen (DB, CSV, XLS usw.). Durch die Verknüpfung dieser Quellen und die Einspeisung in den RAM werden Größenordnungen von mehr als 1 Million Zeilen in Excel erfasst. Kurz gesagt, ich habe nachgeschaut und es vergessen.

Und ich musste mich daran erinnern, wann die Notwendigkeit entstand, bestimmte Phänomene in den Daten zu identifizieren

Was mich dazu veranlasst hat, diesen Artikel zu schreiben, ist, dass es im RuNet nicht viele detaillierte Informationen zu diesem Tool über bestimmte Arbeitsmethoden gibt, hauptsächlich über die Sterne, daher habe ich beschlossen, diese Rezension zu schreiben, während ich dieses Tool studiert habe.

Tatsächlich lautet die Problemstellung (anhand eines unpersönlichen Beispiels) wie folgt:

In den Quelldaten der CSV-Datei:

Es gibt Einzelhandelsgeschäfte, die bis auf Rechnungszeilen detailliert sind, während Filialen mit demselben Namen nur dann unterschiedliche Adressen haben dürfen, wenn sie sich in verschiedenen Städten befinden, aber im ursprünglichen Datenfeld gibt es Punkte, die unterschiedliche Adressen in derselben Stadt haben trotz der Tatsache, dass die Namen der Punkte gleich sind (der Name der Verkaufsstelle ist eindeutig, d. h. es handelt sich um eine Einheit des Netzwerks oder einen separaten Punkt). Als Sonderfall in aggregierter Form:

Die Suche und Bereinigung von Daten mit Standard-Office-Tools wird durch folgende Umstände behindert:

Detaillierte Daten bis hin zu Rechnungszeilen
Anzahl der Datensätze in mehreren Millionen Zeilen
Mangel an SQL-Tools (Beispiel: Access – nicht im Lieferumfang enthalten)

Natürlich kann man jedes kostenlose DBMS (sei es eine Desktop-Version oder eine Server-Version) hochladen, dafür braucht man aber erstens Admin-Rechte und zweitens würde es in dem Artikel nicht mehr um Power Pivot gehen.

Aufgabe: Für jeden atomaren Datensatz ist ein zusätzliches berechnetes Feld erforderlich, das für jeden Namen der Filiale eine eindeutige Anzahl von Adressen innerhalb derselben Stadt berechnet. Dieses Feld ist erforderlich, um schnell alle Namen von Einzelhandelsgeschäften in einer Stadt zu finden, in der es mehr als eine Adresse gibt.

Ich denke, es ist am bequemsten, iterativ zu lösen und zu sagen, unter der Annahme, dass unser Wissen über DAX auf einem rudimentären Niveau ist.
Deshalb schlage ich vor, mich vorerst von der Aufgabe zu lösen und einige grundlegende Aspekte zu betrachten.

Schritt 1: Wie unterscheidet sich eine berechnete Spalte von einer berechneten Kennzahl?
Hier ist ein Beispiel einer berechneten Spalte zum Extrahieren der Mehrwertsteuer aus einem Mehrwertsteuer-Sendungsfeld mithilfe integrierter DAX-Formeln:

RUND([Versand mit MwSt.]*POWER(1.18,-1)*0.18,2)

Wie Sie dem Beispiel entnehmen können, funktioniert die berechnete Spalte (nennen wir sie Mehrwertsteuer) horizontal mit jedem atomaren Datensatz.
Fügen wir nun ein berechnetes Feld für den Stückpreis ohne Mehrwertsteuer hinzu:

RUND([Versand mit MwSt.]*POWER(1,18,-1)/[Versand Stück];2)

Fügen wir nun zum Vergleich noch die Berechnung des durchschnittlichen Stückpreises zu dem Maß hinzu:

Durchschnittspreis pro Stück ohne Mehrwertsteuer: =ROUND(AVERAGE([Field_Price pro Stück ohne Mehrwertsteuer]);2)

Wie aus der Formel hervorgeht, funktioniert die Kennzahl vertikal mit einer Spalte mit Quelldaten, daher sollte sie immer eine Art Funktion enthalten, die mit einer Menge funktioniert (Summe, Durchschnitt, Varianz usw.).

Bei der Rückkehr zur Excel-PivotTable sieht es so aus:

Bitte beachten Sie, dass, wenn das berechnete Mehrwertsteuerfeld auf jeder Datenebene (grüner Strich auf der Ebene einer Einzelhandelsfiliale, einer Stadt oder einer Tabellensumme) den Betrag anzeigt, der im Prinzip korrekt ist, dann die Summe der Preise des berechneten Felds „ „Preis pro Stück ohne Mehrwertsteuer“ (roter Strich) wirft Fragen auf.
Aber das berechnete Maß „Durchschnittspreis pro Stück ohne Mehrwertsteuer“ hat im Rahmen dieses Analysewürfels eine Lebensberechtigung.

Daraus schließen wir, dass das berechnete Feld „Preis pro Stück ohne Mehrwertsteuer“ ein Hilfsmittel zur Berechnung des Maßes „Durchschnittspreis pro Stück ohne Mehrwertsteuer“ ist und um den Benutzer nicht mit diesem Feld zu verwirren, werden wir es aus der Liste ausblenden der Kundentools, wobei das durchschnittliche Preismaß übrig bleibt.

Ein weiterer Unterschied zwischen einer Kennzahl und einer Spalte besteht darin, dass Sie damit eine Visualisierung hinzufügen können:

Lassen Sie uns beispielsweise einen KPI für den Grad der Preisstreuung mit einer Zielgrenze von 35 % erstellen, indem wir die Wurzel der Streuung durch das arithmetische Mittel dividieren.

K_var:=STDEV.P([Field_Price pro Stück ohne MwSt.])/AVERAGE([Field_Price pro Stück ohne MwSt.])

Als Ergebnis sehen wir in Excel folgende Tabelle (das berechnete Hilfspreisfeld befindet sich übrigens nicht mehr in der Liste der verfügbaren Felder rechts):

Ein Doppelklick auf die 80 %-Quote zeigt, dass die Preise tatsächlich um den Durchschnitt schwanken:

Stärker als mit einem Koeffizienten von 15 %:

In diesem Schritt haben wir uns die Hauptunterschiede zwischen Kennzahlen und Feldern in PowerPivot angesehen.

Schritt 2. Machen wir es komplizierter: Berechnen wir den Anteil jedes Eintrags am Gesamtumsatz.
Hier ist das erste Beispiel, das die Fensterfunktionsansätze von MS SQL Server und DAX vergleicht:

Es ist klar, dass dies im Rahmen von Pivot-Tabellen buchstäblich mit zwei Mausklicks erledigt wird, ohne die Tastatur zu berühren, aber um es zu verstehen, versuchen wir es direkt in PowerPivot mithilfe von Formeln.

In SQL würde ich es so schreiben (machen Sie mir nicht die Schuld für Fehler, denn Word prüft die SQL Server-Syntax nicht):

Beginnen Sie mit der Auswahl von „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs“, „ t1.Shipment with VAT“, „t1.Shipment, pcs“/sum(“t1.Shipment, pcs“) über () als Anteil aus Tabelle als t1 order by „t1.Shipment, pcs“/sum(“t1.Shipment , Stück") desc

Wie Sie sehen, öffnet sich hier ein Fenster mit allen Datensätzen im Datensatz. Versuchen wir etwas Ähnliches in PowerPivot:

=[Sendungsstück]/BERECHNEN(SUM([Sendungsstück]);ALL("Tabelle1"))

Konzentrieren wir uns auf den Nenner: Ich habe oben bereits erwähnt, dass der Hauptunterschied zwischen einem berechneten Feld und einer Kennzahl darin besteht, dass sie in einem Formelfeld horizontal zählen (innerhalb eines Datensatzes) und Kennzahlen vertikal zählen (innerhalb eines Attributs). Hier konnten wir die Feldeigenschaften und die Kennzahleneigenschaft durch die CALCULATE-Methode kreuzen. Und wenn wir die Breite des Fensters in SQL mit Over() angepasst haben, dann haben wir das hier mit All() gemacht.

Versuchen wir nun, mit dieser Fähigkeit etwas Nützliches aus unseren Daten zu machen. Denken wir beispielsweise daran, dass der Indikator der Preisstreuung um den Durchschnitt über einen weiten Bereich variiert. Versuchen wir, statistische Ausreißer bei den Preisen mithilfe der 3-Sigma-Regel zu identifizieren.

Fensterfunktionen in SQL sehen folgendermaßen aus:

Wählen Sie „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs“, „t1 .Versand mit Mehrwertsteuer“, „t1.Preis pro Stück ohne Mehrwertsteuer“, CASE WHEN ABS(“t1.Preis pro Stück ohne Mehrwertsteuer“ – AVG(“t1.Preis pro Stück ohne Mehrwertsteuer“) OVER()) > 3 * STDEV ("t1.Preis pro Stück ohne Mehrwertsteuer") OVER() THEN 1 ELSE 0 END as Outlier from Table as t1 Go

Und hier ist das Gleiche im DAX:

If(ABS([Field_Price pro Stück ohne MwSt.]-CALCULATE(AVERAGE([Field_Price pro Stück ohne MwSt.]);ALL("Table1")))>(3*CALCULATE(STDEV.P([Field_Price pro Stück ohne MwSt.] );all("Tabelle1")));1;0)

Wie Sie sehen, ist der Preis mit einem arithmetischen Durchschnitt von 40,03 Rubel etwas hoch.

Schritt 3. Verengen Sie die Fenster.
Versuchen wir nun, im berechneten Feld jedes Datensatzes die Gesamtzahl der Datensätze innerhalb der Stadt zu zählen, zu der dieser Datensatz gehört.
Auf MS SQL Server sehen Fensterfunktionen folgendermaßen aus:

Wählen Sie „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs“, „t1 .Versand mit Mehrwertsteuer", "t1.Preis pro Stück ohne Mehrwertsteuer", count("t1.*) OVER(partition by "t1.City") as cnt from Table as t1 Go

Im DAX:
=BERECHNEN(COUNTROWS("Tabelle1");ALLEXCEPT("Tabelle1","Tabelle1"[Stadt]))

Bitte beachten Sie den Unterschied in der Anzeige der Daten in der Tabelle. Ich habe speziell Adressen in den Maßnahmenbereich eingefügt, um deren Anzahl zu zählen und sie mit dem neuen Feld zu vergleichen, das ich in der Zeilenüberschrift nach dem Namen der Filiale platziert habe.

Der Unterschied ist deutlich sichtbar: Wenn die übliche Berechnung der Anzahl der Adressen für jeden Punkt in der Stadt durchgeführt wird und dann nur eine Zwischensumme für das Aggregat „Stadt“ angezeigt wird, dann ermöglicht die Verwendung von Fensterfunktionen die Zuordnung jedes Atoms Zeichnen Sie den Wert eines beliebigen Aggregats auf oder verwenden Sie ihn in einigen Zwischenberechnungen des berechneten Felds (wie oben gezeigt).

Zurück zum ursprünglichen Problem
Lassen Sie mich Sie an die erste Problemstellung erinnern: Für jeden atomaren Datensatz ist ein zusätzliches berechnetes Feld erforderlich, das für jeden Namen der Filiale eine eindeutige Anzahl von Adressen innerhalb derselben Stadt berechnet. Vergessen Sie nicht, dass unser Datensatz bis in die Rechnungszeilen detailliert ist. Bevor Sie also die Adressen im Fenster berechnen, müssen diese gruppiert werden.

Anfrage an SQL Server:

Mit a1 als (Wählen Sie „t1.TT Name“, „t1.City“, „t1.Address“, „t1.Product“, „t1.TTN No“, „t1.TTN Date“, „t1.Shipment, pcs ", "t1.Shipment with VAT", "t1.Price per Piece without VAT", count(Distinct "t1.Address") OVER(partition by "t1.City", "t1.TT Name") als Adrcnt aus Tabelle als t1) Wählen Sie * aus a1, wobei adrcnt>1 ist

Nun hindert uns nichts mehr daran, dies im DAX zu tun:

CALCULATE(DISTINCTCOUNT("Table1"[Address]);ALLEXCEPT("Table1","Table1"[City],"Table1"[TT Name]))

Dadurch konnten wir verdächtige Datensätze auswählen, bei denen es mehr als eine Adresse am selben Punkt in derselben Stadt gab.

Natürlich wird beim Studium (bei einem Blick auf andere Formeln) klar, dass DAX in PowerPivot viel leistungsfähiger ist als in diesem Thema gezeigt, aber es ist definitiv nicht möglich, die Unermesslichkeit auf einmal zu erfassen.

Ich hoffe, es war interessant.
Fortsetzung des Artikels

Power Pivot ist ein Excel-Add-In, mit dem Sie detaillierte Analysen durchführen und komplexe Datenmodelle erstellen können. Kombinieren Sie mit Power Pivot große Datenmengen aus mehreren Quellen, analysieren Sie Informationen schnell und teilen Sie Daten einfach.

Sowohl in Excel als auch in Power Pivot können Sie ein Datenmodell erstellen, eine Sammlung von Tabellen mit Beziehungen. Das in der Arbeitsmappe in Excel angezeigte Datenmodell ist dasselbe Datenmodell, das im Power Pivot-Fenster angezeigt wird. Alle Daten, die in Excel importiert werden können, sind in Power Pivot verfügbar und umgekehrt.

Hinweis: Bevor Sie die Informationen beherrschen,

Hauptfunktionen von PowerPivot für Excel

Importieren Sie Millionen von Datenzeilen aus mehreren Datenquellen Mit Power Pivot für Excel können Sie Millionen von Datenzeilen aus mehreren Quellen in eine einzige Excel-Arbeitsmappe importieren, Beziehungen zwischen unterschiedlichen Daten erstellen, berechnete Spalten und Kennzahlen mithilfe von Formeln erstellen, Pivot-Tabellen und Pivot-Diagramme erstellen und dann einen Drilldown in Ihre durchführen Daten, um zeitnahe Geschäftsentscheidungen zu treffen – ohne die Hilfe von IT-Spezialisten.

Schalten Sie schnelle Berechnungen und Analysen frei Verarbeiten Sie Millionen von Zeilen gleichzeitig mit Tausenden und nutzen Sie mehrere Prozessoren und GB RAM für eine schnelle Berechnungsverarbeitung. Behebt bestehende Einschränkungen bei der Analyse großer Datenmengen auf dem Computer mit effizienten Komprimierungsalgorithmen, um selbst große Datensätze in den Speicher zu laden.

Unbegrenzte Datenquellenunterstützung Bietet ein Framework zum Importieren und Kombinieren von Quelldaten von überall, um Big Data auf Ihrem Desktop zu analysieren, einschließlich relationaler Datenbanken, mehrdimensionaler Quellen, Cloud-Dienste, Datenfeeds, Excel-Dateien, Textdateien und Daten aus dem Internet.

Sicherheit und Management Mit Power Pivot Control Panel können IT-Administratoren ihre allgemeinen Anwendungen überwachen und verwalten, um Sicherheit, Geschäftskontinuität und Produktivität zu gewährleisten.

Datenanalyseausdrücke (DAX) DAX ist eine Formelsprache, die die Datenverarbeitungsfunktionen von Excel um immer komplexere Gruppierungen, Berechnungen und Analysen erweitert. Die Syntax von DAX-Formeln ist der von Excel-Formeln sehr ähnlich.

Aufgaben in Power Pivot oder Excel

Der Hauptunterschied zwischen Power Pivot und Excel besteht darin, dass Sie im Power Pivot-Fenster komplexere Datenmodelle erstellen können. Hier sind einige Probleme zum Vergleich.

office.microsoft

In weniger als einer Stunde lernen Sie, wie man etwas kreiert Excel PivotTable-Berichte, die Daten aus mehreren Tabellen kombinieren. Der erste Teil dieses Tutorials hilft Ihnen beim Importieren und Erkunden von Daten. In Teil 2 erfahren Sie, wie Sie das Datenmodell hinter Ihrem Bericht verfeinern, neue Berechnungen und Hierarchien zu Power View-Berichten hinzufügen und diese mithilfe des Power Pivot-Add-Ins optimieren.

Beginnen wir mit dem Importieren von Daten.

1. Laden Sie Beispieldaten (ContosoV2) für dieses Tutorial herunter. Extrahieren Sie die Datendateien und speichern Sie sie an einem geeigneten Ort, z. B. im Ordner „Downloads“ oder „Eigene Dateien“.
2. Öffnen Sie eine leere Arbeitsmappe in Excel.
3. Wählen Daten > Empfang externer Daten > Von Access.
4. Navigieren Sie zu dem Ordner, der die Beispieldatendateien enthält, und wählen Sie ContosoSales aus.
5. Klicken Offen. Da Sie eine Verbindung zu einer Datenbankdatei herstellen, die mehrere Tabellen enthält, wird ein Dialogfeld angezeigt Tischauswahl, wo Sie die Tabellen auswählen können, die Sie importieren möchten.

1. Aktivieren Sie im Dialogfeld „Tabelle auswählen“ das Kontrollkästchen Mehrere Tabellenauswahl zulassen.
2. Wählen Sie alle Tabellen aus und klicken Sie auf die Schaltfläche OK.
3. Wählen Sie auf der Registerkarte Daten importieren die Option aus PivotTable-Bericht und drücken Sie die Taste OK.

NOTIZ. Glückwunsch! Sie haben gerade ein Datenmodell erstellt. Ein Modell ist eine Datenintegrationsschicht, die automatisch erstellt wird, wenn Sie mehrere Tabellen gleichzeitig in einem einzigen PivotTable-Bericht importieren oder damit arbeiten.

Das Modell ist in Excel praktisch unsichtbar, Sie können es jedoch direkt mit dem Power Pivot-Add-In anzeigen und bearbeiten. In Excel erkennt man das Vorhandensein eines Datenmodells daran, dass in der Liste der Felder einer Pivot-Tabelle eine Reihe von Tabellen angezeigt wird. Es gibt verschiedene Möglichkeiten, ein Modell zu erstellen.

Daten in einer PivotTable anzeigen

Um die Daten übersichtlich anzuzeigen, können Sie Felder in Bereiche ziehen Werte, Spalten Und Saiten in der Liste der Felder in der Pivot-Tabelle.

1. Scrollen Sie durch die Liste der Felder und suchen Sie die FactSales-Tabelle.
2. Wählen Sie die Spalte „SalesAmount“ aus. Da es numerische Daten enthält, platziert Excel SalesAmount automatisch im Wertebereich.
3. Ziehen Sie aus der DimDate-Tabelle die Spalte CalendarYear in den Bereich „Spalten“.
4. Ziehen Sie aus der Tabelle „DimProductSubcategory“ die Spalte „ProductSubcategoryName“ in den Bereich „Zeilen“.
5. Ziehen Sie aus der DimProduct-Tabelle die Spalte „BrandName“ in den Zeilenbereich und platzieren Sie sie unter der Unterkategorie.

Die PivotTable sollte wie folgt aussehen.

Mit wenig Aufwand haben Sie eine Pivot-Tabelle erstellt, die Felder aus vier verschiedenen Tabellen enthält. Diese Aufgabe wurde dank der vorab erstellten Beziehungen zwischen den Tabellen so einfach. Da die Beziehungen zwischen den Tabellen in der Datenquelle vorhanden waren und Sie alle Tabellen auf einmal importierten, konnte Excel diese Beziehungen im Modell wiederherstellen.

Was aber, wenn die Daten aus unterschiedlichen Quellen stammen oder nicht gleichzeitig importiert werden? Im Allgemeinen können Sie neue Daten hinzufügen, indem Sie Beziehungen basierend auf übereinstimmenden Spalten erstellen. Im nächsten Schritt importieren Sie zusätzliche Tabellen und erfahren mehr über die Anforderungen für neue Beziehungen und die Schritte zu deren Erstellung.

Hinzufügen zusätzlicher Tabellen

Um zu lernen, wie man Verbindungen herstellt, benötigen Sie zusätzliche, unabhängige Tabellen. An diesem Punkt erhalten Sie die restlichen in diesem Tutorial verwendeten Daten, indem Sie eine zusätzliche Datenbank importieren und Daten aus den anderen beiden Arbeitsmappen einfügen.

Produktkategorien hinzufügen

1. Öffnen Sie ein neues Blatt im Buch. Es werden zusätzliche Daten gespeichert.
2. Wählen Daten > Empfang externer Daten > Von Access.
3. Navigieren Sie zu dem Ordner mit den Beispieldatendateien und wählen Sie ProductCategories aus. Klicken Sie auf die Schaltfläche Offen.
4. Wählen Sie auf der Registerkarte Daten importieren die Option aus Tisch und drücken Sie die Taste OK.

Geografische Daten hinzufügen

1. Fügen Sie ein weiteres Blatt hinzu.
2. Öffnen Sie in den Beispieldatendateien Geography.xlsx, platzieren Sie den Cursor im Feld A1 und drücken Sie dann STRG+UMSCHALT+ENDE, um alle Daten auszuwählen.
3. Kopieren Sie die Daten in die Zwischenablage.
4. Fügen Sie die Daten in das leere Arbeitsblatt ein, das Sie gerade hinzugefügt haben.
5. Wählen Als Tabelle formatieren mit jedem Stil. Wenn Sie Ihre Daten als Tabelle formatieren, können Sie ihnen einen Namen geben, was beim Definieren von Beziehungen im nächsten Schritt sehr nützlich ist.
6. Stellen Sie sicher, dass das Kontrollkästchen „Tabelle formatieren“ aktiviert ist Tabelle mit Überschriften. Klicken Sie auf die Schaltfläche OK.
7. Benennen Sie die Tabelle Geographie. Wählen Sie eine Registerkarte aus Arbeiten mit Tabellen > Konstrukteur und geben Sie im Feld „Tabellenname“ den Namen „Geografie“ ein.
8. Schließen Sie die Datei Geography.xlsx, um sie aus dem Arbeitsbereich zu entfernen.

Filialdaten hinzufügen

• Wiederholen Sie die vorherigen Schritte für die Datei Stores.xlsx – fügen Sie deren Inhalt in ein leeres Blatt ein. Benennen Sie die Tabelle Stores.

Jetzt sind es vier Blätter. Blatt1 enthält eine Pivot-Tabelle, Blatt2 enthält Produktkategorien, Sheet3 enthält Geografie und Sheet4 enthält Geschäfte. Da Sie bereits alle Tabellen benannt haben, wird der nächste Schritt – das Erstellen von Beziehungen – viel einfacher sein.

Verwendung von Feldern aus neu importierten Tabellen

Sie können jetzt die Felder aus den Tabellen verwenden, die Sie gerade importiert haben. Wenn Excel nicht herausfinden kann, wie ein Feld zu einem PivotTable-Bericht hinzugefügt wird, werden Sie aufgefordert, einen Tabellenlink zu erstellen, der die neue Tabelle mit einer Tabelle abgleicht, die bereits Teil des Modells ist.

1. Wählen Sie einen Artikel aus Alle oben in der PivotTable-Feldliste, um die vollständige Liste der verfügbaren Tabellen anzuzeigen.
2. Scrollen Sie in der Liste nach unten. Es gibt neue Tabellen, die Sie gerade hinzugefügt haben.
3. Erweitern Sie Stores.
4. Ziehen Sie die Spalte „StoreName“ in den Bereich „Filter“.
5. Beachten Sie, dass Excel Sie auffordert, die Beziehung zu erstellen. Diese Benachrichtigung wird angezeigt, weil Sie Felder in einer Tabelle verwendet haben, die keinem Modell zugeordnet ist.
6. Klicken Erstellen, um das Dialogfeld „Link erstellen“ zu öffnen.
7. Wählen Sie im Tabellenbereich FactSales aus. Die Tabelle „FactSales“ aus den Beispieldaten enthält detaillierte Verkaufs- und Kosteninformationen für Contoso sowie Schlüssel aus anderen Tabellen, einschließlich der Geschäftscodes in der im vorherigen Schritt importierten Datei „Stores.xlsx“.
8. Wählen Sie im Bereich „Spalte (Extern)“ StoreKey aus.
9. Wählen Sie im Bereich „Verwandte Tabelle“ die Option „Stores“ aus.
10. Wählen Sie im Bereich „Zugehörige Spalte (primär)“ StoreKey aus.
11. Klicken Sie auf die Schaltfläche OK.

Verknüpfen von ProductSubcategory mit ProductCategory

1. Wählen Sie in Excel aus Daten > Beziehung > Erstellen.
2. Wählen Sie im Tabellenbereich DimProductSubcategory aus.
3. Wählen Sie im Bereich „Spalte (Extern)“ die Option „ProductCategoryKey“ aus.
4. Wählen Sie im Bereich „Zugehörige Tabelle“ die Option „Table_ProductCategory.accdb“ aus.
5. Wählen Sie im Bereich „Zugehörige Spalte (primär)“ die Option „ProductCategoryKey“ aus.
6. Klicken Sie auf die Schaltfläche OK.
7. Schließen Sie das Dialogfeld Beziehungsmanagement.

Hinzufügen von Kategorien zu einer PivotTable

Obwohl dem Datenmodell zusätzliche Tabellen und Beziehungen hinzugefügt wurden, werden diese in der PivotTable noch nicht verwendet. Diese Aufgabe fügt ProductCategory zur Liste der Felder in der Pivot-Tabelle hinzu.

1. Alle um die im Datenmodell vorhandenen Tabellen anzuzeigen.
2. Scrollen Sie in der Liste nach unten.
3. Löschen Sie im Bereich „Zeilen“ BrandName.
4. Erweitern Sie Table_DimProductCategories.accdb.
5. Ziehen Sie ProductCategoryName in den Zeilenbereich und platzieren Sie es über ProductSubcategory.
6. Wählen Sie im Bereich PivotTable-Felder die Option aus Aktiv sodass die Tabellen, die Sie gerade verwendet haben, in der PivotTable verwendet werden.

Checkpoint: Überprüfen Sie, was Sie gelernt haben

Sie haben eine Pivot-Tabelle erstellt, die Daten aus mehreren Tabellen enthält, die Sie im vorherigen Schritt importiert haben. Um die Daten zusammenzuführen, mussten Tabellenverknüpfungen erstellt werden, die Excel zum Abgleichen von Zeilen verwendet. Sie haben gelernt, dass übereinstimmende Spalten erforderlich sind, um verwandte Zeilen zu finden. In den Beispieldaten enthalten alle Tabellen eine Spalte, die für diesen Zweck verwendet werden kann.

Obwohl die PivotTable funktioniert, sind Ihnen möglicherweise einige verbleibende Probleme aufgefallen. Die PivotTable-Feldliste scheint zusätzliche Tabellen (DimEntity) und Spalten (ETLLoadID) zu enthalten, die in keiner Weise mit dem Unternehmen Contoso in Zusammenhang stehen. Darüber hinaus sind die Daten aus der Tabelle „Geographie“ immer noch nicht integriert.

Als Nächstes: Zeigen Sie Ihr Modell mit Power Pivot an und erweitern Sie es

In der nächsten Aufgabenreihe erweitern Sie Ihr Modell mithilfe des Microsoft Office Power Pivot-Add-Ins in Microsoft Excel 2013. Sie erfahren, dass Beziehungen am einfachsten mit der in diesem Add-In bereitgestellten Diagrammansicht erstellt werden können. Sie verwenden das Add-In auch zum Erstellen von Berechnungen und Hierarchien, zum Ausblenden von Elementen, die nicht in der Feldliste angezeigt werden sollen, und zum Optimieren von Daten für zusätzliche Berichte.

NOTIZ. Das Power Pivot-Add-In in Microsoft Excel 2013 ist in Office ProPlus verfügbar.

Hinzufügen Power-Pivot auf Band Excel durch Aktivieren des Power Pivot-Add-Ins.

1. Gehen Sie zur Registerkarte Datei > Optionen > Add-ons.
2. Auf dem Feld Kontrolle wählen COM-Add-Ins> Gehen.
3. Aktivieren Sie das Kontrollkästchen Microsoft Office Power Pivot in Microsoft Excel 2013 und klicken Sie dann auf die Schaltfläche OK.

Die Registerkarte „Power Pivot“ wird im Menüband angezeigt.

Fügen Sie eine Beziehung mithilfe einer Diagrammansicht in Power Pivot hinzu

1. Wählen Sie in Excel Sheet3 aus, um es zu aktivieren. Sheet3 enthält die Geografietabelle, die zuvor importiert wurde.
2. Wählen Sie im Menüband aus Power-Pivot > Zum Datenmodell hinzufügen. Zu diesem Zeitpunkt wird die Geografietabelle zum Modell hinzugefügt. Außerdem wird das Power Pivot-Add-In geöffnet, mit dem Sie die restlichen Schritte der Aufgabe abschließen können.
3. Beachten Sie, dass im Power Pivot-Fenster alle Tabellen im Modell angezeigt werden, einschließlich der Geografietabelle. Schauen Sie sich einige Tabellen an. In diesem Add-In können Sie alle im Modell enthaltenen Daten anzeigen.
4. Wählen Sie im Power Pivot-Fenster unter „Ansicht“ die Option aus Diagrammansicht.
5. Verwenden Sie die Bildlaufleiste, um die Größe des Diagramms zu ändern, sodass Sie alle Objekte im Diagramm sehen können. Beachten Sie, dass zwei Tabellen nicht mit dem Rest des Diagramms verbunden sind: DimEntity und Geography.
6. Klicken Sie mit der rechten Maustaste auf DimEntity und dann auf Löschen. Diese Tabelle ist Teil der Quelldatenbank und wird im Modell nicht benötigt.
7. Passen Sie den Maßstab der Geografietabelle an, sodass alle Felder sichtbar sind. Mit dem Schieberegler können Sie das Tabellendiagramm vergrößern.
8. Beachten Sie, dass die Geography-Tabelle eine GeographyKey-Spalte enthält. Diese Spalte enthält Werte, die eindeutige Kennungen für jede Zeile in der Geografietabelle sind. Lassen Sie uns feststellen, ob andere Tabellen in diesem Modell denselben Schlüssel verwenden. Wenn ja, können wir eine Beziehung erstellen, die die Tabelle mit dem Rest des Modells verbindet.
9. Wählen Finden.
10. Geben Sie im Feld „Metadatensuche“ GeographyKey ein.
11. Drücken Sie die Taste mehrmals Weiter finden. Der GeographyKey-Wert befindet sich in den Tabellen „Geography“ und „Stores“.
12. Ziehen Sie die Tabelle „Geografie“ in die Tabelle „Stores“.
13. Ziehen Sie die Spalte „GeographyKey“ in der Tabelle „Stores“ auf die Spalte „GeographyKey“ in der Tabelle „Geography“. Power Pivot zeichnet eine Linie zwischen den beiden Spalten, um die Beziehung anzuzeigen.

In dieser Aufgabe haben Sie eine neue Möglichkeit kennengelernt, eine Tabelle hinzuzufügen und Beziehungen zu erstellen. Sie verfügen nun über ein vollständig integriertes Modell, in dem alle Tabellen verbunden und für die PivotTable auf Blatt1 zugänglich sind.

BERATUNG. In der Schemaansicht werden einige Tabellenschemata vollständig erweitert, um die Spalten ETLLoadID, LoadDate und UpdateDate anzuzeigen. Diese spezifischen Felder sind Teil des ursprünglichen Contoso-Datenspeichers und werden hinzugefügt, um Datenextraktions- und Ladevorgänge zu unterstützen. Sie werden in Ihrem Modell nicht benötigt. Um sie zu entfernen, markieren Sie die Felder, klicken Sie mit der rechten Maustaste darauf und klicken Sie dann auf Löschen .

Erstellen Sie eine berechnete Spalte

Im Power Pivot-Add-In können Sie DAX-Formeln verwenden, um Berechnungen hinzuzufügen. In dieser Aufgabe berechnen Sie das Endergebnis und fügen eine berechnete Spalte hinzu, die auf Datenwerte aus anderen Tabellen verweist. Später erfahren Sie, wie Sie Referenzspalten verwenden, um Ihr Modell zu vereinfachen.

1. Kehren Sie im Power Pivot-Fenster zur Datenansicht zurück.
2. Geben Sie der Accdb-Tabelle „Table_ProductCategories“ einen aussagekräftigeren Namen. Sie werden in späteren Schritten auf diese Tabelle zurückgreifen. Ein kürzerer Name erleichtert die Lesbarkeit der Berechnungen. Klicken Sie mit der rechten Maustaste auf den Tabellennamen und klicken Sie dann auf Umbenennen, geben Sie den Namen ProductCategories ein und drücken Sie dann die Eingabetaste.
3. Wählen Sie die FactSales-Tabelle aus.
4. Wählen Konstrukteur > Spalten > Hinzufügen.
5. Geben Sie in der Formelleiste über der Tabelle die folgende Formel ein. Die AutoVervollständigen-Funktion hilft Ihnen bei der Eingabe vollständiger Spalten- und Tabellennamen und zeigt Ihnen die verfügbaren Funktionen an. Sie können auch einfach auf eine Spalte klicken und Power Pivot fügt ihren Namen zur Formel hinzu.

= - -

Alle Zeilen der berechneten Spalte werden mit Werten gefüllt. Wenn Sie in der Tabelle nach unten scrollen, werden Sie feststellen, dass die Zeilen basierend auf den in jeder Zeile enthaltenen Daten unterschiedliche Werte für eine bestimmte Spalte enthalten können.

1. Benennen Sie die Spalte um, indem Sie mit der rechten Maustaste auf CalculatedColumn1 klicken und auswählen Spalte umbenennen. Geben Sie Profit ein und drücken Sie die EINGABETASTE.
2. Wählen Sie nun die DimProduct-Tabelle aus.
3. Wählen Konstrukteur > Spalten > Hinzufügen.
4. Geben Sie in der Formelleiste über der Tabelle die folgende Formel ein.

VERWANDTE(Produktkategorien)

Die RELATED-Funktion gibt einen Wert aus einer zugehörigen Tabelle zurück. In unserem Fall enthält die ProductCategories-Tabelle die Namen der Produktkategorien, die Sie der DimProduct-Tabelle hinzufügen müssen, wenn Sie die Hierarchie erstellen, die die Kategorieinformationen enthält.

1. Wenn Sie mit der Eingabe der Formel fertig sind, drücken Sie ENTER, um sie zu bestätigen.

Alle Zeilen der berechneten Spalte werden mit Werten gefüllt. Wenn Sie in der Tabelle nach unten scrollen, werden Sie feststellen, dass jede Zeile einen Produktkategorienamen enthält.

1. Spalte umbenennen. Geben Sie ProductCategory ein und drücken Sie die Eingabetaste.
2. Wählen Konstrukteur > Spalten > Hinzufügen.
3. Geben Sie in der Formelleiste über der Tabelle die folgende Formel ein und drücken Sie dann die Eingabetaste, um sie zu bestätigen.

RELATED(DimProductSubcategory)

1. Benennen Sie die Spalte um, indem Sie mit der rechten Maustaste auf CalculatedColumn1 klicken und auswählen Spalte umbenennen. Geben Sie ProductSubcategory ein und drücken Sie die EINGABETASTE.

Erstellen einer Hierarchie

Die meisten Modelle enthalten Daten, die hierarchischer Natur sind. Häufige Beispiele sind Kalenderdaten, geografische Daten und Produktkategorien. Das Erstellen von Hierarchien ist nützlich, da Sie damit ein Element (Hierarchie) in den Bericht ziehen können, anstatt jedes Mal dieselben Felder zu sammeln und anzuordnen.

1. Gehen Sie in Power Pivot zur Diagrammansicht. Erweitern Sie die DimDate-Tabelle, um die Arbeit mit ihren Feldern zu erleichtern.
2. Halten Sie die STRG-Taste gedrückt und klicken Sie auf die Spalten CalendarYear, CalendarQuarter und CalendarMonth (Sie müssen in der Tabelle nach unten scrollen).
3. Wenn drei Spalten ausgewählt sind, klicken Sie mit der rechten Maustaste auf eine davon und klicken Sie Erstellen Sie eine Hierarchie. Am Ende der Tabelle wird ein übergeordneter Hierarchieknoten, Hierarchie 1, erstellt und die ausgewählten Spalten werden als untergeordnete Knoten in die Hierarchie kopiert.
4. Geben Sie Datumsangaben als Namen der neuen Hierarchie ein.
5. Fügen Sie der Hierarchie eine FullDateLabel-Spalte hinzu. Klicken Sie mit der rechten Maustaste auf FullDateLabel und wählen Sie aus Zur Hierarchie hinzufügen. Wählen Sie den Datumstyp aus. Die Spalte „FullDateLabel“ enthält das Datum im vollständigen Format, einschließlich Jahr, Monat und Tag. Stellen Sie sicher, dass die Spalte „FullDateLabel“ ganz unten in der Hierarchie angezeigt wird. Sie verfügen nun über eine mehrstufige Hierarchie, die Jahr, Quartal, Monat und einzelne Kalendertage umfasst.
6. Wählen Sie, während Sie sich noch in der Diagrammansicht befinden, die DimProduct-Tabelle aus und klicken Sie Erstellen Sie eine Hierarchie im Tabellenkopf. Am Ende der Tabelle wird ein leerer übergeordneter Hierarchieknoten angezeigt.
7. Geben Sie Produktkategorien als Namen der neuen Hierarchie ein.
8. Um untergeordnete Knoten einer Hierarchie zu erstellen, ziehen Sie die Spalten ProductCategory und ProductSubcategory in die Hierarchie.
9. Klicken Sie mit der rechten Maustaste auf Produktname und wählen Sie aus Zur Hierarchie hinzufügen. Wählen Sie Produktkategorien aus.

Nachdem Sie nun verschiedene Möglichkeiten kennen, eine Hierarchie zu erstellen, wollen wir sie in einer Pivot-Tabelle verwenden.

1. Geh zurück zu Excel.
2. Löschen Sie auf Blatt1 (dem Blatt, das die Pivot-Tabelle enthält) die Felder im Bereich „Zeilen“.
3. Ersetzen Sie sie durch die neue Produktkategorienhierarchie in DimProduct.
4. Ersetzen Sie auf ähnliche Weise CalendarYear im Bereich „Spalten“ durch die Datumshierarchie von DimDate.

Die Vorteile von Hierarchien sind jetzt beim Betrachten Ihrer Daten leicht zu erkennen. Sie können verschiedene Bereiche der PivotTable unabhängig voneinander erweitern und schließen und haben so mehr Kontrolle darüber, wie der verfügbare Platz genutzt wird. Darüber hinaus ermöglicht das Hinzufügen einer einzigen Hierarchie für die Bereiche „Zeilen“ und „Spalten“ eine sofortige Granularität, ohne dass mehrere Felder gestapelt werden müssen, um den gleichen Effekt zu erzielen.

Spalten ausblenden

Sobald die Produktkategorienhierarchie erstellt und in DimProduct platziert wurde, werden DimProductCategory oder DimProductSubcategory in der PivotTable-Feldliste nicht mehr benötigt. In dieser Aufgabe erfahren Sie, wie Sie überflüssige Tabellen und Spalten ausblenden, die Platz in der PivotTable-Feldliste beanspruchen. Durch das Ausblenden von Tabellen und Spalten können Sie Ihre Berichte optimieren, ohne das Modell zu beeinträchtigen, das Beziehungen und Berechnungen für die Daten bereitstellt.

Sie können einzelne Spalten, deren Bereich oder die gesamte Tabelle ausblenden. Spalten- und Tabellennamen werden inaktiv und spiegeln ihren verborgenen Status für Client-Renderer wider, die das Modell verwenden. Ausgeblendete Spalten werden im Modell ausgegraut angezeigt, bleiben aber in der Datenansicht sichtbar, sodass Sie mit ihnen arbeiten können.

1. Stellen Sie sicher, dass in Power Pivot die Datenansicht ausgewählt ist.
2. Klicken Sie auf den Registerkarten am unteren Bildschirmrand mit der rechten Maustaste auf DimProductSubcategory und wählen Sie aus.
3. Wiederholen Sie diesen Vorgang für Produktkategorien.
4. Öffnen Sie die DimProduct-Umgebung.
5. Klicken Sie mit der rechten Maustaste auf die folgenden Spalten und wählen Sie sie aus In Client-Tools ausblenden.

• Produktschlüssel
• Produktetikett
• Produktunterkategorie

1. Wählen Sie mehrere benachbarte Spalten aus, beginnend mit ClassID und endend mit ProductSubcategory. Klicken Sie mit der rechten Maustaste, um sie auszublenden.
2. Wiederholen Sie diesen Schritt mit anderen Tabellen und entfernen Sie IDs, Schlüssel und andere Details, die nicht in den Bericht gehören.

Jetzt verstehen Sie, dass Sie durch die Erstellung berechneter Felder Ihre Daten auf unzählige leistungsstarke Arten analysieren können. Erfahren Sie, wie Sie diese Felder erstellen.

Das Erstellen berechneter Felder in Power Pivot ist mit dem ganz einfach Autosum.

1. In der Tabelle FactSales Spalte auswählen Profitieren.
2. Wählen Berechnungen > Autosum. Beachten Sie, dass ein neues berechnetes Feld mit dem Namen erstellt wurde Summe des Gewinns in der Berechnungsbereichszelle direkt unter der Spalte Profitieren.
3. Wählen Sie in Excel in Tabelle 1 in der Liste der Felder „in Tabelle“ aus FactSales berechnetes Feld Summe des Gewinns.

Bereit! Wie Sie sehen können, haben wir mithilfe von Standardaggregatfunktionen in nur wenigen Minuten ein berechnetes Feld „Gewinnsumme“ in Power Pivot erstellt und zur PivotTable hinzugefügt. Jetzt können Sie Ihre Gewinne schnell analysieren, indem Sie verschiedene Filter anwenden. In diesem Fall sehen Sie eine Spalte „Gewinnsumme“, deren Werte nach den Hierarchien „Produktkategorie“ und „Datum“ gefiltert sind.

Was aber, wenn Sie eine tiefergehende Analyse durchführen müssen, beispielsweise die Umsatzzählung für einen bestimmten Kanal, ein bestimmtes Produkt oder eine bestimmte Kategorie? Dazu müssen Sie ein weiteres berechnetes Feld erstellen, das die Anzahl der Zeilen berechnet, eine für jeden Verkauf aus der FactSales-Tabelle, abhängig von den von Ihnen verwendeten Filtern.

1. Wählen Sie in der FactSales-Tabelle eine Spalte aus Verkaufsschlüssel.
2. In der Gegend Berechnungen Klicken Sie auf den Abwärtspfeil unter der Schaltfläche Autosum > ÜBERPRÜFEN.
3. Benennen Sie das neue berechnete Feld um, indem Sie mit der rechten Maustaste auf die Spalte klicken Anzahl von SalesKey im Bereich „Berechnen“ und wählen Sie den Befehl aus Umbenennen. Eingeben Zählen und drücken Sie ENTER.
4. Wählen Sie in Excel in Tabelle 1 in der Liste der Felder die Option aus FactSales und drücken Zählen.

Bitte beachten Sie, dass der PivotTable eine neue Spalte hinzugefügt wurde Zählen, das die Anzahl der Verkäufe abhängig von den angewendeten Filtern anzeigt. Wie bei der berechneten Spalte „Gewinnsumme“ wird auch das berechnete Feld „Anzahl“ nach den Produktkategorie- und Datumshierarchien gefiltert.

Lasst uns noch etwas üben. Dieses Mal erstellen Sie ein berechnetes Feld, das den Prozentsatz der Verkäufe basierend auf einem bestimmten Kontext oder Filter berechnet. Im Gegensatz zu früheren berechneten Feldern, die Sie mit AutoSum erstellt haben, müssen Sie die Formel dieses Mal jedoch manuell eingeben.

1. Wählen Sie in der FactSales-Tabelle im Berechnungsbereich eine leere Zelle aus. Tipp: Am praktischsten ist es, berechnete Felder beginnend mit der Zelle in der oberen linken Ecke zu platzieren. Dadurch werden sie leichter gefunden. Sie können zu jedem berechneten Feld im Berechnungsbereich navigieren.
2. Geben Sie in der Formelleiste mit IntelliSense die folgende Formel ein: Prozentsatz aller Produkte:=/CALCULATE(, ALL(DimProduct))
3. Drücken Sie ENTER, um die Formel zu bestätigen.
4. In Excel auf Sheet1 in der Liste der Felder in der Tabelle FactSales wählen Prozentsatz aller Produkte.
5. Wählen Sie in einer Pivot-Tabelle mehrere Spalten aus Prozentsatz aller Produkte.
6. Auf der Registerkarte Heim wählen Nummer > Prozentformat. Um neue Spalten zu formatieren, verwenden Sie zwei Dezimalstellen nach dem Komma.

Dieses neue berechnete Feld berechnet den Prozentsatz der Verkäufe für den angegebenen Filterkontext. In unserem Fall sind die Filter immer noch die Produktkategorie- und Datumshierarchien. Unter anderem lässt sich erkennen, dass der Anteil der Computer am Produktumsatz im Laufe der Zeit gestiegen ist.

Wenn Sie mit der Erstellung von Excel-Formeln vertraut sind, können Sie problemlos Formeln für berechnete Spalten und Felder erstellen. Unabhängig davon, ob Sie mit Excel-Formeln vertraut sind oder nicht, haben Sie eine großartige Gelegenheit, grundlegende DAX-Formeln zu erlernen, indem Sie Lektionen aus dem E-Book „Quick Start Guide: DAX Basics in 30 Minutes“ nutzen.

Jobsparen

Speichern Sie das Buch, damit Sie es zusammen mit anderen Lehrbüchern oder für zukünftige Studien verwenden können.

Nächste Schritte

Obwohl Sie Daten problemlos aus Excel importieren können, ist der Import mit dem Power Pivot-Add-In oft schneller und effizienter. Sie können die importierten Daten filtern, indem Sie unnötige Spalten ausschließen. Sie können auch entscheiden, ob Sie Daten mit dem Abfrage-Generator oder dem Abfragebefehl abrufen möchten. Erkunden Sie als nächsten Schritt diese Alternativen: Abrufen von Daten aus einem Datenfeed in Power Pivot und Importieren von Daten aus Analysis Services oder Power Pivot.

Power View-Berichte sind für die Verwendung mit Datenmodellen konzipiert, die denen ähneln, die Sie gerade erstellt haben. Erfahren Sie mehr über die umfassende Ansicht, die Power View in Excel bietet: Aktivieren Sie Power View in Excel 2013 und Power View: Erkunden, visualisieren und präsentieren Sie Ihre Daten.

Versuchen Sie, Ihr Datenmodell zu verbessern, um in Zukunft leistungsfähigere Power View-Berichte zu erstellen, indem Sie sich das folgende Tutorial ansehen. Optimierung des Datenmodells für Power View-Berichte.