Synchronizing Adventure Works DW Partitions Package Sample
A common problem facing developers is how to automate the work of creating partitions within Analysis Services cubes. Partitions are typically sliced by a dimension such as time. For example, in the Adventure Works DW databases, sales data partitions were created statically for the four years 2001 through 2004. However, what happens if the fact tables start to accumulate data outside that date range? If sales data arrives for 2005 and 2006, should the developer create new partitions by hand, or is there a way to automate the creation of new partitions?
The Synchronizing Adventure Works DW Partitions Package Sample is a sample package that demonstrates how to automate the identification and creation of new partitions. Although the sample was written specifically for Adventure Works DW objects, the sample can easily be extended for use in similar environments. This sample is not supported on Itanium-based operating systems.
Important
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.
Requirements
Running this sample package requires the following:
- The sample package and data files that it uses must be installed on the local hard disk drive.
- You must have installed and have administrative permissions on the AdventureWorks OLTP database.
- If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
- If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.
- If you intend to run the sample package on a 64-bit computer, you must set the PreCompile property of the Script task to True. If you want to use the debugging tools to step through the script, set the property to False.
- If the relational and Analysis Services databases are not on the local computer, or if they have been renamed, you must modify the appropriate connection in the sample package to point to the correct database location. You can edit the package directly in SSIS Designer, or you can edit the package configuration.
- To run the script in the package, Analysis Management Objects must be referenced. This requires that the folder, %windir%\Microsoft.net\framework\v2.0.xxxxx, contains a copy of Microsoft.AnalysisServices.dll and the Microsoft Visual Studio for Applications project includes a reference to Analysis Management Objects. The default location from which to copy the assembly is C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.dll.
For more information about how to install samples, see the topic, "Installing Sample Integration Services Packages", in SQL Server 2005 Books Online. To obtain the latest version of the samples, including new samples released since the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).
Location of the Sample Package
If the samples were installed to the default installation location, the Synchronizing Adventure Works DW Partitions sample package is located in the following folder: C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\SyncAdvWorksPartitions Sample\SyncAdvWorksPartitions\
The following files are required to run this sample package.
| File | Description |
|---|---|
Sync Partitions.dtsx |
The sample package. |
Get Partitions List.sql |
The Transact-SQL query used to identify and return partition metadata from the RDBMS. This file contains a copy of the SQL query used in the Execute SQL task, and is not required to run the package; however, you may find it easier to view and edit the SQL statements by using this text file. |
Running the Sample
The package can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.
To run the package by using dtexec
Open a Command Prompt window.
Change the directory to C: Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.
Type the following command:
dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\SyncAdvWorksPartitions Sample\SyncAdvPartitions\Sync Partitions.dtsx"Press Enter.
For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.
To run the package in Business Intelligence Development Studio
Open Business Intelligence Development Studio.
On the File menu, point to Open, click Project/Solution.
Set the Files of type: option to Integration Services Project Files (*.dtproj), locate the SyncAdvWorksPartitions Sample folder, and then double-click the file named Sync Partitions.dtproj.
In Solution Explorer, right-click Sync Partitions.dtsx in the SSIS Packages folder, and then click Execute Package.
For more information about how to run the package in Business Intelligence Development Studio, see the topic, "Running Packages", in SQL Server 2005 Books Online.
Components in Sample
The following table lists the tasks, sources, and destinations that are used in the sample.
| Element | Purpose |
|---|---|
Execute SQL task |
The Execute SQL task, Get Partition List, executes a Transact-SQL query against the fact tables. The query identifies and returns metadata that defines partitions sliced by time. The resulting rowset contains a row for each partition. |
ForEach Loop task |
The Foreach Loop task, Foreach Partition, iterates through each rowset generated in the Execute SQL task. On each iteration the rowset contents are extracted into package variables that are used in both the Script and Analysis Services Execute DDL Tasks. |
Script task |
Within the Foreach Loop task, the Script task, named Partition Already There?, uses Analysis Management Objects (AMO) to identify whether a corresponding partition already exists in the Analysis Services database. This finding is then used to set the |
Analysis Services Execute DDL task |
If required, the Analysis Services Execute DDL task, named Create Partition, executes the XMLA script held in the |
Analysis Services connection manager |
The connection manager, AS database, is used by the Execute DDL task to connect to the Analysis Services database that contains partitions in the Internet Sales, Internet Orders and Customer Count measure groups. |
OLE DB connection manager |
The connection manager, RDBMS database, is used by the Execute SQL task to query the relational database that contains the fact tables FactInternetSales and FactResellerSales, and the dimension table DimTime. |
Sample Results
Because the partitions already exist within the Analysis Services database, no new partitions will be created when the package is executed.
If you want to see the package create new partitions within the Analysis Services database, you can delete partitions from the following measure groups of the Adventure Works cube: Internet Sales, Internet Orders, Reseller Sales, Reseller Orders or Customer Count. When the package is run, the partitions you deleted from the measure groups will be re-created (although without any aggregations) after which the partitions can be reprocessed.
Note If you will be deleting and then re-creating objects in the Analysis Services Adventure Works DW database, we recommend that you make a backup of the current database before you run the sample package.