When we were designing Vivid Reports CPM, we decided to use a separate data warehouse layer. We are often asked why, and what we feel the advantages are. I hope this article will shed some light on why we feel a data warehouse layer provides a superior reporting experience.
There are two fundamental data access methods when it comes to reporting. You can access the data directly at its source for real-time information, or you can access a data warehouse that is updated as part of a scheduled process.
If real-time information is not a requirement, then a data warehouse offers many distinct advantages. Below are some of the major benefits of utilizing a data warehouse layer for corporate performance management and reporting solutions.
1. You can modify or enhance the data without effecting the core system
For example, you could rename fields to make them more meaningful, or attach comments to information that may not be supported in the core system.
2. Improved processing speed
As well as more independence, reduced record locking, and the prevention of other data access issues. Because a data warehouse is a separate database, you have the ability to:
- Run the database on a dedicated server
- Index or optimize data in a way that is designed purely for reporting and analysis
- Perform calculations and store the results of complex queries and relationships
- Separate data write and read operations
3. Security and permissions can be different from the core system
You can enhance the security of the core system by having fewer logins. You can also enhance security through data separation. For example, the source database might have payroll information whereas the data warehouse does not.
4. Combine disparate systems into a single cohesive view of the information
For example, you would be able to take the general ledgers from two distinct ERP systems and combine them into a single database to produce consolidated financials.
5. Change the core system without having to upgrade the reporting system
By having a separate data layer, you can often change the core system without having to upgrade the reporting system. For example, if a field is renamed during an upgrade to the core system this field could potentially be remapped in the data layer of the data warehouse without having to rewrite each report.
6. Control when data is synchronized with the core systems
Therefore, you can schedule this to occur “after hours” or during periods of reduced demand on the core system. Additionally, you can take a “snapshot” of the data, allowing you to compare updates to help identify what has changed.
7. Reduce license fees
You can usually reduce license fees when your users only need to view information that is captured and stored in a data warehouse instead of the core system. That, comparatively, might have much more expensive licensing requirements.
The ideal reporting solution uses a hybrid of both a data warehouse and real-time connection to the source. This can be accomplished by allowing the user to update the data warehouse in addition to a scheduled update. This feature is useful when the user knows a significant change has occurred in the core system and wants this reflected in the data warehouse. These on-demand updates can perform very efficiently if certain techniques are built in. For example, an “incremental update,” which allows the software to quickly find what has changed and therefore has very little work to do.
Vivid Reports CPM will generate the data warehouse for you and update and maintain its structure. As a result, the burden of having two separate databases will be eliminated, but all the advantages of it will be maintained.
Share:
