Hello Carlos,
I built a small Mondrian 3 Cube containing only the necessary Dimensions and Measures: mondrian3_schema.xml (see above)
Here my mondrian.propertis (see above) file which I´ve used for this test. I know it´s quite messy because I´ve tried a lot of things to solve my performance problem J
The Query which Saiku builds here is a bit different from the Query above from Mondrian 4, but the effect is the same and we experienced the same behaviour.
I didn´t just paste the mdx-query in the mdx editor but gradually selected my way to the desired result. Meaning, first I only displayed the ordered quantity per year (see line 3 in mondrian.log), selected only the year 2018, added the month to the query (line 412), filtered the month for april, added the day (line 999)…etc…(see saiku_ui.png below)
But the final query starts at 2011.
There seem to actually be two reasons why this query takes so long:
1. There are many SQL-Queries which are sent. Several queries at several levels are being peformed (I guess for caching) which prolong the execution time. See In postgreql.log (114-131) or mondrian.log (2107-2411). The final query would normally take 3-4 seconds. With these additional queries the whole sql-part takes 25 seconds*. Is there a way to turn off these additional queries in the properties?
2. After the SQL-queries are done (see mondrian.log around 2411) it still takes another good 75 seconds until the result is shown in saiku (you see in the picture below (saiku_ui.png) that the total execution time was 99 sec). During this time the memory usage increases a lot (a couple of Gigs). Do you know what is happening here or what I can do that this phase doesn´t take this long?
Cheers,
Werther
* In another environment (different Cube), additionally to these higher level queries, many queries are sent because segments of the result set are loaded. Is there are way of changing the properties (they use a different mondrian.properties than the one I´ve attached here) that only one query is sent instead of several similar queries which return a subset of the main query.