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.
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
