Top N results from Bex to WebI using BICS

[Former Member]

Hello Folks,

I´m using a BICS connection to get data from a BW Bex query to Web Intelligence report. However, I have too many records in the Bex query and it is too havy extracting all these data to BO and then using the Ranking option from webI.

Therefore, I want to select the top N records from BW Bex, before sending them to webI (600 records).

The problem is that I have found in the documentation (link below) that BW Bex conditions are not supported in webI. I could only read there that:

"

Crystal Reports for Enterprise, Web Intelligence, and SAP BusinessObjects Dashboards are not able to support Local Calculations defined for key figures in the BEx Query (for example, a key figure configured to display as a rank value).

"

http://www.mastering-sap-and-businessobjects.com/sap-businessobjects-bi-4-service-pack-05-support-fo...

So, I cannot use rank option neither conditions!

Any ideas how can I restrict values in Bex query to send to WebI?

We are on BI 4.0 SP5.

Thanks in advance!

Regards,

Mariele

[IngoH · Accepted solution]

Hi,

Web Intelligence does not support Conditions from a BEx query and there is no plan to add such support for Web Intelligence.

If conditions are important you should look at the Analysis products.

regards

Ingo Hilgefort, SAP

[Former Member · Accepted solution]

Thanks Ingo.

Just more one doubt. In the documentation I could find also that rank is not an option. See below:

Crystal Reports for Enterprise, Web Intelligence, and SAP BusinessObjects Dashboards
are not able to support Local Calculations defined for key figures in the BEx
Query (for example, a key figure configured to display as a rank value).

So, if I define rank values in Bex Queries it will not be available in webi, is that correct?

thanks for your help!

regards

Mariele

[IngoH · Accepted solution]

correct, those are also not able to support local calculation.

and correct the ranking will not be available in Web Intelligence.

Web Intelligence is a reporting client - not an analytical client

ingo

[IngoH · Accepted solution]

Correct, something like a condition or a ranking (local calculation) is not supported by Crystal Reports f Enterprise, Web Intelligence, and dashboards.

regards

Ingo Hilgefort

[shawn_mcdowell1]

We had a similar issue with a large data set that is presented in a WebI document.  We modified our ETL process to calculate the "rank" in the InfoCube and then were able to filter in BEx/BICS and deliver only relevant records to WebI.

[Former Member]

In my humble opinion, this is a sloppy solution... Isn't there any other way to limit the number of records sent to Web Intelligence?

[mike_howles4]

I ended up having to do similar to what @Shawn_McDowell mentioned, except we used a BEx Query to do the ranking logic and then flattened it using EasyQuery to then feed it into a DSO (I should have just dumped it into a RDBMS) that had only what we needed in it.  This only makes sense if you have no need for hierarchies or exception aggregation which was fine for our use case.  I agree that there should be a better solution but most of the guys here will say that a relational reporting tool isn't intended to perform analytical functions such as ranking in BICS which lead me to flatten the data to get around it.

[IngoH]

if conditions are important, you should leverage the Analysis products.

regards

Ingo Hilgefort

[IngoH]

if conditions are important, you should leverage the Analysis products.

regards

Ingo Hilgefort

[Former Member]

Thanks for the input!

Well, after several hours trying to not get a Java Heap error on Web Intelligence, I give up.

Here's some of what I tried:

. Do the top 500 in a query then use it as a subquery via replacement path variable. Didn't work, BEx saves the main query successfully but dumps when accessed because the query is configured to external access.

. Rank() with top, count() and Order() functions in Web Intelligence. No luck either.

The customer wants Web Intelligence... So tomorrow I'll just proceed to create a DSO on the same level as the cube in the dataflow, and create some hardcore routines to fill a ranking caracteristic in it.

[Former Member]

Hi Ingo,

What analysis product you recommend for complex queries with conditions and local calculations?

[Former Member]

I think Analysis for OLAP and Analysis for Office...

[IngoH]

Hello Rafael,

there is Analysis, edition for Microsoft Office and Analysis, edition for OLAP

regards

Ingo Hilgefort

[Former Member]

Hey Ingo,

Are conditions supported in Design Studio ?

thanks,

Rahi