What effect does the archiving strategy have the effect on the ODS / Cube / Report design.
1) We are contemplating on the issue of having the calculations done in the front end vs in the cube and its effect on archiving.
2) Snapshot data vs non-cumulative data
Wondering anyone could please explain the effect of archving strategy on the above items. We are planning to archive data older than 5 years going forward.
Any BW project must consider two factors when making archiving decisions for BW.
How long do we need to keep data in the system?
Historical data has value, but keeping it forever is not sensible. Over time, the business value of the data becomes less than the costs incurred to store and manage it.
How do we limit the volume of historical data so it doesnt jeopardize query performance?
Aggressively removing old data from the BW system allows quicker access to important and useful information. Basic rule of thumb: Detailed data has a shorter useful life, while summarized data has a longer useful life. Consider these factors:
1.Common time reporting horizons are variable depending on reporting area, industry, and season (below):
Financial 24 to 30 months (current year plus prior year)
Sales/ production/HR One to seven months
Retail sales 65 weeks
2.Data-retention timeframes typically vary by reporting area as well as by business division/organization and geographic location.
3.Data-retention granularity varies by reporting requirements. For example, executive financial reports typically require a summary while accounting reports require detail. The COO might want a summary while a plant manager needs detail.
4.Little-used data might still be required in strategic reporting. After data loses its immediate use i.e., it is no longer included in the current year/previous year time-window its usage frequency in BW drastically diminishes. However, BW users often demand that they have access to this data for their historical reporting.
The project requirements/BW users will require both i.e. volumes of historic data and optimized BW system performance.