Creating a Partitioned View in the BAM Archiving Database

Article
11/15/2011

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!

Creating a Partitioned View in the BAM Archiving Database

Additional resources