Thanks, Craig, for the value this site (and the videos) bring.  Very helpful!

So, generally, facts are like 'verbs' (some action/event/activity occurs… like a sale, a purchase, service call, complaint, marketing communication, etc.) and dimensions are like 'nouns' (customer, vendor, employee, date, product, account, type, status, etc.). 

These are, of course, necessary for typical transactional analyses such as… 

What were our sales by period, store, customer attributes?
Show me a distribution of service calls by call type and region.
What were the eMail campaigns we executed last year and how many eMails were sent in each of the campaigns? 

These analyses are all based on the grain of the activity.  However, what if we want to look not only at these event-centric analyses, but also look at things from a customer viewpoint (so our grain is the customer instead of the activity) such as the “customer-centric” analysis shown in the table below?  Here, we're looking at all of our Customers (212,000 of them) by how many eMail campains they were part of (even those customers we have on file that have never been in a campaign).

This makes me wonder if the Customer (typically a Dimension) also needs to be treated as a 'fact' (for lack of better way of explaining this)  in order to do this type of analysis.  Is there a best practice for modelling this kind of capability into a cube that consists mostly of "activity" fact tables?  Any help would be greatly appreciated!

thx, Jim

Thanks for the reply, Craig.

The analysis work we do is marketing-centric.  It is nice (and necessary) to be able to slice up typical 'transactional' facts like campaign data (overall campaigns and leads counts by year, overall response to those campaigns, etc.).  Modeling these is relatively straight forward... campaign activity in a fact table and some dimensions (channel, date, service provider, response types, customer, etc.) related to that activity.

The true business value of our analyses is in providing an understanding of the prospect/customer's behavior, not transactions overall.

In Analysis Services 2000, we've typically built a customer cube (with a single 'fact' table, one record per consumer/customer) and pre-aggregated many "transactional" measures to the customer level.  In the "Number of Campaigns" example in my original post, as you have suggested, we would create a categorical "Number of Campaigns" dimension (so yes, that's meant to be a dimension on the rows... sorry if that wasn't very clear).  Appropriate ranges or 'buckets' would be pre-determined during the data discovery/modeling phase of the project, and customized to our client's needs (my example happens to use '0', '1', '2', '3 or more').  While the most frequently used measure in this type of cube is "Customer Count", we would also include other additive facts such as Sales Amounts, Product Usage (perhaps averages), etc., but again all at the grain of the customer.

This approach does work; it gave our clients what they needed and they are pleased.   That said, this approach means a lot of pre-aggregation work must take place in the source data (before constructing the cube).  I suspect that doing things this way might ve viewed as archaic and far from a 'best practice'.  Now that we have moved to Analysis Services 2008 (and have accumulated a bit more dimensional modeling knowledge), we are hoping that new features and techniques will help us do this a better way.

My sample report is just one example of many of these types of customer-level dimensions we'd create.  If the many-to-many approach is the right way to facilitate customer-centric analysis for our end-users (without sacrificing the ability to do traditional transactional analysis as well), then I think it is worth our time to learn and adopt the approach.

Since my original post, I had started reading Marco's M2M article.  Looks very promising.  I hope it can be applied to our situation.

Regards, Jim.