18 ServerarchitekturDie Serverkomponente von Microsoft SQL Server 2005 Analysis Services (SSAS) ist die Anwendung msmdsrv.exe, die normalerweise als Windows-Dienst ausgeführt wird.Diese Anwendung besteht ausSicherheitskomponenteneiner XMLA-Überwachungskomponente (XML for Analysis)einer Abfrageverarbeitungs-komponente undzahlreichen internen Komponenten, die die folgenden Funktionen ausführen:Analysieren von Anweisungen, die von Client empfangen werdenVerwalten von MetadatenBehandeln von TransaktionenVerarbeiten von BerechnungenSpeichern von Dimensions- und ZellendatenErstellen von AggregationenPlanen von AbfragenZwischenspeichern von ObjektenVerwalten von ServerressourcenThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

19 ClientarchitekturMicrosoft SQL Server 2005 Analysis Services (SSAS) unterstützt eine Thin Client-Architektur. Das Analysis Services-Berechnungsmodul ist vollständig serverbasiert, sodass alle Abfragen auf dem Server aufgelöst werden. Daher ist für jede Abfrage nur ein Roundtrip zwischen dem Client und dem Server erforderlich, was zu skalierbarer Leistung führt, wenn die Komplexität der Abfragen zunimmt.Das native Protokoll für Analysis Services ist XMLA (XML for Analysis). Analysis Services stellt mehrere Datenzugriffsschnittstellen für Clientanwendungen zur Verfügung. Diese Komponenten verwenden jedoch alle XMLA für die Kommunikation mit einer Instanz von Analysis Services.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

20 ClientarchitekturZusammen mit Analysis Services werden mehrere unterschiedliche Anbieter zur Verfügung gestellt, um unterschiedliche Programmiersprachen zu unterstützen.Ein Anbieter kommuniziert mit einem Server mit Analysis Services, indem XMLA in SOAP-Paketen über TCP/IP oder durch Internetinformationsdienste (Internet Information Services, IIS) über HTTP gesendet und empfangen wird.Eine HTTP-Verbindung verwendet ein von IIS instanziiertes COM-Objekt, das als Datapump bezeichnet wird und als Datenleitung für Analysis Services-Daten fungiert.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

21 Clientarchitektur Win32-Clientanwendungen können mithilfe vonOLE DB für OLAP-SchnittstellenMicrosoft ActiveX Data Objects-Objektmodell (ADO) für COM-Automatisierungssprachen (Component Object Model) wie Microsoft Visual BasicVerbindungen zu einem Server mit Analysis Services herstellen.Mit .NET-Sprachen codierte Anwendungen können mithilfe von ADOMD.NET eine Verbindung zu einem Server mit Analysis Services herstellen.Programmier-spracheDatenzugriffs-schnittstelleC++OLE DB für OLAP (OLE DB for OLAP)Visual Basic 6ADO MD.NET-SprachenADOMD.NETAlle Sprachen mit SOAP-UnterstützungXMLA (XML for Analysis)The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

22 ClientarchitekturAnalysis Services verfügt über eine Webarchitektur mit einer vollständig skalierbaren mittleren Ebene, die sowohl in kleineren als auch in großen Organisationen bereitgestellt werden kann.Analysis Services stellt umfassende Unterstützung auf mittlerer Ebene für Webdienste zur Verfügung.ASP-Anwendungen werden von OLE DB für OLAP und ADO MD unterstützt, ASP.NET-Anwendungen werden von ADOMD.NET unterstützt.Die mittlere Ebene ist für viele gleichzeitige Benutzer skalierbar.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

23 ClientarchitekturLokale Cubes und lokale Miningmodelle ermöglichen Analysen auf einer Clientarbeitsstation, wenn deren Verbindung zum Netzwerk getrennt ist.Das lokale Cubemodul (Msmdlocal.dll) unterstützt lokale Cubes und lokale Miningmodelle auf Clientcomputern. Das lokale Cubemodul ist ein In-Process-COM-Server.Eine Clientanwendung ruft den OLE DB-Anbieter für OLAP auf, der das lokale Cubemodul zum Erstellen und Abfragen von lokalen Cubes lädt.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

24 ClientarchitekturSowohl lokale Cubes als auch lokale Data Mining-Modelle werden in multidimensionalen Cubedateien (CUB) auf einem Clientcomputer gespeichert.Eine CUB-Datei enthält mindestens einen lokalen Cube, kann jedoch mehrere Cubes enthalten.Abfragen an die lokalen Cubes werden vom lokalen Cubemodul verarbeitet und benötigen keine Verbindung zu einer Analysis Services-Instanz.Ein lokaler Cube kann entweder auf einem vorhandenen, in Analysis Services gespeicherten Cube basieren oder auf einer relationalen OLE DB-Datenbank.Wenn ein lokaler Cube aus einer relationalen Datenquelle erstellt wird, ist dessen Struktur jener von Cubes in Analysis Services sehr ähnlich.Wenn ein lokaler Cube aus einer OLAP-Datenquelle erstellt wird, ist der lokale Cube an den Quellcube gebunden.Ein lokaler Cube kann auch ohne eine Ausgangsdatenquelle erstellt werden.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

25 ClientarchitekturDas lokale Cubemodul kann auch lokale Data Mining-Modelle erstellen, die in lokalen Cubes verarbeitet wurden. Ein lokales Data Mining-Modell basiert auf einem lokalen Cube und wird in derselben CUB-Datei gespeichert, die auch den lokalen Cube definiert. Lokale Miningmodelle können auch ohne eine Verbindung zu einer Analysis Services-Instanz verwendet werden.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

27 Berichte: Excel PivotTables und PivotChartsExcel ist bekannt und oft schon vorhandenUnterstützt AS 2005Arbeitet mit relationalen und OLAP-DatenGetPivotData fügt Flexibilität hinzuAs an intrinsic part of Excel, PivotTabels and PivotCharts are relatively well-known and can give a good functional display of many kinds of data.There is a special function called GetPivotData which allows us to add some flexibility to the report using code.GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)For example,GETPIVOTDATA("Sales",$A$4,"Region","South")would give you the Sales total for the South Region - $A$4 is the location of any cell or range of cells in your Pivot Table.Other than the fairly rigid, structured nature of Pivot Tables, the charting in Excel has not been updated for quite a period of time. Also treating all hierarchies as dimensions could become very confusing for the end user, especially if you have many attributes, which is very likely in Analysis Services 2005.

28 Berichte: Office Web Components (OWC)In eigene Anwendungen einbettbarFlexibler als ExcelBessere Diagramm-Erzeugung als in ExcelBenutzerdefinierte AggregateKeine Office-Lizenz erforderlichVBA/ActiveX-UnterstützungVon Microsoft in SQL Server Studios selbst eingesetztThe Office Web Component is an ActiveX control similar to the Excel Pivot tables. It is the same component that we use when we browse a cube in BIDS.Although it may not be a great choice for a ‘web’ component, due to it’s thick nature, it is still very useful within an intranet where you can control the web browser deployments and capabilities.

29 Berichte: Excel Add-In AcceleratorVerbesserte AbfragenFreiform-Modus mit asymmetrischen Berichten, mehreren Datenquellen und flexiblen FormelnVerteilung über Arbeitsblatt/Excel-DateiThe new Microsoft Excel Add-In for SQL Server Analysis Services provides flexible free-form reports coupled with structured reports. The structured reports are similar in many ways to PivotTable reports, but with some added functionality, most notably the following:Reports automatically support advanced Analysis Services capabilities such as actions, what-if analysis, and Drillthrough.Advanced techniques are available for specifying members from a dimension, including wild card searches and data-driven selectionThe greatest benefits of the add-in come from free-form reports. Unlike Structured reports, free-form reports do not use structured report segments and a data grid. Rather, individual cell formulas connect each cell to the connection. You can insert Microsoft Excel formulas into the middle of the report and rows and columns can be inserted or individual cells moved freely on the worksheet. Following are some of the greatest benefits of a free-form report:Mixed hierarchies can be arranged in a single report axis, which makes it easy to create asymmetrical reports.A single report can integrate members and values from multiple connections, including cubes from different serversThe queries generated by the add-in are optimized so that the performance is similar to that of using a PivotTable report.

30 Berichte: Reporting ServicesTabellen, Matrizen, DiagrammeVordefinierte StrukturInteraktivität möglichStrukturierte Daten: relational, OLAP, hierarchischURL-Zugriff und einbettungsfähig in eigenen AnwendungenWeb Service-UnterstützungReporting Services is a very powerful tool with some very good built-in features, as listed on the slide.It is very structured and sometimes we do want that structure. Along with all of the features listed on the slide, it is important to note that it is a thin-client tool, accessible by accessing the web page or by calling a web service on the Reporting Server. It is therefore very easy to embed in applications as we shall see shortly.

35 UDMDie Rolle eines UDM (Unified Dimensional Model) besteht darin, eine Brücke zwischen dem Benutzer und den Datenquellen bereitzustellen.Ein UDM wird aus mindestens einer physikalischen Datenquelle hergeleitet.Der Benutzer gibt Abfragen für das UDM mithilfe einer Vielzahl von Clienttools wie Microsoft Excel aus.Es ergeben sich Vorteile für den Endbenutzer, auch wenn das UDM nur als minimale Schicht über der Datenquelle erstellt wird: ein einfacheres, verständlicheres Modell der Daten, das Isolieren heterogener Back-End-Datenquellen sowie Leistungsverbesserungen bei Abfragen.Das UDM bietet die folgenden Vorteile:Deutlich verbessertes Benutzermodell.Stellt Abfragen mit hoher Leistung bereit und unterstützt so interaktive Analysen von großen Datenmengen.Erfasst Geschäftsregeln im Modell zur Unterstützung umfangreicherer Analysen.Ermöglicht eine Rückkopplung, bei der Benutzer auf die angezeigten Daten reagieren können.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

36 UDM Direkter Zugriff auf die Datenquellen (traditionell)Zugreifen auf die Datenquellen mithilfe eines UDMThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

37 UDM Hierarchien Key Performance Indicators (KPIs) ZeitangabenÜbersetzungenKategorisierungPerspektivenAttributsemantikThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

38 UDM Erweiterte AnalytikThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

39 UDM UDM MDX Datenquelle Werkzeug DW Analysis Services XML/A or ODBOCacheOLAPBrowser (2)Browser (1)MDXBerichtswerkzeug(1)One of the main purposes of the UDM is to seamlessly integrate the OLAP data with the relational data that is in the Data Warehouse. In order to access the data, we use MDX to go to Analysis Services. Therefore, to take full advantage of the UDMs ability to integrate relational and OLAP data, we need to use MDX.Berichtswerkzeug(1)BI-Anwendungen

40 Data MiningData Mining wird oft als "der Prozess des Extrahierens gültiger, authentischer und aussagefähiger Informationen aus großen Datenbanken" beschrieben. Data Mining ist demnach ein Prozess, der Muster und Trends in Daten aufdeckt. Diese Muster und Trends lassen sich zusammenfassen und als Miningmodell definieren. Miningmodelle werden u. a. auf folgende Geschäftsszenarien angewendet:Erstellen von VerkaufsprognosenDurchführen von Direktmarketingaktionen, die auf einen bestimmten Kundenkreis abzielenErmitteln von Produkten mit Cross-Selling-PotenzialErmitteln der Reihenfolge, in der Kunden Produkte in den Einkaufswagen legenDie Erstellung eines Miningmodells muss als Teil eines größeren Prozesses verstanden werden, der sich von der Definition der zentralen Problemstellung bis hin zur Implementierung des Modells in einer Arbeitsumgebung erstreckt.Ein Data Mining-Prozess besteht im Wesentlichen aus sechs Schritten:Definieren der ProblemstellungVorbereiten der DatenDurchsuchen von DatenErstellen von ModellenDurchsuchen und Validieren von ModellenBereitstellen und Aktualisieren von ModellenThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

41 Data MiningThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

43 Data MiningMicrosoft SQL Server 2005 Analysis Services (SSAS) beinhaltet folgende Algorithmen:Microsoft Decision Trees-AlgorithmusMicrosoft Clustering-AlgorithmusMicrosoft Naive Bayes-AlgorithmusMicrosoft Association-AlgorithmusMicrosoft Sequence Clustering-AlgorithmusMicrosoft Time Series-AlgorithmusMicrosoft Neural Network-Algorithmus (SSAS)Microsoft Logistic Regression-AlgorithmusMicrosoft Linear Regression-AlgorithmusThe server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.

45 Data Mining: Naïve BayesEinfaches und am einfachesten z implementierendes ModellWelches Attribut kann das Attribut am besten vorhersagen?Naïve Bayes is a simple model whose major purpose is to predict the behaviour based on single attributes. It is good as a first start but has several limitations and constraints.

46 Data Mining: Decision TreeWelcher Attributbaum kann das Ergebnis am besten vorhersagen?Zusammenspiel zwischen Attributen.The Decision Tree algorithm is a more advanced version of Naïve Bayes which displays the relevance of particular attributes to the behaviour or purchasing power of the sample population, however it takes it slightly further by looking at the interplay between attributes. For example, Naïve Bayes may show that age has an importance, but Decision Tree may uncover that age is only important for males.

47 Data Mining: Cluster Welche Untergruppen sind in den Daten enthalten?Wie verschieden sind die Untergruppen?Finde Muster ohne spezielles Ziel.The Cluster model tries to see how your customers form subgroups and how similar of different the groups are from one another. This is very useful for finding general patterns within the distribution. As an example, if you have four Vice Presidents within your company, you may decide to focus them on the four major groupings within your customer base.

48 Data Mining: AssociationÄhnlich Cluster, aber mit Blick auf eine einzige Transaktion.Welche Produkte werden zusammen gekauft (Warenkorbanalyse)?The Association model is similar to the Cluster model but would be used to look for groupings within products for a single transaction. This is known as Market Basket analysis and can be used to determine, for example, whether a customer often buys a particular group of products together.

49 Data Mining: Sequence ClusterWie Cluster, aber mit Berücksichtigung einer AbfolgeWelches Ereignis wird wahrscheinlich danach eintreten (Klickpfade)?Benötigt ein Attribut, um die Abfolge abzubilden.The Sequence Cluster model is like the Cluster model with the additional factor of sequence. It can be used to determine what is likely to be the next step in a sequence and is suited to Click-Stream analysis, i.e. the routes that customers may take when navigating through an e-commerce site.

50 Data Mining: Time SeriesWelche Trends sind vorhanden?Wie interagieren Trends miteinander?Benötigt ein zeitbasiertes Attribut (Vorschau).Time Series is effectively trend analysis. We can view how trends are occurring within the data but also how trends interact with each other. We may see that the performance of one attribute over time has parallel behaviour with another attribute.

51 Data Mining: Neural NetErstellt eine mehrschichtige Netzwerk von NeuronenWie ein Entscheidungsbaum, aber mit dreidimensionaler KnotenstrukturThe Neural Net model is similar to the Decision Tree model but operates in three dimensions. It attempts to emulate the behaviour of the brain and looks at the flow of data and relationships though the ‘pipes’ connecting the nodes.

52 Analysis Services-ObjekteDatenquellen: Datenquelle in Analysis Services.Datenquellensichten: ein logisches Datenmodell, das auf mindestens einer Datenquelle in Analysis Services basiert.Cubes: Cubes und Cubeobjekte, einschließlich Measures, Measuregruppen, Dimensionsverwendungbeziehungen, Berechnungen, Key Performance Indicators (KPIs), Aktionen, Übersetzungen, Partitionen und Perspektiven.Dimensionen: Dimensionen und Dimensionsobjekte, einschließlich Attribute, Attributbeziehungen, Hierarchien, Ebenen und Elemente.Miningstrukturen: Miningstrukturen und Miningobjekte, einschließlich Miningmodellen.Rollen: eine Rolle, den Sicherheitsmechanismus, der zum Steuern des Zugriffs auf Objekte in Analysis Services verwendet wird.Assemblys: eine Auflistung von benutzerdefinierten Funktionen, die zum Erweitern der Sprachen MDX und DMX in Analysis Services verwendet werden.The server has a number of formats which can be set up when the Cube is created.One property that can be returned from the CellSet is the Formatted Value property, which returns a rounded string. If you are looking to pass the results to another function, or even to another application, such as Excel, it is better to retrieve the Value property for calculation, and then the Format_String property which can be applied once all of the calculations are finished.We can also define server-side colours and fonts which can be used for exception reporting, for example, a different colour for negative values. These can be incorporated into the application and are a good example of basing the client-side format on the Business–layer rules.