Real-time reporting is one of the important issues for an organization, especially, online reporting on very sensitive data, for example sales and distributions etc.
There are several standard options in SAP BW for online (real time or almost real time) reporting.
In the first part of the article I will give a briefly explanation of some standard methods, like RDA or Virtual Cube, pointing on disadvantages of these methods.
In the second part of the article I will show another, combined, method which I use for some online reports.
RDA and Virtual Cube
There are two general ways, the most popular, for online/near online reporting:
Beside the main advantage of these methods, which is online reporting, there are a lot of disadvantages.
I guess that these disadvantages, which will be described later, may "kill" the whole online concept and, actually, have more weight on possible advantages of the methods.
RDA
First, actually, this is not really real time, but almost real time (RDA daemon might have minimum one minute interval).
In addition, small time intervals between loads may lead (and surely will lead) to overlapping loads.
Therefore loading using RDA with one minute interval in "hot" periods (the end of a quarter) does not seem to be working.
Virtual Cube
One of the main problems of using Virtual Cube is extremely poor performance. I guess most of ABAP and BASIS professionals won't accept an idea of performing direct RFC SELECT from VBAK (for example).
Such model might work on small volume of a data, but RDA as well as standard ETL processes work fine with small volume of data.
In addition, writing complex and performance optimized ABAP code for Virtual Cube based on function modules requires strong ABAP skills.
In conclusion, I guess implementing online reporting model on big volume of a data, sales orders for example, is very complex and even impossible using RDA or Virtual Cube.
Combined Delta Method
I n the next part of the article I would like to suggest another, combined method for online reporting.
Suppose we have the next scenario:
But what if I will bring just deltas from such delta queue using SAP standard function module without resetting delta queue and finally summarize the deltas with the existing records in a cube/DSO?
For example, sales order 123 has booked value 100 EUR and it was loaded to BW at 12:00 using standard hourly ETL.
At 12:15 sales order was updated to 120 EUR.
In this case we have an order with 100 EUR in BW and delta +20 in delta queue (till the next hourly loa d at 13:00).
If I would retrieve such delta value (+20), instead of retrieving whole data from VBAK or running RDA daemon each minute, and add it to the existing value (100) just at the report level, on demand, so my report give me the most updated value - 120 EUR.
In this case we won't use a complex ABAP code or retrieving a data from huge tables such as VBAK.
In addition, we won't use RDA mechanism, but still getting online values - really online.
Last, but not the least, 2LIS_11_VAITM extractor does not support RDA without adjusting the extractor. In most organizations SD is very sensitive area, and getting an approval of principal level for changing such important extractor won't be easy mission.
Implementation
First of all, I assume that you have a cube (standard or not) which has been loaded every hour with 2LIS_11_VAITM extractor.
The first step is creating the function module for online extractor.
Such function module should use SAP standard function module for reading delta queue RSFH_GET_DATA_SIMPLE with the following parameters (I mention here only an important part of the parameters): i_osource = '2LIS_11_VAITM' and i_updmode = 'D'.
For detailed implementation of the function module based extractor you suppose to turn to your ABAP team.
The extractor fetches those records which are currently in delta queue.
The next step is creating the cube which has a similar structure as a main cube loaded each hour.
This cube is a virtual cube based on DTP for direct access with an Info Source 3.X.
Finally we create a Multi Provider based on these 2 cubes (hourly and virtual).
A report built on such Multi Provider will show online data.
Conclusion
I've shown several standard options in SAP BW for online (real time or almost real time) reporting such as RDA and Virtual Cube.
Each of the options separately has very serious disadvantages such as RDA may lead to overlapping loads and Virtual Cube may have a performance troubles.
I've suggested another, combined method - using standard hourly loads and, on other hand, adding the records which are in delta queue fetched by SAP standard function module.
The result is online report with a minimal impact on performance, with very simple extractor based on function module.
There are several standard options in SAP BW for online (real time or almost real time) reporting.
In the first part of the article I will give a briefly explanation of some standard methods, like RDA or Virtual Cube, pointing on disadvantages of these methods.
In the second part of the article I will show another, combined, method which I use for some online reports.
There are two general ways, the most popular, for online/near online reporting:
- RDA (Real Data Acquisition)
- Virtual Cube
Beside the main advantage of these methods, which is online reporting, there are a lot of disadvantages.
I guess that these disadvantages, which will be described later, may "kill" the whole online concept and, actually, have more weight on possible advantages of the methods.
In addition, small time intervals between loads may lead (and surely will lead) to overlapping loads.
Therefore loading using RDA with one minute interval in "hot" periods (the end of a quarter) does not seem to be working.
Such model might work on small volume of a data, but RDA as well as standard ETL processes work fine with small volume of data.
In addition, writing complex and performance optimized ABAP code for Virtual Cube based on function modules requires strong ABAP skills.
I n the next part of the article I would like to suggest another, combined method for online reporting.
Suppose we have the next scenario:
- There is some standard ETL of sales order items (2LIS_11_VAITM standard extractor) which runs hourly. Each load brings about 5000 lines approx).
- Between hourly loads the data (delta) is collected in the delta queue in ECC.
But what if I will bring just deltas from such delta queue using SAP standard function module without resetting delta queue and finally summarize the deltas with the existing records in a cube/DSO?
For example, sales order 123 has booked value 100 EUR and it was loaded to BW at 12:00 using standard hourly ETL.
At 12:15 sales order was updated to 120 EUR.
In this case we have an order with 100 EUR in BW and delta +20 in delta queue (till the next hourly loa d at 13:00).
If I would retrieve such delta value (+20), instead of retrieving whole data from VBAK or running RDA daemon each minute, and add it to the existing value (100) just at the report level, on demand, so my report give me the most updated value - 120 EUR.
In this case we won't use a complex ABAP code or retrieving a data from huge tables such as VBAK.
In addition, we won't use RDA mechanism, but still getting online values - really online.
Last, but not the least, 2LIS_11_VAITM extractor does not support RDA without adjusting the extractor. In most organizations SD is very sensitive area, and getting an approval of principal level for changing such important extractor won't be easy mission.
The first step is creating the function module for online extractor.
Such function module should use SAP standard function module for reading delta queue RSFH_GET_DATA_SIMPLE with the following parameters (I mention here only an important part of the parameters): i_osource = '2LIS_11_VAITM' and i_updmode = 'D'.
For detailed implementation of the function module based extractor you suppose to turn to your ABAP team.
The extractor fetches those records which are currently in delta queue.
The next step is creating the cube which has a similar structure as a main cube loaded each hour.
This cube is a virtual cube based on DTP for direct access with an Info Source 3.X.
Finally we create a Multi Provider based on these 2 cubes (hourly and virtual).
A report built on such Multi Provider will show online data.
Each of the options separately has very serious disadvantages such as RDA may lead to overlapping loads and Virtual Cube may have a performance troubles.
I've suggested another, combined method - using standard hourly loads and, on other hand, adding the records which are in delta queue fetched by SAP standard function module.
The result is online report with a minimal impact on performance, with very simple extractor based on function module.
No comments:
Post a Comment