One of our business requirements is to enable cause-effect analysis on two or more facts – such as sales and advertising expense. While it appears that advertising expense is a dimension for sales, we plan to capture these as separate facts for their own analysis. Further, advertising expense itself is a calculated measure and can vary over time, region, product, etc. Any suggestions on how this can be modeled?


Joyce Bischoff’s Answer: Before building your dimensional model, you need to have a thorough understanding of the data and its relationship(s). The best approach is to build a relational model in third normal form to provide a full analysis and understanding of the data. You do not need to model the whole corporation, only the basic data that you wish to include in your warehouse. The relational model can then be easily transformed into a dimensional model. Your question shows that you do not yet have a full understanding of the underlying relationships.Chuck Kelley’s Answer: Design without full understanding is a design of a fool. I must be a fool for doing this, since I don’t know anymore than what is provided above. This, I would almost guarantee, will be wrong, but hopefully a good starting point. It sounds as if you want to have two sets of facts sharing some dimensions – one for advertising and one for sales. Therefore, you will have the dimensions of time, region, product, store, salesperson, advertising campaign and facts for the sales. You will have the dimensions of time, region, store, product, advertising campaign, expense type and facts for advertising expenses. To see how the expenses of an advertising campaign vs. sales generated by the campaign (you may have to assume that all sales were due to the advertising campaign, unless the advertising campaign had some way to show otherwise – like coupons, etc.), you would have to tie the two facts together using common dimensions. It should be noted that many end-user tools might not work well in this environment.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access