Using Integration Services with Analysis Services
SQL Server Analysis Services 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 Analysis Services 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 Analysis Services, see Logical Architecture Overview (Analysis Services - Multidimensional Data).
Note
Some of the Integration Services components described in this topic are available only in the Enterprise and Developer editions of SQL Server.
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 Analysis Services Objects.
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 destinationloads 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 Analysis Services Objects.
Partition Processing Destination
ThePartition 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 Analysis Services Objects.
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 by 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 in No Cache or Partial Cache Mode.
External Resources
SSIS Package for Collecting SSAS DMV Data (CodePlex Sample)
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services