This is a scenario that has now bitten us twice and I have yet to find a good answer.
We have users who wish to created formatted or interactive reports where the data has been warehoused over the years in SAP BW cubes. We've had SAP BW since at least 2003 and all we had back then was BEx and Crystal (legacy pre-MDX stuff).
We've been shifting to BusinessObjects to broaden the BI options for reporting, and recently moved to BI 4.0 for the promised optimized BICS capabilities with Webi and CR Enterprise over MDX. So while that has been a change for some of our OLAP Universe users to shift from MDX to BICS (things like missing ability to filter on detail attributes and so forth) for the most part there's been a way to address those (such as Nav. Attributes for that example).
So here's my problem I'd love to hear a solution for... Since we are a traditional BW shop with BEx, we'd like to be able to perform rankings or conditions and only bring those into Webi or CR Enterprise for performance reasons instead of doing these at the reporting layer. A recent scenario we have a report that needs to show for a Sales Manager, the top 20 customers's bottom 10 performing items in terms of MSF Shipped YTD. Well, while the end-result report may be only 4 pages, since it must evaluate this ranking at the report layer, we pull back way more cells via BICS than needs, which turns into wait time for our users.
As has been stated many times, we cannot perform rankings or conditions in BICS clients, for some principles around analytics vs relation reporting which I accept. Unfortunately, it's a harder concept to carry over to our users. They do not care if the data is in a multidimensional datastore or a relational datastore, they just want the performance that BEx gave with the reporting functionality. I'm totally open for hearing suggestions of how to push back or separate those concepts to help keep our users in BEx/Analysis/Explorer and out of Webi/Crystal but I'm struggling with how to do that.
Our user's perception is "I can pull this back in seconds in BEx but it takes minutes to Webi". So my question is what technique can be applied to give the OLAP aggregation power of BEx Queries/BICS for a report that needs to apply these types of Top X Bottom Y rankings? As a reminder it's in a cube today because of what we've had over the years, so I'm open to hearing better techniques as the reporting tools, capabilities and business requirements have changed over the years.
Thanks for any helpful discussion!