XML und SQL Server 2000
Werbung
SQL Server 2000 XML, Web-Dienste und DWH/DM Dirk Werther Frank Maar Technologieberater Microsoft GmbH [email protected] [email protected] Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte Data Warehouse Framework Data Mining Herausforderungen XML Nachrichten/Dokumente aus existierenden relationalen Daten erzeugen XML Sichten auf relationale Tabellen Relationale Sichten auf XML Daten Datenänderung mittels XML Vielfältige Zugriffsmöglichkeiten (HTTP, ADO, ASP) zu den XML Daten Architecture Browsers IIS Applications I S A P I OLE/DB (SQLOLEDB) SQL Server 2000 DB Client Middle Tier DB SQL Server Architektur Browsers WEB Apps I S IIS A P I SQLXML Templates and XSLT XQuery XQuery/XPath XPath processor FOR XML SQL Server 7 SQL Row Row Row sets sets Rowset sets XML Views SQLXML OLE DB Provider SQL Server 2000 Updategram SQL SQL FOR XML XML XML Access through ADO Client Middle Tier Row Row Row sets sets sets Open XML SQL Server XML Query SQL Syntax Erweiterung SELECT… FROM… WHERE… ORDER BY… FOR XML (raw | auto [, ELEMENTS] | explicit) [, XMLData] [, BINARY base64]) FOR XML – Raw Modus Je Zeile ein <row> element Keine verschachtelten Elemente/Hierarchien möglich Spalten mit Werten ergeben Attribute mit Werten je Zeile <row> Ähnlich dem CSV Format, aber Erzeugung von XML FOR XML – Raw Modus Abfrage: SELECT Customers.CustomerID, OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerID FOR XML RAW Ergebnis: <row CustomerID=“ALFKI” <row CustomerID=“ALFKI” <row CustomerID=“ANATR” … <row CustomerID=“FISSA” … OrderID=“10643” /> OrderID=“10692” /> OrderID=“10308” /> /> FOR XML – Auto Modus Datenbanktabellenname wird als Elementname verwendet Spaltennamen werden als Attributnamen verwendet die ELEMENTS Anweisung erzeugt Unterelemente verschachtelte XML-Ausgabe kann erzeugt werden Verschachtelung von der Spaltenangabe im SELECT Kommando abhängig FOR XML – Auto Modus Abfrage: SELECT Customers.CustomerID, OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerID FOR XML AUTO Ergebnis: <Customers CustomerID=“ALFKI”> <Orders OrderID=“10643” /> <Orders OrderID=“10692” /> </Customers> <Customers CustomerID=“ANATR”> <Orders OrderID=“10308” /> … FOR XML – Explicit Modus Bietet komplette Kontrolle über die XML-Ausgabe Abfragespalten können individuell zu Elementen oder Attributen verknüpft beliebigste Verschachtelungen „Geschwister“ - Beziehungen herleitbare Hierarchien erzeugt ID/IDREF Beziehungen CDATA Sektionen in der XML Ausgabe enthält XML Anmerkungen FOR XML – Explicit Modus SELECT 1 as Tag, NULL as Parent, Customers.CustomerID as [Customer!1!CustomerID], NULL as [Order!2!OrderID] FROM Customers UNION ALL SELECT 2, 1, Customers.CustomerID, Orders.OrderID FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID ORDER BY [Customer!1!CustomerID], [Order!2!OrderID] FOR XML EXPLICIT <root> <Customer cid="ALFKI"> <name>Alfreds Futterkiste</name> <Order oid="O-10643" /> <Order oid="O-10692" /> <Order oid="O-10952" /> <Order oid="O-11011"/> </Customer> <Customer cid="BOLID"> <name>Bólido Comidas preparadas</name> <Order oid="O-10801" /> <Order oid="O-10970" /> </Customer> </root> FOR XML - Vorlage <root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="path to XSLT file" > <sql:header> <sql:param name=„city">%</sql:param> <sql:param name="state">WA</sql:param> </sql:header> <sql:query> SELECT * FROM Customers AS Customer WHERE City LIKE @city AND Region LIKE @state FOR XML auto </sql:query> </root> HTTP Zugriff – URL Abfragen URL Abfrage http://server/vroot?sql= SELECT+*+FROM+Customers+FOR+XML+AUTO &root=root Direkte Abfrage http://server/vroot/dbobject/ Employees[@EmployeeID=1]/@Photo Vorlagen http://server/vroot/vname?params XML View http://server/vroot/vname/xpath?params HTTP Zugriff – URL Abfragen http://server/vroot?params Parameter sql=select+*+from+Customers+FOR+XML+Auto xsl=table.xsl contenttype=image/gif outputencoding=UTF-8 root=root HTTP Zugriff - Sicherheit Sicherheit wird für jedes virtuelle Verzeichnis gesetzt Rechte im SQL Server ISAPI DLL mit 3 Autorisierungsmöglichkeiten Anonymous Access Windows-/SQL Server Kennung und Kennwort werden direkt zum virtuellen Verzeichnis gesetzt und gelten für alle Benutzer Basic Authentication SQL Server Kennung und Kennwort im Klartext sollte nur in Verbindung mit SSL verwendet werden Integrated Security benutzt die Windows ACLs Business-to-Business-Datenaustausch XPATH /Products d oa Bu lk l lts Annotations su + Re = Mapping Schema Qu ery BizTalk Schema + SQL Server SQL Server XML in BizTalk Grammar Send Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte Data Warehouse Framework Data Mining OpenXML Mechanismus für das Ändern von Daten in der Datenbank mittels XML Format Multi-row updates Multi-table updates basierend auf der XML Hierarchie Wird mit T-SQL Stored Procedures verwendet Bietet relationale Sicht auf XML Daten Zeilen- und Spalten werden mit XPath identifiziert Attributzentrierte und der Elementzentrierte Zuordnung Unterstützung von Hierarchien OpenXML SQL Server 2000 XML INSERT Parsen sp_xml_preparedocument name XML DOM zerlegen … … OpenXML … … ID Phone Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte Data Warehouse Framework Data Mining SQL Server XML View Mapper http://msdn.microsoft.com/downloads/default.asp?url=/downl oads/sample.asp?url=/MSDNFILES/027/001/443/msdncompositedoc.xml Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte DWH/DM Microsoft SQL Server 2000 Web Services Toolkit http://msdn.microsoft.com/downloads/default.asp?url=/dow nloads/sample.asp?url=/MSDNFILES/027/001/872/msdncompositedoc.xml&frame=true Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte Data Warehouse Framework Data Mining OO-Tools Datenbankschnittstelle die auf C++ http://www.roguewave.com/products/xplatform/dbt ools/ DBTools++ Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte Data Warehouse Framework Data Mining Closed Loop Analysis Microsofts Angebot für DWH/BI SQL Server 2000 – vollständige DWH-Plattform Server für relationale Daten Analysis Services Server für multidimensionale Daten Data Mining Funktionen Data Transformation Services (DTS) Metadaten-Services (Repository) English Query „built in“ Hochverfügbarkeit und Skalierbarkeit Graphische Admin- und Optimierungstools Office 2000 / Office XP als Client-Toolset Excel (mit Pivot Table Services) Office Web Components (OWC) für individuelle Entwicklung Data Analyzer, MapPoint Infrastruktur, Protokolle, Partner “OLE DB for OLAP”, “ADO MD” DWH-Allianz, DWH-Framework MSO – Microsoft Solution Offerings für BI Data Transformation Services (DTS) Der „mühsame“ Datenladeprozeß... Datenquelle Temporärer DatenSpeicher Nur bei Bedarf Daten Daten Validierung Validierung DTS Daten Daten Migration Migration Daten Daten Bereinigung Bereinigung Daten Daten Transformation Transformation Data Warehouse Was ist DTS? Data Transformation Service (DTS) ist ein Werkzeug zum Kopieren und Transformieren von Daten zwischen verschiedenen Datenquellen. eingebautes ETL – Tool (Extraktion, Transformation, Load) Transformation Transformation Datenquelle Datenziel “Datenpumpe” “Datenpumpe” ININ OUT OUT Beispiel: komplexer DTS-Workflow Operative Daten (OLTP) Strukturumwandlung Analytische Daten (multidimensional, OLAP) Schritt 1: Von OLTP zum Starschema DTS Faktentabelle enthält Measures (Maßzahlen) Kosten, Umsatz, Profit, Anzahl Kunden, Gewicht Dimensionstabellen enthalten „Zugriffsdaten“ Zeit: Jahr–Quartal–Monat-Tag Geographisch: Staat-Bundesland-PlzGebiet-Ort-Kunde Standard-Entwurfsmuster Star-Schema, Snowflake-Schema, Parent-Child Schritt 2: Vom Star-Schema zum Cube 1 2 3 D im e n s io n : P r o d u k t, R e g io n , Z e it M e a s u re : U m s a tz A g g rre e g a tio n e n a u f je d e r S tu fe 4 N o rd Z e it P rro odukt R e g io n K a te g o rie R e g io n Q u a rta l P ro d u k t S ta d t M onat B ü ro Ta g In d u s trie S üd La n d Jahr O st P ro d u k t Seife Saft Milch W e st Cola Analysis Services Region xxx Q ua r ta l U m s a tz P ro d u k t M ilc h , Q u a rta l 3 , N o rd Im Cube-Editor werden Faktentabelle, Dimensionen und Speicherlayout definiert Alle Schritte durch Assistenten ohne Programmierung Cube - Aufbereitung Zentrale Verwaltung aller Cube Eigenschaften und Features Weitere Dimensions-Schemata Architecture OLAP Services OLAP Manager Processing Querying Application OLAP Store OLAP Server ADO MD Other OLE DB Providers OLEDB for OLAP DSO PivotTable Service SQL Server Data Warehouse Aggregationen: Datenexplosion Erlös + Computer Hardware Software Produkte Quartal 1 100 150 250 Quartal 2 250 100 350 Halbjahr 1 350 250 600 4 Datenzellen, 5 Aggregatzellen: Verhältnis 2.25 Exponentieller Speicher- und Zeitbedarf! Speicherentwurfs-Assistent Vorausbestimmung der wichtigsten partiellen Aggregationen Speicherlayout MOLAP, ROLAP, HOLAP Drei Varianten stehen zur Verfügung MOLAP: Multidimensionales OLAP (erste Wahl!) ROLAP: Relationales OLAP (Strukturbruch) HOLAP: Hybrides OLAP flexible Kombination erlaubt optimales Verhältnis zwischen Performance und Platzbedarf das Speicherlayout ist vor dem Benutzer völlig verborgen, er sieht nur einen Cube Zugriffsrechte OLAP-Server Authenifizierung erfolgt via NT-IntegratedSecurity Mitglied der Domäne oder einer vertrauten Domäne Gruppe „OLAP Administrator“ Zugriffsrechte OLAP-Client Rollenkonzept für Cube – Zugriff Z.B. Rolle Vertrieb, Controlling Jedem Cube wird eine Rolle mit entsprechenden Lese/Schreibrechten zugewiesen . MDX MultiDimensional EXpressions Sprache zur Formulierung von multidimensionalen Abfragen (DML) und zur Datendefinition (DDL) Standardisiert als Bestandteil von “OLE DB for OLAP“ u.A. im Cube-Editor für berechnete Elemente benutzt Ermöglicht Clients beliebige Daten aus einem Cube via „OLE DB for OLAP“ bzw. „ADO MD“ abzurufen Grundlage für alle analytischen Anwendungen Berichte erstellen Excel 2000/2002 Neue Berichte erstellen Fertige Arbeitsmappen aktualisieren Statische HTML-Berichte Office WebKomponenten Data Analyzer Weitere Berichtsformen MapPoint Landkarten Einfache Verknüpfung mit Daten aus Excel und SQL Server Architektur PivotTable Service Übersicht XML-Daten lesen XML-Daten einfügen Zusatzprogramme SQL Server XML View Mapper 1.0 SQL Server 200 Web Services Toolkit OO-Zusatzprodukte Data Warehouse Framework Data Mining Data Mining Overview Evolution of the Database Meistens OLTP Datenbanken SQL Server 7.0 machte Data Warehousing einfacher Analysis Services sind das zentrale Element Data Mining (DM) kann die Analyse erweitern OLTP Data Warehousing Decision Support OLAP Data Mining Data Mining Overview SQL Server 2000 DM Architecture Manager UI DM Wizard DSO DTS DM Tasks DMM OLE DB OLAP DM Server Client OLAP DM Engine Engine OLAP DM Engine Engine (local) (local) TCP/IP and HTTP Der Mining Process Mining Model Training Data Data Mining Model To Predict DM Engine DM Engine Mining Model Predicted Data DM-Grundlagen: Was sind “Fälle”? DM-Algorithmen analysieren “Fälle” (OLAP analysiert Fakten) Ein Fall ist die “Entität”, die kategorisiert und klassifiziert werden soll: Gesundheitsrisiken: Fall = Patient Kreditrisioko: Fall = Kunde Produktrentabilität: Fall = Produkt Erfolg von Marketingkampagnen: Fall = Kampagne Jeder Fall umschliesst das Wissen, das wir über die Entität haben Fallmenge wird oft aufgeteilt: Trainingsdaten: “Input” für statistische Modell Testdaten: Güte des Modells überprüfen Einfache Fälle – eine Tabelle Komplexe Fälle – mehrere Tabellen Z.B. das Wissen über einen Kunden Demographische Daten: Alter, Familienstand, Einkommen, Vermögen, Anzahl Kinder, Beruf, Ausbildung Kaufverhalten: Type, Anzahl, Rabatte, Werbeaktionen, Zeiträume Produktklassifizierung Viele Datensätze zu einem Fall Besuchte Kaufhäuser Viele Datensätze zu einem Fall Anzahl und Eigenschaften der besitzten Autos Viele Datensätze zu einem Fall Komplexe Fälle – geschachtelte Tabellen Gekaufte Produkte KundenID Alter Stand Vermögen Produkte 1 2 3 35 20 57 M S M 380,000 50,000 470,000 Menge TV 1 Coke 6 Ham 3 VCR 1 TV 1 Cake 12 Ham 2 Coke 1 Data Mining Modelle Struktur eines DM-Modells wird als „Tabelle“ definiert Trainieren bedeutet Einfügen der Fälle Vorhersage bedeutet Abfragen der Tabelle Merkmale der Fälle beschreiben, stehe in Spalten Eingabespalten und ggf. Vorhersagespalten Alle Operationen behandeln das Modell als Tabelle: CREATE, INSERT, SELECT Benutzeroberfläche DM-Editor identisch zu OLAP Analysis Server 2000 unterstützt zwei DM-Algorithmen: Clustering und Decision Trees Güte eines Modells kann mittels eines Satzes von Testdaten geprüft werden (DTS Task) Data Mining - Clustering Einteilung der Daten in möglichst getrennte, homogene Gruppen Ableitung von Zielgruppen-orientierten MarketingKampagnen Erkennung von Kunden-Profilen zur optimalen Abstimmung von Produkten und Dienstleistungen separate Gruppen Clustering Algorithmus Gauss’sche Verteilung X X X Anwendung von Clustering Kundensegmentierung Vorhersage des Segments für jeden Kunden Bestimmung der Kunden zu einem Segment Wie kann ich einen neuen Kunden klassifizieren? Vorbereitung von Werbekampagnen Zielgruppenanalyse Was sind die Gemeinsamkeiten? Was unterscheidet die Gruppen? Wie kann ich Kundengruppen gezielt ansprechen? Gegenteil: „spam“-Mail Aufdeckung bislang verborgener Zusammenhänge Einsetzbar, wenn man noch keine genaueren Vorstellungen hinsichtlich der Zusammenhänge der Einflussgrößen hat Kundensegmentierung vorhersagbare Einteilung Data Mining - Entscheidungsmodelle Vorhersage von Kaufverhalten Analyse von Risiken Ideale Hilfe für Verkauf und Marketing Wie hoch ist die Wahrscheinlichkeit, dass ein bestimmter Telefonkunde auch Internetdienste benutzt? Income Good (2) Poor (3) High Good (2) Poor (1) Married Good (2) Poor (0) Low Good (0) Poor (2) Not married Good (0) Poor (1) Anwendung von Decision Trees Kaufverhalten: Welches Produkt wird warum von welchen Kunden gekauft Zielgruppenmarketing: Welche Kundenkarte passt zu einem Kunden? Risikoanalyse: Kreditvergabe Gesundheitsvorsorge, Risikofaktoren Entscheidungsverhalten: Welche Kriterien beeinflussen eine Entscheidung Anwendbar, wenn der Zusammenhang zwischen Einflussgrößen und Vorhersagegröße bekannt ist DMM Decision Tree Kundenkarte Vorhersage Kundenkartentyp mittels der Merkmale Einkommen, Geschlecht, Ausbildung, Stand, Kinderzahl, Beruf, ... Mehr Info … http://msdn.microsoft.com/xml/ http://msdn.microsoft.com/sqlserver http://www.microsoft.com/sql/ http://www.w3.org/XML/ http://www.msdn.microsoft.com/library/default.a sp?url=/library/enus/dnexxml/html/xml07162001.asp Zeit für Fragen
