Points:
1) Before delivering a query, ensure that “Allow external access to this query” option is checked in. Otherwise, BO team won’t be able to access the Bex query.
2) In performance point of view, it’s better to go with the second option ‘X’.The first option “Query to read all data at once” reads all data at once does which include all free characteristic and for sure that means that in most cases you will ask for more data than what is actualy needed.
In case of where hierarchy elements are available, we can go ahead with the option H.
3) Avoid creating query elements with SUMGT, SUMCT, SUMRT etc in the calculations. It’s observed that when we allow the external access to such queries which have SUMCT, SUMGT etc, we cannot save the queries and they are throwing errors like “cannot be released for OLEDB for OLAP”.
4) Avoid giving conditions like Top5 in BW queries. Conditions are not working properly in BO. They have the ranking option which is quite similar to conditions in BW.
5) For performance improvements, try providing variables in BW queries itself, so that creating the variables (prompts) in BO can be avoided.The general rule is that any prompt or filter should be defined in the BW query and not in the Web Intelligence query panel. This helps improving the webi report performance.
6) For large sets of data, use “selection of structure members” option in RSRT query properties in BW side.Checking this property helps improve performance in situations where there are many Restricted Key Figures and other calculations in the Query Definition.Checking this ensures the structure elements are sent to the database for processing.
7) Wherever the requirement is to give multiple values in prompt, it’s better to provide the option “Several Single Values”.
Avoid providing selection option.BO recognizes this as an interval. It will show the display screen as below in BO(with Start value and End value),which would cause lot of confusions to the BO Consultants.
8) Calculated Key Figures containing mathematical functions should be avoided. It’s giving some errors in BO.
9) In case if you are forced to give exception aggregation in query, kindly inform the BO team about the reference characteristic that is used for exception aggregation. In case if they ignore the reference characteristic, they are getting the #UNAVAILABLE error.
10) In case if you have more elements in the query designer which were used only for the calculation purpose, keep them “always hide”.
Elements having the property Hide (can be show) is visible in the BO environment. If we give that option for the case like below, where we have 2 elements with the same text like “CMBO VALUE”, it will be shown as “CMBO Value” and “CMBO Value1” in BO side.
11) Whenever the BW query is delivered to the BO Team, kindly ensure that you give a screenshot of the BEx query output. If possible, a screenshot showing the data in the underlying infoprovider also (with the same filter that was given in Bex).This will serve 3 purposes:
1) We have evidence that the Query was working fine at the time of delivery.
2) The screen shot can be used as a test case.
3) BO team doesn’t have to spend their time in finding actual filter values which has data in the underlying dataprovider.
12) Never create Text Variables with Replacement Path in BW, as it won’t work as expected in the BO side..
As a test scenario, the following text variables were created in BW:
Default value for variable is current Cal month which is 01.2013. As expected, entry when changed in the BW variable will make the corresponding changes in the output column headings aswell.
But when the same query was taken to BO, the default value 01.2013 is coming along with text of the measure(Left pane). Even after refreshing the Webi document, the default value is not changing in the column headers and the data is also not populating correctly.
The following result appeared in BO, even after the prompt was changed to 10.2011 after editing the default value 01.2013
13) When we build the corresponding Query for a dashboard requirement like the following,
For bringing the region here, we would have used the BW object 0CUST_SALES__0SALES_OFF whose actual text would have been something like sales office.But while creating the query, ensure that you change the text from sales office to Region(as mentioned in the dashboard requirement document) so that the BO consultant doesn’t have any confusion while creating/merging in WEBI.
14) Try to restrict the query result set by applying the global filters wherever possible so that huge number of records are not passed to BO level.
15) Use most of the chars in Free characteristics instead of placing them in Rows/columns.(Any ways its not going to affect ur webi output as characteristic in rows/freechar doesnot make any difference as far as webi report is concerned and everything would be shown in the same pane in BO environment).
16) Make it a habit to use the technical name of the BEx variable instead of the text in the UserResponse function(for prompts).It would be helpful if you need it to work in a multi language environment. Example,for the same technical name, the prompt text is “Date” in English and “Datum” in German.
17) Compounded attributes will always be displayed in BO irrespective of whatever trick you try to hide the same in BW.This further leads to some confusion in BO side although they have options like substring function to remove the compounded objects.
But in SP5, SAP has come with a solution for the same.
See the link below-
18) Structures defined in BW Bex part will work fine in BO WEBI aswell:
19) Default values given in BW Bex variables will work fine in BO WEBI aswell:
20) As a best practise, the BW queries should be created on top of the Mullti provider and the same mulitiprovider can be used in BO IDT or CMC for cretaing the BICS connection,in case if the query numbers are more.(for easy tracking)
In case where the query numbers are less,the BO connections can be made on top of BW queries itself.
Also,from the beginning of the project itself, It’s lways better to use a generic system user id credentials rather than the normal dialog user id’s while creating such connections.
21) From the beginning of the project itself, a well defined naming convention (approved by client) should be in place while creating the technical objects in BW and BO side and the objects(like bex query names/BO Webi connection names) created in the course of the project by the BW and BO team members should be added and well maintained in some tracker(may be an excel sheet) on a common folder or share which is accessible to all the stake holders of the project.
22) As soon as the dashboarding requirements are clear, Please have a check on the drop downs/Combo box(Dimensions) used in the charts/trends.Discuss with the client and find the maximum possible number of rows of data that might come at the production environment. This is very important as we all are aware of the Row limitations(512 to a max of 2000) in Dashboard excel.Anything above 2000 rows will badly affect the Dashboad performance.Inform this tool limitation to client before development.See the link below-
23) The rounding off option in Bex query property is not supported at the BO Webi side.
BO guys will do the =Round() function in their side to achieve the desired results.
24) If possible, try to avoid giving the Keyfigures directly from Cube/Multi Provider in the Bex Query, Instead try to give them in the form of selection/RKF/Formula or CKF. This gives the BW user more flexibility in changing the underlying logics(if required) at a later stage also, even if the BO development is completely done.BO will always refer the Bex query elements using the GUID’s generated. GUID will not be affected even if you make some logic changes with in selection/RKF/Formula or CKF.
25) The ‘Access type for Result Values’ option in Bex query Properties will not work in BO side.
26) If the requirement is to find the cumulative values, It’s always better to perform it in the BO side. Though we have the ‘Cumulated’ option available in Bex, its observed that those Key figures where you are giving ‘cumulated’ option gets missed out when you create the corresponding Webi.
For Example: Cumm.Billing Quantity measure created in Bex is missing in the Webi.
PS: a) Cumulated option might not give the values correctly in Bex, if their is a Null value or a unit change in the Keyfigure. In the below example, Billing qty cumulation was working fine till a unit change(from EA to L) happened.
b) BO has the option ‘=RunningSum()’ to deal with such cumulating requirements.
27) Never create any restrictions in the Default Values pane(as shown in the below screenshot).Though it works fine in BW side, Bo won’t consider this restriction atall.
28) There might be situations in Bex where the formula might yield ‘X’ in the report, but it will result in ‘Out Of Range’ error in BO. We can use ‘NOERR’ function in Bex wherein the X will be changed to 0 and thereby the BO error is completely solved.
29) Similar to what we have Key and Text in BW, BO also have some similar options called Caption and Unique names. But in certain cases, even if you select only Caption with the intention of seeing only description , Webi will show you both caption and unique names(technical names) like below:
The underlying Bex query for the above Webi report is as follows:
If you observe the above bex query, you can notice that certain objects with technical names 0MATERIAL and 0MAT_SALES has the same description Material and this similarity in description is the reason why Webi was behaving differently.
So whenever you create a bex query, ensure that you have not used same description for 2 objects which are having different technical names.
30) Suppose, we have a requirement in dashboard wherein we need to show fiscal year period as a drop down in a chart, the underling BW query would be in such a way that the fiscal year period will be available in rows.When the BO consultant use this query for creating a Webi report for the dashboard, he would be trying to sort the fiscal year period. Unfortunately the tool wont understand the year change scenarios and the sorting would yield the result like below (mixed up):
So, as a best practise, try to ensure that you provide fiscal year and if possible,posting period also along with the Fiscal year period in the Bex query. This point needs to be kept in mind at the time of BW modelling part itself. Further the BO consultant can drag in the fiscal year and do a sorting on that as well as the Fiscal year period key so that the sorting order is obtained as expected(see below screenshot)
31) Please be aware that the negative Key figures in BW would sometimes be shown within brackets(without -ive sign) in BO side(as shown in the below screenshots)(as the BO developer would have used the custom format option). This awareness would be very useful when you create some variables in BO which includes some addition or subtraction operations with such measures.There is a high possibility that your variable result would bring some different figures than what we expected, if not properly taken care off.
32) In rare cases, its observed that sorting in Ascending or Descending order behaves strangely in Webi side causing problems in meeting the exact requirements. In such cases, you can depend on the similar option that we have in BW, while creating the Bex query. This option would be handy in cases where you need to sort the fiscal year period key and all.
33) If a multiprovider doesn’t contain any Bex queries on top of that, its observed that such multiproviders will not be shown in the BICS connection.So its mandatory that you build atleast one query on top of the multiprovider to expose it in BO(seen this in BO4.1 also)
34) Recently, one of my colleagues was having a issue with the output of a Bex query that was built on a BW composite Provider:
The issue was that the output of the measure ‘SALES VALUE’ in RSRT was never matching with BO webi report.
Solution: Later we realized that we had to set the property ‘Unique Join Columns’ in the BW Composite Provider; post which BW RSRT and BO Webi report output were exactly matching. ( Sorry am unable to attach the screenshot here).
Actual Technical Reason:
What do you have to remember when defining joins in a CompositeProvider?
When creating a CompositeProvider with join type JOIN, you can choose the option ‘Unique JOIN Columns’. This option is only available in transaction RSLIMO with BW 7.30 SP01 and should be used carefully. In other words, it can be used because the expected drill-downs will not result in incorrect data due to filters, for example, or due to a really unique JOIN condition. It can be set with the Analytic Index used in the CompositeProvider; right mouse click and select ‘unique JOIN condition’.
If this option is not set, the JOIN column is added to the GROUP BY automatically by the BW OLAP engine, in order to calculate a correct sum for the key figures from different sources. It ensuresconsistent data at the cost of the performance, because the result set read from the BWA is increased and a post-aggregation step is performed on the BW application server and not on the BWA.
If the option is set, aggregation is carried out before the JOIN is executed, which could lead toincorrect results if the JOIN is not really unique.
35) Performance Hints
a) If a Bex query contains Hierarchies, don’t forget “supress unassigned nodes” in RSH1(Tcode) in BW side.
b) In BO side, enable “Query Stripping” in webi Query Panel and in report design mode – Document Properties.
This feature optimizes the query to fetch only data which will be dragged on to the report for user display thus increases the performance and faster display of the report for the user.
c) Where possible, define calculated and restricted key figures on the InfoCube instead of locally in the query.
d) When using restricted key figures, filters or sel)ections, try to avoid the exclusion option if possible. Only characteristics in the inclusion can use database indexes. Characteristics in the exclusion cannot use indexes. With selections it is better to include than exclude characteristic values.
e) The setting “Result Rows” for each characteristic the in BEx query should be set to “Always Suppress”. Web Intelligence does not use the “Results Rows” and performance will improve if this option is set.
f) Time characteristics should be restricted, ideally to the current characteristic value.
Move time restrictions to a global filter whenever possible.
g) Check code for all exit variables used in a report to make sure it is optimized and functioning.(in BW side )
h) Reduce the data volume selected by introducing many mandatory variables.(in BW side)
i) Define only those characteristics as navigational attributes, which there is a business requirement for; navigational attributes need additional table joins at query runtime
Even though Web Intelligence does not distinguish between a normal characteristic and a navigational attribute, on the BW system there is still an impact on performance, when Web Intelligence reports are running on BEx Queries with navigational attributes.
New releases like 4.1 would certainly washout many of the existing limitations pertaining to this integration and it will also enhance the power of BO tools where the source would be SAP BW…..
Related Documents:
1) Implementing Bex Data Functions in WEBI:
2) Performance optimization for WEBI reports based on BEX:
3) Best practices for BO4.0 in combination with Netweaver:
4) Query element parameter table:
5) Support for Bex elements in BO4.0 SP5:
6) Tips for developing smart WEBI reports over BEX:
7) Performance optimize BO reports based on BW using BICS:
8) Selecting the right BO tool:
9) Dynamic rolling graphs in Bex-BO integration:
10) Create connections in BO 4.0
%20blogs&source=web&cd=10&cad=rja&ved=0CGkQFjAJ&url=http%3A%2F%2Fwww.pieterverstraeten.com%2Fblog%2F%3Fdownload%3Dhow-to-create-connections-in-bi-4.0.pdf&ei=VUszUejkL4OKrgeCn4CoAg&usg=AFQjCNETrRa74QhxolhCv8BsZZhhcQIIcQ&bvm=bv.43148975,d.bmk
Related OSS Notes:
a) 1611185 – Usage of BEx Queries via BICS in Web Intelligence 4.x
b) 1764607 – Configuring the number of levels for a hierarchical report in SAP BusinessObjects BI 4
c) 1749171 – Performance: BICS_PROV_GET_MEMBERS reduction
d) 1755154 – Performance: Set and read hierarchies in backend (ABAP)
e) 1785451 – Performance: SET_HIERARCHY and GET_MEMBERS optimization 701
f) 1903067 – BICS Connections to SAP BW are not released after some Scheduling workflows for Webi
g) 1399044 – Why do I need XI 3.1 / SP2 / FP2.5 or SP3 and NW BI 7.0 EHP1 both for webi. XI 3.1 and BEX?
i) 820925 – MDX – Restrictions
j) 1687933 – BI 4.0 -WebI Query Panel – not possible to use “OR” operator when creating Query Filters on BEX
k) 1260238 – Decimal places cut during Webi Infoview refresh when reporting off SAP BW Bex
l) 1406146 – BW Qty KF with significant decimal numbers gets rounded to integer in Webi (decimals dropped)
m) 1447838 – Hierarchies on Characteristic InfoObject extracted from ERP to BW gives ‘Error on
NumResultCols WIS 10901′ when prompt level are expanded in Webi.
n) 1712306 – BICS: ABEND RSBOLAP (000) during CHECK in Variable Screen
o) 1806187 – ABEND RSBOLAP(000) error when refreshing Webi
p) 1849851 – BICS: Dump on CL_BICS_CONS_STATE_CONVERTER for exception
r) 1895216 – BI4.0 “Cannot retrieve dimension from cube.” while trying to create a Webi report on
Bex query with an Offset and restriction on Fiscal Year Variant.
s) 1900390 – Hierarchy Error in WebIntelligence reports running from BI LaunchPad
t) 1789916 – TOO_MANY_DRILL_DOWN_OBJECTS error in Web Intelligence based on BICS even with
Query Stripping enabled
u) 1815587 – System error in program CL_RSMD_RS_SPECIAL and form _GET_SID-01 in WebIntelligence
when getting LOV for UOM variable (BEx variable of conversion type to select a unit of measure)
v) 1741829 – Error in case of RRI jump and invalid selections
w) 1733328 – ABEND RSBOLAP (000) Program error when refreshing Webi Report
x) 1649310 – In BI4.0 “Cannot retrieve dimensions from cube <cubename> (WIS 00000)” error is raised when trying to create a Webi report on a BEX query that contains Text Variables in members of a keyfigure structure
y) 1993774 – Error “This document does not contain any reports.(Error:INF)” while opening migrated Bex based WebI reports in BI 4.1 SP02
Related Discussions in SDN:
a) Web Intelligence condition with key element of infoobject ? http://scn.sap.com/thread/3306722
b) Display and Retrieve only Overall Results in Bex Query ? http://scn.sap.com/thread/3381436
c) Query Panel and connection BICS for BW ? http://scn.sap.com/thread/3426559
d) How to achieve the BW infoprovider last refresh date in BO ? http://scn.sap.com/thread/3309546
e) Workarounds for the issue where BICS Connections to SAP BW are not getting released ?http://scn.sap.com/docs/DOC-47284
f) BI4 BICS Webi : Problem with Quarter Aggregation ? http://scn.sap.com/thread/2107986
g) No “OR” Function for WEBI using BEX (BO 4.0) ? http://scn.sap.com/thread/2086716
h) Error CALL_FUNCTION_ILLEGAL_P_TYPE in St22 ?
This article was very informative. Thank you so much for the effort. I have implemented few of the things from here on my client's WebI report. I would also recommend people to follow this article for query stripping in Webi.
ReplyDeleteVery helpful
ReplyDelete