|
Home
>> Insight
Using Microsoft SQL Server 2008® as
a Business Intelligence Platform
Craig McQueen
You may have
an opportunity to reduce costs and provide additional
functionality to your end users by utilizing your existing
investment in Microsoft SQL Server. The standard SQL Server
license includes many Business Intelligence capabilities that
are expensive add-ons with other database platforms.
SQL Server has a diverse set of Business
Intelligence capabilities. In fact, Gartner positioned Microsoft
SQL Server 2005 in the Leaders quadrant of the Gartner Magic
Quadrant for Data Warehouse Database Management Systems, 2008.
The database and BI functionality is even stronger with the
recent release of SQL Server 2008. The services provided within
SQL Server and integration points to other Microsoft
technologies are depicted in the diagram below.
Let’s review specific aspects of functionality required for a
Business Intelligence platform and see how Microsoft SQL Server® meets
those requirements.
Data Profiling
Creating
profiles of the data contained in your data sources is an
important first step to a BI initiative. It provides you an
understanding of what you will be faced with when integrating
the data. The data profile will form a basis for the rules you
need to implement for cleansing (the next step) and provides
input into the dimensional modeling exercise.
For SQL
Server, data profiling previously required a developer to write
code. Data profiling is now built in as a feature of SQL Server
2008. The database itself does not need to reside within SQL
Server 2008; you run data profiling on any data source including
text files. This makes a lot of sense because it is likely that
the data, at this point in your initiative, resides in other
systems.
Data Cleansing
Data
cleansing allows you to prepare data for integration into your
data mart or data warehouse. You may need to remove unwanted
data, align fields to a common description or conform a
dimension to other data sources. Cleansing is often known as the
‘Transform’ portion of the Extract, Transform and Load (ETL)
process.
SQL Server allows you to implement data
cleansing rules through integration services. It has convenient
processing aids such as fuzzy lookup. Fuzzy lookup can take
various field values that are similar and confirm them into a
single field value. Any exceptions can be moved to another
location for manual processing.
Data Integration
Data
cleansing has the rules for transforming data but there is still
work required to extract from source systems and load the data
mart or data warehouse.
SQL Server
2008 provides the framework through SQL Integration Services to
perform the Extract and Load process. It has full job control
and audit capabilities providing a flexible, robust environment
for this portion of the process. Integration services
scalability has improved with SQL Server 2008 with thread
pooling and enhanced lookup transformations.
OLAP
The heart of
a Business Intelligence solution is the OLAP system. Organizing
information into facts and dimensions and providing a fast query
mechanism to this data is the responsibility of the OLAP system.
SQL Server
implemented a comprehensive OLAP system with SQL Analysis
Services. SQL Server 2008 drives broader analysis with enhanced
analytical capabilities and with more complex computations and
aggregations. New cube design tools help users streamline the
development of the analysis infrastructure enabling them to
build solutions for optimized performance.
New MOLAP
enabled writeback capabilities in SQL Server 2008 Analysis
Services removes the need to query ROLAP partitions. This
provides users with enhanced writeback scenarios from within
analytical applications without sacrificing the traditional OLAP
performance. Writeback capability lets the user change the
information in an OLAP cube to perform forecasting or what-if
scenarios.
Reporting
A flexible,
robust reporting platform is key to providing end users the
information they need. The platform should be able to support
report distribution in a variety of methods, allow end user
ad-hoc reports and have a rich report authoring environment.
SQL
Reporting Services is the Microsoft reporting platform that
allows users to create and distribute reports. With SQL Server
2008, richer reports can be created by the addition of new
display components such as gauges and new charts. As well, the
rendering engine has been improved for better performance and
usability.
Data Mining
Once you
have your data in place you derive additional value by utilizing
data mining algorithms. Data mining allows you to do such things
as automatically cluster the data, detect outliers and forecast
future value.
SQL Server
has a number of data mining algorithms built into SQL Analysis
Services.
SQL Server 2008 has improved time series
support that extends the forecasting capabilities. Enhanced
Mining Structures deliver more flexibility to perform focused
analysis through filtering as well as to deliver complete
information in reports beyond the scope of the mining model. New
cross-validation enables confirmation of both accuracy and
stability for results that you can trust. Furthermore, the new
features delivered
with SQL Server 2008 Data Mining Add-ins for
Office 2007 empower every user in the organization with even
more actionable insight at the desktop.
What is still missing from SQL Server 2008?
Master Data
Management (MDM) is not part of SQL Server. MDM allows you to
institutionalize your data definitions in an automated system.
However, MDM is on the Microsoft roadmap. In 2007 Microsoft
purchased Stratature which has an MDM product. Technology
previews have been available through 2008 so we should see it
rolled into the SQL Server platforms in the next year or so.
In each of our BI engagements, we work with our customers to
go through a comprehensive questionnaire to assess each of these
areas. Although the list seems long and daunting, the good news
is that most organizations already have many of the necessary
pieces in place. Also, the introduction of more user friendly
tools, many of which are integrated into software that
organizations already own, means that putting the missing pieces
in place can be achieved more easily than ever before.
Craig McQueen is Director of Business Intelligence Solutions.
He can be reached at
cmcqueen@agorainc.com
|
