SQLServerCentral Article

Analysis Services 2005, the Year of BI

,

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.

http://www.pearlknows.com

Copyright © 2005 - All Rights Reserved.

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

author.

FOR MORE INFORMATION:

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating