This purely depends on the business need. However, in most of the cases, the Data Warehouse is to be refreshed on the daily basis. The reasons are:
- System becoming consistent only after EOD batch-runs: Data warehouse integrated the data from multiple systems. Many of these systems achieve consistency of data across them after the end of the day batches. For example sales compensation system applying commission calculations, after receiving the sales activity details from sales management system, at the end of the day.
- Avoiding Extraction load during OLTP operations: Data Warehouse, as they do extraction can place a significant load on the source systems, thereby slowing them down. There are smart ways to get around this issue is to have partition based extractions, with data not linked across the partitions.
- Simpler and Manageable: It is easy to related to and simpler to manage. Anything less than daily updates can create complexities like tracking the more frequently refreshed data separately from the daily refreshed data.
There can be business requirements on more frequent refresh. However, one has to do it only when there is a very compelling business case. The business requirements is more to do with the operational needs, and not for managerial or strategic needs. You may also like to look at our views on Data Federation. |