Data allocation

Financial background

In absolute terms the profit margin can be illustrated with the following expression:
Profit margin = sales amount - costs - sales deductions (discounts) - rebates
Net profit margin = profit margin - taxes
Sales amount is the gross total sales figure listed on an invoice and paid by a customer
Sales deductions is a discount given during a sales transaction (listed on an invoice)
Costs include variable and fixed costs (provided on a monthly and yearly basis)
Rebates and customer bonus are usually given to a customer and calculated on a monthly, quarterly and yearly basis.

Data allocation concept

Data allocation (technique also referred to as filling gaps) is useful when dealing with data which has a different level of detail (granularity) and there are gaps for some measures.
In data warehousing systems, the allocation technique is in many cases compulsory and used widely in order to get a consistent and complete set of data.

The concept of data allocation is closely related to the granularity of the data. In data warehousing, data granularity refers to the level of detail in a given fact table. The tables below illustrate various levels of granularity.

Coarse-grained data (low granularity) Date value 2007 1000 2008 2000 2009 1500 ... Fine-grained (high granularity) Date value 20080101 8 20080102 15 20080103 12 20080107 14 20080109 11 ...

costs, operational forecasts, sales plans, customer rebates and bonuses, etc.

There are two approaches for data allocation

Dynamic allocation (weighted or proportional allocation) - values are allocated using calculated subtotals of another value. The weighted type of allocation is often used in real-life data warehouses environments. Sample uses of dynamic allocation: designate portions of a budget pool, allocate manufacturing costs to products, etc.

Fixed allocation - which means that there is a constant value assigned to all records included in the allocation group. Be aware that this approach might be risky and confusing as those values cannot be summarized. Sample uses of fixed allocation are: storing values that do not change often (for example credit card limit) or cannot be allocated dynamically.

It is also important to keep in mind that in some business cases allocation is unsuitable. Prior to using allocation it is necessary to analyze the data thoroughly and make sure it fits into a business logic.

Scenario details

The company's data warehouse stores the sales data down to the invoice line level of detail and the costs which are calculated on a monthly (variable costs) and quarterly basis (fixed costs).

- The variable costs total value is assigned per year, month and product group.
- The fixed costs figure is a grand total assigned per year and month.

The aim is to compare revenue to fixed and variable costs in all time dimension levels available.

The source data has the following table structure:
Date_id;Invc_head;invc_line;prod_id;prod_grp;cust_id;quantity;price;sales_amount

Solution outline

The data allocation ETL process will be realized in a few steps:

Load technical table invoices - the table contains all data related to the invoices, including gross sales and net sales
Load updated monthly and yearly costs into a separate costs table
Create another technical table which assigned importance levels and the following figures which are populated using a fixed allocation mechanism mentioned above for groups of data records: variable costs, fixed costs, sales invoice total
Load the DW invoices table - with costs figures allocated accordingly and calculated profit margin

For further analysis please refer to the allocation in Cognos example. Cognos Business Intelligence applications provide an automated mechanism to implement the data allocation technique.

Comments

2009-01-09 10:03:46 by Vincent:
hi there,
what is the point for doing a constant allocation? Could someone provide me with a sample business use of this concept.
Thanks.Vincent

2009-01-10 12:35:11 by BI Specialist:
Hi Vincent,
Basically constant allocation makes sense when the measure doesn't hold any relationship to the data source.

In real world, a good example might be if we for instance wanted to analyze number of employees (as rows in a cube) and product details (as columns in a cube). It wouldn't make any sense to make a dynamic allocation in this case and constantly allocated employees count accross all products will be ok.
Hope this helps

Add a comment

Back to the DW ETL tutorial home