1. What are the Query Tuning you do when you use reporting?
a) Install BW Statistics and use of aggregates for reporting
b) Avoid using too many characteristics in rows and columns, instead place it in free characteristics and navigate / drill-down later.
c) OLAP Cache (Change Cache TCode RSCUSTV14): It’s a technique that improves query performance by caching or storing data centrally and thereby making it accessible to various application servers. When the query is run for the first time, the results are saved to the cache so that next time when similar query is run, it does not have to read from the data target but from the cache.
d) Pre-calculated web templates
e) Use small amount of data as of starting points and do the drill down
f) Instead of running the same query each time save the query results in workbook to get the same query results for different users. Each time you run the query, it refreshes the data /same data should not fetch from data targets.
g) Complex and large reports should not run online rather they should be scheduled run during off-peak hours to avoid excessive contention for limited system resources. We should using RA to run those off-peak hours in batch mode.
h) Queries against remote cubes should be avoided as data comes from different systems.
i) If you have choice between using hierarchies and characteristics or navigational attributes, you should choose char or navigational attributes.
j) Create additional indexes
k) Use compression on cubes since the E tables are optimized for queries.
l) . Turn off warning messages on queries
2. What is BEX Download Scheduler?
The BEX Download Scheduler is an assistant that takes you through an automatic, step-by-step process for downloading pre-calculated Web templates as HTML pages from the BW server onto your PC.
3. Difference between Calculated key figure and Formula?
Formula & calculated key figures are functionality wise same.
Calculated Key Figure is global where as Formula is local (for that query only.
CKF will have tech name and description where as Formula will have only description.
CKF is available across all Queries on same InfoProvider where as formula is available only for that Query.
While creating CKF, certain function will not available from formula builder where as while creating formula, all the function will be available from formula builder.
4. What is difference between filter and restricted key figure?
Filter restricts whole Query result where as RKF restricts only selected KF.
for Example: Lets assume we have 'company code' in filter and it is restricted by '0040'.Query output will be for only '0040'.
if u restrict a KF with '0040' in RKF, then only that KF data will be restricted by '0040'.
Restricted key figures are (basic) key figures of the InfoProvider that are restricted (filtered) by one or more characteristic selections. Unlike a filter, whose restrictions are valid for the entire query.
For a restricted key figure, only the key figure in question is restricted to its allocated characteristic value or characteristic value interval. Scenarios such as comparing a particular key figure for various time segments, or
plan/actual comparison for a key figure if the plan data is stored using a particular characteristic, can be realized using restricted key figures.
5. What is the use of Structure in BEX/Query?
Combination of characteristics and key figures are called Structure.
Structures are basically a grouping of key figures which can be created for a Infocube and reused in any other queries for that cube.
Structures find the biggest use in financial reports. Take an example of a financial report which has about 20 normal keyfigures, 10 calculated keyfigures and another 10 restricted keyfigures. Now assume that someone asks for a new report with all of these as well as 5 more keyfigures. Normally you would have to create a new query and manually re-create all the complex key-figures. However, if you had saved them as a structure, you just have to drag-and-drop the structure into the query. So if there was a change in the calculation of one key-figure, you just have to change the key-figure in the structure and not change all the 10 reports which show that key-figures.
We get a default structure for key-figures. That is most people use structures for key-figures and SAP has designed it that way.
Within a query definition you can use either no structures or a maximum of two structures. Of these, only one can be a key figure structure.
6. Difference between filter and condition in report
Filters act on Characteristics; Conditions act on Key Figures. You do not use KF in the filter area. Only char values can be restricted in the filter area, whereas Conditions are created to key figures.
7. Reporting Agent
Definition: The Reporting Agent is a tool used to schedule reporting functions in the background.
The following functions are available:
- Evaluating exceptions
- Printing queries
- Pre-calculating Web templates
- Pre-calculating characteristic variables of type pre-calculated value sets.
- Pre-calculation of queries for Crystal reports
- Managing bookmarks
You make settings for the specified reporting functions.
You assign the individual settings to scheduling packages for background processing.
You schedule scheduling packages as a job or within a process chain.
8. RRI: Report-Report-Interfacing is the terminology used to describe linking reports together. Report-Report-Interfacing uses Jump Targets that are created using the transaction code RSBBS (see Question #4). A Query with RRI functionality can be identified by clicking on the Goto icon in the BEx Analyzer toolbar.
9. What are the restrictions on ODS reporting?
Active, retired and terminated employees can be separated using different ODS for detail reports.
10. Difference between ODS & Cube Reporting
ODS is 2 dimensional format and it is not good to analyze the data in multi dimensional way. If you want to take flat reporting then go for ODS reporting.
Cube is multidimensional format and you can analyze data in different dimensions, so if your requirement is multidimensional report go for Cube.
Example: List of purchase orders for a vendor is two dimensional reports whereas sales organization wise, sales area wise, customer wise sales for last quarter and comparison with earlier quarters is a multi-dimensional report.
Two dimensional reports are similar to reporting on a table. ODS active table is a flat table like an r/3 table. Reporting is done on active table of ODS. Other tables are for handling the deltas.
Cube structure is a star schema structure. Hence Reports on cubes are multidimensional reports.
11. Why we need to use 0Recordmode in ODS?
0Recordmode is an InfoObject for loading data into ODS. The value indicates how the data should be updated and which type.
Field 0RECORDMODE is needed for the delta load and is added by the system if a DataSource is delta-capable. In the ODS object the field is generated during the creation process.