After we design a visualisation we can export the SQL command. We see that the SQL is inefficient and we can write a better one which will give expected results or will execute faster.
Is there a way to replace the query inside database so that the visualisation uses the new query? Or is there a possibility that from custom DHIS2 app we replace the query and execute the visualisation?
Hi @Mahmud
As far as I know you can’t replace sql queries without forking the backend. Would you like to tell us more about how you think it can be improved?
Hi @jan ,
We have a report which need to shows ‘Element - Sum(Value)’ for a given period. Now, when we export the raw SQL we see that the query is in the following form -
select <fields> from datavalue dv inner join dataelement de on
dv.dataelementid = de.dataelementid inner join period pe on
dv.periodid = pe.periodid inner join periodtype pt on
pe.periodtypeid = pt.periodtypeid inner join organisationunit ou
on dv.sourceid = ou.organisationunitid inner join categoryoptioncombo coc
on dv.categoryoptioncomboid = coc.categoryoptioncomboid inner join
categoryoptioncombo aoc on dv.attributeoptioncomboid = aoc.categoryoptioncomboid
where dv.dataelementid in (7524,6432,6462,8358,5393,6667,8382,5741,6383,5885...)
We actually have selected the elements from an ‘Element Group’ having N number of elements. 2 major issues here -
The report is kind of statically bound to the ‘previously selected N element IDs’ - so adding more elements in the element group does not have any effect. We have to come to each and every report containing those element groups (interesting, when you come back to the visualiser design, how do you know that it was from that group?), find the new element and add it in the report.
We found a limit of around 400 elements can be added in a report because of the SQL command length limitation, and you understand having an ‘in’ clause in an SQL with this many values is not efficient also.
It is just my assumption that the SQL command is created and then executed to have the analytics hold the data and then show it in reports. I can propose 2 things -
A. Just before execution if the SQL can be updated then it can be changed to -
select <fields> from <table list> where Elementid in (<select elements from specific group as subquery>)
This would solve issue 1 and 2 above.
B. The long term option can be in the visualiser design where if you find that someone selected an element group and selected all of those - we can mark that all elements of that group are requested, and we make the sql as option A by default.
Expecting your valuable feedback.
@Mahmud If I understand you correctly you want to dynamically select all data elements in a group - in the same was as you can select all orgunits at a specific level or group. That’s a good suggestion. I’m afraid there’s no way to do that currently in Data Visualizer. You can select the data element group as the dimension, but that would only give you the total of all the elements.
@jan
I guess asking to replace the query was too much 
Let me again give a simple example and try to see what would be the best workaround -
Say you have an element group containing ‘Major HIV Regimens’ and you select 5 top ones, and you make multiple visualizations selecting the elements from that group. Now, after a few months one more regimen is added in the major regimens group, all the visualizations need to be manually updated by adding that extra regimen element and refresh.
This is the only way, right?