Synchronizing Adventure Works DW Partitions Package Sample

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
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 Integration Services.

If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.

If you plan 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 step through the script by using debugging tools, you should 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 (AMO) 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 2005 Tools
for Applications (VSTA) project includes a reference to Analysis Management Objects. The default location from which to copy the assembly is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.dll. For more information about
how to install samples, see the topic, "Installing Sample Integration Services Packages", in SQL Server Books Online.

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\100\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\100\DTS\Binn, the location of
dtexec.

Press Enter.For more information about how to run the package by using the
dtexec utility, see the topic, "dtexec Utility", in SQL Server 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 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 IsNotPresent variable. Additionally, this task also populates the
XMLA_Script variable. This variable can be used to create a new partition in the Analysis Services database.

Analysis Services Execute DDL task

If required, the Analysis Services Execute DDL task, named Create Partition, executes the XMLA script held in the
XMLA_Script variable to create a new partition in the Analysis Services database. This task contains a conditional precedence constraint that prevents the task from executing unless the
IsNotPresent variable evaluates to true.

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.