Decode an Analysis Office Workbook

The Analysis Office Workbook saves a lot of information in it. The Microsoft Excel format xlsx is a container which can save a lot of other information besides the normal data. When you rename a xlsx file to a zip file you can extract it with for example 7-Zip. Now you see the following folder structure.

Analysis Office Workbook Structure
Analysis Office Workbook Structure

When you open the folder xl, you see the custom property. Normally there are several customProperty.bin files listed.

Overview Custom Properties
Overview Custom Properties

In my case it is customProperty3.bin (just search for the custom property with the highest size). When you open it for example with notepad++ and select the language setting xml it should look like this.

Custom Property
Custom Property

You see information like system ID, server, data source and all information which you can set in the component tab in Analysis Office. And you also see a compressed and encoded area which we will now analyze.

Encoded Area
Encoded Area

In this encoded area SAP stores a lot of information which you cannot see at the first sight. I developed a little tool in Visual Studio to decrypt this area to a readable text.

Manipulate Excel Property Tool
Manipulate Excel Property Tool

Now you see what SAP encoded in this <BICS_VIEW_HEADER> area.

Decrypted <BICS_VIEW_HEADER>
Decrypted <BICS_VIEW_HEADER>

Here we go, we see which dimensions are in this workbook, how sorting for each dimension is adjusted or which valus are stored in the prompt. So we can analyze in detail how the workbook behaves and maybe discover difference between a development and production system.

 

Is this something anyone needs? Then I would upload the program to github. Here is the link to github.com

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Analysis Office - The Comprehensive Guide 4th edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.7 and contains 299 Pages.
59,99 €
Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
39,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
29,99 €
Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
9,99 €

Write a comment

Comments: 6
  • #1

    Tim (Monday, 28 October 2019 07:46)

    Thats pretty neat. But what use cases do you see for this?

  • #2

    Joachim (Monday, 28 October 2019 15:44)

    Yes please share your code to decompress the BICS_VIEW_HEADER

  • #3

    Tobias (Monday, 28 October 2019 17:04)

    Hi Tim,

    you can use it to analyze the workbooks from your development system vs. your production system and if they are the same.

  • #4

    Tobias (Monday, 28 October 2019 17:04)

    Hi Joachim,

    I will upload it to github and set the link in the description in the next days.

  • #5

    Joachim (Tuesday, 29 October 2019 11:28)

    Thanks, I managed to decode the string however.
    I'm working on automating the scheduling (and I want to eliminate the need for using Excel) of an AO workbook in SAP-BIP and I need to change the prompt values so my idea is to manipulate the decoded XML and use the rest api to add the file and then schedule it.

  • #6

    Tobias (Wednesday, 30 October 2019 09:33)

    Hi Joachim,

    you find the link at the end of the post.