Should the value of the key figures in a report have a preferred orientation? Would mostly positive or negative be more appropriate?
Can we find a solution that is best performing and easier to be understood by most people? Yes. Lets look at it strictly from the enterprises point of view and based upon the DataSet involved.
Inbound
Stock being purchased has two key figures, the quantity and amount. These are the generic key figures for ‘How many individual units of the product will the enterprise receive? (0QUANTITY)’ and ‘What was the financial burden to the enterprise? (0AMOUNT)’.
- The quantity will always be zero or positive.
- The amount will always be zero or negative.
The timing of when the product is received into the enterprise and when the money to pay for it leaves the enterprise are not related.
The finance business process that loosely relates this timing uses invoices and payment terms via calendar dates to create the relationship. This is only an agreement between the seller and the buyer, which still has no strict relationship to when the money truly flows from the buyer to the seller.
The loose relationship between the quantity and amount over time to complete the full life cycle of the transaction is the core essence behind the Accounts Payable (FI-AP) DataSet with the primary focus of reporting on the amount and the calendar date differences.
Outbound
Stock being sold has two key figures, the quantity and amount. These are the generic key figures for ‘How many individual units of the product will the enterprise ship out? (0QUANTITY)’ and ‘What is the financial gain to the enterprise? (0AMOUNT)’.
- The quantity will always be zero or negative.
- The amount will always be zero or positive.
Why does the DataSet allow for a zero value in the quantity or amount key figures? This is for specific types of transactions where a different part of the enterprises business process was involved in the full life cycle of the purchase and/or sale transaction.
For Example: Product returns will have a positive quantity and a zero amount while financial credit notes will have a zero quantity and a negative amount.
You could view these two examples as being the same transaction so why are they not in the same record? The data is generated at different points in time by different business processes; hence they are stored in different DataSets. The stock return business process will trigger a subsequent activity in the finance business process but both will not be done at the exact same point in time.
The overall efficiency and reporting accuracy of an enterprise depends upon how many of these business processes can be automated based upon structured business rules.
Technical Hint
Use the right data type for the right job. Technically, the quantity and amount key figures should always be able to handle negative, zero and positive values. This will allow a DataSet to use both enterprise and operational data modeling, the key figure sign alignment, as it flows through the data warehouse from the data staging layer through to the consumption layer.
“avoid using integer key figures that are
limited to only zero and positive values”
This will lower the Total Cost of Ownership (TCO) for the data model, especially when it comes to query complexity, maintenance and growth.
Data Modeling Hint
When finalising the design, be sure to review the sign alignment of the key figures through out the whole solution. Has the balance between source system reconciliation, transformation layer reconciliation, OLAP engine performance and query complexity been balanced as best as possible?
When taking a ‘performance optimisation only’ point of view across the entire data model then the enterprise alignment of the sign of the key figures is recommended. It enables you to forget about the need to check the reported sign alignment and just allow every component of the data model to aggregate (add up/summarise) the key figures.
The one point of action is then limited to the focus of a report. For Example: A cost centre report would be a sea of negative numbers. In this case it is ok to use the OLAP engine to reverse the sign of all reported values as cost centre managers are already in the habit of viewing expenses as positive numbers.
This approach would not be suitable for a balance sheet report where the data within the report is a mixture of positive and negative numbers, money going in and out of the enterprise.
Conclusion
At the end of the day there is no right or wrong approach to how the sign of the key figures is modeled within a data warehouse for reporting. It is important to remember these perspectives across the entire data model:
- Aggregation;
- Performance;
- User reporting.
What is my strategy for data modeling the sign of key figures? Why?