How to Perform HANA Archiving Process

[Former Member]

How to Perform HANA Archiving Process

Summary: This article contains the step by step process of how to perform HANA archiving process in SAP HANA.

Author:         Mitul Jhaveri

Company:    Tata Consultancy Services

Created on:  09 August 2016

Introduction

Without archiving, data volumes in BW database will increase. Due to huge amount of data available in BW database, system performance will degrade and can take a longer time to result in a large volume of data.

Increasing amounts of data that need to be available for further analysis or reporting but that are rarely required to place a load on BW system. If you want to keep the amount of data in BW system constant without deleting data, you can use data archiving.

Archiving data from Info Cubes and Data Store objects is the process to store data in near-line storage. The data is first moved to an archive or near-line storage and then deleted from the BW system. You can either access the data directly or load it back as and when require, depending on how you archived the data. When defining the data archiving process, you can choose between classic ADK archiving, storing in near-line storage, or a mix of both solutions.

ADK (Archive Development Kit) Based Archiving:

• ADK based archiving is recommended for data that is no longer relevant to any current analysis processes or not needed for reporting, but must remain archived for the storage period. Data which has been archived will be deleted from the Info Provider and have to re-load them through flat file if we need archived data again in the report.

• ADK based Archiving solution can be afforded and cost reduction can be done using alternative storage media.

NLS (Near-Line Storage) Based Archiving:

• Near-line storage is recommended for the data that we may still require. Storing historical data in near-line storage reduces the data volume of BW database; however, the data is still be available for BEx queries without re-loading process.

• SAP BW has direct access to NLS data. We can access archived data of near-line storage from the query monitor.

Pre – requisites

• NLS-IQ must be set up by respective team (Analyse IQ team) for further database connection with BW server.
• Before archiving data, Cube data must be compressed. Though system will allow us to archive non-compressed data but as a best practice we must compress data first. If compression is running, we are not allow to do archiving.
• To archive the data, an Info provider must have time characteristics / date field.

Procedure

DB Connection (T-Code: DBCO)

Create a database connection with Sybase IQ. In our system, we are using Sybase IQ as a near line storage. This connection was created by SAP BASIS consultant and the parameter values were provided by IQ team.

Configuration of NLS in BW system (T-Code: SPRO)

Once the connection has been created, we need to configure the connection of NLS with BW system.

T-Code – SPRO → SAP Reference img (F5) → SAP Net Weaver → Business Warehouse → General Setting → Process Near-Line Storage Connection → Continue.

Note: One Sybase IQ server can talk to multiple BW systems. You should only need to create separate databases in IQ for the different BW systems.

Establish new connection between BW and NLS:

Near Line Conn.: Give appropriate name for new NLS.

Name of Class: There are standard classes available to create connection with different database. In our case, we are using the Sybase IQ so we will use      “CL_RSDA_SYB_CONNECTION” class.

Connection Mode: Productive Mode is the recommended mode for normal (productive) operation. Every operation on the near-line connection will be passed to the near-line provider. In query processing especially, a query will terminate with an error message due to the unavailability of near-line storage, unless a running mechanism could exclude near-line access in advance.

DB Connection: Pass the DB connection name that has been created through DBCO T-code by BASIS Team.

Conn. Parameter: The connection string generally comprises a list of name/value pairs, separated by a semi-colon. DBCON is also one of the supportedparameter.

              <Name of the parameter> = <Value of the parameter>

Green Light in status indicates that connection has been established successfully.

Creation of Data Archiving Process (DAP)

Once this NLS connection has been established successfully with BW, we can see one additional process by right click on cube or DSO.

General Settings: Mention the NLS connection name in Near-Line Connection.

Selection Profile:

In this tab, select the primary time characteristics for partitioning. To archive the data, an Info provider must have time characteristics/date field. Without time characteristics system will not allow us to perform archiving.

As a best practice, Cube data must be first compressed before performing archiving. Though system allow us to also perform archiving on non-compressed data.

There is no compression mechanism exist for DSO, so Non-compressed data archiving option is not available for DSO.

Semantic Group: Along with defined time characteristics in selection profile tab, we can also select more granular fields for archiving.

Nearline Storage: Near-Line storage Connection name need to maintain for this tab

Once all the above mentioned steps will complete, Check and activate DAP connection. After the activation, we can display, change or delete DAP. When established DAP is deleted in BW, the corresponding tables and all archiving requests are deleted from the Sybase IQ database.

Archiving Data for Cube and DSO

After DAP creation, next step is to archive the data for cube or DSO. For this, first BW consultant and business SPOC mutually need to decide below points for archiving:

• Based on which time characteristics archiving can perform.
• Up to which period data can be archived.

Cube or DSO → Manage → Archiving tab → Click on Archiving Request.

Primary Time Restriction: Define the value for the time characteristics that we considered as a primary partitioning characteristics. We can set the exact partition range that we want to archive by specifying relative or absolute times.

Further Restrictions: We can use the characteristics that we specified as an additional partitioning characteristics to set further restrictions. In the case of semantically partitioned objects, we can restrict them to one partition here along with time characteristics.

Process Flow Control:

     1) 10 Request Generated: Archiving request is only generated.

     2) 30 Data Area of the request is Locked Against changes (Lock Status)

          We can lock the selected data area of the archiving request to prevent any changes. This step is necessary before data archiving begins.

     3) 40 Write Phase completed successfully (Copy Status)

          The data to be archived is copied into near-line storage or the archive. This step is to be   executed in the background.

     4) 50 Verification Phase Ended Successfully (Verification Status)

          In the verification phase, the system checks that the write phase was successful and that the data can be deleted from the Info Provider.

     5) 70 Verification Phase confirmed and Request Completed (Deletion and Overall Status)

          When the archived data is deleted from the InfoProvider, the archiving process is complete. The archived data is deleted from the InfoProvider with the same selection conditions used to copy the data from the InfoProvider.

          When all the steps of the archiving request have been completed, you can no longer change the status of the request. You can only reload the data.

Status - Copy Status, Verification Status and Deletion Status:

• No icons if the corresponding phase was not yet started or if the request was invalidated
• A yellow traffic light if the corresponding phase is currently active
• A green traffic light if the corresponding phase was already completed
• A red traffic light if processing was terminated in the corresponding phase or if it ended with an error

Status - Overall Status:

• A green traffic light if request reached a consistent intermediate or end state and No more active processes for this request
• The Activity icon if there is an active process for this request
• The Failed icon if request processing was terminated or if it ended with an error.

Process Flow Control for DSO:

• 10 Request Generated
• 30 Data Area of the request is Locked Against changes (Lock Status)
• 40 Write Phase completed successfully (Copy Status & Overall Status)

Read NLS data

Cube or DSO Property

CUBE or DSO → Extras → InfoProvider Properties → Change.

NLS Usage: Nearline access switched off → X Nearline Access Switch on

Query Property

In Query property, we are allow to use near line storage data while executing the query. Even we are also allow to extract the near line storage data according to basic info provider setting. We are also allow to create the variable and for which user will enter the value at run time.

BEx Designer → Query Property → Extended Tab → Nearline-Storage

Variable Entry:

Testing of Archiving Data

Through Manage Cube

1) Without selecting Read Data from NLS:

    Fiscal Period: 008.2009 – 008.2009

     Output: No data found.

2) With selecting Read Data from NLS:

    Fiscal Period: 008.2009 – 008.2009

Output: Data will read from NLS.

Using BEx Query

1) Do not Read near Line Storage:

    BEx Designer → Query Properties → Do not Read near Line Storage

    Enter Selection parameters → Execute (F8)

   Output: No Applicable data found.

2) Read Near Line Storage:

     BEx Designer → Query Properties → Read near Line Storage

     Enter Selection parameters → Execute (F8)

    Output:

3) Through Variable Entry:

BEx Designer → Query Properties → Extended → Nearline-Storage → Value Entry → Create New Variable → Save New Variable → Save.

Execute Report:              

Now, user can select Near Line Storage value at report execution time and according to that system will fetch the data for given selection parameters.

Unload Archiving Request

Sometimes we archived the data and for that archived period, change data or delta will come in future during data loading. In this scenario, system will not allow us to load data for that particular Cube or DSO and throw error message “RSDA 239 - Data record locked by archiving request”.

For this type of failure, first we need to unlock the request, load the data again through DTP and then again archive the data for this period.

Unlock and Re-lock the Archiving Request

Step-1: Unlock the particular request by double click on Lock symbol of request in archiving tab.

Step-2: Unlock Archive Request and Load Data again:

To unlock the archive request, we are allow to re-load data from the Near Line Storage or Delete it from NLS. In Reload, data is available in both NLS and BW and while in Delete, data is getting deleted from NLS and available only in BW.

If we set indicator Also Reload Subsequent Requests, all archiving requests that were created later on are reloaded in addition to the selected archiving request.

Unachieved data is again available in BW with request “Request w/o info package (APO Request)”. Execute the DTP again and monitor the request.

Step-3: Re-Archiving

Once the data loading will complete successfully, Re-archiving for that particular period need to be perform. Monitor the archive request.