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.

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.

Adding a variable/characteristic or changing the default characteristics is NOT a reason to create a new query !

Should the query be "Core" ?

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

Can it be used for any perimeter (GBU/Zone/Country...) and give correct results?
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

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 roles must look clean/organised AND only include queries that can be run directly by the users.
Basically, we want the users to feel that they can use safely and trust any query in the folders.

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)

Translation in the roles

When you add a query/workbook into the role, the current language description is applied to ALL languages

When adding a query/workbook into a role, it is important to then translate manually the link via the PFCG transaction : otherwise the description will be the same in all languages.

The same way, if you change the description of a query/workbook, you need to manually change the same description in the PFCG for the link.

Page tree

Query Creation Rules and Process