Reporting is an important part of most business enterprise solutions. Even for end business users, value of software is directly proportional to quality of reporting system it provides. In this article, we shall discuss about very high level design considerations for reporting system. We shall not go in depth of technology, but will talk about the thought process and various considerations which helps in design and in choosing the right tool.
When we talk about reporting, it means we are providing the information stored by Enterprise Application in a form which can provide valuable business and operational information to various stakeholders. Data stored by Application might be in different format, like most of the operations are related to CRUD activities and processing of various business document. However, reporting requirements can be very different. These can be very simple like to show all order processed with summary of their values, and also can be bit complex where one want to see the summary of all operations held, trend of specific business operations, future forecasting of business depending upon current sale trend and market data and so on. That means that we need to maintain data considering reporting requirements of solution, otherwise, it will be a good level of processing at the time of generating the reports if database structure is not compatible.
Next to discuss is, how database structure required for reporting can be different than application general data structure. Here are some of the possible differences:-
When we talk about reporting, it means we are providing the information stored by Enterprise Application in a form which can provide valuable business and operational information to various stakeholders. Data stored by Application might be in different format, like most of the operations are related to CRUD activities and processing of various business document. However, reporting requirements can be very different. These can be very simple like to show all order processed with summary of their values, and also can be bit complex where one want to see the summary of all operations held, trend of specific business operations, future forecasting of business depending upon current sale trend and market data and so on. That means that we need to maintain data considering reporting requirements of solution, otherwise, it will be a good level of processing at the time of generating the reports if database structure is not compatible.
Next to discuss is, how database structure required for reporting can be different than application general data structure. Here are some of the possible differences:-
- Operation database used to be highly normalized to ensure the data integrity and consistency. However, to generate the reports which may need bulky data processing, we may need to maintain data in de-normalized form so that related data can be found in same table/s without applying multiple joins.
- Reporting database may need many indexes to speed up the searching operations while generating the reports. However same level of indexes can slow down the create/update/delete operations in Operational Database.
- We may need to do various calculations on Operational Data to generate the reports. If reporting data is different and is ported from Operational Data, these calculations can be done during porting and can be stored in database for fast access while generating the reports.
