The arrival of SQL-Server 7.0 provides an awesome opportunity
to bring decision support to a vast new market that previously couldn't afford
Richard Creeth, Creeth, Richman & Assoc.
The arrival of the new Microsoft OLAP server is just one piece of Microsoft's
assault on decision support. Microsoft also ships new client side tools as part
of the SQL Server Bundle and promises to ship more with the next release of
Office, BackOffice and Visual Studio. Microsoft's new OLE DB for OLAP API built
into the OLAP server has been widely accepted, and third party decison support
vendors are tripping over each other in their rush to market with new client
and server products that will be able to expand upon the tools that Microsoft
is offering. As one soltutions integrator told us, "I am presented with
such an embarassment of riches that I am forced to focus my attention upon only
those products and features that can be of most benefit to my customers and
my bottom line"
Microsoft PivotTable Service (MPS)
The second major piece of Microsoft's new OLAP initiative is a client side
component know as the Microsoft PivotTable Service (MPS). Currently bundled
as part of SQL Server 7.0, it will also be shipped as part of the next release
of Microsoft Office, Visual Studio and, of course, Back Office. MPS is a client-side
calculation engine that can be used to cache data locally to improve performance,
reduce network traffic and to enable analyses to be performed while disconnected
from the OLAP Server. MPS and the OLAP server share a optimized proprietary
protocol, not OLE DB for OLAP, that lets them work together in concert as a
single client/server OLAP solution. Solutions integrators can build upon MPS
to provide customized client side OLAP functionality, or in cases where too
much data would need to be downloaded to the client can chose to run MPS on
Client applications connect to the MPS using either OLE DB for OLAP or another
new Microsoft interface called ADO MD (Active Data Objects Multidimensional)
which is a simpler interface that will be more easily mastered by less experinced
Microsoft is also working hard to add OLAP capabilties to the next version
of Office, (Office 2000) which is due to ship a few months after SQL Server
7.0. Individual applications will be provided with support for the OLE DB for
OLAP API, providing them with access to any application or data compatible with
the new API. As we said earlier, Microsoft also intends to bundle the Microsoft
PivotTable Service into Office 2000 Office, which will provide millions of users
with basic OLAP functionality out of the box.
Individual applications are also being provided with new features to allow
them to play better in this new OLAP world. For example, Microsoft is beefing
up the Pivot Table features included as part of Excel. This will allow Excel
to act as a desktop OLAP product in it's own right, and provide a readymade
client for the new OLAP server.
Access is also being redesigned for OLAP and SQL Server 7.0. For example, users
will be able to bypass the exising Jet engine and connect directly to SQL Server
via the OLE DB interface.
Microsoft has created a new language for specifying OLAP queries, dubbed MDX.
Essentially a multidimensional equivalent of SQL, MDX provides a level of control
few solutions integrators will ever need. C and C++ programmers building commercial
products will also be able to connect at the OLE DB for OLAP level. Far more
common with be Visual Basic level programers who attach to OLE DB for OLAP applications
via a new DLL Microsoft has created for exposing multidimensional data. Dubbed,
ADO MD, the new DLL wil be easily accessible from within Visual Basic, C++ or
J++. ADO will also be accessible from within Excel's VBA, allowing Solutions
Integrators to build sophisticated applications which directly access OLE DB
for OLAP API servers from within Excel.
The Transformation Tool
SQL Server 7.0 includes a new utility dubbed the DTS (Data Transformation Service.
While in no way as powerful or sophisticated as high-end transformation tools
such as Informatica, it does provide the basics for free.
"You've got some horrendous data out there. 50 percent of my time is spent
trying to integrate data from multiple sources. There are some tools out their
from vendors like Sagent <www.sagenttech.com>, Informatica <www.informatica.com>
and Prism <www.prismsolutions.com> that do a very nice job helping the
process along, but they're pretty expensive. Not every site can afford these
tools or the resources needed to implement them. The new tool Data Transformation
Services (DTS) tool that Microsoft bundles with SQL Server 7.0 provides a nice
affordable alternative." Carlos Prieto, associate consultant at NetBase
Computing of El Segundo, CA a global IT consulting firm specializing in building
and deploying complex client-server distributed business systems for Fortune
1000 clients and high-growth firms.
DTS provides simple point and click translation from an Oracle, ASCII flat
file, or Access databases into SQL Server or the Microsoft OLAP server or to
export out to an ODBC data source, or Excel spreadsheet. And then from there
you can point-and-click transactions such as to tell it to copy only the first
two columns into a new database. More complex transformations can be handled
by inserting Visual Basic, Java script, or C++ routines. Expect to see a thriving
third party market of DTS plug-ins to do things like translation between one
currency to another.