Only 64k rows? I used to hear an requirement from my customers regarding BW report’s length very often. There are several reasons why BW reports should not be that long but usually customers resist in it and simply they want to see all rows that report can produce. Therefore from this viewpoint a limitation in MS Excel 2003 (and its predecessors) of displaying in particular 65.536 rows is seen as obstacle. Once new MS Excel 2007 came to the market (in 2007) it was assumable that since this limit is overcame up to 1,048,576 rows it has impact on BW reporting as well. However if you see e.g. on SDN what people are saying regarding this limitation; there are still doubts how many rows can be displayed. Those can be eye witnessed e.g. here, here or here. At the time I’m was researching what’s the matter really is I came into the conclusion that only with BW 7.x limitation of 65k rows is overcome. Unfortunately I was wrong. To be really sure I did few test which I’m going to introduced further in this blog post.
I prepared scenario with one very simple BW report without any selection screen based on demo cube ZD_SALES. I filled this cube with approximately 100k of records. I placed this cube and whole its data flow into BW based on version 7.0. I used SAP GUI version 7.2 and MS Excel 2007 together with whole Office 2007 installed on my laptop. Test query was comprised with just 4 columns. To be really sure that there is no aggregation in place Document Number was always unique in my test data loaded into cube.
As a first trial; I relied on transaction RSRT and I ran my report in Query Display mode = List. In this mode on MS Excel formatting is in not place so we can assume that we get all of data out of cube in our report. As seen on screenshot we have also total lines displayed. Therefore all rows from cube are displayed.
Later I continued with BEx Analyzer as Add-In in MS Excel 2007. From the very top; report looked good. All the columns were displayed. I started to be eager that time just to see all 100k in my Excel…
But surprise! The report suddenly stops at (and surprise again) 65.536th row. :(
Even I’m running BEx Analyzer based on the newest SAP GUI 7.2, even I’m using BW 7.0 as backend even my MS Excel is version of 2010 I’m still not able to get more than 65k records.
What to do next? I tried to find some information on OSS. There are notes like 1411545 - BExAnalyzer: safety belt for large result sets and 1499986 - BExAnalyzer: safety belt default value does not work which suggest to set parameter called ANALYZER_LIMIT_DEF into table RSADMIN. However after setting it my report got me only 65k records.
Hmm, ok, again no luck. What about trying to google it up? Finally there is OSS’s wiki post bringing some light on this issue. It says that limitation of 65k is due to the functionality which is exporting the data from SAP systems in general. It is called XXL Export and it cares of generic export of any list data object into the MS Excel. Also is written here that this functionality is not planned to be changed. Ok, game seemed to be over for me that time. Moreover I found interesting discussion in this SDN forum post. User posted reply from SAP OSS related to this. It is stated the same as in wiki post: “The excel file can have maximum 65536 rows. You cannot select more than this amount of rows using excel.” SAP is explaining within this that there are practical reasons for this limit. They stressing out point that BEx is not a tool of mass data extraction etc.
Game over, this time for real. But what would be the other option how to get avoid this limitation. At the moment following options came to my mind:
1. Usage of web reporting for export of more than 65k rows. According the note: 1127156 - Safety belt: Result set is too large, in BW version 7.x it I possible to set parameters that would allow such a massive export of data.
2. Functionality of OpenHubs. Data can be placed directly into the files (e.g. in CSV format) on application server. Be careful here OpenHubs are specially licensed and by using it there are additional fees.
3. Coding of custom ABAP report which runs BW query and gets all its output and saves it in the flat files.
I completely realize that those options are not based on BEx Query Analyzer nor even in MS Excel but I cannot see any other option at the moment.
2 comments:
you can also use transaction RSCRM_BAPI to extract the query into table or .csv file on filesystem.
You don't need to then create Open Hub and fields and it works with multiprivders as well where OH don't.
Worked fine for me :-)
Regards,
Pavol Feranec
There is no limitation for excel, limitation is of .net frame work, which can handle only 2.2GB data which is approximately 750k filled cells of excel. If your query contains more than this size or no.of filled cells, you will get CLIENT OUT OF MEMORY error.
Post a Comment