DWH data structure for sales deals

[emonchen]

I am faced with the challenge to design a data architecture and dimensional model for a sales deal process. I won't go into the full detail of the case, but in general it concerns opportunities (the facts) with several items linked to an opportunity and the status of the opportunities. Until so far I was thinking of the following table structure:

- SalesDealsDimension: An SCD2 based table that holds the sales deals dimension data.
- SalesDealsFactsCompleted: This table holds all the won and lost sales deals on a transactional basis. After all the won or lost sales deals are finished and don't change anymore, therefore they don't have to be in a snapshot table
- SalesDealsFactsCurrent: This holds all the ongoing sales deals in a snapshot table. This allows reporting on how open sales deals have been developing over time.

The differences between the two fact tables should be minor, because the SalesDealsFactsCompleted doesn't hold a field for SnapShotDate, and the -Current doesn't have a field for the date of the deal being won or lost because it only holds the ongoing sales deals.

The second thing is that a sales deal consists of a endless number of items. Of course the reporting needs to be done on item level, and since there is a 1:N relationship between the sales deal and the item, I would have to create a table that only has two fields, the sales deal key and the item key.

The question is, am I making it very complicated for myself to work this way? The end result is basically the logical things that sales managers want to know about their past sales deals and the current pipeline...

Thanks!

[BoxesAndLines]

Pipeline reporting is best managed through accumulating snapshots. It looks like you'll need at least 2 facts, one for deal metrics and one for item metrics. I would not break it up by status. Each status would simply be a stage in the pipeline.