In-Depth

SQL Server 2005 Gets BI Infusion

Microsoft rebrands ETL facility, beefs up Analysis Services, and hints at plans for ActiveViews end-user query and authoring technology

• By Stephen Swoyer
• 10/13/2004

Microsoft Corp. capped the Professional Association for SQL Server (PASS) community summit last month with a bevy of SQL Server-related news, including details about changes to major BI services in the forthcoming SQL Server 2005 database, the announcement of new reporting packs for its SQL Server Reporting Services offering, and a decision about how it plans to use the ActiveViews technology it acquired earlier this year.

Microsoft has long promised revamped ETL and OLAP components with SQL Server 2005, formerly code-named “Yukon” (see http://www.tdwi.org/research/display.asp?id=6912). At PASS last week, the software giant outlined a few of the biggest changes it plans to make—starting first and foremost with a rebranding of its ETL tool, Data Transformation Services (DTS).

“What we have announced is that the next version of DTS will have a new name—SQL Server Integration Services,” says Alex Payne, a senior product manager on the SQL Server team. Payne says Microsoft’s drastic overhaul of the former DTS product—“there isn’t a single line of common code between DTS in SQL Server 2000 and what’s coming in 2005”—was one impetus for the rebranding. “It’s really a fully scalable enterprise-ready ETL solution, but it goes beyond ETL where you can integrate with other Microsoft products.”

The new Integration Services boasts support for RSS feeds and Web services, Payne explains, and can exploit enhancements in SQL Server 2005’s revamped Analysis Services offering, too: “You can actually apply text mining to mine on data in the data flow, and the data is never persisted in any data source, possibly. We’re breaking the bar, if you will, with what we can do with ETL.”

What of Analysis Services, which itself was rebranded (Microsoft originally called it “OLAP Services”) in the SQL Server 2000 timeframe? The company has already said it will add support for new features such as XML for Analysis (XML/A) and enhanced support for the MultiDimensional eXpressions (MDX) language, of course, but Payne says Microsoft also plans big changes to the data mining algorithms that power Analysis Services (see http://www.tdwi.org/research/display.asp?id=6922). “We’re adding five new data mining algorithms, for a total of seven,” he says, noting that customers can also develop and add their own, custom, data mining algorithms.

In addition, says Payne, the next version of Analysis Services supports a feature called “proactive caching,” which helps to increase OLAP performance by making the system more responsive to changes in the underlying data source. “We’re really trying to get around the idea that you have to chose MOLAP for performance and ROLAP for the real-time aspect. We’re saying through proactive caching, we can provide the best of both worlds.”

The idea, he notes, is that MOLAP and ROLAP are all-or-nothing propositions: “If you truly want to run in a MOLAP-type world, you cache everything. If you want to run in a ROLAP world, you choose not to cache things. As the underlying data structures change, you go update the model.”

In the revamped Analysis Services, OLAP administrators will define their data models just as they’ve done in SQL Server 2000. Once that’s done, Payne says, proactive caching will take care of the rest. “I can resolve my queries the minute my query’s defined, and proactively as cycles become available on the machine, the underlying data source is going to proactively cache data in the model, giving you more of a MOLAP model."

ActiveViews Technology Surfaces

Improvements in DTS and Analysis Services are long overdue, of course. Microsoft shipped both products more than four years ago.

Even so, says Payne, Microsoft is cooking up several SQL Server 2005-specific enhancements for Reporting Services, which it delivered in January 2004. For starters, the company plans to drop an MDX editor into the Report Designer. This lets organizations create and run relational and OLAP queries against SQL Server—once they get a handle on the drastically revamped version of MDX that ships with Yukon, of course.

Perhaps the single biggest Reporting Services-specific change is the incorporation of technology Microsoft acquired from the former ActiveViews earlier this year.

First, some background: Reporting Services’ native development environment is Visual Studio, and—in its first go-round—Microsoft did little to make that product usable for business executives and other non-technical users. The result is that users unfamiliar with Microsoft’s Visual Studio .NET IDE have little or no discretion to create their own reports.

The ActiveViews technology addresses precisely this problem. It gives Microsoft an end-user query and report authoring environment, complete with drag-and-drop capabilities, that’s more suitable for average business users than the Visual Studio .NET IDE. The good news, says Payne, is that that technology will ship with SQL Server 2005 Reporting Services, where it will be rebranded as Report Builder.

“Essentially what Report Builder is, is it’s a client application where an end user, not a developer, comes in and can build report or modify a report, but they don’t build it the way you have to today, where you have to write a SQL query and write a string and understand what they’re doing,” he says.

Some users, such as Dave Bienstock, a systems specialist with mobile home manufacturer Fleetwood Enterprises Inc., say the lack of a usable end-user report authoring or modification tool is one big drawback of an otherwise very compelling Reporting Services 1.0. “Unfortunately, [it] only makes read-only views of data easy, but doesn't provide non-programmers with a way to make data entry easier, thus causing extra expense."

Mike Schiff, a senior analyst with consultancy Current Analysis, says the new Report Builder component is a double-edged sword of sorts for Microsoft. It delivers vital capabilities that should make the jobs of report designers easier, but could alarm many DBAs. The upshot, Schiff says, is that the software giant must walk a fine line in how it positions Report Builder:

“Microsoft should highlight the ease of use capabilities of SQL Server Reporting Services Report Builder while at the same time explaining how DBAs can set appropriate controls and permissions to prevent an out-of-control situation.”At the same time, Schiff notes, the rapidity with which Microsoft has apparently subsumed and productized the ActiveViews technology bodes well for future acquisitions, and should serve notice to the software giant’s BI competitors.

Reporting Services Tops 100,000 Downloads

When it released Service Pack 1 for Reporting Services in June, Microsoft reported that its first-ever enterprise reporting offering had been downloaded 75,000 times. Since then, Payne notes, Reporting Services has been downloaded at least another 25,000 times. “It’s just a testament to the amount of the adoption and the happiness we feel about the adoption of Reporting Services,” he says.

It’s possible that many potential Reporting Services adopters are still waiting to see just what Microsoft will actually deliver in the upcoming version 2.0 release of that product. Others—including Chris Ward, a support services consultant with an Australian-based provider of solutions for the financial services community—say that they’re studying the business case for investing in reporting services. Now that Microsoft has released a Report Pack that ties its CRM system to Reporting Services, this business case just got a whole lot more compelling, Ward says. (See http://www.esj.com/business_intelligence/article.aspx?t=y&EditorialsID=7240)

“As a software house we develop complex systems that require comprehensive and efficient reporting solutions. We are already looking into Reporting Services for this requirement, although no concrete release dates have been formulated thus far,” he says, noting that his employer is intrigued because “economies of scale [are] apparent with the Reporting Services component.”