NebulaCore Consulting Schedule a Conversation

SAP BW · Near-Line Storage

SAP BW Near-Line Storage series · Archiving walkthrough

Archiving SAP BW Data to Near-Line Storage

Creating and validating a Data Archiving Process with SAP IQ

A practical walkthrough for archiving SAP BW InfoProvider data from SAP HANA to SAP IQ Near-Line Storage and validating access through RSA1, RSRT, and BEx Query Designer.

By Alexandre Capeletto Scaglia Published  Updated 

Scope of this article. This procedure was performed to validate a newly configured NLS infrastructure. It demonstrates the basic archiving path and the relationship between SAP HANA tables, SAP IQ tables, virtual tables, and BW queries. It is not presented as a complete production archiving strategy.

1. Introduction

This article describes a basic procedure for creating an archiving process for an SAP BW InfoProvider and moving data to Near-Line Storage (NLS). The sequence follows SAP's SAP First Guidance - SAP BW: Implementation SAP-NLS/SDA with SAP IQ 16.x, with additional implementation logic used to complete the validation.

The objective was to confirm that the newly configured NLS infrastructure could receive archived data and that BW queries could still retrieve that data when NLS access was enabled.

2. Source object and baseline

The example uses a custom InfoProvider named ZXXXX_O4, with the CALDAY field as the archiving rule. Before archiving, the SAP HANA table /BIC/AZXXXX_O400 contained records with CALDAY values ranging from 20080101 through 20120430, for a total of 523,680 records.

2.1 Display and aggregate source data

In transaction RSA1, locate the InfoProvider, right-click ZXXXX_O4, and select Display Data.

RSA1 context menu for InfoProvider ZXXXX_O4 with Display Data selected.
Selecting Display Data from the InfoProvider context menu in RSA1.

Select Fld Selectn for Output, clear the existing selections, and select only Calendar day. Execute with F8, return to the previous screen, and then enable:

  • Use DB aggregation
  • Output number of hits

Set an appropriate value in Max. no. of hits. The example used 2000. Execute again with F8. BW counts the occurrences of each unique value in the CALDAY column and groups the result by that column.

Beginning of the aggregated CALDAY result showing dates from January 2008 and their record counts.
Beginning of the aggregated CALDAY result before archiving.
End of the aggregated CALDAY result showing dates in April 2012 and their record counts.
End of the aggregated CALDAY result before archiving.

A similar result can be obtained directly from the database with:

SQL query

select CALDAY, count(CALDAY)
from "/BIC/AZXXXX_O400"
group by CALDAY

2.2 Review the SQL generated by BW

Run the query again with Display SQL query selected and Use DB aggregation cleared. Before displaying the data, BW shows a statement similar to the following:

SQL generated without DB aggregation

SELECT
TOP 20000
"O1"."CALDAY" AS "K____070"
FROM
"/BIC/AZXXXX_O400" "O1"

With Use DB aggregation selected, the generated statement includes the count and grouping operations:

SQL generated with DB aggregation

SELECT
"O1"."CALDAY" AS "K____070",
COUNT( * ) AS "K____017"
FROM
"/BIC/AZXXXX_O400" "O1"
GROUP BY
"O1"."CALDAY"

2.3 Establish an RSRT baseline

For comparison after archiving, run a query on the object using transaction RSRT for the period from 01.04.2012 through 30.04.2012. Before archiving, the query returned 22,872 records.

RSRT result counter showing 22,872 records before archiving.
RSRT baseline before archiving: 22,872 records.

3. Create the archiving process

3.1 Configure the Data Archiving Process

In transaction RSA1, right-click the InfoProvider ZXXXX_O4 and select Create Data Archiving Process.

RSA1 context menu with Create Data Archiving Process selected.
Creating a Data Archiving Process for the InfoProvider.

In the configuration window, select the NLS connection previously created in transaction RSDANLCON. Save and activate the configuration.

Data Archiving Process configuration showing the Near-Line Connection field.
Selecting the Near-Line Storage connection in the Data Archiving Process.

Right-click the InfoProvider again and select Manage.

RSA1 context menu with Manage selected for the InfoProvider.
Opening the InfoProvider management screen.

3.2 Create the archiving request

Open the Archiving tab and select Archiving Request.

Archiving tab with the Archiving Request button highlighted.
Starting a new archiving request from the Archiving tab.

For this example, the absolute date range was set so that all data between 01.01.2000 and 15.04.2012 would be archived to NLS. The cutoff was chosen to make it possible to demonstrate the query changes needed to access both the remaining SAP HANA data and the archived NLS data.

Create and Execute Archiving Request screen with an absolute calendar-day range from 1 January 2000 through 15 April 2012.
Archiving selection and target status used in the validation example.

3.3 Execute the request

After entering the selection values, click the first In the Background button. This creates a job that simulates the archiving process.

First In the Background button used to simulate the archiving request.
Background execution for the archiving simulation.

If the simulation job completes successfully, click the second In the Background button to execute processing through the status selected in Continue Processing Until Target Status.

Second In the Background button used to execute the archiving process.
Background execution for the actual archiving process.

Operational note. The test ran the complete process through the final phase. In a production environment, it may be preferable to divide the stages - for example, performing the archive copy during business hours and deleting the source data during a lower-activity period.

4. Validate data placement

After the job completed, the records previously stored in SAP HANA table /BIC/AZXXXX_O400 were divided between the SAP HANA source table and SAP IQ table /BIC/OUZXXXX_O4:

  • /BIC/AZXXXX_O400 in SAP HANA: 10,926 records
  • /BIC/OUZXXXX_O4 in SAP IQ: 512,754 records
  • Total: 523,680 records

The total matches the original record count. Table /BIC/OUZXXXX_O4 also exists in SAP HANA as a virtual table that points to the table of the same name in SAP IQ.

SAP HANA virtual table definition for /BIC/OUZXXXX_O4 showing its remote SAP IQ source object.
The SAP HANA virtual table points to the archived table in SAP IQ.

The completed request appears in the Archiving tab with the selected date range and the archived record count.

Completed SAP BW archiving request showing the selection condition and 512,754 archived records.
Completed archiving request showing 512,754 archived records.

5. Access archived data

5.1 Read NLS data from RSA1

Return to transaction RSA1 and display the data using the same parameters used before archiving. Without NLS access enabled, BW displays only the records that remain in SAP HANA. Because data through 15.04.2012 were archived, the visible result begins on 16.04.2012.

Aggregated CALDAY result after archiving, beginning on 16 April 2012 because only SAP HANA data are being read.
Result when only the remaining SAP HANA records are read.

To include the archived records, select Read Data from Near-Line Storage.

RSA1 data display settings with Read Data from Near-Line Storage selected.
Enabling Near-Line Storage access in the RSA1 data display settings.

After executing the query again, the result includes the dates stored in both SAP HANA and SAP IQ, matching the pre-archiving validation.

Aggregated CALDAY result including dates stored in both SAP HANA and SAP IQ Near-Line Storage.
Combined result after enabling Near-Line Storage access.

5.2 Review the NLS SQL

With Display SQL query selected and Use DB aggregation cleared, BW now shows two queries. The first still reads the SAP HANA table /BIC/AZXXXX_O400. The second reads the virtual table /BIC/OUZXXXX_O4, which points to SAP IQ:

SQL generated for the NLS virtual table

SELECT
TOP 20000
"W1"."CALDAY" AS "K____070"
FROM
"/BIC/OUZXXXX_O4" "W1"

5.3 Enable NLS in BEx Query Designer

Running the same RSRT query that previously returned 22,872 records now returned only 12,164 records. By default, the query was still reading only the records in SAP HANA.

RSRT result counter showing 12,164 records when only SAP HANA data are read.
RSRT result before enabling Near-Line Storage access in the query.

Open the query in BEx Query Designer. In Properties, open the Extended tab and change Nearline-Storage from Do Not Read Near-Line Storage to Read Near-Line Storage. Save the query.

BEx Query Designer Extended properties with Nearline-Storage set to Read Near-Line Storage.
Configuring the BEx query to read Near-Line Storage.

After this change, the query retrieves records from both SAP HANA and NLS and again returns 22,872 records.

RSRT result counter showing 22,872 records after enabling Near-Line Storage access.
RSRT result after enabling Near-Line Storage access: 22,872 records.

6. Conclusion

This procedure demonstrates the basic SAP BW archiving sequence and the relationship between the tables queried by BW and their physical location in SAP HANA or SAP IQ Near-Line Storage.

The validation was successful because the Data Archiving Process moved the selected records to SAP IQ, the total record count remained consistent, and the archived data could be retrieved again after enabling NLS access in RSA1 and BEx Query Designer.