Retrieving huge quantities of records in one report

building reports webintelligence
1

Hi everyone,

I’m experiencing this issue with some users that would like to run reports at low-level detail, so that retrieved data could be difficult to manage by BO caching system.

I am trying to find a solution to make their reports feasible: the first idea has been to split them in more than one document, creating, for example, one report per region.
Anyway this is not a good solution in my opinion, and I’m trying to explore different ones.

Here comes my question:

If I have to retrieve maybe 3 millions of records in on WebI Document, is it the same to retrieve them with one single query, or is it better to run more smaller ones (each with a different filtering condition to make it retrieve only a part of needed data) and then to build up (in same document) different report pages, populated by data from different queries?

Thanks everyone for your help.

Regards,
Paolo

p.baldini (BOB member since 2009-03-10)

2

With 3 million records to be retrieved you are bound to encounter performance problems (when the Webi document is rendered).

Why are you needing 3 mio. records?
3 mio records (100 records per page) equals 30.000 pages.
I got to ask: Who is scanning through 30.000 pages of data? :crazy_face:

Andreas :de: (BOB member since 2002-06-20)

3

I know that’s not “The best thing” :roll_eyes: …
what have I to do? users are making such reports… :hb: and asking me to let them run! :hb: :hb:
than it will be up to them to look at data and find what they need in such a huge doc! :twisted:

p.baldini (BOB member since 2009-03-10)

4

I suggest showing the users how one can start with a summary view and then DRILL down your way to more detailed data.

Or use OpenDocument.jsp to call a another document and passing query parameters to the child document, this is also drilling.

Andreas :de: (BOB member since 2002-06-20)

5

Sorry, isn’t the drill option some kind of post-elaboration done after query data retrieve?
To dril down a report you must have designed your query to retrieve all needed dta, anyway.
Is this wrong?

About your secnod advice, I must say I do not know how does it works… could you show me some liks to any documentation about that?

Thanks once more
Bye

p.baldini (BOB member since 2009-03-10)

6

Drilling:
You have to setup hierarchies in the universe, you can then choose (via document properties I believe) if you want to pre-fetch data down to a particular drill level, or you can have a new query executed in the background if someone is drilling down a hierarchy with the drill filter set as a condition in the SQL statement.
Workflow example:

  • User opens document, data is shown at Region level.
  • User drills down on Region EMEA
  • BusObjects transparently fetches all countries only for region EMEA (SQL code) and displays the results.

OpenDocument:
Look here for example. It is also documented in the BusObjects Designer guide I believe.

Andreas :de: (BOB member since 2002-06-20)