Analysis Services Fundamentals - SQL Server 2008

In this section, we will look at the architecture of Analysis Services, as established in SQL Server 2005 and continued in SQL Server 2008. We will also explore some of the fundamental features of Analysis Services.


Figure gives a high-level view of the Analysis Services architecture. A main component is the Unified Dimensional Model (UDM), which is a cohesive dimensional model representing relational and multidimensional data repositories. The UDM provides a bridge (standard representation) from a client such as Excel or SQL Server Reporting Services to a diverse set of heterogeneous data sources. Rather than accessing the data sources in their native languages (such as specific SQL dialects), the UDM enables client applications to issue commands against the UDM.

Analysis Services architecture

Analysis Services architecture

A large number of Analysis Services features build on the UDM. These include hierarchies, categorizations, time representations, language translations, and perspectives. Even advanced functionality such as proactive caching and analytics are intrinsically part of the UDM and therefore available to Analysis Services.

As XML and web services become ever more important, Analysis Services supports these contemporary technical mainstays in a comprehensive manner. An Analysis Services server behaves as a web service, and it should come as no surprise that the language for administering this server is XML-based. All client communication with the Analysis Services server is via the standards-based XML/A protocol.

Microsoft is continuing to support a variety of technologies aimed at backward compatibility with the functionality provided by XML/A (ADOMD, ADOMDB.NET, OLE DB for OLAP, and Win32). XML/A is actually a subset of the Analysis Services Scripting Language (ASSL), where XML/A sends action commands such as Create, Alter, and Process (to create a cube, alter a cube, and process a cube). ASSL is also the language used to specify the objects within Analysis Services. XML/A handles commands, while the rest of ASSL is a Data Definition Language (DDL) for Analysis Services objects.

BIDS, the development environment for SQL Server BI, uses Visual Studio’s development style (solutions, projects, and source files). The source files associated with a BIDS Analysis Services project contain the metadata associated with Analysis Services objects. You can check these files in to standard source code control (such as Visual SourceSafe or Visual Studio Team Foundation Server). The metadata language is XML and is stored in files. SQL Server Management Studio and BIDS use XML/A to communicate with Analysis Services.

Microsoft introduced proactive caching in Analysis Services in SQL Server 2005 to empower low-latency applications with minimal managerial overhead. The primary purpose of proactive caching is the management of data obsolescence. For example, you can configure the proactive cache to update when the underlying data changes. If the cache is in the process of updating, Analysis Services is sophisticated enough to direct a query not to the cache, but instead to run the query against the underlying data source. Proactive caching works optimally against a relational database that provides notifications when the underlying data has changed.

Analysis Services in SQL Server 2008 does not cache entire dimensions in memory. Just as an operating system keeps certain pages in memory and has certain pages swapped out to disk, Analysis Services uses physical memory and disk space. The benefit of this approach is that Analysis Services now supports unlimited dimension sizes. For example, Analysis Services in SQL Server 2000 hit constraints depending on the physical RAM available. Analysis Services can now take advantage of caching (keeping results partially on disk) and is therefore not RAM-bound.

Development Environment

BIDS is the primary environment for Analysis Services development. This application is actually a version of Visual Studio 2008 that ships at no extra cost with SQL Server 2008.

BIDS supports offline development before the developer deploys an Analysis Services project to a server. This offline development is possible because BIDS works with snapshots of the schemas from the data sources accessed. The Analysis Services objects it creates (cubes, dimensions, fact tables, and so on) are just local copies of these objects. The developer must deploy them to a specific Analysis Services server. The true benefit of this is that development can take place even if the Analysis Server is not running locally and even if a developer is not connected to the network.

On top of offline development, BIDS provides administrative dialog boxes and wizard-based support for the creation of computations. These user interface enhancements automate the manipulation of computations ranging from time manipulation and interpretation, to account intelligence, financial aggregation, support for multiple currencies, and semi-additive measures. The alternative to using the dialog box and wizard-based support would be hand-coding computations.

Another benefit of BIDS is support for multiple configurations. This enables you to deploy projects against multiple instances of Analysis Services. Also supported are localized objects and translations, soAnalysis Services supports deployments in multiple languages.

Analysis Services Objects and Concepts

Analysis Services includes many features of interest. Here, we will review some of the most important objects and concepts that are especially useful for effective BI implementations. We’ll cover some more concepts that are particular to OLAP applications in the next section.

Data Source Views

Data source views (DSVs) form a virtual layer on top of a data source and contain a subset (just the pertinent elements) of the objects associated with a data source. For example, a DSV could contain a subset of the tables and views pertaining to payroll processing. This virtualization enables developersto rename objects, and to create calculated columns and named queries. An added benefit of named query support is developers gain access to what are fundamentally views without requiring a user to have CREATE VIEW permissions on the data source.

Multidimensional Expressions

As noted earlier, MDX is the language supported by Analysis Services for calculations and security rules. Developers use MDX to query Analysis Services and to build calculations and key performance indicators.

Key Performance Indicator Framework and Perspectives

Key performance indicators (KPIs), introduced in SQL Server 2005, are server-side objects that can graphically represent a value, a desired goal for that value, the status of the indicator (good, bad, and so on), and the trend of the measure.

For example, a brokerage house could use a thumb-is-up or thumb-is-down graphic to indicate whether a stock should be purchased or sold. The underlying algorithm to determine this could be quite complex (value, goal, status, and trend), but the KPI (the corporate measure) can be quite simple: buy (thumb-is-up graphic) or sell (thumb-is-down graphic).

A trend is directional behavior, which can be associated with a graphic, such as a thermometer, a fuel gauge, or an arrow pointing up or down. For example, a car might be winning a race (the status of the KPI), but another car could be gaining rapidly (the trend). Developers define the value, goal, status, and trend of a KPI using the MDX language.

Another feature that supports a user-specific context of information is perspectives. A perspective is a logical collection of attributes, user-defined hierarchies, actions, and measure groups optimized to provide a given category of user a highly sophisticated, customized view of the underlying data. You can think of perspectives in Analysis Services as being rather like a view in the relational engine.

Common Language Runtime (CLR) Integration

At times, the Analysis Services developer may still need to take advantage of computations performedin a high-level language such as the common language runtime (CLR) languages C# and VB .NET. In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers.

You can develop such CLR add-ons to SQL Server using Visual Studio 2008. It would be possible for the same Visual Studio solution to contain both an Analysis Services project and the C# or VB .NET project for the user-defined functions utilized by the Analysis Services project—one application and one development environment.

Analysis Management Objects (AMO)

From an administrative standpoint, Analysis Management Objects (AMO) expose an API for creating and maintaining Analysis Services objects. AMO can handle administrative tasks including security and processing, and even backup and restore.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics