Integration Services - Professional SQL Server Analysis Services 2005 with MDX - page 853

Processing an Analysis Services Object
SSIS provides a task for processing Analysis Services objects. You can pro-
cess an entire Analysis Services database or choose a specific dimension or
cube or even partitions for processing using the SSIS task called the SQL
Server Analysis Services Processing task. The Analysis Services processing
task is useful whenever you have changes in your relational data that need to
get propagated to the cube. Often retail companies have new products added
to their catalog every day, and the products table gets updated with the new
products or changes in existing products as a daily batch process. Also, the
daily sales data gets updated in the relational database as a nightly batch
process. In order to propagate these changes to the cube for analysis, the di-
mensions and cubes need to be processed unless you have set the storage
mode as ROLAP for dimensions and cubes. There are several considerations
involved in determining frequency of processing. Should cubes be processed
on a daily, weekly or monthly basis? The decision to process the Analysis
Services objects is typically based upon the size of the dimensions and
cubes, how often data changes on the relational database, and the frequency
with which business analysts analyze the cube data. In most cases there are
additions to the products table rather than updates, and hence an incremental
process of the products table might be sufficient. If your fact table gets up-
dated with daily transactional data in the same table, you have the option of
creating new partitions in the cube on a daily/weekly basis or doing a full pro-
cess of the cube. The Microsoft operations guide for Analysis Services sug-
gests you have a new partition for every 20 million records or when the parti-
tion file reaches 5GB — in this way you can achieve optimal performance.
How you partition your data is based on decisions that relate to your business
needs. To create an Integration Services package that processes an Analysis
Services Sales partition, do the following:
1. Right-click the SSIS Packages folder and select New SSIS Package.
Name the package PartitionProcessing.
2. Similar to what we did in the Backup package earlier in this chapter;
create a connection to the Analysis Services Tutorial 2005 database
in Connection Managers.