Tour of Analysis Services

To better understand the various touch points that you must manage as a DBA, we will start with a quick tour of Analysis Services. The primary value that Analysis Services brings to businesses is useful, important, and timely information that can be difficult or impossible to obtain from other sources of information (enterprise resource planning systems, accounting systems, customer relationship management systems, supply chain management systems and so on). Analysis Services provides two distinct services that assist in supplying these business needs; Online Analytical Processing (OLAP) and data mining. With these services, Analysis Services differs from the more traditional Online Transaction Processing (OLTP) systems in that it is optimized for fast access to vast quantities of data often spanning many years. Our focus will be on the OLAP services provided.

The OLAP engine has to be optimized for lightning-quick data retrieval but also offers the following strategic benefits:

• Shared data access that includes security at the most granular level and the ability to write back data

• Rapid, unencumbered storage and aggregation of vast amounts of data

• Multidimensional views of data that go beyond the traditional row and column two-dimensional views

• Advanced calculations that offer better support and performance than RDBMS engine capabilities

Where SSAS differs from traditional OLAP servers is that the 2005 release offers a Unified Dimensional Model (UDM) focused on unifying the dimensional and relational models. The relational model represents the standard model used by transaction processing systems. This relational model is generally optimized for data validity and storage optimization rather than query performance. The dimensional model was developed specifically to address query performance primarily by the denormalization of relational model schemas into a simpler model characterized as having fewer joins and more user-friendly model elements (table and column names). Denormalization, as you might recall, is the process of optimizing the performance of a database by adding redundant data into a database table that would normally have been normalized or removed to additional tables.

Unified Dimensional Model Components

First you need to understand that in the 2005 release of Analysis Services, the Unified Dimensional Model (UDM) is the cube. Let's begin looking at the composition of the UDM.

Data Source View

At the heart of the UDM is the logical data schema that represents the data from the source in a familiar and standard manner. This schema is known as the data source view (DSV), and it isolates the cube from changes made to the underlying sources of data.

Dimensional Model

This model provides the framework from which the cube is designed. Included are the measures (facts) that users need to gain measurable insight into their business and the dimensions that users use to constrain or limit the measurements to useful combinations of factors.

Calculations (Expressions)

Often, a cube needs to be enhanced with additional calculations in order to add the necessary business value that it is expected to achieve. The calculations within the UDM are implemented by writing MDX expressions. MDX is a multidimensional expressions language that is to the cube as SQL is to the database. In other words, MDX is what you use to get information from a cube to respond to various user requests.

Familiar and Abstracted Model

Many additional features enhance the end-user analysis experience by making their reporting and navigation through the cube more natural. Again, like calculations, the model is often enhanced to include features not found in the data sources from which the cube was sourced. Features such as language translations, aliasing of database names, perspectives to reduce information overload, or Key Performance Indicators (KPIs) to quickly summarize data into meaningful measurements are all part of the UDM.

Administrative Configuration

With the cube designed and developed, the administrative aspects of the UDM come to the forefront. Often, administrative tasks such as configuring the security to be applied to the cube or devising a partitioning scheme to enhance both query and processing performance are applied to the UDM.

Analysis Services Architectural Components

Now that you understand the basics about the Unified Dimensional Model (UDM), we'll now discuss the various components that make up Analysis Services.

The Analysis Services server (msmdsvr.exe application) is implemented as a Microsoft Windows service and consists of security components, an XMLA listener, and a query processor (for MDX queries and DMX data-mining queries).

Query Processor

The query processor parses and processes statements similarly to the query processing engine within SQL Server. This processor is also responsible for the caching of objects, storage of UDM objects and their data, processing calculations, handling server resources, and managing transactions.

XMLA Listener

This listener component facilitates and manages communications between various clients and the Analysis Services server. The port configuration for this listener is located in the msmdsrv.ini file. A value of 0 in this file simply indicates that SSAS is configured to listen on the default port of 2725 for Analysis Services 2000 instances, 2383 for the default instance of SSAS 2005, and 2382 for other instances of SSAS 2005.

SSAS 2005 named instances can use a variety of ports. The SQL Server Browser keeps track of the ports on which each named instance listens and performs any redirection required when a client does not specify the port number along with the named instance. We highly recommend that you use a firewall to restrict user access to Analysis Services ports from the Internet.

XML for Analysis

XML for Analysis (XML/A) is a SOAP-based protocol used as the native protocol for communicating with SSAS. All client application interactions use XML/A to communicate with SSAS. This protocol is significant in that clients who need to communicate with SSAS do not need to install a client component as past versions of Analysis Services required (such as Pivot Table Services). As a SOAP-based protocol, XML/A is optimized for disconnected and stateless environments that require time- and resource-efficient access. In addition to the defined protocol, Analysis Services also added extensions to support metadata management, session management, and locking capabilities. You have two different methods to send XML/A messages to Analysis Services: The default method uses TCP/IP, and an alternative is HTTP.