The community of Analytics developers are invited to follow and participate in the developer network.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.

Multiple querys vs One query in BIRT report

pavlicicpavlicic Junior Member
edited December 31, 1969 in Designing reports
Hi lads,

I have one question i think most of you had developing a BIRT report.

I want to do a report with 5 tables and 5 charts. This report will use just one fact table with at least 5 dimensions (could be more). Fact table has millions and millions of rows. Tables will contain one dimension and differente measures (one dimension each tables) and charts will be the same.

My question here is: what is better, multiple queries (i.e. one per table or chart) or just one query (and then use data cube to aggregate values)?

I am bit confused about this.

With one query, i can aggregate values and use data cube to generate the 5 different tables. For chart, as I think, i can not use data cubes and i have to use a data set with grouping functions.

With multiple queries, i can develop tables and charts very easy with just the values I need, but will be attacking the DB 5 times (fact table is huge).

What are your opinions about this? And your experiences?

Thanks in advance

Comments

  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    Hi pavlicic,

    What version of BIRT are you using?

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • pavlicicpavlicic Junior Member
    edited December 31, 1969
    Hi michael,

    I am using RCP BIRT 2.2.1

    Thanks!
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    pavlicic,

    Ok. Since you're using 2.2.1, running the 5 smaller queries would actually be faster. The reason is that the way the data is handled in 2.2 is that for each report element, the query is run, so you'd be running the large query multiple times which would really slow things down.

    If you upgraded to 2.3, the data is handled differently and running the 1 big query would probably then be the faster of the two.

    Hope this helps.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • pavlicicpavlicic Junior Member
    edited December 31, 1969
    Hi Michael,

    Thanks a million for your answer. Do you know if there is any place in BIRT doc where talks about how BIRT run queries? I will look for it, but i ask just in case you know the answer quickly. If not, don't worry, will be looking for it.

    Anyway, thanks again, that is one of the important things to be aware of.
  • pavlicicpavlicic Junior Member
    edited December 31, 1969
    another silly question related to this.

    Imagine that i have one table, with 3 dimensions and 2 measures. Then, i create a group by first dimension (to emulate a section) and in the group footer, i put a chart based in "Inherit Data from Container". And after the chart, another table with other data (same dataset) but still inside the main table (in the group footer or table footer).

    In this case, we will be emulating a section with 3 elements. Then, does BIRT query the DB 1 time (just for the main table) or 3 times (main table, chart and second table)?

    Thanks in advance
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    pavlicic,

    I believe that before BIRT 2.3, this would still run the query again, even thought the chart gets its data from the container. I could be wrong, so if I find out differently, I'll post back in here.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • pavlicicpavlicic Junior Member
    edited December 31, 1969
    hi michael,

    thanks for your answer. I will try to find the way of looking at the queries BIRT is doing (don't know how to do it yet...), so if i find something, will let you know..

    thanks again
  • mwilliamsmwilliams BIRT Guru
    edited December 31, 1969
    pavlicic,

    I'm pretty sure that what I said before is correct about it not mattering if the chart was using the data from the container prior to 2.3. The query is always ran twice. One thing you could do to track things being ran in your reports and when they're being ran is to use a logging function that writes out to a log file when script events happen.

    Regards,

    Michael Williams

    eSignLive Evangelism & Community Manager | eSignLive by VASCO
     
    Find me on:
    Twitter
    Facebook
    Blog
    LinkedIn

    eSignLive Developer Community
     
    Email me:
    Google: [email protected]

  • pavlicicpavlicic Junior Member
    edited December 31, 1969
    Hi everybody,

    I did some tests using a variable X in "initialize" event in the report, which is incremented in 1 every time "afterClose" event in DataSet is raised. Then, at the end of the report, i show that variable in a Text field.

    I have one table grouped by 2 fields and 2 aggregations. If i put a chart in the lower level of grouping using same data set, data set is refreshed just one time. But if i put a table using fields of that dataset, it is refreshed Y times, when Y is the number of group headers (aggregated rows), which could be thousands.

    So, looked at this, i think i won't do any more effort trying to optimize the report and will do all the queries necessaries till we can upgrade to BIRT 2.3.

    Thanks everybody for your answers
Sign In or Register to comment.