asked on March 28, 2017
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
⋅ 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
⋅ 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
⋅ March 29, 2017
Hello Davide,
Thank you for writing. Yes, providing us with the URL of the XMLA provider would help us.
Regards,
Tanya
⋅ March 30, 2017
Hi, Can we talk about this on Skype ?
skype contact : davideraffaele.marro
Greetings
⋅ 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
⋅ 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.
⋅ 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
⋅ 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
⋅ 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
⋅ 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
⋅ 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
⋅ 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
⋅ 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
⋅ 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
⋅ April 11, 2017
Hi Tanya,
Sorry for the delay in answering.
I re-enabled access to the XMLA provider.
Greetings
⋅ April 12, 2017
Hi, Davide!
Finally, I've tested your data and have some results for you.
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
⋅ April 27, 2017
Ok Thank You
Greetings
