While the integration of Microsofts Visual Studio 2005 development-tools suite with its SQL Server 2005 database is geared at helping developers more easily build database applications, it also delivers a platform for data warehousing and business intelligence, the company said.
“We wanted to build a professional-grade development environment for data warehousing and BI, and if you look around, you say, who has a professional-grade development environment? Visual Studio,” said Bill Baker, general manager for SQL Server Business Intelligence at Microsoft Corp.
“So, its a natural for us to say, lets take Visual Studio and extend that so it becomes a full-fledged BI development environment,” Baker said.
“And thats what weve done. So, we have all the team development aspects that Visual Studio has anyway, and bring that into this space. Then we add the editors for ETL [extraction, transformation and loading], for cubes, for KPIs [key performance indicators], for reports, et cetera, and just put all that into Visual Studio,” Baker said.
Baker said SQL Server 2005, code-named Yukon, has three business intelligence servers—Integration Services, Analysis Services and Reporting Services—and each are extensible: “The method for extending them is code written in one of the languages written against the Common Language Runtime [CLR].” The CLR is a key component in Visual Studio 2005, code-named Whidbey.
Indeed, Baker said, Microsoft made a bet by basing its whole BI stack on managed code.
“Reporting Services is probably one of the most widespread managed programs out there in terms of adoption—there are well over 100,000 servers at this point,” Baker said. “And data is managed all the way from day one. So, it was a huge bet on our part.”
“I have said many times to many people that the real Yukon story is BI,” said Andrew Brust, chief of new technology at Citigate Hudson Inc., New York. “This is really the release where Analysis Services grows up.”
Brust said OLAP [online analytical processing] features in the upcoming SQL Server product “have been improved in a number of ways to make building and maintaining cubes much, much easier. The creation of the unified dimensional model concept and the addition of something called proactive caching make it far easier to get fast performance and real-time or near-real-time analytics in place.”
In addition, Brust said, data mining features in the new, yet-to-be-released version “have matured so much that the SQL Server 2000 Data Mining engine looks like a simple prototype by comparison. Several statistical algorithms have been added, as have a number of tools and features to allow measuring the absolute and relative accuracy of multiple mining models on the same data. This stuff makes it immediately feasible for all kinds of organizations to derive predictive capabilities from their transactional data.”
For instance, imagine a retail chain being able to determine which demographics correlate to sales of specific products, Brust said. “Or imagine pharmaceutical researchers being able to correlate symptoms and inherited traits with positive prognoses of a drug working well or causing serious side effects,” he added, noting that SQL Server enables this.
Moreover, on the reporting side, the new ReportBuilder functionality in Yukon “makes it feasible to deliver true ad hoc reporting capabilities to end users, without the usual limitations in query types and output formats,” Brust said.
Reiterating the value of the Visual Studio integration, Baker said, “If you want to write an ETL package to tear apart some record format that we dont know about, you would typically use some of the VBScript, and that would be running the CLR,” Baker said. “The way you write the equivalent of a stored procedure in Analysis Services is to use one of the CLR languages. The way you write expressions in reports is to use, again, extensions, expressions that we actually compile into C#, although youre not too aware of that as somebody writing a report; but everything in terms of extensibility is done with the CLR, or languages that compile on top of the CLR.”
Baker said Microsoft realized business intelligence will be led by applications.
“If you really believe in the goodness of BI, and want to see it used in a very widespread way, you have to put it in the everyday apps; you have to put it in things people are going to use anyway,” Baker said.
Microsoft simply moved to meet user needs where an integrated environment for building BI applications had never existed, Baker said. “It had been a best-of-breed kind of space, so youd pick the architecture for your OLAP bench, and youd pick one of three or four vendors depending on which architecture you picked. Youd pick a porting vendor, et cetera. You wind up with six or seven parts and seven or eight development environments, and you have to kind of be the general contractor to put that all together.”
The Yukon/Whidbey integration eliminates the need for this, he said.