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


Problem Statement


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

Alternative Approach/Workarounds


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


Thanks

Abhishek Shanbhogue

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

      1. Abhishek Shanbhogue Post author

        Syam – I spoke to people who are experts in other BI platforms and understood from them that BO/Qlikview/Tableau are mostly used as data visualization tool and not for huge data extraction, Also they might not have same features as we have in BEx.

        For Tableau we will have to be SAP BW700 SP20 or above without which we may encounter compatibility issues and this is the same with others also

        Thanks

        Abhishek Shanbhogue

        (0) 
  1. Charlie Belt

    A good analysis. One of the telling problems that most of us have in the BW development environment is that end users think that BW is a ‘reporting’ tool and capable (or even recommended) for large data set extraction. We have found that counseling our users that BW is an analytic tool, designed to optimally produce small sets of data for ‘results’ rather than a data extraction tool for further analytic tool use.

    Too many users are dependent on their personal analytic tools and use BW as a front end for them. BW is the wrong tool for that purpose. Teach ad hoc query development and ad hoc Bex navigation instead.

    (0) 
  2. vaneet arora

    @ Abhikshek –

    Could you please re direct me to SAP official document where it suggests that “Anything extracted beyond ~.5M cells is considered as ETL requirement.

    I need it very urgently to show it to client .

    Also , is there any patch to overcome 1 million limit in Bex .

    (0) 

Leave a Reply