以下内容转自:http://www.mssqltips.com/tip.asp?tip=1560
Problem
We are looking to automate the processing of our SQL Server Analysis Services dimensions and cubes. We'd like to add this processing to our existing SQL Server Integration Services (SSIS) packages which periodically update our data warehouse from our OLTP systems. Can you give us the details on how the Analysis Services Processing Task can be used in an SSIS package?
Solution
The Analysis Services Processing Task allows you to process dimensions, measure group partitions, and mining models in an SSIS package. While you can process all of these objects at one time, you can also select a subset of these objects to be processed as well. For example you may update certain dimension and fact tables in your data warehouse on a periodic basis by running an SSIS package. As a final step in the SSIS package, you would like to process just the dimensions and measure group partitions that use those data warehouse tables as their data source. The Analysis Services Processing Task allows you to do that.
In this tip we will walk through the steps to use the Analysis Services Processing Task in an SSIS package. We'll create a sample package that will process a dimension and a measure group partition in the Adventure Works DW Analysis Services database that comes with SQL Server 2005. Our hypothetical scenario is that we run an SSIS package to update the Product and Currency Rate tables in our data warehouse on a daily basis. We would like to add a step to the SSIS package to process the Product dimension and the Currency Rate fact table, thereby updating the information available in our SQL Server Analysis Services cube.
Create the Sample SSIS Package
To begin launch Business Intelligence Development Studio (BIDS) from the Microsoft SQL Server 2005 program group and create a new Integration Services project. An SSIS package named Package.dtsx will be created automatically and added to the project. Rename the package to SSASProcessingTask_Demo.dtsx then perform the following steps on the SSIS package:
Step 1: Add a Connection Manager for the SSAS server. Right click in the Connection Managers area and select New Analysis Services Connection from the context menu. Accept the defaults in the dialog to connect to the local SSAS Server (or edit as appropriate if you want to connect to an SSAS Server on another machine):
Step 2: Drag and drop the Analysis Services Processing Task from the Toolbox onto the Control Flow of the SSIS package. Edit the Analysis Services Processing Task; select the connection manager defined in step 1 above and click the Add button to select the objects to be processed:
The Process Options selected work as follows:
- Process Incremental on a measure group partition is used to load just new rows from the fact table. It requires additional settings which we will complete in the next step.
- Process Update for a dimension will update the dimension with any inserts, updates or deletes from the data warehouse.
Step 3: Click the Configure hyperlink in the Currency_Rates row shown in step 2 above. Since we have selected Process Incremental as the Process Option we need to either specify a table or view to load the new fact rows from or specify a query; we'll specify a query and assume that the stg_FactCurrencyRate table is populated with just the new fact rows to be added to the measure group partition.
Next Steps
- If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip. Click the AdventureWorksBICI.msi link. Also click on the Release Notes link for the details on attaching the relational database. The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders. We used the project in the Enterprise folder.
- Take a look at the technical article Analysis Services 2005 Processing Architecture for an in-depth discussion of the processing options available for cubes, dimensions, and mining models.
- You can download the sample SSIS project created in this tip here.
分享到:
相关推荐
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
Discover how to: Use SSIS to extract, transform, and load data from multiple data sources Apply best practices to optimize package and project configuration and deployment Manage security settings in ...
How to integrate Analysis Services with other SQL Server 2005 components in order to provide the best possible end-to-end solutions How to manage and secure Analysis Services efficiently in support ...
Written by members of the Analysis Services product team at Microsoft, this timely, authoritative book shows you how to use Analysis Services with SQL Server components to provide comprehensive, end-...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
Dig into SQL Server Analysis Services, Integration Services, and Reporting Services Navigate the Business Intelligence Development Studio (BIDS) Write queries that rank, sort, and drill down on ...
Beginning SQL Server ... automate redundant monitoring and maintenance tasks, and use hidden tools so that you can quickly get over the learning curve of how to configure and administer SQL Server 2005.
How to create branch and how to do the integration between the different branch in p4
The examples throughout this book use the following sample databases, which are available to download from Microsoft: the sample database for SQL Server 2005 is called AdventureWorks, and the sample ...
How to use the Bayes Net Toolbox? This documentation was last updated on 29 October 2007.
Pro SQL Server 2008 Analysis Services will show you how to pull that data together and present it for reporting and analysis in a way that makes the data accessible to business users, instead of ...
SQL Server is the perfect product for the How to Cheat series. It is an ambitious product that, for the average SysAdmin, will present a difficult migration path from earlier versions and a vexing ...
SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when ...