SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Analysis Services 2005, the Year of BI

By Robert Pearl,

High atop a New York City office building, several people from diverse backgrounds gathered around a large table in a boardroom like setting for intense discussion. No, it wasn’t another episode of the Apprentice! Instead, folks with titles ranging from CTO, President, Programmer, Manager, Developer and Consultant all converged on the scene for an in-depth first look at SQL Server 2005 Analysis Services.

Hosted by the NY Database Professionals Council, one of the New York Software Industry Association’s (NYSIA) many SIGs (Special Interest Groups), this special event focused on some of the big differences between SQL Server Analysis Services 2000 and 2005, as well as some of the coolest features that make up part of SQL Server’s new BI toolkit.

First, some information about our friends at NYSIA. NYSIA is a non-profit trade organization, the leading trade association for software, information technology, and Web development companies in the New York City area. Its mission is to promote and support the growth of the software industry in this region. Check them out at http://www.nysia.org, and if you’re in the New York area, do register to attend one of their marvelous events.

As Microsoft gears up for its release of SQL Server 2005, we find that SQL Server is no longer just for DBAs and that Business Intelligence is no longer limited to the upper echelons of management. While traditional business intelligence systems were technically complex, and huge budget busters, prohibiting the flow of information throughout, these major changes in the way the technology is used and implemented, AS 2005’s new features will make it easier for the common user to take advantage of this evolving and fast growing phenomena known affectionately in the industry, as BI.

Indeed, the Microsoft’s key objective is push SQL Server 2005 deeper into the enterprise, by offering a comprehensive end-to-end integrated business intelligence platform, to reach every level of the organization. Once the technology was out-of reach for most, Analysis Services 2005, combined with its already released Reporting Services, is shaping up to be a cost-effective, highly flexible, scalable, customizable and high performance solution that will bring BI to all employees’ desktops in their every day work life.

By no means is BI a new concept. It is more than just about technology; it is about process. Now that technology has caught up with demand, and the growing need to provide instant information readily available to pervade the entire organization throughout, Microsoft’s enhanced BI tools, will surely aid with the goal of pervasive business intelligence (PBI), to support and improve the decision-making process. And making faster and better decisions, based on the speed and format at which data is collected, sorted by relevance, and delivered, is key to a company’s survival and ability to stay competitive.

Back in the boardroom, Microsoft’s Jaime Basilico, Senior Database Technology Specialist, and Citigate Hudson’s Mark Frawley, Senior Software Developer, sought to give us a head start on SQL Server Analysis Services 2005 by focusing on the changes to the developer user interface, architecture and programming model of AS, as well as a side-by-side comparison of 2000 vs. 2005.

While there is so much to write about on the topic of BI, and the new suite of tools being released by Microsoft – made up of Reporting Services, Integration Services (formerly DTS), and of course, Analysis Services - we will stick with the scope of the presentation that I attended. Since AS 2005 itself is so extensive, we will need to narrow the focus to some of the major enhancements. Later in the article, I will include some resource links to more information about other related topics, for your perusal at your leisure.

Back in the day, we used to categorize data processing and data retrieval in to two camps, OLTP (On-line transaction processing) and OLAP (On-line Analytical Processing). When Microsoft introduced OLAP services bundled with SQL Server version 7.0, it was to revolutionize decision support systems, by putting data warehousing technologies in the hands of the mainstream user. Although SQL Server's OLAP Services provided the foundation for mainstream data warehousing as well as a back-end data repository, SQL Server didn't include query, analysis, and decision support tools.

Highlighting the differences between AS 2000 and AS 2005, client tools such as the Analysis Services Manager (ASM), Query Analyzer (QA), and Enterprise Manager (EM), has now morphed into a single suite of management and development tools called the Business Intelligence Development Studio (BIDS), and SQL Server Management Studio (SSMS). Otherwise known as the “BI Workbench” and the “SQL Workbench”

With Microsoft’s State of the Art Analysis Services 2005, they provide the most comprehensive integrated business intelligence, data mining, analysis and reporting solution. The gap between back-end and front-end technology has finally been bridged in one smart consistent interface, allowing higher development standards, as well as a better user experience.

Moving on to some of the key differences in the User Interface is the approach to data modeling. In AS 2000, the user must be connected to an existing AS server before you can even perform any other function. AS 2005 introduces Data Source Views, which once established, you could work with cube models, without even being connected to the original data source. A DSV differs from the cube schema in Analysis Services 2000 in that the DSV is a logical representation of the source data from which multiple cubes can be defined, whereas the Analysis Services 2000 cube schema is a physical representation of relationships between tables for each individual cube.

With AS 2000, each cube that is built is based on a single fact table, derived from a single data source, while AS 2005 can use multiple various data sources to build out a DSV that can contain tables from all of them. You can even create named arbitrary sql queries, and use them as a data source to build your cubes.

Each object in a migrated data source view is migrated as a named query. New cubes can be defined on the migrated DSV after relationships between the named queries are defined. For improved consistency and administration, DSV’s can be shared between cubes and Data Transformation Services (DTS)

Creating your cubes in SQL Server 2000 was a manually intensive process, even using the wizard. Before designing a new cube, you needed to set up a database. More specifically, in Analysis Manager, you needed to set up an OLAP database.

The lowest level of detail for the values that we choose as measures typically resides in a more-or-less relational fact table. While operational data often comes from a variety of original data repositories, the most common way of managing relational data for multidimensional reporting is with a star schema-based warehouse/mart, or similar storage concept. A star schema in its simplest form consists of a single fact table, linked to multiple dimension tables through a common key or keys shared between each member of a linked-table pair.

If that isn’t enough to make your head spin, let us take comfort and joy in the knowledge of the wonderful new creation known as the IntelliCube. The IntelliCube will automatically create cubes from relational schema's - no need to define a star schema, no need to define hierarchies, no need to define measures, no need to type in proper names for things. It’s all done automatically in a few clicks. When creating a cube in a data warehouse project, the Cube Wizard will include an option to enable one-click-cube detection and suggestions. This option will examine the relationships in a data source view and make suggestions for fact tables, dimension tables, and measures. But, hey, if you still insist on being a multidimensional geek, you can still manually build your cube using the old Cube Editor from AM 2000.

As for metadata storage there is no longer a metadata repository, eliminating the need for a database, and certainly rid ourselves of the Access db. Instead, metadata is stored in XML documents, which means they can easily be source-controlled. This, as the presenters pointed out, will facilitate team development, as in other company-wide projects. Speaking of projects, to prove that BI technology is not just for techies, the new term for the basic element of deployment is the solution, which is a collection of one or more, you guessed it, projects. So, we go from the ASM “database” to the BIDS “solution” and “project”. AS 2005 is only one type of “project”, where Reporting and Integration Services are others. You can easily create a solution that incorporates all three of these BI components, using one standard interface via the BIDS. An example would be a data-mining project. In this case, one would design multidimensional data models via OLAP, refresh and update your models periodically via DTS, and generate detailed up-to-the-minute reports via Reporting Services. As the technology becomes more user-friendly, to aid in the goal of making BI more pervasive in the organization (remember PBI, see above), the lexicon changes as well, bringing the terminology in sync with the business side.

In terms of deployment, no longer will this collection of connection info, cubes, shared dimensions, data-mining models and roles, be the primary unit – the database archived as a CAB file - of distribution between servers. Hard-coded data source definitions, made configuration unmanageable, and was virtually non-existent in AS 2000. This, as well as the Archive and Restore method of deployment will be discarded in favor of the more sophisticated configuration and deployment model in AS 2005. The new model will allow for greater portability between servers and different environments, without the need to make manual changes.

One of the most important major changes to the architecture of Analysis Services, is -BIG drum roll - the Unified Dimensional Model. (This link will bring you to Microsoft’s ‘Introduction to UDM, with an excellent overview of its features, and some great graphical representations.) UDM combines the best aspects of traditional OLAP-based analysis and relational reporting into one-dimensional model. (See article: UDM: Best of Both Worlds) In the old AS 2000, one could note the distinctions among different data sources, such as flat file, relational cube, and web-service data sources. The role of UDM is to provide a bridge between the user and all types of data sources, whereby the data consumer sees a single, multi-dimensional interface.

In addition, a UDM has four key elements: heterogeneous data access, a rich end-user model, advanced analytics, and proactive caching. These elements in turn allows for the user model to be greatly enriched, captures business logic, provides high performance queries supporting interactive analysis, and delivers low-latency reporting for OLAP. These features will enable more complex calculations, and uses a built-in Key Performance Indicator (KPI), which give businesses important metrics used to measure corporate benchmarks. The main components of a KPI are: Value, Goal, Status, and Trend. The UDM allows such KPIs to be defined, enabling a much more understandable grouping and presentation of data.

One of the great boons to SQL Server AS 2005, and listed as one of the top ten features for Business Intelligence, is proactive caching. As laborious as it was in AS 2000 to finish processing cubes before one could even use it, the need to maintain OLAP stores is eliminated, and instead are cached and managed automatically. This great feature allows the user to immediately query the cubes, as it synchronizes and maintains an updated copy, which gets populated automatically as they are being used.

Even though using the cube as a cache offers great benefits to performance, especially high-speed querying, there are several things one must take into account. As our presenter pointed out, if we want to use the cube as a cache, we must be concerned about stale or obsolete data, and how often and when the cache gets rebuilt. After every update, there will often be new data and new dimension members. Another question raised is, where exactly are the queries being directed to when the cache is being rebuilt – to the old data in the cache, or the source data?

Well, in AS 2005, the user can control the latency and the life of the data, by setting the caching policies that specify when the cache is rebuilt. For example, when the data changes, or at periodic intervals, regardless if the data has been updated. Based on the needs of the business, you can now finely tune your cubes to balance performance with latency of data. (As, I will reference the link below, p.20-25, of the .pdf presentation has graphical walkthrough of Proactive Caching, and p.27 displays a table of properties and description of UDM’s MOLAP caching policy)

We can’t end our discussion without talking a little about the new Programming Model of AS 2005. The language used to query Analysis Services cubes, MDX (Multi Dimensional eXpressions), is now simplified, both its syntax and calculation model. The new model allows for server-side programming, and as part of MS’s .Net platform, is integrated with CLR (Common Language Runtime). Analysis Management Objects (AMO) replaces the DSO object model, allowing for BI objects to be created programmatically. For those hard-core developers, you may want to look up the following topics for enhancements to MDX in AS 2005:

  • Querying

  • Scripting

  • Debugging

  • Calling SP/CLR components

You could even use MDX scripting to assist in the migration from AS 2000-2005. However, if you’re like me, you will more than likely want to use the MigrationWizard.(Click this link for MS’s info on same.) Included with the AS 2005 install, the wizard can be used to migrate the metadata from one or more databases from a source server to a destination server. The key thing to remember, as stated in the aforementioned link, is that after migration, the database must be processed from the original data source before queries can be completed.

With so much to talk about on the topic of BI and Analysis Services 2005, indeed as stated by presenter Mark Frawley, who has extensively tested and worked with the Beta Versions of AS 2005, “both current users of Analysis Services and users considering it as the core of their BI technology approach will find many reasons to give serious consideration to upgrading.”

With SQL Server 2005 Analysis Services, it moves into the realm of real-time analytics. From scalability enhancements to deep integration with Microsoft Office, SQL Server 2005 will help extend business intelligence to every level of your business.

We covered a lot of ground in this article. In addition to the terrific and detailed presentation hosted by NYSIA, I researched and compiled various articles and materials to bring this piece to you. I tried to touch on the impact of Business Intelligence on all of us, and the how the onset of new technology will facilitate that impact. The assortment of industry professionals attending this meeting reflects the growing reality of pervasive business intelligence in the enterprise.

Yes, a new day has arrived, and 2005 is the year of BI.

Written by: Robert Pearl, President
Pearl Knowledge Solutions, Inc.

Copyright © 2005 - All Rights Reserved.

Note: Not to be reprinted or published without express permission of the author.


http://www.nysia.org/events/past/2004/2004129dbprof.pdf - The complete NYSIA's NY Database Professionals Council PowerPoint presentation in .pdf format.

http://supportech.insa-lyon.fr/Download/HOL/TechEd04/SQL/SQL_2005_Analysis_Services.pdf - If you want to work with SQL Server AS 2005 hands-on, check out this hands-on lab.

msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/IntrotoUDM.asp - Introduction to the Unified Dimensional Model (UDM)

http://www.devx.com/dbzone/Article/21539/0/page/1 - The original article written by Mark Frawley, which probably served as the basis for the presentation

msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/OvASDMEnvr.asp - Overview of the Analysis Services 2005 Development and Management Environments.

http://www.windowsitpro.com/SQLServer/Articles/ArticleID/41240/pg/1/1.html - UDM:- the Best of Both Worlds

Total article views: 15519 | Views in the last 30 days: 6
Related Articles

SQL Server Analysis Services

SQL Server Analysis services terminates unexpectedly


SQL Server 2005 Analysis Services

SQL Server 2005 Analysis Services


SQL Server Analysis Services not starting

SQL Server Analysis Services not starting


(SSAS) SQL Server Analysis Services Tabular Basics

What is (SSAS) SQL Server Analysis Services Tabular Mode? SQL Server 2012 ships Analysis Services...


Analysis Services deployment wizard

Analysis Services deployment wizard