One of the most common requests from SAP BW end users can also lead to one of the biggest errors in BW design: They want quick queries, yet they also want access to significant volumes of data in BW. This request for peak performance and a long data horizon contradicts itself, and it is the BW development team’s responsibility to prevent inappropriate system design.

The value of historical data diminishes quickly. Even if the system is built to handle all historic data, the novelty fades as the end users realize that only the current and previous 12 to 18 months of data are useful in most analytical business reports. As BW databases grow in size, it becomes more difficult for the system to load, store, manage, and report on the vast amount of aged data, and it fails to provide acceptable system performance.

Archiving seldom-used or “dead” data is critical for better BW performance (query, load, backup, upgrade, and database analysis), hardware efficiency, and administrative cost control. You must consider two factors when making archiving decisions for BW.

How long do you 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 you limit the volume of historical data so it doesn’t 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:

• 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

• Data-retention timeframes typically vary by reporting area as well as by business division/organization and geographic location.
  
  
• 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.
  
  
• 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.

So which is it that BW users want: volumes of historic data or optimized BW system performance? Well, both.

Several options exist for handling this design conundrum, and BW administrators must select the appropriate proven, durable and affordable strategies to keep their aged data readily accessible to users. These options include augmentation of the BW hardware/software infrastructure, the SAP ArchiveLink tools, the SAP Archive Development Kit (ADK), and external “alternative storage” solutions.

It is important to think about the issue of database growth in BW during your design phase. Then, you can include methods for handling this data as part of your business intelligence architecture strategy. If you do not plan upfront, you may be pressed to make a hasty decision in the future to satisfy complaints from your BW end-user community.

The key to managing this situation is to assure BW end users that regardless of the solution chosen, they will have easy access to all historic data for reporting purposes. The challenge for the IT team is to choose the safest, most efficient and cost-effective method for managing historic BW data for its user population.

I will review the four options — augmenting your infrastructure, ArchiveLink, ADK, and alternative storage solutions — in light of the factors you must consider. Table 1 shows the pros and cons of each.

Augment Your Infrastructure

This involves adding more servers and buying more database space to support BW data volume. In this scenario, all data is still kept in the primary BW database for direct user access. Hardware upgrades improve system performance, and the databases are extended to accommodate more data.

Pros: All data remains in the primary database, and you don’t have to manage a process for removing or recovering data for the BW system. You implement no new software applications and add no administrative processes. From a planning and strategy standpoint, this is the easy road.

Cons: Adding hardware is an expensive option, and while adding database space is not terribly expensive, it is a reactive approach that is not cost-effective in the long run. In choosing this option, as the BW database grows in size, query performance can be impaired by the number of records that must be searched to produce the results set, and administration functions (load, indexing, building aggregates, etc.) become more involved and time- consuming. Furthermore, the limitations in the capabilities of the available CPU and disk products create a performance ceiling for data-burdened BW systems. For these reasons, the easy road should be the one less traveled.

ArchiveLink

With ArchiveLink, you can capture and store BW data in document form. It is used to collect physical and electronic documents into an archive system, and it is an alternative to photocopying, distributing originals, or searching for information in paper-based archives.

ArchiveLink is a part of the SAP Content Management Service for storing external and SAP data. Figure 1 shows how ArchiveLink fits into the Content Management Service architecture. An interface connects to the SAP Content Server for storing documents not worth archiving immediately.

Figure 1

How ArchiveLink fits into the SAP Content Management Service

ArchiveLink is an Applications Program Interface (API) developed by SAP, and complementary software vendors must comply with its standards and protocols. Each vendor must receive certification from SAP that its ArchiveLink solution meets these criteria. Some of the other existing SAP archiving tool vendors such as IXOS use the ArchiveLink interface.

Pros: It is a part of the standard delivered SAP application solution for data storage. Also, any vendor software archiving solutions that are approved by SAP will be compliant with the ArchiveLink API.

Cons: ArchiveLink is geared to work more with the R/3 architecture rather than the BW architecture. It does not store data in a form that SAP BW can easily retrieve or use. Therefore, it is not suitable for storage of data warehouse objects that are required for fast or frequent access in SAP BW. Archiving solutions that use the ArchiveLink interface are more useful in the long-term storage of large-volume datasets, rather than quick-access relational data stores.

SAP ADK

SAP designed the ADK to move data out of the primary SAP BW database and onto various archiving media. SAP R/3 customers using the mySAP Business Intelligence solution typically deploy the ADK to offload and manage their aged data, most often to optical disk.

Pros: The ADK is a standard-delivered SAP data storage mechanism in BW 3.x designed to handle the offload of BW data from the primary BW database. It uses a comprehensive procedure for writing, deleting, and storing BW data, and it allows the use of a blend of more cost-effective data storage media. The ADK is platform-independent, can handle structural changes in the definition of BW elements, and offers an easy-to-use front-end transaction to perform its functions.

Cons: The ADK archiving process is intensive from an administrative standpoint, and it may not satisfy the quick access requirements of the BW end users (Figure 2). This might take away your bargaining chip to encourage the BW users to relinquish data from the active or primary BW database.

Figure 2

End-user access to archived data using the SAP ADK

Alternative Storage: Near-Line Storage

Near-line storage (NLS) can be comprised of a blend of various storage media, such as silo tape storage (where tape cartridges are managed robotically), low-speed disk, high-speed disk and optical disk.

NLS allows end users direct SQL access to data stored outside of the primary BW database on a blend of alternative media to fit their performance and budgetary requirements. With this approach, no BW reload process is necessary (the historical data is held outside of the primary BW database in the NLS system). The result is seamless drill- down capability to reach detailed historical data in the Business Explorer (BEx) Analyzer and BEx Query Designer.

NLS systems function as a database extension to BW. This permits BW users to submit direct SQL queries against aged data on a mix of less-expensive media, with the primary goal of offering faster data access at lower storage and administrative costs. Only a few software vendors offer NLS software applications, including SAP software vendor partners FileTek and Legato.

Data should be reviewed for transfer (if not strictly moved) from the primary BW database to an NLS system once its frequency of access has decreased to less than once every month, as Figure 3 shows. In so doing, this means that less data is in the primary BW database, and thus BW data load performance improves, fact table compression becomes quicker and easier, index and aggregate creation is faster, and fewer records have to be read during BEx query execution.

Figure 3

When to move data to an NLS system

Cons: NLS adds technology to the IT infrastructure, thus increasing training needs. You will likely need outside expertise during the initial deployment, and the interface between NLS and hard-disk storage requires ongoing maintenance. NLS technology is not as advanced as DBMS technology.

Figure 4

Factors to consider when determining which archiving strategy to use

In the final analysis, the best SAP BW data storage option depends upon your data and end-user requirements. Figure 4 shows a grid that helps you decide which option is right for your company. If the BW business users do not require significant historical data for reporting or quick access to historical data, then the ADK and more traditional offline storage systems and media can be used. It is important, however, to be aware of the extra costs of administration and maintenance of these offline systems.

Otherwise, if your end users “want this, and that, some of this, and most of that” and a fast response time on top of it all, then employing a long-term strategy around the use of alternative storage best supports a sound BW design.