Data Provisioning Options for SAP BW (ODP) with ODP DEMO example
Data Provisioning Options for SAP BW
In this Unit the main content is –
- Overview of Source Types
- ODP source systems
ODP Source Systems- It’s a new way of connecting source systems to BW.
In the following image you see a variety of options that allow you to connect sources to BW.
- The first most prominent one is the connectivity of SAP ERP systems to BW, which is classically based on extractors. It’s one of the most famous use cases of connectivity between an SAP BW and SAP ERP system because extractor is an encapsulated business object, representing few source tables already in ERP system which brings business logic into SAP BW and it’s really a strength of BW which most probably never go away because it’s very easy to get the business logic extracted via this structure into SAP BW. They also contain all the information which you have made in your customizing, like fiscal year variant and fiscal variant etc.
- DB Connect – It’s very generic. So it’s based on SQL, allows you basically create SQL connection between BW application server and the external databases. In a very generic way, extract data from most of the traditional database system into BW.
- File systems are very similar. Also a very generic way of connecting, of loading like CSV files into BW. For e.g. Flat files, anything which is local and contains any structured data in a way that is necessary to load up into the BW.
- Web Services – These are very similar and very fundamental and often used. There are many external tools or applications calling the web service interface because it is an open standard, so it is very easy to call or load data into BW.
- ODP – Its new one called Operational Data Provisioning Connection. It’s the new technology with SAP BW 7.4. As per picture above, it basically provides some connectivity which existed before, so you can connect ERP systems via ODP to your BW systems. It’s an alternative way to the existing way. Also you can connect BW system to BW system using this connectivity. It’s a very strong framework and therefore another opportunity to connect the SAP Landscape Transformation replication server. It’s a triggered based technology and with the use of it we are able to load data into BW via ODP.
- Data Services– BW also provides connectivity to ETL tools like a specific and optimized connectivity to SAP data services. Its standalone ETL tool which is able to load data from a variety of sources into the BW. Even data services have so many different source connections that with the combination of both we can load any source into BW.
- Partner – It’s an open partner API where external applications developed by SAP Partners can write their data into the BW.
- Smart Data Access- It’s called SAP HANA Smart data access which is new with SAP BW 7.4. Its connectivity allows you to connect remote databases to your BW system so It’s similar to DB connect. It also allows you to connect big data instances like Hadoop to your BW system. It also has tools where you can have a real-time and a never ending streaming into SAP BW, and that technology is Smart data Access. This is in general interfaces and via these interfaces we can do now a direct access of data, we can load data in a batch mode in a scheduled way.
Note – Without ODP and Smart data Access, all are non-HANA specific. SDA is based on HANA technology and is only exist in BW on HANA. All other sources can exist with any DB.
Operational Data Provisioning (ODP)
Renovating and Unifying existing connectivity means they provide a new way to connect ERP and external BW systems to BW.
From the BW prospective it’s a very flexible framework and this framework is really allowing us to unify the data transfer between source and target system. In general, it is based in a certain net weaver installation.
As per the above graphic image, it’s really defining the data transfer between so-called Providers. There are different types like:
- SLT(Trigger Based Technology)
- ERP Extractors
- SAP HANA Views (modeled in Native HANA – Analytical, Attribute and Calculation View)
- Source BW
This IDP Technology has their owned queue called Operational Delta Queue (ODQ). ODQ is really an own physical queue in this framework encapsulating the data for a subscriber. Here the subscriber is the target system. It could be:
- Data Services as ETL Tool
- BW System
- Embedded Analytics
We can have one provider with many subscribers. We can share the same queue for delta which is the main benefit of this use case here.
Improved Monitoring capabilities: With the classical connectivity of ERP systems to BW. For e.g. Tcode RSA7 provides the basic overview of delta state of each data source there. In ODP Context it is renovated and it is much more elaborate transaction which allows much better monitor the delta state. It’s really a nice monitor with many capabilities like how many delta subscriptions you have.
Flexible recovery and retention periods: It is also easy to define, a very flexible, recovery and retention periods of each queue. It means, for each source we are bringing or we are transferring, you can define how long the data is being kept there and everything else is then done automatically.
PSA become optional on BW side if use ODP as connectivity because ODQ has a lot of capabilities which we used to have in PSA and therefore now we can now basically skip the PSA Layer.
ODP – Primary Use Cases:
- DTP in BW can work or write directly into an Info provider, without the PSA because this ODQ, which sits in source, also provides the services of PSA. In this way we can recover data very easily request wise and we can do that subscriber by subscriber. So it could also be that we have many BW systems, for instance, sitting on one ODQ.
- From a modeling prospective things will not change, so you still model a DTP based on a data source into DSO but you can check a flag which says by pass PSA when you load data then data will not be transferred into PSA first via info package and then transferred via the DTP, but the DTP directly reads from the source.
- Since BW 7.4 data transfer between BW systems to another BW system we can leverage trigger-based technology SLT.
Practical Demo: ODP Source Systems
- Create ODP Data source on based on ERP Extractor.
- Create a “lean” data flow bypassing PSA.
- Monitoring operational delta queue.
In the following screenshot there is a different source system which is called context for the ODP Case. Here you can create a source system which is then the connection to the source via this context. Here is a list of all source system types from RSA1. In BW 7.4 ODP source system types are probably new.
Go into the source system and create an ODP data source. Right click on application component and create data source.
Choose the name of ODP, so that is the object in the source which provides the data. In this case it’s just an extractor or a data source in source system. It would be the same for any other data extractor by the logistic cockpit or financial controlling. It’s exactly the same, it’s just a generic extractor here and this is the only new step of this whole ODP exercise. So here choosing an ODP the source table and then its behaving like a source, a data source in BW. There are other contexts, for example HANA analytic views, then you would provide the name of an analytic view here which is the object in the source. Same thing applied for SLT then it would be the name of the table in the source system.
In extraction tab it shows the same delta processes, methods eg means after images etc. And it’s really depending on the source. The ODP, it’s just the way how we transfer data in a very compressed way and via this we should see a performance improvement for existing extractors because the way how we data transfer is now different. Check the adapter line what type of extraction you are doing.
Data Load: Its Demo flow in BW with source ODP to show data load processes.
Data Extraction: Directly from source system. PSA not used. Here we can directly jumping over the PSA table and writing directly into info provider.
Execute it: Data Loading
It opens the DTP Monitor.
Here what’s new is the data monitoring about it. This delta queue monitor is really interesting.
Open TCODE ODQMON, it’s a new version of RSA7. If you compare this to RSA7, there is a big difference. It’s looking bit different. You see here again the different sources, the different providers.
There are many requests, many subscribers, means how many targets called the same data.
Now go to BW data source, you will see our extractor and monitor it here.
Here we can calculate the data volume as well; you can see how many data records have been transferred and how many are in the source.
Click on calculate data volume (Extended View). This is really new. This was not possible before in classical BW.
In the following screenshot Compressions ratio is almost 99%, the original size is something 5 billion and after compression size in ODQ is 77 million.
If you double click on it, you will see now different number of subscribers. The subscribers are in our case all the DTPs of our BW.
We can monitor everything what is happening in the source and target.
So this gives you a lot of information about the state of extraction, all the connected systems, all the connected subscribers, all this information is contained in there. This is administration perspective if you would like to create a data retention period and stuff like that.
Nice blog Gunpreet Singh
Thank you..explained in details.
How do we handle invalid records in case on ODQ . In classic Extraction we can edit that request in PSA and load to DSO .
Also if we deleted say the last loaded request from DSO . Would the next load fetch same Request from OQDMON ? Like in PSA we can fetch the same request .
Thank you in advance !!
Even I have the same doubt. Could you please clarify.
Thanks in advance.
If DTP fails with invalid records, it moves to error stack.
Correct error records in error stack.
Create Error DTP and load to target.
2. What will happen if delta request fails and how to extract?
When you delete any delta request pop up will come and give information that ODQ source has data only for limited time technically that is data retention period.
3. Where to check Data retention period?
Go to T-code ODQMON—GOTO – Reorganize delta queues.
Standard data retention time is 24 hrs.
Standard period job scheduled every day at 01:23:45 System time.
Thanks Gunpreet for this useful piece of info.
I have few questions on how exactly the data set is being transferred in case of ODP extraction when compared to SAPI. I do not think there are any TRFCs or IDOCs generated in case of ODP, if that is true how does the data moves from ECC to BW?
Thanks in advance.
Thank you very much for your time.
This is a focused and practical information.
The old tables for V3 queues and delta queues exist yet?
With SAP Hana delta treatments are different?
Thanks for the Blog .. explained it in detail with practical screen shots .. very helpful to understand step by step .
Nice Document.very helpful
I want to load data from BPC BW 7.4 on HANA to BW 7.3 on Oracle.
Will ODP work here ? If not can you advise on solution this, please ?