Steps to enhance a business content delivered extractor using user exit:

 1. First append a field to the structure either by SE11 append structure or directly in SBIW

2. Use CMOD to go to the user exit and look for SAP enhancement for BW in the source system, here you will find one called RSAP0001 it has 4

components inside it, one for transaction, master data-attributes, one for text and one for hierarchy

3. You need to add logic to populate the extra field at this time.

 Similarly, it is possible to add extra fields to the transfer structure as well and you can populate them either in start routine by ABAP or in 

routine of the transfer rules.

LO Cockpit Step By Step 

LO EXTRACTION 

- Go to Transaction LBWE (LO Customizing Cockpit) 

1). Select Logistics Application 

       SD Sales BW 

            Extract Structures 

2). Select the desired Extract Structure and deactivate it first. 

3). Give the Transport Request number and continue 

4). Click on `Maintenance' to maintain such Extract Structure 

       Select the fields of your choice and continue 

             Maintain DataSource if needed 

5). Activate the extract structure 

6). Give the Transport Request number and continue 

- Next step is to Delete the setup tables 

7). Go to T-Code SBIW in R/3

8). Select Business Information Warehouse 

i. Setting for Application-Specific Datasources 

ii. Logistics 

iii. Managing Extract Structures 

iv. Initialization 

v. Delete the content of Setup tables (T-Code LBWG) 

vi. Select the application (01 – Sales & Distribution) and Execute 

- Now, Fill the Setup tables 

9). Select Business Information Warehouse 

i. Setting for Application-Specific Datasources 

ii. Logistics 

iii. Managing Extract Structures 

iv. Initialization 

v. Filling the Setup tables 

vi. Application-Specific Setup of statistical data 

vii. SD Sales Orders – Perform Setup (T-Code OLI7BW) 

        Specify a Run Name and time and Date (put future date) 

             Execute 

- Check the data in Setup tables at RSA3 

- Replicate the DataSource 

Use of setup tables: 

You should fill the setup table in the R/3 system and extract the data 

to BW - the setup tables is in SBIW - after that you can do delta 

extractions by initialize the extractor. 

Full loads are always taken from the setup tables 

     A Process Chain Has Not Started At The Expected Time

Check whether all the jobs on which the process chain is known to be dependant have completed successfully – the R/3 dependencies can be established by examining the status of the events in the “R3 Last Trigger Monitor” event collector in RSA1 (“Tools, Event Collector”)

 

If there is no obvious reason for the process chain not having started, contact SBS and get them to check whether UC4 is functioning correctly

 

If SBS diagnose a problem with UC4 and can rectify it, allow them to do so in order to get the delayed job started.

 

If SBS cannot diagnose the problem with UC4, start the process chain manually according to the Break-Fix Technique described below.

 A Process Chain Has Not Finished By Its Normal Completion Time

 

Check what time the process chain started. If it was delayed, for example by an unusually long Master Data Critical load, or long-running R/3 updates on which it is dependant, it may continue running past its normal finish time.

 

Use the monitor to check the InfoPackage(s) running within the Process Chain

 

If it is suspected that the load is stuck, compare the run time so far with the last successful load from a previous day / week.

 

Look for error messages in the Status and Detail views

 

Look for “stuck” tRFCs from the Detail view:

 

Look for short dumps in ST22 in both the BW and R/3 systems.

A Process Chain Has Terminated In Error

 

Look for error messages in the monitor Status and Detail views.

 

Look for short dumps in ST22 in both the BW and R/3 systems.

 

Apply the appropriate break-fix technique if listed below or otherwise known.

When compared to a fact table, dimensions ideally have a small cardinality.  However, there is an exception to this rule. For example, there are InfoCubes in which a characteristic document is used, in which case almost every entry in the fact table is assigned to a different document. This means that the dimension (or the associated dimension table) has almost as many entries as the fact table itself. We refer here to a degenerated dimension. In BW 2.0, this was also known as a line item dimension, in which case the characteristic responsible for the high cardinality was seen as a line item. Generally, relational and multi-dimensional database systems have problems to efficiently process such dimensions. You can use the indicators line item and high cardinality to execute the following optimizations:

…

       1.      Line item: This means the dimension contains precisely one characteristic. This means that the system does not create a dimension table. Instead, the SID table of the characteristic takes on the role of dimension table. Removing the dimension table has the following advantages:

¡        When loading transaction data, no IDs are generated for the entries in the dimension table.  This number range operation can compromise performance precisely in the case where a degenerated dimension is involved. 

¡        A table- having a very large cardinality- is removed from the star schema.  As a result, the SQL-based queries are simpler. In many cases, the database optimizer can choose better execution plans.

Nevertheless, it also has a disadvantage: A dimension marked as a line item cannot subsequently include additional characteristics. This is only possible with normal dimensions.

       2.      High cardinality: This means that the dimension is to have a large number of instances (that is, a high cardinality). This information is used to carry out optimizations on a physical level in depending on the database platform. Different index types are used than is normally the case. A general rule is that a dimension has a high cardinality when the number of dimension entries is at least 20% of the fact table entries. If you are unsure, do not select a dimension having high cardinality.

Note: In SAP BW 3.0, the term line item dimension from SAP BW 2.0 must a) have precisely one characteristic and b) this characteristic must have a high cardinality. Before, the term line item dimension was often only associated with a). Hence the inclusion of this property in the above.  Be aware that a line item dimension has a different meaning now than in SAP BW2.0.

This InfoCube allows you to evaluate stocks .The reporting requirement is to report the historical stock balances on a daily level and 90 percent of all materials were moved 1 time in the month.

Basic structure of cube (Only main characteristics/key figures):

Characteristics

Time Characteristics

Key Figures

Units

SAP Query Performance  /Optimization FAQ

1. What kind of tools are available to monitor the overall Query Performance?

   1. BW Statistics
   2. BW Workload Analysis in ST03N (Use Export Mode!)
   3. Content of Table RSDDSTAT

   2. Do I have to do something to enable such tools?

   Yes, you need to turn on the BW Statistics:
   RSA1, choose Tools -> BW statistics for InfoCubes
   (Choose OLAP and WHM for your relevant Cubes)

   3. What kind of tools is available to analyze a specific query in detail?

   1. Transaction RSRT
   2. Transaction RSRTRACE

   4. Do I have an overall query performance problem?

   i. Use ST03N -> BW System load values to recognize the problem. Use the number given in table ‘Reporting – InfoCubes:Share of total time (s)’ to check if one of the columns %OLAP, %DB, %Frontend shows a high number in all Info Cubes.
   ii. You need to run ST03N in expert mode to get these values

   5. What can I do if the database proportion is high for all queries?
   Check:
   1. If the database statistic strategy is set up properly for your DB platform (above all for the BW specific tables)
   2. If database parameter set up accords with SAP Notes and SAP Services (EarlyWatch)
   3. If Buffers, I/O, CPU, memory on the database server are exhausted?
   4. If Cube compression is used regularly
   5. If Database partitioning is used (not available on all DB platforms)

   6. What can I do if the OLAP proportion is high for all queries?
   Check:
   1. If the CPUs on the application server are exhausted
   2. If the SAP R/3 memory set up is done properly (use TX ST02 to find bottlenecks)
   3. If the read mode of the queries is unfavourable (RSRREPDIR, RSDDSTAT, Customizing default)

   7. What can I do if the client proportion is high for all queries?

   Check whether most of your clients are connected via a WAN connection and the amount of data which is transferred is rather high.

   8. Where can I get specific runtime information for one query?

   1. Again you can use ST03N -> BW System Load
   2. Depending on the time frame you select, you get historical data or current data.
   3. To get to a specific query you need to drill down using the InfoCube name
   4. Use Aggregation Query to get more runtime information about a single query. Use tab All data to get to the details. (DB, OLAP, and Frontend time, plus Select/ Transferred records, plus number of cells and formats)
   9. What kind of query performance problems can I recognize using ST03N
   values for a specific query?

   (Use Details to get the runtime segments)
   1. High Database Runtime
   2. High OLAP Runtime
   3. High Frontend Runtime

   10. What can I do if a query has a high database runtime?

   1. Check if an aggregate is suitable (use All data to get values “selected records to transferred records”, a high number here would be an indicator for query performance improvement using an aggregate)
   2. Check if database statistics are update to data for the Cube/Aggregate, use TX RSRV output (use database check for statistics and indexes)
   3. Check if the read mode of the query is unfavourable – Recommended (H)

   11. What can I do if a query has a high OLAP runtime?

   1. Check if a high number of Cells transferred to the OLAP (use “All data” to get value “No. of Cells”)
   2. Use RSRT technical Information to check if any extra OLAP-processing is necessary (Stock Query, Exception Aggregation, Calc. before Aggregation, Virtual Char. Key Figures, Attributes in Calculated Key Figs, Time-dependent Currency Translation) together with a high number of records transferred.
   3. Check if a user exit Usage is involved in the OLAP runtime?
   4. Check if large hierarchies are used and the entry hierarchy level is as deep as possible. This limits the levels of the hierarchy that must be processed. Use SE16 on the inclusion tables and use the List of Value feature on the column successor and predecessor to see which entry level of the hierarchy is used.
   5. Check if a proper index on the inclusion table exist

   12. What can I do if a query has a high frontend runtime?

   1. Check if a very high number of cells and formatting are transferred to the Frontend (use “All data” to get value “No. of Cells”) which cause high network and frontend (processing) runtime.
   2. Check if frontend PC are within the recommendation (RAM, CPU MHz)
   3. Check if the bandwidth for WAN connection is sufficient

Why use Query Optimization?

When working with this mass generation, a few observations have been made. In general, I have seen anywhere between a 5% and a 250% increase in performance. Every time a query is run, the flag OPT_OCCURS marks the query “not-optimized”. This does not actually mean the performance has degenerated. In actuality, tests show that queries generally run ok for 2 to 5 days before re-generation is needed. This is due to how quickly your model changes. This means that if your data grows a lot, aggregates change, stats change, etc… The optimization changes the code of the query to optimize it on how the system currently looks. If this changes frequently, optimization may need to occur more often. If it changes in-frequently, then scheduling this every few weeks may not be a bad idea. The following enhancement allows you to only regenerate queries that have been run since being last optimized. To do this, you would copy program RSR_GEN_DIRECT_ALL_QUERIES to a Z Program, and then add the 2 lines of code below. If you wanted, you could also enhance this program to add the query technical name as an input field. That way, you could choose individual queries that you wanted to generate.

What is Query Optimization?

It is based on internal storage and aggregation data that is collected by the OLAP processor. There are things that are different in each system like aggregates, size of tables, and variable values. That’s why the OLAP processor might generate different run schedules.

How optimization works:

This optimization has nothing to do with CBO or database statistics. Every time a query is started, BW checks if optimization would be beneficial. If yes, it sets the OPT_OCCURS flag in table RSRREPDIR. Once the query is generated the flag is reset. Using SE16 on table RSRREPDIR you can quickly findout which queries should be regenerated (OPT_OCCURS = X). The timestamp tells you when the last check was performed.

Generally, as reports are run, they are almost always flagged to be optimized again.

Executing and scheduling optimization:

To optimize a query individually, go to RSRT and hit Generate Report. To schedule query optimization in mass on a periodic basis, you can schedule this by cube using program RSR_GEN_DIRECT_ALL_QUERIES. In your process chain, you can parallelize the optimization by running each cube in parallel. To enhance scheduling such that you can choose the OPT_OCCURS=X (only optimize queries that have been executed since last optimization), you can enhance this program by creating a Z Program. In most cases, this will speed up the optimization.