Menu
Download Free Trial

ExpandAllData too slow

Hi,
i have a flexmonster pivot table connected with Pentaho Mondrian via XMLA.
When i try to call the expandAllData(false) function (with three dimension selected), Mondrian starts to execute an impressive number of queries (some of which are unnecessary) and the interface doesn’t respond.
I’ll show you an example.
This is my fields configuration. 
http://imageshack.com/a/img922/2190/hePXWj.jpg
When i filter the “Tempo” dimension (Tempo is a hierarchical dimension Year-Month) for Year = 2015, i see only a partial set of the elements of the dimension “Soggetto”.
 
The dimension “Soggetto” normally has 632 Elements (http://imageshack.com/a/img924/6739/OSQExO.jpg) but when i filter the “Tempo” dimension i see only 93 elements on the grid (http://imageshack.com/a/img922/3562/jB97Z4.jpg).
So, I drill the Tempo dimension on the column (Tempo is a hierarchy Year-Month) and call the expandAllData(false) : Mondrian starts to execute the queries for the expansion of the node. The problem is that the queries are executed for all the elements of the “Soggetto” dimension (632) and not for only the elements that are filtered.
In addition, the expansion query is not unique but it is repeated by including each time only three elements of the dimension “Soggetto”. This is the snippet of the query.

select "time_dimension"."year" as "c0",
"time_dimension"."month" as "c1",
"documenti_dim"."id_documento_testata" as "c2",
"soggetti_dim"."id" as "c3",
sum("costi_fact"."costi") as "m0"
from (
...
...
..
) as "soggetti_dim"
where "time_dimension"."year" = 2015 and
"soggetti_dim"."id" in (152, 323, 419) and
"costi_fact"."data_competenza" = "time_dimension"."the_date" and
"costi_fact"."id_documento_testata" =
"documenti_dim"."id_documento_testata" and
"costi_fact"."id_soggetto" = "soggetti_dim"."id"
group by "time_dimension"."year",
"time_dimension"."month",
"documenti_dim"."id_documento_testata",
"soggetti_dim"."id"

At this point, I would like to understand where I’m wrong.
I would be grateful if you helped me: D
 
Greetings

16 answers

Public
Tanya Gryshko Flexmonster March 29, 2017

Hello Davide,
Thank you for the detailed explanation of the issue. We have not managed to reproduce it on our side. Please tell us if this problem is reproducible with Flexmonster Mondrian demo. If not, please provide us with your Mondrian schema and dump of the database – we will test it and try to help you.
Regards,
Tanya

Public
Davide Marro March 29, 2017

Hi tanya, 
thanks for the reply.
Can I directly give you the URL of the provider xml/a? (and the mondrian schema obviously)
 
Greetings

Public
Tanya Gryshko Flexmonster March 29, 2017

Hello Davide,
Thank you for writing. Yes, providing us with the URL of the XMLA provider would help us.
Regards,
Tanya

Public
Davide Marro March 30, 2017

Hi, Can we talk about this on Skype ?
skype contact : davideraffaele.marro
 
Greetings

Public
Tanya Gryshko Flexmonster March 31, 2017

Hello Davide,
To reproduce the issue we need to test your data at first. Hence, please send the URL of the XMLA provider via our Contact Form or pass it to your client service manager. Thank you.
Regards,
Tanya

Public
Dmytro Zvazhii Flexmonster April 3, 2017

Hello David,
We have looked through your data and it seems that there is some inconsistency between the data and the screenshots you sent us above. The Soggetto dimension from your screenshot has six hundred and thirty-two members when the data you have provided us with has only one Soggetto member https://www.screencast.com/t/kDy3ksR8G. Could you please check if the data is correct?
Waiting for your feedback.
Best regards,
Dmytro.

Public
Davide Marro April 4, 2017

Hi Dmytro,
Sorry for the delay in answering.
Sorry, you are right. The catalog is parameterized and I had not replaced the parameter with values.
Can you try now.
 
Thanks
Greetings

Public
Tanya Gryshko Flexmonster April 5, 2017

Hello Davide,
When we try to connect to your data we are redirected to the login form. Please send us username and password so we can check the data. Thank you.
Regards,
Tanya

Public
Davide Marro April 5, 2017

Hi Tanya,
I’m sorry again for the inconvenience.
We deployed the application without allowing access to XMLA.
Sorry. Can You check now.
 
Greetings

Public
Tanya Gryshko Flexmonster April 5, 2017

Hello Davide,
Thank you. We have tested your data. XMLA protocol takes too much time and memory to load and process the data. We suggest you try Flexmonster Accelerator instead. It is a special server-side utility that helps you to increase data loading speed. Please find more details in the documentation: Getting started with Accelerator. Let us know if this helps.
Regards,
Tanya

Public
Davide Marro April 6, 2017

Hi Tanya,
I tried to do the same operations with the Flexmonster Accelerator but i get the same results.
The Loading of the information when i call the expandAll method is very slow. 
How can I improve the performance?
There is an alternative way of expanding the lines?
 
Greetings

Public
Tanya Gryshko Flexmonster April 6, 2017

Hi Davide,

We need some time to investigate possible options for you. I will keep you updated about the progress.

Anyway, we recommend you to use Flexmonster Accelerator because it works much faster and provides better security to your data.

Thanks,
  Tanya

Public
Davide Marro April 6, 2017

My doubt remains the same.
The doubt is that, on the expandAll call, the queries are executed for all the elements of the “Soggetto” dimension (632) and not for only the elements that are filtered.
In addition, the expansion query is not unique but it is repeated by including each time only three elements of the dimension “Soggetto”. This is the snippet of the query.

select "time_dimension"."year" as "c0",
"time_dimension"."month" as "c1",
"documenti_dim"."id_documento_testata" as "c2",
"soggetti_dim"."id" as "c3",
sum("costi_fact"."costi") as "m0"
from (
...
...
..
) as "soggetti_dim"
where "time_dimension"."year" = 2015 and
"soggetti_dim"."id" in (152, 323, 419) and
"costi_fact"."data_competenza" = "time_dimension"."the_date" and
"costi_fact"."id_documento_testata" =
"documenti_dim"."id_documento_testata" and
"costi_fact"."id_soggetto" = "soggetti_dim"."id"
group by "time_dimension"."year",
"time_dimension"."month",
"documenti_dim"."id_documento_testata",
"soggetti_dim"."id"

If the query were performed to the filtered elements, it would run in a short time.
 
The problem could be the structure of the Mondrian schema?
or it is linked to the mdx query generated?
 
Greetings

Public
Tanya Gryshko Flexmonster April 7, 2017

Hi Davide,

I have a trouble when trying to access to your XMLA. Could you please check on your side?

Thanks,
  Tanya

Public
Davide Marro April 11, 2017

Hi Tanya,
Sorry for the delay in answering.
I re-enabled access to the XMLA provider.
Greetings

Public
Tanya Gryshko Flexmonster April 12, 2017

Hi, Davide!

Finally, I’ve tested your data and have some results for you.

  1. When you apply the filter to “Tempo” field it’s normal that you don’t see all rows in the resulted dataset because you don’t have data for each row for the year 2015, component ignores empty rows.
  2. There is no filter in your example so when you run expandAllData it starts to expand all nodes in your dataset. Potentially, in your case, it can be more than 2M rows. If you already drilled 2015 and have 12 months in columns your dataset can be as large as 27M cells. It is no wonder that it takes a lot of time.
  3. All responses from your server are really slow. Probably you have to check your Mondrian settings or improve your server performance. It’s a very common issue for Mondrian to become slow when it doesn’t have enough memory to run queries.

I understand that you expected some fix from our side but honestly, there is nothing to fix. I hope that my answers will help you to resolve the issue.

Best regards,
  Tanya

Please login or Register to Submit Answer