Register Login

Usage of BEx Queries via BICS in Web Intelligence 4.x

Updated May 18, 2018

Usage of BEx Queries within SAP Business Objects Web Intelligence based on the direct BEx Access (BICS) for DSL (for limitations on using BEx Queries via the traditional)

Why can I use only a subset of my BEx Queries within Web Intelligence?

Even though the interface used is the same as for Analysis OLAP / Office Edition and BEx, not all queries are suitable for usage in Web Intelligence. The reason behind this is the different architecture / use cases behind the different tools. The system tries to automatically suppress features which will not behave as expected as far as possible, however it makes sense to have someone checking whether the BEx Query has still value with the features omitted for the Web Intelligence scenario. If certain BEx query features (outlined below) are needed that are not supported by Web Intelligence SAP recommends to leverage SBO Analysis as frontend tool.

What about BEx Query Views?

BEx Query Views are objects based on BEx Queries, which describe a current navigation state (e.g. which characteristics are in Rows / Columns, which hierarchy is active). In Web Intelligence or Crystal these BEx objects are not necessary, as you use the Query Panel within the tools, to achieve the same result.

What are the restrictions around BEx Variables?

Nearly all variables can be used, however some restrictions exists:

Variable Types

For Selection Option variables you cannot enter complex selections, only intervals are supported here.

Usage of Variables

Avoid using input enabled Variables which are used only in Exceptions. Exceptions are not evaluated, and therefore a prompt with no effect will appear.

Special Considerations for Variables

To get the possible dimensions / attributes in the query Panel, the BEx Query needs to get instantiated. For this step mandatory variables with no default values are filled automatically by the system with random values. In certain situation the automatic assignment of default values will fail (e.g. Check routine implemented in customer exit variables).

What are the restrictions around Conditions in the BEx Query?

Conditions should not be used in BEx Queries and can create not understandable results in WebI or Crystal. They will be deactivated automatically with SAP Business intelligence (BI) 4.1 and BI 4.0 Patch 2.1.

What about Calculations / Local Calculations?

Measures, which use "Calculate Single Value as" will be omitted . They would produce inconsistent results within the client tools. The calculation depends heavily on the layout of the data requested (e.g. in which order are the characteristics requested, result line switched on / off,#) and could therefore be easily misinterpreted. To avoid those misinterpretations, these calculations are automatically switched off.

You should not use the following Calculation functions: %GT, %CT,SUMCT, SUMRT, Leaf as they might not work correctly within the client tools (same reason as above). It is not feasible to filter them out, as the knowledge about the calculations are not exposed through the interface, therefore the query designer should make sure, that those calculations are not used. If you switch on the MDX-Flag in the BEx Query Designer the usage of those calculations is checked.

What about the Default Layout of the BEx Query?

The WebI access in general does not take the default layout of the BEx Query into account. To the default layout belong for example
Arrangement of Characteristics in Rows and Columns
Default Presentation (e.g. Text / Key-Presentation)
Whether a structure member has the state hidden (can be shown) or visible
You can easily use the DSL Query Panel for this purpose.

Why is the zero suppression not taken into account?

There are 2 reasons for this: Zero suppression in BEx is defined on the axis, but the access via the Query-Panel is always only for the rows axis. Additionally zero suppression (which suppresses zeroes and null values) would clash with the show empty rows flag in the WebI Query Panel. However, even if the settings from the BEx Query itself are not taken into account you can create starting with BI 4.0 FP3 within WebI itself a similar behavior as BEx for certain scenarios. For this you can switch on settings in the Format table / Format chart dialog

What are the restrictions around hierarchies?

  • Hierarchies with Linked nodes: Only the first node will appear the second appearance of the node will be omitted
  • Keep in mind, that expanding collapsing nodes in the WebI Report will not issue any new requests to the server. This is done locally on the Mircrocube. Exactly what is defined in the member selector of the query Panel will be requested from the BW server.
  • The Initial Expansion level defined within the hierarchy maintenance (or in the BEx Query Designer) will be used to determine the requested nodes, if the member selector is not used.
  • Parent node position is always top
  • Time-dependent Hierarchy structures / Entire Hierarchy time-dependent/Version dependent hierarchy: If you use variables to determine the key date for the hierarchy (or the version), you should be aware that the prompts will only be executed after the usage of the member selector. This means, that the member selector uses the default value of the prompt (and if empty the actual date) to determine the members / the hierarchy to be shown in the member selector. If you change then the prompt later on, you might miss some data (as you have not been able to select the correct data in the member selector)

What about hierarchical display of an entire axis?

This is not supported. You can achieve similar results directly within WebI

What about hierarchical structures?

Hierarchical measure structures are always flattened , but you can use hierarchical non-measure structures.

Can I use Queries with 2 structures?

Technically it works, however the user will get no data values back, if he doesn't use the second structure In the query panel (in BEx scenarios, this is usually prohibited in the UI). As there is no visual indication for the non measure structure, we do not recommend to use this feature in use cases where people without the BEx Query Design knowledge use the Query Panel(or name the structure in a way, that makes it clear to the end user on what to do)

If for a specific structure cell somewhere "Calculate Single value as#" is set, the corresponding measure will be omitted.

How can I interpret the differences within executing a BEx Query in WebI and executing a BEx Query in the BW query test tool (transaction rsrt)?

RSRT is a Test-Tool in BW for running BEx Queries, which has been developed with the BEx usage in mind. It is based on the older BEx interface to the OLAP processor.

As described in "What about the Default Layouts of a BEx Query" , the Default Layout is not taken into account in DSLs Query Panel, instead the user defines its own layout via adding fields to the WebI Query Panel. Additionally some features which are part of the WebI Query Panel are not available in the HTML-View of RSRT (as it is running on the older BEx interface) and vice versa some features of the BEx Query are automatically omitted if you define your WebI Query. So it is not always possible to define exactly the same data request within the RSRT test tool.