|
|
|
|
||||||
| Research Report: SQL Server 2005: Microsoft's BI Platform Introduction |
||||||
|
||||||
|
By Chris Alliegro [bio]
More than five years in the making, SQL Server 2005 includes major enhancements to its business intelligence (BI) platform. BI is a catch-all term for the process of extracting raw business data from operational data stores (such as sales orders in an enterprise resource planning system) and combining, correlating, and summarizing those data for analysis of business trends and conditions, and to make business-related decisions. Microsoft's BI platform is a collection of development tools, programming interfaces, run-time services, and management utilities for building BI applications. A more flexible, better performing platform and a new integrated development environment based on Visual Studio 2005, coupled with new data integration, analysis, and reporting features, will help developers create sophisticated BI applications with less custom or third-party code compared with past versions. This set of improvements could drive upgrades to SQL Server 2005 and help Microsoft draw customers with complex, enterprise-class data integration, analysis, and reporting needs away from competitors such as Business Objects, Informatica, and Oracle. However, the SQL Server 2005 BI platform is not without catches for customers and partners. For example, some features in previous versions are not carried forward to SQL Server 2005, and some BI applications will need to be rewritten to run on the SQL Server 2005 BI platform. Furthermore, the scope of changes suggests that developers and other users will face a nontrivial learning curve before they can take full advantage of the platform's new and enhanced capabilities. Making Sense of Business Data BI applications help organizations extract actionable information from the seas of raw data stored in operational databases. Such applications might gather low-level transaction records from multiple sources, such as sales orders recorded in an enterprise resource planning (ERP) system or Web site clicks recorded in Web server logs, derive higher-level business measures from those records (such as monthly sales by product line), and present users with views of the calculated measures in a Web-based report or through more interactive client tools, such as Excel. For example, typical BI scenarios could include the following:
The process of gathering, organizing, and presenting business data has evolved considerably in recent decades—early BI-like applications often constituted nothing more than custom-built, static, paper-based reports extracted from proprietary database systems. Today, however, organizations can choose from an array of general BI platforms or applications that can connect to, and integrate data from, a range of sources; these newer platforms and applications also give users more autonomy as well as more flexible tools and mechanisms for viewing, manipulating, and analyzing data. Microsoft Invests for SQL Server Growth A part of SQL Server, Microsoft's BI platform is a collection of development tools, programming interfaces, run-time services, and management utilities for building applications that support such analyses. The platform contains three distinct services: Integration Services helps developers collect, integrate, and summarize operational data from multiple sources; Analysis Services provides support for online analytical processing (OLAP) and data mining; and Reporting Services allows developers to create infrastructure for managing and distributing reports. Microsoft entered the BI market with the 1998 release of SQL Server 7.0, which included rudimentary BI features for data integration and analysis. SQL Server's evolution from a simple database management system (DBMS) to a general database platform that also supports BI application development has boosted product sales while strengthening Microsoft's competitive stance in the database market vis-À-vis database vendors such as Oracle, and against specialized BI software vendors such as Hyperion and Business Objects. Bundling BI capabilities with the SQL Server database management system gives SQL Server customers a relatively low-cost and convenient entry-point to BI, and also creates opportunities for third-party software vendors and systems integrators. Although Microsoft does not disclose how the separate components of individual products contribute to the product's overall sales, it is evident that SQL Server's BI platform has made important contributions to product sales: the BI features have gained increased research and development focus in SQL Server releases since their introduction, and adoption and use of the features has been relatively strong. For example, according to the OLAP Report, SQL Server is the top player in the OLAP market as of 2004, ahead of competitors such as Business Objects, Cognos, Hyperion, and Oracle. Furthermore, according to Microsoft, Reporting Services has been downloaded more than 150,000 times since its introduction in Jan. 2004. (However, the company has not disclosed the fraction of these downloads resulting in production deployment of Reporting Services.) Consequently, the platform helps generate incremental sales of SQL Server (since many deployments require separate SQL Servers and thus SQL Server licenses), particularly among customers that use SQL Server as a database management platform. Another important factor in the platform's success, and a key tenet of Microsoft's BI strategy, is data-source independence. The SQL Server BI platform can work not only with SQL Server databases but also with IBM DB2 and Oracle databases, flat files, and a variety of other data sources. Thus, in addition to driving incremental sales among existing SQL Server customers, the platform allows Microsoft to sell SQL Server to companies using other products as their enterprise database management system. For example, it has helped Microsoft get a foot in the door of the enterprise database management market previously dominated by Oracle and IBM. Integration, Vertical Opportunities Finally, the nature and popularity of the SQL Server BI platform creates opportunities for integrators and third-party software developers. The BI platform is itself complex and is intended to address complex problems, so it requires considerable planning, broad knowledge of operational data sources, expertise with the platform itself, proprietary code development, and software and hardware integration skills. Thus, to build workable BI solutions, many companies may require outside help, such as system integrators. In addition, the SQL Server BI platform can serve as a foundation for building BI capabilities into other products or applications, or to create business analysis applications for end users. For example, OutlookSoft and Panorama create a variety of business monitoring, performance management, and reporting applications on the SQL Server BI platform. Product teams at Microsoft also build on the platform: for example, the Microsoft Business Solutions group uses SQL Server as the foundation for BI features in products such as Great Plains and Navision. Overview of Microsoft's BI Platform With the release of SQL Server 2005, the Microsoft BI platform will consist of three major components: Integration Services, Analysis Services, and Reporting Services. (For a graphical overview of these components, see the illustration "Business Intelligence in SQL Server 2005".) Integration Services contains development tools, programming interfaces, run-time services, and management utilities for building and managing data-integration processes that are generically referred to as extract, transform, and load (ETL). These processes extract data from one or more data sources, transform or process those data (for example, casting them in a common format or correlating data elements from different sources), and then load the transformed data into a separate destination database. Perhaps the most important and common ETL process is the periodic update of data warehouses, databases specifically designed and structured to support decision-making in organizations. (For more information on the ETL process and data warehouses, see the sidebar "ETL and Data Warehousing: A Complex Challenge".) Analysis Services delivers online analytical processing (OLAP) technology, which structures and presents historical data in ways that are amenable to analysis. In addition, Analysis Services contains features for building data mining applications, which help analysts spot hidden correlations and patterns in data. Users work with Analysis Services data via client tools (such as Excel or third-party OLAP client applications) that interact with Analysis Services' OLAP server, which is an engine for updating, querying, and managing OLAP data sources. (For more information on OLAP, data mining, and associated terms, see the sidebar "Where Do OLAP, Data Mining Fit?".) Reporting Services is a collection of development tools and programming interfaces, run-time services, and management utilities used to define, generate, store, and manage reports. Managed-reporting software such as Reporting Services typically serves business or technical decision-makers and others who need specific information on a recurring basis, and it enhances reporting beyond what ad hoc query and charting tools such as Excel provide. Similar to the other components of Microsoft's BI platform, Reporting Services can be used to build reports from other vendors' databases, such as Oracle. (For more information about the advantages of server-based managed reporting, see the sidebar "Why Organizations Use Managed Reporting".) The first iteration of the BI platform shipped in late 1998 with SQL Server 7.0. It included a precursor to Integration Services, called Data Transformation Services (DTS), and a predecessor to Analysis Services, called OLAP Services. In SQL Server 2000, OLAP Services and DTS evolved significantly, with expanded feature sets, successive improvements to performance and scalability, and increasingly refined development and management tools. Furthermore, in early 2004 Microsoft introduced Reporting Services as an add-on to SQL Server 2000. BI Platform Changes in SQL Server 2005 Compared with SQL Server 2000, SQL Server 2005 includes major enhancements across the BI platform. Corporate and ISV developers will find improved development tools and a more flexible, better-performing platform on which to build BI infrastructure and applications. This combination of better development tools and a more fully realized feature set could drive existing SQL Server customers to upgrade and prospective customers to select SQL Server 2005 to meet their core BI platform needs. In general, improvements center on the following themes: Easier development. A new integrated development environment, the BI Development Studio, is based on Visual Studio 2005 and bears only superficial resemblance to the host of stand-alone designers, tools, and wizards that developers used to create BI applications in SQL Server 2000. The BI Development Studio encompasses the functionality found in those earlier tools, while making use of the familiar project paradigm in Visual Studio and inheriting useful Visual Studio capabilities, such as the ability to integrate with source-code control software. The new environment could prove particularly interesting to corporate developers working on larger BI projects: for example, it allows development teams to manage and organize separate development tasks (constructing Integration Services programs, or "packages," and Analysis Services cubes, for instance) within a single environment. SQL Server 2005's BI development environment should also make it easier for ISVs to build BI capabilities into their own products or create value-added analysis applications on top of the platform. Smarter applications. In addition to the new development environment, a range of new programming tools, features, and constructs could help developers deliver smarter BI applications with less custom code and allow the creation of applications that would have been prohibitive or impossible in previous versions. For example, new built-in data transformations in Integration Services will let developers build out-of-the-box ETL processes that would require custom-built or third-party code in SQL Server 2000. Similarly, Analysis Services ships a number of new data mining tools and provides infrastructure for key performance indicators (KPIs), important components of many business monitoring and dashboard applications. Thus, SQL Server 2005 promises better BI applications with a concurrent reduction in custom or third-party code, which could both motivate existing customers to upgrade to SQL Server 2005 and make SQL Server an increasingly attractive alternative to competitors' BI products. BI for a broader range of users. In SQL Server 2005, a new end-user reporting feature (Report Builder) in Reporting Services allows users to construct, format, and publish ad hoc reports without requiring those users to possess programming skills. In the current version of Reporting Services, report creation and publishing is strictly a developer activity. Faster, more scalable platform. Integration Services offers improved performance and management capabilities compared with its predecessors, due to a completely rewritten run-time engine and new management tools. These improvements could attract new customers with complex, enterprise-class data integration needs, whose options today are limited to costly third-party products such as Informatica's PowerCenter. Analysis Services 2005 introduces a new cube-caching mechanism that allows administrators to configure when and how frequently cube data are refreshed from their underlying data source (a data mart or data warehouse, for instance). The feature will help administrators more easily optimize both the currency of cube data and the performance of user queries against those cubes. Finally, Microsoft will ship 64-bit versions of most components and features of the BI platform. Support for 64-bit servers could help large shops consolidate BI infrastructure on fewer servers and reduce the processing time of memory-intensive operations, such as complex Integration Services data transformations or processing large, complex Analysis Services cubes. Challenges for Customers, Partners Despite functional and architectural changes that will benefit developers, business workers, and IT staff, the SQL Server 2005 BI platform has some catches for customers and partners. Prospective and existing customers should be aware of the following caveats: Learning curve, complex upgrade. SQL Server's BI platform includes many new features, and the tools and procedures for creating BI applications have changed. Consequently, even expert developers should plan on some ramp-up time. In addition, existing customers may face problems when upgrading from previous versions of the BI platform. For example, many complex DTS packages will need to be rewritten and some SQL Server 2000 Analysis Services (Analysis Services 2000) features are discontinued in SQL Server 2005 Analysis Services (Analysis Services 2005). No end-user tools in Analysis Services. As is the case with previous iterations, Analysis Services 2005 is a platform for development. Organizations will need additional products, such as Excel or third-party applications from Microsoft partners like Panorama Software, for users to view, analyze, and navigate cube data. Similarly, customers will need to develop or purchase other applications (such as the recently announced Office Business Scorecard Manager 2005, described below) to view and work with Analysis Services KPIs. Expanded platform could squeeze competitors and partners. In general, the SQL Server 2005 BI platform is an improvement for partners providing integration services, building custom BI applications, or embedding BI features within other applications. However, Microsoft will continue to pursue platform enhancements that it views as potentially lucrative or broadly applicable (providing additional data mining algorithms, for instance), which could squeeze some partners and will further challenge competitors. Moreover, with Office Business Scorecard Manager (explained below), Microsoft is entering the arena of analytic applications formerly held by its partners. What's Ahead This report is geared to IT professionals and developers who want to orient themselves on the major additions and enhancements found in the SQL Server 2005 BI platform and the potential risks and challenges related to the deployment and use of the platform. It should also provide readers an overview of what the BI platform is, the business problems it is designed to address, and how it addresses those problems. Finally, an appendix provides a guideline to SQL Server 2005 editions and how features of the BI platform are represented in those editions. The report includes material previously published in Update and contains the following major sections: "Integration Services Overhauled". This section describes major enhancements to Integration Services in SQL Server 2005, including changes to the Integration Services run-time components, management tools, and development environment. "Analysis Services Extends the OLAP Cube". This section outlines significant changes in Analysis Services, including a more flexible and general model (referred to by Microsoft as the Unified Dimensional Model) for defining cubes, improved performance, and support for new data-mining algorithms. The section also includes a sidebar describing Microsoft's recently announced Business Scorecard Manager 2005 server product (referred to previously by the code-name Maestro), which is used to create business scorecards. "End-User Report Creation in Reporting Services" provides an overview of the new Report Builder features, which allow nondevelopers to create and publish reports, and other major feature enhancements in Reporting Services for SQL Server 2005. "Caveats and Considerations". This section highlights some of the challenges customers and partners may face when working with, or upgrading to, the SQL Server 2005 BI platform. "Resources". This section contains links and pointers to additional information about the SQL Server 2005 BI platform. "Appendix: SQL Server 2005 Availability, Pricing, and Packaging". Microsoft has changed SQL Server pricing and licensing for SQL Server 2005. This section describes those changes and maps BI platform features to SQL Server 2005 editions.
|
||||||
