Skip to Content
author's profile photo Former Member
Former Member

Table or way to know selections for which query ran

Hi Experts,

I want to find out selections for which particular query ran, with the help of Technical queries i can find out time take, Cube got hit, and User who ran it.

I Need to Clear some space in my BW system, to do so i need to know the selections for which query got executetd, if query is not running for older data i can delete them.

Thanx in Advance.

Regards,

Ajit Mishra.

Add a comment
10|10000 characters needed characters exceeded

Related questions

9 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 28, 2013 at 07:19 AM

    Hi Ajit,

    If I understand correctly, you are looking for what selections user entered to run the report. So you can get details of what data is generally seen by users. Say users just see data for year 2013, 2012 and 2011 and not older.

    I am not aware of any table contains these details that what selections were passed by user while executing the query.

    One possible option I can suggest is to check in generated Cache of query. By this analysis you may come to know what selections are executed.

    Another thing if you have aggregates on cube on year basis. Check the hit on the aggregates to get the count these are hit for reporting.

    These are ways you can check what data is generally not seen by users.

    One suggestion - before deleting data one should always confirm with users if older data is required or not. I have seen some reports that are used one in 5 years of so for auditing purpose only. So do not delete any data for just securing space.... 😊

    Regards,

    Anil

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 03:35 AM

    Hi Ajit,

    Even if you google it, you will get whole table list thru WIKI.

    Table:

    RSZSELECT

    RSZRANGE

    http://wiki.sdn.sap.com/wiki/display/BI/Relation+between+between+BEX+Query+DefinitionTables

    Thanks

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 04:29 AM

    Deleting query definition does not give you great space gain. It will enable other queries to run faster. memory space can be gained in other ways like Change-Log Deletion, PSA deletion, Delete older statistics data etc..You need to basically do some house keeping jobs to gain space.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 27, 2013 at 10:47 PM

    Hi Ajit

    You can run the transaction RSZDELETE to find out the details of BEx Query objects. Please note that this transaction is used for deletion purpose. In this screen, I believe Type, Technical Name and Last Used will be useful for you to perform the searching.

    Regards

    Mak

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 05:47 AM

    Hi Ajit,

    For all such purposes ,you need to install BI Admin Cockpit and I hope you have implemented this.

    Once this is done ,yu can use standard BI ADMIN Queries where it shows you the hits of Query.If the Hit is Zero then you can plan to clear the unwanted queries.

    http://scn.sap.com/thread/1428664

    http://scn.sap.com/docs/DOC-19540

    Rgds

    SVU

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 06:19 AM

    Ajit Mishra wrote:

    ...if query is not running for older data i can delete them...

    I hope I misunderstood the above... you're seriously NOT going to delete data from your data warehouse, are you? If so, then what's the purpose of having a data warehouse?

    You could consider "archiving" instead, but still... you're missing the point of data warehouse then.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Suman Chakravarthy K

      Hi Suman,

      I had seen your reply, we have already cleared PSA, DSO change log and other housekeeping stuffs, we are following those from long time, At present i want to Know selection used to execute queries.

      Thanks for helping Suman.

  • Posted on Aug 28, 2013 at 07:46 AM

    Hi Ajit,

    Inorder to know the query stats you can check the table RSDDSTAT_OLAP.

    In this table you can find the

    - User

    -Date on which the query execute

    - How many time the query is being execute

    based on the information you can get to conclusion on which is query is being executed last time and take decision to delete the queries.

    I Need to Clear some space in my BW system, to do so i need to know the selections for which query got executetd, if query is not running for older data i can delete them.

    Really if you are looking to clear space in BW system ... you can follow the things suggested by Suman.

    Check the below links

    http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/1058b68f-96c4-2d10-fbb1-de8757ca6a58?quicklink=index&overridelayout=true

    http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a02ba9e7-bb6f-2c10-09b4-e86b9fcbad41?quicklink=index&overridelayout=true

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 12:12 PM

    HI

    Agree with the others. We use rszdelete to determine the queries that have not been executed in a certain time and then delete them in dev system and transport the changes. RSzdelete will directly delete queries if you execute it in production. As the others mentioned you won't save disk space by deleting a query but it helps to get a cleaner overview

    Martin

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 22, 2016 at 12:15 AM

    Hi Ajit, were you able to find a way to achieve the variables used in query except going through cache? Please advise as now I need to do similar exercise 😊.

    Also for Cache - I am finding it hard to get details how many times particular cache is used so really can't rely on that.

    Regards,

    Anil

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.