When we were designing Vivid Reports CPM we decided to go the route of 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 either access the data directly at the source for “real-time” information (note only as “real time” as the data is entered). Or you can access a Data Warehouse that is updated as part of a scheduled process.
If real-time information is not a requirement (which is often the case with financial reporting) 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 affecting 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, independence, and reducing or preventing record locking or 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. This can enhance the security of the core system by having fewer logins. You can also potentially enhance security through data separation. For example the source database might have payroll information whereas the data warehouse does not.
4) Ability to combine disparate systems into a single cohesive view of information. An example would be to take the General Ledgers from two distinct ERP systems and combine them in a single database to produce consolidated financials.
5) 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) You can 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. A second advantage is that you can take a “snap shot” of the data which allows you to compare updates to help identify what has changed.
7) You can usually reduce license fees when some of your users only need to view information that is captured and stored in a data warehouse versus the core system that 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 perform an “on demand” data synchronization or update of the data warehouse in addition to a scheduled update. This feature is useful when the user knows a significant or important 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. One example is something we call an “incremental update”, which means the software can quickly find what has changed and therefore has very little work to do.
Vivid Reports CPM will actually generate the data warehouse for you and update and maintain the structure eliminating the burden of having two separate databases while providing all the advantages.