Pivot tabel - Expands and drills with custom datasource API

Thanks, we understand the current API call implementation was designed primary for single item user control.
No possibility to expand/drilldown effectively a dimension level is a blocker issue for us. There are several similar questions on this issue in your questions pages. It seems expansion of whole dimension (hierarchy level) was already solved for other datasource types (json, csv) and the SELECT API method is also enabled to filter more items in one request so we dont understand the reason why its not possible to request the additional data effectively for both cases (one item or bulk of items defined by tuples array just using different filters).
Is there a workaround to solve this problem?  We could use expandAll which generates one SELECT but the items are too many and custom API as effective data source doesnt make no sense (compared to JSON), or extremely optimize the backend - but still there remains problem with network response time and the SELECT iteration for hundreds of items (is it possible to run the requests in parallel ?).
The possibility to use effectively expands and drilldowns with large datasets using custom API was one of the main reasons we chose flexmonster for one solution.
 
 

Thank you very much for possible solution of our problem. The only other workaround we have is expandAll with JSON format data without custom API, which limits the index size for some of intended use cases.
We hope this new implementation for custom API could also improve usability for other customers.
We think the offered solution is useful for us. To ensure we understand it exactly the same way you defined it - Does your solution of expand/drill-down correspond to this scheme ? :
 
POST request by Flexmonster =
[
{
"type": "select",
"index": .. index name (the same one for all in request array) .. ,
"query": {
"aggs": {
"by": {
"rows": [
.. all first level fields + fields of current expanded or drilled down item in row
],
"cols": [
.. all first level fields + all fields of current expanded or drilled down item in column
]
},
"values": [
... standard values definition ...
]
},
"filter": {
"type": "and",
"value": [
{
"field": {
"uniqueName": ... top level expaned or drilled-down field (hieararchy level) ..
},
"include": [
.. filters up to lowest level of expanded or drilled down item ..
]
}
]
}
},
"querytype": .. "expand" or "drilldown" ,
"page": 0
},
{
... other SELECT object for each expanded or drilled down item in row or column ...
},
....
]
 
ressponse by custom API backend =
{
"aggs": [
.. all key combinations + their values corresponding to ALL expanded/drilled-down select request objects ...
]
}
 

Hi Nadia,
thanks for commitment to make this update in Flexmonster.
Just a notice to this statement:
"but the response is supposed to be the array of separate /select responses. However, the approach of receiving all the data in one object is also possible."
Both type of responses may be implemented on our server side but one array of "aggs" has more advantages and is preffered by us. 
To mention the most important: There would be many duplicate data in the response in case of more separated arrays for each "select" - The same parent tuples with values would be present in all expanded/drilled-down items' responses.
regards Tomas

Hello,
thank you for information, do you have a new estimate of schedule for this task ?
We have to report to our customer and finalize our application rollout,
regards Tomas Vrsek

Hello,
thank for the solution you provided.  We understand you want to keep architecture of your application stable in this aspect. 
Now we have to optimize our application for many small requests (queries, cache) so that your solution could make sense with our backend, and also make functional tests the updated version of Flexmonster Pivot,  then we can give some feedback,
regards Tomas
 
 

Hello,
I've first results of testing your new expand solution and modified version.
a) In our testcase, even by manual drill-down of hiearchy, the pivot-table locked after after first SELECT request and the browser received succesful response (200). The same one with previos Flexmonster version works. We will continue debugging this but it seems its related to the change (? some threads related problem).
b)  As you declared the API functionality and contract is the same as before. So we tested the SELECT sequences in order to optimize our backend. There is still some problem, which may be blocking for the expands. It can be also reproduced on your own testcase.
Not only all the data are requested and sent twice, if there are drills in rows and expands in columns at once. Thats not fine but may be acceptable.
But for expands there are sent NO filters on predefined drills. As a result - values for all members  of drilled-down field are returned even if they are not in drills list.
For fields with many members in the second hierarchy level (for example when only one or two parent members are drilled-down) a magnitude more of useless data are prepared and transfered for every expanded member.
Even in this example with your backend and simple drill-downs you can see the "expand" requests and the result (the expand response is several times larger than drilldown response):
https://jsfiddle.net/ft47g91k/
Shoud we enter another ticket, or is this ok this way?
 
Regards, Tomas
 
 
 

Hello, thanks for answer,
let's describe the problem b] in more detail. The declared behavior isn't related to our new version with parallel threads but can be also perfectly reproduced in current version on your site.
Also it's not happening by expand settings in API slice only but also by manual actions. So its bug of any expand action in combination with drills in other dimension (field).

In this jsfiddle example (your backend and data, your flexmonster version) we declared slice with more drills (3 members) and expands (2 members):
https://jsfiddle.net/9wzobh5r/   
This slice setting is producing tens of request on start. The drillDown queries look fine - in every request there is a filter on drilled member (like category=cars + destination=canada). The combination of drilled and expanded members are requested. [Just one more idea - Aggregation of completely same queries to one query using filter with include clause would be really better for performance improvement and overhead reduced - we would appreciate it, and it would be no API change.]
On the other hand the expand queries dont include filter for applied drills in the "Category" field :
{"type":"select","index":"fm-product-sales","query":{"aggs":{"by":{"rows":[{"uniqueName":"Category"},{"uniqueName":"Color"}],"cols":[{"uniqueName":"Destination"},{"uniqueName":"Country"}]},"values":[{"func":"sum","field":{"uniqueName":"Price"}}]},"filter":{"type":"and","value":[{"field":{"uniqueName":"Destination"},"include":[{"member":"Australia"}]}]}},"querytype":"expand","page":0}
So the result for this query contains all values for "Category"+"Color", which are not drilled and invisible , like this and many more ("Category": "Clothing" is not drilled down):
{
"values": {
"Price": {
"sum": 957
}
},
"keys": {
"Category": "Clothing",
"Color": "green",
"Destination": "Australia",
"Country": "France"
}
}
Even in this simple case, the response is several times larger than it should be. But the problem gets worse when applied to real data. For example we have hierarchy of county (14) and municipality (cca 100 in each county). One drilled-down county in rows (produces 100 detail lines) with some other expanded field "B" in columns (for example 1 expanded column -> 30 columns) makes sense for the user.
This one expand could return 3000 values. But the "expand" query for each member of field "B" in column with county filter omitted produces 42000 values, 90% useless. This is much worse if the hierarchy has more than two levels.

Hello Solomilia,
we have worked on some test, the queries are running concurrently but the overall performance is still poor.  The overall speed-up is 2-3x compared to non-parrallel version. We have to solve problems not previously expected at the server side when tens or hundreds queries are run per one user opening the table  (overhead per request, thread count, database connections). The server-side performance doesnt depend much on value-count in the result (for tens or hundreds of values), not much difference when returning 5 or 200 values.  So for our backend this style of calls is quite unfortunate, thinking about additional caching, interconnecting the independent sibling request in the backend etc but its a lot of work with doubtful results to solve this single problem.
On the other hand we cant use the JSON format data source because there is a need for deep expand and drilldowns at some level.
Therefore I would like to ask for some hint, isnt there really an other way to workaround this problem, for example populate the table side-way using the frontend-API. We have full control of the frontend and backend and can prepare own calls to feed initial data to the client in effecient way.   Or is there some way to run expandAll and then add other fields to rows and columns (for user expands) without initial data reload?
In addition to this performance problem, we are experiencing problems with drillDowns in slice (expands are OK). In majority of cases, the drillDown queries are sent to backend (in the same form as in manual mode) but the table component doesnt show the drilled rows. This is happening also without concurrentRequest and are trying to find a way to reproduce it in jsFiddle example so you could see it.
 
 
 
 
 
 
 

Hello,
thank you for your care. Yes , we have tested the expand/drilldown behaviour. We have delayed the answer because we weren't able to make a JSFiddle example before (it was necessary to install working backend to external environment).
We have 2 issues with drill-downs, expands:
a) drill-down function using slice doesn't work well (the visual part):
https://jsfiddle.net/jxvcptu3/
With this definition in our backend, there is a multi-level hierarchy, using hierarchy with name ""Území podle okresů" and attached set of hierarchy fields. As it's returned by /fields.
We define a drill-down in slice, all backend methods, even the drilldown /select is called and our data returned but the pivot table doesnt drill-down  visually . 
If you make the same drilldown in GUI, on the same member, everything works, and the /select methods are called the same way as in slice.
b)  There is still a performance problem after your fix with expands combined with drill-downs, it works another way than before (drills are taken in account) but still the queries are not correct.
https://jsfiddle.net/3tgjrcfz/
In this example, there is hierarchy in rows and one field to expand in column.
If you:
a) drilldown the hierarchy in rows first (more levels , one item per each level is the best example use-case) and then
b) expand the first column item and watch the requests -  more SELECT expand request are called. Its obvious its one expand call per each drilldown item in the column.
The problem is this - Each expand /select call contains all row fields of the lower-most drilled-down level.  So for the top level item of hierarchy this (subtotal) request contains all field which were expanded , just with the filter on the top level field "uniqueName": "UzOkr_STAT_NUTS".
This leads to big size SELECT of all data rows up to the lower-most level of hierarchy because the filter is not corresponding to rows (hierarchy levels) in the request.
If you try it other way - expand the column first and then drill-downs  (same viewed data in the result), the drill-down selects are correct (for each drilldown level the column list corresponds to drilldown filter).
 
Problematic /SELECT request parts:
...
"rows": [{"uniqueName": "UzOkr_STAT_NUTS"},{"uniqueName": "UzOkr_OBLAST_NUTS"},{"uniqueName": "UzOkr_KRAJ_NUTS"},{"uniqueName": "UzOkr_OKRES_NUTS"},{"uniqueName": "UzOkr_CISOB"}
],
....
"filter": {
"type": "and",
"value": [{"field": {"uniqueName": "UzOkr_STAT_NUTS"},
"include": [{"member": "19"}]},
.....
 
Thanks for advice. Please try this example till next friday, there will be somw firewall changes then and dont know if the backend will be accessible after.
regards Tomas

Hallo, thanks for your attention. We have tested the new version and both issues seem as resolved succesfully:

• slice with fields using number codes drills down, 
• the cross-queries SELECTs with drilldowns and expands provide usefull filters.

We have still problem with high SELECT queries count in some cases, but this is to be resolved at our side. Maybe if we encounter some duplicate queries, i will send you some info for possible performance improvements.