Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Analysis Services 2005 delivers new features -- start to finish

Microsoft Analysis Services 2005 is designed to improve business intelligence performance in SQL Server. Among its new features are the Unified Dimensional Model, Data Source View, new aggregation functions and querying tools. Baya Pavliashvili brings you up to speed on MSAS while sharing some migration tips.

  In this series of tips about Microsoft Analysis Services (MSAS) 2005, I'll be discussing some of the new features...

available and how they affect migration from the existing applications developed with MSAS 2000. The new Analysis Services tool is considerably different from previous versions, so each tip will only cover a small subset of the new features.


   Unified Dimensional Model (UDM)
   Data source views
   New aggregation functions
   Failover clustering and multiple instances
   Querying and browsing tools
   Migration tips

Unified Dimensional Model (UDM)

MSAS 2005 introduces Unified Dimensional Model (UDM) – a concept that attempts to use MSAS for reporting as well as analytics. Traditional analytics lack flexibility but they provide faster response time than traditional reports. Traditional reports are flexible, but usually take a long time to generate. Let me elaborate on this idea and show you how it applies to different versions of Analysis Services.

Note: Many people who are relatively new to Analysis Services think that UDM is an actual feature found in MSAS 2005. Don't waste your time looking through SQL Server Management Studio (SSMS) or Business Intelligence Development Studio (BIDS) because you won't find it. UDM is simply a concept -- it is an attempt to marry reporting and analytics.

Dimensions built with OLAP Services 7.0 or Analysis Services 2000 aggregate data in hierarchical fashion. For example, typical date and time dimensions would show you a breakdown of sales by year, quarter, month, day and perhaps hour. Similarly, a customer's geographical dimension levels would include country, state, county, city, postal code and individual customer name. That type of hierarchical analysis performs very well, but what happens if you want to include some items on the report that don't easily roll up to other levels? For example, customer phone number or email address attributes can't be easily rolled up into this hierarchy and can't be easily reported through such a hierarchical structure.

With MSAS 2000, you can expose such attributes as member properties. Using member properties isn't necessarily difficult, but it makes reporting somewhat convoluted.

More on this topic:

 Furthermore, including numerous member properties on analytical views can impose a significant performance penalty. Reports built upon a relational data warehouse, on the other hand, are very flexible. You can include any column in any dimension table on your report; however, relational reports are considerably slower than analytical views you build on pre-aggregated data with Analysis Services 2000.

Fortunately, with MSAS 2005, you can include any column within your dimension table as an attribute of your dimension. Then, you can build multiple hierarchies within a single dimension to expose each attribute. Furthermore, you can build hierarchies even on such attributes as email or phone number. Technically, you could build multiple hierarchies within a single dimension in MSAS 2000 as well, but this feature wasn't used much.

If you wanted to expose member properties as a separate hierarchy, you typically would end up building virtual dimensions, which have several drawbacks. With MSAS 2005, you can expose each attribute included in the dimension as its own attribute hierarchy with a single level. So, if you'd like to generate an analytics view that includes customer email addresses and respective Internet orders, you don't have to build a virtual dimension (in fact, MSAS 2005 doesn't even support the concept of virtual dimensions). Such views will perform as well as the views built on a customer geographical hierarchy.

MSAS 2005 also allows you to analyze members of different hierarchies, of the same dimension, on rows and columns. For example, the following is a perfectly legal MDX query:

 SELECT {customer.[geography].[state].members} ON COLUMNS, {customer.
[income level].members} ON ROWS FROM [My Cube] WHERE (measures.[sales

That query lets you examine sales by customer geography and income level; it can highlight very interesting purchasing trends. Unfortunately, you cannot easily build such queries with previous versions of MSAS because they don't permit exposing members of the same dimension on multiple axes of the query.

Analysis Services 2005 supports building a single cube with multiple fact tables, or using new terminology, multiple measure groups. Previous versions only supported a single fact table per cube, which forced cube architects to build multiple cubes and correlate data stored in multiple fact tables using virtual cubes. Virtual cubes expose measures and dimensions from multiple non-virtual cubes.

The concept of virtual cubes in MSAS 2005 is obsolete because you can include multiple measure groups within a single cube. Each cube can peruse a subset of all dimensions available in the database; therefore, there is no longer a need for private dimensions -- all dimensions are considered shared. If you have used private dimensions with MSAS 2000, you're probably aware that they can cause problems because they require additional shared memory space. Historically, private dimensions were used when we didn't want to process a cube fully, when some of the shared dimensions had to be processed incrementally. MSAS 2005 has a number of options for dimension and cube processing, which I will discuss in a future article in this series on the features of MSAS 2005. You can learn much more about UDM directly from the Microsoft SQL Server web site.
Data source views

MSAS 2005 introduces a layer of abstraction between the relational data warehouse and cubes -- data source view (DSV). Why would you want to have such an abstraction layer? Well, a relational data warehouse isn't always built using a SQL Server relational engine, which means the business intelligence architect who is familiar with SQL Server might have to learn other RDBMS tools as well. Furthermore, the data warehouse architect might not be permitted to make changes to the relational schema. For example, the relational warehouse might be provided by a third party, but you might want to build your own cubes. DSVs allow you to define logical relationships between fact and dimension tables, create named calculations, named queries and more.

Performance tip: Be sure to always define primary key / foreign key relationships between your dimension and fact tables either in the relational warehouse or through DSV.
New aggregation functions

Previous versions of Analysis Services allowed you to aggregate your measures using SUM, COUNT, MIN, MAX or DISTINCT COUNT functions. These functions work well for some business scenarios, but not for others. For example, what if you work with a business intelligence application for a chain of retail stores and your measure is the level of inventory for each product? You can't aggregate such a measure using SUM, COUNT or DISTINCT COUNT function across the time dimension.

Aggregating inventory levels using the MIN or MAX function might not be exactly what you need. For instance, you might need the starting and ending inventory levels, as opposed to the most and least levels of inventory for each product. You'll be glad to learn that MSAS 2005 allows you to aggregate measures using FIRST CHILD, LAST CHILD, FIRST NON-EMPTY and LAST NON-EMPTY functions. Similarly, banking applications cannot use SUM or COUNT functions for account balances. Fortunately, MSAS 2005 allows AVERAGE aggregation, which you can use to calculate the average daily balance for each account.
Failover clustering and multiple instances

MSAS 2005 supports native failover clustering that the SQL Server relational engine has supported for several years. Much like the relational database engine, the clustering feature is exclusively for providing high availability and not for load balancing. You could have server A and server B with the same cubes -- and if server A becomes unavailable, server B automatically takes over.

MSAS 2005 also supports multiple instances of service on the same server. In fact, you can run MSAS 2000 and MSAS 2005 side by side on the same server, as long as MSAS 2000 is the default instance. Being able to run multiple instances is convenient because you can allow multiple development (or testing) teams to share the same physical server. You could, in some cases, successfully use multiple instances on the same server to separate read-only querying and processing functionality.

On the other hand, you should generally limit the number of instances running on a production server. Since all instances running on a given server share a pool of resources, you might encounter a scenario where one server is performing great and the others are starved for memory / processor resources.
Querying and browsing tools

Unlike previous versions, you can query MSAS 2005 cubes using SSMS, rather than having to build your own query tool or using an MDX (multidimensional expression) sample application, which left much to be desired. SSMS gives you a cube browsing tool that allows you to simply drag and drop dimensions and measures to the viewing pane. You can also write MDX queries, DMX (data mining extensions) queries and XMLA (XML for Analysis) within SSMS. Developing in the new set of tools is considerably easier and it automatically alerts you of any syntax errors.
Migration tips

So what should you look out for when migrating to MSAS 2005? From the user perspective, cubes will look very similar. For example, if you're exposing an analytical solution through ProClarity or Excel, your users can still pick the dimensions they want to use to slice data and the measures they want to examine. Since each attribute can be exposed through a single-level hierarchy, your users will be surprised to see many more "dimensions" choices.

Note: Excel sorts dimensions alphabetically, but all hierarchies for a given dimension are grouped together. Let's say you have a customer dimension with hierarchies called geography, marital status and contact information. Further suppose that your product dimension has hierarchies called category and type.

With previous versions of MSAS, your users would expect to see dimensions in the following alphabetic order:

  • Brand
  • Contact Information
  • Customer
  • Geography
  • Marital Status
  • Product
  • Type

With MSAS 2005, the same scenario will result in the following order:

  • Customer
  • Contact Information
  • Geography
  • Marital Status
  • Product
  • Brand
  • Type

The order changed because each hierarchy appears immediately below its parent dimension. Unfortunately, Excel doesn't provide a way to reorder the hierarchies other than including the parent dimension name in the hierarchy name. So if you rename the "Brand" hierarchy to be called "Product Brand," then hierarchies will look as though they're ordered alphabetically, as before.

Cube architects will see many differences. Here are just a few:

  • They'll have to get used to working with BIDS (which is a plug-in for Visual Studio) for cube development. With BIDS, architects can debug, build and deploy cube projects much the same way other Visual Studio projects work.
  • They will appreciate a much better MDX development and testing environment than what's in previous versions.
  • They can now expose any attribute as a hierarchy with a single level, without the workaround of virtual dimensions. In addition, they can now correlate multiple hierarchies of the same dimension within an MDX query.
  • They'll have to learn to use building cubes with multiple fact tables as opposed to using virtual cubes to correlate data stored in multiple fact tables.
  • They will have to create data source views and define dimension relationships to each measure group.
  • They can use new aggregation functions to build inventory, banking and many other types of applications with relative ease.
  • They can take advantage of multiple instances and failover clustering to increase the availability of their solutions.

As I mentioned in the beginning, MSAS 2005 has far too many new features to cover in a single article. Look for my next tip in which I will continue discussing new features and how they affect migrations.

Baya Pavliashvili is a DBA manager overseeing database operations that support more than one million users. Pavliashvili's primary areas of expertise are performance tuning, replication and data warehousing.
Copyright 2007 TechTarget

This was last published in March 2007

Dig Deeper on Microsoft SQL Server Analysis Services (SSAS)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.