Analysis Services Engine Enhancements

One of the primary areas of enhancements with Analysis Services has been in the Analysis Services engine itself. Many of these enhancements address limitations that were present in the SQL Server 2000 version of Analysis Services, and others have brought the product forward into totally new areas of functionality.

Multiple Instance Support

With SQL Server 2000, Analysis Services did not provide multiple instance support even though the relational database engine was able to support up to 16 instances. Multiple instance support is especially useful for application service providers (ASP) when multiple customers each maintain their own database instances that are all located on a shared server. Earlier versions of Analysis Services could not really be deployed in these situations. With SQL Server 2005, Analysis Services now provides support for up to 50 instances per server. Instances of SQL Server 2005 Analysis Services can also be set up to run side-by-side with previous versions of Analysis Services.

Failover Clustering Support

SQL Server 2005 has extended its support for failover clustering to Analysis Services. SQL Server 2000 Analysis Services did not support failover clustering for Analysis Services out of the box. The SQL Server 2005 Analysis Services installation process is cluster aware and can seamlessly install Analysis Services on cluster nodes. With SQL Server 2005, failover clustering is now aware of Analysis Services, and SQL Server Agent and Notification Services make failover clustering a complete server-level availability solution.

Integration with the .NET Framework

Just like the SQL Server 2005 relational database engine is integrated with the .NET Framework, Analysis Services also provides .NET integration. The new Analysis Services .NET integration enables full support for XML and Simple Object Access Protocol (SOAP) support as well as support for creating stored procedures and triggers in .NET languages such as Visual Basic and C#.

Unified Dimensional Model

One of the biggest changes for Analysis Services in SQL Server 2005 is the introduction of the new unified dimensional model. UDM, which can be viewed as the next evolutionary step in OLAP processing beyond cubes, promises to provide a unified reporting model by combining the best of OLAP and relational reporting. With previous technologies, some relationally based reports such as orders and invoices were very difficult to produce using OLAP tools. Likewise, the high-performance ad hoc query style of reporting that OLAP supports cannot be well adapted to relational reporting. The UDM provides a common ground that can handle both of these very different types of requirements. You can see a high-level overview of the relationship between applications and the UDM in Figure 10-1.

Figure 10-1: The unified dimensional model

In SQL Server 2005 the cube is essentially the external representation of the UDM. While a cube is still presented to the reporting application under the covers, the mechanism for accessing the data is quite different. The UDM contains metadata that enables a number of capabilities that are not possessed by the MOLAP, ROLAP or HOLAP cubes that were present in SQL Server 2000 Analysis Services. In SQL Server 2005, OLAP applications connect to Analysis Services using XMLA and query the UDM, which can be built directly over both analytical and relational data sources.

Proactive Caching

The UDM can automatically cache data, providing very fast MOLAP-style data access without the accompanying need to explicitly define MOLAP storage. Using slider style controls you can control the latency and lifespan of the data in the cache. A setting of zero latency means that all of the data will be cached as MOLAP data. The data lafttime slider controls how long the data will live in the cache. You can specify that lifetimes of varing intervals such as daily, weekly or monthly and when the interval expired the cache will be cleared. Under the covers the proactive cache uses an on disk structure that’s similar to a MOLAP cube.

Proactive caching really addresses the cube deployment pain points that were present in SQL Server 2000 Analysis services. With SQL Server 2000 Analysis Services a cube need to be processed (populated with data) before it could be deployed. For large data sets this processing time could be lengthy. Proactive caching solved this problem by enabling you to deploy cubes before they are processed. The cube is automatically populated as requests are made for the data.

Trigger Support

The addition of trigger support in SQL Server 2005 Analysis Services is another long-anticipated enhancement. Like their relational database counterparts, Analysis Services triggers can fire stored procedures when a specific database action occurs. Analysis Services triggers run synchronously, which means that the job that fired the trigger is blocked until the triggered stored procedure has executed.

Trace Support

Another important new enhancement for SQL Server 2005’s Analysis Services is support for tracing. Trace events are asynchronous and are used to monitor system performance and troubleshoot problems.

Scripting Support

SQL Server 2005’s Analysis Services now supports the creation of Analysis Services databases and objects via scripting. SQL Server 2005 provides the new XML-based Object Definition Language (ODL), which can be used to create, modify, and delete Analysis Services database objects. ODL can also initiate such server actions as cube processing and comparing database versions. More information about ODL is presented later in this chapter.

Localization Enhancements

Another major enhancement in the Analysis Services engine is improved localization support. SQL Server 2005’s Analysis Services engine is able to store object information in multiple languages. This allows Analysis Services applications to display both cube metadata and business data in the end user’s native language. The Analysis Services engine also supports default language settings for client applications. This enables multilingual client applications to automatically use the appropriate language to display object information.

Orphaned Fact Table Row Support

Another limitation of SQL Server 2000 Analysis Services that SQL Server 2005 addresses is the problem of orphaned rows. SQL Server 2000 Analysis Services ignored rows that had an undefined member for a dimension. This resulted in a mismatch of the cube totals when compared with the data from the data source. Analysis Services in SQL Server 2005 allows you to specify how the Analysis Services engine handles fact table rows that have missing dimension information. You can choose either to continue to ignore the missing information or to force Analysis Services to create an Unknown dimension member for a fact table row that is missing dimension information.