Near-line Storage (NLS) is used to archive BW data which can still be available for reporting using BEx queries. If you are behind BI Optimization, it is a great way to save BW database space by moving less frequently used data to NLS.

The Near line data is stored in a compressed form and the storage is inexpensive compared to BW database.


So, What’s next after data is archived to NLS? Is it ready for reporting? You will find answers in this Blog.


Once data is archived to NLS, BW still needs some adjustments in order to effectively report data from NLS. This Blog will help understand the changes needed to control NLS access, scenarios which need extra attention with NLS, tips and techniques to optimize query read from NLS.


How to control access to NLS?


     With BW data archived to near line storage, BW queries have the capability to retrieve data from BW database and NLS seamlessly.  This means, data is read from BW and from NLS and data is merged in the query.


As of BW 7.3, NLS usage in queries can be configured at four different levels:

  • In the properties of the BEx Query Designer (Level 1)
  • In the query selection screen using near line storage variable (Level 1)
  • In the properties of the Multi Provider (Level 2)
  • In the properties of the Part Provider(Info provider part of multi provider) (Level 3)

The setting is prioritized Top-Down with Query Designer property and query selection screen variable in the same level. For eg, If the BEx query is set to read from NLS, the settings in the info provider is overridden and ignored even though it is disabled to read from NLS.


As of BW 7.3, in the Default setting, access to data in near-line storage is disabled, even if a data archiving process (DAP) with near-line storage exists for an Info Provider. It has to be switched on and controlled to retrieve required data and avoid unnecessary issues caused by NLS.


Let’s see how data retrieved from NLS can be controlled at each level. Assumption is, there is a DAP with near line storage, data is archived in one or more part providers and query is created on a Multi provider that includes these part providers.


I. In the properties of the BEx Query Designer:

Data read from NLS can be specified in Query Designer -> Query properties -> Extended -> Near-Line Storage.

/wp-content/uploads/2016/06/pic_1_975676.png

There are 3 options to choose from:

     1.    Do not Read Near-Line Storage

     2.    Read Near-Line Storage

     3.    Use Near-Line Storage according to part provider Setting


Do not Read from Near-Line Storage:

This is the setting of the query by default. This option will prevent the queries from reading NLS even though data is stored in NLS.


Read Near-Line Storage:

As I said in the beginning, If the BEx query is set to read from NLS, the settings in the info provider is overridden and ignored even though it is disabled to read from NLS. So, it is advisable to use “Read Near-Line Storage” when all the part provider in the multi provider needs to access NLS.

Use Near-Line Storage According to Part Provider Setting:

This option gives more control in enabling NLS access to some of the part providers where NLS access is required.


For example, the settings below enable read from NLS on the part providers only where Near-Line Access is switched on. It does not retrieve data from archive for those part providers where Near-Line Access is switched off even though it has data archived.


BEx Query Designer – ‘Use Near-Line Storage According to Provider Settings’

MultiProvider – ‘Near-Line Access Set the Same as for PartProvider’

PartProvider – ‘Near-Line Access Switched On/Off

II. In the query selection screen using near line storage variable:

     Data read from NLS can be decided during execution of the query, the decision here will override all the info provider/Multi provider setting and retrieve data from NLS.


This is achieved by using Near line storage variable in the User Input Screen.


Create a variable in the query designer on characteristics 0NEARLINE by clicking the icon highlighted below.

/wp-content/uploads/2016/06/pic_2_975685.png

Use the User Entry variable as the option for Nearline-Storage in Query properties.


This will let the user to decide and retrieve data from NLS even if it is switched off in the properties of Info provider/Multi provider.

/wp-content/uploads/2016/06/pic_3_975689.png

/wp-content/uploads/2016/06/pic_4_975690.png


This option helps if online data in BW cannot be retrieved by the query due to issues in NLS or poor performance in retrieving NLS data.

III. In the properties of the Multiprovider:


MultiProvider -> Extras -> InfoProvider Properties -> Change -> NLS Usage

/wp-content/uploads/2016/06/pic_5_975691.png


NLS Switched on at the multi provider level will let the query to access archived data in all the part providers irrespective of the NLS property in each part provider.


But again, NLS property of query will override if the query is set to “Do not read from NLS”


IV. In the properties of the Part Provider


InfoProvider -> Extras -> InfoProvider Properties -> Change -> NLS Usage


The table below will help understand the data retrieval behavior of NLS data.

Query or

NearLine Variable        

Multi Provider

Part Provider

Result

Do not Read NLS

NLS Switched OFF

NLS Switched OFF

Data Not Retrieved from NLS

Read NLS

NLS Switched OFF

NLS Switched OFF

Data Retrieved from NLS

According to

Part Provider Setting

NLS Switched OFF

NLS Switched OFF

Data Not Retrieved from NLS

Do not Read NLS

NLS Switched ON

NLS Switched OFF

Data Not Retrieved from NLS

Read NLS

NLS Switched ON

NLS Switched OFF

Data Retrieved from NLS

According to

Part Provider Setting

NLS Switched ON

NLS Switched OFF

Data Retrieved from NLS

Do not Read NLS

Part Provider Setting

NLS Switched OFF

Data Not Retrieved from NLS

Read NLS

Part Provider Setting

NLS Switched OFF

Data Retrieved from NLS

According to

Part Provider Setting

Part Provider Setting

NLS Switched OFF

Data Not Retrieved from NLS

Do not Read NLS

Part Provider Setting

NLS Switched ON

Data Not Retrieved from NLS

Read NLS

Part Provider Setting

NLS Switched ON

Data Retrieved from NLS

According to

Part Provider Setting

Part Provider Setting

NLS Switched ON

Data Retrieved from NLS


Turning on NLS? Need EXTRA Attention here!


     While we saw the options to control NLS data retrieval at various levels, here are few design considerations that need to be tested in existing or new BW objects to make sure archived data can be extracted without any error when NLS is turned on.


1.    Check for navigational attributes used in query

     If a navigational attribute is used in the query created on top of a multi provider, the multi provider should have the base characteristic of the navigational attribute available in the multi provider AND it must be assigned to info object in all the part providers where NLS is turned on/NLS data needs to be retrieved.  For example, If 0MAT_SALES__0MATL_GRP_1 is used in the query, 0MAT_SALES must be available in the multi provider and it must be mapped to 0MAT_SALES (in info object identification) for all the part providers where NLS is turned on.

This design adjustment is needed to read data from NLS as navigational attributes are not available (not archived) in NLS. Instead, its corresponding base characteristic is requested from NLS and the attributes from BW database.

Query produces the following error if this adjustment is needed.

           DBMAN 309 Missing mapping for characteristic ‘_‘ of InfoProvider ‘ _ $N’

/wp-content/uploads/2016/06/pic_6_975693.png

Alternatively, this design adjustment is not needed if Smart-Data-Access (SDA) on HANA is used.

2. Check for Constant value in the “Provider Specific properties”


       If an info cube has a constant value in the “Provider Specific properties” and NLS is turned on in that info cube, the constant value is not passed on to NLS interface resulting in error or incomplete extraction. When data is read from near line storage using a query or DTP or list cube, Query/DTP will return the following error or fail to finish the extraction. In rare cases, it does not extract any data from NLS without any error/warning message.


  • RS_EXCEPTION101: Inconsistent input parameter (parameter: missing i_th_sfc-chanm, value <CHAR>)
  • DBMAN305: Error reading the data of InfoProvider <INFOPROV>$N


This is due to insufficient information sent to near line storage.

Check for constant value in the “Provider Specific properties”  (for example 0FISCVARIANT set to constant value K4) and make one of the adjustments from below options.

/wp-content/uploads/2016/06/pic_7_975694.png

     1. Add Field mapping 0FISCVARIANT = K4 in the transformation

                     (OR)

     2. Add 0FISCVARIANT in query drilldown. It can be a hidden field if needed.

                    (OR)

     3. For BW 7.4 and above, check SAP note 2164494 and implement if applicable

3.    Check for Constant value assigned in Navigational Attribute


If a constant value is assigned to a Navigational Attribute in RSD1, the same error with same symptoms as in point 2 can occur when NLS is turned on in an info cube which uses this Navigational Attribute.


  • RS_EXCEPTION101: Inconsistent input parameter (parameter: missing i_th_sfc-chanm, value <CHAR>)
  • DBMAN305: Error reading the data of InfoProvider <INFOPROV>$N


This is due to program logic used to retrieve information from near line archive.

Apply SAP note 2275935 to adjust the program logic and to avoid this error.

Tips for effectively reporting on NLS:


  • Maintain the corresponding settings in the Query Designer and in the metadata of the InfoProvider to prevent the system from reading data for a query from NLS if no archiving has taken place for the affected InfoProvider.
  • If data is read from NLS, avoid navigation attribute selections as much as possible. This prevents master data from being read with some very complex SQL statement.
  • In case of using PBS CBW NLS(one of the certified vendor for NLS), update master data and hierarchy snapshot  into NLS on a regular basis.
  • Use query statistics in RSRT to identify performance issues and optimize data archiving. Cube name followed by $N in the Aggregate column shows data is read from NLS, whereas cube name followed by $X indicates data is read from HANA or BWA interface.
  • As of BW 7.4 SP 8 and HANA SP7 ‘HANA Smart Data Access (SDA)’ can be used for query access to data in Sybase IQ as a near-line storage solution. It optimizes execution of queries by moving processing as far as possible to the database connected via SAP HANA Smart Data Access.

Smart Data Access Vs Traditional Query Access:


Data from NLS is transferred to HANA via SDA. So, most of the processing happens in HANA and sent to BW application server.  From query perspective, data is retrieved from BW only.

/wp-content/uploads/2016/06/pic_8_975695.png

Fig: Reporting on archived data using SDA

Whereas in traditional BW query access to NLS even with HANA, data is retrieved from NLS and from BW database and the final calculations are performed in the BW application server. This is applicable for querying of BW data residing in NLS archive for both partner solutions and native SAP IQ solution.

/wp-content/uploads/2016/06/pic_9_975696.png

Fig: Reporting on archived data using traditional query access to NLS


I hope all this information will help in designing more optimized, controlled NLS reporting and also throw insights on what to expect when planning an NLS implementation. Thanks for reading!


Reference:

https://help.sap.com/saphelp_nw74/helpdata/en/ae/776dfb9ebc4c2389454c711f1ae6c2/frameset.htm


https://help.sap.com/saphelp_nw73/helpdata/en/4c/2c60d7477f51e6e10000000a42189b/content.htm?frameset=/en/1a/2038b6f2e1430c8178aa74d6c72a5a/frameset.htm&current_toc=/en/c5/f1c99abdd3fb4aa7febe103b5d77e5/plain.htm&node_id=182


https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F64653D3030312669765F7361706E6F7465735F6E756D6265723D3232363537323926


https://wiki.scn.sap.com/wiki/display/BI/Example%3A+NLS+and+DBMAN+309

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply