Monday, 6 January 2020

SLT vs ODP/ODQ for extraction to SAP BW on HANA

SLT vs ODP/ODQ for extraction to SAP BWonHANA 

SLT for BW

SAP LT Replication Server offers a useful alternative for data transfer to BW in the following cases:

• The tables in the source are simple tables without joins or transformation logic.

• The DataSources (extractors) that you want to replace using the SAP LT Replication Server are DataSources on simple tables/views that do not provide a delta mechanism and only contain minimal extractor logic.

Two interfaces are available for transferring data using SAP LT Replication Server:

Operational Data Provisioning

•   Data transfer using operational data provisioning is supported for tables from SAP systems only.

•   SAP LT Replication Server provides the operational data provisioning infrastructure with the source tables as delta queues. The data from the delta queue can be replicated in BW as a subscriber.

•   If you use operational data provisioning, you can load the data directly into the InfoProviders (bypassing the PSA layer) by using a data transfer process. The ODP infrastructure (with delta queues) takes over important services such as monitoring data requests. In addition, the ODP infrastructure has been prepared to support BW and other subscribers with SLT data transfer (for example, SAP Data Services).

Web service

•   Data transfer using the Web service interface is supported for tables from SAP systems and for tables from non-SAP systems.

•   SAP LT Replication Server replicates the data in a Web service DataSource of the BW Persistent Staging Area, where the data is available for further processing.

•   If you use the Web service interface, the data can be pushed at regular time intervals to BW, where it can be updated using real-time data acquisition.


ODP(Operational Data provisioning) is a Netweaver based framework, ODQ(Operational Delta queue) is the queue for data extraction where ODP is installed as a source.

ODP is a unified infrastructure for data provisioning and consumption which supports scenarios like:

Option 1:  ODP based Data Provisioning Aspects for SAP ERP Sources

ODP allows to skip the PSA layer and load directly with DTP from the source system into a DSO. So this ODQ acts as replacement of BW Service API Delta Queue (RSA7) w/o the need of collective jobs run needed earlier.

Option 2:   SLT/ODP based real-time replication

SLT replication server allows loading and replicating data in real time from SAP and non-SAP source systems into SAP HANA environment. SLT allows real time and scheduled data replication, replicating only relevant data into HANA. SLT Server can act as a provider for the Operational Data Provisioning Framework (ODP) and stores data from connected SAP systems in this framework in an Operational Delta Queue (ODQ).

Option 3:   ODP based data transfer between BW systems

ODP extractors can be used to transfer data between BW systems.

Good business case on Data extraction and Modelling

Re-iterating from my earlier email on this topic(attached), below is a good example which shows how SLT replicates the data to HANA DB from ECC system and then replicated data is modelled in HANA and accessed finally to BW using ODP. This is a very good example for faster accessibility to data using SLT and HANA.

Advantages of SLT

• ODP w/o SLT is only possible for BW datasources, ODQ is a replacement of RSA7 delta queue. So this becomes useful when you are dealing only with SAP ERP datasources and there is no requirement of HANA Native modelling further for Analytics.

• With SLT, its possible to replicate source tables from SAP as well as non-SAP systems which can further be used in native HANA modelling. Additionally, it also supports BW modelling. Here is the advantage of SLT which offers more flexible options.

• So, SLT is most useful when you have multiple SAP and non-SAP  sources of data want to leverage the HANA Native modelling aspects and this is more aligned to future SAP roadmap.

Advantages of ODP/ODQ without SLT

  • There is no additional licensing and hardware cost required for ODP extraction without SLT. Hence no additional maintenance effort is required here.
The selection should entirely be made based upon the business requirements and needs.

Thursday, 6 August 2015

3 Ways to find PSA Table Name

We have 3 ways to find PSA table name.

1.  Use table at SE11, Table - RSDSTS: click on display, choose Contents, you can give data source name and execute. You will see below details in a row.


2. At RSA1, left side select info provider or Data source, go to respective data source ,select your data source(showed in green circle), select on wrinch symbol, marked in red circle. Below screen comes up. There you can see PSA Table.


The 2nd step and below screens shows the same, but the way is different. In below link, we need to go data source, from menu --> Go To--> Technical attributes.

3.  At RSA1, left side choose  info provider, right side, Go to respective data source, right click--> manage, there one pop screen comes, there on top you may see request for psa /BIC/B0000XXX.

For this name you need to add 3 ‘0’ at end. That will indicate psa name. i.e The below screen show as /BIC/B0000574, so the PSA name will be /BIC/B000574000. You can see the same name in the below 2nd screen.

From the below screen, PSA Table name - /BIC/B0000574000:

Very small blog but might be useful.

Friday, 31 July 2015

Data Reconciliation

   We had a requirement for data reconciliation between two systems. Client wanted all the migrated data at contract level available in BI Netezza move to SAP BW system. And thus keep a copy of reconciliation data from pre-prod cube into production for audit purposes. The risk involved was once the data was approved by the user, huge number of flat files were required to download in application server and loaded to production cube.

Flat files were required because no RFC was allowed between the pre production and production system.

The best part was data reconciliation of 20 billion records was done by business and our responsibility as a BW developer was to move those reconciled data into the BW system.

Issue/Risk: Too many flat files will be created and which needs to be loaded manually into the infoprovider manually.

Approach:  The requirement had three stages of development.

Stage 1: Already the flow of Insurance, Billing and Payment were present in the development which had all contract level information. A transparent table was provided which contained migrated data in the ECC development system. A generic datasource was created using the table in which delta was created on anniversary date. This datasource was replicated in BW system and a standard DSO was created. It had key fields same as in datasource. Some of the infoobject were already present which were reused and few of them were created as per the requirement. Lookup was done using the migrated DSO in Insurance, Billing and Payment DSO’s. A multiprovider was created which contained all the cubes followed by the query using Multiprovider as the infoprovider. According to the requirement 65 key figures (which included both restricted and calculated key figures) were created and 28 characteristics were required. Query was restricted on key date, calendar month, contract number and group ID. This optimized the performance of the query as 20 billion of records were expected in pre production environment.

Stage 2: Now the challenge was to move the query data into an infoprovider. We went for two approaches.

A) First was using RSCRM_BAPI. But we found that the extract table which was created is always saved in $TMP which cannot be changed also. And it can have only 20 key figures. Since we were having 65 key figures so the idea was scrapped. There were many other limitations found refer the link:

B) The second approach was the standard one i.e. use of an APD. So we created one APD whose source was a query and target was a direct DSO. There was limitation in DSO, a DSO can have only 16 key fields. So what we did is analyzed the data and found 16 fields as key and remaining fields were derived in the start routine of the DSO. In this way we transferred all query data into an infoprovider.

Always remember when we use the APD always create variant in the query as per the selection screen of the query which is saved in the APD otherwise it generates an error of no variable entry found.

Stage 3: Now this infoprovider which had all migrated data of BI Netezaa was moved to an open hub. There was a need of open hub because all migrated data was required to move to production system. And this was possible only through flat files. So an open hub was created whose destination was an application server. Now since there will be multiple files so a function module was created which would generate timestamp and store multiple csv files in an application server.

These flat files will then be loaded to infoprovider in the production environment.

Thursday, 30 July 2015

Define YTD(Year to date) logic on Query Designer

Bex queries with YTD(Year to Date), month average calculation etc. are usually used in analyzing the metrics, input to dashboards etc.

There are various ways of setting up the YTD. In this case, we will see how to set-up a YTD logic on the fly on the query designer.

To perform a YTD calculation for a keyfigure, you should have Calmonth, Calyear on your "selection or RKF". This selection should be called in a "formula or CKF" with standard aggregation and reference characteristic as "Calmonth".

Some of the key steps on how to set-up the YTD calculation :

   1.     The Calmonth on your “Selection or RKF”: this should be always <= (less than or equal to). What this signifies is consider all the months for calculation of YTD.

   2.        Make sure you also have a CALYEAR in your “Selection or RKF”, this should be derived based on the calmonth that you input as a variable screen or customer exit. You have to calculate or find the year via a routine or by using the offset functionality on the variable properties .. i.e, offset =4, length =4(as shown below).

   3.   Based on the steps 1 and 2, we can define the "Selection or RKF" as shown below :

Please note, In the aggregation tab there is no change, use the standard exception aggregation. Since this is a "selection or RKF" we don't use the reference characteristic here. Instead we will use in the next step- 4.
     4.       Based on the step 3 -"selection or RKF", We define the "formula or CKF", with the aggregation as standard aggregation and reference characteristic as “0CALMONTH”.

Learning SAP BW: Everything that beginner needs to start career in SAP NetWeaver BW in a better way

     We have many resources available(Blogs, wiki, documents, articles) on SCN network but it will be great if we have any document which will give directions to SAP BW beginner to explore SAP BW world step by step and also clear many queries, questions, confusion on different aspects of SAP NetWeaver BW.   

    While searching on internet normally we get lost to some other discussions and topics. So despite of having unlimited resources on internet, SAP BW beginners are facing many problems and asking basic questions on SCN.. and there is chance that basic question may get blocked because as per Rules of engagement person asking question needs to search on SCN because there are many resources already available. I think its correct rule which will avoid replication of material, similar discussions and make SCN better place where one can get correct answers without getting more confused. But because of this many beginners are suffering as they don’t get correct path which will guide them to learn SAP BW… I was one of them when I started learning SAP BW.

      I will try creating one such document which will help all SAP BW beginners and will try guiding them. This document is result of same.

      So let’s start:

     SAP NetWeaver BW is huge topic to cover but I am trying to design my blog in such way that they will provide answers to many queries which are running in your mind as beginner.

   First aspect or first question which comes to your mind as SAP BW beginner could be one of the following or related:

   Is it really worth enough to learn new technology?

   Is SAP NetWeaver BW worth enough to look as a career path after few years of experience in SAP?

  Is it good to switch to SAP NetWeaver BW after getting experience in SAP ABAP or some other SAP module?

   I am functional consultant. Can I get basic knowledge of SAP NetWeaver BW?

   I am new to SAP world (Fresher). Can I choose SAP BW for career path?

   What is the market value for SAP BW? What is the future?
   And many related questions. Am I right?

   The answer is Yes,
  In my opinion SAP BW is really worth enough to learn and choose as a career path.

   Let me explain you few more things.

   For any industry, business, company or Enterprise the most important thing is Data. It can be historical data, transnational data, master data, or data in any form or type. The main point is Data is important for any company to run business and to stay in business and no need to tell you that SAP NetWeaver BW(Business warehouse) is all about it.

  Business data plays key role in shaping company overall strategy. All fortune 500 companies care a lot about their own business data. Now a days as companies are expanding their business globally, the volume of data which any company needs in day today business, for developing future strategy is increasing day by day. So in this kind of situation application, tool and technology which will take care of company data plays key role. SAP BW is the solution which many companies already decided to invest in.

   As data volume is increasing day by day so to resolve related performance issue we have SAP HANA in place which is combination of hardware and software which will provide base architecture for BW. SAP BW on HANA is reality in today’s world.

   There are quite good demand in market for SAP BW skill set and in my opinion it will remain for next couple of years, till date these entire new products(like HANA) will get stabilize in market.  Anyways in any technology to stay in business you need to acquire knowledge about upcoming new technologies, new versions, updates and all which have potential to affect your current technology demand.  In case BW is also same. As there are many new technologies, products are in line which are related  to BW and have potential to affect BW demand like HANA, BI, BO and all. Does SAP HANA replacing BW? In my opinion,the answers is no.

   On internet there are survey available which will give you some more detail about current BW rate and pay scale. Certainly everyone will think about this too.

   This is all about Market demand, market condition.

   Now let’s go through Pre-Requisite to Learn SAP BW.


·         In my opinion, to learn anything the first and mandatory pre requisite is passion towards it. If you have passion you can learn this technology even if you don’t have following other skill set. I mean you can build following skill set and still can reach to your goal if you have passion.

·       We have few tasks in BW for which we need ABAP knowledge. That is extraction, Routine (Field level Routine, Start and End routine in transformation step).  ABAP knowledge will be great. If you don’t have ABAP knowledge then also you can lean remaining things in SAP BW and even learn basic of ABAP. In my opinion if you have experience in ABAP then it is really good while learning any new technology in SAP because you will come to know what is happening in background and you get some more confidence.

·    Experience in Data-warehousing tools will help you understand database related concepts.

·       Functional knowledge of at least one SAP module will give you good start while exploring and understanding SAP BW and it will help you in modeling also.

·     Analysis skills if you want to understand the reporting needs of your customers or end-users in one go without rework. This plays very important role as in BW because after looking at the report if end users says you need to change few things you will need to change on every level which adds time and money and energy. So to understand business requirement in one go analysis and functional knowledge will help a lot.

   After pre-requisite let me explain different aspect of SAP BW:

    -   SAP BW architecture:

   It is important to understand basic architecture of SAP NetWeaver BW. This will help you throughout your career with SAP BW.

    -   Extraction:

   This part of BW is all about extracting needed data from different source system. Source system can be ECC system, any other ERP, Files and all. There are   different types of extractor and related knowledge will help you in extracting correct data. It involves complex logic related to delta mechanism, LO cockpit and all. Data retrieval is one of the data warehousing processes in SAP. SAP provides mechanisms for retrieving data (master data, transaction data, metadata) from various sources. You can differentiate as to whether the SAP BW is the target or the source of the data transfer: If data from various sources is retrieved from various sources for transfer into a SAP BW system, the SAP BW system is the target of the data transfer. If the data from SAP BW is retrieved for distribution within the SAP BW or for distribution into analytical applications or other applications, the SAP BW system is the source or hub in relation to data transfer.

   -    Modeling:

   This Part of BW is very important because in this you will create data model by using different objects like info-object, infoSource, datasource, DSO, Cube, Multi-provider, etc., DTP and transformation also comes into the picture. It required good knowledge about all these objects because every object has its own advantage and different process of working. If you know all of them you will be able to build robust data model. Writing routine is also part of modeling. Routines can be field level, start or end routine. Along with this you will need knowledge about of PSA, all modeling objects, DTP, transformation. Process chains,

   -     Reporting:

   This part of BW deals with actual reporting skills where you actually build queries using BEx or BI, BO and use it to display reports for end users.

   SAP BW uses BEx tool (Business explore) for reporting purpose. The Business Explorer is the SAP Business Information Warehouse component that provides flexible reporting and analysis tools for strategic analyses and decision-making support within a company. These tools include query, reporting, and analysis functions. You can use BEx Information Broadcasting to distribute Business Intelligence content from SAP BW by e-mail either as precalculated documents with past data, or as links with live data. You can also publish it to the Enterprise Portal.

   The Business Explorer gives a large spectrum of users access to the information in SAP BW: Using the Enterprise Portal, using the Intranet (Web Application Design) or using mobile devices (WAP or iMode-enabled mobile telephones, Personal Digital Assistants). Now a days BI and BO is also in market which many companies are considering for advance anlysis and many new advance fetures:

   Some more good links:

   Good books for SAP BW:

   A Practical Guide to SAP NetWeaver Business Warehouse 7.0
By Bharat Patel, Amol Palekar, and Shreekant Shiralkar

  Data Modeling in SAP NetWeaver BW
  by Frank K. Wolf, Stefan Yamada

  SAP Certified Application Associate — Business Intelligence with SAP NetWeaver 7.
  By Lisette Bellissimo

  Inside SAP BusinessObjects Advanced Analysis
  by Ingo Hilgefort

  SAP BW: A Step by Step Guide for BW 2.0 (Paperback)
  by Biao Fu, Henry Fu

  SAP BW Data Modeling
by Norbert Egger, Jean-Marie Fiechter, and Jens Rohlf
  Data Modeling in SAP NetWeaver BW
  By Frank K. Wolf, Stefan Yamada

  You can use following SAP Press link to look for latest books on BW.

  That’s it for this blog. Happy Learning.