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