Components

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

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

Office Components

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.

Programmability

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.