TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

SQL Server 2005 Taking the Fight to the Business Intelligence Pure Plays

Some SQL Server shops already expect to replace BI pure play tools with SQL Server’s native BI functionality.

• By Stephen Swoyer
• March 1, 2006

In early December of last year, Microsoft Corp. finally delivered its long-awaited SQL Server 2005 database. Given the learning curve associated with the new SQL Server, however, it seemed likely that many organizations would take up Redmond’s next-generation database at a comparatively slow pace. After all, you don’t implement a new version of a mission-critical database overnight—even if you’ve been beta-testing that next-gen database for a year or more.

Three months on, many would-be SQL Server 2005 adopters are still staying put on SQL Server 2000. This doesn’t mean they’re disenchanted with the next-gen SQL Server, however. Far from it. In fact, the SQL Server technologists we interviewed have mostly good things to say about Microsoft’s next-gen database deliverable. And what they have to say might also be of interest to business intelligence (BI) pure play vendors. As it turns out, some customers already expect to replace BI pure play tools with SQL Server’s native BI functionality.

Take Dino Hsu, a SQL Server professional with the Taiwanese subsidiary of a global cosmetics company based in the U.S. Hsu’s employer, like a lot of prospective SQL Server 2005 shops, hasn’t yet green-lighted a move to the new database. This doesn’t mean such a move isn’t in the works, Hsu says.

“We have standalone SQL Server 2000 [systems] developed by outside vendors for specific purposes such as voice-response ordering, short messaging, intranet associate information system, on-line survey, etc, but no 2005 at the moment,” he says. “We plan to do BI on SQL server 2005, including OLAP, data mining, [and] reporting, because it's functionally flexible and cost-effective. I have visual developer, Oracle DBA and BI designer backgrounds, and I am extensively studying [SQL Server 2005].”

Hsu, like many of his colleagues, enthuses about SQL Server 2005’s BI functionality. “The interactivity among integration, analysis, reporting services is great, the ROLAP and DOLAP mix [or HOLAP] is great, semi-additive and non-additive roll-up are especially important. The success of OLAP applications relies on them, and there's no decent BI if there's no decent OLAP in the first place,” he indicates. He also commends what SQL Server 2005 brings to the table BI-wise vis-à-vis competitive databases such as Oracle 10g. “Personally, I like the DMX and MDX languages that Oracle lacks.”

More importantly, Hsu says, his employer already expects to tap SQL Server 2005 to replace some of its third-party BI assets. “[W]e will replace Cognos PowerPlay, Impromptu, and IBM DB2 Intelligent Miner with SQL Server 2005,” he confirms. “Reasons are simple: functional flexibility and cost-effectiveness. Particularly, I feel Microsoft's commitment on BI.”

Ditto for Jerry Higgins, a SQL Server technologist with an independent software vendor (ISV) and Microsoft Gold Partner. He says his company has already tapped SQL Server 2005 in a few recent customer accounts, and will most likely make greater use of next-gen SQL Server after Microsoft ships its forthcoming Great Plains 9 accounting software. (Higgins’ company develops solutions on top of SQL Server and Great Plains.) “We have just recently made use of SQL [Server] 2005 in a couple installations, and with the recent release of Great Plains 9, we expect that to ramp up over the next few months,” he comments.

Higgins likes SQL Server 2005’s programmer-friendliness, courtesy of its native .NET Common Language Runtime (CLR), which—for the first time—lets programmers directly interact with the database using languages other than SQL Server’s bread-and-butter T-SQL. “The single most important feature that I think SQL 2005 offers is the CLR—mainly because I work in the developers group of my company. In the past we'd had to design a number of data ‘massaging’ applications that run outside of SQL, but we expect to be able to make use of the CLR rather heavily looking forward,” he indicates.

Higgins’ employer first deployed SQL Server Reporting Services shortly after that product’s debut, more than two years ago. Going forward, he expects even better things from SQL Server 2005 Reporting Services. In fact, Higgins says, his company will most likely be able to curtail its use of a third-party BI solution (Component1’s Reporting Classes) by making the move to next-gen SQL Server. “We've made heavy use of Reporting Services for the past two years, replacing a number of Access reporting applications that we previously deployed. We also make use of Component1's Reporting classes to help us out, although with the new 2.0 reporting objects, we expect to use [Component1] less,” he confirms.

Microsoft’s burgeoning BI stack will come into play elsewhere, too, says Higgins. “A number of our clients have tremendous amounts of data available to them, and so others in our company have been in charge of [Analysis Services] projects to help cull data. We have also made use of DTS in the past, and there is no reason to think that it will slow down in the future. As such, we expect to use [Integration Services] more and more.”

Michael Robert, an information systems consultant with the National Fire Protection Association, says SQL Server 2005 is both hit and miss, BI-wise. On the plus side, says Robert, next-gen SQL Server’s improved BI feature stack will probably enable NFPA to wean itself away from some third-party BI tools. “Yes, we currently have limited licenses for older versions of Cognos Powerplay and Impromptu, but licensing Cognos tools for our entire enterprise is too costly. Rather than upgrade Cognos, we plan to replace those reports with SQL Server.”

That’s the good. The not-so-good, says Robert, is that Microsoft hasn’t done enough to bolster SQL Server 2005’s analytic feature set. “One thing [Microsoft] has missed the boat on is a user-friendly cube reader tool. The only way to deliver 3D data cube browsing to the client is using Excel Pivot tables, which are slow and confuse the heck out of most business users. We really need a browser-based cube reader,” he comments.

SQL Server pro Hsu agrees, to some extent. He says he’s waiting for Microsoft’s next-gen Excel client (part of the Office 12 suite, due this year), which is expected to be tightly integrated with its SharePoint portal stack.