Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

DWH data structure for sales deals

2 posters

Go down

DWH data structure for sales deals Empty DWH data structure for sales deals

Post  emonchen Fri Aug 06, 2010 6:55 am

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...



Posts : 12
Join date : 2010-02-11
Age : 44
Location : Delft, The Netherlands

Back to top Go down

DWH data structure for sales deals Empty Re: DWH data structure for sales deals

Post  BoxesAndLines Tue Aug 10, 2010 8:46 am

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.

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum