Query Creation Rules

General best practices

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

The "Core" flag in master data

When creating a core query in WBP, you need to add the core flag in the queries master data. This is used for the BW Statistics.

Core query = Flag = X in the infoobeject 0TCTBWOBJCT with BI Object = ELEM)

Publication to a Role - Rules & Process

Query assignment to role

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

The rules are :

• 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.
• Broadcast Queries must be added into the "Competence Center" role, Broadcast sub-folder.
• Qlikview Queries must be added into the "Competence Center" role, QV sub-folder

This means, these queries should NOT be published in the user roles (But you can create a sub-folder in the Competence Center role) :

• Test queries.
• Temporary queries.
• Any query in "development" that has not yet been validated.
• Any queries that cannot be run because end users do not have access (Ex : some master data, COPA ...)
• 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)

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.