6 Replies Latest reply: Dec 22, 2009 11:22 PM by John Gordon RSS

BI System Administration Tasks

John Gordon
Currently Being Moderated

Hi All,

 

I have recently been tasked by my project manager to have a look at the current BW system and to try and make it run more smoothly.

From my first impression I can see that the PSA are always full and are not deleted as part of any chain and therefore are using up database resources, a point highlighted to me by our basis team.

 

Secondly I do not think they are deleting and re-creating any indexes on the system cubes which could improve performance

 

My question is:

 

Is there a standard job in sm36/37 that automatically deletes the PSA requests from the PSA table, I would be looking to do this on requests older than 10 days. also what potential issues am I likely to face and is there anything I need to be careful of when creating this job.

 

Similarily for all the cubes on the system If I am looking to delete and re-created indexes on a periodic basis, how often should I be doing this and what are the steps I need to carry out to schedule this job in the system and what do I need to be careful of, is it a safe job to run in production?(after testing in Dev and Q of course).

 

Many Thanks

 

Paul

  • Re: BI System Administration Tasks
    Neha Paroha
    Currently Being Moderated

    Hello Paul,

     

    You can automate the deletion of requests from PSA using Process Chains. Add the Process type Deletion of Requests from PSA in your Process chain. In the Process Maintenance screen, specify your PSA Table name and various Deletion options like:

    Older Than Days

    Page Before {nn/nn/nnnn} Date

    Only Successfully Booked/Updated Requests

    Only those requests with errors, that are not  booked in a data target

    Delete load requests only; do not delete activation requests (ODSR...)

     

     

    Similarly, the Deletion and Generation of Indexes can be automated using Process Chains with Process Types Delete Index and Generate Index.  In the Process Maintenance of these process Types, you need to mention the name of your infocube. The frequency of Deletion and Generation of Indexes depends on the volume of data the Info cube holds. If the cube has less data, the Indexes can be dropped before every load and Generated after the load is complete. Otherwise you can schedule a weekly run for the Deletion and Generation of Indexes in case of large volume of data.

     

    Hope this solves your problem.

     

    Regards,

    Neha

  • Re: BI System Administration Tasks
    Arda Anil
    Currently Being Moderated

    Hello,

     

    Best way to delete PSA is via process chain. You can use DBACOCKPIT transaction to make further analysis on disk usage by tables (ODS, Cube, PSA, etc.) Space -> Additional Functions -> BW Analysis.

     

    Besides monitoring tablespace I also recommend you to have a look at below points as well:

     

    1- Short dumps in ST22

    2- System logs in SM21

    3- Operation System Monitor in ST06

    4- Periodic check by SAP_INFOCUBE_DESIGNS program for the infocubes (fact table vs. dimension table)

    5- Deletion trfc Queues in SM58

    6- Schedule RSRV checks to analyze the important BW objects

    7- Check scheduling of your process chains.

     

    These are the points that came to my mind at first.

     

    Please also check the links below:

    [FAQ - The Future of SAP NetWeaver Business Intelligence in the Light of the NetWeaver BI&Business Objects Roadmap?rid=/library/uuid/08f1b622-0c01-0010-618c-cb41e12c72be]

    [FAQ - The Future of SAP NetWeaver Business Intelligence in the Light of the NetWeaver BI&Business Objects Roadmap]

     

    Many thanks,

    Arda.

  • Re: BI System Administration Tasks
    Arun Varadarajan
    Currently Being Moderated

    Paul,

    Things that could be quick wins from a database perspective :

    1. List out all your tables in descending order of size / number of records ( this can be done directly on the database using a client - like TOAD for Oracle etc ) and then attack the largest tables.

    Attack would qualify as :

    1. Delete data in PSA Tables - sometimes the PSA deletion job runs in the process chain but the tables never get deleted in terms of data - if this is the case do an SE14 on the same to drop the data.

    2. Archive older data from the DSOs wherever applicable

    3. Run a reorg on your tables frequently - atleast once every two weeks - this will give you unallocated space that can be used.

    4. Run stats on your databse regularly - ideally for all tables if this is possible.

     

    As for Cube Indices - dropping the indices and then recreating the same after data load is the recommended way.

     

    Check aggregate usage and identify unused aggregates ( using statistics / early watch reports )

     

    All this could give you a lot of space on your database and make the job of the DBA easier...

  • Re: BI System Administration Tasks
    Bryan Koetting
    Currently Being Moderated

    Paul,

    actually, cleaning up the PSA tables is a good housekeeping task to free up datebase space, but it doesn't have much impact on system performance, either for data load performance or query performance.

    You need to discuss data load performance separate from query performance, because these are 2 entirely separate issues. 

     

    Data load performance has much to do with how well your data model is designed, and whether you have any complex routines in the updates.  Dropping indexes from infocubes may speed up load performance for large loads, but has negligible impact for small loads.  I have found that DSO activation, and complex lookup routines, have a much much bigger impact on load performance than infocube indexes, and these are the design issues, not technical issues.

     

    The data model also plays a large part of query performance.  Modeling with data-segregated infoproviders and multiproviders, use of OLAP Caching and aggregates, partitioning and compression are all modeling tools used for query performance, and again these are design issues.  I'm sure you can find numerous BW performance documents on SDN, and SAP also offers classes on the topic.

  • Re: BI System Administration Tasks
    Vikram Srivastava
    Currently Being Moderated

    Hi Paul,

     

    Very good and interesting question to answer. We have carried out the same exercise recently. There can be multiple areas hat can be looked at to run the system smoother, two of which you have rightly picked up.

     

    Deleting the data older than 10 days from PSA

     

    You have 3 options:

     

    1. Use the process type " Deletion of Requests from PSA", here you can specify number of days oder than 10 and schedule the process chain.

     

    2. You can also use the program RSAR_PSA_CLEANUP_DIRECTORY to schedule a background job.

     

    3. You can use program RSPSADEL1 to schedule a background job from SM36, by specifying PSA name as the variant.

     

    Practically speaking in a running Productive environment, there will definitely be some PSA's which you want to clean up on a weekly basis and there will be some in which you always want to retain 10 days data. Now option 1 is better when you want to retain 10 days data, and option 3 is the best option for PSA cleanup.

     

    It would be a good idea, to carry out this exercise in phases.This wil also assure everything is fine.

     

    You can identify top 15-20 % ,PSA tables according to thier size for the first phase.This will save lot of redundant space at the databse level.

     

    Deleting and Recreating Indexes for cubes

     

    It as absolutely safe to delete and recreate the cube indexes in poduction. You can easily do this by using the process types "Delete  Index" and " Generate Index".

     

    Ideally you should first delete index , load the data and then recreate the indexes.But ofcourse the time taken by this is dependant on data volumes.More the data volume,the more time it will take.

     

    You should deinitely consider the below areas for making the system better:

     

    1. Compression of data older than X days

     

    2. Calculation of DB Statistics of the cube

     

    3. Run Stats of tables ( Basis Team can help in this area)

     

    4. Reorg of tables ( Basis Team can help in this area)

     

    5. Data deletion for the cubes above retention period ( This will save a lot of space in BW and BWA ( If you are using one).

     

    6. Change log table deletion

     

    Hope this aswered your query.

     

    - Vikram Srivastava

     

    Edited by: Vikram Srivastava on Dec 22, 2009 7:57 PM

  • Re: BI System Administration Tasks
    John Gordon
    Currently Being Moderated

    great answers guys appreciate the advice.

     

    will try implement these points over a period of time.

Actions