Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

Analysis Services 2005 is not just an evolutionary step up from Analysis Services 2000, but a quantum leap forward in functionality, scalability, and manageability. Relational databases provide a simple, flexible, manageable schema; they provide access of data to the end user easily congealed into information rich reports. On the other hand, OLAP databases are typically used for high-end performance by the user who needs rich analytics and exploration capabilities. Analysis Services 2005 merges the capabilities of relational and OLAP worlds, thereby providing a unified view of the data to the end user. This unified model is called the Unified Dimensional Model (UDM). In sum, Analysis Services 2005 is a powerful, enterprise-class product and one that you can use to build large-scale OLAP databases and implement strategic business analysis against those databases. You learn more about the UDM and the advanced analytics capabilities of Analysis Services 2005 in chapters 6, 9 and 18. This chapter gives you hands-on experience with both the development and management tools environments.

Development, Administrative, and Client Tools

If you have used Analysis Services 2000, you have used the Analysis Manager. The Analysis Manager, which is shipped with that version, is implemented as a snap-in to the Microsoft Management Console (MMC). The Analysis Manager is a development environment for building Analysis Services databases as well as a management environment to manage multi-dimensional databases. Analysis Services 2000 provided limited functionality with respect to client tools. Customers were able to browse data within the Analysis Manager. A sample application called MDX Sample that was shipped along with the product provided you with the capability to build and send queries against Analysis Services databases and view the results.

Analysis Services 2005 has separate environments for development and management. The development environment is called Business Intelligence Development Studio (BIDS) and is integrated with Microsoft Visual Studio. Similar to a developer building a Visual Basic or C++ project, you will be able to build a Business Intelligence project. The management environment is called SQL Server Management Studio (SSMS). SSMS is one complete integrated management environment for several services (including SQL Server itself, Analysis Services, Reporting Services, Integration Services and SQL Server Mobile) released in SQL Server 2005. The SSMS was built to provide ease of use and manageability for all the database administrators in one single environment. The client tools available to analyze or retrieve data from Analysis Services 2005 are integrated within BIDS as well as SMSS. You can browse data from both of these environments as well. In SSMS you are provided with a query builder to retrieve data from Analysis Services. The query builder replaces the MDX Sample application that came with Analysis Services 2000. In addition the query builder provides intellisense support providing an array of options for you to access MDX language reference including auto completion of key words.

If you have used Microsoft SQL Server 2000 you might also be familiar with SQL Profiler. In the SQL Server 2005 release the capability of tracing, or profiling, queries run against Analysis Services has been integrated into SQL Profiler. Analysis Services Profiler information can be utilized to analyze and improve performance. You learn more about the Profiler in Chapter 12.

Analysis Services Version Differences

Analysis Services 2000 provided a rich feature set that helped in building solid data warehouses. The features combined with the MDX query language provided rich analytics for the customers. As with any software package, though, Analysis Services 2000 had limitations. Some of the limitations of Analysis Services 2000:

• Even though Analysis Services 2000 had a rich feature set, modeling certain scenarios either resulted in significant performance degradation or simply could not be accomplished.

• There were size limitations on various objects such as dimensions, levels and measures within a specific database.

• Analysis Services 2000 loaded all the databases at startup. If there were a large number of databases and/or a very large database, this resulted in long server startup time.

• Analysis Services 2000 had a thick client model that helped in achieving very good query performance, but did not scale very well in 3-tier applications (for example, Web scenarios).

• The metadata information of the databases was either stored in an access database or a SQL Server database. Therefore maintenance of data and metadata had to be done carefully.

• The backup format used to back up Analysis Services databases limited the file size to 2GB.

Analysis Services 2005, in addition to providing the best of the relational and OLAP worlds, overcomes most of the limitations of Analysis Services 2000. Following are some of the benefits of using the Analysis Services 2005:

• Two fundamental changes in Analysis Services 2005 are the thin client architecture that helps in scalability of 2-tier or 3-tier applications and the support of the native XML/A (XML for Analysis) protocol for communication between client and server.

• Several new features added to Analysis Services 2005 facilitate optimal design of data warehouses to form UDMs. Parts II and III of this book introduce these new features.

• Most of the size limitations on various objects have been greatly extended; or for all practical purposes, removed.

• Analysis Services 2005 provides better manageability, scalability, fine-grain security, and higher reliability by supporting fail-over clustering.

• Analysis Services 2005 natively supports Common Language Runtime (CLR) stored procedures with appropriate security permissions.

• Metadata information is represented as XML and resides along with the data. This allows for easier maintainability and control over the service.

• Analysis Services 2005 uses a different backup format (you learn about backup in Chapter 12) than the one used in Analysis Services 2000. Therefore, the 2GB backup file limit in Analysis Services 2000 has been eliminated.

Overall, Analysis Services 2005 provides you with a great combination of functionality and ease of use that enables you to analyze your data and make strategic business decisions. You will see these capabilities emerge step by step as you advance through this book.