There is always a fine balance between development which tries to control query development and power users especially people well trained in BEX to have access to everything...
But then is there a balance and should you try to find one...? or are there other ways of achieving the same ?
1. Different namespaces :
Objects with Z namespaces will be transported versions . There will be a set of queries which will be part of this namespace and they will be collected every quarter from Development and transported into Production. Any changes in Production will be overwritten.
If there is a query that needs to be added : a request must be raised to create the same in development. When transporting into WP1 - make sure all Z Query elements created locally are deleted using RSZDELETE and also all query elements created with Z which are in $TMP namespace are deleted - this can either be done manually or through programs written for the same.
Also you can have reports of objects created in the Z namespace which will identify these objects on a regular basis...
All local queries will be in the Y namespace and will be deleted every 6 months since they are considered temporary.
The creation of queries in different namespaces can be controlled through authorizations if required...
2. Object changeability - Changeable Original
Block changes to query elements and people can create copies of queries and change then ( set object changeability to changeable original ) - this might lead to a spurt in new queries being created but then using methods identified above - you will have to regulate the deletion of these queries and the workbooks if any that exist for the locally generated queries.
Before deletion a report could be sent to the creators of the query to make sure that they want it to be deleted. Here since the volume of queries created locally will be high - deletion will probably have to be done monthly..?
3. Creating Saved Views
Another option we used was - create an ad-hoc query with all the fields of the multiprovider / cube and the end user can create their own views on top of the same for anything specific. These views are anyway temporary and can be deleted if required..
Expose Saved Views on Web might give you an idea...
Also these views are all connected to the main query and in my case - we implemented this at a previous project and query creation requests came down drastically and we rolled it out to the entire user group.
Variable / CKF / RKF creation would have to be handled through the development process to keep the landscapes consistent.
There is also a downside to this procedure - saved views can be deleted from BEX and no where else ... so if a user wants their 500 query views deleted - it takes a long time!!! ( we have had this scenario once and it was not ful with all the clicking !!!)
4. Use Business Objects
If you have Business Objects - then expose the OLAP universe and then users can create their own WEBI queries and save them under their favorites - this is similar to approach 3 but done using BO....
These are the three things that come to my mind initially... But before doing this you will have to clean up your existing query base and make sure that all queries are copied over to Dev and then transported back with care....
Another thing is that this does not happen overnight - it took us about 1 month to set the landscapes in sync before we could do anything...
You also have the option of a retransport to dev and back but then I found it very cumbersome and since it involves moving transports physically - I always ran afould with BASIS and necessary approvals and eventually decided not to use this route - not because of bureaucracy but more so because it was cumbersome to do it as opposed to the options above....
