Query Creation Rules

Test Queries

• If you copy a query for a test, REMOVE the "(Core Query)" from the description and add "(Test)" in the description.

Core/Specific Queries

• If possible, create the query on a multi-provider.

Before creating a new query, check if an existing one can be used instead directly, or by doing a simple modification.

Should the query be "Core" ?

To know if a query is core, then you can ask 2 questions : 

1. Can it be used for any perimeter (GBU/Zone/Country...) and give correct results?
2. Is the main purpose of the query something that is interesting for all perimeters (WW) ?

If yes, then it's CORE ! If not, it's specific and has to be indicated as such.

Usually specific queries are created for special cases :

• Reports for legal / tax reporting which is specific for each country (Ex : India has some special taxes reports requirements).
• Reports which have specific content for a GBU due to the type of business (Ex : Rare Earth is the only GBU to use the "Rare Earth Oxyde" REO unit conversion).
• Reports for indicators on which GBU's cannot agree on the definition (Ex : OTIF-C)
• Reports for SAP modules/Sources that were only implemented for a specific perimeter (Ex : GTS was initially launched only for north america).

Query Naming conventions

Query naming rules can be found in the Naming Conventions.

Query Performance Optimization

To optimize the performances, please follow the following rules :

• Use Inclusions instead of Exclusions in the filters (Exclusions cannot use the DB indices)
• Result all result rows by default
• User SAP Exit over Client Exit for variables.
• Keep free characteristics to a 8-10 minimum
• Keep the number of calculated/restricted key figures to a minimum

More in details : 

http//scn.sap.com/community/business-explorer/blog/2013/07/25/how-to-optimize-reporting-performance

Query Publication Rules

The rule to assign Queries/Workbooks into a rule are based on a very simple principle : 

The rule is :

• Core queries should go into the root folder of the role.
  
• Validated specific queries should go into a sub-folder and be clearly identified as specific.

This means, these queries should NOT be published in the roles : 

• Test queries.
• Temporary queries.
• Any query in "development" that has not yet been validated.
• Qlikview Queries (because there are usually no prompts).
• Any queries that cannot be run because end users do not have access (Ex : some master data, COPA ...)
• Broadcast Queries (These should be in the "Competence Center" menu role only).
• Jump queries (too detailed - meant to be used only via jump).
• Queries that should only be used via workbooks (Ex : WC, VS)
• Backup queries
• Any query used by support/CC for checks but that should not be (Ex : monthly inventory checks, Integrated Margins)
• Any query that gives incorrect results as is, without first including filters (Ex : FC2)

How to create BEx Variant based on Security Authorization

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

1. On both systems, run transaction SE38
2. Launch program "RSRQ_QUERYDEFINITION"
3. Run the program for the Query you want to analyse.

1. Click on the "Download" button. Then OK.
2. Go to "System" -> List -> Save --> Local File

1. Export to the "Clipboard"
2. Open a txt file and paste the content.
3. 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:

• Role in English :

• Role in French :

• 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 :

1. - 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.