i have a problem in refreshing bex analyzer workbook. the situation is,
1. i have over 50 queries, each attached on one seperate excel sheet, on one workbook.
2. it takes some 20 minutes to refresh the whole workbook.
3. when i refresh the book, the following popup window appears again and again, over 30 times before the refresh finishes.
<internal error>problem when writing table :E_T_CEL
but as far as i click the CANCEL button to continue, the processing will just go on and no problem in query result.
4. i searched the note and understand the reason is the overflow of a hidden sheet used to store metadata by bex analyzer, just as described in sap note 711442
5. i decided to split the workbook to reduce the metadata in one workbook.
6. <b>here is the question:</b>
when i design the new workbook, how can i caculate the total amount of the metadata based on the complexity of the query? eg. how many query cells yields how many metadata.
can you help me?
Sorry that I missed your posting last month. If you do not have the answer already ...
There is no way to know exactly what is the practical limit to the number of queries that can be embedded in a single workbook.
The limit is the number of rows available in an Excel worksheet = 65536. As each query is embedded in the workbook, tables are created in the (very) hidden worksheets. There are 8 tables in the worksheet named SAPBEXqueries and 3 tables in the worksheet named SAPBEXfilters.
The CEL table details the display attributes of the Key Figures. Apparently, in your queries you have more Key Figures than Characteristics. So, the total number of Key Figures (whether displayed or not) provides the limit to the number of queries you can embed in a single workbook. Each Key Figure will create one entry (one row) in the CEL table.
For the queries that I work with, there are usually more Characteristics than Key Figures. More importantly (for me), each Characteristic has non-navigational attributes. Each attribute will create one entry (one row) in the ATR table. So, for me, the number of attributes will determine the maximum number of queries that I can embed in a single workbook.
For someone else, it might be the number of filters. Each filter value creates one entry (one row) in the FAC table.
The easiest thing for you to do is to make the (very) hidden sheets visible and press Ctrl+End to locate the last row that contains an entry.
I hope this helps.