Skip to Content

Creating Queries / changing Queries in Production – Pitfalls

I came across a lot of posts where people had created queries in production and then wanted to reconcile their landscapes. Just wanted to cover some pitfalls of a free hand in creating and changing queries in production.

In many cases it is common to have queries changed in production after transport or ad-hoc queries created directly in  production for some exigencies.

Some of the issues that this possibly creates :

1. When you have a portal based environment where all the query access is through portal – results will be different between landscapes and testing will be problematic – especially so with SOX compliant systems…

2. When queries are changes in production – the changes have to be documented carefully else the changes are lost in detail  and we end up with queries with changes that the developers are not too aware as to why it was done in the first place and 
the original requirements are lost in the changes that were done…

3.In many cases once this becomes chronic the issue is too big to resolve.

Issues that can be faced

Also issues that might be faced when reconciling the systems by way of changing the queries in the respective systems etc are  :

1. Never create a query directly in production and then transport the same back into production – this will create copies of  the query since the Query is tied to a GUID that is generated in the local system . For Example if you create Query ZQUERY in 
production and then create the same ZQUERY in Development and transport the same – you will see two ZQUERY entries in BEX and  reconciliation becomes that much harder.

Solution : Delete ZQUERY in production using BEX or RSZDELETE before transporting the same into production.

2. Never create any Info provider specific CKFs or RKFs in production – basically any query elements that have a technical  name on their own. I am referring to CKFs created at the info provider / CKFs / RKFs with a technical name the effect on 
transporting the same is the same as above where duplicate entries get created and Query designer starts showing errors since  two elements with the same technical name should not exist.

3. The same rule applies to variables also.

Solution :
Delete the duplicate elements using their GUIDs ( or GENUINIDS ) using RSZDELETE and then the problem goes away – else you will have Query designer failing for certain queries and creating havoc on your queries if left unattended for long.

Concessions :

Make sure that no elements are created in production with technical names. Otherwise existing elements can be edited and later retransported without any impact. Example – The same ZQUERY can be first transported and then edited in production provided no new query elements with technical names are created.

Also for implementations where queries are being created in production – moderate your query change access and also once you have the above restrictions of not creating technical IDs in production you can still continue to change / modify queries in production – but then make sure your landscapes are in sync.

I have not come across an effective means of reconciling these query designs , will do so .. but would require your inputs on how this can be done…

You must be Logged on to comment or reply to a post.
  • We allow our business users to create/change adhoc queries in production. These have a certain naming standard (Y).  We then have standard queries that get transported to production that have a different naming standard (Z).  Business users can not change these standard queries in production.  They can only change the adhoc queries.  We do the same thing for all reusable query components.  This gets around the pitfalls of potentially introducing an issue to standard queries while allowing the flexibility of business users to create/change queries in production.
  • Mark, that's a good way to meet halfway.  I now can see that it might be ok to build queries in Production, but not RKF's, CKF's, or Variables. 

    Also, thanks for the article Arun Varadarajan!

    • Kenneth, you can have RKFs and CKFs created in production - but do not assign a technical name to them - keep them local - basically do not create reusable RKFs and CKFs in production.
  • Hi,

    important weblog! We encountered the problems you described at our clients very often.

    A nice way to find out such duplicate technical names with different GUIDs is to use report ANALYZE_RSZ_TABLES, which also gives you the possibility to change the "Technical Name" of a query component or variable.

    Best regards,

    • We got to know that after some serious issues with some of our queries .. there were cases where the issue got so out of hand that a query got renamed as an RKF!!! - the analyze_rsz_tables came to our rescue in helping us clean up the mess...
  • We have problem users that they want try to justified the change or creation of queries and web templates on production system.
    This users are really a headache, with this information we could justified, why is not possible to do this kind of bad practices.

    Ramón sánchez
    Grupo LALA, México.