Using Integration Services with Analysis Services

Article
12/03/2008

New: 14 April 2006

SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, which are integrated in a specialized development and management environment. This topic focuses on the OLAP features in Analysis Services and the new tools for working with OLAP in Integration Services. For information about using Integration Services with the data mining features in Analysis Services, see Using Integration Services with Data Mining.

Online analytical processing (OLAP) lets you access aggregated and organized data from business data sources, such as data warehouses, by using a multidimensional structure called a cube. SQL Server 2005 Analysis Services (SSAS) provides tools for OLAP that you can use to design, deploy, and maintain cubes and other supporting objects.

For more information about the OLAP features in SQL Server 2005 Analysis Services (SSAS), see Analysis Services Concepts and Objects and Working with Online Analytical Processing (OLAP).

Note

Some of the Integration Services components described in this topic are available only in the Enterprise and Developer editions of SQL Server 2005.

Connection Managers

A connection manager is a logical representation or encapsulation of a connection to a data source. You can use the Analysis Services connection manager to connect to an Analysis Services database in which cubes and OLAP structures are stored. All the tasks and data flow components described in this topic require an Analysis Services connection manager.

Analysis Services Connection Manager

An Analysis Services connection manager enables a package to connect to a server that runs an Analysis Services database, or to an Analysis Services project that provides access to cubes and other OLAP structures. You can connect to an Analysis Services project only when developing packages in Business Intelligence Development Studio. At run time, packages connect to the server and the database to which you deployed the instance of Analysis Services.

For more information, see Analysis Services Connection Manager.

Tasks

Tasks are control flow elements that define units of work that are performed in a package. You can use the following tasks to create, alter, and process objects in an Analysis Services database.

Analysis Services Execute DDL Task

The Analysis Services Execute DDL task runs data definition language (DDL) statements that can create, drop, or alter cubes and other objects. The DDL statements are represented as statements in Analysis Services Scripting Language (ASSL), and framed in an XML for Analysis (XMLA) command.

For more information about this task, see Analysis Services Execute DDL Task. For more information about creating and altering Analysis Services objects by using XMLA, see Creating and Altering Objects (XMLA).

Analysis Services Processing Task

The Analysis Services Processing task automates the processing of Analysis Services objects such as cubes, dimensions, and partitions.

For more information about this task, see Analysis Services Processing Task. For more information about processing Analysis Services objects, see Processing in Analysis Services.

Data Flow Transformations

Transformations are the components in the data flow of a package that aggregate, merge, distribute, and modify data.

Slowly Changing Dimension Transformation

The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. This transformation supports four types of changes: changing attribute, historical attribute, fixed attribute, and inferred member.

For more information about this transformation, see Slowly Changing Dimension Transformation. For information about the Slowly Changing Dimension Wizard that is used to configure this transformation, see Slowly Changing Dimension Wizard F1 Help.

Data Flow Destinations

Destinations are the data flow components that load the output of a data flow into different types of data sources or create an in-memory dataset.

Dimension Processing Destination

The Dimension Processing destination loads data into an Analysis Services dimension and processes the dimension.

For more information about this destination, see Dimension Processing Destination. For more information about processing Analysis Services objects, see Processing in Analysis Services.

Partition Processing Destination

The****Partition Processing destination loads data into an Analysis Services partition and processes the partition.

For more information about this destination, see Partition Processing Destination. For more information about processing Analysis Services objects, see Processing in Analysis Services.

Other Integration Services Components for OLAP

Almost all Integration Services components could be useful as part of an OLAP solution, especially in loading a data warehouse, but the following components are used most frequently:

Data Conversion transformation. Converts data types and formats. For more information, see Data Conversion Transformation and How to: Convert Data to a Different Data Type Using the Data Conversion Transformation.

Lookup transformation. Useful when substituting meaningful text values for numeric identifiers while loading a data warehouse. For more information, see Lookup Transformation and How to: Implement a Lookup Using the Lookup Transformation.