OLAP und Analytische Fähigkeiten in SQL Server 2005
Werbung
Business Intelligence in SQL Server 2005 Steffen Krause Microsoft Deutschland GmbH [email protected] http://blogs.technet.com/steffenk Agenda 1. Überblick SQL 2005 2. Analysis Services Überblick 3. Analysis Services: Unified Dimensional Model SQL Server Plattform OLAP Marktanteile Quelle: The OLAP Report Beurteilung durch Gartner ”Microsoft will emerge as a business intelligence (BI) market leader by 2005 (0.7 probability).” Gartner Group, April 2004 Als BI Plattform Skalierbare, umfassende Lösung • Alle Aspekte einer BI Lösung • Integriert mit Visual Studio für einfache Entwicklung Echtzeitwerkzeuge für die Entscheidungsunterstützung • Erweiterbare Reporting-Plattform • Skalierbar und Zuverlässig für 24*7 Betrieb BI für das ganze Unternehmen • Unterstützt heterogene Datenquellen • Enterprise-Class ETL (Integration Services) BI Development Studio • • Eine vollständige integrierte Umgebung zur Erstellung von BI Applikationen Ein Tool, mehrere Technologien: – Relational, OLAP, DM, DTS, Reporting, Code, Webseiten… • Enterprise SoftwareEntwicklungsumgebung: – Integriert in Visual Studio.NET – Team-Development, Source Control, Versionierung … • Lifetime-Support für die Entwicklung – Entwicklung, Test, Deployment, Änderung, Test… Integration Services Umfassende ETL-Fähigkeiten • Unternehmensweite ETL-Platform – Hohe Performance – Hohe Skalierbarkeit – Sicher und zuverlässig • “Best in Class”-Usability – Umfassende Entwicklungsumgebung – Source Control – Visuelles Debugging von Kontrollfluß und Daten – Umfassende Bibliothek von Transformationen mitgeliefert • Umfassend erweiterbar – Benutzerdefinierte Tasks – Benutzerdefinierte Aufzählungen – Benutzerdeninierte Transformationen – Benutzerdefinierte Datenquellen Reporting Services • Eingeführt mit SQL Server 2000 – Offene, erweiterbare Unternehmens-Reporting-Lösung – Reporterstellung, Management, Verteilung – Office System-Integration – VS.NET Entwicklungsumgebung • SQL Server 2005 Erweiterungen – Integration mit AS, Integration Services, Managementtools – Erweiterungen für Entwickler – Verbesserte Interaktivität – Umfassendes EndbenutzerReporting Analysis Services • Unified Dimensional Model – Integration von OLAP und Relationalen Sichten • Proaktives Caching – Bringt das Beste von MOLAP auf ROLAP • Fortgeschrittene Business Intelligence – KPIs, MDX Scripte, Übersetzungen, Währungen • Web Services – Natives XML/A Data Mining Fokus • Vollständige Analysemöglichkeiten – Populäre Data Mining Algorithmen (5 neu) – Verbesserte Umgebung für Erstellung, Bearbeitung und Ansicht • Eingebettetes Data Mining – Eingebettetes Data Mining in Line-of-Business (LOB)-Applikationen – Komplettes, auf SQL basierendes API – Nativer XML/A Support • Integrierte Lösung – Tiefe Integration mit Relationaler, OLAP, DTS und ReportingTechnologie – Umfassende SQL Server BI Plattform • Zusammenarbeit mit ISVs – Fokus auf Verbreiterung des Marktes – DM Industriestandard basierend auf OLE DB für DM und XML/A Vollständiger Satz von Algorithmen Entscheidungsbäume Clustering Zeitreihen Eingeführt mit SQL Server 2000 Sequence Clustering Assoziation Neuronale Netzwerke Naïve Bayes + Lineare Regression Logistische Regression Agenda 1. Überblick SQL 2005 2. Analysis Services Überblick 3. Analysis Services: Unified Dimensional Model Analysis Services 1 SQL Server Analysis Services Komponenten • • • • • • • • • Datenquellen Datenquellen-Sichten Dimensionen Measures/Measure Groups Cubes Partitionen, Aggregation Kalkulationen/MDX KPIs Client-Zugriff SQL Server Analysis Services Datenquellen • Verbindung zu Datenspeichern • Beliebige OLEDB oder .NET Provider • Oracle, DB2, ISAM, … • Flat Files, Excel, … • Optimiertes SQL • Integrierte Sicherheit oder User/Password SQL Server Analysis Services Data Source Views • Virtuelles Gesamtschema über alle Datenquellen • Ausschnitt aus Schemas der Datenquellen • Erlaubt Integritätsregeln über Datenquellengrenzen • Friendly Names • Berechnete Spalten SQL Server Analysis Services Dimensionen • Logisch zusammengehörige Attribute aus einer oder mehreren Tabellen • Jedes Attribut bildet eine Hierarchie mit 2 Ebenen • Key, Name • Übersetzungen (Titel & Daten) • Können benutzerdefinierte Hierarchien enthalten • Typen: – Standard, Time, Server Time, Account… SQL Server Analysis Services Dimensionen (2) • Parent-Child-Hierarchien: Selbstbezug über Parent Key, unterschiedliche Tiefe • Verarbeitungsattribute: – StorageMode, ProactiveCaching, ProcessingMode… Changing Dimensions Support • Typ 1: trivial • Typ 2: Über Surrogate Key & Gültigkeitsdatum von/bis und Status SQL Server Analysis Services Measures/Measure Groups • • • • Kennzahlen, meist numerisch Pro Fakttabelle eine Measure Group FormatString, DataType, Visible Aggregation – Fully Additive • Sum – Standard • Min, Max, Avg, Count, DistinctCount – Semiadditive (over time) • First, Last, FirstNonEmpty, LastNonEmpty – ByAccount – Custom SQL Server Analysis Services Cubes • Dimensionen • Measure Groups • Dimension Usage pro Measure Group • Drillthrough • Actions • Translations • Perspectives als virtuelle Data Marts SQL Server Analysis Services Nuts Product Customer Chocolate Chips Hotdogs Beer 2004 2003 2002 M Joh An Sve Tom Har ar a na n ry y n Partitionen, Aggregation te a D • Partitionen teilen Cubes in physische Speicherblöcke • Haben unterschiedliche Datenquellen • Partitionsfunktion definiert Teilung • Aggregationstyp (MOLAP, ROLAP, Automatic) pro Partition • Aggregat-Design pro Partition Wie viele Aggregationen? Customer Product Time All Customers Country State City Name All Products Category Brand Item SKU All Time Year Quarter Month Day • 125 mögliche Kombinationen – Je fünf Levels • Bei 10 Dimensionen mit je 5 Ebenen: – 510 = 9,765,625 Kombinationen! (Exponentielles Wachstum!) • Daher können nicht alle berechnet werden • Ziel: Beste Untermenge für die Abfragestruktur der Benutzer SQL Server Analysis Services Kalkulationen/MDX • Berechnungen innerhalb des Cubes • Berechnete Measures • Berechnete Hierarchien • MDX als Standardsprache • Deklarativ SQL Server Analysis Services KPIs • • • • Kennzahlen gegenüber Vergleichs- oder Planwerten Hierarchisch Einfach erfassbar Angepaßt auf jeweilige Ebene SQL Server Analysis Services Clients • Offene Client-Architektur • APIs OLEDB for OLAP und XML/A • Abfragesprache MDX: select NONEMPTYCROSSJOIN( {[Measures].[Reseller Sales Amount],[Measures].[Discount Amount]} , [Date].[Fiscal Year].members) on rows, [Customer].[Country-Region].[Country-Region].members on Columns from [Adventure Works] WHERE [Product].[Category].[Category].&[1] Analysis Services 2 Agenda 1. Überblick SQL 2005 2. Analysis Services Überblick 3. Analysis Services: Unified Dimensional Model Enterprise Business Intelligence Heute Datenquelle Datenmodell Client MOLAP OLAP Browser (1) MOLAP OLAP Browser (2) Data Mart Data Mart Reporting Tool (3) Reporting Tool (2) Data Warehouse (DW) Reporting Tool (1) Enterprise Business Intelligence Heute Datenquelle OLAP vs. Reporting Datenmodell Client MOLAP OLAP Browser (1) MOLAP OLAP Browser (2) Data Mart Reporting Tool (3) Data Mart Reporting Tool (2) DW Duplizierte Daten Reporting Tool (1) Duplizierte Modelle Relationale Berichte vs. OLAP Feature Flexibles Schema Zugriff auf Echtzeitdaten Daten nur einmal vorhanden Einfaches Management Detail-Reporting Hohe Performance Endbenutzerfreundlich Einfache Navigation Umfangreiche Analyse Umfangreiche Semantik Relational OLAP 9 9 9 9 9 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 Das Unified Dimensional Model Das Beste von Relational und OLAP Relationales Reporting OLAP Cubes ¾ Mehrere Fakten-Tabellen ¾ Vollständige Attribute ¾ Anzeige einzelner Transaktionen ¾ Star, Snowflake, 3.NF… ¾ Komplexe Beziehungen ¾ Rekursive Self-Joins ¾ Slowly Changing Dimensions ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ Multidimensionale Navigation Hierarische Präsentation Benutzerfreundliche Namen Mächtige MDX Berechnungen Zentrales KPI-Framework “Aktionen” Mehrsprachigkeit Multiple Perspektiven Partitionen Aggregationen Verteilte Datenquellen Das Unified Dimensional Model Das Beste von Relational und OLAP Relationales Reporting l e d o M l a n o i s n e m i D ¾ Mehrere Fakten-Tabellen ¾ Vollständige Attribute ¾ Anzeige einzelner Transaktionen ¾ Star, Snowflake, 3.NF… ¾ Komplexe Beziehungen ¾ Rekursive Self-Joins ¾ Slowly Changing Dimensions d e i f i n U e Th OLAP Cubes ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ ¾ Multidimensionale Navigation Hierarische Präsentation Benutzerfreundliche Namen Mächtige MDX Berechnungen Zentrales KPI-Framework “Aktionen” Mehrsprachigkeit Multiple Perspektiven Partitionen Aggregationen Verteilte Datenquellen Enterprise Business Intelligence Mit einem UDM Datenquelle Datenmodell MOLAP OLAP Browser (1) MOLAP Data Mart Client OLAP Browser (2) UDM Reporting Tool (2) Data Mart Reporting Tool (1) DW BI Applikationen Enterprise Business Intelligence Mit einem UDM Datenquelle Datenmodell MOLAP OLAP Browser (1) MOLAP Data Mart Client OLAP Browser (2) UDM Reporting Tool (2) Data Mart Reporting Tool (1) DW BI Applikationen Ein einziges dimensionales Modell für alle relationalen und OLAP Berichte Analysis Services Skalierbarer, hochperformanter UDM Server Datenquelle Client MOLAP MOLAP Data Mart UDM Data Mart OLAP Browser (1) XML/A oder ODBO Analysis Services OLAP Browser (2) Reporting Tool (2) Reporting Tool (1) DW BI Applikationen z z Optimiertes SQL für alle wesentlichen RDBMS-Plattformen Hochskalierbarer OLAP-Store z z z OLE DB und XML/A APIs UDM als Web Service nutzbar API unterstützt von allen wesentlichen BI Herstellern Analysis Services Transparentes MOLAP-Caching Datenquelle Client MOLAP MOLAP Data Mart UDM Data Mart DW Cache OLAP Browser (1) XML/A oder ODBO Analysis Services OLAP Browser (2) Reporting Tool (2) Reporting Tool (1) BI Applikationen Nachricht z z z “Proaktives Caching” – Automatische MOLAP Cache-Erstellung und –Verwaltung MOLAP wird transparent–muß nicht mehr gemanaget werden Relationales Reporting erhält MOLAP-Performance Analysis Services Data Marts werden virtualisiert Datenquelle Client Data Mart UDM Data Mart DW z z z Cache OLAP Browser (1) XML/A oder ODBO Analysis Services OLAP Browser (2) Reporting Tool (2) Reporting Tool (1) BI Applikationen UDMs bieten themenorientierte Sichten auf das DW “Perspektiven” erlauben Benutzer/Gruppenspezifische Sichten auf dieselben Daten Hohe Performance durch den proaktiven Cache Analysis Services Optimierte BI-Infrastruktur Datenquelle Client DW UDM Cache z z z OLAP Browser (1) XML/A oder ODBO Analysis Services OLAP Browser (2) Reporting Tool (2) Reporting Tool (1) BI Applikationen Einheitliches logisches Modell für Reporting und OLAP mit exzellenter Performance und Skalierba Ein Datenspeicher garantiert Konsistenz und niedrige Total Cost of Ownership (TCO) Umfassende Unterstützung durch sehr viele Microsoft und Third-Party Tools Analysis Services 3 Backup Teil 3 SQL Server Integration Services Integration Services Architektur Standard transforms Custom transforms Data Destination Adapters Data Source Adapters Package XML Package Loops & Sequences Tasks XML Package Event Handlers Wizards DTS Designer Command Line Entwicklungsumgebung • • • Integration Services Projekte Kontrollfluß und Datenfluß getrennt Kontrollfluß – – – – • • Tasks Schleifen, Sequenzen und Events Variablen und Scopes Vorbedingungen Connections Datenfluß – – – – Quell- und Ziel-Adapter Transformationen Mehrere Quellen mit Joins und Unions Mehrere Ziele mit Splits und Multicast Visuelles Debugging • Debuggen des Kontrollflusses mit Breakpoints – Breakpoints auf Ereignisse – Variablen im “Lokal”-Fenster – Watches und Call Stack • Debug des Datenflusses mit Viewern – Grid Data Viewer – Data Visualizer – Chaining Data Viewers Datenqualität • Datenbereinigung von MS Research – Fuzzy Lookup für Matching – Fuzzy Grouping für Duplikatbeseitigung • Glaubwürdigkeit und Ähnlichkeit – Ähnlichkeit – Berechnung der Nähe – Glaubwürdigkeit – Wahrscheinlichkeit, dass Zuordnung korrekt ist – Hohe Ähnlichkeit muß nicht hohe Glaubwürdigkeit bedeuten • Domänenspezifische Algorithmen von 3rd Parties (z.B. Adressbereinigung) DTS/UDM Integration • Entwurfs-Integration mit Analysis Services – Benutzung von Datenquellen und Data Source Views – Zugriff auf UDM-Metadaten (Entwurf und installiert) aus dem Projekt • Laufzeitintegration mit Analysis Services – UDM prozessen im Kontrollfluß – UDM laden aus dem Datenfluß (ohne relationalen Zwischenspeicher) • Für “komplizierte” Quellen – Flat Files oder Mainframe über ODBC • Für “Wegwerf”-Cubes • Report Server Integration • Web Services Unterstützung Echtzeit-Reporting • In SQL 2005 können die Reporting Services direkt eine SSIS Pipeline konsumieren • Disk Ressourcen <-> Prozessorzeit Applikation Daten ‘OLTP’ Reporting SSIS SQL Server Integration Services Komponenten einer BI-Lösung Cubes Reports Data Marts Quellsysteme Relationales Data Warehouse XML BI = ETL + Relationale DB + OLAP + Reporting + Clients – + Metadata ? – + Data Marts ? – + Data Mining ? Clients Komponenten einer BI-Lösung Quellsysteme • Relational, Flat File, XML,… • Verschiedenste Plattformen Herausforderungen • Optimiert für operatives Geschäft • Sicherer, performanter Zugriff • Kommunikation mit Eigentümern • Unzureichende Dokumentation SQL Server Plattform • Integration Services Datenquellen • Biztalk Server + Adapter, Host Integration Server Komponenten einer BI-Lösung Extract, Transform & Load • Extraktion und Transformation von Daten • Matching zusammengehöriger Daten Herausforderungen • Übersetzung verschiedenster Nummernkreise • Daten-Bereinigung • Fehlende & doppelte Daten • Nachvollziehbarkeit der Datenherkunft & Verarbeitung • Umsetzung, z.B. Währungsrechnung SQL Server Plattform • SQL Server Integration Services Komponenten einer BI-Lösung Relationales Data Warehouse • Einheitliches, denormalisiertes Schema • Alle relevanten Daten über einen langen Zeitraum Herausforderungen • Politik • Datenmenge • Skalierbarkeit • Verfügbarkeit • Backup & Recovery • Tuning SQL Server Plattform • SQL Server (Enterprise/Datacenter Edition) Komponenten einer BI-Lösung Data Marts • Abteilungs/Fachsicht auf Daten • Oft Basis für DW-Projekte Herausforderungen • Wartung • Ease of Use • Integration in Unternehmens-DV SQL Server Plattform • SQL Server Standard Edition Komponenten einer BI-Lösung OLAP • Multidimensionale Sicht auf Daten • Benutzt Wortschatz der Anwender Herausforderungen • Datenmodell • Performance • Zeitnähe • Aufbereitungszeiten • Slowly Changing Dimensions SQL Server Plattform • SQL Server Analysis Services Komponenten einer BI-Lösung Reporting • Berichte aller Art Herausforderungen • Einfache Benutzung vs. Flexibilität • Layout • Formate • Aktualität SQL Server Plattform • SQL Server Reporting Services Komponenten einer BI-Lösung Clients • Web-Clients • Excel-Clients • Analyse-Clients Herausforderungen • Akzeptanz • Einfache Benutzung vs. Flexibilität • Verschiedene Benutzergruppen (Vertrieb vs. Controlling) • Web vs. Excel vs. Spezialclients SQL Server Plattform • XML/A, OLEDB for OLAP • Excel 2003, Office Web Components … Komponenten einer BI-Lösung Data Mining • Forecasts • Versteckte Zusammenhänge (z.B. Basket Analyse) Herausforderungen • Algorithmen-Auswahl • Verständlichkeit der Ergebnisse • Nutzbarmachung für Anwender • Trainings-Performance SQL Server Plattform • SQL Server Data Mining © 2003-2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
