Query Creation Rules
Query Naming conventions
Query naming rules can be found in the Naming Conventions.
Query Performance Optimization
Here is a link to a guide on how to optimise query performances:
http//scn.sap.com/community/business-explorer/blog/2013/07/25/how-to-optimize-reporting-performance
Query Publication rules
How to create BEx Variant based on Security Authorization
google:com/a/solvay:com/presentation/d/1QJvOEY-s3dMOQi106Ri4zCejC5Qe3ho-kXVJwrHpwTg/edit
Query cleaning procedure (Un-used query deletion)
In order to delete un-used queries that may polute the system, it is necessary to run a cleaning process from time to time.
To do so :
- Launch transaction "RSZDELETE*"
- Select Type = REP (Query)
- Select Last Used = Current date - 365
- Run the list
- Check the list one by one to select the queries to be deleted.
- Run the deletion (this can take several minutes)
- When prompted to delete workbooks, views etc, select * O* : it is possible that some workbooks using these queries are still used for other queries.
Rules for deletion :
- To be deleted queries not used for more than a year (Last used < Current date - 365)
General rule : if you are not sure, do not delete it.*
You can safely delete :
- Test queries :
Queries containing "test" or "tst" or "temp" in description or technical name.
Copy queries for UR's : Queries with "URxxxx" in the description or technical name.
Queries with IT member initials in it. - Super user queries : Technical name starting by "Z".
You should not delete :
- GSV Queries : Technical name starting by GSV - For this you need validation from Purchasing Tools team.
- GSV Cockpit queries : Description starting by GSV Cockpit - For this you need validation from Purchasing Tools team.
- Core queries : queries with technical name starting by "BW_QRY" or "RCS_QRY" and that have "Core Query" in the description - For this you need validation from corresponding stream Reporting Process Expert
- Technical content queries : technical name starting by 0
- Backup queries : stated in the name or technical name it is a back up query.
+ you can check the usage of the query on the 18 months ago to be sure that the queries are not used anymore by someone.
How to compare Queries between 2 systems
- On both systems, run transaction SE38
- Launch program "RSRQ_QUERYDEFINITION"
- Run the program for the Query you want to analyse.
- Click on the "Download" button. Then OK.
- Go to "System" -> List -> Save --> Local File
- Export to the "Clipboard"
- Open a txt file and paste the content.
- Use a code comparison tool to compare both files. Exemple : http//www.tareeinternet.com/scripts/comparison-tool/
How to find queries using an info object
You can use the following function modules in order to display the queries using an info object.
Function modules
RSZ_I_BASIC_CHA_WHERE_USED
RSZ_I_BASIC_KEYFIG_WHERE_USED
It’s much more efficient than using the metadata repository.
Example for info object 0GL_ACCOUNT__C_GL_TYPE. With metadata repository I list around 40 queries with this object.
The reality is much more different because with the function module I obtain 96 queries!
There are also some tables to list the info provider using the nav attributes
RSDODSOATRNAV – For DSO
RSDCUBEIOBJ – For Cubes and MPR
In this page, there is a list of very useful tables
http//wiki.scn.sap.com/wiki/display/BI/Important+Tables+in+SAP+BW+7.x
Managing query descriptions in the roles
+When you change a query description in the query designer, it does NOT update it in the roles.
+When you add a query to a role, the description for all languages is the one in the current language.
Example in the PP role:
- If you do a search with the query technical name, it will show this (the description is correct!) :
2 ways to solve this problem:
- Each time you update a query description, you need to delete it from the roles and add it again (using the analyzer/analysis).
- Each time you update a query description, go to transaction PFCG, select the role, go to Menu, click on the report, click on “Translate Node” and change the name in all the relevant languages.
Step by step :
- - Go to transaction PFCG
2 - Enter the menu role :
3 - Go to Menu and select the query
4 - Click on “Translate Node”
5 - Change the text in English and any other relevant language.
