Analysis Services 2005

Analysis Services 2005 is part of Microsoft's product SQL Server 2005. SQL Server 2005 is the latest SQL Server release from Microsoft in November of 2005. In addition to Analysis Services 2005, SQL Server 2005 contains other services such as Integrations Services, Reporting Services, and Notification Services among other things. Integration Services, Analysis Services, and Reporting Services together form the core of business intelligence platform with SQL Server as the backend. Analysis Services 2005 not only provides you the ability to build dimensions and cubes for data analysis but also supports several data mining algorithms which can provide business insight into your data that are not intuitive. Analysis Services is part of a greater Business Intelligence platform, which leverages not only the rest of SQL Server 2005, but the .NET Framework (Common Language Runtime) and Visual Studio development environment as well. Next you will learn about the overall architecture of Analysis Services 2005 followed by the concept of Unified Dimensional Model (UDM) which helps you to have a unified view of your entire data warehouse.

SQL Server Analysis Services 2005 has been re-architected as both scalable and reliable enterprise class software that provides fine-grain security. So, not only is it quite manageable; but also protects your data from malicious attacks. The architecture of Analysis Services 2005 provides efficient scalability in terms of scale-out and scale-up features. Several instances of Analysis Services 2005 can be integrated together to provide an efficient scale-out solution. On the other hand, the service has been architected with efficient algorithms to handle large dimensions and cubes on a single instance. Analysis Services 2005 provides a rich set of tools for creating OLAP databases; efficient and easy manageability, as well as profiling capabilities.

The Business Intelligence Development Studio (BIDS) integrated within Visual Studio is the development tool shipped with Analysis Services 2005 used for creating and updating cubes, dimensions, and Data Mining models. The SQL Server Management Studio (SSMS) provides an integrated environment for managing SQL Server, Analysis Services, Integration Services, and Reporting Services. SQL Profiler in the SQL Server 2005 releases supports profiling Analysis Services 2005, which helps in analyzing the types of commands and queries sent from different users or clients to Analysis Services 2005. You learn more about BIDS and SSMS in Chapter 2 with the help of a tutorial. You learn about profiling an instance of Analysis Services using SQL Profiler in Chapter 12. In addition to the above-mentioned tools, Analysis Services 2005 provides two more tools: the Migration Wizard and the Deployment Wizard. The Migration Wizard helps in migrating Analysis Services 2000 databases to Analysis Services 2005. The Deployment Wizard helps in deploying the database files created using BIDS to Analysis Services 2005.

The SSMS provides efficient, enterprise-class manageability features for Analysis Services. Key aspects of an enterprise class service are availability and reliability. Analysis Services 2005 supports fail-over clustering on Windows clusters through an easy setup scheme and fail-over clustering certainly helps provide high availability. In addition, Analysis Services 2005 has the capability of efficiently recovering from failures. You can set up fine-grain security so that you can provide administrative access to an entire service or administrative access to specific databases, process permissions to specific databases, and read-only access to metadata and data. In addition to this, certain features are turned off by default so that the Service is protected from hacker attacks.

Analysis Services 2005 natively supports XML for Analysis specification defined by the XML/A Advisory Council. What this means is that the communication interface to Analysis Services from a client is XML. This facilitates ease of interoperability between different clients and Analysis Services 2005. The architecture of SQL Server Analysis Services 2005 includes various modes of communication to the service as shown in Figure 1-8. Analysis Server 2005 provides three main client connectivity components to communicate to the server. The Analysis Management Objects (AMO) is a new object model that helps you manage Analysis Server 2005 and the databases resident on it. The OLE DB 9.0 is the client connectivity component used to interact with analysis services 2005 instances s for queries that conforms to the OLE DB standard. The ADOMD.Net is dot Net object model support for querying data from Analysis Services 2005. In addition to the three main client connectivity components, two other components are provided by Analysis Services 2005. They are DSO 9.0 (Decision Support Object) and HTTP connectivity through a data pump. DSO 8.0 is the extension of the management object of Analysis Server 2000 so that legacy applications can interact with migrated Analysis Server 2000 databases on Analysis Server 2005. The data pump is a component that is set up with IIS (Internet Information System) to provide connection to Analysis Services 2005 over HTTP (Hypertext Transfer Protocol).

Figure 1-8

Even though XML/A helps in interoperability between different clients to Analysis Server, it comes with a cost on performance. If the responses from the server are large, transmission of XML data across the wire may take a long time depending on the type of network connection. Typically slow wide area networks might suffer from performance due to large XML responses. In order to combat this, Analysis Services 2005 supports the options for compression and binary XML so that the XML responses from the server could be reduced. These are optional features supported by Analysis Services 2005 that can be enabled or disabled on the Server.

Analysis Services 2005 stores metadata information of databases in the form of XML. Analysis Services 2005 provides you with the option of storing the data or aggregated data efficiently in a proprietary format on Analysis Services instance or storing them in the relational database. If you choose the data and/ or aggregated data to be stored in the proprietary format you can expect better query performance than the case where the data is being retrieved from the relational database. This proprietary format helps Analysis Services 2005 to retrieve the data efficiently and thereby improves the query performance. Based on where the data and/or aggregated fact data is stored you can classify the storage types as MOLAP (Multi-dimensional OLAP), ROLAP (Relational OLAP), or HOLAP (Hybrid OLAP).

MOLAP is the storage mode in which the data and aggregated data are both stored in proprietary format on the Analysis Services instance. This is the default and recommended storage mode for Analysis Services databases since you get better query performance as compared to the other storage types. The key advantages of this storage mode is fast data retrieval while analyzing sections of data and therefore provides good query performance and the ability to handle complex calculations. Two potential disadvantages of MOLAP mode are storage needed for large databases and the inability to see new data entering your data warehouse.

ROLAP is the storage mode in which the data is left in the relational database. Aggregated or summary data is also stored in the relational database. Queries against the Analysis Services are appropriately changed to queries to the relational database to retrieve the right section of data requested. The key advantage of this mode is that the ability to handle large cubes is limited by the relational backend only. The most important disadvantage of the ROLAP storage mode are slow query performance. You will encounter slower query performance in ROLAP mode due to the fact that each query to the Analysis Services is translated into one or more queries to the relational backend.

The HOLAP storage mode combines the best of MOLAP and ROLAP modes. The data in the relational database is not touched while the aggregated or summary data is stored on the Analysis Services instance in a proprietary format. If the queries to Analysis Services request aggregated data, they are retrieved from the summary data stored on the Analysis Services instance and they would be faster than data being retrieved from the relational backend. If the queries request detailed data, appropriate queries are sent to the relational backend and these queries can take a long time based on the relational backend.

Based on your requirements and maintainability costs you need to choose the storage mode that is appropriate for your business. Analysis Services 2005 supports all three storage modes.