Indexes in the BAM star-schema database

Some customers have asked why some tables in the star-schema database have indexes and others don't.

As suggested by SQL Books Online “Creating and Using Data Warehouses”, all the dimension tables have the index automatically created on the primary key column “_ID”. (BTW these implicit indexes don’t show up in the Query Analyzer, but you can see them in the Enterprise Manager.) In addition, for the hierarchical dimensions (data dimensions and time dimensions) composite indexes are created on all dimension levels.

One place that doesn’t have index is the fact table. Theoretically, the fact table should be indexed on the composite primary key made up of the foreign keys of the dimension tables. A few special considerations were taken into account before it was decided not to create index on the BAM fact table:

1. Index occurs overhead on data insert (as well as delete & update)

2. Unlike the BAM primary import table which needs to support heavy instance query and real-time aggregation queries, fact table is only queried during the execution of the star-schema transformation stored proc.

3. Unlike dimension table, the entire fact table is truncated before next DTS run. The table is expected to stay relatively small (of course, table size depends on the incoming data volume, the scheduling of cubing DTS and archiving DTS). Indexing small tables may not be optimal because it can take SQL Server longer to traverse the index searching for data than to perform a simple table scan.

4. The star-schema is dynamically created. Customers may know best what additional index they may need on the fact table based on their business data characteristics and DTS scheduling etc.

Source: BAM, BizTalk and Beyond

BAM real-time aggregation vs. scheduled aggregation

BAM supports two types of aggregations: real-time aggregation (RTA) and scheduled aggregation. Some customers have asked what are the differences between them and when to choose what.

The biggest difference is the underling storage. The storage of RTA is a SQL table and the aggregation is updated and maintained by SQL trigger. The event importing and aggregation update are completed in the same transaction, therefore its data latency is negligible and almost real-time. The scheduled aggregation is saved in Olap cubes which need to be processed periodically by the cubing DTS package (btw the Olap cubes and the cubing DTS package are all dynamically created by the BAM Manager command line utility bm.exe).

The biggest advantages of RTA are its zero-latency and low maintenance. Once your new instance data is in the BAM database, the aggregation instantly reflects the new data. And there's no need to run any DTS package, the sql trigger takes care of everything for you automatically. Compared to scheduled aggregation though, RTA doesn't support as many dimensions and measures, and it can only keep relatively short period of data to keep satisfactory query performance. Scheduled aggregation, on the other hand, have little problem handling years of years of enterprise data, and can support much more dimensions and measures. Scheduled aggregation's advantanges don't come without a price -- it needs Analysis Service licence, a star-schema database and scheduling of the cubing DTS. And the new instance data won't make into the cubes until the next cubing DTS run.

After you understand the pros and cons of both aggregations, it's easy to understand their usage scenarios: use RTA for time-critical, small set of Key Performance Indicators (KPI) type of tracking and use scheduled aggregation for historcail/trend analysis which normally involves large volume of data and requires complex cubes.

Source: BAM, BizTalk and Beyond

You receive an error message when you try to apply a tracking profile to an x64-based computer in the Tracking Profile Editor

Problem

When you apply a tracking profile to a BizTalk server running on an x64-based computer you receive the error "Failed to apply the tracking profile" along with one or more of the following exception details:

· System.Runtime.InteropServices.COMException: The transaction has already been implicitly or explicitly committed or aborted

· Communication with the underlying transaction manager has failed. (System Transactions)

· Unhandled Exception: System.InvalidOperationException: The Promote method returned an invalid value for the distributed transaction.

Cause

This issue can occur if one or more of the following conditions are true:

· The Microsoft Distributed Transaction Coordinator (MSDTC) service cannot be initialized. This occurs if a lightweight remote procedure call (LRPC) association contains an incorrect Service Principal Name (SPN) in its authentication information structure because of the Winspool.drv file.

· The Tracking Profile Editor tries to use RPC to connect to the MSDTC transaction coordinator. When this occurs, the interface may perform additional security checks.

Resolution

When the Tracking Profile Editor tries to connect to the MSDTC on the local computer, the interface that is used may perform additional security checks. Depending on the operating system version and service packs that are installed on the computer, RPC may perform these additional security checks when the interface does not have a registered security callback.

There are two potential resolutions:

· Use the Group Policy Object Editor to disable the RestrictRemoteClients setting that the RPC configuration enforces.

· Use the Registry Editor to disable the RestrictRemoteClients setting that the RPC configuration enforces.

Source: Microsoft