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.
Prev. page
[1]
2
next page 
