Modernizing BizTalk Server BAM with PowerPivot

Business Activity Monitoring (BAM) is one of the most attractive features of the BizTalk Server product family. Since its first release in 2004, BAM have been positioned as a mechanism to enable real time visibility across business processes implemented using BizTalk Server or other technologies such as Windows Communication Foundation (WCF) and Windows Workflow Foundation (WF).

Whether BAM has over delivered on this promise, we should admit that it is still challenging to implement and maintain medium to large BAM-based solutions in enterprise applications. Why? Well, in my opinion, BizTalk Server BAM is still based on a traditional business intelligence model on which OLAP cubes and ETL packages are at the center of the data analysis process. In a typical BAM implementation, a business user (which always ends up being a BizTalk architect) defines the activity model and that gets translated into a set of tables in the BAMPrimaryImport as well as multidimensional models defined in the BAMStarSchema and BAMAnalysis databases.

As mentioned before, BAM’s model is based on the traditional BI techniques such as cubing and ETL. Whether this type of models very effective to enable long terms analysis or large data volumes is has proven to be really unpractical to visualize the information near real time and find answers quickly which, ironically, is one of the fundamental principles of BAM! Let’s try to explain this reasoning using some of the major challenges of BAM implementations:

· Processing time: BAM SSIS packages take time to run and calculate the multidimensional measures and dimensions.

· Model versioning: Adding new dimensions and measures to an existing BAM model without affecting the existing data is extremely challenging

· Rapid Visualization: Given the dynamic characteristics BAM activity data (aka the data processed by BAM models), it is very often to find users wanting to visualize, sort, search the data in different ways. Enabling reports for all these views is completely unpractical from a long term perspective.

· Maintenance: Traditional BI infrastructures based on cubes and ETL packages are really expensive to maintain both from the infrastructure as well as from the people standpoint.

Some of these challenges were the fundamental catalyst for the adoption of self-service BI technologies a few years ago. In a self-service BI model, the data is made available to the user in an optimized manner (very often using an in-memory engine) so that it can be processed according that the user’s specific perspective. This is a very popular model in financial systems or government solutions on which the user dictates how the data is visualize instead of accessing via a preconceived report.

With the release of SQL Server 2008 R2, Microsoft gave a big step towards adopting self-service BI technologies with the release of PowerPivot for Excel and SharePoint respectively. PowerPivot allows users to load incredibly large amounts of data using an in-memory analysis service engine that runs within Excel or a SharePoint Server farm. You can find more details about this technology here: http://www.powerpivot.com/

From a BizTalk developer perspective, PowerPivot can modernize and extend the scenarios for using BAM within an enterprise. At a first glance, I can see some immediate benefits:

• Simplification: The use of PowerPivot will enable the creation of  simpler BAM models that don’t necessarily have to include all the multidimensional information.
• Personalization: Users will be able to analyze the BAM data according to their specific perspective.
• Maintenance: Simpler BAM model will translate into less of nightmare from the maintenance standpoint.
• Real time visualization: By using PowerPivot with BAM, users will be able to visualize the information near real time and modify it according to their perspective.
• Performance: Having the BAM data loaded into the PowerPivot engine will prevent from users to repeatedly hit the BAM databases when accessing the information.
• Collaboration: When using PowerPivot for SharePoint, users can take advantage of the features included in SharePoint 2010 to enable a more collaborative approach as part of their BI initiatives.

Well, enough talking…. Let’s take a look at a sample. Suppose that we have the following BAM model that describes a purchase order activity.

PO_CREATED              Milestone
PO_PROCESSED         Milestone
Amount                         Double
Customer_Country         Text
Customer_State             Text

Notice that the previous model doesn’t include any multi-dimensional information in order to keep it extremely simple.

The following code creates A MILLION instances of the purchase order activity.

   1: private static void PopulateBAMModel()

   2:      {

   3:          var r = new Random();

   4:          BufferedEventStream bamStream = new BufferedEventStream(BTS MsgBox, 0);

   5:          for (int index= 0; index <= 1000000; index++)

   6:          {

   7:              var activityID = Guid.NewGuid().ToString();

   8:              bamStream.BeginActivity(cActivityName, activityID);

   9:              var country = GetCustomerCountry();

  10:              var state = GetCustomerState(country);

  11:              bamStream.UpdateActivity(cActivityName, activityID, "PO_CREATED",

  12:                  DateTime.Now.Subtract(new TimeSpan(r.Next(1, 11),

  13:                  r.Next(1, 24), 0, 0)),

  14:                  "PO_PROCESSED", DateTime.Now,

  15:                  "Amount", GetPOAmount(), "Customer_Country",

  16:                  country, "Customer_State", state);

  17:              bamStream.EndActivity(cActivityName, activityID);

  18:          }

Using PowerPivot for Excel we can point it to the PO_AllInstances view and load the entire data set in just a few seconds. The following two figures illustrate that concept.

Having the data loaded, we can now take advantage of the entire Excel calculation engine to aggregate the data according our perspective. For instance, the following figure illustrates the process of creating a new chart that renders our BAM activity instances using the Excel Pivot control.

Using these techniques, we can analyze the data without accessing the BAM databases. At any point we can reload the data using PowerPivot’s data refresh mechanisms.

In order to enable collaboration and guarantee the scalability of the solution, we can publish the PowerPivot BAM workbook to SharePoint and access it without the need to having Excel 2010 installed in your local machine.

If you are a BizTalk developer, I truly hope these type of techniques makes as much sense to you as they do to me. Bottom line, PowerPivot is an ideal complement to BizTalk Server BAM and by no means a replacement to the existing infrastructure. When applied correctly, the combination of PowerPivot and BAM can enable scenarios have been prohibited for BizTalk Server until now.