Skip to Content
Technical Articles
Author's profile photo Mohamed EZZAT

BEx 3.x/7.x to AfO 2.x Automated Mass Conversion Tool v2.2

Introduction

Hello everyone, I am back again with a very interesting topic, the Automated Mass Conversion Tool v2.2, it will be my pleasure to walk you through the newest features that I added to the previous version that was firstly introduced by my colleague Bhargav Malsani in this blog.

What is the Mass Conversion Tool?

The Mass Conversion Tool was internally developed by SAP to accelerate and facilitate the conversion process from BEx (Analyzer) Workbooks to Analysis for Microsoft Office Workbooks.

The Automated Mass Conversion Tool is 6x to 8x faster than the manual conversion method, conversion of 100 Workbooks take around 5-6 Minutes, compared to at least 30 seconds per one workbook in case of manual conversion.

This is a screenshot for a real run on 100 Workbooks that were converted in 5:44 minutes, after successfully setting up the tool on the customer’s environment:

 

When is the conversion process mandatory?

  • If you are migrating to BW/4HANA, as BEx Analyzer will not be able to access the BW/4HANA system.
  • If you are upgrading your Microsoft Office landscape to 64bit Edition, as BEx Analyzer only runs on Excel 32bit Edition

More information about the maintenance strategy for BW BEx Explorer can be found in note number 1322923.

New Features:

Rename workbooks after conversion

Standardize the naming convention of the converted workbooks, by simply providing a CSV file containing the Workbook ID, Destination Role ID, and New Workbook ID.

WID Role WBName
3FAFDUZNSGVNIHLO63YTUX9HA ZE_ROLE1 AFOWB_1
3VYKQU1PO9DKICADNOPQN0G70 ZE_ROLE2 AFOWB_2
1JW9S8WCDXYZ277J3NDNAVHH8 ZE_ROLE3 AFOWB_3

Workbook%20IDs%20after%20Conversion

     Workbook IDs after Conversion

Converted workbooks can now be opened using 64bit Excel:

This was a limitation even in the manual conversion method, when a workbook is converted the leftover code from BEx Analyzer remains, in order to surpass it, the 3 default VBA modules must be manually deleted, or “Declare PtrSafe” must be written before each Sub.

In the new version of the tool, you do not have to worry about that, you are just one checkbox away from solving it.

Saving to multiple different roles:

This feature allows the converted workbooks to be saved to multiple different workbooks, they can be their original roles or entirely new ones.

For example WB1 to Role1, WB2 to Role2, and WB3 to WB3, a csv can be provided to identify the workbook technical ID and destination role, as shown below:

WID Role
3FAFDUZNSGVNIHLO63YTUX9HA ZE_ROLE1
3VYKQU1PO9DKICADNOPQN0G70 ZE_ROLE2
1JW9S8WCDXYZ277J3NDNAVHH8 ZE_ROLE3

Workbook branding options:

Gain advantage of the conversion service by rebranding or standardizing the layout of your old workbooks, the following options are now available to be applied to the converted workbooks:

A screenshot for a workbook before conversion:

The workbook had 2 images, the filter and information buttons were clicked, there was no descriptive title, there were the Author and Status of Data info fields in the range below the title.

This is the same workbook after conversion using the options in Advanced Conversion Settings in the first screenshot:

  • The images were removed and replaced by SAP logo and the orange line.
  • The 3 buttons from Analyzer were removed, whether they were opened or closed.
  • A descriptive title was captured from the workbook description, centralized and added in merged cells.
  • The whole workbook was colored white.
  • The old info field was removed and replaced by Query Created By, Data Source Name, Last Date Update and Key Date were added, using a different font for the title and the value.
  • Rows between 1:4 were deleted.
  • Rows between 1:5 were inserted to be populated by the info fields.

Note:

  • The table styling cannot be applied in this version, it will always reset to the AfO default blue theme.
  • Workbook styling will be applied to the main sheet, not all of them, this is a current limitation that I will be working on.

Conclusion:

The new version is not only about conversion, it offers an opportunity to:

  • Make all of the converted workbooks 64bit Compatible.
  • Redistribution of the workbooks among different roles.
  • Standardize workbooks format or entirely change the design.
  • Standardize the naming convention for the converted workbooks.

This service is available for both Max Attention(PE) and PS customers, if you would like to request it or find out more about it please contact your Engagement Architect, Technical Quality Manager, Account Manager or you can reach out to the contacts below.

This blog post will be updated whenever a new feature is rolled out.

Lastly I would like to give well deserved credit to my colleague Mike Luang-Khot who played a major role in the development of the first version of the tool, and also played a crucial part in this version by thoroughly explaining the tool’s primary code to me, without his help these features would not have been possible.

CoE EMEA Contacts:

 

Mohamed EZZAT Egypt CoE, Analytics, Customer Success

Carsten Egner  Germany CoE, Analytics, Customer Success

 

CoE North America Contacts:

 

Mandanna Mandechanda Uthappa CoE Analytics, Customer Success

Selvakumar Sithavan CoE Analytics, Customer Success

 

CoE Ireland Contacts:

 

Muralikrishnan Ethiraj CoE Analytics, Customer Success

Ram Ravi Srinivas CoE Analytics, Customer Success

 

Thank you for reading this, I hope it was interesting and comprehensive enough, please do not hesitate to ask any questions, provide feedback, or suggest any further enhancements.

Assigned tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Karsten RUF
      Karsten RUF

      Great stuff! I'm super happy to see this - and that you're working on even more improvements.

      Keep up the good work!

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Thank you very much for your feedback Karsten, I am really glad you liked the improvements.

       

      Author's profile photo Manoj Kumar
      Manoj Kumar

      Isn’t mass migration tool was developed by CoE US team?US COE team is also working on bex to AO conversion projects . Great to know that tool is further updated with new features.thanks.

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Manoj,

      I mentioned the original blog for the tool, thank you for your feedback.

      Author's profile photo Mostafa EL BARBARY
      Mostafa EL BARBARY

      Great Blog and rich information , please keep it up!

       

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Thanks for your support Mostafa!

      Author's profile photo Moataz ABU KARAT
      Moataz ABU KARAT

      Very nicely structured and visualized with screenshots and very informative. Keep up the good work!

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Thank you for the feedback Moataz, glad you liked it, much appreciated!

      Author's profile photo Babaakbar Pulatipalli
      Babaakbar Pulatipalli

      Hi Mohamed.

       

      Good Article ,with screenshots.  Do we need to buy the tool separately , any pros and cons like all the bex variables will automatically converted. and for the Workbooks if there are macros those all will be migrated correctly.

      In my current project we are having 1200 reports and 500 WB.

       

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Babaakbar,

      Thank you for your feedback, as for your questions, no you need to buy the service only, the tool is not sold.

      If by the BEx variables you are referring to the query variables, they will be there and automatically converted.

      Finally, for the macros, the code will still be there, in some cases the code needs to be reassigned to its relevant object, also if you are planning to consume the workbooks with 64bit Excel, you need to make sure that the code is compatible with it, this can be easily tested out if you do a manual conversion for one of your workbooks and check the result, in case of code compatibility issues you can refer to this link: https://docs.microsoft.com/en-us/office/client-developer/shared/compatibility-between-the-32-bit-and-64-bit-versions-of-office

      If you would like to find out more about how you can request the service, in case you were a Max Attention customer, you can contact your TQM, if not please reach out to your SAP account Manager.

       

      I hope this answers your questions.

      Author's profile photo Jan-Christoph Ahlers
      Jan-Christoph Ahlers

      Hello Mohamed,

      would it be possible to do an automated mass conversation from the BW platform to the BI platform?

      Best regards

      Jan

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Jan,

      Yes it is possible, however, currently there is a limitation that should be considered, the connection will remain directly to BW, which means that only the user that have the system in their SAP GUI will be able to access them, it doesn't get switched to an OLAP connection from BIP.

      Best Regards,

      Ezzat

      Author's profile photo Lukasz Krys
      Lukasz Krys

      HI,

      ​How can I get this tool?

      Is it available to download from SAP Support Portal?

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Lukasz,

      No, this is an internal tool that is only used by SAP Center of Expertise teams.

      You can request the conversion process as a service, which is available if you are a premium engagement customer or a professional services customer, please contact your Engagement Architect, Technical Quality Manager, or Account Manager, they will take it forward to us from there.

      Author's profile photo Krzysztof Korościk
      Krzysztof Korościk

      Hi Mohamed,

       

      This looks very promising! Is it possible for SAP consultants to get access to this tool to try and test it out?

      If yes, who should I contact ?

       

      Kind regards,

      Chris

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Chris,

       

      Thank you for your feedback. Unfortunately no, this tool is only available for SAP CoE employees, it's not available to partners or customers.

       

      Best Regards,

      Ezzat.

      Author's profile photo shailender dubey
      shailender dubey

      Experts ,

       

      We need to Migrate the Bex Workbook to Analysis for Office 2.7/2.8 .Could you please help me the pre-requisites and Steps by Steps process to achieve the Same? Reason for conversion is our workbook is on 32 bit which is not executing post upgradation to 64 bit.

      I would be thank full for your guidance.

       

      Regards

      Shailender

      Author's profile photo Mohamed EZZAT
      Mohamed EZZAT
      Blog Post Author

      Hi Shailender,

      There are 2 ways to achieve this, the first way is through the manual conversion method, what you will need to do is to open Excel, Click File, navigate to Analysis tab, choose "Convert BEx Workbook", choose the workbook you want to convert, I would suggest you do this with a 32bit Excel in order to avoid the compatibility errors that will show if you do the conversion with the 64bit Excel, here is what you have to do in both cases:

      In case of 32 Excel, got to View Macros, delete the 3 Modules:

      • Accessibility
      • DefaultWorkbook
      • Service

      These modules are the reason for the errors being thrown on the 64bit Excel.

      In case of 64 bit Excel, in order to be able to proceed to the deletion step:

      1. You will need to put a "Declare PtrSafe" statement before each sub that is throwing an error.
      2. Delete the 3 Modules mentioned above.

      The automatic method would be through ordering the BEx Conversion service described in this blog, which will be 8x faster than the manual method, it will also carry out the modules deletion activity, without the need of any interference.

      Regards,

      Ezzat