query design recommendations and performance issues

recommendations:

1. free characteristics :There must be a meaningful number of characteristics here (maximum 8-10 characteristics) whose content is required for the data analysis. In addition, the free characteristics should be defined in the different queries that have the same InfoProvider in the most consistent way possible.
2. Conditions: Conditions can require a lot of calculations (such as Top N or joining conditions). To improve query performance, one can precalculate the results set using the Reporting Agent in defined gaps and then transfer them to the query using a variable. See the section on Performance below. In NW04 you can use Information Broadcasting to
   precalculate Queries or Web Templates.
3. Exception aggregation:
   In the query definition, the exception aggregation that was specified with the InfoObject
   maintenance can be overridden. This overriding leads to a worsening of performance
   however.
4. General Points of the Design of Queries:
   For an InfoProvider, usually only very few, though more complex (around four) queries are delivered. Complex queries are not be used directly in a Web template as a data provider.They can be restricted using query views.
   Queries are only allowed for ODS to request individual records. This is because
   aggregation of the ODS records can lead to performance problems.
   We recommend to create Queries only based on Multi Provider (not on Info Cube or ODS
   level). If no existing Multi Provider is available, you should create a new Multi Provider.
   You can then split the Info Provider (e.g. by year) and use the Multi Provider as a virtual
   layer.
5. Units
   Here you have the option using currency/unit 1CUDIm
   alongside the key figure structure to drilldown by different units and to build a total per unit.
6. Decimal Places
   In the Query Designer, you can define the decimal places for each key figure. To do so,
   you need to open the dialog properties (right mouse click on the required key figure).
   Generally speaking, as many decimal places should be provided as required by the end
   user. However, the following usability guidelines should be taken into account when
   defining decimal places.
   • With percent values, a maximum of 2 decimal places should be used.
   • Key figures of data type integer are to be defined without decimal places.
   • Currency fields are to have a max. of 2 decimal places.
   • Key figures of data type DEC are to have a max. of 2 decimal places.
   • No decimal places are to be used with highly aggregated data (annual revenue).
7. Don’ts in Query Design
   The multiplication of key figures leads to incorrect results in the total rows:
   Example: Revenue (price * amount) is to be displayed in the query:
   Price Quantity Revenue (price * amount)
   10 4 40
   5 6 30
   15 10 150
   Example: (a+b)/(c+d) instead of a/c + b/d
   Calculated key figures on totals are calculated by the OLAP processor analogous to
   the single records. These can lead to errors in the totals rows (revenue: 40 + 30 =
   150!!!!!! See red arrow).
   Instead of the average price, the amount and revenue should be saved in the
   InfoProvider. The average price (revenue/amount) can be generated in the query
   (see black arrow).
   • Hierarchy name cannot be delivered.
   • Partner Content Development: When saving Content objects, the associated
   package must be specified in the development system. Local objects have the
   package “$TMP” and are not transported. Query elements that are added
   subsequently are not allowed to be assigned to the package “$TMP” (also not for
   testing purposes), when the associated query was created on a correct development
   class.
   • Report-Report interface (Online Documentation): RRI settings must be collected
   individually. They will not be collected together with the corresponding Query.
8. keep drill down as few as possible at the begining of the report.
9. define calculated and restricted KF on Multiprovidor level.
10. The expected result set of the query should be kept as small as possible (max. 1000
    lines).
11. A Web application returns quicker query results than the BEx Analyzer. Also, the
    transfer time increases much quicker as the data set increases in the BEx Analyzer
    compared with the Web application.
12. Using graphic elements (charts, buttons, frames..) significantly affects query runtime.
13. InfoCubes and MultiProviders are optimized for aggregated requests. A user should
    only report in a very restricted way on ODS objects and InfoSets. In other words, only
    very specific records are to be read with little aggregation and navigation.
14. All calculations that need to be made before aggregation (such as currency
    translation) should take place when loading data where possible (see note 189150).
15. With selections it is better to include than exclude characteristic values.
16. Do not use totals rows when they are not needed.
17. The calculation of non-cumulative key figures takes a long time. In this case, the
    InfoCube must be compressed.
18. Time characteristics should be restricted, ideally to the current characteristic value.

way to improve performance:

Precalculated Value Set

Aggregates