CubeGrid - Unable to fetch data in paged fashion!

[pankaj.jain]

Hi Isomorphic,

While evaluation CubeGrid on SmartGWT 6.0, we have hit another issue.
We are unable to fetch data in paged fashion for CubeGrid.

We are trying to fetch data in Paged fashion from CubeGrid but setting cubeGrid.setDataFetchMode(FetchMode.Paged) does not help. The same settings work fine for ListGrid.
We want to use sqlLimit as Paging strategy and even after specifying it in the ds.xml the final query does not have Limit and offset appended.
The only thing which works is setting the startRow and endRow in dsRequest explicity on server side, but this means client has to be kept in sync for requesting further pages passing correct start and end rows.

We generate cube with 5 facets, and then fetch the values for the grid combinations post that.
The sample code looks something like: (borrowed from my prev. post at: http://forums.smartclient.com/forum/...ng-as-expected)

It is the fetch in the createCube() method that we would like to page.
Our current code has changed a bit, but the underlying mechanism remains almost same.

package com.pankaj.gwt.example.client.cube;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.data.DSCallback;
import com.smartgwt.client.data.DSRequest;
import com.smartgwt.client.data.DSResponse;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.types.DSOperationType;
import com.smartgwt.client.types.FetchMode;
import com.smartgwt.client.widgets.cube.CubeGrid;
import com.smartgwt.client.widgets.cube.Facet;
import com.smartgwt.client.widgets.cube.FacetValue;
import com.smartgwt.client.widgets.layout.VLayout;

import java.util.ArrayList;
import java.util.List;

public class AdvancedCubeGrid implements EntryPoint {

    private String f1Values, f2Values, f3Values, f4Values, f5Values;

    final String F1 = "F1";
    final String F2 = "F2";
    final String F3 = "F3";
    final String F4 = "F4";
    final String F5 = "TITLE";
    final String ALL = "ALL";

    private CubeGrid cube;
    private VLayout mainLayout;

    @Override
    public void onModuleLoad() {

        getCubeGrid();

        mainLayout = new VLayout();
        mainLayout.setWidth100();
        mainLayout.setHeight100();
        mainLayout.setMembersMargin(8);
        mainLayout.setLayoutMargin(10);

        mainLayout.draw();
    }

    private void getCubeGrid() {
        getCubeFacets();
    }

    private void getCubeFacets() {

        final DSRequest request = new DSRequest();
        request.setOperationType(DSOperationType.FETCH);
        request.setOperationId("fetchf1");
        request.setShowPrompt(true);
        request.setWillHandleError(true);
        final DataSource dataSource = DataSource.get("cubeData");

        dataSource.fetchData(null, new DSCallback() {
            @Override
            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                f1Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F1);
                request.setOperationId("fetchf2");
                dataSource.fetchData(null, new DSCallback() {
                    @Override
                    public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                        f2Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F2);
                        request.setOperationId("fetchf3");
                        dataSource.fetchData(null, new DSCallback() {
                            @Override
                            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                                f3Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F3);
                                request.setOperationId("fetchf4");
                                dataSource.fetchData(null, new DSCallback() {
                                    @Override
                                    public void execute(DSResponse dsResponse, Object data, DSRequest dsRequestOld) {
                                        f4Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F4);
                                        request.setOperationId("fetchf5");
                                        dataSource.fetchData(null, new DSCallback() {
                                            @Override
                                            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequest) {
                                                f5Values = CubeUtil.getStringFromData(dsResponse.getDataAsRecordList(), F5);
                                                createCube();
                                            }
                                        }, request);
                                    }
                                }, request);
                            }
                        }, request);
                    }
                }, request);
            }
        }, request);
    }

    private void createCube() {
        cube = new CubeGrid();
        cube.setWidth100();
        cube.setHeight100();
        cube.setDataFetchMode(FetchMode.LOCAL);
        cube.setHideEmptyFacetValues(true);

        List<Facet> facetList = new ArrayList<>();
        facetList.add(getFacet(F1, "Origin", f1Values, true));
        facetList.add(getFacet(F2, "Destination", f2Values, true));
        facetList.add(getFacet(F3, "Quarter", f3Values, true));
        facetList.add(getFacet(F4, "Month", f4Values, true));
        facetList.add(getFacet(F5, "Values", f5Values, false));

        cube.setAutoFetchData(true);
        cube.setFacets(facetList.toArray(new Facet[facetList.size()]));
        cube.setValueProperty("VAL");

        cube.setRowFacets(F1, F2);
        cube.setColumnFacets(F3, F4, F5);

        DataSource dataSource = DataSource.get("cubeData");
        cube.setDataSource(dataSource);
        [B]cube.setFetchOperation("fetchCubeData");[/B]

        mainLayout.addMember(cube);
    }

    private Facet getFacet(String facetName, String facetTitle, String facetValues, boolean isTree) {
        Facet facet = new Facet();
        facet.setId(facetName);
        facet.setTitle(facetTitle);
        facet.setIsTree(isTree);
        facet.setCollapsed(false);
        facet.setValues(getFacetValues(facetValues, !isTree));
        return facet;
    }

    private FacetValue[] getFacetValues(String facetValuesString, boolean isFixedFacet) {
        List<FacetValue> facetValues = new ArrayList<>();
        if (!isFixedFacet) {
            facetValues.add(new FacetValue("sum", ALL));
        }
        String[] facetValuesArray = facetValuesString.split(",");
        FacetValue facetValue;
        for (String facetString : facetValuesArray) {
            if (!isFixedFacet) {
                facetValue = new FacetValue(facetString, facetString, "sum");
            } else {
                facetValue = new FacetValue(facetString, facetString);
            }
            facetValues.add(facetValue);
        }

        for (FacetValue facet : facetValues) {
            facet.setWidth(100);
        }

        return facetValues.toArray(new FacetValue[facetValues.size()]);
    }
}
​

package com.pankaj.gwt.example.client.cube;

import com.smartgwt.client.data.RecordList;

public class CubeUtil {
    public static String getStringFromData(RecordList recordList, String propertyName) {
        StringBuilder stringBuilder= new StringBuilder();
        for(int i = 0 ; i < recordList.getLength(); i ++) {
            stringBuilder.append(recordList.get(i).getAttribute(propertyName));
            if (i < recordList.getLength() - 1) {
                stringBuilder.append(",");
            }
        }
        return stringBuilder.toString();
    }
}

<DataSource
        ID="cubeData"
        tableName="CUBE_DATA"
        serverType="sql"
        dbName="Oracle">
    <fields>
        <field name="F1" type="text"/>
        <field name="F2" type="text"/>
        <field name="F3" type="text"/>
        <field name="F4" type="text"/>
        <field name="TITLE" type="text"/>
        <field name="VAL" type="integer"/>
    </fields>

    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchCubeData"></operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf1">
            <selectClause>
                DISTINCT F1
            </selectClause>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf2">
            <selectClause>
                DISTINCT F2
            </selectClause>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf3">
            <customSQL>
                SELECT DISTINCT F3
                FROM CUBE_DATA
                ORDER BY
                CASE F3
                WHEN 'Q1' THEN 1
                WHEN 'Q2' THEN 2
                WHEN 'Q3' THEN 3
                WHEN 'Q4' THEN 4
                END
            </customSQL>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf4">
            <customSQL>
                SELECT DISTINCT F4
                FROM CUBE_DATA
                ORDER BY
                CASE F4
                WHEN 'JAN' THEN 1
                WHEN 'FEB' THEN 2
                WHEN 'MAR' THEN 3
                WHEN 'APR' THEN 4
                WHEN 'MAY' THEN 5
                WHEN 'JUN' THEN 6
                WHEN 'JUL' THEN 7
                WHEN 'AUG' THEN 8
                WHEN 'SEP' THEN 9
                WHEN 'OCT' THEN 10
                WHEN 'NOV' THEN 11
                WHEN 'DEC' THEN 12
                END
            </customSQL>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetchf5">
            <selectClause>
                DISTINCT TITLE
            </selectClause>
        </operationBinding>
    </operationBindings>
</DataSource>

Is there a way to force fetching of the data for the cube in paged fashion?
I am also assuming that once this works fine, the export of this data should also not be a problem? (We are facing problem when exporting data from client currently, getting STATUS_MAX_POST_SIZE_EXCEEDED using the showcase code: http://forums.smartclient.com/forum/..._size_exceeded)

Thanks!

[Isomorphic]

CubeGrids fetch data according to sets of facetValues, generally requesting only the data that is currently visible to the user (except for your settings of dataFetchMode:local and hideEmptyFacetValues).

There is no meaning of "row number" for cube data, as the same data can be pivoted in various ways such that row numbering is completely different. To use paging based on row numbers, you'd have to have a complete representation of the current visual arrangement of the CubeGrid on the server - but that's obviously nonsense. The current approach of fetching limited data via use facetValues as criteria is much simpler.

[pankaj.jain]

Thanks for the reply.

I am just a bit confused about your first statement:

CubeGrids fetch data according to sets of facetValues, generally requesting only the data that is currently visible to the user (except for your settings of dataFetchMode:local and hideEmptyFacetValues).

I assume this means that the data is fetched to the client from the server depending on what is visible to the user, and not to the application from the database? (Since currently, the entire data is fetched for us from the database!)

Also, is there a way to know what facets are currently visible on the screen, so that we fetch the data only for those respective facets?

What we currently plan is:
1. Fetch all the facets just as we are fetching right now, keep all the requests as is, except the final request.
2. The final request, where we fetch all the data for the cube (in our createCube() method), we modify that to fetch only the data for the facets that is currently visible to the user.
3. On the cube scroll event (vertical or horizontal scroll), we fire new fetch request to fetch the data for the newly visible set of facets.

Would this approach work? Or is there something better that you would suggest?
Also, we would still like to use hideEmptyFacetValues() as this helps us to eliminate unwanted rows/columns. Still possible to use it with the above approach?

[Isomorphic]

As we just covered, you have set dataFetchMode:"local", and if you read the docs for that, it forces all data to be fetched up front.

If you're having trouble understanding how facetValue-based load on demand works, watch the requests and responses that happen in the Advanced Cube sample. Again the built-in behavior of the CubeGrid is to send dsRequests for data where facetValues are used as criteria. There is no custom server-side code in the Advanced Cube sample - it's just using the built-in criteria handling of every DataSource.

And again bear in mind, hideEmptyFacetValues settings will cause more data to be fetched, by necessity - see the docs for that attribute for details.

[pankaj.jain]

Got it working now.
Thanks!

[pankaj.jain]

Hi,

Well, we solved the data paging issue, however, still we face another issue because our facets are not paged!
As in, when the facets are loaded, it loads all the facets right in the beginning, which could easily be around 500-600 entries per facet! (Can even go upto 2000-3000).
Thus, when we load multiple facets, even though the data values are paged, the facet entries itself becomes so heavy, that Chrome ends up taking 500-600 MB of memory, or at times, running out of memory when loading cube.

I tried running the advanced cube sample code (from showcase) assuming that it would be paging the facet values, but I don't think it is getting paged! (Reason for this being, when i scroll, i see queries being fired for data values, and it takes time to refresh. However, the facets values are always present - or maybe they are just refreshed so quickly, that there is Zero lag!?).

I am confused as to how do i ensure that the facet values get paged and not fetched all at once on the client.
Our code has a few minor changes from above, notably we use Fetchmode.PAGED and we don't use hideEmptyFacetValues() property anymore.
Any pointers/tips?

Thanks!

[Isomorphic]

A Facet definition consists of a set of facetValues that generally have just an ID and title. Loading a few facets with several hundred data members each should not be a problem and should not result in the memory consumption you claim.

One thing you may be doing is running in GWT Developer Mode? This causes more than double the memory consumption. It is 100% useless to do any kind of memory usage or performance testing in this mode - test in compiled mode only.

See also the Memory Leaks overview to see other ways that memory usage can be falsely inflated (as this heavily overlaps will false memory leaks).

Finally, you mentioned a facet with 2-3k members. Although this should still not be a problem in terms of memory consumption except on older machines, from a usability, scalability, and performance perspective, you want an interface that allows this facet to be appropriately filtered before the cube is displayed.

[pankaj.jain]

One thing you may be doing is running in GWT Developer Mode?

Not actually, I am running the analysis on the application deployed on Tomcat, and noticing this memory usage.
In the attachment, we have 3 facets (Origin, Destination and a facet for value headers). The first 2 facets, each have around 4.5K facet items.
Given the fact that the we provide the user with total of 5 facets to be selected, the memory consumption shoots up like crazy! In the current screenshot, the memory usage on the deployed application was around 500 MB.
Please note that the values (numbers) are paged and loaded dynamically only when the user scrolls.

See also the Memory Leaks overview to see other ways that memory usage can be falsely inflated (as this heavily overlaps will false memory leaks).

I'll have a look at it.

...from a usability, scalability, and performance perspective, you want an interface that allows this facet to be appropriately filtered before the cube is displayed.

Not sure I understand this! What I need, is some way to load the facet items dynamically (page them so that they load only when the user scrolls to the resp. page/grid location)
Any way to load these facets items dynamically, or any sample application/link you can point me to?
Also, what do you exactly mean by filtering? Is it reduction in the amount of facet items we display, or is it dynamically loading facet items from server?

Thanks!

[Isomorphic]

There is no point in creating a cube with something like 60 *million* cells (4500x4500x3 for your metric facet), that's not a usable interface. This is still true even if hierarchical facets are used to limit the initial number of cells - having to drill through a deep hierarchy to see the data you want is still a very poor UI.

Instead, you want some kind of interface that allows users to search or select from these large facets, so that when they arrive at the cube, they are looking at the immediately relevant data. This is what we meant when we said:

Finally, you mentioned a facet with 2-3k members. Although this should still not be a problem in terms of memory consumption except on older machines, from a usability, scalability, and performance perspective, you want an interface that allows this facet to be appropriately filtered before the cube is displayed.

.. and if you survey actual business analytics products, you will find that all of them do this, and most of them have warnings about creating cubes that are too large to be usable.

There is no point in pursuing load on demand of facet values because it can only lead to unusable interfaces - and that's aside from all the issues that arise in terms of auto-sizing, selection, hierarchies and LOD (see Tree DataBinding overview), extra requests, and other ways in which adding LOD of facetValues to a Cube necessarily degrades the interface.

If you instead take the approach of giving the user a way to reduce the facets before they arrive at the cube (which is again, industry standard), you will have a better UI, better performance, and no problems with memory consumption.