Analysis Services Integrates with Reporting

I've worked with SQL Server 2005 Analysis Services for several months now, and recently I had the opportunity to talk with Ariel Netz, an Analysis Services Group Program Manager and a core member of the original OLAP Services team. Netz and I discussed some big news that a lot of people don't already know about: the integration of OLAP and reporting.

Traditional ad hoc analysis focuses on OLAP, and reporting targets relational data stores. The two methods can create significantly different results. In Analysis Services 2005, many OLAP constraints have been removed so that you can model many-to-many relationships, multiple fact tables, and dynamic hierarchies--and all using less memory. Those features, in addition to XML for Analysis (XMLA), ADOMD.NET, and advances in the MDX query language, let SQL Server 2000 Reporting Services, Report Builder (the new ad hoc reporting tool for Reporting Services), Microsoft Excel, and other third-party analysis and reporting tools meet various requirements from a single database--with Analysis Services providing that unified view or single version of the truth. It's understandable why the OLAP part of Analysis Services (there's a Data Mining part too) is called the Unified Dimensional Model (UDM).

Netz also talked with me about some lesser-known changes in Analysis Services 2005. Many features that we've come to expect in the SQL Server relational engine are now extending to Analysis Services, such as SQL Server Profiler, the ability to kill queries, support for multi-instancing, clustering, and full backup and restore capabilities. Some tasks that currently require complex workarounds are simpler in SQL Server 2005 Analysis Services. For example, Analysis Services 2005 will automatically process cubes in parallel when possible. Another new feature is the Server Sync feature, which lets one server perform the processing, then distribute its database to other query servers. The new Analysis Services includes declarative MDX scripts, lets you aggregate DISTINCT COUNTS, and has a new CASE statement in MDX. No more of those endless nested IIF statements--hallelujah!

Analysis Services 2005 also includes a lot of highly functional wizards that handle everything from rapidly creating a UDM to generating common-time series, such as moving averages or year-to-date calculations. As an administrator, I like to hear that new administration security roles have been added that let the operations team perform maintenance without letting them see the actual data.

And because I'm a BI developer, I'm impressed by the new features that let development teams collaborate more effectively during projects and the solution-deployment model that the teams are using. Moving BI development into Visual Studio has added rich debugging, easy integration with source control, and the ability to deploy to different servers (i.e., development, staging/test, and production). Please let me know if there's something you like about Analysis Services 2005 that I missed.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.