Berechnen Moving Average In Access Sql

22 SQL für Analyse und Reporting Oracle verfügt über erweiterte analytische Verarbeitungsfunktionen für SQLs, indem eine neue Familie von analytischen SQL-Funktionen eingeführt wird. Mit diesen analytischen Funktionen können Sie berechnen: Ranglisten und Perzentile Verschieben von Fensterberechnungen Lineare Regressionsstatistik Die Rangierfunktionen umfassen kumulative Verteilungen, Prozentrang und N-Kacheln. Bewegliche Fensterberechnungen ermöglichen es Ihnen, verschiebende und kumulative Aggregationen wie Summen und Mittelwerte zu finden. Die Laglead-Analyse ermöglicht direkte Reihenreferenz-Referenzen, so dass Sie Periodenänderungen berechnen können. Mit der Firstlast-Analyse können Sie den ersten oder letzten Wert einer geordneten Gruppe finden. Weitere Erweiterungen für SQL sind der CASE-Ausdruck und der partitionierte äußere Join. CASE-Ausdrücke bieten, wenn-dann Logik nützlich in vielen Situationen. Partitionierte äußere Verknüpfung ist eine Erweiterung der ANSI-Outer-Join-Syntax, die es Benutzern ermöglicht, bestimmte Dimensionen selektiv zu verdichten und andere spärlich zu halten. Auf diese Weise können Reporting-Tools selektiv die Dimensionen verdichten, zum Beispiel diejenigen, die in ihren tabellarischen Berichten erscheinen, während andere spärlich beibehalten werden. Um die Performance zu erhöhen, können analytische Funktionen parallelisiert werden: Mehrere Prozesse können gleichzeitig alle diese Anweisungen ausführen. Diese Fähigkeiten machen Berechnungen einfacher und effizienter, wodurch die Leistung, Skalierbarkeit und Einfachheit der Datenbank verbessert wird. Analytische Funktionen werden wie in Tabelle 22-1 beschrieben klassifiziert. Tabelle 22-1 Analytische Funktionen und ihre Verwendungen Um diese Operationen auszuführen, fügen die analytischen Funktionen der SQL-Verarbeitung mehrere neue Elemente hinzu. Diese Elemente bauen auf vorhandenen SQL auf, um flexible und leistungsfähige Berechnungsausdrücke zu ermöglichen. Mit wenigen Ausnahmen haben die analytischen Funktionen diese neuen Elemente. Der Verarbeitungsablauf ist in Abbildung 22-1 dargestellt. Abbildung 22-1 Verarbeitungsauftrag Die wesentlichen Konzepte, die in analytischen Funktionen verwendet werden, sind: Die Abfrageverarbeitung mittels analytischer Funktionen erfolgt in drei Stufen. Erstens, alle Joins, WHERE. GROUP BY und HAVING-Klauseln durchgeführt. Zweitens wird die Ergebnismenge den analytischen Funktionen zur Verfügung gestellt, und alle ihre Berechnungen erfolgen. Drittens, wenn die Abfrage eine ORDER BY-Klausel an ihrem Ende hat, wird die ORDER BY verarbeitet, um eine präzise Ausgabeordnung zu ermöglichen. Die Verarbeitungsreihenfolge ist in Abbildung 22-1 dargestellt. Ergebnismengenpartitionen Die analytischen Funktionen ermöglichen es Benutzern, Abfrageergebnismengen in Gruppen von Zeilen, die als Partitionen bezeichnet werden, zu teilen. Beachten Sie, dass der Begriff Partitionen, die mit analytischen Funktionen verwendet werden, nicht mit dem Tabellenpartitionsmerkmal verknüpft ist. In diesem Kapitel bezieht sich der Begriff Partitionen nur auf die Bedeutung der analytischen Funktionen. Partitionen werden nach den Gruppen definiert, die mit GROUP BY-Klauseln definiert sind, sodass sie für alle aggregierten Ergebnisse wie Summen und Durchschnittswerte verfügbar sind. Partitionsbereiche können auf beliebigen Spalten oder Ausdrücken basieren. Eine Abfrageergebnismenge kann in nur eine Partition partitioniert werden, die alle Zeilen, einige große Partitionen oder viele kleine Partitionen mit nur wenigen Zeilen enthält. Für jede Zeile in einer Partition können Sie ein Schiebefenster von Daten definieren. Dieses Fenster bestimmt den Bereich der Zeilen, die für die Berechnung der aktuellen Zeile verwendet werden. Fenstergrößen können entweder auf eine physikalische Anzahl von Zeilen oder ein logisches Intervall wie Zeit basieren. Das Fenster hat eine Anfangszeile und eine Endzeile. Je nach ihrer Definition kann sich das Fenster an einem oder beiden Enden bewegen. Beispielsweise würde ein Fenster, das für eine kumulative Summenfunktion definiert ist, seine Startzeile in der ersten Zeile seiner Partition festgelegt, und seine Endzeile würde vom Anfangspunkt bis zur letzten Zeile der Partition verschoben. Im Gegensatz dazu würde ein Fenster, das für einen gleitenden Durchschnitt definiert ist, sowohl seine Anfangs - als auch seine Endpunkte gleiten lassen, so daß sie einen konstanten physikalischen oder logischen Bereich beibehalten. Ein Fenster kann so groß sein wie alle Zeilen in einer Partition oder nur ein Schiebefenster einer Zeile innerhalb einer Partition. Wenn ein Fenster in der Nähe eines Rahmens ist, gibt die Funktion Ergebnisse nur für die verfügbaren Zeilen zurück, anstatt Sie zu warnen, dass die Ergebnisse nicht das sind, was Sie möchten. Bei der Verwendung von Fensterfunktionen ist die aktuelle Zeile während der Berechnungen enthalten, so dass Sie nur n (n) angeben sollten, wenn Sie mit n Elementen zu tun haben. Jede Berechnung, die mit einer analytischen Funktion durchgeführt wird, basiert auf einer aktuellen Zeile innerhalb einer Partition. Die aktuelle Zeile dient als Referenzpunkt, der den Anfang und das Ende des Fensters bestimmt. Zum Beispiel könnte eine zentrierte gleitende Durchschnittsberechnung mit einem Fenster definiert werden, das die aktuelle Zeile, die sechs vorhergehenden Zeilen und die folgenden sechs Zeilen enthält. Dies würde ein Schiebefenster von 13 Reihen erzeugen (siehe Abbildung 22-2). Abbildung 22-2 Schiebefenster Beispiel Ranking, Windowing und Reporting Funktionen In diesem Abschnitt werden die grundlegenden analytischen Funktionen für Ranking, Fenstering und Reporting dargestellt. Beispiel Lineare Regressionsberechnung In diesem Beispiel berechnen wir eine Regressionsgerade, die die Menge eines Produktes als lineare Funktion des Produktlistenpreises ausdrückt. Die Berechnungen werden nach Vertriebskanal gruppiert. Die Werte SLOPE. INTCPT. RSQR sind Steigung, Intercept und Bestimmungskoeffizient der Regressionslinie. Der (ganzzahlige) Wert COUNT ist die Anzahl der Produkte in jedem Kanal, für den sowohl die verkauften Mengen als auch die Listenpreisdaten verfügbar sind. Statistische Aggregate Oracle bietet eine Reihe statistischer SQL-Funktionen und ein Statistikpaket, DBMSSTATFUNCS. In diesem Abschnitt werden einige der neuen Funktionen zusammen mit der grundlegenden Syntax aufgelistet. Beschreibende Statistik Sie können folgende deskriptive Statistik berechnen: Median eines Datensatz-Modus eines Datensatzes Sie können folgende parametrische Statistik berechnen: Spearmans rho Koeffizient Kendalls tau-b Koeffizient Zusätzlich zu den Funktionen verfügt diese Version über ein PLSQL-Paket, DBMSSTATFUNCS . Es enthält die beschreibende statistische Funktion ZUSAMMENFASSUNG zusammen mit Funktionen zur Unterstützung der Verteilungsanpassung. Die SUMMARY-Funktion fasst eine numerische Spalte einer Tabelle mit einer Vielzahl von deskriptiven Statistiken zusammen. Die fünf Verteilungsanpassungsfunktionen unterstützen normale, einheitliche, Weibull-, Poisson - und Exponentialverteilungen. Benutzerdefinierte Aggregate Oracle bietet eine Möglichkeit zur Erstellung eigener Funktionen, sogenannte benutzerdefinierte Aggregatfunktionen. Diese Funktionen sind in Programmiersprachen wie PLSQL, Java und C geschrieben und können als analytische Funktionen oder Aggregate in materialisierten Sichten verwendet werden. Weitere Informationen zu Syntax und Einschränkungen finden Sie im Oracle Database Data Cartridge Developers Guide. Die Vorteile dieser Funktionen sind: Sehr komplexe Funktionen können mit einer vollständig prozeduralen Sprache programmiert werden. Höhere Skalierbarkeit als andere Techniken, wenn benutzerdefinierte Funktionen für die Parallelverarbeitung programmiert werden. Objektdatentypen können verarbeitet werden. Als einfaches Beispiel für eine benutzerdefinierte Aggregatfunktion ist die Skew-Statistik zu betrachten. Diese Berechnung mißt, wenn ein Datensatz eine schiefe Verteilung um seinen Mittelwert hat. Es wird Ihnen sagen, wenn ein Schwanz der Verteilung ist deutlich größer als die anderen. Wenn Sie ein benutzerdefiniertes Aggregat namens udskew erstellt und auf die Kreditlimitdaten im vorherigen Beispiel angewendet haben, können die SQL-Anweisung und die Ergebnisse wie folgt aussehen: Bevor Sie benutzerdefinierte Aggregatfunktionen erstellen, sollten Sie prüfen, ob Ihre Anforderungen erfüllt werden können In regelmäßigen SQL. Viele komplexe Berechnungen sind direkt in SQL möglich, insbesondere durch Verwendung des CASE-Ausdrucks. Der Aufenthalt mit regulärem SQL ermöglicht eine einfachere Entwicklung, und viele Abfrageoperationen sind in SQL bereits gut parallelisiert. Selbst das frühere Beispiel, die Skew-Statistik, kann mit Standard, wenn auch langwierig, SQL erstellt werden. Pivoting-Operationen Das von Business Intelligence-Abfragen zurückgegebene d ata ist oft am nützlichsten, wenn es in einem Kreuztabellenformat dargestellt wird. Mit der Pivotklausel der SELECT-Anweisung können Sie Querstabulationsabfragen schreiben, die Zeilen in Spalten umwandeln und dabei Daten in den Prozess der Rotation aggregieren. Pivoting ist eine Schlüsseltechnologie in Data Warehouses. Dabei transformieren Sie mehrere Zeilen der Eingabe in weniger und allgemein breitere Zeilen im Data Warehouse. Beim Drehen wird für jedes Element in der Pivotspaltenwerteliste ein Aggregationsoperator angewendet. Die Pivot-Spalte darf keinen beliebigen Ausdruck enthalten. Wenn Sie auf einen Ausdruck schwenken müssen, dann sollten Sie den Ausdruck in einer Ansicht vor dem PIVOT-Vorgang alias. Die grundlegende Syntax lautet wie folgt: Um die Verwendung von Pivotings zu veranschaulichen, erstellen Sie die folgende Sicht als Grundlage für spätere Beispiele: Beispiel: Pivoting Die folgende Anweisung veranschaulicht ein typisches Pivot in der Kanalspalte: Beachten Sie, dass die Ausgabe vier neue Aliasspalten erstellt hat , DIREKTVERTRIEB. INTERNETSALES. KATALOGVERZEICHNIS. Und TELESALES. Eine für jeden der Schwenkwerte. Die Ausgabe ist eine Summe. Wenn kein Alias ​​angegeben ist, sind die Spaltenüberschriften die Werte der IN-Liste. Schwenken auf mehrere Spalten Sie können auf mehr als einer Spalte schwenken. Die folgende Anweisung veranschaulicht einen typischen Mehrfachspalten-Pivot: Beachten Sie, dass dieses Beispiel eine mehrspaltige IN-Liste mit Spaltenüberschriften angibt, die für die Übereinstimmung mit den IN-Listenmitgliedern ausgelegt sind. Pivot: Mehrere Aggregate Sie können mit mehreren Aggregaten schwenken, wie im folgenden Beispiel gezeigt: Beachten Sie, dass die Abfrage Spaltenüberschriften erstellt, indem die Pivotwerte (oder der Alias) mit dem Alias ​​der Aggregatfunktion und einem Unterstrich verkettet werden. Unterscheidung von PIVOT-generierten Nulls aus Nullstellen in Quelldaten Sie können zwischen Nullwerten unterscheiden, die aus der Verwendung von PIVOT und denen, die in den Quelldaten vorhanden sind, erzeugt werden. Das folgende Beispiel veranschaulicht NULLs, die PIVOT erzeugt. Die folgende Abfrage gibt Zeilen mit 5 Spalten, Spalte prodid zurück. Und die sich ergebenden Säulen Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Für jeden eindeutigen Wert von prodid. Q1COUNTTOTAL gibt die Gesamtzahl der Zeilen zurück, deren qtr-Wert Q1 ist. Das heißt, und Q2COUNTTOTAL gibt die Gesamtzahl der Zeilen zurück, deren qtr-Wert Q2 ist. Angenommen, wir haben eine Tabelle sales2 der folgenden Struktur: Aus dem Ergebnis wissen wir, dass es für prodid 100 zwei Verkaufsreihen für Quartal Q1 gibt. Und 1 Verkaufsreihe für Quartal Q2 für Prodid 200 gibt es 1 Verkaufsreihe für Quartal Q1. Und keine Verkaufsreihe für Quartal Q2.So, in Q2COUNTTOTAL. Können Sie identifizieren, dass NULLlt1gt aus einer Zeile in der ursprünglichen Tabelle stammt, deren Maß einen Nullwert hat, während NULLlt2gt aufgrund der Tatsache, dass keine Zeile in der ursprünglichen Tabelle für prodid 200 in Quartal Q2 vorhanden ist. Unpivoting-Operationen Ein Unpivot kehrt eine PIVOT-Operation nicht um. Stattdessen dreht es Daten aus Spalten in Zeilen. Wenn Sie mit geschwenkten Daten arbeiten, kann eine UNPIVOT-Operation keine Aggregationen, die von PIVOT oder auf andere Weise erstellt wurden, umkehren. Um Unpivot zu veranschaulichen, erstellen Sie zunächst eine schwenkbare Tabelle, die vier Spalten enthält, für Quartale des Jahres: Die Tabelleninhalte ähneln den folgenden: Die folgende UNPIVOT-Operation dreht die Viertelspalten in Zeilen. Für jedes Produkt gibt es vier Zeilen, eine für jedes Quartal. Beachten Sie die Verwendung von INCLUDE NULLS in diesem Beispiel. Sie können auch EXKLUSIVE NULLS verwenden. Die die Standardeinstellung ist. Darüber hinaus können Sie auch mit zwei Spalten unpivot, wie im Folgenden: Wildcard und Unterabfrage Pivoting mit XML-Operationen Wenn Sie ein Platzhalter-Argument oder Unterabfrage in Ihre Pivot-Spalten verwenden möchten, können Sie dies mit PIVOT-XML-Syntax. Mit PIVOT XML ist die Ausgabe der Operation korrekt formatiertes XML. Das folgende Beispiel veranschaulicht die Verwendung des Wildcard-Schlüsselworts ANY. Es gibt XML aus, das alle Channel-Werte in salesview enthält: Beachten Sie, dass das Schlüsselwort ANY in PIVOT-Operationen nur als Teil einer XML-Operation verfügbar ist. Diese Ausgabe enthält Daten für Fälle, in denen der Kanal im Datensatz vorhanden ist. Beachten Sie außerdem, dass Aggregationsfunktionen eine GROUP BY-Klausel angeben müssen, um mehrere Werte zurückzugeben, doch enthält die Pivotklausel keine explizite GROUP BY-Klausel. Stattdessen führt die Pivotklausel eine implizite GROUP BY durch. Das folgende Beispiel veranschaulicht die Verwendung einer Unterabfrage. Es gibt XML aus, das alle Kanalwerte und die Verkaufsdaten enthält, die jedem Kanal entsprechen: Der Ausgang verdichtet die Daten, um alle möglichen Kanäle für jedes Produkt einzuschließen. Data Densification for Reporting Daten werden in der Regel in spärlicher Form gespeichert. Das heißt, wenn kein Wert für eine gegebene Kombination von Dimensionswerten existiert, existiert keine Zeile in der Faktentabelle. Sie können die Daten jedoch in dichter Form anzeigen, wobei Zeilen für alle Kombinationen von Bemaßungswerten angezeigt werden, auch wenn keine Faktendaten für sie vorhanden sind. Wenn beispielsweise ein Produkt während eines bestimmten Zeitraums nicht verkauft wurde, können Sie das Produkt weiterhin für diesen Zeitraum sehen, wenn der Verkaufswert daneben liegt. Darüber hinaus können Zeitreihenberechnungen am einfachsten durchgeführt werden, wenn Daten entlang der Zeitdimension dicht sind. Dies liegt daran, dass dichte Daten eine konsistente Anzahl von Zeilen für jede Periode füllen, was wiederum es einfach macht, die analytischen Fensterfunktionen mit physischen Offsets zu verwenden. Datenverdichtung ist der Prozess der Umwandlung von spärlichen Daten in dichte Form. Um das Problem der Sparsität zu überwinden, können Sie eine partitionierte äußere Verknüpfung verwenden, um die Lücken in einer Zeitreihe oder einer anderen Dimension zu füllen. Eine solche Verknüpfung erweitert die herkömmliche äußere Verknüpfungssyntax, indem die äußere Verknüpfung auf jede in einer Abfrage definierte logische Partition angewendet wird. Oracle partitioniert die Zeilen in Ihrer Abfrage auf der Grundlage des in der PARTITION BY-Klausel angegebenen Ausdrucks logisch. Das Ergebnis einer partitionierten äußeren Verknüpfung ist eine UNION der äußeren Verknüpfungen jeder der Partitionen in der logisch partitionierten Tabelle mit der Tabelle auf der anderen Seite der Verknüpfung. Beachten Sie, dass Sie diese Art von Join verwenden können, um die Lücken in jeder Dimension zu füllen, nicht nur die Zeitdimension. Die meisten Beispiele beziehen sich hier auf die Zeitdimension, da sie die am häufigsten verwendete Dimension für Vergleiche ist. Partition Join Syntax Die Syntax für partitionierte äußere Verknüpfung erweitert die ANSI SQL JOIN-Klausel um den Ausdruck PARTITION BY, gefolgt von einer Ausdrucksliste. Die Ausdrücke in der Liste geben die Gruppe an, auf die die äußere Verknüpfung angewendet wird. Im Folgenden finden Sie die beiden Syntaxformen, die normalerweise für partitionierte äußere Verknüpfungen verwendet werden: Beachten Sie, dass FULL OUTER JOIN nicht mit einer partitionierten äußeren Verknüpfung unterstützt wird. Beispiel von Sparse-Daten Eine typische Situation mit einer spärlichen Dimension wird im folgenden Beispiel gezeigt, das die wöchentlichen Verkäufe und den Jahresabschluss für das Produkt Bounce für die Wochen 20-30 in den Jahren 2000 und 2001 berechnet Erwarten würde 22 Reihen von Daten (11 Wochen jeweils von 2 Jahren), wenn die Daten waren dicht. Allerdings erhalten wir nur 18 Zeilen, weil die Wochen 25 und 26 im Jahr 2000 und die Wochen 26 und 28 im Jahr 2001 fehlen. Füllen von Datenlücken Wir können die spärlichen Daten der vorherigen Abfrage nehmen und eine partitionierte äußere Verknüpfung mit einem dichten Satz ausführen Zeit-Daten. In der folgenden Abfrage übergeben wir unsere ursprüngliche Abfrage als v und wir wählen Daten aus der Zeittabelle, die wir alias als t. Hier werden 22 Zeilen abgerufen, da es keine Lücken in der Reihe gibt. Die vier hinzugefügten Zeilen haben jeweils 0, wenn der Verkaufswert auf 0 gesetzt wird, indem die NVL-Funktion verwendet wird. Beachten Sie, dass in dieser Abfrage eine WHERE-Bedingung für Wochen zwischen 20 und 30 in der Inline-Ansicht für die Zeitdimension platziert wurde. Dies wurde eingeführt, um die Ergebnismenge klein zu halten. Füllen von Lücken in zwei Dimensionen N-dimensionale Daten werden typischerweise als ein dichter zweidimensionaler Querstreifen von (n - 2) Seitenabmessungen angezeigt. Dies erfordert, dass alle Bemaßungswerte für die beiden Dimensionen, die in der Quer-Registerkarte angezeigt werden, eingegeben werden. Im Folgenden ist ein anderes Beispiel, bei dem die partitionierte äußere Verknüpfungsfunktion zum Füllen der Lücken auf zwei Dimensionen verwendet werden kann: In dieser Abfrage die WITH-Unterabfrage Factoring-Klausel V1 fasst die Umsatzdaten auf Produkt-, Länder - und Jahresebene zusammen. Dieses Ergebnis ist spärlich, aber Benutzer können alle Länder-, Jahr-Kombinationen für jedes Produkt sehen. Um dies zu erreichen, nehmen wir jede Partition von v1 basierend auf Produktwerten und äußere verbinden sie auf der Land-Dimension zuerst. Dieses gibt uns alle Werte des Landes für jedes Produkt. Wir nehmen dann dieses Ergebnis und partitionieren es auf Produkt-und Landeswerte und dann äußere verbinden sie auf Zeitdimension. Damit erhalten Sie für jede Produkt - und Länderkombination alle Zeitwerte. Füllen von Lücken in einer Inventartabelle In einer Inventartabelle wird typischerweise eine Menge von Einheiten angezeigt, die für verschiedene Produkte verfügbar sind. Diese Tabelle ist spärlich: sie speichert nur eine Zeile für ein Produkt, wenn es ein Ereignis gibt. Für eine Verkaufstabelle ist die Veranstaltung ein Verkauf, und für die Inventartabelle ist das Ereignis eine Mengenänderung, die für ein Produkt verfügbar ist. Beachten Sie zum Beispiel die folgende Inventartabelle: Die Inventartabelle verfügt nun über die folgenden Zeilen: Für Berichtszwecke können Benutzer diese Inventardaten anders sehen. Sie können z. B. alle Werte der Zeit für jedes Produkt sehen. Dies kann durch partitionierte äußere Verknüpfung erreicht werden. Außerdem können Benutzer für die neu eingefügten Zeilen fehlender Zeitperioden die Werte für Mengeneinheitsspalten sehen, die von der aktuellsten vorhandenen Zeitdauer übertragen werden sollen. Letzteres kann mit dem analytischen Fensterfunktion-LASTVALUE-Wert erreicht werden. Hier ist die Abfrage und die gewünschte Ausgabe: Die innere Abfrage berechnet eine partitionierte äußere Verknüpfung auf Zeit innerhalb jedes Produkts. Die innere Abfrage verdichtet die Daten auf der Zeitdimension (dh die Zeitdimension hat nun eine Zeile für jeden Wochentag). Für die neu hinzugefügten Zeilen hat die Maßsäulenmenge jedoch Nullen (siehe die Ausgabe in der Spaltenmenge in den folgenden Ergebnissen: Die äußere Abfrage verwendet die analytische Funktion LASTVALUE.) Bei Anwendung dieser Funktion werden die Daten nach Produkt partitioniert und die Daten auf der (Timeid) Für jede Zeile findet die Funktion aufgrund der Option IGNORE NULLS, die Sie sowohl mit LASTVALUE als auch mit FIRSTVALUE verwenden können, den letzten Nicht-Nullwert im Fenster. In der Spalte repequentity sehen wir die gewünschte Ausgabe Die folgende Ausgabe: Berechnen von Datenwerten zum Füllen von Lücken Beispiele im vorherigen Abschnitt veranschaulichen, wie partitionierte äußere Verknüpfung verwendet wird, um Lücken in einer oder mehreren Dimensionen zu füllen. Die Ergebnismengen, die durch partitionierte äußere Verknüpfung erzeugt werden, haben jedoch Nullwerte für Spalten, die nicht in enthalten sind Die PARTITION BY-Liste, die normalerweise analytische SQL-Funktionen verwenden, um diese Nullwerte durch einen Nicht-Nullwert zu ersetzen. Beispielsweise berechnet die folgende Abfrage monatliche Summen für Produkte 64 MB Speicherkarte und DVD-R Discs (Produkt-IDs 122 und 136) für das Jahr 2000. Es verwendet partitionierte äußere Verknüpfung, um Daten für alle Monate zu verdichten. Für die fehlenden Monate wird dann die analytische SQL-Funktion AVG verwendet, um die Umsätze und Einheiten als den Durchschnitt der Monate zu errechnen, in denen das Produkt verkauft wurde. Bei der Arbeit in SQLPlus enthalten die folgenden zwei Befehle die Spaltenüberschriften für eine bessere Lesbarkeit der Ergebnisse: Zeitreihenberechnungen für Densified Data Densificatio n ist nicht nur für Berichtszwecke gedacht. Es ermöglicht auch bestimmte Arten von Berechnungen, insbesondere Zeitreihenberechnungen. Zeitreihenberechnungen sind einfacher, wenn Daten entlang der Zeitdimension dicht sind. Dichte Daten weisen eine konsistente Anzahl von Zeilen für jede Zeitperiode auf, die es wiederum einfach machen, analytische Fensterfunktionen mit physikalischen Offsets zu verwenden. Um dies zu illustrieren, nehmen wir zunächst das Beispiel zum Füllen von Lücken in Daten. Und fügen Sie eine analytische Funktion zu dieser Abfrage hinzu. In der folgenden erweiterten Version berechnen wir wöchentlich jährliche Verkäufe neben dem wöchentlichen Verkauf. Die NULL-Werte, die die partitionierte äußere Verknüpfung bei der Herstellung der Zeitreihen dicht einfügt, werden in der üblichen Weise behandelt: Die SUM-Funktion behandelt sie als 0s. Period-to-Period-Vergleich für eine Zeitstufe: Beispiel Wie verwenden wir diese Funktion, um Werte über Zeiträume hinweg zu vergleichen. Im Einzelnen: Wie berechnen wir einen Vergleichsvergleich im Vergleich zum Vorjahr auf der Wochesebene Die folgende Abfrage gibt dieselbe Zeile zurück , Für jedes Produkt den Jahresabschluss für jede Woche von 2001 mit dem von 2000. Beachten Sie, dass wir in diesem Beispiel mit einer WITH-Klausel beginnen. Dies verbessert die Lesbarkeit der Abfrage und lässt uns auf den partitionierten äußeren Join fokussieren. Bei der Arbeit in SQLPlus enthält der folgende Befehl die Spaltenüberschriften für eine bessere Lesbarkeit der Ergebnisse: In der FROM-Klausel der Inline-Ansicht densesales. Verwenden wir eine partitionierte äußere Verknüpfung der Aggregatansicht v und der Zeitansicht t, um Lücken in den Verkaufsdaten entlang der Zeitdimension zu füllen. Die Ausgabe der partitionierten äußeren Verknüpfung wird dann durch die analytische Funktion SUM verarbeitet. OVER, um den wöchentlichen Jahresabschluss zu berechnen (die weeklyytdsales Spalte). So berechnet die Ansicht densesales die Jahresabschlussdaten für jede Woche, einschließlich der fehlenden Summenansichten. Die Inline-Ansicht yearoveryearsales berechnet dann den vorjährigen wöchentlichen Jahresabschluss mit der LAG-Funktion. Die LAG-Funktion weeklyytdsalesprioryear gibt eine PARTITION BY-Klausel an, die Zeilen für die gleiche Woche der Jahre 2000 und 2001 in einer einzigen Partition paaren. Wir übergeben dann einen Offset von 1 an die LAG-Funktion, um das wöchentliche Jahr zu erhalten, um die Verkäufe für das vorherige Jahr zu erreichen. Der äußerste Abfrageblock wählt Daten von yearoveryearsales mit der Bedingung yr 2001. und so gibt die Abfrage für jedes Produkt seine wöchentliche zurück Jahr-to-date-Umsatz in den angegebenen Wochen der Jahre 2001 und 2000. Period-to-Period Vergleich für mehrere Zeitstufen: Beispiel Während das vorherige Beispiel zeigt uns eine Möglichkeit, Vergleiche für ein einziges Zeitniveau zu schaffen, wäre es noch mehr Nützlich, um mehrere Zeitniveaus in einer einzelnen Abfrage zu behandeln. Zum Beispiel könnten wir den Umsatz gegenüber dem Vorjahreszeitraum auf Jahr, Quartal, Monat und Tagestufe vergleichen. Wie können wir eine Abfrage erstellen, die einen jährlichen Vergleich des Jahresabschlusses für alle Ebenen unserer Zeithierarchie durchführt. Wir werden diese Schritte in mehreren Schritten durchführen. Das Ziel ist eine einzelne Abfrage mit Vergleichen am Tag, Woche, Monat, Quartal und Jahr. Die Schritte sind wie folgt: Wir erstellen eine Ansicht namens cubeprodtime. Die einen hierarchischen Würfel von Umsatz aggregiert über Zeiten und Produkte hält. Dann erstellen wir eine Ansicht der Zeitdimension, die als Kante des Würfels verwendet werden soll. Die Zeitkante, die einen vollständigen Satz von Daten enthält, wird partitioniert mit den spärlichen Daten in der Ansicht cubeprodtime verbunden. Schließlich, für maximale Leistung, erstellen wir eine materialisierte Ansicht, mvprodtime. Gebaut mit der gleichen Definition wie cubeprodtime. Weitere Informationen zu hierarchischen Cubes finden Sie in Kapitel 21, SQL für Aggregation in Data Warehouses. Die materialisierte Ansicht wird in Schritt 1 im folgenden Abschnitt definiert. Schritt 1 Erstellen Sie die hierarchische Cube-Ansicht Die materialisierte Ansicht, die im Folgenden dargestellt wird, kann in Ihrem System vorhanden sein, wenn nicht, erstellen Sie sie jetzt. Wenn Sie es generieren müssen, beachten Sie, dass wir die Abfrage auf nur zwei Produkte beschränken, um die Bearbeitungszeit kurz zu halten: Da diese Ansicht auf zwei Produkte beschränkt ist, gibt sie knapp über 2200 Zeilen zurück. Beachten Sie, dass die Spalte HierarchicalTime Stringdarstellungen von Zeit aus allen Ebenen der Zeithierarchie enthält. Der CASE-Ausdruck, der für die HierarchicalTime-Spalte verwendet wird, fügt eine Markierung (0, 1.) jeder Datumszeichenfolge hinzu, um die Zeitstufe des Wertes anzugeben. A 0 steht für das Jahr, 1 für Quartale, 2 für Monate und 3 für Tag. Beachten Sie, dass die GROUP BY-Klausel eine verkettete ROLLUP ist, die die Rollup-Hierarchie für die Zeit - und Produktdimensionen angibt. Die GROUP BY-Klausel bestimmt den hierarchischen Cube-Inhalt. Schritt 2 Erstellen Sie die Ansicht edgetime, die eine vollständige Menge von Datumswerten ist Edgetime ist die Quelle für das Ausfüllen von Zeitlücken im hierarchischen Cube mit einem partitionierten äußeren Join. Die Spalte HierarchicalTime in edgetime wird in einer partitionierten Verknüpfung mit der Spalte HierarchicalTime in der View cubeprodtime verwendet. Die folgende Anweisung definiert edgetime: Schritt 3 Erstellen der materialisierten Ansicht mvprodtime zur Unterstützung schnellerer Performance Die materialisierte Viewdefinition ist ein Duplikat der zuvor definierten View cubeprodtime. Da es sich um eine doppelte Abfrage handelt, werden Verweise auf cubeprodtime neu geschrieben, um die materialisierte Ansicht mvprodtime zu verwenden. Die folgenden Materialisierungen können in Ihrem System vorhanden sein, wenn nicht, erstellen Sie es jetzt. Wenn Sie es generieren müssen, beachten Sie, dass wir die Abfrage auf nur zwei Produkte beschränken, um die Bearbeitungszeit kurz zu halten. Schritt 4 Erstellen der Vergleichsabfrage Wir haben nun die Stufe für unsere Vergleichsabfrage gesetzt. Wir können Periodenvergleichsberechnungen auf allen Zeitniveaus erhalten. Es erfordert das Anwenden von analytischen Funktionen auf einen hierarchischen Würfel mit dichten Daten entlang der Zeitdimension. Einige der Berechnungen, die wir für jedes Zeitniveau erreichen können, sind: Summe der Verkäufe für vorherigen Zeitraum auf allen Zeitniveaus. Umsatzabweichung gegenüber Vorjahr. Summe des Umsatzes im gleichen Zeitraum vor einem Jahr auf allen Ebenen der Zeit. Umsatzveränderung im Vergleich zum Vorjahreszeitraum. Im folgenden Beispiel werden alle vier Berechnungen durchgeführt. Es verwendet eine partitionierte äußere Verknüpfung der Ansichten cubeprodtime und edgetime, um eine Inline-Ansicht von dichten Daten namens densecubeprodtime zu erstellen. Die Abfrage verwendet dann die LAG-Funktion auf dieselbe Weise wie das vorherige einstufige Beispiel. Die äußere WHERE-Klausel gibt die Zeit auf drei Ebenen an: die Tage August 2001, den gesamten Monat und das gesamte dritte Quartal 2001. Beachten Sie, dass die letzten beiden Zeilen der Ergebnisse die Monats - und Quartalsniveau-Aggregationen enthalten Die Ergebnisse leichter zu lesen, wenn Sie SQLPlus verwenden, sollten die Spaltenüberschriften mit den folgenden Befehlen angepasst werden. Die Kommandos falten die Spaltenüberschriften, um die Zeilenlänge zu verkürzen: Hier ist die Abfrage, die die aktuellen Verkäufe mit dem Vorjahresvergleich verglichen hat: Die erste LAG-Funktion (salespriorperiod) partitioniert die Daten auf gidp. Katze. Subkat. Prod. Gidt und ordnet die Zeilen auf allen Zeitdimensionsspalten an. Es erhält den Verkaufswert der Vorperiode, indem er einen Offset von 1 übergibt. Die zweite LAG-Funktion (salessameperiodprioryear) partitioniert die Daten auf zusätzlichen Spalten qtrnum. Monnum Und daynum und ordnet es auf yr, damit, mit einem Versatz von 1, es den Vorjahresabsatz für den gleichen Zeitraum berechnen kann. Die äußerste SELECT-Klausel berechnet die Abweichungen. Erstellen eines benutzerdefinierten Member in einer Dimension: Beispiel In vielen analytischen SQL-Tasks ist es hilfreich, benutzerdefinierte Member in einer Dimension zu definieren. Beispielsweise können Sie einen spezialisierten Zeitraum für Analysen definieren. Sie können eine partitionierte äußere Verknüpfung verwenden, um ein Element vorübergehend zu einer Dimension hinzuzufügen. Beachten Sie, dass die neue SQL-MODEL-Klausel geeignet ist, komplexere Szenarios mit neuen Mitgliedern in Dimensionen zu erstellen. Weitere Informationen zu diesem Thema finden Sie in Kapitel 23, SQL für Modellierung. Als Beispiel für eine Aufgabe, was ist, wenn wir ein neues Mitglied für unsere Zeitdimension definieren wollen Wir wollen in unserer Zeitdimension ein 13. Mitglied der Monatsebene schaffen. Dieser 13. Monat ist definiert als die Summe der Verkäufe für jedes Produkt im ersten Monat eines jeden Quartals des Jahres 2001. Die Lösung hat zwei Schritte. Beachten Sie, dass wir diese Lösung mit den Ansichten und Tabellen erstellen, die im vorherigen Beispiel erstellt wurden. Es sind zwei Schritte erforderlich. Erstellen Sie zunächst eine Ansicht mit dem neuen Element, das der entsprechenden Dimension hinzugefügt wurde. Die Ansicht verwendet eine Operation UNION ALL, um das neue Element hinzuzufügen. Verwenden Sie einen CASE-Ausdruck und eine partitionierte äußere Verknüpfung, um das benutzerdefinierte Mitglied abzufragen. Unser neues Mitglied für die Zeitdimension wird mit folgender Ansicht erstellt: In dieser Anweisung wird die Ansicht timec definiert, indem eine UNION ALL der Edgetime-Ansicht (definiert im vorherigen Beispiel) und der benutzerdefinierte 13. Monat ausgeführt wird. Der Gidt-Wert von 8 wurde gewählt, um das benutzerdefinierte Mitglied von den Standardmitgliedern zu unterscheiden. Die UNION ALL gibt die Attribute für ein 13. Monatsmitglied an, indem ein SELECT aus der DUAL-Tabelle ausgeführt wird. Beachten Sie, dass die Gruppierungs-ID Spalte GIDT. Ist auf 8 gesetzt und die Viertelzahl auf 5 gesetzt. Dann ist der zweite Schritt, eine Inline-Ansicht der Abfrage zu verwenden, um eine partitionierte äußere Verknüpfung von cubeprodtime mit timec auszuführen. Dieser Schritt erzeugt Umsatzdaten für den 13. Monat auf jeder Ebene der Produktaggregation. In der Hauptabfrage wird die analytische Funktion SUM mit einem CASE-Ausdruck verwendet, um den 13. Monat zu berechnen, der als Summierung des ersten Monatsumsatzes jedes Quartals definiert ist. Die SUM-Funktion verwendet einen CASE, um die Daten auf Monate 1, 4, 7 und 10 innerhalb eines Jahres zu begrenzen. Aufgrund des winzigen Datensatzes mit nur 2 Produkten sind die Rollup-Werte der Ergebnisse notwendigerweise Wiederholungen von Aggregationen auf niedrigerer Ebene. Für realistischere Rollup-Werte können Sie weitere Produkte aus den Spielkonsolen - und Y-Box-Spiele-Unterkategorien in die darunterliegende materialisierte Ansicht aufnehmen. Verschiedene Analyse - und Reporting-Funktionen Dieser Abschnitt erläutert die folgenden zusätzlichen analytischen Fähigkeiten: WIDTHBUCKET Funktion Für einen gegebenen Ausdruck gibt die WIDTHBUCKET-Funktion die Bucket-Zahl zurück, die das Ergebnis dieses Ausdrucks zugewiesen wird, nachdem sie ausgewertet wurde. Sie können mit dieser Funktion Gleichheitshistogramme erzeugen. Equiwidth-Histogramme unterteilen Datensätze in Buckets, deren Intervallgröße (höchster Wert zum niedrigsten Wert) gleich ist. Die Anzahl der Zeilen, die von jedem Eimer gehalten werden, variiert. Eine verwandte Funktion, NTILE. Schafft gleich große Schaufeln. Equiwidth-Histogramme können nur für numerische, Datums - oder Datetime-Typen erzeugt werden. Die ersten drei Parameter sollten also alle numerischen Ausdrücke oder alle Datumsausdrücke sein. Andere Ausdrücke sind nicht zulässig. Wenn der erste Parameter NULL ist. Das Ergebnis ist NULL. Wenn der zweite oder dritte Parameter NULL ist. Wird eine Fehlermeldung zurückgegeben, da ein NULL-Wert keinen Endpunkt (oder einen beliebigen Punkt) für einen Bereich in einer Datums - oder numerischen Wertdimension angibt. Der letzte Parameter (Anzahl der Buckets) sollte ein numerischer Ausdruck sein, der einen positiven Integerwert 0, NULL auswertet. Oder ein negativer Wert führt zu einem Fehler. Die Buckets sind von 0 bis (n 1) nummeriert. Bucket 0 enthält die Anzahl der Werte, die kleiner als das Minimum sind. Bucket (n 1) enthält die Anzahl der Werte, die größer oder gleich dem maximalen angegebenen Wert sind. WIDTHBUCKET Syntax Das WIDTHBUCKET nimmt vier Ausdrücke als Parameter an. Der erste Parameter ist der Ausdruck, für den das equiwidth-Histogramm gilt. Der zweite und dritte Parameter sind Ausdrücke, die die Endpunkte des akzeptablen Bereichs für den ersten Parameter bezeichnen. Der vierte Parameter bezeichnet die Anzahl der Buckets. Betrachten Sie die folgenden Daten von Tischkunden. Dass die Kreditlimiten von 17 Kunden zeigt. Diese Daten werden in der Abfrage, die in Beispiel 22-24 gezeigt wird, gesammelt. In der Tabelle Kunden. Die Spalte custcreditlimit enthält Werte zwischen 1500 und 15000, und wir können die Werte zu vier Equiwidth Buckets, nummeriert von 1 bis 4, mit WIDTHBUCKET (custcreditlimit, 0, 20000, 4) zuweisen. Idealerweise ist jede Schaufel ein geschlossenes Intervall der reellen Zahlenlinie, z. B. ist die Schaufelzahl 2 Scores zwischen 5000.0000 und 9999.9999 zugeordnet. Manchmal mit 5000, 10000 bezeichnet), um anzuzeigen, daß 5.000 in dem Intervall enthalten sind und 10.000 ausgeschlossen sind. Um Werte außerhalb des Bereiches 0, 20.000 zu erfassen, werden Werte kleiner als 0 einer bezeichneten Unterlaufschaufel mit der Nummer 0 und Werte größer oder gleich 20.000 einer bestimmten Überlaufschaufel zugeordnet, die mit 5 (num Schaufeln 1 Im Algemeinen). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (analysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way. Calculate Exchange Online requirements for bandwidth Bandwidth planning for Exchange Online migrations can be a dry subject, but its essential for a successful move. It isnt as simple as plugging the values into the Microsoft calculators. Admins need to use values and other data to understand where the weak points are and remediate. Its important to look at end users across the organization and perform calculations using Microsoft tools to understand the impact of these Exchange Online requirements. Admins can then use the results to understand if theyll need to make upgrades. Well focus on how to perform this planning and understand the ongoing requirements for using Office 365. Before we begin, its essential to understand how clients access Office 365. Typically, an Outlook client will connect from its local site directly to the Internet, or, if its on a wide area network (WAN), at the closest site with Internet breakout. The client will perform a DNS lookup to obtain the nearest Office 365 point of presence and connect over HTTPS to the service. This typically provides reasonably low latency when accessing the service. During a migration, its typical to switch on features such as Cached Mode and upgrade clients in advance. The Offline Cache stays intact after a hybrid migration, so this removes the need for every client to re-download the mailbox en masse. Collect information to use as input Before calculating the bandwidth necessary for these Exchange Online requirements, we need good input data. Collect the following information about each site with end users, each data center with Exchange and each site with an Internet breakout that end users will use (or used to migrate mailboxes): List of sites with end users, Exchange and Internet breakouts List of end users at each site Define the route to the Internet from each site For each site that traverses the WAN to another site with Internet breakout, define the WAN link speed For each site with an Internet breakout, define the Internet link and WAN link speeds Determine the average mailbox profile for end users either as a whole or based on a site. Admins can use one of Microsofts tools to perform this analysis, such as the Generate Message Profile script for Exchange 2010 and Exchange 2013 and Collate the average and total mailbox sizes per site. The goal for collecting the above data should be to have a picture of the end users, mailboxes, Exchange sites and enough information to determine the bandwidth likely to be required. Calculate Exchange workloads Admins can use the raw data collected with Microsofts Exchange Client Network Bandwidth Calculator to understand the per-site requirements for end users. On the Input sheet, update the Exchange Service field to state Office 365 if required, admins can then use their Exchange mailbox message profile data to update the User Profile information (Figure 1). On the Client Mix sheet, enter each site, accompanied by the sites user profile and the number of end users. This should give you the per-site bandwidth requirements (Figure 2). Well use the results in the next step. Collating per-site user requirements For the per-site end user requirements, well collate the results from the previous step along with other information about the site into a new spreadsheet. Well typically expect this to have the following fields. Country: To help group sites together in larger organizations. Site Name: The physical site name as used in the Exchange calculator. Number of Users: The same number of end users used in the Exchange calculator. Exchange Download Bandwidth Required: The Exchange-to-Client bandwidth required in the calculator. Exchange Upload Bandwidth Required: The Client-to-Exchange bandwidth required in the calculator. Site Type (Internet Breakout Downstream WAN): If the clients access the Internet directly at this site, choose Internet Breakout. If the Internet is accessed via another site or data center, choose Downstream WAN . Total Download Bandwidth: The total download bandwidth available. Total Upload Bandwidth: The total upload bandwidth available. Average Download Bandwidth Utilization: The average download utilization of the relevant link. Average Upload Bandwidth Utilization: The average upload utilization of the relevant link. Upstream Internet Breakout Site Name: If this is a downstream WAN site, then record the site this connects to the Internet through. After adding the appropriate fields and then entering data, the results should appear (Figure 3). In the diagram, weve captured the data from the Exchange Client Network Bandwidth Calculator across the first fields and then entered additional data collected above. This immediately provides us with important information about our Exchange Online requirements, such as whether downstream sites connecting via the WAN have enough bandwidth available and whether sites with their own Internet breakout have enough bandwidth. Collate per-Internet breakout requirements What the per-site user requirements dont tell us is whether sites that have multiple sites connecting through to the Internet can cope. Well need to collate the information on a second worksheet to make this determination. This summary spreadsheet will have the following fields and contain only Internet breakout sites. Country. To help group sites together in larger organizations. Site Name. The physical site name used in the previous spreadsheets. Combined Number of Users. The total number of end users, including end users on this site and those who use this site as Internet breakout. Combined Exchange Download Bandwidth Required. The combined Exchange download bandwidth required across this site and those connecting through it. Combined Exchange Upload Bandwidth Required. The combined Exchange upload bandwidth required across this site and those connecting through it. Total Internet Download Bandwidth. The total download bandwidth available. Total Internet Upload Bandwidth. The total up load bandwidth available. Average Internet Download Bandwidth Utilization. The average download utilization of the relevant link. Average Internet Upload Bandwidth Utilization. The average upload utilization of the relevant link. Downstream Capacity Available Checking to see if the combined Exchange download bandwidth and average current utilization is less than the total available download bandwidth. Upload Capacity Available This should include the same as above, but it checks if enough upload capacity is available. After collating the data, were able to easily identify the Internet breakout sites that will have capacity problems in our example (Figure 4). Our next step as part of our preparation will be to upgrade both the Internet links that fall short and in downstream WAN sites. About the author: Steve Goodman is an Exchange MVP and works as a technical architect for one of the U. K.s leading Microsoft Gold partners. Goodman has worked extensively with Microsoft Exchange since version 5.5 and with Office 365 since its origins in Exchange Labs and LiveEDU. Next Steps Limit bandwidth use during Outlook offline address book syncs