Web Intelligence reports does not due to huge data volume

[ab81f90e67574787845c8beb7]

Hi,

I am working on BO XI 3.1 on SAP BI. I have a query in BI, and the data contained in it huge, (around 1.5 crore records). The query contains around 30 fields.

I have built a universe on top of the query. When I select "New Web Intelligence" report in INFOVIEW, and select the universe, I get all the fields as objects in the "Data" panel.

There are 10 optional prompts that I have put in the query. I pull certain objects and then click on "Run Query" button. The report shoes "Retrieving Data" in the status bar and I don't even get the "Prompts" window. This is the scenario that continues to stay. Nor do I get the Prompts window, nor does the report run any further.

Kindly suggest what should I do in this scenario?

[Henry_Banks · Accepted solution]

Hi,

please be aware that units such as crore and lahks are not wideline known outside of India .

Depending on the BW version, you could be hitting the 1 million cell (row x col) limit imposed by BW .

everything after BW 7 Ehp1 SP05 and above was 'optimized' (at about release ~ XI3.1 SP04)

You should monitor the BW work processes SM50 to see what's happening in the BW backend.

try and filter to a maximum, by answering all the prompts

also, query stripping enabled in both query panel and webi document.

also, tick 'suppress unassigned nodes' in bw transaction RSH1, and also 'enable selection of structure elements' in RSRT

regards,
H

[ab81f90e67574787845c8beb7 · Accepted solution]

Hi H,

I re-chcked my data. There are 50 million records in the query.

When I try to run the query through RSRT, the prompts window appears in less than 2 minutes, while in WebI report, prompts do not appear even after 1 hour of running the report.

My concern is regarding the prompts. Once the prompts appear, I will filter the data, so as the output num of records will be reasonable enough for the report to display.

[Henry_Banks · Accepted solution]

what about all the other factors/checkpoints i outlined?

[ab81f90e67574787845c8beb7 · Accepted solution]

I am working on those points. I will post the outcome.

Thanks.

[former_member188911 · Accepted solution]

Bear also in mind that connecting through a UNV to BW is not the optimal solution.

And when you have large documents in XI3.1 you normally face the architectural limitations explained in this blog post http://scn.sap.com/people/michael.thompson/blog/2010/04/12/when-is-a-large-document-too-large-some-r...

[Former Member]

Hi Amrita,

I have worked on scenarios where we had millions of records. And then it required rigorous performance improvement measures .You can try out below scenarios and see how it works for you:

Step 1 : Optimise your bex query , restrict as much data as you can by applying characteristic restrictions or filters to remove any extra records . See if the query is fetching ONLY relevant records.

Step 2: If data remains huge , u need to improve bex query performance, go for building aggregates on the cube on which bex query is created OR go for putting the cube on BW Accelerator.This will reduce the data retrieval time.

Step 3:After that do ensure that only relevant fields have been dragged in the Webi report. Do ensure that whichever field is not being used in webi, should not be present in the the bex query as well.

If all this doesnt help, then think of logically spiltting your Bex query.

For example, if you have 5 characteristics and 2 key figures required for 1 tab in Webi and another 3 characteristics and 2 key firgures for 2nd tab in Webi , split the two bex queries.

This is because if you have 1 bex query , then total no. of cells being hit = 8 characteristics * 4 key figures = 32 cells.

On splittting,  then total no. of cells being hit =( 5 characteristics * 2  key figures)+ (3 characteristics * 2  key figures) = 10 + 6 =16 cells.

This approach can reduce the number of cells your bex query is hitting drastically.

Take care of merge dimension in case you follow this approach.

Even if this doesnt work , then you might have to think of spliting your Webi report.

Thanks,

Richa

[ab81f90e67574787845c8beb7]

Hi,

I have progressed and have been able to display the prompts in the WebI report:

Solution: I removed the LOVs that were being generated. I changed the code in the universe.

Now, when I run the query with a specific number of prompts, in RSRT, the query is taking 25 mins to run and generating 50k records.

In BO, when I run the report with same parameters, there is an error in the report after 2 hours, "WebI server could not be reached".

Note: The "Timeout" in the BI server is 2 hours. On pulling a report, the query runs as a backend.

Why is the report in BO taking so much time, to pull only 50k records, when the query is taking only 25 mins ?

Any suggestion would be helpful.

Thank you.

Regards,

Amrita

[Henry_Banks]

Hi,

RSRT doesn't use the MDX interface - it goes straight to the OLAP engine.

what happens if you extract the MDX select from the BO logging, then test the statement under transaction MDXTEST ?

All those BW notes still apply, would be nice to hear you respond on those points!

regards,

H

[ab81f90e67574787845c8beb7]

Hi,

The BW notes suggest that I need to upgrade my BI and/or apply a Support Package.

I donot have Admin Rights for the same, and making these changes will require approvals in my project and also a lot of time.

- How do I extract MDX query in BO?

Recent observations:

I did indexing in my BI query and extended my BW server "Timeout" to 2 hours.My BO report (the same parameters that I put in RSRT and it takes 25 mins, and the BO report could not get completed its process in 1 hour), it took 1 hour 45 mins and the status was "Success" ! But when I downloaded the report it showed 0(zero) no of records.

- I am not sure how do I go forward.

Regards,

Amrita

[Henry_Banks]

.. and therein lies the problem:

1) you are trying to extract too much data via webi - effectively a data dump. the tool isn't built for this.

2) you don't have the BW-BO interoperability fixes which will speed-up routines, and compress the data exchange . that's the upgrade project that needs to happen.

This is is not a new phenomenon, but rather a classic 'complaint' that was very common in 2009/2010 - which we took steps to fix by way of software releases.

Regards,

H

[ab81f90e67574787845c8beb7]

Hi,

Yes I understand where the problem is.

But, let me mention my latest observation for this situation:

In RSRT, I put some parameters and the report took 3 mins, and brought in around 15k records (not a huge number).

Same report I run in BO and took me 1 hour 53 mins, thankfully not crossing my Timeout limit. It brought in the same records.

15k is not a huge number of records. I am aware of the MDX query and BICS connection that run in the backend.

But there is such a huge difference in the run time of the reports in BI and BO !   Is this the case always?

Regards,

Amrita

Reference: http://scn.sap.com/thread/1905022

[Henry_Banks]

Hi,

As mentionned above - comparing RSRT (native OLAP engine) and MDX (flattened XML datastream) is not the same.

Also, there is no relationship between MDX and BICS they are totally disctinct seperate middleware/architecture.

When you say "i tested the same report in BO, and it took xx" - I am 100% sure that you are not comparing the same:  i bet your webi query panel includes many more objects (dimensions, details, and measures) than exist in the bex query's default/initial query view (which is what loads in RSRT) .  Start adding the additional drill downs in RSRT to better represent the actual Webi query being requested.. Soon RSRT performance will blow up.

Now capture the MDX statement from BO's MDA.log. Run this statement in BW transaction MDXTEST, and see BW perform as badly as BO, this limitation comes from the MDX interface (which is support component BW-BEX-OT-MDX) which is where the aformentionned notes orginate, and where your partial solution resides.

Next, you should use the parameters i described on my initial response dated 02-Sep-2013 13:16

You might also be interested in these notes

1381821 - Memory and Performance Analysis for MDX

1406664 - MDX: Extracting MDX commands from RSTT traces

and also this tuning document: https://websmp209.sap-ag.de/~sapidb/011000358700000750762010E

Regards,

H

[ab81f90e67574787845c8beb7]

Hi,

I know this is not a solution, as BO should not be used as a data dump tool.

But, what if I upgrade my BO to BI 4.0, which uses BICS connection to connect to the database.

Will my exact same report,(will remove the Universe layer), run properly, inspite the hude volume of data?

Regards,

Amrita

[Former Member]

Did you get any help by upgrading to BI 4.0/4.1 using BICS.

BW: EHP2 SP6, BO: 4.1 SP1 Patch 3

We use latest BW and BO releases.

But I have a similar situation. The report business wanted from Webi tool is basically a master data dump from an Employee Infoset.

And it has two tabs which brings 1 Million cells into Webi report from BICS.

For Employee Analysis tab: 792000 Cells (~ 9000 Rows * 88 Columns)

                Education Tabs :  297000 Cells ( ~9000 Rows * 33 Columns)

They Sum to = 1089000 Cells  = 1 Million+  Cells

Several times i get several error messages .

JAVA Heap Space Issue - Increased the DSL Memory from 2 GB to 6 GB but didn't help

CORBA error while communicating with SL  - > No clue why this message comes (Bad debugging capability of Business Objects)

Do you know how to over come this issue? Do we have any settings which would make sure there is no restriction of 1 Million Cells in Webi?

[CdnConnection]

Amrita,

    You will NOT see any BW prompt because you have created a UNV against the BW source.  You now need to create your own prompts with the query.  Only in BI 4.x can you talk directly against the BW source using a BICS connection, then you will get the BW prompts.

Summary:

- In XI 3.1 when you create a OLAP UNV against BW you DO NOT get the BW prompts

- In BI 4.x you can now create a direct BW connection using BICS, you will see the BW prompts.

Regards,

Ajay

[ab81f90e67574787845c8beb7]

Ajay,

I have created this same flow, i.e. BEx query -> BO universe -> Web I report, in other queries that had less num of records and also contained prompts on the BI side.

They are working fine and even the prompts are functional.

In my current scenario, the issue is regarding huge volume of data.

Regards,

Amrita

[Henry_Banks]

again, what are your SP levels for 'optimized interoperability' ? (i.e. to have any chance of success, you need *minimum* BW 7 Ehp1 SP05++ in conjunction with XI3.1 SP04++ )

[ab81f90e67574787845c8beb7]

Hi,

I have BW 7.0 EHP 1 and BO XI 3.1 SP2

[Henry_Banks]

OK, so we're getting closer now..  but what's your BW SP?   it needs to be higher than SP5!

You need to implement all the latest BW MDX interface corrections described in these cumulative notes:

Note 1446246 - MDX: Composite SAP Note for flattening API based on basXML

Note 1156101 - MDX: Composite SAP note for incorrect data

Note 1142664 - MDX: Composite SAP note about performance improvements

Then, same goes for your BOE XI3.1  - you need a higher SP (described above)..

then you can enable all the performance settings i've mentionned above!

Good luck and please do the needful.
Regards,
H

[IngoH]

Hi,

in XI 3.1 you do get the prompts in the Universe based on the BEx variables

regards

Ingo Hilgefort, SAP

[ab81f90e67574787845c8beb7]

Hi,

Actually, I wasn't getting prompts because, in the universe, I had selected to display "LOVs" for the prompts. Because of huge data volume, it might be taking huge time to get the LOVs, because of which I thought that the prompts are not getting displayed.

But, when I removed the LOVs, I got the prompts window immediately.

Regards,

Amrita