When you run the BAM data maintenance package (BAM_DM_<activity name>) BAM copies each partition in the BAM Primary Import database to a separate table in the BAM Archive database. You can create partitioned views in the BAM Archive database to facilitate locating the data. However one is left to create these partitioned views oneself.
A version of a script that can be used to create these partitioned views can be found on the MSDN site:
Whereas this version of the script will work when the BAM activities are first deployed, one will get issues if one modifies the BAM configuration by adding new activity items. The sample script works by creating a view that does a UNION ALL of SELECT * FROM BAM_DM_<activity partition>.
This works fine as long as the activity definition is unchanged, but the view falls over if a new item is added. This is because the schema of the archive partitions is no longer consistent. There are also issues for failed partitions as they are not excluded from the partitioned view.
However, one can easily resolve this issue by using explicit column names. As activities are augmented with new items the base table definition in the BAM Archive database is modified such that it represents the full items list for the activity. Using this one is able to determine the columns required for the partitioned view.
Thus when creating a column list for an archived partition one has to determine one can match the columns with those needed for the view. If a column is not present in a partitioned table then the column definition is modified to be:
NULL AS [column_name]
This ensures consistency for all SELECT statements that make up the partitioned view.
Here is a full listing of a stored procedure one can use to create a new partitioned view:
To create a new partitioned view for an activity one merely has to call the stored procedure with the activity name:
Consider a simple activity definition for Audit:
A partitioned view could be created for the activity using:
EXEC dbo.CreateBamActivityView 'Audit'
If the activity was changed over time, with the addition of the pre and post change columns, the view that would be created would be:
To use the stored procedure that creates this view one merely has to schedule the procedures execution after each archive run.
Hope you find this useful.
Written by Carl Nolan