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:-
To meet the data structure requirement, there are following options which we can exercise:
After considering all of above options, fourth option seems to be best considering the performance of live system. As data porting will be done at some scheduled later than actual operation, we may need to keep some reports critical for live reporting information system on live/operational database. However, still we shall be getting the benefits with most of the other informational reports, which will be generated from separate reporting database.
How to implement the fourth option:- We need some application/tool which
Now one option to have such application/tool is to develop a custom application in house. This option needs development efforts, specific expertise in that domain and moreover a good testing cycle. Other option is to utilize some readily available tools in market. There are many open source, free and commercial all kind of tools available in market. You can explore these and can choose any from these. Second option seems better considering that available tools would be developed by some experts in that field, would have pass some good testing cycles and would also be used by many other clients. So it can save your time and money both.
After exploration, we have found following tools good in this domain. You may explore these further for your requirements. These are:-
This was the basic overview of various consideration which we may need to take while designing a reporting system. Once we are done with defining the requirements, high level conceptual structure and with the selection of tool; then we can proceed further to define the actual low level structure and process for reporting.
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.
To meet the data structure requirement, there are following options which we can exercise:
- Store the data in the a structure optimum for Application daily operations, and then process this data at runtime to generate the reports. This approach could be slow for report generation, as then we shall be processing the bulk data at the time of report generation.
- Store the data in the data structure optimum for reporting. This approach could be slow for application operations as application operations might be quite simple, but storing this data in report format may increase the processing with increased level of indexing and de-normalized structure.
- Store the data considering Application Operations and Reporting both whenever any data is posted from Operations. Again this might be slow for application operations. As storing the data for reporting simultaneously will cost more CPU cycles.
- Store the data in application operation specific data structure at runtime. Later data can be ported to reporting specific data structure at some schedule in off working hours or when load is minimum on system. Data can be extracted from Operational Database, transformed to required data structure optimized for reporting requirements, and then can be loaded to target reporting database.
After considering all of above options, fourth option seems to be best considering the performance of live system. As data porting will be done at some scheduled later than actual operation, we may need to keep some reports critical for live reporting information system on live/operational database. However, still we shall be getting the benefits with most of the other informational reports, which will be generated from separate reporting database.
How to implement the fourth option:- We need some application/tool which
- Can perform the extraction, transformation and loading operations at some given schedule.
- Can provide some User Interface which can help to define the metadata of both database structures, and then see this structure of both database, to map the data from one structure to other by defining some transformation rules.
- Is simple enough to be usable for non-technical users also.
- Can facilitate the users to create new mappings and can see the reports on existing data immediately using the tool.
- Can facilitate to define the workflow for data transformation operations.
- And so on
Now one option to have such application/tool is to develop a custom application in house. This option needs development efforts, specific expertise in that domain and moreover a good testing cycle. Other option is to utilize some readily available tools in market. There are many open source, free and commercial all kind of tools available in market. You can explore these and can choose any from these. Second option seems better considering that available tools would be developed by some experts in that field, would have pass some good testing cycles and would also be used by many other clients. So it can save your time and money both.
After exploration, we have found following tools good in this domain. You may explore these further for your requirements. These are:-
- Pentaho (http://www.pentaho.com) - A comprehensive Business Intelligence and Reporting tool. Have both free and commercial enterprise level versions.
- Spagoworld (http://www.spagoworld.org) - An absolutely free Business Intelligence and Reporting solution, worth to explore.
This was the basic overview of various consideration which we may need to take while designing a reporting system. Once we are done with defining the requirements, high level conceptual structure and with the selection of tool; then we can proceed further to define the actual low level structure and process for reporting.
0 comments:
Post a Comment