DISCLAIMER. This is old home-grown basis for Data Warehouse design, burned in my head in past. It may influence you with some ideas, but i believe, there is a better one somewhere.
The article is illustrated by data marts schema for telecommunications.
Table of Contents
- Identifying expense resource and reason
- Cost allocation and profitability processes
- Dimension and datamart design
- Some dimensions described
Resource and reason conception
When you are thinking about company’s money spendings, you should identify (mentally disjoin) two things – resources and reasons. Names of resources answer to the question “What bought for money?” and reasons answer to the question “What for it bought?”.
Resources include materials, employees, equipment and externally bought services.
Reasons can be projects, promotions, buying for leasing to customer, buying for selling to customer.
Reasons with future value
Short time expenses with (relatively) long time results are subjects for future value calculation.
These are expenses that tend to be questioned “What is the return of the investment?” by business. From formal point of view, these expenses are cases, when paid period of resource usage or moment of buying is not match with (longer) period of results consumption (when revenue is generated).
You should decide “Future value calculation is reasonable or not?” for every identified expense reason.
Resources with direct profitability
There are things directly delivering customer value, requiring, at the same time, well identified expense. These things basically have what we pay for unit and what we get from the same unit. Business believes that profitability for every such a thing can be accounted independently from other resources.
For example, these are equipment leased to customer, TV channels, films, services over the top. Yes, these resources may be associated with future value, such as buying equipment for leasing to customer, but may be not associated with future value, like TV channel rightholder fee.
We see that all these identified things are resources by nature, but with implied expense reason and revenue – resource is bought for certain, directly profitable usage scenario.
Fact tables of finance datamart
To show expenses by date of spending money and by date of cost allocation, we need two fact tables – one for facts of expense (‘Expenses’) and one for costs allocated (‘Cost allocation’).
In first table (‘Expenses’) we put actual expenses with actual value. Theoretically we may see three ‘Date’ dimension roles – day of expense, begin of paid period of resource, end of paid period. But minimally, i hope, binding to paid month (of resource) is enough.
In second table (‘Cost allocation’) we allocate future or actual value of the same expenses at every day (or month, depending on chosen granularity) of result consumption period.
To keep records in first and second tables joinable, both tables need degrade dimension (DD), that identify expense transaction – it should be surrogate key that is the same in ‘Expenses’ and ‘Cost allocation’.
To calculate profitability we need cost and revenue together. ‘Profitability’ table should be in the same dimensions as costs in ‘Cost allocation’, and in the same dimensions as revenues in LOB (Line Of Business) datamarts (look part III). LOB datamarts are sources of revenue and ‘Cost allocation’ is source of costs for ‘Profitability’.
I suggest identifying at least following processes related to finance datamart:
- Cost allocation process. Conceived1 as internal for consolidated finance datamart (look part III), cost allocation process should first fill ‘Expenses’ table and then, using cost model, rates and periods, fill ‘Cost allocation’ table.
- Profitability consolidation process, which takes revenue data from LOB datamarts (look part III) or from staging tables (should be the same as for LOB datamarts), takes cost data from ‘Cost allocation’ table and puts it together in ‘Profitability’ table.
1 Difficult thing with this design may be to allocate costs based on customer activity like video on demand purchases. Obviously, purchase of video has cost, revenue and profit for us generated at the same day – day of purchase, nevertheless expense (or revenue) actual date is normally next month. To be correct with profitability consolidation, we should allocate cost, assuming that paid period of resource is one day – day of purchase, as revenue registered in LOB datamart (look part III).
Line of business datamarts and consolidated datamarts conception
To split DWH delivery some way, i suggest use line of business (LOB) datamarts and consolidated datamarts.
LOB datamarts example: TV and VoD, Voice, Fiber lease, VPN, Internet. This line of business split can be considered as technology split to simplify data feasibility or break teams down by LOB domains to work independently.
I suggest use of consolidated financial datamart, where cost allocation and profitability consolidation should occur.
Also, i think that marketing and quality information consolidation is reasonable to focus elaboration on marketing and QA needs. Consolidation somewhat dependent on data of LOB sources or datamarts.
That way, we build two types of datamarts – LOB and consolidated. Also, I suggest LOB dimension for consolidated datamarts to split some measures by line of business.
Datamarts processing order
If we are going to profitability analysis for each customer, we need to allocate shared costs (may be regarding to geography, customer consumed services, customer profile and other factors) for each customer. In this case, cost allocation could not be performed before we acknowledge certain customers live, which means, that we need churn detection process to be performed before cost allocation.
That way, the correct order of processing of datamarts should be:
- LOB datamarts
- Marketing datamart
- Finance datamart
And, i presume, order of development should be the same.
Dimensions for resources and reasons
Generally speaking, in cost allocation, dimension can represent resource or expense reason (look part I) or combination of resource and expense reason.
So at least, we need one dimension for common resources, which will not be represented in separate dimensions. I suggest this dimension name ‘Resource’.
Resources, with direct profitability (look part I), should not be included in ‘Resource’ dimension, because they are shared between finance datamart and conforming LOB datamart, where it has hard meaning for users and should be developed with LOB team.
Also, like for resources, we need at least one dimension for expense reasons. It we have more than one such dimension, for example ‘Projects’ and ‘Promotions’ as separate dimensions – we must avoid simultaneous use of meaningful keys in any two reason dimensions (rule 1), because this prevents answering to question “What for it bought?”.
Similarly to rule 1 above, we must avoid simultaneous use of meaningful key for direct profitability resource dimension and any other resource dimension (rule 2), for example ‘Leased equipment’ and ‘Resource’ or ‘Leased equipment’ and ‘Film’ – this prevents answering to question “What bought for money?”.
And finally, we must avoid simultaneous use of meaningful key for direct profitability resource dimension and any reason dimension (rule 3), for example ‘Leased equipment’ and ‘Project’ – this prevents answering to question “What for it bought?” because for direct profitability resource expense reason is implied (for leased equipment it is buying for lease to customers).
Sale, provisioning and wiring dimension
Especially for fixed-line provider, ‘Sale, provisioning and wiring’ dimension is suggested to be created and shared between finance and marketing datamart.
Dimension itself should keep sets of standard works that are performed for new customer, including sale, provisioning equipment and wiring.
In ‘Expenses’ table in finance datamart it should lead to money paid (may be allocated) for those three kinds of work for customer.
In ‘Cost allocation’ table the future value for period of average (or reasonable) lifetime of customer should be allocated.
In ‘Profitability’ table, as designed, cost the same as in ‘Cost allocation’ table is stored and, if provisioning or wiring is paid by customer, revenue equal to customer payment should be stored.
‘Sale, provisioning and wiring’ is expense reason dimension – in finance datamart its meaningful key should be used with meaningful key for ‘Resource’ dimension, but cannot be used with meaningful key to other expense reason dimension or direct profitability resource dimension.
This dimension let us introduce initial costs for each customer to see correct customer profitability without promotion - it would be customer profitability if we do nothing in marketing.
Promotion dimension is end-to-end for DWH – promotions are engaging existing customers to switch tariffs (we put this information in LOB datamart) or support some up-sale, or engaging new customers to subscribe (we put this information in marketing datamart).
‘Promotions’ is expense reason dimension. We should allocate costs for promotions, using meaningful keys for ‘Promotion’ dimension and ‘Resource’ dimension, with one of possible solutions around customers:
- Allocate promotion costs for existing customers (of certain profile).
- Allocate promotion costs for existing customers made tariff switch or some promoted activity.
- Allocate promotion costs for new customers, gained with aid of promotion.
‘Promotion’ dimension with ‘Sale, provisioning and wiring’ dimension together let us see full customer profitability and full cost of new customer.
Geography and building
Should be designed to answer queries where and in what building type takes place cost, revenue and profit?
For fixed-line provider it is quite simply to know revenue by geography, if we have customer addresses accurate, but can be hard to allocate costs by geography.
For profitability calculation of equipment it costs should be allocated to customers, which actually use this equipment – cost of one unit allocated per unit leased to customer. This mean, that number of bought units should be kept and taken from staging (or datamart), and number of units leased should be kept in bridge structure for ‘Leased equipment’ dimension.
Date – used in following roles:
- In expenses this represents at least period of resource usage.
- In cost allocation this represents period of results consumption (with future value calculation) or period of resource usage (with actual expense value).
- In fact tables, keeping revenue in LOB datamarts, this represents date of taking revenue into account (billed period).
- In profitability table this represents the same as in cost allocation table and the same as in revenue source fact tables.
Also, i suggest defining of following end-to-end DWH dimensions:
- Legal body – what juridical entity of company spends money or gets money?
- Cost allocation model – what set of parameters and algorithms is used for cost allocation?
We consider, that cost allocation model is subject to change even for past.
- Agent or principal – who are taking commissions from our revenue, or are taking revenue, paying commissions to us?
General design steps
- Define dimensions for resources with direct profitability (LOB specifics)
- Define expense reason dimensions (projects, promotions and so on)
- Commit universal resource dimension name (‘Resource’)
- Describe relationships of 1-2-3 dimensions, LOB, ‘Agent or principal’ dimensions.
- Design full Kimball bus matrix of DWH
Describing relationships of cost dimensions, LOB and ‘Agent or principal’
Every acceptable combination of resource and expense reason dimensions should have certain meaning – it is important thing to describe.
Considering, that restricted number of combinations for resource and reason dimensions is possible due to rules (look part III – “Dimensions for resources and reasons”), it seems good idea to develop matrix with possible combinations or resource / reason dimensions for cost and revenue, including ‘Agent or principal’ and ‘LOB’ dimensions. Such a matrix could be subject to discuss with financial representatives.
Filling following matrix leave customer cost allocation (for ‘Customer’ and ‘Customer profile’ dimensions) and date allocation (for ‘Date’ dimension) out of picture.
Remember, that payments we take for third parties is not included in our revenue and should not be included in our costs – it should be kept outside of ‘Expenses’ and ‘Cost allocation’ tables and outside of revenue facts, but commissions they pay us back is our revenue, so it would be included. Vice-versa, commissions we pay to our agents is our cost.