Originally Posted 5/24/2010
I'm sure that you often had to do with sales data marts. I'm sure you designed, implemented and reported against it many times. Actually, sales and margins are often the first business areas to be covered by a BI implementation. Sometimes accounting leads the pack, but usually sales are the first subject on the list.
Implementing sales is somewhat easier than other datamarts because the granularity is almost invariably defined by the sales events. That is, each line in your fact table will likely be an order/invoice line. Defining what dimensions to add is rather simple. We'll have customers, products or activities, sales force and many dates; plus a bunch of minor or degenerated dimensions.
Conversely, defining dimension structures is not trivial at all as their layout greatly vary with the specific business. A shop order company works differently than a consumer packaged goods company, which, in turn, is different from a consulting shop or a resort business. There's no point in describing all the possible attributes for every business, but there are few criteria which should always be met because your business users will likely need them.
The company will have various level of commercial responsibilities: there are going to be product managers who take care of a product line and area managers who deal with a group of customers or channel managers who deal with a sales channel etc. Sales must be sliceable according to this group of responsibilities to adhere to the business vision the company has adopted. Each level of responsibility must be provided with its own data.
This is not as easy as it may seem. First, not all the aggregation levels required are necessarily defined into source systems. While each customer is assigned to a salesman, maybe a dummy one, areas may not be defined as well. Sales force may be an unbalanced hierarchy, thus adding a further complexity level. This is when external, manual, data must be added to the datawarehouse. In some cases, some very complex responsibility schemes are implemented. A single position might control sales of a product group for an area, but all the products for some customers. That is, data might have to be sliced through different dimensions to comply to sales responsibility. Often BI clients can accommodate this, but defining an appropriate, mixed, dimension can also be of help, especially in defining specific KPIs.
I'm not done with the highest rows of the P&L, there's another topic about sales structure: how to organize the sales events.
But I save this for the next post.