Microsoft SQL Server 2005

SQL Server Reporting Services
SQL Server Analysis Services
SQL Server Integration Services

Your organization today faces numerous data challenges. You need your people to make faster and more data-driven decisions, your developers to be more productive and flexible, and your managers to reduce their overall information technology (IT) budgets even as they scale your infrastructure to meet ever-increasing demands.

SQL Server 2005 is designed to help enterprises address these challenges. This next-generation database management and analysis solution delivers increased security, scalability, and availability to enterprise data and analytical applications, while making them easier to build, deploy, and manage.

Extending the strengths of SQL Server 2000, SQL Server 2005 provides an integrated database management and analysis solution that can help your staff do the following:

• Build, deploy, and manage enterprise applications that are more secure, scalable, and reliable.
• Maximize IT productivity by reducing the complexity of developing and supporting database applications.
• Share data across multiple platforms, applications, and devices to make it easier to connect internal and external systems.
• Control costs without sacrificing performance, availability, scalability, or security.

SQL Server 2005 advances your data infrastructure in three key areas: it makes your enterprise data more manageable, your developers more productive, and your business intelligence (BI) more comprehensive. It also breaks new ground in affordable pricing and licensing, upgrade paths to SQL Server 2005, and the Microsoft Windows Server System.

SQL Server is a comprehensive, integrated, end-to-end data solution that empowers your people by providing a more secure, reliable, and productive platform for enterprise data and BI applications. SQL Server 2005 delivers powerful, familiar tools to IT professionals as well as to information workers, reducing the complexity of creating, deploying, managing, and using enterprise data and analytical applications on platforms ranging from mobile devices to enterprise data systems. Through a comprehensive feature set, interoperability with existing systems, and automation of routine tasks, SQL Server 2005 provides a complete data solution for enterprises of all sizes.

The SQL Server 2005 data platform provides organizations of all sizes with the following benefits:

Capitalize on Data Assets.In addition to delivering a secure, reliable database for line-of-business and analytical applications, SQL Server 2005 enables your people to get more value from their data by including embedded functionality such as reporting, analysis, and data mining. You can take advantage of this power and flexibility to deliver data to every corner of your organization at a fraction of the cost of some other systems.

Increase Productivity.Through comprehensive BI capabilities and integration with familiar tools such as Microsoft Office System, SQL Server 2005 delivers timely business information to those who need it and tailors it to their specific needs. The goal is to extend BI to your entire staff and ultimately to help everyone, at all levels of the organization, make better business decisions based on one of their most valuable assets—their data.

Reduce IT Complexity.SQL Server 2005 simplifies the development, deployment, and management of line-of-business and analytical applications by providing a flexible development environment for developers and integrated, automated management tools for database administrators.

Lower Total Cost of Ownership (TCO).The integrated approach and focus on ease of use and deployment in SQL Server 2005 provides the industry's lowest upfront, implementation, and maintenance costs for rapid return on your database investment.

SQL Server 2005 provides the technology and capabilities that your people can count on. With significant advancements in the key areas of enterprise data management, developer productivity, and BI, the benefits of upgrading or migrating to SQL Server 2005 are substantial. SQL Server 2005 has the potential to take your business to the next level—we encourage you to try it for yourself.

To be successful in today's competitive marketplace, organizations need to extend information beyond the walls of their organization and seamlessly interact with customers, partners, and suppliers in real time. Microsoft SQL Server Reporting Services enables organizations to transform valuable enterprise data into shared information for insightful, timely decisions at a lower total cost of ownership.

SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft business intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions.

An Integrated Architecture
SQL Server Reporting Services supports a wide range of common data sources, such as OLE DB and Open Database Connectivity (ODBC), as well as multiple output formats such as familiar Web browsers and Microsoft Office System applications. Using Microsoft Visual Studio .NET and the Microsoft .NET Framework, developers can leverage the capabilities of their existing information systems and connect to custom data sources, produce additional output formats, and deliver to a variety of devices.

Full Reporting Life Cycle Support
SQL Server Reporting Services supports the full reporting life cycle, including:

• Report authoring
  Report developers can create reports to be published to the Report Server using Microsoft or other design tools that use Report Definition Language (RDL), an XML-based industry standard used to define reports.
  
• Report management
  Report definitions, folders, and resources are published and managed as a Web service. Managed reports can be executed either on demand or on a specified schedule, and are cached for consistency and performance. New in SQL Server 2005 Reporting Services, administrators can use the Management Studio to organize reports and data sources, schedule report execution and delivery, and track reporting history.
  
• Report delivery
  SQL Server Reporting Services supports both on-demand (pull) and event-based (push) delivery of reports. Users can view reports in a Web-based format or in e-mail.
  
• Report security
  SQL Server Reporting Services implements a flexible, role-based security model to protect reports and reporting resources. The product includes extensible interfaces for integrating other security models as well.

Key Reporting Scenarios
SQL Server Reporting Services combines a single, complete reporting platform with a scalable and extensible architecture to meet a wide variety of reporting needs, including:

• Enterprise reporting
  Enterprises can use Reporting Services for their operational reporting or business intelligence applications. Using Reporting Services, corporate IT staff can design a variety of reports and deploy them to individual throughout the enterprise.
  
• Ad-hoc reporting
  SQL Server 2005 Reporting Services includes Report Builder, a new ad-hoc reporting tool that enables business users to create their own reports and explore corporate data. Report Builder incorporates a user-friendly business query model that enables users to build reports without deep technical understanding of the underlying data sources.
  
• Embedded reporting
  Organizations can access pre-defined or ad-hoc reports from third-party applications that use Reporting Services and use these reports as-is, customize them, or create new ones for specific business needs.
  
• Web-based reporting for partners and customers.
  Organizations can deploy interactive Web-based reports to deliver information to customers or partners over extranets of the Internet. Reporting Services isolates report consumers from the complexity of the underlying data sources, while providing personalization and interactivity.

SQL Server 2005 Analysis Services provides, for the first time, a unified and integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining.

Analysis Services provides a business semantic model, referred to as a Unified Dimensional Model (UDM), that defines business entities, business logic, calculations, and metrics. The UDM is a central place that serves as the single version of truth for all reports, spreadsheets, OLAP browsers, KPIs, and analytical applications.

Using the powerful new Data Source View feature, the UDM is mapped to a host of heterogeneous back-end data sources, providing a complete and integrated picture of the business regardless of the location of the data. With friendly descriptions of the business entities, navigation hierarchies, multiple perspectives, and even automatic translations to native languages, users will find it easy to explore corporate business data.

Proactive Caching enables the combination of real-time updates with multidimensional online analytical processing (MOLAP) class performance. Analysis Services maintains a highly compressed and optimized data cache that is maintained automatically as the data in the underlying source databases changes. The cache provides superb query performance and isolates back-end source systems from the load of the analytical queries.

The KPI framework provides a rich centralized repository defining key metrics and scorecards. The KPI framework in Analysis Services lets you easily build balanced scorecards and other types of business performance management applications.

Translations. Translations provide a simple and centrally managed mechanism for storing and presenting analytic data to users in their preferred languages.

MDX Scripts. Multidimensional Expression (MDX) Scripts are the new mechanism for defining calculated members, named sets, and cell calculations.

Business Intelligence Wizards. A set of easy-to-use wizards can help even the most novice user in modeling some of the more complex business intelligence problems.

Semi-Additive Measures. This new measure aggregation type for advance data modeling includes last-nonempty, last-child, first-child, average-of-children, and even by-account-type.

Data Mining. Microsoft SQL Server 2005 Analysis Services (SSAS) provides tools for data mining with which you can identify rules and patterns in your data, so that you can determine why things happen and predict what will happen in the future – giving you powerful insight that will help your company make better business decisions.

XML for Analysis (XML/A) is the native, standards-based protocol for communication with the Analysis Services server. New kinds of applications are enabled and easy to develop—applications that integrate analytics with operations in real time that can access the data from any platform and any language.

SQL Server 2005 Analysis Services sets a new standard for business intelligence servers in enterprise scalability, manageability, and productivity.

Scalability. Analysis Services scales to the most demanding analytical and reporting applications. With a new dimensional architecture, UDM-enabled applications let users quickly perform rich and intuitive ad hoc analysis using hundreds of dimensions and hierarchies. Hierarchies make it easier for users to navigate and query UDMs with hundreds of millions of members.

In addition, the new architecture removes the limitations of memory–resident dimensions and the number of children members per parent. With an XML/A–based communication protocol that is paired with greatly optimized role–based security, Analysis Services helps a UDM handle thousands of users, enabling enterprise-scale business intelligence applications using Web/middle-tier or client/server architectures.

Manageability. By integrating the management tools for the relational engine and Analysis Services, business intelligence administrators benefit from having a single, uniform environment for managing SQL Server Analysis Services. With SQL Server Management Studio, administrators can easily script Analysis Services objects and operations or tasks. Administrators can use a rich editor for MDX and Data Mining eXtensions (DMX) queries. SQL Profiler can be used to trace, capture, and replay Analysis Services queries and other commands. Multi-instancing, enhanced backup and restore, synchronization of databases across servers, and improved fine-grained administrative permissions are some of the new manageability tools available.

Productivity. Integrated with Visual Studio, the intuitive Analysis Services wizards and editors provide a true application development environment supporting the project's full life cycle. Source control, versioning, workstation isolation, embedded debugging, and configuration management are some of the benefits of the new Business Intelligence Development Studio.

Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, for executing SQL statements, or for sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service, for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model.

Typical Uses of Integration Services

SQL Server 2005 Integration Services (SSIS) provides a rich set of built-in tasks, containers, transformations, and data adapters that support the development of business applications. Without writing a single line of code, you can create SSIS solutions that solve complex business problems using ETL and business intelligence, manage SQL Server databases, and copy SQL Server objects between instances of SQL Server.

The following scenarios describe typical uses of SSIS packages.

Merging Data from Heterogeneous Data Stores
Data is typically stored in many different data storage systems, and extracting data from all sources and merging the data into a single, consistent dataset is challenging. This situation can occur for a number of reasons. For example:

Many organizations archive information that is stored in legacy data storage systems. This data may not be important to daily operations, but it may be valuable for trend analysis that requires data collected over a long period of time.

Branches of an organization may use different data storage technologies to store the operational data. The package may need to extract data from spreadsheets as well as relational databases before it can merge the data.

Data may be stored in databases that use different schemas for the same data. The package may need to change the data type of a column or combine data from multiple columns into one column before it can merge the data.

Integration Services can connect to a wide variety of data sources, including multiple sources in a single package. A package can connect to relational databases by using .NET and OLE DB providers, and to many legacy databases by using ODBC drivers. It can also connect to flat files, Excel files, and Analysis Services projects.

Next, the data is typically transformed by using the transformations that Integration Services includes. After the data is transformed to compatible formats, it can be merged physically into one dataset.

After the data is merged successfully and transformations are applied to data, the data is usually loaded into one or more destinations. Integration Services includes destination for loading data into flat files, raw files, and relational databases. The data can also be loaded into an in-memory recordset and accessed by other package elements.

Populating Data Warehouses and Data Marts
The data in data warehouses and data marts is usually updated frequently, and the data loads are typically very large.

Integration Services includes a task that bulk loads data directly from a flat file into SQL Server tables and views, and a destination component that bulk loads data into a SQL Server database as the last step in a data transformation process.

An SSIS package can be configured to be restartable. This means you can rerun the package from a predetermined checkpoint, either a task or container in the package. The ability to restart a package can save a lot of time, especially if the package processes data from a large number of sources.

You can use SSIS packages to load the dimension and fact tables in the database. If the source data for a dimension table is stored in multiple data sources, the package can merge the data into one dataset and load the dimension table in a single process, instead of using a separate process for each data source.

Updating data in data warehouses and data marts can be complex, because both types of data stores typically include slowly changing dimensions that can be difficult to manage through a data transformation process. The Slowly Changing Dimension Wizard automates support for slowly changing dimensions by dynamically creating the SQL statements that insert and update records, update related records, and add new columns to tables.

Additionally, tasks and transformations in Integration Services packages can process Analysis Services cubes and dimensions. When the package updates tables in the database that a cube is built on, you can use Integration Services tasks and transformations to automatically process the cube and to process dimensions as well. Processing the cubes and dimensions automatically helps keep the data current for users in both environments; users who access information in the cubes and dimensions, and users who access data in a relational database.

Integration Services can also compute functions before the data is loaded into its destination. If your data warehouses and data marts store aggregated information, the SSIS package can compute functions such as SUM, AVERAGE, and COUNT. An SSIS transformation can also pivot relational data and transform it into a less-normalized format that is more compatible with the table structure in the data warehouse.

Cleaning and Standardizing Data
Whether data is loaded into an online transaction processing (OLTP) or online analytic processing (OLAP) database, an Excel spreadsheet, or a file, it needs to be cleaned and standardized before it is loaded. Data may need to be updated for the following reasons:

Data is contributed from multiple branches of an organization, each using different conventions and standards. Before the data can be used, it may need to be formatted differently. For example, you may need to combine the first name and the last name into one column.

Data is rented or purchased. Before it can be used, the data may need to be standardized and cleaned to meet business standards. For example, an organization wants to verify that all the records use the same set of state abbreviations or the same set of product names.

Data is locale-specific. For example, the data may use varied date/time and numeric formats. If data from different locales is merged, it must be converted to one locale before it is loaded to avoid corruption of data.

Integration Services includes built-in transformations that you can add to packages to clean and standardize data, change the case of data, convert data to a different type or format, or create new column values based on expressions. For example, the package could concatenate first and last name columns into a single full name column, and then change the characters to uppercase.

An Integration Services package can also clean data by replacing the values in columns with values from a reference table, using either an exact lookup or fuzzy lookup to locate values in a reference table. Frequently, a package applies the exact lookup first, and if the lookup fails, it applies the fuzzy lookup. For example, the package first attempts to look up a product name in the reference table by using the primary key value of the product. When this search fails to return the product name, the package attempts the search again, this time using fuzzy matching on the product name.

Another transformation cleans data by grouping values in a dataset that are similar. This is useful for identifying records that may be duplicates and therefore should not be inserted into your database without further evaluation. For example, by comparing addresses in customer records you may identify a number of duplicate customers.

Building Business Intelligence into a Data Transformation Process
A data transformation process requires built-in logic to respond dynamically to the data it accesses and processes.

The data may need to be summarized, converted, and distributed based on data values. The process may even need to reject data, based on an assessment of column values.

To address this requirement, the logic in the SSIS package may need to perform the following types of tasks:

• Merging data from multiple data sources.
  
• Evaluating data and applying data conversions.
  
• Splitting a dataset into multiple datasets based on data values.
  
• Applying different aggregations to different subsets of a dataset.
  
• Loading subsets of the data into different or multiple destinations.

Integration Services provides containers, tasks, and transformations for building business intelligence into SSIS packages.

Containers support the repetition of workflows by enumerating across files or objects and by evaluating expressions. A package can evaluate data and repeat workflows based on results. For example, if the date is in the current month, the package performs one set of tasks; if not, the package performs an alternative set of tasks.

Tasks that use input parameters can also build business intelligence into packages. For example, the value of an input parameter can filter the data that a task retrieves.

Transformations can evaluate expressions and then, based on the results, send rows in a dataset to different destinations. After the data is divided, the package can apply different transformations to each subset of the dataset. For example, an expression can evaluate a date column, add the sales data for the appropriate period, and then store only the summary information.

It is also possible to send a data set to multiple destinations, and then apply different sets of transformation to the same data. For example, one set of transformations can summarize the data, while another set of transformations expands the data by looking up values in reference tables and adding data from other sources.

Automating Administrative Functions and Data Loading
Administrators frequently want to automate administrative functions such as backing up and restoring databases, copying SQL Server databases and the objects they contain, copying SQL Server objects, and loading data. Integration Services packages can perform these functions.

Integration Services includes tasks that are specifically designed to copy SQL Server database objects such as tables, views, and stored procedures; copy SQL Server objects such as databases, logins, and statistics; and add, change, and delete SQL Server objects and data by using Transact-SQL statements.

Administration of an OLTP or OLAP database environment frequently includes the loading of data. Integration Services includes several tasks that facilitate the bulk loading of data. You can use a task to load data from text files directly into SQL Server tables and views, or you can use a destination component to load data into SQL Server tables and views after applying transformations to the column data.

An Integration Services package can run other packages. A data transformation solution that includes many administrative functions can be separated into multiple packages so that managing and reusing the packages is easier.

If you need to perform the same administrative functions on different servers, you can use packages. A package can use looping to enumerate across the servers and perform the same functions on multiple computers. To support administration of SQL Server, Integration Services provides an enumerator that iterates across SQL Server Management Objects (SMO) objects. For example, a package can use the SMO enumerator to perform the same administrative functions on every job in the Jobs collection of a SQL Server installation.