New Analysis Services Features in SQL Server 2008 - SQL Server 2008

Users of Analysis Services in SQL Server 2005 will be anxious to know what is new in the latest release. For these users, we’ll quickly review some new features, before examining the Analysis Services architecture and helping new users get started with this exciting technology. For novice users, this section may be rather redundant, since all features are new to you, by definition. Nevertheless, it will be useful to see that Analysis Services is far from a static technology, but rather one in which Microsoft is constantly developing and innovating new ideas.

The improvements in Analysis Services in SQL Server 2008 fall into three categories: improvements to design, monitoring, and runtime operations.

Improvements in Design Tools

In Analysis Services in SQL Server 2005, Microsoft made significant advances in the usability of the design tools for building OLAP databases. However, user feedback indicated that users needed help and guidance on how to build best practices into their applications. Therefore, in Analysis Services in SQL Server 2008, the design experience now includes comprehensive best practices alerts. These alerts appear as blue and red “squiggly” lines in the user interface, which draw attention to warnings and errors, respectively. Think of these as being like the warnings displayed in the Microsoft Word environment to show spelling or grammar errors. This technique enables the user to continue working—it does not require any action on the part of the user—while flagging important issues. In this chapter, you will see these alerts in action in the “Configuring Dimensions” section later in this chapter.

In addition to these alerts, Microsoft has also radically revised some of the designers. You will find the Cube Wizard, described in the “Defining Cubes” section later in this chapter, to be much simpler than previously. In addition, the Dimension Designer now includes a special interface for developing attribute relationships. This is also described in the “Configuring Dimensions” section.

In this chapter, we describe some of the basics of MDX, the language used with Analysis Services. For the more advanced MDX user, Microsoft has introduced dynamic named sets. A named set is, in essence, a predefined subset of data values, such as “Top 10 Customers,” which can be used in an expression. In SQL Server 2005, named sets were always static—the top 10 customers were selected when the cube was processed. This meant that running a query such as “find the top 10 customers over 70 years old” would actually return the subset of the original top 10 customers who were over 70, which could be none of them. Dynamic named sets enable you to return the top 10 customers in the context of the query. Therefore, you can now easily and dynamically query for your top 10 septuagenarian customers.

Improvements in Monitoring Tools

For the advanced user, who already has cubes designed and in production, new features in SQL Server Management Studio help to monitor the performance of deployed databases. You can think of the monitoring features as being equivalent to dynamic management views for the SQL Server relational database engine. An Analysis Services administrator, working in Management Studio, can now issue an MDX query such as this:

SELECT
session_spid,
session_user_name,
session_last_command,
session_start_time,
session_CPU_time_MS,
session_reads,
session_writes,
session_status
FROM $system.discover_sessions
WHERE session_status = 1

The results give a detailed view of which sessions are running on the server and the resources they are using. Similar schemas exist for connections, sessions, commands, and objects touched by commands, affording a very comprehensive resource-monitoring solution.

Runtime Improvements

The improvements in Analysis Services in SQL Server 2008 are not just in the user interactions—whether for designers or administrators. The runtime engine has also seen substantial enhancements. Some of these are deeply technical.

For example, a technology known as subspace (or block) computation offers remarkable performance improvements for queries that return sparse result sets. Take the case of a query returning sales of all products in all stores on all dates. Typically, only a relatively small subset of all products are sold in all stores on all dates, so the result from this query will include many null (or default) values. Subspace computations optimize the engine operations required to answer this query, and they can show performance improvements up to 20 times faster in some cases.

Another significant runtime improvement for the advanced user is the ability to write back to a Multidimensional OLAP (MOLAP) partition (rather than a Relational OLAP partition). For users building interactive budgeting and planning applications, this again offers substantial performance gains.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics