There are numerous documents available on SDN that explain how to monitor, analyse and improve BW performance. SAP delivers standard queries and workbooks to analyse the BW statistics (technical contents). What I missed was a global and visual overview of the query performance over time.
Using a query from the technical contents (BW 3.5), I decided to graph in Excel the overall query mean time (in seconds) by month and by InfoCube using Sparklines (Intense, Simple, Word-Sized Graphics). Here is an extract of the result:
To further analyse the mean time, the same kind of report could be built to show the mean time for all the individual queries on a specific InfoCube.
For an explanation on Sparkline, see Edward Tufte at http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0001OR
If you want to design your own report, you need to download the Excel AddIn Sparklines for Microsoft Excel from http://sparklines-excel.blogspot.com/ or directly from Sourceforge at http://sourceforge.net/projects/sparklinesforxl/files/. This AddIn will automatically draw the Sparkline for you. If the data you want to plot is in the range A2:H2, simply type in a formula like =barchart(A2:H2) in a cell of your Excel spreadsheet to obtain the required Sparkline (you actually don’t have to type anything as all the functionalities are available through the toolbar and dialog boxes).
To conclude, what is the value of such a report ? In our case, we immediately spotted a few issues that needed further investigations.