Using SDK to identify Bex Queries accessed by BO Analysis and Design Studio reports

[business]

I was recently asked to create a list of all Bex Queries accessed by each report installed on a BO 4.1 platform. Getting this information for "classic" BO tools like WebI and Crystal reports was relatively easy and is already documented for both the SDK and RESTFul web services, but it appeared impossible to retrieve similar information for BO Analysis Excel and Design Studio reports.

The method I finally hit upon was as follows:

1. Identify the source of the report in the FRS. This can be done by accessing the SI_FILES property bag for the report using the SDK
2. Copy the file to a work directory. Do not touch the report in the FRS
3. In the case of BOA, you will have an XLSX file. You will be able to unzip this. Next navigate to the folder \xl\ and identify the custom properties files
   The CustomPropertyN.bin files are in XML format. One of them, usually the  2 in my experience, contains all the datasources accessed by the workbook.
   
   
   The name of the Bex Query is in the ID field. The only way I have currently found to identify which CustomProperty file contains the datasources is for the Java routine to loop through them until the correct one is found.
   
   
4. Identifying the datasources accessed by Design Studio is slightly easier as the dashboard is already stored in XML format in the FRS and no unzipping is required.
   
   The value of DATA_SOURCE_NAME gives the information required.
   
   

I hope you'll find this useful. I'd be interested to know if anyone else has hit upon another way of achieving the same result.