In-Depth

Microsoft Reveals OLAP and Data Mining Features in Next SQL Server

Microsoft will support XML/A and claims to have simplified the MultiDimensional eXpressions language used by Analysis Services to define calculations and security rules, among other changes.

• By Stephen Swoyer
• 12/10/2003

Now that the first public beta of Microsoft Corp.’s forthcoming SQL Server “Yukon” database is finally in the hands of testers, the software giant has been more forthcoming about impending changes to SQL Server’s business intelligence (BI) feature set.

For starters, Microsoft announced a substantial overhaul of its SQL Server extraction, transformation, and loading (ETL) component, called Data Transformation Services (DTS) (http://info.101com.com/default.asp?id=3765). Less well known is that the software giant also plans a refresh of SQL Server Analysis Services, the online analytical processing (OLAP) and data mining facility that it first introduced five years ago with SQL Server 7.0.

One of the biggest changes in Yukon Analysis Services is native support for an emerging standard called XML for Analysis (XML/A), which provides a single, consistent interface into OLAP servers or data mining servers, as a native protocol (http://info.101com.com/default.asp?id=3076). In a nutshell, XML/A eliminates the need for developers to write to multiple interfaces or OLAP engines. It also makes it possible for developers to tap Analysis Services to build new kinds of applications, says Chris Harrington, president of Web application and information portal development shop Active Interface Inc.

“It creates capabilities that previous to this … the infrastructure from a developer’s standpoint simply wasn’t there to do those things,” he comments. “As my code samples show, you can [use XML/A to] generate very rich PowerPoint presentations directly from [SQL Server’s] Analysis Services with very little code.” (Harrington’s code samples are available at http://www.activeinterface.com/thinolap/thinolap.asp.)

XML/A enjoys broad industry backing from partners such as OLAP giant Hyperion Solutions Corp. and ERP powerhouse SAP AG, which currently supports it in its SAP Business Warehouse offering. Microsoft says that Yukon Analysis Services will continue to support a light-footprint Win32 layer for backward compatibility with tools that work with Analysis Services 2000 on OLE DB for OLAP, ADOMD, and ADOMD.Net.

Elsewhere in Yukon Analysis Services, Microsoft claims to have simplified the MultiDimensional eXpressions (MDX) language, which is used by Analysis Services to define calculations and security rules. The next version of Analysis Services will support a new calculation model with MDX Scripts that use simplified constructs and syntax.

That’s welcome news to Nicolas Lievain, a SQL programmer with Mobile Workers, an EU provider of enterprise purchasing and software consolidation services. Mobile Workers has tapped SQL Server 2000 Analysis Services for use with its Corporate Purchasing Portal, a pay-for-use service that aggregates invoices and provides enterprise users with the ability to classify data and extract knowledge from them.

Lievain says he’s very pleased overall with Analysis Services, but admits that the MDX language represents a substantial learning curve. “I wish to have a more powerful MDX tool because the last one is very poor and we spend too much time debugging it,” he comments. “[It is] not so easy to develop at the beginning if you want to have your own query generator … [because] the MDX language is a bit complicated to understand, but it is a very powerful tool to describe multi-dimensional expression.”

In Yukon Analysis Services, MDX Scripts are constructed in the “Calculations” view of the cube design user interface, which is part of the “BI Workbench". Microsoft says that MDX Script can be viewed in the default Calculations Form view—which also provides guidance on syntax—or in the Calculations Script view, which presents the MDX Script as a set of commands terminated by semicolons. One caveat is that the Calculations Form view requires the correct syntax in the entire script in order to display.

Other new features in Yukon Analysis Services are a Unified Dimensional Model, which Microsoft says combines the characteristics of relational and OLAP data models; proactive caching, which reduces the management cost associated with the operation of low-latency applications; new developer and management tools in the form of the “BI Workbench” and “SQL Workbench” consoles; substantial changes to the security model; support for unlimited dimension sizes.

One intriguing addition to Yukon Analysis Services is a Key Performance Indicator (KPI) framework, which Microsoft says provides a server-defined mechanism for defining performance indicators and other key metrics. KPIs can be incorporated into reports, portals, and dashboards, through data access APIs and tools from Microsoft and third party providers, although the software giant says that no client tools are available as of Yukon Beta 1.