I've been playing around a bit with Business Activity Monitoring in BizTalk Server 2006 R2 and ran into a problem deploying a BAM definition XML file I had just created in Excel.
When I tried to deploy the definition, I'd get an error similar to this one:
Deploying Activity... Done.
Deploying View... ERROR: The BAM deployment failed.
Encountered error while executing command on SQL Server "NEWTON".
Column names in each table must be unique. Column name 'abc_id' in table 'bam_AbcEvents_Dim_abc' is specified more than once. I checked my BAM definition several files (even checking IDs on the raw XML) and couldn't find any duplicates of this field. However, after struggling for about 15 minutes with it, I nailed it down: The problem was how I named a dimension!
I had an item defined in my activity named 'abc_id'. When I created my view, I created a custom dimension that combined 'abc_id' with another item and named it 'abc'. That seemed like a proper way to name it.
Problem is, when you try to deploy this, the activity tables will be created correctly, but bomb when creating the dimension table for 'abc', because bm.exe will try to create an artificial identity column for the dimension called 'abc_id'. That's how you end up with an apparent duplicate column error!~
The solution: rename your dimension in your view definition.
