BEx Delete Duplicate queries

Applies to:

SAP BW 3.5x / Business Intelligence 7.3x, Business Explorer (BEx).

Summary:

This document is intended to a specific case where Multiple Queries gets created with Same Technical Name.

Author(s)     :  Ganesh Wathare

Created on  :  11 November 2013

Author Bio:

Ganesh Wathare works as Technology Lead in Infosys Limited. He has 7 years of experience in IT Consulting Industry during this time he has worked majorly on SAP NetWeaver BW and relational databases together with different BI reporting tools. He has worked on Various Support and implementation Projects.

Summary:

While transporting query to further landscape some time multiple entries gets created for a same query (Technical Name). This happens in a special cases like  we delete query in development which is already sent in landscape and then we create new query with same technical name. But while transport we just send a Transport for newly created query. In such a cases sometime system creates new query with same technical name and previous query will be still present. Because of this when we execute query it will execute old query itself. Even multiple time transport does not help.

This blog is to those specific cases where such a scenario gets created. Some times when we execute query system takes new query sometimes it takes old query, in such a case this blog will help to clear orphan queries.

Steps to Delete Queries:

1) Check if multiple queries present with same technical name

Query elements are stored in table RSZCOMPDIR i.e. Directory of reporting components. We can check if there are multiple entries for same query technical name. For each query there will be 2 version’s so to check if there are multiple queries with same technical name, provide query technical name in Query filter and Provide Version as ‘A’ to check active records. Below screen shows a selection for table. ( We can see table data in se11 or se12 or se16)

If there are multiple queries present then in table there will be two or multiple entries in the table. These entries will be having different UUID hence it is possible to have such a entries in table.

When we execute query if old query is still getting executed then it is because of multiple entries in tables, which we need to clean, so that system will recognize new query. We can identify the entries for respective queries by using Changed by, Changed At, etc fields as below.

2)Delete Entries from table using RSZDELETE

We can delete these entries from table using t-code RSZDELETE for deletion of entry of a query we need to provide UUID for query. Query is identified by Type field, so provide input as a REP in Type field. Select Version Transport System Based on Process related to your system.

Dependent Objects like Workbooks, Query Views and BBS Sender/Recipient we need to delete before we run this transaction individually.

If there are no dependent Objects in the system, transaction will show output as below. You can identify dependent Objects from this screen also, so that you can delete those objects also from system.

Once we click on Execute button prompt will come to confirm for deletion. Select Yes to delete the query from system.

Confirmation screen will pop-up once query is deleted as below.

To confirm deletion in the system we can go back to table RSZCOMPDIR and see entries, we can see one entry got deleted from system and only one entry is present in the table.

Also as we start executing query then system will not find any query, as query is deleted.

3)Remove inconsistency in tables using program “ANALYZE_RSZ_TABLES”

Once we delete the query using a transaction RSZDELETE then it creates a inconsistency in tables which can be identified and correct using a program ANALYZE_RSZ_TABLES (we can execute Program using se38 transaction).

This Program has selection screen as below, where we can select tables where the program should check inconsistency,

Once we click on execute, program will give a output with all inconsistencies. Icon shows Inconsistencies which need to be fixed. As we have delete one query we can see count 1 in front of RSRREPDIR table.

If we click on button in front of count we can see the query as below.

Once we confirm and click on Execute Button system ask for confirmation, click on Yes.

Once program gets executed below screen comes which will shows 0 count for RSRREPDIR table. This shows that inconsistency got corrected.

For ANALYZE_RSZ_TABLES check reference attached to this blog.

Now if we execute query we can see that new query is getting executed without any error.

Suggestions for using these steps

Its better to avoid problem than solve problems, so always transport Query deletion request in system. Don’t use this program if correct query is getting executed.

References:

https://scn.sap.com/thread/10671

https://scn.sap.com/thread/1997950

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/602074dd-bcc9-2e10-4eb5-8107050066bc?QuickLink=index&overridelayout=true