BAM: The Case of the Missing Pivot Table Data

I had a recent problem getting a pivot table to display its data on the BAM portal. I went thru a series of steps to get it working, so in case you have a similar problem here are some things to check to get the data displaying.

To frame things: I had a BAM activity with a series of data points to be tracked. I needed to extract a very simple count/sum cube from the data.

I set up the activity and went thru the view setup in Excel. I had a dimension which tracks a calling system and a then I wanted to get a count/sum of another field which was setup as an integer.

I couldn’t get it working; the view in the portal wouldn’t show any data at all. The calling system wouldn’t appear and neither would the count. I tried to set up the view as both a count (like a rowcount) and a sum and neither one showed the data.

I was pretty sure that I set the activity up correctly so I began to look at different things that could be wrong.

One of the first was to check if the SQL job "TrackedMessages_Copy_BizTalkMsgBoxDb" was running, it was.

Next, I used the SQL Management Studio to connect to Analysis Services on the server that BAM was configured on. I located the cube I was interested in, right clicked on it and select "Process...", data should have appeared. It didn’t. Something else was wrong.
I checked the associated SSIS package which is called BAM_AN_ which is responsible for populating the cube. I ran the package and was finally able to see the data in the BAM Portal.

Documentation for this can be seen here: http://msdn.microsoft.com/en-us/library/aa560072.aspx

So, to get things working right, I scheduled the SSIS package to the correct time interval and things were on track.