Donnerstag, 29. Juli 2010

Überlegungen über den Aufbau eines Repositorys in einer Business Intelligence Umgebung

Ein kürzlich geführtes Gespräch mit einem Community Kollegen über das Thema Repositorys in Microsoft Business Intelligence Umgebungen, brachte mich auf die Idee meine Überlegungen und Erfahrungen zu dem Thema hier mal kurz zu skizzieren.

Meine Definition eines Repositorys ist eine zentrale Sammlung von Metadaten, Versionsständen und Protokollen. Ein solches Repository gehört meiner Meinung nach primär zu dem Themenkomplex Data Warehouse.

Das Versprechen einer Business Intelligence Lösung bzw. eines Data Warehouses ist u. a. die Bereitstellung der sogenannten “einzigen Wahrheit” in der Organisation. Das ist zum Beispiel durch den Einsatz von OLAP Cubes technisches relativ leicht durchzusetzen. Alle weiteren Frontend Werkzeuge (zum Beispiel Reporting Services oder Excel) greifen auf diese Datenquellen zu. Was passiert aber nun, wenn sich Strukturen des Data Warehouses und/oder der OLAP Cubes verändern? Diese Veränderungen ziehen sich in der Regel bis zu den Frontends durch und haben sehr wahrscheinlich auch Einfluss auf die schon bereitgestellten Reports in Reporting Services und Excel.

In einer klassischen Microsoft Business Intelligence Umgebung greifen die Dienste direkt auf die jeweils anderen Dienste zu.

Präsentation1

Die Dienste des SQL Servers sind technisch gesehen eigenständig. In einer BI Lösung werden diese zwar miteinander verbunden, aber diese Kopplung ist recht lose. Auf der einen Seite sind damit äußerst flexible Architekturen möglich, auf der anderen Seite gibt es kein zentrales Element, welches die Kommunikation zwischen den Diensten steuert. Szenarien, wie das oben geschilderte, werden durch keine Architekturschicht “aufgefangen”.

Die Verwendung eines zentralen Repositorys, welches zwischen den Dienste steht, unterstützt die Business Intelligence Umgebung dabei das Versprechen einer einzigen Wahrheit nachhaltig einzulösen.

Präsentation1

Die Technologie hinter einem solchem Repository ist in unseren Projekten die relationale Engine des SQL Servers. Die notwendige Flexibilität gibt mir aber die Kombination aus strukturierten und semi-strukturierten Daten. Ein Ziel ist es, dass im Projekt als auch bei der späteren Verwendung der Business Intelligence Umgebung nicht mehr an unterschiedlichen Orten nach Informationen gesucht werden muss, sondern, dass gezielt das Repository genutzt werden kann.

Bereits während der Einführung einer Business Intelligence Lösung sollte daher auf den konsequenten Aufbau eines durchgängigen Repositorys geachtet werden.

Das ist zwar jetzt sehr theoretisch, aber gehen wir mal bei einem Beispiel ein wenig ins Detail. Die Reporting Services sind ein sehr leistungsfähiger Dienst. Die Definition der Reports liegen in sogenannten RDL-Dateien vor. Als Beschreibungssprache wird die Report Definition Language verwendet. Diese ist auch sauber von Microsoft dokumentiert.

Innerhalb einer RDL-Datei wird auf eine Datenquelle verwiesen, welche gesondert beschrieben wird. Aber die eigentliche Definition der verwendeten Felder innerhalb des Reports ergibt sich im Report direkt aus dem CommandText der Query und den definierten Feldern. Es ist mit Hausmitteln später nicht mehr einfach herauszufinden, woher die Felder im Report kommen. Dazu müsste die Query analysiert werden (komme ich noch zu). Auch das neue Feature der Shared datasets bringt hierfür nicht wirklich etwas (sonst äußerst praktisch!), da es nur eine weitere Abstraktionsebene zwischen Report und Datenquelle zieht, aber keine eindeutiges Mapping der Felder zu Tabellen (oder Cubes) zulässt. Hier mal ein Blick direkt in eine RDL-Datei. Wir sehen die Query und die einzelnen Felder, aber kein Mapping!

image

Eine konkrete Überlegung für das Repository ist, dass dieses Mapping aber für Impact-Analysen auf jeden Fall stattfinden muss. Wie kann sowas nun realisiert werden?

Im Falle einer relationalen Quelle, also zum Beispiel des direkten Zugriffs von Reports auf das Data Warehouse, lässt sich das recht einfach mittels automatischer generierter Views lösen. Das Data Warehouse stellt über das Repository, als eine Art Presentation Layer, eine Reihe von Views bereit, welche in den Reports verwendet werden können. Wichtig ist dabei, dass die Flexibilität nicht künstlich beschnitten wird. Es sollte auch nicht verschwiegen werden, dass es durchaus Möglichkeiten gibt auf Basis der Query innerhalb eines Reports an die verwendeten Tabellen und Views zu kommen. Realisierbar ist das mit einer Mischung aus RDL-Datei auslesen und Queryplan abfragen! Damit bringen uns selbst Sub-Queries und geschachtelte Views nicht um den Verstand! :-)

Bei einer multidimensionalen Quelle, also einem OLAP Cube, ist es leider nicht ganz so einfach. Da haben wir out-of-the-box keine Views, die wir dafür verwenden können. Um dieser Herausforderung Herr zu werden, macht es Sinn sich mit AMO zu beschäftigen. Den Analysis (Services) Management Objects. Mittels dieses praktischen Frameworks können Cubes auf Basis unserer Metadaten aus dem Repository automatisiert erzeugt werden. Damit haben wir zumindest eine zuverlässige und saubere Datenbasis, auf welcher wir dann aufsetzen können mit den Frontends. Die Metadaten, welche AMO nutzt, greifen zum Beispiel wiederum auf die oben erwähnten Views des Data Warehouses zu.

Wie Ihr seht gibt es durchaus Anwendungsfälle für ein Repository im Rahmen eines Business Intelligence Projektes!

Ein Repository wird bei der Erstellung eines ersten Prototypen einer Business Intelligence Lösung bzw. Umgebung aber meist als überflüssig empfunden. Vielleicht entsteht sogar der Verdacht, dass der Berater künstlich zusätzlichen Zeitaufwand generieren möchte und keinen echten Gegenwert dafür liefert. Dabei gibt es ja eine ganze Reihe von sehr guten Gründen für den Einsatz einer solchen Architektur!

Ein guter Ansatz ist es wesentlich mehr Aufmerksamkeit (und das ist nicht zu verwechseln mit Zeit!) in die Pflege der verwendeten Quellen zu legen. Wenn es geschafft wird die Quellen als eigenständige Objekte zu verstehen, welche entsprechend auch für sich gepflegt werden, dann ist das Projekt meist auf einem guten Weg. Im Rahmen eines standardisierten Deployment Prozesses sollten später neue Objekte auch direkt in das Repository aufgenommen werden.

Ich denke, dass ich mit Sicherheit auf dem PASS Camp im Oktober das eine oder andere zu diesem Thema zeigen werde. Wir werden zum Beispiel RDL-Dateien auseinandernehmen und uns eigene AMO Prozesse codieren.

Samstag, 24. Juli 2010

ADOMD.NET und die CellSet Klasse

So, als eine Ergänzung zu meinen letzten Einträgen zum Thema ADOMD.NET, habe ich heute ein paar weitere Details zur CellSet Klasse.

Ich hatte ja bereits gezeigt, wie Ihr generell mittels ADOMD.NET und der CellSet Klasse auf Daten eines Cube zugreifen könnt. Dazu hatte ich auch ein paar Eigenschaften und Methoden der CellSet Klasse erläutert. Hier nun ein Beispiel für den Zugriff auf ein Resultset innerhalb eines CellSets.

Hier nochmal ein Stück Code, welches sich mit einem Analysis Services Cube verbindet und eine MDX Query ausführt.

AdomdConnection mdcon = new AdomdConnection(“<ConnectionString>”);
mdcon.Open();
AdomdCommand cmd = mdcon.CreateCommand();
cmd.CommandText = “Query”;
CellSet cs = cmd.ExecuteCellSet();
mdcon.Close();

Entscheidend für uns ist hier das CellSet cs, welches wir nun ein wenig mehr “auseinandernehmen” werden!

Dafür brauchen wir aber auch die MDX Query:

SELECT
{[Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Calendar Year].&[2004]} on Columns,
NON EMPTY CROSSJOIN({[Product].[Product Categories].[All Products].children}, {[Customer].[Customer Geography].[All Customers].children}) on Rows
FROM [Adventure Works]
WHERE ([Measures].[Internet Sales Amount])

Wir sind mal wieder auf “Adventure Works” unterwegs. Hier lassen wir uns für die Jahre 2003 und 2004 die Internet Umsätze aufgeschlüsselt nach Produktkategorien und Kundenregionen darstellen.

Das Ergebnis können wir uns so vorstellen:

image

Nur wie kommen wir jetzt mittels des CellSet Objektes cs an die einzelnen Zellen? Um das mal anschaulich darzustellen, habe ich hier für das Resultset von oben die jeweiligen “Pfade” durch das Objekt zu den Daten und Überschriften aufgeführt.

image

Das ist ja schon mal übersichtlich. Nur was sind jetzt diese ganzen Auflistungen und Objekte?

Fangen wir einfach an. Bei der Aufbereitung eines CellSets sollten wir uns zunächst fragen: Wie viele Achsen hat das Resultset? Wer sich mit MDX schon mal beschäftigt hat, weiß ja, dass durchaus mehr als nur zwei Achsen möglich sind. Sowas sollten wir abfangen!

Also mit cs.axes.count kommen wir an die Anzahl der Achsen.

Von der Achse aus kommen wir zur Auflistung Positions, welche uns zu den Tuples der jeweiligen Achse bringt. Unterhalb der Positions haben wir die Members. Hier müssen wir klären, wie viele Members wir haben. Also kommt hier zum Beispiel ein cs.Axes[1].Positions[0].Members.Count. Damit können wir die Beschriftung oben und an der Seite durchführen. Wenn wir uns den UniqueName mit merken, können wir auch bereit sein für weitere Filtervorgänge.

An die eigentliche Werte kommen wir sehr einfach. Wir nehmen das CellSet und adressieren die jeweilige Position innerhalb des Resultsets direkt. Oben im Beispiel seht Ihr das Verfahren sehr deutlich! Und schon haben wir alles Wesentliche vom Resultset ausgelesen.

Schaut doch eigentlich ganz einfach aus, oder?

Donnerstag, 22. Juli 2010

Analysis Services Metadata mittels ADOMD.NET auslesen

Ok, mein Blogartikel über ADOMD.NET war ja auch nur als Einstieg und Anregung gedacht! Aber es kamen nun die Wünsche nach Beispielen und Demos. Daher heute mal ein wenig Democode inkl. Download. Ich hoffe, dass ich in den nächsten Tagen noch mehr dazu beisteuern kann.

Also, ich habe ein kleines Beispiel in WinForms gebaut, welches die Metadaten aus einer Verbindung mit den Analysis Services ausliest und in einem TreeView darstellt. Das ist jetzt bewusst keine fertige BI Anwendung, sondern soll als Beispiel für den Zugriff auf besagte Metadaten dienen.

image

Wichtig ist, dass Ihr auf Eurem Rechner eine ADOMD.NET DLL habt. Sprich Ihr solltet ADOMD.NET installiert haben!

Der eigentliche Code ist recht “einfach” gehalten und hält sich mit wenig auf. Es geht hier ja auch um ein erstes Demo.

            int nCubes = 0;

            tvMetadata.Nodes.Clear();

            AdomdConnection mdcon = new AdomdConnection(tbConnString.Text);
            mdcon.Open();

            foreach (CubeDef cube in mdcon.Cubes)
            {
                if (cube.Name.StartsWith("$"))
                    continue;
                tvMetadata.Nodes.Add(cube.Name + " (" + cube.LastProcessed.ToString() + ")");

                tvMetadata.Nodes[nCubes].Nodes.Add("Measures");
                foreach (Measure mes in cube.Measures)
                    tvMetadata.Nodes[nCubes].Nodes[0].Nodes.Add(mes.Name);

                tvMetadata.Nodes[nCubes].Nodes.Add("Dimensionen");
                int nDims = 0;
                foreach (Dimension dim in cube.Dimensions)
                {
                    tvMetadata.Nodes[nCubes].Nodes[1].Nodes.Add(dim.Name);

                    foreach(Hierarchy hier in dim.Hierarchies)
                        tvMetadata.Nodes[nCubes].Nodes[1].Nodes[nDims].Nodes.Add(hier.Name);
                    nDims++;
                }

                tvMetadata.Nodes[nCubes].Nodes.Add("KPIs");
                foreach (Kpi kpi in cube.Kpis)
                    tvMetadata.Nodes[nCubes].Nodes[2].Nodes.Add(kpi.Name);

                nCubes++;
            }

            mdcon.Close();

        }

Mehr ist für das Darstellen der Metadaten eines Cubes erst mal nicht notwendig!

Eine mögliche Erweiterung wäre sich neben den sprechenden Bezeichnungen auch gleich noch die UniqueNames ausgeben zu lassen bzw. zu speichern. Dafür müsst Ihr statt “Name” nur “UniqueName” verwenden. Hier mal ein Beispiel wie sowas auf der Ebene der Hierarchien aussehen kann.

image

Des Weiteren ignoriere ich zurzeit komplett die DisplayFolder, diese könnten natürlich auch eingebaut werden. Also da ist noch Raum für Kreativität. Und bevor ich es vergesse, dieses Demo und viele weitere gibt es auf dem kommenden PASSCamp 2010 in meinem Track!

Das Projekt gibt es als Download genau hier: http://www.sascha-lorenz.de/downloads/ADOMD.NETMetadataSample.zip

Montag, 19. Juli 2010

Microsoft SQL Server 2008 R2 Update for Developers Training Course

Es gibt auf Basis des SQL Server 2008 R2 Update for Developers Training Kit nun auch noch auf Channel9 einen passenden Kurs in Form von Webcasts!

Also, hier geht es zum Microsoft SQL Server 2008 R2 Update for Developers Training Course ! :-)

Samstag, 17. Juli 2010

PivotViewer Extension for Reporting Services CTP1

Vielleicht erinnert Ihr Euch noch an Pivot? Ich hatte schon mal kurz drüber geschrieben. Dazu ist mittlerweile einiges passiert.

Es gibt nun ein PivotViewer Silverlight Control als Download, welches Ihr auf Euren Seiten einsetzen könnt!

Aber was noch cooler wird, ist die Tatsache, dass Microsoft nun für den SharePoint 2010 ebenfalls eine Erweiterung, welche auf dem Control basiert, als CTP auf der Straße. Diese wird zwar als Designstudie bezeichnet, aber das wirkt schon durchdacht für den Moment.

image

Den Download der “PivotViewer Extension for Reporting Services CTP1” gibt es hier: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d31f609d-a353-41ad-a1a4-f81456e3a6c4

Den Blog zum Projekt und einer sehr nützlichen Anleitung für die Installation hier: http://petcu40.blogspot.com/2010/07/pivotviewer-extension-for-reporting.html

Erste Schritte mit ADOMD.NET

In den letzten Jahren habe ich eine ganze Reihe von Vorträgen über den Zugriff auf Analysis Services Cubes mittels .net Code und ADOMD.NET gehalten. Da die Funktionsweise eigentlich äußert einfach ist, habe ich aber bisher wenig bis wohl gar nichts dazu geschrieben. Das möchte ich nun nachholen, da sich hier bei mir die Nachfragen langsam häufen! :-)

Also, was ist ADOMD.NET eigentlich, was kann das und kostet das was?

ADOMD.NET ist ein sogenannter Data Provider von Microsoft für das .net Framework und bietet u. a. aus meiner .net Anwendung heraus die Möglichkeit auf SQL Server Analysis Services Cubes zuzugreifen. Wichtig ist dabei zu verstehen, dass ADOMD.NET kein fertiges BI Tool oder Control ist, welches ich nur noch einbinden muss und damit eine Art Excel-BI-Grid-GUI in meiner Anwendung habe. ADOMD.NET bietet mir im Code alle Möglichkeiten für den Zugriff auf die Daten, aber ich muss diese selbst kreativ nutzen . Wäre es dann nicht einfacher sich nicht gleich was Fertiges zu kaufen? Ok, wer schon mal eine Session von mir zum Thema gesehen hat, der kennt meine Antwort darauf. In der Regel wollen wir ja nicht den x’ten BI Client schreiben (auch wenn das wirklich Spaß machen kann!) oder unsere Anwendung um das Look & Feel von Excel erweitern, sondern unsere Anwender auf intelligente Art und Weise von den Fähigkeiten eines wahrscheinlich eh schon existierenden Cubes profitieren lassen. Mit Fähigkeiten meine u. a. ich die Flexibilität und Performance in Bezug auf sogenannte Ad-Hoc Queries und die damit verbundenen fast schon magischen Berechnungsmöglichkeiten. Das mit SQL Queries auf einer relationalen Datenbank abzubilden ist zwar meist möglich, aber dazu ist schon einiges an Experten KnowHow erforderlich und an die Performance eines Cubes ist meist gar nicht zu denken. Es lässt sich halt nicht jede Ad-Hoc Query vorher mit einem entsprechenden Index vorbereiten. Liegt nun mal in der Natur der Sache mit diesen Ad-Hoc Abfragen! Kommen wir kurz zu den Kosten. Good news, ADOMD.NET gibst von Microsoft zum SQL Server einfach dazu und ist Bestandteil des Feature Packs. Für den Zugriff wird nur eine gültige Lizenz für den Client benötigt. Also, wenn es aus lizenztechnischer Sicht möglich ist Excel als Client zu nutzen für die SSAS, dann auch ADOMD.NET. Details zu Lizenzen gibst bei Eurem freundlichen Microsoft Kontakt! Ach ja, Feature Packs! Hier kurz die Links, falls Ihr die nicht zur Hand habt:

Microsoft SQL Server 2008 R2 Feature Pack, Microsoft SQL Server 2008 Feature Pack & Microsoft SQL Server 2005 Feature Pack

Wie Ihr seht, gibst das Feature Pack für jede aktuellere Version des SQL Servers. Alles was ich Euch hier über ADOMD.NET schreibe, sollte auch mit jeder dieser Versionen möglich sein. Falls jemand andere Erfahrungen macht, dann Feedback an mich bitte.

Ok, auf was für Daten können wir nun in so einem Cube zugreifen? Ohne jetzt einen Grundlagenartikel über SSAS Cubes zu schreiben, hier mal kurz das Wichtigste kurz zusammengefasst:

Ein Cube besteht aus Dimensionen und Measures. Dabei könnt Ihr Euch die Dimensionen aus Geschäftsobjekte vorstellen und die Measures als die damit verbundenen Geschäftsprozesse bzw. die Ergebnisse der Geschäftsprozesse. Ein einfaches Beispiel ist der Verkauf von Artikel, dabei sind die Geschäftsobjekte “Artikel” und “Kunden” vorhanden und der Prozesse “Verkauf” und die damit verbundenen Umsätze. Nun kann in einem Cube entweder die Summe aller getätigten Verkäufe betrachtet werden oder diese werden auf Basis der Geschäftsobjekte gefiltert. Menschen mit einem Business Intelligence Hintergrund sprechen auch gern von dem “Schneiden” von Daten. Also zeige mir zum Beispiel nur die Verkäufe für die Artikelgruppen A, B & Z und nur für gewerbliche Kunden in Süddeutschland. Klingt doch einfach? Ist es auch, wobei irgendwann wird die Arbeit mit Cubes ein wenig tricky, aber für den Moment reicht uns das an Hintergrund. Das Schöne an Cubes ist es, dass sie solche und ähnliche Abfragen quasi in Gedankengeschwindigkeit verarbeiten und beantworten.

Eine Datenbank in Analysis Services ist in der Lage sich mittels einer ganzen Reihe von sogenannten Metadaten ausführlich selbst zu beschreiben. Wir haben u.a. folgende Metadaten im Zugriff:

  • Cubes
  • Dimensionen
  • Hierarchien
  • Level
  • Measures
  • KPIs
  • Named Sets

Macht es eigentlich Sinn, dass unsere Clients sich immer wieder alle Metadaten ziehen müssen? Auch dazu habe ich mich ja schon öfters geäußert. Aus der Erfahrung heraus ist dafür der Einsatz einer BI Middleware sinnvoll, welche u. a. ein Repository bereitstellt, welches alle Entitäten aller in der BI Umgebung verfügbaren Objekte bereitstellt. Dazu gehören neben der Datenbewirtschaftung (SSIS Pakete) und Reports (RDL Dateien) auch Informationen über unsere Cubes.

Häufig höre ich bei Vorträgen oder auch bei Coachings, dass es doch sicherlich viel zu aufwändig sei so einen Cube mit einzubinden. Da ist es doch einfacher zu versuchen mit den bisherigen Techniken voran zu kommen. Nun ja, was der Bauer nicht kennt, dass frisst er nicht.

Und bevor wir zu sehr in die Details einsteigen, hier ein erstes Codebeispiel für das Herstellen einer Verbindung mit einem Cube:

using Microsoft.AnalysisServices.AdomdClient;

AdomdConnection mdcon = new AdomdConnection(<constring>);
mdcon.open();

Wir machen was hoffentlich Sinnvolles…

mdcon.close();

Ok, das sieht doch schon mal ganz schlank aus. Kommen wir kurz zum Connectionstring.

Eine ganz einfache Variante ist die folgende: “datasource=localhost;catalog=adventure works dw 2008”. Ideal für Demo- und Testzwecke. Dummerweise reicht das nicht im echten Leben und es gibt fast schon unverschämt viele Parameter mit denen die Verbindung konfiguriert werden kann. Für den Moment soll das aber reichen. Unser Connectionstring zeigt auf den Server “localhost” und die Datenbank “Adventure Works DW 2008”. Und immer daran denken, eine Datenbank in SSAS kann mehrere Cubes umfassen!

So, ein weiterer Punkt ist der Namespace “Microsoft.AnalysisServices.AdomdClient” den wir hier verwenden. Damit wir ADOMD.NET überhaupt nutzen können, müssen wir zuerst im Visual Studio einen Verweis auf die “C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll” anlegen! Ab dann klappt das auch mit dem Namespace.

Und was können wir nun mit so einem AdomdConnection Objekt anfangen? Eine ganze Menge! Zunächst fragen wir einfach mal die Umgebung ab, damit wir wissen was uns da eigentlich so geboten wird. Also hier ein wenig mehr Code:

foreach (CubeDef cube in mdcon.Cubes)
  {
                if (cube.Name.StartsWith("$"))
                    continue;
                lbT1.Items.Add(cube.Name+ " ("+cube.LastProcessed.ToString()+")");               

                foreach (Dimension dim in cube.Dimensions)
                {
                    lbT1.Items.Add(" -> "+dim.Name);
                    
                    foreach (Hierarchy hier in dim.Hierarchies)
                    {
                        lbT1.Items.Add("   -> " + hier.Name);

                    } 
                } 
    }

Ist eigentlich recht einfach. Wir nehmen “mdcon”, also die Connection, und lassen uns in der Auflistung “Cubes” alle, nun ja, Cubes zeigen. Wir bekommen ein Cube Objekt, welches wiederum Eigenschaften hat, wie zum Beispiel “Name” & “LastProcessed”. In dem kleinen Codebeispiel schreibe ich die Werte in eine Listbox “lbT1”, daher dieses “lbT1.Items.Add” usw. Nun ja, und genauso machen wir weiter. Jeder Cube hat eine Auflistung für die verwendeten Dimensionen und die wiederum für die Hierarchien usw. Das ist doch einfach, oder?

Was können wir nun mit diesen Daten, welche die Cubes beschreiben, anfangen? Bereits eine ganze Menge! Hängt auch ein wenig von unserer Rolle im Unternehmen oder dem Projekt ab.

Wir können die Metadaten als Basis für spätere Abfragen nehmen. Gute Idee! Merken wir uns mal.

Wir können aber auch die Daten verwenden für den Aufbau einer Dokumentation. Wir lesen die Metadaten aus und schreiben sie relational weg. Vielleicht speichern wir auch die Daten gleich historisch ab, damit uns Änderungen auffallen bzw. wir diese dokumentiert haben. Darauf lassen sich super Reports bauen. Wozu? Kommt auf die Rolle von uns an. Wir können unsere eigene Arbeit dokumentieren und Veränderungen festhalten, also quasi unseren Arbeitsfortschritt. Aber genauso gut können wir die Arbeit anderer damit kontrollieren bzw. protokollieren wie sich über die Zeit hinweg ein Cube verändert. Das geht dann so in Richtung Projekt – und Qualitätsmanagement.

Kommen wir nun zu der eigentlichen Abfrage von Daten.

Einen Pferdefuss hat die Arbeit mit ADOMD.NET, denn Ihr müsst MDX für die Queries nutzen. Sofern Ihr bisher MDX aus dem Weg gegangen seit oder es einfach keinen Grund gab für Euch sich damit zu beschäftigen, nun ja, jetzt ist der Moment gekommen in dem MDX in Euer Leben tritt!

AdomdConnection mdcon = new AdomdConnection(tbConString.Text);

mdcon.Open();
AdomdCommand cmd = mdcon.CreateCommand();
cmd.CommandText = strMDXQuery;
CellSet cs = cmd.ExecuteCellSet();

Wir machen was Nützliches mit dem CellSet…

mdcon.Close();

Und schon haben wir unser Resultset als CellSet im Objekt “cs” vorliegen. Hier kommen so die wohl wichtigsten Zeilen mit denen Ihr ein CellSet zerlegen könnt. Mit den beiden folgenden Zeilen bekommt Ihr die Große des Resultsets zurück.

int x = cs.Axes[0].Positions.Count; // Anzahl Spalten

int y = cs.Axes[1].Positions.Count; // Anzahl Zeilen

So, nun wollen wir die Spalten- und Zeilenbeschriftungen auslesen. Für die Darstellung hat das CellSet die Eigenschaft “Caption” dabei und für Abfragen haben wir den UniqueName.

cs.Axes[0].Positions[xi].Members[0].Caption;

cs.Axes[0].Positions[xi].Members[0].UniqueName;

Ach ja, der eigentliche Wert ist ja auch noch da. Also nichts wie ran.

Value = cs[xi,yi].Value.ToString();

Das Ganze hier und noch viel, viel mehr schauen wir uns übrigens während des zweiten PASS Camp 2010 gemeinsam im Detail an.

Mittwoch, 14. Juli 2010

Warum sollten Tabellenhinweise (table hints) vermieden werden?

Immer wieder sehe ich bei der Analyse von Anwendungen, dass mit sogenannten Tabellenhinweisen (table hints) gearbeitet wird. Meist ist die Begründung, dass der “doofe” SQL Server einfach nicht verstanden hat, dass es doch extra einen nicht-gruppierten Index für die Abfrage gibt. Den hat der doch einfach ignoriert! Und von anderen Entwicklungsumgebungen sei man ja schließlich auch gewöhnt die Verwendung von Indizes explizit anzugeben. Wo ist also das Problem?

Nun ja, das Problem ist, dass die Erfahrung gezeigt hat, dass wenn soweit alles in Ordnung ist, der SQL Server ziemlich genau weiß, was er da so macht bei der Aufbereitung von Abfragen. Und wenn er einen Index nicht nutzen möchte, dann hat er in der Regel einen verdammt guten Grund dafür. Dummerweise wird dieser nicht angeben.

Der häufigste Grund dafür, dass der SQL Server einen Index nicht verwendet, ist die Vermeidung von unnötiger Arbeit. Der SQL Server kann nämlich auf Basis der sogenannten Statistiken ziemlich genau abschätzen wie viele Datensätze er berühren muss für die Verarbeitung einer Abfrage. Daher ist es extrem wichtig, dass die Statistiken aktuell sind! Also nicht einfach in den Optionen für eine Datenbank deaktivieren, wenn Ihr nicht noch zumindest einen entsprechenden Job dafür aufgesetzt habt! Ist wirklich wichtig!

Was meine ich nun mit unnötiger Arbeit? Dazu ein einfaches Beispiel.

Wir nehmen als Datenbank mal nicht AdventureWorks, sondern die neue Contoso BI. Da ist ein klein wenig mehr Futter drin. Die Tabelle FactOnlineSales bringt es zumindest auf 12.627.608 Datensätze. Das ist zwar noch nicht wirklich viel, aber für unser Beispiel reichst auf jeden Fall. Auf “statistics io on” bin ich schon mal kurz eingegangen. Damit sind die IO-Operationen gemeint und nicht die Statistiken in den Indizes!

set statistics io on;

select SUM(SalesAmount)
from FactOnlineSales
where DateKey between '31.12.2007' and '01.01.2009';

Als Wert für die “logische Lesevorgänge” bekomme ich 46648. Den gilt es zu nun minimieren. Die Tabelle hat zurzeit nur einen gruppierten Index. Der scheint zwar auch alles andere als optimal gewählt zu sein, aber das ein anderes Thema. Im Ausführungsplan sehen wir, dass ein “Clustered Index Scan” durchgeführt wird.

Nun fügen wir einen nicht-gruppierten Index hinzu für DateKey. Das macht doch Sinn, schließlich suchen wir ja nach DateKey.

Und nun? Wir führen die Query nochmal aus und bekommen wieder ein ähnliches Ergebnis. Im Ausführungsplan ist immer noch ein “Clustered Index Scan”. Hier ist nun der Moment, wo der eine oder andere schwach wird und dem SQL Server nun den Index aufzwingen will.

select SUM(SalesAmount)
from FactOnlineSales with (index(ix_datekey)) 
where DateKey between '31.12.2007' and '01.01.2009';

So, jetzt aber! Im Ausführungsplan sehen wir nun endlich, dass der Index verwendet wird. Und was kommt raus? 11186313 logische Lesevorgänge!!! Ist mein SQL Server kaputt? :-)

Natürlich nicht! Wir sehen jetzt nur, warum der SQL Server den Index bisher vermieden hat. Der Index war nämlich alles andere als optimal für diese Abfrage. Zwar nutzt der SQL Server nun den Index, aber der eigentliche Wert, der SalesAmount, steckt ja noch im gruppierten Index. Daher fliegt unsere Abfrage nun immer eine schöne Schleife zwischen nicht-gruppierten und gruppierten Index. Das kostet uns so richtig Leseoperationen und damit Zeit. Wir nennen sowas eine Schlüsselsuche (bookmark lookup).

image

Das Coole daran ist, dass hat der SQL Server aufgrund der Statistiken kommen sehen und daher entschieden, dass ein simpler Scan auf dem gruppierten Index am Ende günstiger wird. Daher mein Hinweis, dass der SQL Server schon ziemlich genau weiß, warum er einen Index mal nimmt und mal nicht. Ehrlich!

Und unsere Query? Haben wir denn nun keine Chance diese mit einem Index zu unterstützen? Aber sicher doch!

Wir nehmen wieder unseren nicht-gruppierten Index und fügen als eingeschlossene Spalte den SalesAmount hinzu. Und los. Jetzt bekomme wir nur noch 10512 logische Lesevorgänge. Und ganz wichtig, im Ausführungsplan sehen wir nur noch einen Index Seek auf unserem nicht-gruppierten Index.

image

Da der SQL Server nun alle notwenigen Spalten in unserem Index findet, gibt es keine Notwendigkeit mehr für den Ausflug in den gruppierten Index. Das ist bewusst eingesetzt Redundanz, wobei wir um die Pflege nicht kümmern müssen. Ach ja, und wir brauchten dafür natürlich keinen Tabellenhinweis!

Indizes mit eingeschlossenen Spalten sind zwar äußerst nützlich, aber bitte übertreibt jetzt nicht die Verwendung! Die Pflege kostet im Hintergrund immer Zeit beim Schreiben.

Und letzte Worte zu Tabellenhinweisen: Ja, es gibt ganz, ganz wenige Sonderfälle in denen die Verwendung Sinn macht. Aber glaubt mir, die sind wirklich selten!!!

Montag, 12. Juli 2010

Reifegradmodelle im Projektmanagement

Mich erreichte die Nachfrage, was ich denn mit Reifegradmodellen im Projektmanagement meinen würde. In einem Post hatte ich den Begriff kurz fallengelassen als Hinweis auf einen möglichen Projektverlauf im Rahmen eines Performance Tunings.

Oft beschreiben Reifegradmodelle themenbezogen die möglichen Entwicklungsschritte einer ganzen Organisation. Es gibt dabei Modelle für ganz unterschiedliche Herausforderungen: IT-Infrastruktur & Security, Business Intelligence, Projektmanagement usw. Dabei wird in der Regel als Orientierung festgelegt, wo die Organisation im Referenzmodell gerade steht, was die nächste Stufe ist und durch welche Merkmale diese zu erreichen wäre. Das Reifegradmodell fasst dabei durch sein “Modell” die einzelnen “Reifegrade” der notwendigen Prozesse innerhalb der Organisation zusammen.

Es geht mir nun darum, wie große, unhandliche und komplexere Projekte “eingefangen” werden können und damit das Risiko eines Scheiterns bereits während der Planung minimiert werden kann.

Im Rahmen von Projekten werden häufig eine ganze Reihe von Prozessen bzw. Funktionalitäten implementiert oder auch erweitert. Während der Planung von Projekten gilt es für diese Implementierungen bzw. Änderungen einen Entwurf zu erstellen, welcher beschreibt wann was wie umgesetzt wird. Die Praxis zeigt, dass dieser Vorgang häufig damit endet, dass der Planer vom Hundertsten ins Tausendste kommt. Dieser Umstand kommt häufig daher, dass nicht immer klar ist, wie weit eine Implementierung in dem jeweiligen Teilprojekt gehen soll bzw. muss! So entstehen oft große undhandliche Projekte, welche dann leider auch meist zum Scheitern verurteilt sind.

Schauen wir uns mal ein plakatives Beispiel an. Es soll ein komplettes Data Warehouse erstellt werden. Dieses Vorhaben als ein einziges großes Projekt abzubilden ist nicht unüblich. Das Scheitern solcher Projekte leider auch nicht.

Erfolgsversprechender ist es die für den Betrieb eines Data Warehouse notwendigen Prozesse in einem so genannten Implementierungsprojekt ans Laufen zu bringen. Das Projekt ist erfolgreich, wenn die Prozesse “leben” und funktionieren. Änderungen und Erweiterungen am DWH sind dann meist keine Projekte mehr, sondern fallen in die Kategorie Change Request. Damit befinden wir uns dann mitten im Change Management des Life-Cycle-Managements eines DWHs.

Trotz alledem wird es bei größeren Erweiterungen oder gar neuen Funktionen wieder die Notwendigkeit von Projekten geben. Was für Erweiterungen können das sein? Dafür ist das Thema Daten Qualität ein schönes Beispiel. Wie weit wollen oder müssen wir im Implementierungsprojekt beim Thema Daten Qualität im Data Warehouse gehen? Wäre es nicht sinnvoll für den Prozess DQ eine Reihe von Reifegraden zu definieren, mit denen wir die Entwicklungsstände dieses Teilprozesses dokumentieren und planen können?

Wenn wir bei dem Data Warehouse bleiben, dann gibt es zum Beispiel auch das Thema nachhaltige Protokollierung des Zugriffs auf Daten (kurz Audit). Ist in vielen Umgebungen ein sehr wichtiges Thema, nur wie weit soll bereits im Implementierungsprojekt dieser Prozess vollständig abgebildet werden? Wieder kann hier eine Reihe von Reifegraden die Planung vereinfachen, da diese die künftige Entwicklungsschritte der Funktionalität vorbereiten und klar voneinander abgrenzen.

Fassen wir nun die vorgesehenen Reifegrade der geplanten Prozesse und Funktionalitäten zusammen, so erhalten wir ein Referenzmodell für die angestrebte “komplette” Lösung, welche wir schrittweise erreichen können. Die Reifegrade sind damit auch die Grundlage für die Planung der Teilprojekte, welche wieder ganz klassisch im Projektmanagement stattfinden kann. Hier kommen sich unser Reifegradmodell und er Begriff Strategie sehr nahe.

Unterstützt kann diese Vorgehensweise dadurch werden, dass wir für unsere diversen technischen Objekte in unserer Lösung ein Repository nutzen und in diesem u. a. den jeweiligen Entwicklungsstand festhalten und auch gleich die weitere Planung daran binden. Wichtig ist mir hier die Abgrenzung zur klassischen separaten Dokumentation. Diese hat zwar ihre Daseinsberechtigung, aber hat in der Regel keine direkte Bindung mit den Objekten in der Lösung. Objekte in einem solchen Repository können zum Beispiel SSIS Pakete, DQ Regeln, Inhalte von Reports (RDL), Dimensionen und Fakten eines Cubes und / oder eines Data Warehouses sein.

Die Kombination aus einem Reifegradmodell für die angestrebte Lösung und Repository ist eine wertvolle Unterstützung für das Projektmanagement, da es sowohl die Planung als auch die Kommunikation im Teilprojekt vereinfacht. Dieses Vorgehensmodell lässt sich leicht auf andere komplexere IT-Projekte ausdehnen. SharePoint Projekte sind zum Beispiel ebenso prädestiniert für diese Vorgehensweise.

Donnerstag, 8. Juli 2010

Size matters! Woher bekomme ich größere Testdatenbanken?

Häufig bekomme ich zu hören, dass die Adventure Works Datenbank ja zu klein sei, um mal wirklich Performance Tuning Szenarios im stillen Kämmerlein zu testen.

Ok, zuerst natürlich die Anmerkung, dass wohl die wenigsten in besagtem Kämmerlein auch eine entsprechende Hardware haben, um “echte” Erlebnisse zu haben und damit Erfahrung zu sammeln. Sowas bekommt Ihr nur im Feld!

Aber gut, ich gebe gern zu, dass die Adventure Works Umgebung tatsächlich ein wenig schlank ist. Daher hier ein paar Tipps wie Ihr an wesentlich mehr Daten kommen könnt!

Erst mal die Frage an den Hersteller Microsoft, was dieser uns so bietet? Und siehe da, es gibt eine wesentlich größere Datenbank:

Microsoft Contoso BI-Demodatensatz für die Einzelhandelsbranche

Woher kommen die denn nun? Das ist u. a. das Futter für PowerPivot Demos! :-)

Hey, wer sagt da jetzt, dass ihm der Download zu groß ist? Ihr wolltet doch mehr…

Ach so, es sollen mehr Datensätze aus dem Nix entstehen und zwar genau jetzt! Auch da kann geholfen werden. Es gibt zwar diverse Skripte dafür im Netz, aber die haben wir natürlich nie dann zur Hand, wenn wir diese brauchen. Müssen wir also kurz lernen, wie wir das quasi aus dem Kopf immer wieder schaffen werden.

Es gibt zwar Ansätze mit Schleifen und einzelnen Inserts. Wir wollen das aber mal in einer Query versuchen. Vielleicht wird es auch noch eine Sub-Query.

Rettung naht in Form der master.sys.columns. Wie das jetzt? Nun ja, die ist hoffentlich immer da und hat einige Zeilen. Daher merken:

master.sys.columns

Und? Folgende Query wirft doch gerade mal 659 Zeilen bei mir und da ist auch gar nix nützliches drin!

select COUNT(*)
from master.sys.columns a;

Natürlich sind das jetzt noch zu wenig Zeilen, aber jetzt kommt der zweite Teil zum merken:

cross join

Was? Wozu sollen das denn jetzt taugen? Ok, schaut Euch die folgende Query an:

select COUNT(*) as Anzahl
from master.sys.columns a
cross join master.sys.columns b

Das macht schon mal 434.281 Zeilen bei mir. Und die hier?

select COUNT(*) as Anzahl
from master.sys.columns a
cross join master.sys.columns b
cross join master.sys.columns c

Wow, da kommen nun 286.191.179 Zeilen bei rum. Nur was haben wir damit gewonnen? Den Zähler an den wir unsere Testdaten anhängen können! Nun brauchen wir nur noch kurz eine laufende Nummer. Moment mal, es gibt doch zum Beispiel ROW_NUMBER(). Aber die ist ja vorgesehen für die Over Klausel, aber das lässt sich relativ leicht “aushebeln”. Und mit dem TOP lässt dich die Anzahl genau steuern.

select top 500000
ROW_NUMBER() over( order by a.is_filestream  ) as nummer
from master.sys.columns a
cross join master.sys.columns b
cross join master.sys.columns c

 

ROW_NUMBER() over( order by a.is_filestream  ) as nummer

Hey, jetzt nicht schlapp machen beim merken! Ok? Führen wir mal alles zusammen. Mit der folgenden Query haben wir schon 50.000.000 Zeilen. Mit den Spalten Produkt, Lieferant und Umsatz könnt Ihr wenig spielen.

select
nummer,
N'Produkt'+ CAST( nummer % 100 as nvarchar(15)) as Produkt,
N'Lieferant' + CAST (nummer % 10 as nvarchar(15)) as Lieferant,
CAST(5000+ (nummer % 5000) as money) as Umsatz
into t9
from
(
  select top 50000000
  ROW_NUMBER() over( order by a.is_filestream  ) as nummer
  from master.sys.columns a
  cross join master.sys.columns b
  cross join master.sys.columns c
) x

So, ich hoffe, dass nun der eine oder andere was für sich mitnehmen konnte bzw. nun eine Idee hat, wie aus dem Nix Testdaten entstehen können. Und nun viel Spaß beim Indizieren und passt mir bitte auf, dass Ihr nun nicht produktive Server mit Testdaten flutet!

Mittwoch, 7. Juli 2010

Indizes? Haben wir doch schon längst! Lassen Sie uns nun bitte zu den echten Tuning Methoden kommen.

Das ist häufig eine spontane Antwort, welche ich auf den Vorschlag bekomme eine Anwendung mittel Indizes zu optimieren, wenn so das Gröbste aus dem Weg ist.

Sowohl Entwickler als auch Administratoren scheint durchaus bewusst zu sein, dass Indizes und das Laufzeitverhalten einer Datenbank durchaus zusammenhängen (können). Aber ihnen scheint oft nicht die mögliche Tiefe des Themas bewusst zu sein, denn sie suchen häufig den wahren heiligen Gral der Performance Optimierung. Indizes haben sie schon, also muss es da draußen noch etwas anderes geben. Ist doch logisch, oder? :-)

Mit diesem Post möchte ich den Leser, welcher sich angesprochen fühlt, dafür sensibilisieren, dass Indizierung viel mehr zu bieten hat, als er sich für den Moment vielleichtnvorstellen kann.

Es hat sicherlich auch etwas damit zu tun, welche Rolle Indizes früher gespielt haben. Unter dbase III+ waren Indizes zum Beispiel eigentlich nichts anderes als nur zusätzliche Sortierreihenfolgen, welche auch für eine schnellere Suche genutzt werden konnten. Wer kann sich noch an FIND und SEEK erinnern? :-)

In einem modernen relationalem Datenbank System wie dem SQL Server schaust ein wenig anders aus. Daten liegen meist bereits in Form eines B-Trees vors. Was ist ein B-Tree? –> http://de.wikipedia.org/wiki/B-Baum. Und ja, es gibt wohl tatsächlich Fälle, in denen ein sogenannter Stapel (Heap) Sinn machen würde. Sind aber recht selten und legen wir gedanklich mal zur Seite.

Es gibt pro Tabelle daher einen gruppierten (clustered index, CI), der entspricht auch gleichzeitigt DEN DATEN, und meist eine ganze Reihe von nicht gruppierten Indizes (non-clustered index, NCI). Damit scheint das Thema bereits durch zu sein. Es werden die Spalten mit einem Index hinterlegt, nach denen später “gesucht” werden soll. Damit ist meist die Verwendung mittels WHERE gemeint. Alles Mögliche wurde getan. Dabei fängt der Spaß doch jetzt erst richtig an!

Ein Index ist nicht nur ein Weg um Daten schneller zu finden, häufig ist der Index gleichzusetzen mit den Daten. Nun ja, oder zumindest einem Teil der Daten. Am Besten den Teil der Daten, den ich gerade benötige. Ich hatte ja in einem Post darauf hingewiesen, dass die Vermeidung von IO Operationen das Ziel sein sollte. Mir bringen die schönsten Indizes nichts, wenn sich der SQL Server durch Unmengen Datenbankseiten wühlen muss.

Worauf kann geachtet werden? Und, nein, ich möchte hier keine Kurzanleitung für die effektive Indizierung liefern, sondern nur mal kurz anreißen wie weit das Thema gehen kann. by the way, viel Sinnvolles über Indizierung steht in der SQL Server Hilfe (BooksOnline)!

Fangen wir mal mit was Einfachem an. Die Wahl des richtigen Clustered Index Keys ist bereits entscheidend. Viele schauen dann auf und fragen: Welche Wahl? Ist das nicht immer der Primäre Schlüssel? Ja, per default ist er das meist, aber das muss nicht so sein und macht auch oft keinen Sinn! Denn die Reihenfolge in welcher meine Daten vorliegen ist gerade bei sogenannten Bereichsabfragen von Vorteil, aber wer fragt schon ständig von Kundennummer 4711 bis 5289 ab? Eher kommt wohl sowas vor wie “Alle Kunden in Köln” oder “Alle Umsätze in 2009”. Also ist hier die erste Chance zum Mitmachen.

Kommen wir mal zu den nicht-gruppierten Indizes. Das ist doch “nur” ein Verweis auf die Daten im gruppierten Index, oder? Ja, aber das muss nicht so sein! Weil wenn der SQL Server im NCI alles findet, was er für die Beantwortung einer Abfrage benötigt, dann ist der so clever und lässt den CI doch tatsächlich links liegen und bedient sich aus dem NCI. Nun kommt natürlich sofort die Frage: “Aber was soll eine Abfrage in der nur Ort vorkommt, weil mehr steht doch nicht im Index?!? Ich brauche doch auch noch den Umsatz!” Und da fängt der Zauber an, denn es hält uns nichts davon ab, dass wir den Umsatz einfach mit in den Index legen. Ab SQL Server 2005 gibt es für sogenannte abdeckende Indizes sogar ein extra GUI Erlebnis (Eingeschlossene Spalten). Wir erzeugen bewusst Redundanz. Das ist immer ein Moment, da zeigt sich, ob der Berater den starrenden Augen des Kunden gewachsen ist. “Was, bewusst Redundanz herbeiführen? So ein Blödsinn! Und wie wird das gepflegt?” Das Schöne ist, dass sich das selbst pflegt. Ok, die Pflege kostet extra Zeit beim Schreiben, aber wenn der lesende Zugriff unterstützt werden soll, dann macht sowas durchaus Sinn. Die meisten Anwendungen sind eh viel mehr am Lesen als am Schreiben. Passt schon, aber besser vorher mal prüfen und nicht übertreiben.

Die Reihenfolge der Spalten in einem Index spielt auch noch eine entscheidende Rolle! Hier kommt das Thema Selektivität zum tragen.

Dann kann doch tatsächlich ein View wiederum in Form eines gruppierten Index persistiert werden. Damit lässt sich häufig auch eine Menge machen. Und natürlich lassen sich auf diesem CI auch wieder NCIs anlegen.

NCI können gefiltert werden, es  wird nur ein Teil der Zeilen im CI vorgehalten. Spart wiederum Zeit bei der Pflege und Platz!

Und pro Partition können auch wieder unterschiedliche Indizes verwendet werden. Viele Indizes nur für die aktuellen Daten und für das historische Zeug nur das nötigste.

Um wem das noch nicht reicht, dem der Hinweis, dass selbst die Wahl auf welchen Festplatten welche Indizes liegen, oft zur Performance des Systems beitragen kann.

Und so weiter… wir wollten das ja auch nur mal kurz anreißen!

Das Thema Performance Tuning kann äußerst tief in die Interna des SQL Servers gehen. Häufig kommt die Frage nach dem “richtigen” Tuning. Die ist man mal meist gar nicht so einfach zu beantworten. Wie viel Tuning darf es denn sein? Am Anfang eines Performance Tuning Projektes kann meist mit einfachen Mitteln eine ganze Menge Performance rausgeholt werden. Dann geht es in die Detailanalyse, um den vielen kleinen aber lästigen Abfragen auf die Spur zu kommen. Pflicht ist dann noch ein Blick auf die Auswirkungen von parallelen Transaktionen, damit kann der Berater auch noch viel Spaß haben.

Performance Tuning ist immer ein laufender Prozess, kein einmaliges Projekt! Um trotzdem allen Maßnahmen, Möglichkeiten und Abhängigkeiten Herr zu werden empfehle ich die Verwendung eines Reifegradmodells, in welchem festgelegt wird wie viel Zeit für welches Thema wann aufgebracht werden soll. Sonst kann das Ganze leider ziemlich schnell ins Endlose ausufern und keine messbaren Erfolge bringen.

Der Lernprozess beim Performance Tuning ist wohl nie vorbei, da es immer noch eine tiefere Ebene gibt im SQL Server. Damit man aber mit seinem SQL Server überhaupt vom Fleck kommt, sollte ganz ordentlich beim Fundament angefangen werden und dann darf es nach und nach komplizierter werden. Viel es sein muss, nun ja, da empfehle ich von Koch “Die 80/20 Regel” Basiswerk zu.

Dienstag, 6. Juli 2010

GROUP BY mit ROLLUP

Anbei ein Thema, welches mir neulich erst wieder aufs Flipchart kam. Wie errechne ich in einem gruppierten Resultset möglichst einfach Zwischensummen?

Nun ja, da sieht der Berater draußen schon recht spannende Konzepte in denen diverse UNION ALL eine entscheidende Rolle spielen! Dabei gibt es dafür in der Abfragesprache SQL extra ein Argument für diese Herausforderung.

Kurze Anmerkung dazu, wenn Ihr zum Beispiel mit den Reporting Services unterwegs seit, dann habt Ihr diese Fragestellung sehr wahrscheinlich gar nicht, da dort Zwischensummen durch die Gruppierungsfunktion im Report abgebildet wird. So, der Rest von uns kann nun weiterlesen.

Ach ja, und das betrifft jetzt nicht nur den SQL Server, wir bewegen uns da durchaus im ANSI Standard! :-)

Wir nehmen unsere gute alte AdventureWorksDW2008. Als Beispiel wollen wir uns den Umsatz aus der FactInternetSales aufgeteilt nach Attributen aus der Dimension Product darstellen lassen.

select EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, SalesAmount
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey

image

Ok, so im Detail muss es ja man mal gar nicht sein, also bilden wir Gruppen mittels GROUP BY.

select EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, sum(SalesAmount) as Umsatz, COUNT(*) as Anzahl
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey
group by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName
order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName

image

Besser! Aber jetzt fragt doch tatsächlich jemand nach Zwischensummen für Category und Subcategory! Wie bringen wir die nun in das Resultset? Dafür gibt es die GROUP BY Erweiterung ROLLUP!

Und hier jetzt ein Beispiel mit GROUP BY ROLLUP.

select EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, sum(SalesAmount) as Umsatz, COUNT(*) as Anzahl
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey
group by rollup( EnglishProductCategoryName , EnglishProductSubcategoryName, EnglishProductName)
order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName

image

Was ist passiert? Wir sehen in der ersten Zeile nun die Umsätze gesamt über alle drei Attribute. Das ist noch keine Kunst, dafür hätten wir kein GROUP BY benötigt, dass hätte auch ein einfaches SUM geschafft. Aber nun bekommen wir auch die Summen je Subcategory und Category dargestellt. Das NULL zeigt jeweils an, dass es sich um eine Summenzeile für das jeweilige Attribut handelt.

Und wer jetzt sagt, dass er das schon alles kennt aber eine andere Syntax dafür verwendet. Der sei gewarnt! Denn die Notation mit “with rollup” ist nicht ANSI-konform und läuft daher aus! Fällt Euch also vielleicht bei einem späteren Update auf die Füße!

Wer sich nun noch an den NULLs stört und diese nicht in seiner Anwendung nutzen möchte, der kann diese natürlich auch direkt in der Abfrage in was Sprechendes ändern .

select
isnull(EnglishProductCategoryName,'Summe') as Produktkategorie,
isnull(EnglishProductSubcategoryName, 'Summe') as Produktunterkategorie,
isnull(EnglishProductName,'Summe') as Produkt,
sum(SalesAmount) as Umsatz, COUNT(*) as Anzahl
from FactInternetSales fis
left join DimProduct dp on fis.ProductKey = dp.ProductKey
left join DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
left join DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey
group by  rollup( EnglishProductCategoryName , EnglishProductSubcategoryName, EnglishProductName)
order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName

image

 

Und noch was. Da AdventureWorksDW2008 ja die Basis des Microsoft Beispiel-Cubes für die Analysis Services ist, können wir die Ergebnisse unserer Query auch mittels Cube überprüfen bzw. den Cube mit unserer Query gegenprüfen. Hey, cool, geht auf! :-)

 

image 

Für viele Anwendungsfälle wäre es sehr wahrscheinlich einfacher, wenn wir direkt aus unserer Applikation auf einen OLAP Cube zugreifen würden. Das wird aber noch seltener genutzt als die ROLLUP Erweiterung, dabei ist die Anwendung fast einfacher. Das bringt mich zu ADOMD.NET, aber das beschreibe ich ein anderes mal im Detail.

Montag, 5. Juli 2010

Wie messe ich eigentlich den Erfolg der Optimierung einer SQL Abfrage?

Hier ein Thema, welches mir immer wieder in Coachings von Einsteigern und Juniorberatern begegnet. Daher speziell für diese Zielgruppe ein paar Grundlagen.

Wie messe ich eigentlich, ob dank einer Maßnahme eine Query “schneller” geworden ist?

Der eine oder andere mag denken, dass das doch eine überflüssige Frage ist. Die Optimierung war erfolgreich, wenn eine Abfrage schneller fertig ist. Wo ist also das Problem? Und schließlich hat doch das Management Studio in der rechten untern Ecke extra dafür einen Timer. Und im Profiler kann ich mir doch auch die Dauer einer Abfrage anschauen. Damit sollte das Thema doch eigentlich durch sein, oder? Und bei vielen Queries steht da unten doch eh nur 00:00:00, die können also doch gar nicht weh tun.

Nun ja, ganz so einfach ist es dann doch nicht. Die Zeit, welche eine Query benötigt, ist immer relativ und kann von vielem abhängig sein. Die wesentlich entscheidendere Größe beim Tuning ist die Anzahl der für die Beantwortung notwendigen Datenbankseiten! Und dabei ist es fast egal, ob die Seiten gerade warm und trocken im Cache liegen oder gar erst vom Plattensystem gelesen werden müssen. Seite bleibt Seite. Daher sollte ein Ziel von Optimierungsmaßnahmen immer sein, dass die Anzahl der für ein Resultset notwendigen Datenbankseiten minimiert wird! Auch bei Abfragen, welche gefühlt flott laufen, kann es Sinn machen diese entsprechend zu optimieren, wenn diese auf einem Server vielleicht viele tausend Mal am Tag ausgeführt wird.

Ok, nur wie kommen wir nun an diese Anzahl der notwendigen Seiten und wie reduzieren wir diese?

Für den Einstieg in das Thema ist es nicht immer notwendig gleich den SQL Profiler mitlaufen zu lassen. Auch Querypläne müssen nicht immer gleich zerlegt werden, auch wenn das einen bereits deutlich nach vorne bringen würde.

Mit folgender Anweisung schalten wir die Darstellung einer Statistik ein, welche uns die Anzahl der Datenbankenseiten pro verwendeter Tabelle zeigen wird:

set statistics io on;

Und nun noch eine kleine Abfrage für die gute alte AdventureWorksDW2008:

select SUM(SalesAmount), COUNT(*)
from dbo.FactInternetSales fis
left join dbo.DimCustomer dc on dc.CustomerKey = fis.CustomerKey
where dc.YearlyIncome > 50000;

Und schon sehen wir im Management Studio unter Meldungen folgendes:

(1 Zeile(n) betroffen)
Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
FactInternetSales-Tabelle. Scananzahl 1, logische Lesevorgänge 1030, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
DimCustomer-Tabelle. Scananzahl 1, logische Lesevorgänge 978, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Das “Ziel” habe ich mal markiert. Diese Werte gilt es im Rahmen einer Optimierung zu reduzieren. Denn je weniger Seiten benötigt werden, um so schneller wird die Abfrage und, kleiner Nebeneffekt, um so geringer sind Probleme durch Sperren. Performanceschwierigkeiten kann so viele Gründe haben, aber das würde hier zu weit gehen.

Aber wie kommen wir nun zu einer Optimierung? Es gibt zwei grundsätzliche Wege:

  • durchdachtere SQL Abfragen (weites Feld, würde den Rahmen dessen hier für den Moment sprengen)
  • bessere Indizierung (wird von viel zu vielen unterschätzt und von einigen tatsächlich auch überschätzt)

Am Ende geht es um weniger Seiten oder wie der Profi sagt: Weniger IO Operationen.

Wir versuchen es mal mit der Erzeugung von zwei Indizes, um die Lesevorgänge deutlich reduzieren. Für Details einfach mal einen Blick in die BooksOnline des SQL Server werfen.

CREATE NONCLUSTERED INDEX [ix_DimCustomer_YearlyIncome] ON [dbo].[DimCustomer]
(
    [CustomerKey] ASC
)
INCLUDE ( [YearlyIncome]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ix_FactInternetSales_CustomerKey_SalesAmount] ON [dbo].[FactInternetSales]
(
    [CustomerKey] ASC
)
INCLUDE ( [SalesAmount]) ON [PRIMARY]

So, identische Query nochmal starten und schon sehen wir, dass unsere Bemühungen erste Erfolge zeigen. Ach ja, natürlich bleibt das Ergebnis der SQL Abfrage identisch!

(1 Zeile(n) betroffen)
FactInternetSales-Tabelle. Scananzahl 1, logische Lesevorgänge 257, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
DimCustomer-Tabelle. Scananzahl 1, logische Lesevorgänge 44, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Was passiert da? Kurzgefasst kann gesagt werden, dass der Queryoptimizer nun nur noch unsere beiden Indizes verwendet, welche genau die Spalten bereitstellen, die unsere Query benötigt. Und damit wäre normalerweise noch nicht Schluss mit der Optimierung, aber für den Moment soll das reichen, um das Konzept zu verdeutlichen wie wir den Erfolg einer Maßnahme messen können. Denn darum ging es mir hier.

Also, nochmal wiederholt: Erfolgreiche Optimierung heißt u. a. Reduzierung von Seitenzugriffen!

Und messen könnt Ihr das u. a. mittels set statistics io on.