What’s New in SQL Server 2005 Business Intelligence?

SQL Server 2005 is finally here. If you've been tracking this release's course for a long time by using the betas and scouring the Microsoft site for feature lists, how-to articles, and white papers, you're probably very much in tune with what the SQL Server 2005 Business Intelligence (BI) platform offers. But if you haven't been keeping up with it, or if you've been concentrating on all the advancements in the relational engine and now want a glimpse at the BI features, this article should provide a helpful SQL Server 2005 BI summary.

The SQL Server 2005 BI Platform

To evaluate BI platform capabilities, I like to look at everything from a holistic standpoint. I like to study the different tiers or roles in a BI solution and the products or features that support them. Microsoft has taken a similar stance in its BI marketing efforts. If you've been to any Microsoft conference sessions on SQL Server 2005 BI, you've heard the phrase "Integrate, Analyze, Report." Breaking down the marketing-speak, we can see that SQL Server Integration Services, Analysis Services, and Reporting Services serve as the three pillars of the Microsoft BI platform. Of course, the SQL Server relational engine can also play a crucial role, but it isn't required. Also, alerting functionality from Notification Services and presentation features from Microsoft Office and SharePoint all play a part in the BI platform, but those aren't the chosen cornerstones of the Microsoft marketing message.

Integration Services (the successor of Data Transformation Services—DTS) is targeted toward data integration, or what's commonly dubbed extraction, transformation, and loading (ETL). Analysis Services is intended for metadata modeling, multidimensional (OLAP) analysis, and data mining. Finally, Reporting Services covers flexible enterprise reporting and ad-hoc reporting. I'll dive more deeply into these features in a moment.

Business Intelligence Development Studio

Before investigating each BI component's interesting new features, we need to look at the new Business Intelligence Development Studio (BIDS). In SQL Server 2000, we were forced to do our best by switching from Enterprise Manager to Analysis Manager to Visual Studio—different development tools with different interfaces and deployment models—to approximate what a Microsoft BI solution requires.

Microsoft received plenty of feedback about these pain points and moved everything into the Visual Studio integrated development environment (IDE) for robust development features, mature collaboration and deployment capabilities, as well as a standardized developer experience. Now, in SQL Server 2005, you can create BI solution files that contain projects corresponding to Integration, Analysis, and Reporting alongside your C# and VB.NET projects. Another tool in SQL Server 2005—SQL Server Management Studio—also uses the Visual Studio IDE; it's focused on operational tasks rather than development tasks, but it's also consolidated: All administration of the SQL Server relational engine, Integration Services, Analysis Services, and Reporting Services instances occurs inside Management Studio.

Integration Services

You'll already find many terrific articles about Integration Services, but one point I want to drive home is that Integration Services replaces DTS; it's not merely the upgrade or next version. It's a new product that doesn't use any of DTS's legacy code base. Integration Services is Microsoft's first real foray into enterprise data integration, bringing in many key enterprise-level capabilities that DTS lacks.

The first notable change you'll discover in Integration Services is a major architectural shift: a separation of Control Flow (or work flow) from Data Flow. So, when you're developing Integration Services packages, you'll start off by working with Control Flow, in which you have Execute SQL tasks, Execute Process tasks, File System and FTP tasks, Send Mail tasks, and WMI Event tasks. You also have built-in looping and finite control over precedence constraints through objects such as sequence containers, so you can group tasks that must be executed together—all with error flow and sophisticated debugging, complete with support for breakpoints. Following the addition of a Data Flow object to the Control Flow (in one Control Flow, many Data Flow objects can be executed, in whatever order necessary), a dedicated designer tab supports complex handling of high-performance data flows that contain significant transformations (e.g., highly scalable aggregations and lookups, data merging and branching). These transformations can be highly parallelized and use dynamic memory management, and they can use as many processors and threads as are available—all handled by a highly optimized Data Manager.

Integration Services offers more excellent features, including additions to the technology's data-cleansing functionality, which now offers fuzzy lookups and fuzzy grouping—handy when you're marrying data from multiple sources and you don't want to write custom code to resolve minor differences in data. The use of VB.NET in scripting, access to Microsoft .NET libraries during data cleansing, and the ability to build custom .NET components (e.g., source, transform, destination) extends data-integration capabilities: You can leverage the full .NET Framework 2.0 from within Integration Services. Also, extensive support for variables and configurations simplifies the control of packages after deployment, without requiring package modification. From an operations standpoint, SQL Server 2005 offers features that encourage enterprise deployment, including logging, package restartability (with support for checkpoints that permit successfully executed tasks not to be re-executed), a deployment utility, and a server-based SSIS Service for monitoring and managing package execution on one or multiple servers.

Analysis Services

I get the feeling Microsoft wants us to stop thinking about Analysis Services as just OLAP in the traditional sense. Although Analysis Services is still an OLAP engine, many feature changes have been made to expand the product's reporting and analysis capabilities.

The Unified Dimensional Model. The big change in this arena is the introduction of the Unified Dimensional Model (UDM). The UDM is a consolidated metadata model for one or more underlying data sources that provides new capabilities, such as support for multiple fact tables, many-to-many relationships, attribute-based reporting, dynamic dimensional hierarchies, and cube subsets (similar to SQL Server views, called Perspectives). These are all difficult concerns in Analysis Services 2000.

Microsoft has added a Key Performance Indicator (KPI) framework/engine to permit better understanding of business measures by providing each as a KPI, which is the combination of a measure and its goal, status, and trend. To address needs for multilingual environments, Microsoft has added metadata translations so that you can customize BI applications to provide locale-specific metadata to users. Microsoft has also revamped the Multi-Dimensional eXpression (MDX) language to support scripts for defining calculated members, named sets, and cell calculations, as well as server-side stored procedures.

Performance has gotten a boost in several areas. Basing dimensions on attributes—rather than hierarchies and levels—permits native support for selecting and filtering by large numbers of attributes, without a lot of Analysis Services 2000's performance limitations. Most memory limitations associated with large dimensions in Analysis Services 2000 have been alleviated with the presence of a dimension member cache. Also, custom dimension security uses a bitmap that captures the dimension differences between multiple security roles without producing multiple shadow copies of dimensions into memory. A new concept called Proactive Caching blurs the line between relational reporting and OLAP, granting nearly real-time access to data but keeping a Multidimensional OLAP (MOLAP) cache for optimized query results between changes in the source data. This feature also eliminates the necessity to manually process Analysis Services cubes.

Data Mining. Microsoft is turning heads with its scalable server-based Data Mining toolset. In addition to the Decision Trees and Clustering algorithms that SQL Server 2000 includes, many new algorithms in SQL Server 2005—Naïve Bayes, Neural Networks, Sequence Clustering, Time Series, Association Rules, and Linear & Logistic Regression—make for a complete toolset for most data-mining tasks. Microsoft has added Text Mining tasks (Term Extraction and Term Lookup) to Integration Services for inclusion in data integration or dedicated packages. Data Mining is strongly integrated—with the ability to use relational or UDM data sources—and data-mining results can be used in cubes and reports. Also, aside from Text Mining, Data Mining is quite integrated with Integration Services, letting you add Data Mining to your solutions without a single line of code. All this server-side functionality is terrific but can be difficult to access and comprehend without good client tools, so Microsoft has developed a cadre of stellar Data Mining viewers that can be redistributed in applications that need them.

Operations. Microsoft transcends several pain points that once characterized enterprise deployments. High availability is provided with failover clustering support for Analysis Services, and new Server-Sync functionality lets a server push its current database to one or more other servers for availability and scale-out performance. Also, Microsoft provides new support for multiple Analysis Services instances on one physical computer. In Analysis Services, administrators now have functionality that's more commonplace in relational engine products: backup support for large databases, fine-grained administrative security, and a new scripting language with an XML-based syntax for administering and scripting instances of Analysis Services.

Reporting Services

In SQL Server 2005, Reporting Services has gone into its second version release. Based on developer and user feedback about Reporting Services 2000, the new version has many improvements—some of them not overly apparent but nevertheless making the product more scalable and extensible. A few seemingly minor (but real stand-out) items that Microsoft added are the date-picker, multi-value support for parameter selections, floating headers (similar to Excel's Freeze Panes functionality), and interactive sorting of rows or columns. Of course, report development got richer because the Visual Studio Report Designer is hosted in Visual Studio 2005 IDE, so things such as debugging and the expression editor are far more mature. The whole OLAP reporting story is so much more exciting with the introduction of the Analysis Services UDM and an added MDX query editor in Report Designer—you can add report parameters by simply selecting a check box.

However, stealing the thunder from these improvements are three items that really change the landscape: Report Builder, Report Controls, and modifications to SQL Server editions.

Report Builder. Report Builder is an exciting development in Reporting Services, especially if you've been using Reporting Services and wonder how you can permit certain users a little independence in report authoring—without needing the Visual Studio-hosted Report Designer. Report Builder is an ad-hoc reporting tool that lets a business user create a report from a table, matrix, or chart template that's built on a business model of the underlying database. So, a user authors reports by dragging and dropping attributes and measures, with intelligent support for formatting, aggregates, and the "big money" feature of Infinite Drill-through, which lets the user dig into underlying information from any report—on the fly. Detail reports are generated upon request, tracking filters and drill-path for accuracy. Users can then save Report Builder reports to ReportServer for other users to use and perform their own Infinite Drill-through on.

Report Controls. Actually, Report Controls aren't part of SQL Server at all but rather Visual Studio. Report Controls let you easily embed Reporting Services reporting functionality into your ASP.NET or .NET applications. These embedded Report Controls can process and render reports without ReportServer (or ReportServer licensing); alternatively, they can interact with ReportServer (to get report definitions, security information, and so on) but let you offer richer reporting functionality in your applications without a lot of hand-coding.

SQL Server editions. The final item isn't a feature, but it's big news nonetheless: Reporting Services will be included in all editions of SQL Server 2005, including the free Express version! Report Builder will be in all versions except Express. Originally, Microsoft intended to make Report Builder an Enterprise Edition–only feature, but due to overwhelming feedback, the company decided to also include Report Builder in the Standard and Workgroup editions—good news for anyone looking to add ad-hoc reporting at minimal cost.

Notification Services

I would be remiss if I didn't acknowledge Notification Services as a new BI feature in SQL Server 2005. The product didn't receive an overhaul, but Microsoft has added some nice enhancements that make the framework easier to develop alerting applications—applications that are scalable, embeddable, and easier to deploy and monitor. Even more exciting, the Notification Services development team was moved under the BI umbrella in February. Of course, there are many, many needs for alerting applications that aren't part of the BI platform, but alerting is a crucial component of gaining visibility into your business and enabling your decision-makers—even if those decision-makers are simply your online buyers who need to know that a certain item is back in stock. So, now that Notification Services is part of the Microsoft BI platform, we'll see its increasing presence in solution blueprints, suggested designs, and end-to-end demonstrations.

Dive In!

I've merely scratched the surface of SQL Server 2005 BI, but there's much more that's worth checking out. Start off by getting familiar with the new SQL Server Books Online, as well as the Tutorials and Samples included with SQL Server 2005 (be sure to select them as part of your "Advanced" installation). Then, to get deeper on specific areas of interest, circle back to SQL Server Magazine and investigate all the SQL Server Yukon and SQL Server 2005 articles that we've published in the past 2 years.