Microsoft SQL Server 2008 Analysis Services Unleashed [NOOK Book]

Introduction

Analysis Services began as the project of a small Israeli firm named Panorama, which had responded to a request from a British publishing company to develop an application that would analyze the data stored in its relational database. By the end of 1994, Panorama developers began work on a more general application that would make it possible for business managers to analyze data with relative ease.

With its first release in 1995, Panorama deployed the application to several dozen customers. As the next release moved the application more deeply into the Israeli market, the Panorama team began to develop a new client/server analytical application. The server would process the data and store it in a proprietary format, and the client would also offer users an easy-to-use, rich graphical interface.

By 1996, the application had come to the attention of Microsoft, which acquired the technology by the end of that same year. In early 1997, a small Panorama team comprised of Alexander Berger, Amir and Ariel Netz, Edward Melomed, and Mosha Pasumansky moved from Tel Aviv to Redmond to start work on the first version of Microsoft OLAP Server. After the move to the United States, the team added new developers Irina Gorbach and Py Bateman.

To make the application attractive to enterprise customers, the team took on the challenge of formalizing and standardizing data exchange protocols, and they eliminated the client side of the application in favor of supporting a variety of third-party client applications. In early 1997, a small group including Alexander Berger retreated to a Puget Sound island to brainstorm the foundation of what would become SQL Server Analysis Services.

That retreat produced a plan for developing a standard protocol for client applications to access OLAP data: OLEDB for OLAP. More important, and more challenging, was the plan for developing a new query language that could access multidimensional data stored in the OLAP server—MDX (Multidimensional Expressions). MDX is a text language similar to SQL. MDX makes it possible to work with a multidimensional dataset returned from a multidimensional cube. From its inception, MDX has continued to change and improve, and now it is the de facto standard for the industry.

The original release plan was to include the OLAP server in the 1997 release of SQL Server 6.5. However, instead of rushing to market, Microsoft decided to give the development team more time to implement MDX and a new OLEDB for OLAP provider. Microsoft’s first version of a multidimensional database was released in 1998 as part of SQL Server 7.0. That version was integrated with Microsoft Excel PivotTables, the first client for the new server.

Under the slogan, “multidimensionality for the masses,” this new multidimensional database from Microsoft opened the market for multidimensional applications to companies of all sizes. The new language and interface were greeted favorably. The simplicity (and, one could say, elegance) of the design made it possible for users to rapidly become proficient with the new product, including users who weren’t database experts. Technology that used to be available only to large corporations was now accessible to medium-sized and small businesses. As a result, the market for new applications that use multidimensional analysis has expanded and flourished in an environment rich with developers who write those applications.

But, of course, we were not satisfied to rest on our laurels. We took on a new goal—turn Analysis Services into a new platform for data warehousing. To achieve this, we introduced new types of dimensions, increased the volume of data the server can process, and extended the calculation model to be more robust and flexible. Even though no additional personnel joined the team for this effort, by the end of 1999 we brought the new and improved Analysis Services 2000 to market.

For the next five years, more and more companies adopted Analysis Services until it became a leader in the multidimensional database market, garnering a 27% market share. Now, multidimensional databases running on OLAP servers are integral to the IT infrastructures of companies of all sizes. In response to this wide adoption of multidimensional database technology, Microsoft has increased the size of the team devoted to OLAP technology in order to continue to develop the platform to meet the requirements of enterprise customers.

For the 2005 release of SQL Server Analysis Services we started from ground up, rewriting the original (and now aging) code base. We built enterprise infrastructure into the core of the server.

SQL Server 2008 release continues to improve architecture and functionality of Analysis Services. While improving the performance of query execution, it also introduces query language extensions and new management capabilities.

Who Is This Book’s Intended Audience?

In this book, we bring you the tools you need to fully exploit Analysis Services and explain the architecture of the system. You’ll find all of the coverage of our previous book (just in case you were wondering if you needed to go back and read that one first), including the basic architecture established in Analysis Services 2005 as well as all the improvements introduced in Analysis Services 2008. Analysis Services Unleashed gives you a full understanding of multidimensional analysis and the MDX query language. It also exposes all the aspects of designing multidimensional applications and management of the system.

How This Book Is Organized

The book is divided into the following nine parts:

Parts I and II are devoted to a formalized description of the multidimensional model implemented in the new version of the OLAP server. We give you the vocabulary and concepts you’ll need to work with this model.

In Part III, we present a detailed discussion of MDX and explanation of the way we use it to query multidimensional data. You’ll need a practical grasp of the data model and MDX to take advantage of all the functionality of Analysis Services.

We devote the middle section of the book in Parts IV–VII to the practical aspects of loading and storing data in Analysis Services, as well as methods of optimizing data preparation and data access. In addition, we examine server architecture.

In the last section of the book, Parts VIII–IX, we discuss data access, the architecture of client components, and data protection. In addition, we examine the practical aspects of administering the server and monitoring its activities.

We wish you great success in your work with Analysis Services 2008, and we hope that our humbly offered book is of service to you.

Conventions Used in This Book

Commands, scripts, and anything related to code are presented in a special monospace computer typeface. Bold indicates key terms being defined, and italic is used to indicate variables or for emphasis. Great care has been taken to be consistent in letter case, naming, and structure, with the goal of making command and script examples more readable. In addition, you might find instances in which commands or scripts haven’t been fully optimized. This lack of optimization is for your benefit, as it makes those code samples more intelligible and follows the practice of writing code for others to read.

Other standards used throughout this book are as follows:

---

CAUTION - Cautions alert you to actions that should be avoided.

---

---

NOTE - Notes give you additional background information about a topic being discussed.

---

© Copyright Pearson Education. All rights reserved.