SAP Online Documentation
- Data Archiving Process
- Creating Near-Line storage Connections
- Sybase IQ as a Near-Line Storage Solution - SAP Help
- Blog: SAP Sybase IQ as a Near-Line Solution
- Read Stored Data
If you want to avoid a constant increase of the amount of data in your BW system without deleting data, you can use data archiving. The data is first moved to an archive or near-line storage and then deleted from the BW tables.
There are basically two possibilities to archive data: you can use ADK-based archiving or near-line storage for InfoCubes and DataStore objects. In this page we only deal with the near-line storage, mainly with the reading of archived data during query runtime.
Archiving based on ADK: This type of archiving is recommended for data that is no longer relevant to any current analysis processes, but must remain archived for certain reasons. In case you need this data for queries it is necessary to load it back as required.
Near-Line Storage(NLS): This type is recommended for data that might still be needed. Storing historical data in near-line storage reduces the data volume of InfoProviders,however, the data is still available for BEx queries! So, you do not have to load the data back into the BW system. Of course, reading data from the near-line storage isn't as fast as from the data base and hence only data should be stored there which is not needed during daily business!
In case of performance problems of queries using navigation attributes please review note 2156717 .
Near-Line Storage Solution (NLS) based on SAP IQ
The adapter for SAPIQ as a near-line solution is delivered with the BW system, see Sybase IQ as a Near-Line Storage Solution and BW NLS with SAP Sybase IQ. As of BW release 7.4 support package 8 and HANA >= SP7, HANA Smart Data Access(SDA) can be used for this NLS solution. In general, SDA has some big advantages as explained below.
SDA: Important Points
- HANA SDA optimizes execution of queries by moving processing as far as possible to the database connected via SDA. The SQL queries then work in SAP HANA on virtual tables. The HANA Query Processor optimizes the queries and executes the relevant part in the connected database, returns the result to SAP HANA and completes the operation.
- In particular this means that the BW NLS query can run a HANA optimized statement. E.g. also filters on navigation attributes and hierarchy node restrictions can be transferred to the data base, see note 2156717 for details.
- The DAP setting 'Use Query Optimization if Applicable' needs to be active in order to use the SDA solution, see Example III for further details. On BW/4 Hana systems this setting does not exist any longer, 'Use Query Optimization if Applicable' is always set to X.
- as explained in notes 2100962(point 2) and 1990181, the HANA Query Optimizer needs optimizer statistics for the HANA Virtual Table in order to create a suitable query execution plans. SAP Note 1990181 provides the ABAP Report RSSDA_CREATE_TABLE_STAT which can be used to create such statistics. See also Example IV - Query II.
- If the NLS provider should be accessed at all is decided in BW by the Analytic Engine. Only if the relevant query restrictions(regarding the characteristic which is used as primary partitioning characteristic) intersect with the archived data set, then a corresponding statement will be created by the BW DataManger, see Example IV. There is also a optimization available(see note 2633035 listed below) which might be necessary in case the query does only use so called 'local' restrictions(see Processing of Filter Values) regarding the partitioning characteristic.
- Example I: NLS based on SAP IQ and HANA SDA SAP(InfoCube)
- Example II: NLS based on SAP IQ
- Example III: DAP setting 'Use Query Optimization if Applicable'
- Example IV: NLS based on SAP IQ and HANA SDA SAP(ADSO: How to check remote statements)
- NLS and DBMAN 309 error
- NLS and Non-Cumulative Key Figures
- Archiving and Compression flag example
- BW NLS Archiving and Deleting data
- Maintain NLS settings for Queries
BW/4Hana and Data Tiering
Near-line storage (as described above) with SAP IQ is still supported in SAP BW∕4HANA. In addition there is the concept of Data Tiering Optimization (DTO) which offers the following advantages:
- A single data tiering solution for hot data (hot store in SAP HANA), warm data (warm store in SAP HANA Extension Nodes or SAP HANA Native Storage Extension) and cold data (cold store in SAP IQ).
- Central definition of the data 'temperature'(hot,warm,cold) based on partitions of the Advanced DataStore object.
From a technical perspective, cold data corresponds to the data stored in NLS. So, when you want to check details of the cold data access of a BW query, you can proceed as described above in Example IV(see also example below).
SAP Online Documentation
Tables and Transactions
- RSOADSOPART - Datastore Objects: description of range partitioning
- RSODTONODELOC - DTO: DB-Nodes with their associated dto-location
- RSODTOPARTINFO - DTO: Information about partitions
- Transaction RSOADSODTO
- 2165650 - FAQ: BW Near-Line Storage with HANA Smart Data Access
- 2100962 - FAQ: BW Near-Line Storage with HANA Smart Data Access: Query Performance
- 1990181 - BW HANA SDA: Create Database Statistics for Virtual Tables of Open ODS Views and Near-Line Storage (NLS) Archives
- 3019063 - NLS Data of InfoCube not displayed in BW Query(about warning "Cannot read near-line storage for PartProvider..."(RS_HCPR_M 101))
- 2633035 - BW with Near-Line Storage and HANA Smart Data Access: Enhancements for Pruning
- 2268398 - Hierarchy Node restrictions lead to a termination of a NLS query
- 2156717 - NLS: queries with navigation attributes/hierarchy node restrictions are slow
- 2203131 - BW Near-Line Storage with HANA Smart Data Access: Turn off read access using the "HANA-API" with HANA column views(with RSADMIN Parameter RSSDA_NLS_TREX_OFF)
- 2202052 - BW Near-Line Storage with HANA Smart Data Access: Poor Query Performance with InfoCubes
- 2104392 - RSADMIN parameter MDS_MAX_DATA_CARD_FOR_SET
- 1814452 - Large SID restriction on virtual BW InfoCube - rsadmin parameter BW_EQSID_CONVERSION_LIMIT
- 2035359 - Large SID restriction on virtual BW InfoCube (2)
- 1997128 - Large SID restriction on virtual BW InfoCube (7.4x)
- 2156282 - Infoset and NLS (archiving) does not work together
See also the following blog https://blogs.sap.com/2016/10/12/sap-nls-solution-sap-bw/
Link to this page: https://wiki.scn.sap.com/wiki/x/HyUCGg