How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017
BizTalk Orchestrations compose the physical implementation of a Business Process. In a typical Orchestration, you may share data with an external system or invoke a Web service. Although your clients may be pleased with the new automation, automating the process with an Orchestration oftentimes is only part of the solution. Not long after an Orchestration is implemented, clients begin asking questions like "how long does it take to move from step 1 to step 5?" or "what is currently in process?"
To gather such metrics on the steps within your BizTalk 2004 Orchestration, check out Business Activity Monitoring (BAM). With BAM, you can log numeric, textual, and chronological data about any step in an Orchestration to a SQL Server database and even an Analysis Services Cube. With the logged data, you can build reports or custom monitoring applications for BizTalk Orchestrations.
Using a sample application, this article shows you how to set up BAM for your own BizTalk Orchestration. Before diving into the sample application, however, here's an overview of BAM.
What Is BAM?
BAM is composed of the following items:
- The BAM Definition Workbook is an Excel add-in built into an Excel document, which you use to build BAM definition Activities and Views (more on these in a moment). You use the Excel spreadsheet to create a BAM Definition.
- The BAM API is used by BizTalk and your BizTalk Orchestration. The API is divided into a synchronous (DirectEvent Stream) and an asynchronous (BufferedEvent Stream) variety. (The article discusses the BAM API in more detail later.)
- A set of SQL Server databases store BAM data in different stages. Figure 1 shows the BAM Primary Import database, a Staging database, and an Analysis Cube.
Figure 1. Simplified View of All BAM Server-side Components
As you can see, BAM is separate from the BizTalk Service. BizTalk interacts with BAM through the BAM API. The BAM API updates the BAMPrimartImport database. Separately, a set of DTS packages are responsible for archiving the data, staging the data in BAM Star Schema, and interacting with Analysis Services.
You don't have to wire BAM into your Orchestration to use BAM features. However, if you don't like how BAM logs particular pieces of information, you can build custom features at different levels within BAM, including making BAM part of the Orchestration. (The default and custom behaviors of BAM are discussed later.)
You begin BAM development by creating a BAM Definition using the BAM Excel document. As stated earlier, you set up Activities and Views in the BAM Definition. Activities contain Items, and Views contain Measures. Items are the things you log, and Measures define how you slice and dice the Items.
Before learning the BAM Definition, look at the Orchestration sample application.
The Sample Application
Figure 2 shows the sample Orchestration.
Figure 2. Orchestration of Sample Application
Two ports are set up using the File Adapter. A Receive File adapter Port initiates the Orchestration. The receive message contains three pieces of information:
- IDValue: a representation of a unique value identifying the Orchestration
- NumIterations: a parameter passed to a class in an assembly
- Descrip: a description of the message
A Construct Message utilizes a transformation to create the message for the Send Port. Next, the Orchestration invokes a class from a .NET assembly, invokes some BAM API code, and then posts a message to the Send port.
Everything is now in place to begin BAM development. The first step is creating a BAM Activity.
Creating a BAM Activity
The BAM Definition Excel spreadsheet is engineered for use by information workers rather than developers. So, defining Activities requires no coding. Start by making a copy of the BAM.xls spreadsheet located on the following path:
C:\Program Files\Microsoft BizTalk Server 2004\Tracking
The BAM Add-in appears as a menu option when you open the spreadsheet. See Figure 3.
Figure 3. Sample Menu
When you select BAM Activity, the BAM Activity wizard appears. From the Activity wizard screen, select "New Activity". The dialog in Figure 4 appears.
Figure 4. Dialog from New Activity Selection
As you'll see later, the Activity Name will appear in SQL tables when the BAM definition is loaded. Items for an Activity fall into the categories shown in the dialog in Figure 5.
Figure 5. Dialog of the Item Categories
All but Business Milestones are self-explanatory. Business Milestones are datetime value fields. Use Milestones when you want to record the time a step in your orchestration executed.
As you define the items in your Activity, keep in mind that each instance of your Orchestration creates one record in the SQL database. So, for example, if you have a loop in your Orchestration recording a Milestone, the most recent datetime value will be recorded in the database.
The sample contains two Activities: IterationTracking and IterationTrackingDirect. Once you've created your Activities, you can create Views to aggregate the Activity Data collected by BAM.
Creating a BAM View
The BAM View Wizard also can be accessed from the BAM Excel menu. Once you access the BAM View Wizard and elect to create a new View, the dialog in Figure 6 appears.
Figure 6. New View Dialog
As you can see, you can select multiple Activities. Once you've created your View, you must define Dimensions and Measures. Think of Dimensions as the "what" and Measure as the "Value." Measures allow you to aggregate Items in your view; thus, measures are confined to numeric Items you collect in your BAM Activity (see Figure 7).
Figure 7. Measures Dialog
Dimensions explain what a Measure represents. Dimensions can be composed of combinations of Activity Items. The Dimension dialog for a Business Milestone appears in Figure 8.
Figure 8. Dimension Dialog for a Business Milestone
You are now ready to deploy the BAM Definition.
Deploying the BAM Definition
You can deploy your BAM Definition by using a command-line utility called BM.exe. BM.exe accepts the definition in Excel or an exported XML format. I recommend exporting to XML, mainly because if the computer you are running BM.exe from does not have Excel loaded, you will get an error message. Figure 9 shows the output of the BM.exe utility.
Figure 9. Output of the BM.exe Utility
When BM.exe deploys the BAM Definition, it creates SQL Tables and, if you've defined Views, Analysis Services Cubes. BM also creates Data Transformation Services (DTS) packages that you can modify to move the BAM data into the desired place.
As BAM writes the Items defined in your Activities, it places the data in a number of databases, beginning with the BAMPrimaryImport database.