Improving CAFE performance?

[NYD3030]

I'm wondering if anyone has an detailed understanding of how CAFE query execution differs from BI query execution and what can be done to make CAFE more performant? I'm pretty deep in the weeds at this point and need a fresh perspective.

For some background, I'm working with a client who is hellbent on using CAFE as a financial forecast solution (over our objections!) and is seeing extremely poor performance of data retrieval - as in, the template they have designed takes upwards of an hour to run. Poor performance was anticipated because the packages on which this template are built perform poorly in BI as well, however this is significantly worse than expected. As part of the solution we building a data mart that will make the template simpler, with fewer tabs and queries, however I'm still trying to quantify the causes of poor performance.

I have done an ipfclientconfig perf.qfs trace which indicates almost the entire process is taking place at the UDA stage, which makes it tempting to declare it is just a database problem. However, when I convert the CAFE queries to Report Studio reports and run them serially in a job, it takes eighteen minutes to return data. The performance logs indicate that when executed from cafe the UDA stage is taking significantly longer, and the process is generating a greater number of queries. This leads me to believe there is some critical difference between CAFE and BI query execution of which I am unaware.

So all that said...

1. Is there reason to expect CAFE to generate less efficient/a greater number of queries than BI?
2. Is CAFE engaging in significant local processing of data sets to display results, or is it relying on the BI server to do so?
3. Is there any way to control where processing takes place in CAFE if necessary?
4. Just in general, does anyone have experience improving CAFE performance?

I'll end by saying - CAFE is transitioning to being a TM1/Planning Analytics tool instead of a Cognos tool and I for one am thrilled. I have a number of clients who are insistent on using it and I've had nothing but problems.

Cheers!

[NYD3030]

PS, sorry for the wall of text ;)

[bdbits]

I have seen CAFE demoed but have not worked with it personally, so just throwing out some ideas. I would actually expect it could be generating more queries than a similar BI report, but that would be highly dependent on the template. I initially thought perhaps it was the formatting phase, but you have the ipf trace that seems to say otherwise. I guess my next step would be to have a DBA trace both a CAFE and a non-CAFE run of the reports. If it truly is in the database phase that is the difference, the captured SQL may reveal some clues or give some ideas about how the CAFE side could be improved. Certainly when you look at it, you should be able to tell if there must be local processing going on to transform the SQL result sets into what is appearing on the reports.

From what an IBM salesman once told me, I think CAFE was actually created for creating financial reporting scenarios, so your client(s) might not be totally crazy. :-)