Creating a Partitioned View in the BAM Archiving Database
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:
https://msdn.microsoft.com/en-us/library/aa562047.aspx
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:
USE [BAMArchive]
GO
CREATE PROCEDURE [dbo].[CreateBamActivityView]
(
@activityName nvarchar(64),
@viewType nvarchar(64) = 'Instances'
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @partitionName nvarchar(128);
DECLARE @tableName nvarchar(128);
DECLARE @viewName nvarchar(128);
DECLARE @templateName nvarchar(128);
DECLARE @columnNames nvarchar(max);
DECLARE @schema nvarchar(12);
DECLARE @isFirstTable bit;
DECLARE @dropScript nvarchar(max);
DECLARE @createScript nvarchar(max);
DECLARE @newLine nvarchar(12);
DECLARE @likeData nvarchar(128);
DECLARE @likeDefault nvarchar(128);
DECLARE @likeFailed nvarchar(128);
DECLARE @likeUsage nvarchar(128);
SET @newLine = CHAR(13) + CHAR(10);
SET @schema = 'dbo';
SET @templateName = N'bam_' + @activityName + N'_' + @viewType;
SET @viewName = N'[' + @schema + '].[bam_' + @activityName + '_' + @viewType + 'View]';
SET @likeDefault = N'bam[_]' + @activityName + N'[_]' + @viewType;
SET @likeData = @likeDefault + N'[_]%';
SET @likeFailed = @likeDefault + N'[_]%[_]Failed';
-- Define a table of the expected columns based on the table template
DECLARE @activityColumns TABLE (activity_columnname varchar(256));
INSERT INTO @activityColumns
SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @templateName;
-- See if activity has data otherwise create default view
IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE [name] LIKE @likeData AND [name] NOT LIKE @likeFailed AND [type] = N'U')
BEGIN
SET @likeUsage = @likeData;
END
ELSE
BEGIN
SET @likeUsage = @likeDefault;
END;
SET @isFirstTable = 1;
SET @dropScript = N'IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''' + @viewName + '''))'
+ @newLine + ' DROP VIEW ' + @viewName + ';'
-- Define a cursor to iterate through created table
DECLARE instance_cursor CURSOR LOCAL FOR
SELECT [name] FROM sys.sysobjects
WHERE [name] LIKE @likeUsage AND [name] NOT LIKE @likeFailed AND [type] = N'U';
OPEN instance_cursor;
FETCH NEXT FROM instance_cursor INTO @partitionName;
WHILE @@fetch_status = 0
BEGIN
IF (@partitionName IS NOT NULL)
BEGIN
SET @tableName = N'[' + @schema + '].[' + @partitionName + N']';
IF (@isFirstTable = 1)
BEGIN
SET @createScript = N'CREATE VIEW ' + @viewName + N' AS ' + @newLine;
SET @isFirstTable = 0;
END
ELSE
BEGIN
SET @createScript = @createScript + @newLine + N' UNION ALL' + @newLine;
END
-- Calculate the column defintions based on the working partition
SET @columnNames = '';
SELECT @columnNames = @columnNames +
CASE
WHEN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @partitionName AND COLUMN_NAME = activity_columnname) IS NOT NULL THEN '[' + RTRIM(activity_columnname) + ']'
ELSE 'NULL AS [' + RTRIM(activity_columnname) + ']'
END + ', '
FROM @activityColumns
OPTION (FAST 1);
IF (LEN(@columnNames) > 0) SET @columnNames = SUBSTRING(@columnNames, 1, LEN(@columnNames) -1);
-- Add the table into the view definition
SET @createScript = @createScript + N' SELECT ' + @columnNames + ' FROM ' + @tableName + ' WITH (NOLOCK)';
END;
FETCH NEXT FROM instance_cursor INTO @partitionName;
END
IF (@createScript IS NOT NULL)
BEGIN
SELECT @createScript = @createScript + ';';
END;
CLOSE instance_cursor;
DEALLOCATE instance_cursor;
-- Display commands to be executed
PRINT @dropScript;
PRINT @newLine;
PRINT @createScript;
-- Execute the creation
EXEC(@dropScript);
IF (@createScript IS NOT NULL)
BEGIN
EXEC(@createScript);
END;
END
GO
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:
CREATE TABLE [dbo].[bam_Audit_Instances] (
[RecordID] [bigint] NOT NULL,
[ActivityID] [nvarchar](128) NOT NULL,
[Audited] [datetime] NULL,
[AuditReference] [nvarchar](38) NULL,
[AuditAction] [nvarchar](50) NULL,
[Caller] [nvarchar](50) NULL,
[AuditPreChange] [nvarchar](1000) NULL,
[AuditPostChange] [nvarchar](1000) NULL,
[LastModified] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)
)
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:
SELECT [RecordID], [ActivityID], [Audited], [AuditReference], [AuditAction], [Caller],
[AuditPreChange], [AuditPostChange], [LastModified]
FROM [dbo].[bam_Audit_Instances_20111111] WITH (NOLOCK)
UNION ALL
SELECT [RecordID], [ActivityID], [Audited], [AuditReference], [AuditAction], [Caller],
[AuditPreChange], [AuditPostChange], [LastModified]
FROM [dbo].[bam_Audit_Instances_20111112] WITH (NOLOCK)
UNION ALL
SELECT [RecordID], [ActivityID], [Audited], [AuditReference], [AuditAction], [Caller],
NULL AS [AuditPreChange], NULL AS [AuditPostChange], [LastModified]
FROM [dbo].[bam_Audit_Instances_20111012] WITH (NOLOCK)
UNION ALL
SELECT [RecordID], [ActivityID], [Audited], [AuditReference], [AuditAction], [Caller],
NULL AS [AuditPreChange], NULL AS [AuditPostChange], [LastModified]
FROM [dbo].[bam_Audit_Instances_20111011] WITH (NOLOCK);
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. Enjoy!