Custom Datasource API: Lazy loading of Flat Table View

posted on March 26th, 2021

Answered

Parmod asked on March 26, 2021

Hi Flexmonster Team,
I am using the Custom Datasource API and everything is working fine with the Pivot. But when it comes we do flat view(select-request-for-flat-table) form the Pivot, we are facing problem of loading data into the flat table.
Record size : 1,000,000(approx rows)
Flat table page size: 10,000
Request to server : 100
Problem:
Until all the pages are downloaded from the server flat view doesn’t display anything on the UI.
We can’t wait for that long(approx 30-60 min depending on network speed and how may fields need to display).
For Flat Table View in Custom API Datasource, we want lazy loading mechanism on scrolling the Flat View to download more data based on the Total page number.
I don’t know about rest of the api. But for Flat View on Custom API Datasource It make sense to have lazy loading feature because all the computation we are doing on the database/server.
Regards,
Parmod

17 answers

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ March 26, 2021

Hello, Parmod,

Thank you for reaching out to us.

Currently, Flexmonster does not support lazy loading for the custom data source API.

Still, loading 1M records for 30-60 minutes seems to exceed expectations even when loading the whole dataset at once (without lazy loading or paging). Our team assumes this may be due to the server layer's bottleneck (for example, slow database querying) or specifics of the data itself.

Do you think you could provide us with a sample dataset (use dummy data if needed) so that we could test it on our side?

Also, it would be useful for us to understand how the server operates with the data. For example, does it load the data in advance and store it in the RAM, or query the data storage every time Flexmonster performs a request.

We are looking forward to hearing from you.

Best regards,
Illia

Public

Parmod ⋅ March 26, 2021

Hi Illia,
The maximum timing taken by the database to process the query.
Testing:
drillthroughMaxRows: 50000
Total Records: 1,199,760
Number of Fields: 2
Time taken around 20 minute.
Average time of execution: 29 seconds.
Q: it would be useful for us to understand how the server operates with the data?
A: We follow below steps
1. After receiving the request from UI we create a database query from it and execute the query.
2. After getting the response(result set) from the database, we pull row in sequential manner from the result set.
3.Convert the row to create Flexmonster Response and flush that data to ui.
4. Repeat step 2 and 3 until we get all the records from the db.
Q: does it load the data in advance and store it in the RAM
A: Its not feasible for us, because the data set size is in Tera Bytes. So everything is done with the query. Only Flexmonster Response generation is handled on the Server side.
One more thing, In general we scroll to 2-5 pages of Flat view data only. So if lazy loading support is provided it will help use to reduce the server calls and we can reduce the "drillthroughMaxRows"
to 1000 or 500 so that user will get fast response.
Please see the attachments.
Regards,
Parmod

Attachments:
dummyPage.json
FlatView Network Response.PNG
FlatViewHeader.PNG

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ March 29, 2021

Hello, Parmod,

Thank you for providing us with the details.

We agree that lazy loading is a nice feature to have. Still, it is a complex feature, and our roadmap is full at the moment. Therefore, we are not planning to add it in the nearest future.

Still, we suggest checking if anything can be improved on the server-side.

For example, make sure you receive unique data from the data storage only once.
As far as we understand, you query the database every time Flexmonster makes a request.
Does this query return the whole dataset, which is split into pages afterward?
Or, maybe, the query returns only the part needed for the specific page?

Please make sure you do not query the database for the whole dataset every time Flexmonster requests a page. It would mean that only the part of the data is used.

Instead, we suggest querying the database only once (after the first request) and pass it to Flexmonster page by page.
Another approach is to query the database only for a specific number of records needed for the requested page.
In both cases, we suggest preparing the next page in advance while transferring the current one to the client-side. According to the provided waterfall chart, it would minimize the TTFB and increase speed up to two times.

Also, we want to draw your attention to the network speed demonstrated on the chart.
The approximate speed seems to be approximately 1-1.5Mb per second. Please note that regardless of whether pagination is implemented, this speed significantly affects the loading process.

We hope it helps.
Please contact us in case other questions arise.

Regards,
Illia

Public

Parmod ⋅ March 30, 2021

Hello Illia,
Question: For example, make sure you receive unique data from the data storage only once.
As far as we understand, you query the database every time Flexmonster makes a request.
Does this query return the whole dataset, which is split into pages afterward?
Or, maybe, the query returns only the part needed for the specific page?
Answer: For 'Flat Table View' if the db have duplicate value then we need to show same value on UI to support the underline pivot value. We are doing everything with the help of query i.e Pagination, Pivot(cube) query.
And on the server side we are just generating the Flexmonster response in the the streaming manner and flushing the data to UI on the basis of records.

Statement: Please make sure you do not query the database for the whole dataset every time Flexmonster requests a page. It would mean that only the part of the data is used.
Answer: We are using Paginated SQL query to fetch the record. So we don't need to fetch unnecessary data.

Suggestion: Instead, we suggest querying the database only once (after the first request) and pass it to Flexmonster page by page.
Another approach is to query the database only for a specific number of records needed for the requested page.
In both cases, we suggest preparing the next page in advance while transferring the current one to the client-side. According to the provided waterfall chart, it would minimize the TTFB and increase speed up to two times.
Answer: The database size is in Tera Bytes so caching the database response to server is not an option for us and we are getting multiple request from same user(browser) as well as different user. If we do something you suggested it will eat up entire resources and make the server slow or unresponsive. To counter the above scenario we are using prepared statements.

Statement: Also, we want to draw your attention to the network speed demonstrated on the chart.
The approximate speed seems to be approximately 1-1.5Mb per second. Please note that regardless of whether pagination is implemented, this speed significantly affects the loading process.
Answer: If we have a lazy loading feature I know the loading time will not be less even it will be more but we have multiple benefits
1. User do not need to wait for 20-30 min to view data. User have at least some data to view.
2. User might not see the entire data. So we have less server\database calls.
Or can we show data while it is loading rest of the pages from the server.
Regards,
Parmod

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ March 31, 2021

Hello, Parmod,

Thank you for the detailed response.

Our team understands the benefits of the pagination functionality for your case.
Still, we are not ready to provide you with any estimates due to mentioned obstacles.

Concerning the following question from your recent email: "Or can we show data while it is loading rest of the pages from the server" we want to explain that Flexmonster requires the whole dataset in order to compose the internal structure and render the grid afterward.

However, we want to suggest the workaround you may find useful for your case.
In your previous email, you mentioned that usually, only 2-5 first pages of the data are used.
Do you think you could request only the first 2-5 pages from the database and pass them to Flexmonster as if it was the whole dataset? In case the user needs the rest of the data, he can request the entire dataset/its bigger part manually.

It is even possible to allow choosing the number of records before requesting the data.
The required number of pages/records can be inserted in the request made by Flexmonster with capabilities of the customizeAPIRequest method or sent as a preflight request.

Please let us know if you find this workaround useful.
Do not hesitate to contact us in case other questions arise.

Regards,
Illia

Public

Parmod ⋅ May 13, 2021

Hi Illia,
Is there any mechanism to get the 'drillThrough' request and open my custom 'Report' instead of Flexmonster's drillThrough view.

If possible can you please provide and example for that.
Regards,
Parmod

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ May 13, 2021

Hello, Parmod,

Thank you for your question.

If we understand correctly, your requirement is to display a custom pop-up with some custom data that would be based on the clicked cell.

If so, we suggest using the following approach.

Disable the built-in drill-through feature. It is achievable by setting the drillThrough property of the options object to false.
Subscribe to the celldoubleclick event. This event passes the Cell Data Object to its handler.
Create the corresponding handler that would be responsible for creating the desired pop-up.
Prepare the data to display in the created pop-up. If it needs to be based on the clicked cell, use information stored in the received Cell Data Object. For example, you can send a custom request to the server and retrieve the desired data.
Display the data inside the created pop-up.

Please let us know if this approach works for you.
In case we did not understand your requirement correctly, please provide some details/illustrations of the desired functionality.

Looking forward to hearing from you.

Regards,
Illia

Public

Parmod ⋅ May 14, 2021

Hi Illia,

Yes, you have understand it correctly.

The problem here is we have already develop the code which accept the drillThrough request and generate the response.

We want to re-utilize the same mechanism to generate the response for custom Report with pagination.

If user double click the cell as we do for drillThrough view, we want to capture the request and send it to custom resource to generate the Report.
i.e

{
  "index": "PivoteName",
  "filter": [
    {
      "field": {
        "uniqueName": "Month"
      },
      "include": [
        {
          "member": "JAN"
        }
      ]
    }
  ],
  "fields": [
    {
      "uniqueName": "Month"
    },
    {
      "uniqueName": "Weight"
    }
  ]
}

Is there any mechanism where I can capture the request and send it to custom resource?

Or can I use https://jsfiddle.net/flexmonster/vrfhb68o/ mention by 'Vera Didenko' in https://www.flexmonster.com/question/custom-datasource-api-error-message-from-server/ and use a function base approach for datasource i.e 'pivotEndpoint'?
One more thing how can I know that this request is drillThrough when using function base approach for datasource 'pivotEndpoint'

Regards,
Parmod Singh Rana.

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ May 17, 2021

Hello, Parmod,

Thank you for your feedback.

In order to preserve the request's structure, the following approach can be used:

Send a request through the mentioned proxy function.
Check if the request is performed for a drill-through view (its type property equals select, `query` does not have aggs property).
If the request is not performed for a drill-through, simply return the received data.
Otherwise, return an empty response of the corresponding structure. Flexmosnter will be unavailable until all the requests are satisfied.
Use an actual received data to build and display the desired report.

To enhance this workaround, we suggest also closing the opened drill-through window right after the drillthroughopen event is fired:

flexmonster.on("drillthroughopen", () => {
  document.querySelector("#pivotContainer .fm-drillthrough-view .fm-ui-btn-close").click();
});

Also, apply the following CSS rule to avoid flickering:

#pivotContainer .fm-drillthrough-view {
  display: none !important;
}

Replace the bold parts with an actual id of the Flexmonster's container.

The complete workaround is illustrated in the following JSFiddle we prepared: https://jsfiddle.net/flexmonster/pbqjvd0u/.

Please let us know if it works for your case.
Looking forward to hearing from you.

Regards,
Illia

Public

Parmod ⋅ May 17, 2021

Hi Illia,

Thanks for the help and example. It is working.

Regards,
Parmod

Public

Parmod ⋅ May 18, 2021

Hi Illia,

Is there a way to call the drillTrough-view with using javascript code.
I have tried runquery, it only accept rows, columns, measures and reportFilters from Slice .

Regards,
Parmod

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ May 18, 2021

Hello, Parmod,

Thank you for your question.

We want to explain that you can drill through only by double-clicking the cell. There is no possibility to do it programmatically.

Please contact us if other questions arise.

Regards,
Illia

Public

Nil ⋅ October 1, 2021

Hi Flexmonster ,
Can we have lazy loading now?

Public

Parmod ⋅ October 1, 2021

Hi Nil,

We have custom Report framework to display record with pagination.

Regards,
Parmod

Public

Nil ⋅ October 1, 2021

Hi Pramod,
Can you please provide the link for it?
Is is compatible with flexmonster pivot in frontend?

Public

Parmod ⋅ October 1, 2021

Hi Nil,

We are using pivotEndpoint function: https://jsfiddle.net/flexmonster/vrfhb68o/ mention by ‘Vera Didenko‘ in https://www.flexmonster.com/question/custom-datasource-api-error-message-from-server/ and use a function base approach for datasource i.e ‘pivotEndpoint’

pivotEndpoint(requestStringified, successCallback, errorCallback) {
  const request = JSON.parse(requestStringified);
  if (request.type === 'select' && isEmpty(request.query.aggs)) {
    queryString = JSON.stringify(request.query);
    // logic to call custom Report framework based on queryString
    successCallback({
     'fields': [],
     'hits': [],
    });
  } else {
   // default flow
  }
}

You need to add below css please check above reply by Illia

flexmonster.on("drillthroughopen", () => {
  document.querySelector("#pivotContainer .fm-drillthrough-view .fm-ui-btn-close").click();
});

Regards,
Parmod

Public

Illia Yatsyshyn ⋅ Flexmonster ⋅ October 1, 2021

Hello, Nil,

Thank you for your question.

We want to explain that lazy loading is not available for the flat form. We suggest considering the approach mentioned by Parmod in the current thread.

Feel free to reach out if other questions arise.

Kind regards,
Illia

Custom Datasource API: Lazy loading of Flat Table View

17 answers

Please login or Register to Submit Answer