BAM – SQL Server Views in the BAM Primary Import Database

Posted: August 23, 2010 in BizTalk
Tags: , ,

When you issue the deploy-all command using “bm.exe” tool, BAM automatically creates the underlying data model for you 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 are 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.

References:

Tags: BizTalk | BAM | SQL | Views

Comments
  1. Sudheer says:

    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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s