Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)

Hi all,
My first post on this forum.

I am in the process of designing a data mart for sales and marketing to analyse sales activity, opportunities, quotes and sales orders.

• Each sales activity is a record of a state transition e.g. Opportunity Identified->Opportunity Qualified->…-> Order Installed)
  
• Each sales activity is associated with 1 opportunity.
  
• An opportunity may be associated with 0 or more quotes.
  
• A quote may be associated with 0 or more orders.
  
• Not all sales orders are associated with quotes.

Currently, we have the following basic requirements:

1. Be able to analyse units and amounts of opportunities, quotes and orders separately
   
2. View all opportunities, quotes and orders for a selected sales activity state (e.g. Order Installed)

In order to solve this problem I have created granular fact tables for sales activity, opportunity, quote and order. Each fact table contains business key (nvarchar) as well as unit and amount fields. The fact tables are linked to dimensions such as time, product, employee etc. I believe that my current design will give me the ability to fulfil the 1st requirement above but I am not sure if I need any new dimensions or fact tables to fulfil the 2nd requirement.

I welcome your design suggestions. I am likely to use SSAS to develop the cube.

So long as the later facts reference its predicessor, (in other words, the sales fact contains the quote ID as a degenerate dimension), you should be able to handle the second part. However, it is common to create an aggregate fact table, combining the facts you have, to simplify queries and improve perfomance. If a requirement #2 query is often done, than such an aggregate is recommended. You could also implement a view rather than a physical table if the resultant query times are acceptable.

ngalemmo wrote:So long as the later facts reference its predicessor, (in other words, the sales fact contains the quote ID as a degenerate dimension), you should be able to handle the second part. However, it is common to create an aggregate fact table, combining the facts you have, to simplify queries and improve perfomance. If a requirement #2 query is often done, than such an aggregate is recommended. You could also implement a view rather than a physical table if the resultant query times are acceptable.

Thanks for the reply ngalemmo. I guess by 'quote ID' you are referring to the business key of quote. The fact tables do contain references to predicessors.

Sorry, I can not visualise the suggested aggregate fact table or view. Will it contain the business keys and all the details of actity, opportunity, quote and orders? When I first started modelling, I had considered implementing a single fact table containing all the details but later I realised that I would end up with a massive fact table having in excess of 70 columns, which would be a nighmare for our ETL developer to populate. Also, we have some (almost 40%) orders with no references so quotes. I guess if I go ahead with the aggregated fact table, will I not have lots of null values where orders do not have references to quotes and opportunities?

If you plan to use SSAS, you might be better off storing the base facts in the cube and let cube do all the aggregates for you, so that you can leverage aggregate navigation feature built in SSAS.