|
Jan 12, 2015
Optimizing SAP BW on SAP HANA Part 1
Jan 7, 2015
How to optimize Reporting Performance in SAP BI
Via Content in SCN
Introduction
We face many challenges in our BI projects in terms of Reporting Performance. Users always expect outstanding performance of our BI reports. No matter what ever we do in the backend (Modeling and Query Designer). I am going to share very important tips & techniques which I have learned through out my experience which consists of some standard thumb rules too.
It is recommended to use Inclusions Instead of Exclusions wherever possible as Exclusions cant access DB indices and will impact performance. Because, when huge no. of RKFs & CKFs are Included in a Query, Restricting, Conditioning and Computations are done for each of them during query execution. This is very time consuming and a high number of RKFs & CKFs can seriously hurt Query Performance that means, please open your query definition and pick up the fields which are used in the query while defining Aggregates. Delete the unused Aggregates
If the aggregates are too Large, those not only degrades the Query performance but also loading performance by longer times to roll up and Attribute Change runs also takes longer times.
Make sure you get good valuation indicators for your Aggregates, but not like below
It is recommended to to Suppress Result rows Wherever possible.
It is recommended to use SAP Exits whe re ever possible and bring down the Customer Exits
SAP Suggests free characteristics in reports should be limited to 8-10.
In RSRV checks, free characteristics usage is marked in red which has very high impact on reports
It is recommended to reduce RKFs & CKFs in the Query to as few as possible.
It is recommended to redefine your Aggregates in an optimized way by taking Statistics(BI Admin Cockpit should be in place) & Query definitions into Consideration.
Archiving (NLS) is recommended to archive unused data.
a. Reduction of online disk storage
b. Improvement in BW query performance
c. Increased data availability as rollup, change runs and backup times will be shorter
d. Reduced hardware consumption during loading and querying
Reports should be designed on Multiproviders wherever possible.
Logical and physical partitioning is recommended for bet ter performance.
Extensive use of Filters at Query level is recommended.
Select appropriate Read mode settings for Multiproviders with "H" and Infosets with "X" in RSDIPROP t-code
Reporting on Infosets should be considered below tips :
a. Do not select all the fields which are part of Infoset definition like below. You can select the fields which we want to use in query only.
b. You should select "Use Selection of Structure Elements" in RSRT-->Properties
c. Do not make too many joins as it cause high runtimes to fetch data after the joins
Statistical Reports performance can be improved by broadcasting query result to cache and prefilling cache.
Archiving (NLS) is recommended to archive unused data.
a. Reduction of online disk storage
b. Improvement in BW query performance
c. Increased data availability as rollup, change runs and backup times will be shorter
d. Reduced hardware consumption during loading and querying
Reports should be designed on Multiproviders wherever possible.
Logical and physical partitioning is recommended for bet ter performance.
Extensive use of Filters at Query level is recommended.
Select appropriate Read mode settings for Multiproviders with "H" and Infosets with "X" in RSDIPROP t-code
Reporting on Infosets should be considered below tips :
a. Do not select all the fields which are part of Infoset definition like below. You can select the fields which we want to use in query only.
b. You should select "Use Selection of Structure Elements" in RSRT-->Properties
c. Do not make too many joins as it cause high runtimes to fetch data after the joins
Statistical Reports performance can be improved by broadcasting query result to cache and prefilling cache.
Deletion of unused Queries is recommended
Delete temporary Query Views is recommended.
It is recommended to be careful while creating Cell Structures as they require high query run times and will lead to performance degradation.
Program RSR_CACHE_RSRV_CHECK_ENTRIES can be scheduled to run on regular basis to remove the unused Cache entries .< /p>
Make proper Query read mode and Cache mode settings in RSRT-->Properties. The recommended Cache mode could be 1 or 5.
It is recommended to be careful while creating Cell Structures as they require high query run times and will lead to performance degradation.
Program RSR_CACHE_RSRV_CHECK_ENTRIES can be scheduled to run on regular basis to remove the unused Cache entries .< /p>
Make proper Query read mode and Cache mode settings in RSRT-->Properties. The recommended Cache mode could be 1 or 5.
Conclusion :
It is always better to keep an eye on above points while developing our Business models and Queries. This blog will help us to satisfy our users with good reporting performance.
It is always better to keep an eye on above points while developing our Business models and Queries. This blog will help us to satisfy our users with good reporting performance.
Oct 18, 2014
SAP Hana Series - 5
HANA Overview - 5
How is HANA licensed?
SAP tried to keep licensing simple with HANA.
HANA is available in the Cloud as Infrastructure as a Service (IaaS), Platform as a Service (PaaS) and as an application platform (AaaS), and it is possible to buy all those options now, on a monthly basis, from the SAP Website.
For on-premise customers, HANA is licensed in one of two major ways:
First, is as a proportion of your Software Application value (SAV), just like you can license other databases from SAP. This could be for your whole estate, or for a specific product like BPC.
Second, is by the unit, which is 64GB of RAM. There are a few editions of HANA, depending on your need, that bundle other software and allow more, or less, restrictive usage. The pricing is tiered, depending on the number of units you buy, and accretive.
In all cases, HANA licensing includes a lot of functionality that you would pay extra for in other databases. For example, Dev, Test, HA, DR licensing are always included. And if you buy HANA Enterprise, you have access to all functionality at no additional cost – including Predictive Libraries, Spatial, Graph, OLAP, Integration and Web. HANA contains a huge amount of functionality that would require 20-30 different SKUs from Oracle.
For those customers who need the base functionality of HANA but not the bells and whistles, there is now a HANA Base Edition, on which you can add other functionality as required, at a lower cost point.
| Feature | HANA Base | HANA Platform | HANA Enterprise |
|---|---|---|---|
| Partitioning, Compression, Security | ✓ | ✓ | ✓ |
| Calculation Engine, Aggregation Engine | ✓ | ✓ | ✓ |
| XS Engine, River, SQLScript, HANA Studio | ✓ | ✓ | ✓ |
| BRM, BFL | ✓ | ✓ | ✓ |
| Smart Data Access | ✓ | ✓ | ✓ |
| Predictive Analytics Library | ✗ | ✓ | ✓ |
| Geospatial | ✗ | ✓ | ✓ |
| Planning | ✗ | ✓ | ✓ |
| Graph | ✗ | ✓ | ✓ |
| Search, Text Analysis | ✗ | ✓ | ✓ |
| System Landscape Transformation, Data Services | ✗ | ✗ | ✓ |
~~~Contd.
Oct 13, 2014
SAP HANA Series - 4
HANA Overview - 4
Is SAP HANA a database, platform, appliance, or cloud?
SAP HANA was designed to be a truly modern database platform, and as a result the answer is: all of the above. A modern database should be a database, platform and be available on-premise or in the cloud.
SAP has a large installed-base of on premise ERP customers, and the HANA platform supports their needs, especially the need for an enterprise-class database. Many of those customers are looking for an on-premise database to replace the traditional RDBMS.
The demanding needs of an in-memory database mean that SAP elected to sell SAP HANA as an appliance, and it comes pre-packaged by the major hardware vendors as a result.
However the future of business is moving into the cloud, and SAP HANA is available as Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) with HANA Cloud Platform and Managed Cloud as a Service (McaaS) with secured HANA Enterprise Cloud and via 3rd party cloud vendors. Customers can also choose Hybrid deployment model that combines on premise and cloud. More details on this are available here.
How does SAP HANA compare to Oracle or IBM?
SAP HANA was designed to be a replacement to Oracle or IBM databases, either for net new installations or for existing customers. In most cases it is possible to move off those databases easily, and gain reporting performance benefits out of the box. Then it is possible to adapt the software to contain functions that were not possible in the past.
All three of the major RDBMS vendors have released in-memory add-ins to their databases in the last year. All of them support taking an additional copy of data in an in-memory cache, or in IBM’s case columnar tables. All of them provide improved performance for custom data-marts. But make no mistake; caching data has been around for a long time, while an in-memory database platform to run transactions and analytics together in the same instance is a new innovation.
Traditional database caching solutions are similar to the GM and Ford response to hybrid cars – take their existing technology and bolt new technology to it. SAP HANA is more akin to Tesla, who rebuilt the car from the ground up based on a new paradigm.
And so HANA’s capabilities from a business application perspective are 3 years ahead in technology from what others have.
~~~Contd.
Oct 8, 2014
SAP HANA Series - 3
HANA Overview - 3
What SAP Applications run on SAP HANA?
SAP CEO Bill McDermott said “HANA is attached to everything we have”.
Almost all the major SAP Applications now run on the SAP HANA platform. This includes the SAP Business Suite (ERP, CRM, PLM, SCM) and the SAP BW Data Warehouse.
The BI Suite including BusinessObjects Enterprise, Data Services and SAP Lumira are all designed to run on the HANA platform.
There are a set of Applications Powered by SAP HANA including SAP Accelerated Trade Promotion Planning, SAP Collection Insight, SAP Convergent Pricing Simulation, SAP Customer Engagement Intelligence, SAP Demand Signal Management, SAP Assurance and Compliance Software, SAP Liquidity Risk Management, SAP Operational Process Intelligence, and SAP Tax Declaration Framework for Brazil.
In addition, SAP runs much of its cloud portfolio on HANA, including the HANA Cloud Platform and SAP Business ByDesign. The Ariba and SuccessFactors apps are in the process of migration.
What’s the business case for SAP HANA?
We’ve built business cases for HANA deployments of all sizes and whilst they vary, there at a few common themes:
- TCO Reduction. In many cases HANA has a lower TCO. It reduces hardware renewal costs, frees up valuable enterprise storage and mainframes and requires much less maintenance
- Complexity to simplicity. HANA simplifies landscapes by using the same copy of data for multiple applications. Our implementations have shown that adding additional applications to a HANA dataset are very fast and easy, delivering business benefits quickly.
- Differentiation. HANA’s performance, advanced analytics (Predictive, Geospatial, Text analytics) and simplicity often mean a business process can be changed to be differentiating compared to competitors. Customer scenarios like loyalty management, personalized recommendations and anything where speed or advanced analytics capabilities are differentiating are all candidates.
- Risk Mitigation. Many customers know that in-memory technologies are changing the world and so want to put an application like SAP BW on HANA or LOB Datamarts as a first step, so they can react quickly for future business demands.
~~~Contd.
Oct 3, 2014
SAP HANA Series - 2
HANA Overview - 2
Where does SAP HANA come from?
SAP built SAP HANA from the ground up, including research from the Hasso Plattner Institute in Potsdam, the acquisition of the IP from the p*Time database, the TREX search engine, BWA in-memory appliance and MaxDB relational database. It has been extended with intellectual property from the Business Objects and Sybase acquisitions with products like Sybase IQ and Business Objects Data Federator.
Whilst HANA has a legacy and some code from other products, the bulk of the database and platform has been written from the ground up.
What makes SAP HANA fundamentally different?
SAP HANA is different by design. It stores all data in-memory, in columnar format and compressed. Because HANA is so fast, sums, indexes, materialized views and aggregates are not required, and this can reduce the database footprint by 95%. Everything is calculated on-demand, on the fly, in main memory. This makes it possible for companies to run OLTP and analytics applications on the same instance at the same time, and to allow for any type of real-time, ad hoc queries and analyses.
On top of this SAP built solutions to all the problems of columnar databases, like concurrency (HANA uses MVCC) and row-level insert and update performance (HANA uses various mechanisms like a delta store).
If this wasn’t enough SAP added a bunch of engines inside HANA to provide virtual OLAP functionality, data virtualization, text analysis, search, geospatial, graph (will be available soon) and web. It supports open standards like REST, JSON, ODBO, MDX, ODBC and JDBC. There is as much functionality in there as a whole Oracle or IBM software stack, in one database.
What kinds of use cases does SAP HANA support?
The first HANA deployments were all analytical use cases like Datamarts and Data Warehouses because the benefits are there right out the box. EDWs like SAP BW run like lightening with a simple database swap.
With a transactional application like Finance or Supply Chain, most things run a little better from a simple database swap (SAP claim 50% faster for their own core finance). The real benefits come when logic from the applications are optimized and pushed down to the database level, from simplification of the apps (SAP is building a simplified version of their Business Suite), or from ancillary benefits like real-time operational reporting, real-time supply chain management or real-time offer management.
Best of all, unlike the other database systems in the market, HANA supports all applications on the same instance of data at the same time. No more copying, transforming and re-organizing data all over the enterprise to meet the needs of different applications. HANA perfectly serves the needs of all applications with one “system of record” instance.
~~~ Contd.
Sep 30, 2014
SAP HANA - Series - 1
HANA Overview
What is SAP HANA?
SAP HANA is an in-memory database and application platform, which is for many operations 10-1000x faster than a regular database like Oracle on the same hardware. This allows simplification of design and operations, as well as real-time business applications. Customers can finally begin to reduce IT complexity by removing the need for separate and multiple Application Servers, Operational Data Stores, Datamarts and complex BI Tool implementations.
SAP HANA is a “reinvention” of the database, based on 30 years of technology improvements, research and development. It allows the build of applications that are not possible on traditional RDBMS, and the renewal of existing applications like the SAP Business Suite.
Why did SAP build a database?
SAP co-founder and Chairman Hasso Plattner believed that if a database could be built with a zero response time, that business applications would be written fundamentally differently – and IT landscapes could be simplified. The research institution at the Hasso Plattner Institution in Potsdam theorized that with modern computers and software design, this would be very nearly possible.
SAP makes business applications and since it was clear that none of the incumbent software vendors like Oracle would write such a database and application platform, they needed to build their own. In addition, this would be the springboard for a complete renewal and simplifying of SAP’s applications to take them through the next 20 years.
Is SAP HANA just a database?
No. When SAP went to build HANA, they realized that the next generation of business applications would require a much more integrated approach than in the past.
SAP HANA contains – out of the box – the building blocks for entire enterprise applications. HANA can take care of the requirements that would be served by many layers in other application platforms, including transactional databases, reporting databases, integration layers, search, predictive and web. All of this is served up working out the box, with a single installation.
~~~Contd.
Sep 22, 2014
Generic Delta Extraction using Function Module along with currency conversion in source system
Via Content in SCN
Business Scenario:
1. Need to have additional fields from Sales Partner Table (VBPA) for all Order Line Items in Sales Item Table (VBAP) for Sales Order Line Item Level Reporting. Standard data sources cannot be used as many info providers such as DSO's; Cubes are using them so it involves lots of effort in terms of time and money maintaining the same. Also this approach involves a lot of risk considering if anything gets deactivated during Transports.
2. In addition to this Curren cy Conversion has to be done in Source system as per client norms.
Note: It is recommended to do currency conversions in BW system.
R3 Side: In order to meet the above 2 requirements we decided to go for Generic Delta Extraction using Function Module. Need to make sure that Generic Extraction is Delta Based as Sales Order Items Table (VBAP) contains all the line item level information for Orders and its not easy extracting everything i.e. doing full update on daily basis and then maintaining the same in BW. Here, we shall be building a logic using AEDAT (Created on) and ERDAT (Changed on) of VBAP to extract the order Items getting changed/Created since the last BW Extrac tion.
Steps for Delta Enabled, Function Module Based Data source
INCLUDE LZRSAX_TESTTOP.
* Structure for the Cursor - extraction of data
TYPES: BEGIN OF ty_vbap,
Vbeln TYPE vbeln,
Posnr TYPE posnr,
Netwr TYPE netwr,
Waerk TYPE waerk,
Dltdate TYPE dats,
END OF ty_vbap.
< p>* Structure for VBPA to extract PERNR
1. Need to have additional fields from Sales Partner Table (VBPA) for all Order Line Items in Sales Item Table (VBAP) for Sales Order Line Item Level Reporting. Standard data sources cannot be used as many info providers such as DSO's; Cubes are using them so it involves lots of effort in terms of time and money maintaining the same. Also this approach involves a lot of risk considering if anything gets deactivated during Transports.
2. In addition to this Curren cy Conversion has to be done in Source system as per client norms.
Note: It is recommended to do currency conversions in BW system.
1. Create an Extract structure including DLTDATE field in addition to all other required fields. DLTDATE would be used to build the logic to extract the delta using AEDAT and ERDAT.
Reason for Addition of a DLTDATE Field in Extract Structure
While configuring delta in the RSO2 screen, the field on which the delta is requested must be a field present in the extract structure. To allow the extractor to provide delta on timestamp, there must be a timestamp field in the extract structure. Hence the timestamp field is added here – it is merely a dummy field created to allow us to use the extractor for delta purposes, as will become clear later.
2. Copy the Function group RSAX from SE80, give new function group as ZRSAX_TEST.
3. Copy function module. Deselect all and then select only RSAX_BIW_GET_DATA_SIMPLE name it as ZBW_FUNCTION.
4. Go to the Include folder and double-click on LZRSAX_TESTTOP define the structure and Field symbol and internal table as below.
TYPES: BEGIN OF ty_vbap,
Vbeln TYPE vbeln,
Posnr TYPE posnr,
Netwr TYPE netwr,
Waerk TYPE waerk,
Dltdate TYPE dats,
END OF ty_vbap.
Vbeln TYPE vbeln,
Posnr TYPE posnr,
Pernr TYPE pernr,
END OF ty_vbap.
TYPES: BEGIN OF ty_ord_f inal,
Vbeln TYPE vbeln,
Posnr TYPE posnr,
Pernr TYPE pernr,
Dltdate TYPE datum,
Netwr TYPE netwr,
Waerk TYPE waerk,
Netwr_loc_val TYPE WERTV8,
Loc_curr TYPE waers,
Netwr_rep_val TYPE WERTV8,
Rep_curr TYPE waers,
END OF ty_ord_final.
DATA: t_vbap TYPE STANDARD TABLE OF ty_vbap,
t_vbpa TYPE STANDARD TABLE OF ty_vbpa.
DATA: wa_vbap TYPE ty_vbap,
wa_vbpa TYPE ty_vbpa.
DATA: lv_bukrs TYPE bukrs,
lv_vkorg TYPE vkorg,
lv_waers TYPE waers,
lv_prsdt TYPE prsdt,
lv_netwr TYPE netwr.
DATA: save_ukurs LIKE tcurr-ukurs,
save_kurst LIKE tcurr-kurst,
save_ukurx(8) TYPE p,
save_ffact1 LIKE tcurr-ffact,
save_tfact LIKE tcurr-tfact,
save_ffact LIKE tcurr-ffact,
save_ukurs1(11) TYPE p DECIMALS 5.
FIELD-SYMBOLS: <i_fs_order_item> LIKE LINE OF t_vbap.
DATA: l_s_select TYPE srsc_s_select.
STATICS: s_s_if TYPE srsc_s_if_simple,
s_counter_datapakid LIKE sy-tabix,
s_cursor TYPE cursor.
RANGES: l_r_vbeln FOR vbap-vbeln, "DOC
l_r_posnr FOR vbap-posnr, "ITEM
i_r_dltdate FOR vbap-erdat. "DELTA DATE
* Initialization mode (first call by SAPI) or data transfer mode
* (following calls)?
IF i_initflag = sbiwa_c_flag_on.
* Initialization: check input parameters
* buffer input parameters
* prepare data selection
************************************************************************
CASE i_dsource.
WHEN 'ZBW_DS_TEST'.
WHEN OTHERS.
IF 1 = 2. MESSAGE e009 (r3). ENDIF.
* This is a typical log call. Please write every error message like this
log_write 'E' "message type
'R3' "message class
'009' "message number
i_dsource "message variable 1
' '. "message variable 2
RAISE error_passed_to_mess_handler.
ENDCASE.
s_s_if-requnr = i_requnr.
s_s_if-dsource = i_dsource.
s_s_if-maxsize = i_maxsize.
* (in case that there is no 1:1 relation between InfoSource fields
* and database table fields this may be far from beeing trivial)
APPEND LINES OF i_t_fields TO s_s_if-t_fields.
* Data transfer: First Call OPEN CURSOR + FETCH
* Following Calls FETCH only
************************************************************************
IF s_counter_datapakid = 0.
* of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.
LOOP AT s_s_if-t_select INTO l_s_select.
CASE l_s_select-fieldnm.
WHEN 'VBELN'.
ls_vbeln-sign = ls_select-sign.
ls_vbeln-option = ls_select-option.
ls_vbeln-low = ls_select-low.
ls_vbeln-high = ls_select-high.
APPEND l_r_vbeln.
WHEN 'POSNR'.
ls_posnr-sign = ls_select-sign.
ls_posnr-option = ls_select-option.
ls_posnr-low = ls_select-low.
ls_posnr-high = ls_select-high.
APPEND l_r__posnr.
WHEN 'DLTDATE'.
ls_delta_date-sign = ls_select-sign.
ls_delta_date-option = ls_select-option.
ls_delta_date-low = ls_select-low.
ls_delta_date-high = ls_select-high.
APPEND l_r_dltdate.
ENDCASE.
ENDLOOP.
* from input parameter I_MAXSIZE. If there is a one to one relation
* between DataSource table lines and database entries, this is trivial.
* In other cases, it may be impossible and some estimated value has to
* be determined.
itm~netwr AS netwr itm~waerk AS waerk itm~aedat
AS dltdate FROM vbap AS itm
WHERE itm~vbeln IN ls_vbeln
AND itm~posnr IN ls_posnr
AND ( ( itm~aedat EQ '00000000'
AND itm~erdat IN ls_dltdate )
OR ( itm~aedat NE '00000000'
AND itm~aedat IN ls_dltdate ) ).
ENDIF. "First data package ?
* named E_T_'Name of extract structure'.
FETCH NEXT CURSOR s_cursor
APPENDING CORRESPONDING FIELDS
OF TABLE t_vbap
PACKAGE SIZE s_s_if-maxsize.
IF sy-subrc <> 0.
CLOSE CURSOR ls_cur sor.
RAISE no_more_data.
ENDIF..
* Loop at it_vbap to build the final table
CLEAR: t_final, lv_vkorg,lv_bukrs,lv_waers,lv_prsdt.
MOVE: <i_fs_order_item>-vbeln TO t_final-vbeln,
<i_fs_order_item>-posnr TO t_final-posnr,
<i_fs_order_item>-netwr TO t_final-netwr,
<i_fs_order_item>-waerk TO t_final-waerk,
<i_fs_order_item>-dldat TO t_final-dltdate.
WHERE vbeln = <i_fs_order_item>-vbeln
AND posnr = '000000'
AND parvw = 'ZM'.
IF sy-subrc NE 0.
t_final-pernr = space.
ENDIF.
SELECT SINGLE vkorg FROM vbak INTO lv_vkorg
WHERE vbeln = <i_fs_order_item>-vbeln.
IF sy-subrc = 0.
* Select the company code based on sales org
SELECT SINGLE bukrs FROM tvko INTO lv_bukrs
WHERE vkorg = lv_vkorg.
IF sy-subrc = 0.
* Select the local currency based on company code
SELECT SINGLE waers FROM t001 INTO lv_waers
WHERE bukrs = lv_bukrs.
IF sy-subrc = 0.
t_final-loc_curr = lv_waers.
t_final-rep_curr = 'USD'.
ENDIF.
SELECT SINGLE prsdt FROM vbkd INTO lv_prsdt
WHERE vbeln = <i_fs_order_item>-vbeln
AND posnr = & lt;i_fs_order_item>-posnr.
IF sy-subrc NE 0.
SELECT SINGLE erdat FROM vbap INTO lv_prsdt
WHERE vbeln = <i_fs_order_item>-vbeln
AND posnr = <i_fs_order_item>-posnr.
ENDIF.
IF
EXPORTING
date = lv_prsdt
foreign_amount = 1
foreign_currency = <i_fs_order_item>-waerk
local_currency = lv_waers
type_of_rate = 'M'
IMPORTING
exchange_rate = save_ukurs
foreign_factor = save_ffact
local_amount = save_tfact
local_factor = save_ffact1
exchange_ratex = save_ukurx
derived_rate_type = save_kurst
EXCEPTIONS
no_rate_found = 1
overflow = 2
no_factors_found = 3
no_spread_found = 4
derived_2_times = 5.
IF sy-subrc = 0.
save_ukurs1 = save_ukurs / save_ffact.
IF save_ffact1 NE 0 .
save_ukurs1 = save_ukurs1 * save_ffact1.
ENDIF.
lv_netwr = <i_fs_order_item>-net wr * save_ukurs1.
t_final-netwr_loc_val = lv_netwr.
ENDIF.
ELSE.
t_final-netwr_loc_val = <i_fs_order_item>-netwr.
ENDIF.
IF lv_waers NE 'USD' .
* Convert the currency in the reporting currency
CLEAR : save_ukurs1,lv_netwr,save_ukurs,
save_ffact,save_tfact,save_ffact1,
save_ukurx,save_kurst.
EXPORTING
date = lv_prsdt
foreign_amount = 1
foreign_currency = lv_waers
local_currency = 'USD'< /span>
type_of_rate = 'M'
IMPORTING
exchange_rate = save_ukurs
foreign_factor = save_ffact
local_amount = save_tfact
local_factor = save_ffact1
exchange_ratex = save_ukurx
derived_rate_type = save_kurst
EXCEPTIONS
no_rate_found = 1
overflow = 2
no_factors_found = 3
no_spread_found = 4
derived_2_times = 5.
IF sy-subrc = 0.
save_ukurs1 = save_ukurs / save_ffact.
IF save_ffact1 NE 0 .
save_ukurs1 = save_ukurs1 * save_ffact1.
ENDIF.
lv_netwr = t_final-netwr_loc_val * save_ukurs1.
t_final-netwr_rep_val = lv_netwr.
ENDIF.
ELSE.
t_final-netwr_rep_val = t_final-netwr_loc_val.
ENDIF.
ENDIF.
ENDIF.
APPEND t_final TO e_t_data.
ENDLOOP.
s_counter_datapakid = s_counter_datapakid + 1.
ENDIF. "Initialization mode or data extraction ?
l_r_posnr FOR vbap-posnr, "ITEM
l_r_delta_date FOR vbap-erdat. "DELTA DATE
CASE l_s_select-fieldnm.
WHEN 'VBELN'.
ls_vbeln-sign = ls_select-sign.
ls_vbeln-option = ls_select-option.
ls_vbeln-low = ls_select-low.
ls_vbeln-high = ls_select-high.
APPEND l_r_vbeln.
itm~netwr AS netwr itm~waerk AS waerk itm~aedat
AS delta_date FROM vbap AS itm
WHERE itm~vbeln IN ls_vbeln
AND itm~posnr IN ls_posnr
AND ( ( itm~aedat EQ '00000000'
AND itm~erdat IN ls_delta_date )
OR ( itm~aedat NE '00000000'
AND itm~aedat IN ls_delta_date ) ).
1. ( ( itm~aedat EQ '00000000' AND itm~erdat IN ls_delta_date ) – For New records
2. ( ( itm~aedat NE '00000000' AND itm~aedat IN ls_delta_date ) ) – For Changed records.
WHERE vbeln = <i_fs_order_item>-vbeln
AND posnr = '000000'
AND parvw = 'ZM'.
IF sy-subrc NE 0.
t_final-pernr = space.
ENDIF.
2. Local Currency: The Company Code Currency is called Local Currency. It is stored at Compan y Code level in T001 Table.
3. Reporting Currency: In our case it is fixed as 'USD'.
Logic to get Local Currency:
SELECT SINGLE vkorg FROM vbak INTO lv_vkorg
WHERE vbeln = <i_fs_order_item>-vbeln.
SELECT SINGLE bukrs FROM tvko INTO lv_bukrs
WHERE vkorg = lv_vkorg.
SELECT SINGLE waers FROM t001 INTO lv_waers
WHERE bukrs = lv_bukrs.
Date – PRSDT "Pricing Date". Need to fetch it for doing conversions.
From Currency - already fetched above.
To Currency – already fetched above.
Type of Conversion: Fix ed as 'M' in our case
WHERE vbeln = <i_fs_order_item>-vbeln
AND posnr = <i_fs_order_item>-posnr.
Finally we are passing everything to the Function Module to have the Conversion done to Local Currency first and later to Reporting Currency.
2. Fill in the various Details including the Function module and Structure name.
Note: We could have selected Calend. Day but in that case the delta extraction can only be done once in a day.
Note that the DLTDATE field is disabled for selection; this is because this will be populated automatically as part of the delta. As a result, it will be unavailable for manual entry in the Info package or in RSA3
Following this step, create the corresponding ODS, Data source etc. in BW side and replicate. These steps are similar to what would be done for a normal generic data source.
Later this ODS active table is read to have these additional fields in BW Old Flow.
Subscribe to:
Posts (Atom)