OLAP und Analytische Fähigkeiten in SQL Server 2005

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.