BAM – SQL Server Views in the BAM Primary Import Database

Posted: August 23, 2010  |  Categories: BAM BizTalk Deployment

When you issue the deploy-all command using “bm.exe” tool, BAM automatically creates the underlying data model for your activities and views.

Microsoft has committed to maintaining the structure of the views in futures versions of the product so you can read the view data using any programming tool that works with SQL Server.

The term view in the content of BAM is used with two different meanings. A SQL Server view is a virtual table that is defined using a SELECT statement. A BAM view is derived from an activity by subsetting, aggregation, or aliasing. SQL Server view is used to implement both BAM activities and BAM views

The views that are directly derived from the deployment of a BAM activity:

  • bam_<Activity Name>_ActiveInstance: Returns the list of the active activity instances based on the records stored in the active activity table.
  • bam_<Activity Name>_AllInstances: Joins the active and complete activity views, returning a list of all the BAM activity instances, in other words, return all instances of the activity regardless of state.
  • bam_<Activity Name>_AllRelationships: Joins the active and complete activity relationship tables, returning a list of all the relationships of the BAM activity instances.
  • bam_<Activity Name>_CompletedInstances: Returns the list of the completed activity instances based on the records stored in the completed activity table.
  • bam_<Activity Name>_InstancesForArchive: return all instance of the activity that will be archived on the next execution of the DTS Package.

Additionally, a new set of views are created for each activity view that we include in the BAM activity model:

  • bam_<ActivityName>_<ActivityView Name>_ActiveView: Returns the list of active activity instances including the calculations of the required dimension levels.
  • bam_<ActivityName>_<ActivityView Name>_ActiveAliasView: Return the list of items currently in the active state by alias.
  • bam_<ActivityName>_<ActivityView Name>_CompletedView: Returns the list of completed activity instances including the calculations of the required dimension levels.
  • bam_<ActivityName>_<ActivityView Name>_CompletedAliasView: Items currently in completed state by alias.
  • bam_<ActivityName>_<ActivityView Name>_View: Joins the two previous views (ActiveView and CompletedView), returning the list of all activity instances included the calculation of the required dimension levels, in other words, all items in the view.

These views enable two key functionalities in the BAM infrastructure: At the first level, activity views are the fundamental mechanism to expose BAM data to other applications, including other components of the BAM infrastructure like the aggregation process. Additionally, these views include some of the calculations required by elements such as measures that are not directly included in the activity tables.


Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

1 thought on “BAM – SQL Server Views in the BAM Primary Import Database”

  1. Hi Sandro,

    I came across similar kind of scenario where bam data is not moving to completed tabled even though the status of Iscomplete is 1. Not sure how we can check to make it resolve. Any suggestions from you end.

Leave a Reply

Your email address will not be published. Required fields are marked *


Back to Top