Tips & Tricks on developing smart Web intelligence reports over SAP BW Bex Query

[former_member203132]

Tips & Tricks on developing smart Web intelligence reports over SAP BW Bex Query - Part 1

by Adir Oren-January 2013- mail: adir.oren@bics.co.il

Business Scenario:

You are using SAP BW as your Data Warehouse and want to use the SAP Business Objects 4 Suite web intelligence as your front end tool.

For this you have 2 options :

1. recommended by SAP is working with Bex Query designer– which uses SAP BICS protocol
2. to build Universe over SAP BW Provider- this method is called multi source universe, and is being defined via the SAP BO IDT(Information Design tool) , it also known as relational connection

For more info you can visit here

This document refers for Option 1 – develop web-intelligence reports over SAP BW Bex Query

Q: The dilemma of developing 1 Bex query that contains all the fields you need for all BO queries ,or developing as many Bex Query as the BO reports.

A:I think It is a combination – when you have several of requirements which is on the same Bex Query – you will properly want to develop 1 bex Query that will serve all of the Webi requirements, but if you have several requirements which contains several levels of aggregation or there is a difference between business consumer’s – managers, Analysts ,etc , or you have in some reports complicated calculation or logic, I would separate the Bex Queries. I would recommend on defining a report strategy before you start development

Q: Where to put the Characteristics in the Bex Query for BO ?

A: It is recommended to put everything in the free characteristics section from performance point

Q: Naming Convention- Do we need them?

A: I would recommend you to have naming convention for Bex queries that is for BO reports, it will give you more clear view and manage those queries  in the BW

Q: Data restriction -Filtering- where is most recommended to do filtering?

A: Restricting data directly in a SAP BW  BEx query decreases the number of data records the system will retrieve from the SAP BW and will increases 

report performance, another advantage  is that when you do the filter in Bex Query you control the data retrieval, if not you will need to do it in the BO  Query Panel, the risk is that if IT builds the reports you can “trust” them that they will know how to build smart queries , if super user builds the queries with query panel, you cannot control the filtering , which can cause performance problems and system overload if it is over Provider that contains millions of  records

Q :Prompts/Filtering- Gui Aspects – is there are difference on Gui presentation

A: Yes, when you use Filter coming from  Bex , and when you filtering in Query Panel, the Gui looks a bit different from user perspective .

Tip: If you will use Universe over provider, you will be able to have smart filtering in the query panel and use "join" or "or" command between info objects– this is standard webi functionality with universe

Q:Can I show in the BO prompts, values coming from Info provider and not from MD

A: yes you can, it depends on what you have defined in the Bex info object properties in the filter value selection during BW extraction, choose “only  vales from info provider”

Q: Calculation- where to do the Calculated key figures and restricted key figures  ?

A: I would recommend creating  the calculation in the Bex Query- this increase performance, and works faster than in the web-intelligence

Q: Smart Calculations- if I need smart calculation as doing sub string- where to do it in BO or BW?

A: As you all know in Bex Query Designer you cannot do function as Sub String, you need to do it in BW modeling- I would recommend doing that in  the Web-intelligence

Q: I have larger sets of key figures, is it ok or will I face performance problems?

A: I recommend to “Use Selection of Structure Members/Elements” in transaction RSRT (Query Monitor) for better performance

Q: Do I need to add the Flag “Allow External Acess to the Query” in the Bex query properties?

A: Yes you do, for SAP BO Dashboard you don’t need to flag it only for webi

Q: Will KF that has exception aggregation work in BO?

A: Yes they will

Q: In BW Bex Query we have the option to create variable for condition – e.g I have 50 rows and I want to present the rows that the revenue is over 10M or

top 3 , I want the number 10  or 3 will come from the user variables in the prompts  , will it be shown in webi? Or can I create this on webi?

A:No you cannot, the webi is not OLAP tool, what you will need to do is to use the web intelligence input control functions

Q: If I want to present the Loading time and date information in the BO reports, can I find it in the standard BO report information?

A: Unfortunately you cannot- there are several workaround for these requirements

Q: Is it “correct” to compare report performance time – webi vs Bex Analyzer or WAD

A: No it is not- BW reports with Bex Analyzer and WAD always run faster

Q: I am facing performance problems, what can I do?

A: this section I will review in Part 2 – but for now you can use query stripping , and checking your BW model from Index ,Aggregation, statistic’s etc ,more detail info will be found in Part 2 of my document

In document part 2 , I will review and have tips for better BO performance, starting with BW modeling threw the BO webi preference’s and  the BO query  Modeling .

For any comments or questions you can send me mail to : adir.oren@bics.co.il