Now it is becoming increasingly clear that users need liveliness and interactivity on static data from OLTP. This is possible through multi-dimensional modeling available in free-form pivot table or cube structures. As there are not many cube applications and not many established routines to populate cubes incrementally periodically automatically, report-directly-in-excel is becoming vogue. Though most summarized multi-dimensional modeling cab be done easily with old MS Excel 2003, with excellent enhancement in MS Excel 2007, scalability and liveliness has increased manifold in pivoting (multi-dimensional modeling using pivot table).
All that one need is accurate, reliable and relevant figures side-by-side in CSV format for all dimensions / measures to follow report-directly-in-excel-with-data-dict-advantages strategy. For example, here is a baan report which gives all important dimensions/measures related to customer financial behavior.
Here is the list of dimensions:
- Customer / Supplier
- Half Year
- Kind of transaction (Opening, Sales , Receipts etc for customer & closing as grand total)
- Document Type (sales invoice , currency difference ,Normal receipts , assignments , payment difference etc for customer )
The measures are:
- Amount in HC
- Amount in Invoice currency
Grand total for a customer would be the closing balance for it which can be verified with many baan standard reports like tfacr/p2434m000, tfacr/p2433m000 for customer etc.
From this single pivot table users can get following sub-reports:
- Customer transaction behavior for a given period (sales , receipts , opening etc )
- Change in customer balances period wise (Quarter, HY , year etc )
- Currency gain loss customer wise , period wise , country wise , currency wise
- Sales trends for a customer , for country etc
- Average currency rate for a particular period for a particular period for particular transaction type.
Such a report could also be useful for incremental update to cube structures using baan job management (if at all data warehousing is deployed for multi-dimensional applications).
Sample report can be viewed from sample cust analysis with functional doc. One can send PM / email to avail of such ready reports in their systems for a consideration.
There is supplier analysis also similar to customer and it is readily available .
Next we'll blog on ERPJewels scheduler advantages for unequal and numeric period for scheduling emails and incremental update of cubes .