Export (0) Print
Expand All

BizTalk Server Interchange Throughput Analysis

 

Mak Rowshan
Microsoft Corporation

June 2002

Applies to:
   Microsoft BizTalk Server 2002
   Microsoft SQL Server 2000

Summary: This article illustrates how you can leverage SQL Server Analysis Services to analyze the BizTalk Server’s document processing. By creating a cube using the BizTalk Server’s document tracking database, you can aggregate the number and sizes of interchanges by source, destination, document type, transport type, and time. Using the PivotTable Office Web Component, you can easily browse this cube and analyze your BizTalk Server’s document throughput as well.

Contents

Introduction
A View to a Cube
Create the Data Source
Create the Dimensions
Source Organization Dimension
Destination Organization Dimension
Source Application Dimension
Destination Application Dimension
Direction Dimension
Transport Dimension
Time Dimension
Build the Cube
Refreshing the Cube

Introduction

To identify data transfer trends, patterns, and possible bottlenecks in BizTalk Server EAI and B2B solutions, it is important to know the volume, size, and type of submitted documents. Furthermore slicing and dicing these performance numbers by transport type, source and destination, time and other important criteria is valuable. BizTalk Document Tracking allows for queries against the interchanges and documents. As such it is very useful for tracking a given interchange and document. However to look at the big picture and aggregate the details, an OLAP solution is more suitable.

A View to a Cube

All of the data columns used for building this cube are stored in one table, namely dta_interchange_details in the Interchange_DTA database. However some of the columns in this table are foreign keys that refer to descriptive values in other tables. We can create a view that provides all the necessary data for building the cube, as shown in the following diagram.

Ee265639.bts_intercube_01(en-US,BTS.10).gif

The following SQL script creates the view in the Interchange_DTA database.

SELECT   dbo.dta_interchange_details.nInterchangeKey, 
dbo.dta_interchange_details.dtProcessedTimeStamp, 
dbo.dta_interchange_details.nvcSrcAliasId, 
dbo.dta_interchange_details.nvcDestAliasId, 
 dbo.dta_transport_type_values.nvcTransportTypeName, 
dbo.dta_interchange_details.nvcTransportAddress, 
 dbo.dta_interchange_details.nvcServerName, 
 dbo.dta_interchange_details.nNumberOfBytes, 
dbo.dta_direction_values.nvcDirectionName, 
 dbo.dta_interchange_details.nvcSrcAppName, 
 dbo.dta_interchange_details.nvcDestAppName, 
dbo.dta_interchange_details.uidInterchangeGUID, 
 dbo.dta_interchange_details.dtTimeSent, 
 dbo.dta_indoc_details.nvcDocType
FROM dbo.dta_interchange_details INNER JOIN
dbo.dta_transport_type_values ON 
dbo.dta_interchange_details.nTransportType = 
dbo.dta_transport_type_values.nTransportTypeKey INNER JOIN
dbo.dta_direction_values ON dbo.dta_interchange_details.nDirection = 
dbo.dta_direction_values.nDirectionKey LEFT OUTER JOIN
dbo.dta_outdoc_details ON 
dbo.dta_interchange_details.nInterchangeKey = 
dbo.dta_outdoc_details.nInterchangeKey LEFT OUTER JOIN
dbo.dta_indoc_details ON dbo.dta_interchange_details.nInterchangeKey 
= dbo.dta_indoc_details.nInterchangeKey


Create the Data Source

  1. Use the Analysis Manager to create a new database or open existing database. This database will contain the BTS throughput cube that you will create.
  2. Create a new data source in this database, by right clicking on Data Sources in the Analysis Manager explorer window, and clicking on New
  3. Select Microsoft OLE DB Provider for SQL Server as the provider on the first tab.
  4. Click on the Connection tab. Enter the name of the server, where you created the view in Step 1, as the source of data.
  5. Enter the user name and password for an account that has read access to the BizTalk Tracking Database.
  6. Select the BizTalk Server Tracking Database (Interchange_DTA) from the server drop down list box.
  7. Click on Test Connection to make sure the data source is functional. Then click on OK to save the data source.

Create the Dimensions

Creation of the following shared dimensions allows you to slice and dice BizTalk Server’s throughput from different perspectives, namely from transport type to document type to source. Most of the dimensions presented below are simple one level dimension. The only exceptions are Time and Transport Type dimensions which have multiple levels.

Even though what is presented in this article limits the dimensions to the content of BizTalk Server tracking database, you may refine the dimension and measures by adding custom data to what BizTalk provides. For example, this article discusses a Source Organization dimension that contains one level. You may categorize the source organizations into suppliers, internal applications, external applications, etc. This will allow you to aggregate the throughput by source organization categories and then, as needed, drill down to each category for more detailed information.

Source Organization Dimension

  1. Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Editor. This allows you to use the dimension editor to create a new shared dimension
  2. In the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.)
  3. In the Dimension Editor window right click on <new> and select New Level, then select the nvcSrcAliasId column from the “Insert Level” dialog box.
  4. Rename the newly created level to “Source Organization” by right clicking on it and selecting Rename from the pop-up menu.
  5. Save the dimension and enter “Source Organization” when prompted for its name.
  6. Select Process Dimension from the Tools menu item to populate the dimension with data.

Destination Organization Dimension

Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Editor. This allows you to use the dimension editor to create a new shared dimension.

  1. In the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.)
  2. In the Dimension Editor window right click on <new> and select New Level, then select the nvcDestAliasId column from the “Insert Level” dialog box.
  3. Rename the newly created level to “Destination Organization” by right clicking on it and selecting Rename from the pop-up menu.
  4. Save the dimension and enter “Destination Organization” when prompted for its name.
  5. Select Process Dimension from the Tools menu item to populate the dimension with data.

Source Application Dimension

This article takes the approach of defining separate dimensions for source and destination organizations and applications. Since organizations and applications are usually used differently in implementing business processes with BizTalk Server. However, an alternative approach may be to roll both organizations and applications in the same dimension. If the later approach is used, you may define two levels for the dimension, one being the source or organization type, and the other the actual source or destination.

  1. Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Editor. This allows you to use the dimension editor to create a new shared dimension.
  2. In the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.)
  3. In the Dimension Editor window right click on <new> and select New Level, then select the nvcSrcAppName column from the “Insert Level” dialog box.
  4. Rename the newly created level to “Source Application” by right clicking on it and selecting Rename from the pop-up menu.
  5. Save the dimension and enter “Source Application” when prompted for its name.
  6. Select Process Dimension from the Tools menu item to populate the dimension with data.

Destination Application Dimension

  1. Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Editor. This allows you to use the dimension editor to create a new shared dimension.
  2. In the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.)
  3. In the Dimension Editor window right click on <new> and select New Level, then select the nvcDestAppName column from the “Insert Level” dialog box.
  4. Rename the newly created level to “Destination Application” by right clicking on it and selecting Rename from the pop-up menu.
  5. Save the dimension and enter “Destination Application” when prompted for its name.
  6. Select Process Dimension from the Tools menu item to populate the dimension with data.

Direction Dimension

Direction is an attribute indicating whether the document is incoming or outgoing.

  1. Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Editor. This allows you to use the dimension editor to create a new shared dimension.
  2. In the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.)
  3. In the Dimension Editor window right click on <new> and select New Level, then select the nvcDirectionName column from the “Insert Level” dialog box.
  4. Rename the newly created level to “Direction” by right clicking on it and selecting Rename from the pop-up menu.
  5. Save the dimension and enter “Direction” when prompted for its name.
  6. Select Process Dimension from the Tools menu item to populate the dimension with data.

Transport Dimension

  1. Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Editor. This allows you to use the dimension editor to create a new shared dimension.
  2. In the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.)
  3. In the Dimension Editor window right click on <new> and select New Level, then select the nvcTransportTypeName column from the “Insert Level” dialog box.
  4. Rename the newly created level to “Transport Type” by right clicking on it and selecting Rename from the pop-up menu.
  5. In the Dimension Editor window right click on <new> and select New Level, then select the nvcTransportAddress column from the “Insert Level” dialog box. This will create the second level in the dimension.
  6. Rename the newly created level to “Transport Address” by right clicking on it and selecting Rename from the pop-up menu.
  7. Save the dimension and enter “Transport” when prompted for its name.
  8. Select Process Dimension from the Tools menu item to populate the dimension with data.

Time Dimension

The following steps, unlike to ones used so far, use the Dimension Wizard to create the Time dimension. The Dimension Wizard provides additional built-in functionality for creation of time dimensions.

  1. Right click on Shared Dimensions, in Analysis Manager. Select New, and then click on Wizard.
  2. Select Star Schema in response to “How you want to create the dimension”, and click on Next.
  3. On the “Choose a Dimension Table” dialog box, select the view that you created in step 1 (vBTSCube.) Then click on Next.
  4. Select “Time Dimension” as the dimension type. A drop down list box allows you to select the column. This list box is populated with the existing date fields. Select dtTimeSent as the Date column. Then click on Next.
  5. The “Create time dimension levels” dialog box allows to define the granularity of the time dimension. Accept the default as Year, Quarter, Month, Day. If you’d like to drill down to more granular levels, you may add Hour and Minute to your levels.
  6. Click Next on the Advanced Options page, and enter “Time” for the name of the dimension. Click on Finish to generate the Time dimension. Finally select Process from the Tools menu item to populate the dimension.

This article uses the dtTimeSent date field from the BizTalk Server Tracking Database as the source for building the time dimension. However, this field will not have entries for the dates when no interchanges occurred. So to have a complete time dimension, you may use an existing time dimension such as the one provided with the Foodmart sample database, or use a stored procedure to create a complete time table to be used as the source for your Time dimension.

The following table provides a summary of the dimensions and their sources.

Dimension

Name

Dimension

Table

Dimension

Column

Level(s) Name
TimevIntAnalysisdtTimeSentYear
Quarter
Month
Day
Source OrganizationvIntAnalysisnvcSrcAliasIdSource Organization
Destination OrganizationvIntAnalysisnvcDestAliasIdDestination Organization
Source ApplicationvIntAnalysisnvcSrcAppNameSource Application
Destination ApplicationvIntAnalysisnvcDestAppNameDestination Application
DirectionvIntAnalysisnvcDirectionNameDirection
TransportvIntAnalysisNvcTransportTypeName
nvcTransportAddress
Transport Type
Transport Address

Creating the above dimensions as shared dimensions allows you to use the dimensions in multiple cubes. This is especially useful for a clustered BizTalk Server environment in where more than one SQL Server databases are actively tracking documents. So you will need to create more than one cube.

Build the Cube

  1. Create the cube by right clicking on Cubes in Analysis Manager, and then clicking on New and selecting Editor. This will allow you to create a new cube using the Cube Editor.
  2. Select the view created in Step 2 (vBTSCube) as the fact table.
  3. Dimensions: Right click on Dimensions and select Existing Dimensions. In the “Dimension Manager” dialog box select all of the dimensions that you created in Step 3.
  4. Measures: You can easily add two measures to the cube, the number of documents, and the size of documents.
  5. To create the Count measure, right click on the Measures, select New Measure, and then select the nInterchangeKey from the “Insert Measure” dialog box. Change the measure name to Count by entering the name in the Properties under the Basic tab. Also change the Aggregate Function to Count in the Properties under the Basic tab. This will tell the Analysis Services to count the number of records rather than adding their values.
  6. To create the Size measure, right click on the Measures, select New Measure, and then select the nNumberOfBytes from the “Insert Measure” dialog box. Change the measure name to Size by entering the name in the Properties under the Basic tab. This measure shows the size of the document in bytes.
  7. You may create a calculated measure to show the size of the document in KB instead of bytes. To create this measure, right click on Calculated Members in the Cube Editor and select New Calculated Member. Enter the following in the Value Expression text box: [Measures].[Size] / 1024
  8. Also change the Member name to “Size KB”. If you want to only show the size in KB, click on Size under Measures, and change its Visible property under Advanced Properties to False.
  9. Save the cube as “BTS Throughput”.
  10. Click on Tools from the menu bar and select Process Cube to populate the cube with data.

Refreshing the Cube

You can create a DTS package and schedule it to refresh the cube’s data.

  1. Use the SQL Server Enterprise Manager to open the database in which you want to create the DTS package.
  2. Right click on Local Packages under the Data Transformation Services and select New Package.
  3. Select the Analysis Services Processing Task from the task pane and drag it to the package window.
  4. Select the BTS Throughput cube in the tree view.
  5. Select Refresh Data as the processing option.
  6. Save and close the package.
  7. To schedule the package, right click on it, and select Schedule. In the Schedule dialog box, select the occurrence, frequency, and duration, and click on OK to save the schedule.

Creating the User Interface for the Browsing the Cube

You may use Microsoft Excel, or create a form with Visual Basic, or write an ASP page to browse the cube. Following are the instructions for browsing the cube in Excel.

Using Excel

You can create a PivotTable in Excel and link it to the BTS Throughput cube which you generated in the previous steps.

  1. Open an Excel Workbook. Select Data from the menu bar, then select Import External Data, and then Import Data.
  2. Define the data source type: In the Select Data Source dialog box, select “OLAP Queries/Cube Files” from the “Files of type:” combo box.
  3. Select the data source: If you have already created a data source for the BTS Throughput cube, select it. Otherwise create a new data source as shown in the next step.
  4. Create a new data source: Click on the “New Source …” button.
  5. Select “Microsoft SQL Server OLAP Services” on the Data Connection Wizard and click on Next.
  6. Enter the server name where the BTS Throughput resides and the Logon credentials. Click on Next.
  7. Select the OLAP database from the combo box and select the BTS Throughput cube. Click on Next. Click on OK and then Finish. This step creates the data source. Click on Open to open the new data source.
  8. Accept the selected range for the PivotTable. This creates the PivotTable inside your Excel Workbook.
  9. You can drag the cube fields from the PivotTable Field List pop-up and drag them to the row, column, and data areas.

The following diagram shows the PivotTable where the Date is the column field, Transport Type is the row field, and Count and Size are the data items. This PivotTable allows you to aggregate, dice, and slice the number and sizes of the documents by transport type and address.

 Click here for larger image

Figure 2. Pivot Table Example

You can also use the PivotTable control from the Office Web Components on an HTML page or a Visual Basic form to browse the cube.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft