Query design rules

Query Naming conventions

Type	Technical Name	Description
Core Query	BW_QRY_ + Infoprovider + 0XXX Ex : BW_QRY_CUB_PM001_0004	"BW - QueryDescription (Core Query)"`footnote}}if name if too long, you can use "(Core)".{{/footnote` Ex : BW - Maintenance Costs (Core Query)
Zone Specific Query	BW_QRY_+ Infoprovider + CXXX where C : 9 for Latin America 3 for Asia Pacific 7 for North America ? for Europe

Ex : BW_QRY_CUB_IC001_9001| "BW - QueryDescription ( "Zone" Specific)"
Ex : BW - Freight Costs (LA Specific)

Country Specific Query	BW_QRY_+ Infoprovider + CXXX where C : 9 for Latin America 3 for Asia Pacific 7 for North America ? for Europe	"BW - QueryDescription ( "Country" Specific)" Ex : BW - Freight Costs (Brazil Specific)
GBU Specific Query
Test/Temp Query	anything with "TEST"	anything with "TEST"

Where X is an incremental number

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 "* SZDELETE*"
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 (WBD & WBP)

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:

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 :