Building Better BI in SQL Server 2005

Since its inception, Microsoft's SQL Server Business Intelligence (BI) team has been guided by the overriding goal of making business data usable and accessible to as many people as possible. As the team's general manager, Bill Baker works with the people who design and develop BI tools such as Integration Services (formerly data transformation services—DTS), Analysis Services, and Reporting Services. Baker recently talked with SQL Server Magazine about SQL Server 2005's new BI tools and how they work together to streamline delivery of business-critical information.

How are SQL Server 2005's BI enhancements meeting Microsoft's goals for serving the BI community? And how long has your team has been working on these enhancements?

Our goal since we started the SQL Server BI team has been to give as many people as possible in every organization greater insight into their business and the market. We call this "BI for the Masses," and with every version of SQL Server and Microsoft Office, we take further steps to make BI available to every person at every level of a company. For example, in the integration space, SQL Server 2005 Integration Services delivers far greater throughput and more data warehousing and BI functionality out of the box. In addition, customers need to analyze far more data from many more sources and with more immediacy than ever before. In the analysis space, our investments in the Unified Dimensional Model (UDM) and proactive caching move SQL Server 2005 Analysis Services beyond the niche-OLAP market and into the mainstream. Our new Report Builder in SQL Server 2005 Reporting Services opens up report authoring way beyond the Visual Studio audience we support well today. Our vision is about getting the right information, in the right format, to the people who need it—when they need it. Every BI investment we make supports that goal.

Initial planning for SQL Server 2005 started several years ago, but we are now starting to see the fruits of our labor and are definitely in "ship mode." Through our beta releases and Community Technology Previews (CTPs)—advance previews into the upcoming beta—we are receiving incredible customer feedback on our features and implementations.

What kind of feedback have you been getting from beta testers, and which features are they most enthusiastic about?

Our customers tell us they really appreciate how comprehensive our BI solution is. Our solution not only enables seamless integration of components, but it's cost-effective, which is essential. We are getting great feedback on the BI Development Studio—formerly called the BI Workbench—which provides one development environment for Integration Services, Reporting Services, data mining, and Analysis Services. Beta testers have also praised the integration of the BI engines into SQL Server Management Studio—formerly called the SQL Server Workbench—which combines the functionality of Enterprise Manager, Query Analyzer, and Analysis Manager into one integrated tool. Beta testers also appreciated the overall ability SQL Server 2005 gives them to deploy and manage BI applications.

According to news reports, Microsoft and some large customers have deployed SQL Server 2005 Beta 2 in production environments. What is your recommendation for deploying Beta 2 and running it in production? What caveats do you have for businesses eager to move to the new version now?

We're amazed at how many customers ask us to support their Beta 2 implementation in production. Honestly, we don't recommend it since there is no Service Level Agreement (SLA) for Beta 2, but that has not stopped several customers. So far, they are having good experiences, but our recommendation is to get experience with the beta bits, start developing your applications, and plan to roll out your applications with the final version of SQL Server 2005.

How compatible are SQL Server 2000's BI tools (OLAP, DTS, data mining) and SQL Server 2005's new BI tools? Because some of SQL Server 2005's BI tools—such as Integration Services—are completely rewritten, will they still work with SQL Server 2000 data and packages?

This is an area where we need to be very, very clear with our customers because the choice to upgrade or migrate varies depending on the situation. Our commitment is to be transparent about what will upgrade automatically and what will require migration, and we have migration aids for any objects that don't come over automatically.

For example, we will continue to support SQL Server 2000 DTS packages running beside SQL Server 2005 Integration Services. However, if you want to use some of the new SQL Server 2005 Integration Services features or performance, you will need to migrate your packages. We do not automatically migrate DTS packages because they usually contain code, very often in script, and the new SQL Server 2005 Integration Services has newer and better ways to do what that code used to do. In some cases, the benefits of the new technology will be worth rewriting the packages.

SQL Server 2000 Analysis Services supports only clustering and decision-tree data-mining algorithms. Does SQL Server 2005 add support for other algorithms?

Yes. The next version of SQL Server Analysis Services will include five new algorithms in the extensible data-mining solution. We have a great partnership with Microsoft Research that lets us cooperate on new data-mining algorithms, so we've identified the most popular requests and added algorithms for association sets, neural nets, text mining, and other needs. (Editor's Note: For more information about the new data-mining algorithms, see "Data Mining Reloaded," page 21.)

We also made enhancements to data mining, including a set of rich, graphical model editors and viewers in the BI Development Studio. We added support for training and querying data-mining models inside the extraction, transformation, and loading (ETL) pipeline. Developers will benefit from easy integration of data mining into their applications, and analysts will receive finer-grain control over their models. We're excited about these enhancements because they address making data mining and data quality operational.

Microsoft relies on an integrated technology stack—from OS to database to user interface. How does that integration help Microsoft's BI offerings better serve your customers' needs?

Our belief in the Windows platform is long-standing and probably well understood by now. It's important to note that while we have an integrated offering from top to bottom, it is also an open environment. This openness is critical for BI, where much of the opportunity for our customers is in gaining additional insight and value from the operational systems they already have. All of our BI platform components can read data from a huge variety of databases and applications, and they provide Web services for embedding and integrating with other applications—even on other platforms. We get strength from the integration and consistency of the elements we provide, but lose nothing in terms of openness. Our customers benefit from the flexibility of our interoperability. By using our integrated solution, customers also witness a reduction in training time, management staff, and total cost of ownership. It's a win-win situation.

SQL Server 2005 will be the first release in which database tools converge with Visual Studio development tools. Can you tell us what it took to align these two releases and what benefits customers will realize from the change?

Databases and applications used to be two separate worlds, but more and more, people are recognizing the similarities between application development and database development. For instance, what interesting business application doesn't store and access data in a database? With Visual Studio 2005 (codenamed Whidbey) and SQL Server 2005, we've taken the next step in melding the database- and application-development experiences. We based our BI Development Studio on Visual Studio, and all the Visual Studio features that support team and enterprise development, including source-code control and deployment, also work for the data warehouse and BI developer. We built a single environment where people can develop all of the components of a data warehousing or BI application, including relational design, ETL, cubes, reports, data mining, and even code if desired. There is no other end-to-end, professional-grade environment for BI.

The introduction of the UDM is said to blur the line between relational and multidimensional database architectures. This approach is new for the Microsoft BI platform. What are the most interesting features the UDM offers? And based on your experience, what features do you think will surface as the most valuable for customers and ISVs?

Ultimately, OLAP is cool because it brings together navigation and query. Pivoting and drilling down are really just queries. But the OLAP world has never been attribute-rich; OLAP engines have never had good ways to express attributes, and adding something as simple as a phone number to a dimension would have caused size and performance issues in earlier SQL Server releases. With the UDM, we bridge the hierarchical drill-down world and the attribute-reporting world to present a dimensional view of data without losing the rich attributes present in the data.

The UDM is also the place where we express business logic, since MDX calculated members and cells are expressions of business logic. The UDM adds time intelligence, account intelligence, and key performance indicators (KPIs). You might think KPIs are only calculations, but they are much more. A SQL Server 2005 KPI includes the calculation, an expression for the goal, an expression for the trend, and a means of visualizing the results. KPIs are first-class elements of the UDM.

What tools will Microsoft add to the Visual Studio 2005 IDE to help developers create and manage SQL Server (and other database platforms') users, groups, and permissions to better insulate private data from those who shouldn't have access?

The Visual Studio and SQL Server development teams work together on integration and new methods of managing data. Our team supplies components to Visual Studio, and they supply components to SQL Server. In SQL Server 2005, we've added Data Insulation features to the core SQL Server engine. The end result is that developers using Visual Studio can easily create the database elements they need for their application. For enterprise-management activities, we anticipate that people will use SQL Server Management Studio.

In one of your past conference keynote addresses, you mentioned that Microsoft is adding a new set of controls to Visual Studio 2005 to permit reporting without Reporting Services. Could you describe what those controls will do, when we'll see the controls appear in Visual Studio 2005, and where you expect them to be documented?

The reporting controls will ship with Visual Studio 2005 and SQL Server 2005, and they will enable programmers to embed reporting in their applications. We support both WinForms and WebForms. Programmers will either provide Report Definition Language (RDL) and a data set to the reporting control or point to an existing Reporting Services server. We think every application of any sophistication can use at least a little reporting against data contained in the application. These controls just make it easier.

What benefit does 64-bit bring to SQL Server BI, and do you think 64-bit can really help the Microsoft BI platform scale to the levels that UNIX-based BI platforms scale to today?

In a word: memory. The 64-bit architecture lets customers break out of the 3GB memory limit that they have with 32-bit SQL Server, which allows for far larger dimensions in OLAP. It also enables the new ETL engine in SQL Server Integration Services to hold more data and process rows that much faster. And yes, we absolutely think we will reach into the upper ranges of scale with 64-bit.

Who are some BI vendors you're working closely with to develop 64-bit BI computing?

What's important to recognize is not which vendors support 64-bit, but that SQL Server 2005 supports both 32-bit and 64-bit on Intel and AMD platforms. Our customers and partners can start with 32-bit and easily move to 64-bit later or take existing 32-bit applications to 64-bit with near-total transparency. This support means our customers and partners don't have to worry about the differences because they are quite small and well documented.

Did you leave out any BI features that you planned to add to SQL Server 2005 because of deadlines or other issues?

We are confident that SQL Server 2005 will offer a comprehensive BI solution to address our customers' business problems. We've worked closely with our customers for several years to determine their pain points and create BI tools that provide relief. We started delivering those tools with SQL Server 7.0 and OLAP and continued with SQL Server 2000, DTS, and Reporting Services. With SQL Server 2005, customers will have the complete package to integrate, analyze, and report data. Even after all that, we still have a million ideas! We're already dreaming of what we can do beyond Yukon, so you can bet we'll be charged up for the next round—right after we ship SQL Server 2005. It's too early to discuss specifics, but as always, we'll work with our customers to determine new features and technologies.

Your team puts a lot of long hours into your work on SQL Server BI. What drives you and your BI developers to invest so much personally in the product?

Even when I started with the BI team 8 years ago, we said the same thing we say now: Companies improve when more of their employees use BI. "BI for the Masses" is very motivating. Unlike some of our competitors, our team is not working to provide a response to competitive offerings. The team works hard for the purpose of improving our product to best meet our customers' needs. It might sound corny, but it truly is as much a journey as it is a destination. The personal investment across the board is impressive and humbling, and I'm awed by the effort our team contributes every single day. I hope it shows in our product.