Recently I worked on a project where we upgraded our BEx components from 3.x to 7x an encountered numerous issues, In this technical blog I have listed one of the interesting issue related to BEx Analyzer limitation
Part of BEx Upgrade project BW workbooks when upgraded from BEx 3.x to 7x started experiencing issues for having huge data set (1 Million data cells and beyond). Business users were executing workbooks which were either stored on local machine OR from the server having 10,000+ Rows & 90+ Columns, upon execution they got a popup “Client Out of Memory” .
This phenomenon has been described in SAP note 1411545 & 1040454. Understood from SAP that there is a design limitation for 7x analyzer (0.75 Million data cells) because BEX 7.X leverages MS .NET framework having this memory limits.
As per note number of cells that can be displayed as defined by SAP is 750,000 OR 1.2GB but the our workbooks had 1+million data cells. Calculation of the data cells is as per allocated memory MB = 100 + ( (# Rows * # Columns) * 0.0016)
Only the result set in the workbook should be considered for Number of Rows and Columns in above calculation
1. Cut down data set size by including additional filters to display the data, so that number of rows and columns gets reduced. SAP says “Anything extracted beyond ~.5M cells is considered as ETL requirement. BEx is a reporting and analytical tool and this should not be used for data extraction as an ETL tool.”
2. If your result set has more number of columns due to Drill Across fields for all key figures/measures then change the way how reports are executed by rearranging the drills down/across so that the number of columns get reduced as we have no control on the number of rows.
3. Every workbooks will have corresponding query associated with it. If in your case workbooks have only one query in the back end then as k the users to access corresponding report from either 3.x OR 7x analyzer instead of workbooks as queries have separate memory restriction which can be extended in RSADMIN.
With this workaround you can instantly run in excel and you also have refresh functions as in workbooks but the look and feel might be slightly different
4. If in your case the workbooks can be accessed both 3.x and 7x then you can ask the users to open 3x analyzer and access the migrated workbooks as 3.x workbooks dont have .Net limitation
5. Execute the report in Web and download to CSV as query can handle huge data set, presuming you maintain appropriate settings in RSADMIN
6. Execute the report in smaller chunks using a custom ABAP program and then merge the files together
All the above workarounds will be limited until SAP BW/BI version 7.30 and presuming you enable both 3.x and 7x front end tools.
I also performed research on couple of other BI tools (Hadoop, Qlikview, Tableau..) Some of them dont have the restriction as in Analyzer but most of them are meant for small data packets and not for huge data volumes.
One that can be a potential alternative is SAP Business Objects Analysis for Office Edition, A premium alternative to Bex Analyzer. When I googled found combination of OS 64 Bit + Excel 64 Bit + Analysis for office 64 Bit can handle huge data set but there are mixed reviews about this tool.
Relevant SAP Notes
1411545 & 1040454
1973478 – BEx Analyzer: Safety Belt option for large Query Results
1411545 – BExAnalyzer: safety belt for large resultsets
1860872 – Report not executable in BEx Analyzer 7x / Client out of Memory
2061104 – BEx Analyzer: Throws out of memory exception when there are many data providers and hence the size of the XML is very large.
2041337 – BExAnalyzer: Performance in Serialization of Structure Members and Cells
1958613 – Optimizing the BEx Analyzer Performance – Known Corrections and Best Practice Guidelines
1466118 – Hardware & Software requirements for Analysis, edition for MS Office