Multiple querys vs One query in BIRT report

pavlicic
edited February 11, 2022 in Analytics #1
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

  • mwilliams
    edited December 31, 1969 #2
    Hi pavlicic,

    What version of BIRT are you using?
    Warning No formatter is installed for the format ipb
  • pavlicic
    edited December 31, 1969 #3
    Hi michael,

    I am using RCP BIRT 2.2.1

    Thanks!
  • mwilliams
    edited December 31, 1969 #4
    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.
    Warning No formatter is installed for the format ipb
  • pavlicic
    edited December 31, 1969 #5
    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.
  • pavlicic
    edited December 31, 1969 #6
    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
  • mwilliams
    edited December 31, 1969 #7
    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.
    Warning No formatter is installed for the format ipb
  • pavlicic
    edited December 31, 1969 #8
    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
  • mwilliams
    edited December 31, 1969 #9
    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.
    Warning No formatter is installed for the format ipb
  • pavlicic
    edited December 31, 1969 #10
    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