What are the requirement of using SAP HANA Smart Data Access with BW Near-Line Storage?
- SAP-NLS Solution with SAP IQ
- SAP BW 7.40 SP5 or higher
- SAP HANA as Database, SP6 or higher
- SAP IQ 16.0 or higher as Nearline Storage Database
How can I configure the BW system to use SAP HANA Smart Data Access for BW query access?
Refer to the document "SAP First Guidance - SAP-NLS Solution with SAP IQ" at https://scn.sap.com/docs/DOC-39627
How can I check if BW Queries use HANA Smart Data Access for reading the archived data?
The activation log of the Data Archiving Process contains the following messages:
Virtual table /BIC/OU created for remote source , schema , table /BIC/OU (Message no. RSDA_SYB030)
CalculationScenario 0BW:BIA:$N deleted (Message no. RSDD_CS019)
CalculationScenario created for data archiving process (Message no. RSDA_SYB032)
The BW Query Execution can be checked using support transaction RSRT:
Call Transaction RSRT, enter the name of a query and choose "Execute and Debug"
Set Debug Option "Display SQL/BWA/HANA Query"
Execute
The screen will show each query sent to the Database, which is either a SQL-statement or a preview for of the statement of the TREX/HANA-API (depending on the query execution mode).
If Smart Data Access is NOT used for reading data from the NLS, then the statement preview will show a SQL-statement with comment /* Near-line Query in pseudo-OpenSQL */ on top.
If Smart Data Acess is used, then there is either a SQL-statement or a call to the HANA-API:
SQL-Statement: SELECT FROM "/BIC/OU" "W1" .... / *$ dmuid= */.
HANA-API: Index/Column View 0BW:BIA:$N is requested
The activation of a Data Archiving Process fails when creating the virtual table
A Data Archiving Process using HANA Smart Data Access needs to create a HANA Virtual Table during activation. If the HANA Database User of the ABAP Application Server (typically SAP) does not have sufficient authorization to create this HANA Virtual Table, then the activation of the Data Archiving Prcocess fails.
Error message: System error: Error creating Virtual Table /BIC/OU: insufficient privilege
For creating virtual tables, the object privilege CREATE VIRTUAL TABLE is required.
If the remote source has not been created by the SAP user, then the SAP- user needs the object privilege CREATE VIRTUAL TABLE, granted by the owner of the remote source.
Therefore it's recommended to create the remote source with the SAP user.
How to find the owner of the remote source: select * from ownership where object_name = ''
How does Smart Data Access function?
- First inn SAP HANA, a virtual tables can be created which point towards the remote tables in different data sources.
- Now the customers then able to write SQL queries in SAP HANA, which could operate on virtual tables.
- Now the SAP HANA query processor optimizes these queries, and then executes the relevant part of the query in the target database and then returns the results of the query to SAP HANA, and completes the operation
How to assign the Object Privilege:
In HANA Studio, open the maintenance dialog of user SAP
Search for the name of the remote source under Tab 'Object Privileges'
On the right-hand side, assign the privileges CREATE VIRTUAL TABLE and DROP
Or use SQL to grant the privilege: GRANT CREATE VIRTUAL TABLE, DROP ON REMOTE SOURCE "" TO "-user>";
Refer to the HANA Security Guide for more information: http://help.sap.com/hana/SAP_HANA_Security_Guide_en.pdf (Section 'SAP HANA Smart Data Access').
How can I improve the Query Performance when reading from archived data?
Refer to the following :
2100962 - BW Near-Line Storage with HANA Smart Data Access: Query Performance
What's new in SAP BW 7.4 SP8/SP9 with regards to BW Query Execution on archived data?
As of BW 7.40 SP8 and HANA SP08, BW Queries can use the so called "HANA-API" to read from archived data. This means that more query features can be pushed down to HANA to reduce post-processing in the ABAP server. This can have a big effect on the query-runtime especially when restricted keyfigures are used. 2063449 provides an overview on the operations that can be pushed down to HANA.
As of BW 7.40 SP8, InfoCubes with non-cumulative key figures can be archived, too. HANA Smart Data Access is required for query processing.
A Calculation Scenario is only created if the InfoProvider has a HANA Column View itself, too. This means that for DataStore Objects (classic), the setting "SID Generation during activation" needs to be set, otherwise the Data Archiving Process will not generate a Calculation Scenario. For the other supported InfoProviders (InfoCube, DataStore Object (advanced), the Calculation Scenario is always created.
After an upgrade from SAP BW 7.4 <= SP7 to to SAP BW >= 7.4 SP8, queries terminate with an error.
The error indicates that the logical index / column view of the Data Archiving Process does not exists. This column view follows the naming convention 0BW:BIA:$N.
As described under the previous point #6, as of SAP BW 7.4 SP8, the so called "HANA-API" is used to read from archived data using HANA column views. These column views are created during activation of the Data Archiving Process (DAP). This means that Data Archiving Processes that are configured to use Smart Data Access need to be activated again after upgrading.
A Data Transfer Process (DTP) reads from an InfoProvider with NLS Archive. The runtime is very long and a significant amount of memory is consumed on the HANA Database.
Please refer to 2203484 and 2210552 which change the behavior of DTP Extraction on InfoProviders with NLS-Archives. If in the DTP, the option "No Extraction using API" is set, then HANA Smart Data Access is not used for extraction, but the ABAP Application Server uses a direct ODBC Connection to the IQ Database. This option typically works better compared to extracting data using SDA.
The activation of a Data Archiving Process (DAP) fails with the following error message: Error in BW: Table already exists.
Please apply 2224621.
You want to use the enhanced Query Runtime with HANA Smart Data Access only for some InfoProviders.
2231332 provides a new configuration option within the Data Archiving Process that allows to deactivate the usage of HANA Smart Data Access for query access.
The archived InfoCube contains virtual Navigation Attributes based on a HANA attribute View. The result of the query is incorrect or empty.
This scenario is not yet supported by the SDA-based query runtime. For the corresponding Data Archiving Process, the optimized query runtime needs to be turned off as explained under point 10.
How can the executed remote queries been checked?
The SAP HANA Studio provides an own monitoring view called "Smart Data Access", which can be found under folder "Provisioning". The view can be used to monitor queries and connections to remote databases.
As an alternative, you can also directly query the system view M_REMOTE_STATEMENTS. Find below a sample SQL query to display the executed remote queries order by start_time descending:
select
start_time,
end_time,
SECONDS_BETWEEN(start_time,
end_time) as duration,
remote_source_name,
fetched_record_count,
remote_statement_status,
remote_statement_string
from M_REMOTE_STATEMENTS
order by start_time desc;
The field remote_statement_string is a NLCOB-field. With the default settings, larger statements will be truncated in HANA Studio. The limit for displaying LOB Columns in a Pop-up can be increased under Window -> Preferences -> SAP HANA -> Runtime -> Result: Set Limit for Zoom (Bytes) to a higher value and enable the checkbox at Enable zoom of LOB columns.
The activation of a Data Archiving Process fails with the following error message (same as in 1956295):
"internal SQL error occurred in DBSL; SY-SUBRC from ADBC(DBSL) = 16; maybe it is due to memory shortage, try to increase the parameter ztta/short_area; refer to developer trace file dev_wX for further"
The activation of a Data Archiving Process includes the creation of a HANA Calculation Scenario. The definition of this Calculation Scenario can become quite large, especially if the InfoProvider contains a lot of BW InfoObjects. As explained in 1956295, it is usually necessary to increase the value of parameter ztta/short_area. Tests have shown that this problem does not occur anymore as of Kernel Release 745 (which is e.g. used for BW 7.50).
Error message when checking the database object of the "OU"-View when using the ABAP DDIC Database Utility (SE14)
For earch Near-Line Archive, a so-called template view is created in the ABAP DDIC. The name of this template view is /BIC/OU, respectively /BI0/OU. This template view is required for using ABAP Open SQL for selecting data using the secondary database connection. If Smart Data Access is configured, then the virtual table is created on the database with exactly the same name. If the database object is checked within SE14 (Database Utility), then the following error message is returned: "Database object for /BIC/OU is inconsistent: (Fields,Primary Index,Storage Type)".
This error message can however be ignored as HANA Virtual Tables do not have a proper represenatation in the metadata of the ABAP Data Dictionary.