Analysis Services 200x Then and Now ...

Since most companies spend their first development efforts on OLTP applications, with BI coming around after the fact, you may be left with a number of different versions of Analysis Services to work with based on the heterogeneous nature of your environment. i.e. you may have some applications on SQL 2000, even more on 2005 and some you have converted to 2008.

This likely left you with licenses and installations of Analysis Services across your farm and now you'd like to make the leap into a full production BI environment. One of the first steps you need to take is gaining an understanding of what each version will offer and how they will work together.
First it's important to understand some of the critical differences arrived at through the evolution of the product over several versions. Just like SQL Server went through some major changes from 2000 - 2008 so did its associated products like SSIS and SSAS.

If you're using SSAS 2000, you are probably working with the Analysis Manager, which is an MMC snap-in serving as the development and management environment for SSAS 2000. Analysis Manager is rather limited in it's features and functionality, but it does allow you to browse SSAS data in the cube.  If you have begun writing and developing in MDX, then you will likely have used the MDX Sample Application that shipped bundled with SSAS 2000 as well.
 
SQL Server 2005 and 2008 have introduced a new separated environment toolset for developing and managing Analysis Services (and all of SQL Server for that matter). This included Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS). BIDS is essentially Visual Studio with a suite of new projects to enable business intelligence development on SSIS, SSAS, and SSRS.  SSMS is a management environment where you can query and work with data, objects, and OLAP environments. It is also the main tool for managing jobs, backups, databases, etc.. (Typical DBA management items)

 Some of the other limitations of SSAS 2000 included:
 
Certain scenarios were difficult or impossible to model due to a lack of specific support.

1. There were significant size limitations on many of the types of object like dimensions, measures, etc..
2. All SSAS databases were loaded at startup, which could cause serious performance problems with a large number of databases.
3. The thick client model that was used made it difficult to scale and stored the metadata in access or SQL Server databases, not providing for a true OLAP experience or performance.
4. The older style backup format, used by SSAS 2000 was limited to only 2GB.

  

SQL Server 2005 made major improvements and overcame most of the functionality ,limitations experienced in SSAS 2000. For Instance:

1. Most of the size limits have been removed for all practical purposes.
2. SQL Server profiler was also enhanced in SQL Server 2005. The ability to trace, or profile, Analysis Services databases and queries was added. 
3. Better manageability, scalability, security and extensibility are all a core part of the new architecture.
4. Common Language Runtime is natively supported (CLR).
5. Metadata is represented as XML and is stored in SSAS instead of in Access or SQL databases.
6. New backup format provides for significantly increased backup performance for backups over 20GB. (remember that your cube is usually 1/10 the size of your warehouse. (This a combination of averages and the compression functionality in SSAS)
7. New OLAP and data mining features were added providing new functionality for modeling, analysis, and forecasting.
8. New thin-client architecture was introduced improving integration into 2 and 3 tier environments.

 

Analysis Services 2008 improved on this even more with the following enhancements.

1. BIDS now has informational warnings that alert during design time through a series of AMO objects.  This is extremely helpful when working in dimension editor and other areas where it is common to make mistakes. (See my next article on getting rid of those repeated messages.)
2. SSAS 2008 now provides additional trace events and performance counters for PERfmon (or Windows Resource Manager) depending on which version of Windows Server you are running. These deliver even more insight into the performance and activity in your cube.
3. Built in optimizations and aggregation functionality work to improve query performance in the background without user intervention.
4. For those of you who have gotten used to managing your relational environments with then new DMV's (Dynamic Management Views), they are now extended to SSAS 2008.
5. Full features attribute relationship editor for dimension design enables the process to be a much more fluid and understandable event.

 

Overall, SSAS 2008 provides the best combination of design features, scalability, and performance for your enterprise, but SQL 2005 is a good step in the right direction if you don't have any SQL 2008 in your environment yet.  Remember, you can always start your 2008 environment with SSAS. Make BI the priority will only make your business more intelligent.

Stay tuned for more great BI content here on BIDN !