Power BI Dashboards

We recently built Power BI Dashboards hosted in the Cloud for surfacing BizTalk BAM aggregates that are tracking near real-time performance metrics. The Dashboard will be used by the customer to monitor BizTalk application performance for all integrations flowing through the Enterprise Service Bus. For example, we track durations for each integration, message sizes, etc., so that the Network Operations Center (NOC) can quickly review the health of the system when issues are reported. For example, when end users complain that the response times are very poor etc., the NOC can go to the Power BI BAM Dashboard and quickly review historical charts to identify potential root causes and then contact the BizTalk DevOps team to look into a specific application that may not be meeting SLAs. We’re also building alerts so that, for example, if we detect a particular integration is performing poorly, or if messages sizes are larger than expected etc., or percentage change in the number of messages received is greater than or less than expected, we send a notification to the NOC. We’re also able to use Power Query to quickly search for a particular transaction, and can either display it in tabular form, or create a chart as part of the query.

Below are some sample screen shots showing how we’ve used Power Query, Power Maps, and Power View to display BAM aggregates.

PowerBIDashboard1
PowerBIDashboard2
PowerBIDashboard3
PowerBIDashboard4
PowerBIDashboard5
PowerBIDashboard6
PowerBIDashboard7
PowerBIDashboard8

BizTalk ESB send port failure with SQL adapter error “Could not find stored procedure ‘usp_insert_Fault'”.

Reconfigured ESB toolkit since code was missing in SQL Server. This happened because the accounts security settings were incorrect (local were being used instead of domain) and when applying configuration, we saw errors regarding group name incorrect etc. SQL DB ‘EsbExceptionDb’ credentials setup for host account access ‘db_owner’ for host account, bts admin group and bts app user group.

When using select for Oracle within send port, you get a Microsoft.ServiceModel.Channels.Common.MetadataException error.

The reason for this is because the generated binding file created the BtsActionMapping with a default operation name (in this case GET_AGGREGATE_VALUES). But this operation name has to match the operation name used by the logical send port in the orchestration.

In BizTalk Administration Console, go back to the send port’s transport properties and modify the SOAP Action header. Change the operation name to match the logical send port’s operation name in your calling orchestration.

BizTalk Server performance was impacted and message flow completely stopped. Deadlock issue reported in SQL Server event log.

We observed throttling and finally message flow within BizTalk completely stopped. When we checked SQL Server event log, we saw the following error:

The following stored procedure call failed: ” { call [dbo].[bts_UpdateMsgbox_BizTalkServerApplication_64]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}”. SQL Server returned error string: “Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.;Duplicate key was ignored.”.