Custom CubeGrid Database Layout

[samliew94]

Hi Devs,



The project I'm currently working on requires the use of CubeGrid.

The image below shows how I get the GUI to respond to the Database structure.


The code below shows the CubeGrid object (advancedCube) code that sets the all the columns, rows and data between the GUI & Database.

ds = testVcmCubegridDs.getDataSource();
                setDs(ds);

                ds.fetchData(null, new DSCallback () {

                    @Override
                    public void execute(DSResponse dsResponse, Object data, DSRequest dsRequest) {
                        RecordList rl = dsResponse.getDataAsRecordList();                            

                        if (rl.isEmpty() == true)
                        {
                            rl.addList(preloadTime());
                        }

                        advancedCube.setData(rl);
[B]                       advancedCube.setColumnFacets("Date","Time");
                        advancedCube.setRowFacets("Header","Header2");
                        advancedCube.setValueProperty("Value");
                        advancedCube.setCellIdProperty("cellID");   [/B]

The issue with my approach currently is that I foresee there would be too many data to load (inefficient).

As demonstrated above, 8 rows is required to display the values at only Time 08:00 (In reality there are 31 rows by default).

If I needed to show all the values from Time 08:00 to Time 09:00 (1 hour difference) with an increment of 5 minutes, the amount of rows I need to produce in my Database would be:

31 x (60/5) = 372 rows of data for 1 hour.

From the calculation above, if I need to display data from 08:00 - 23:00 (15 hours difference), I would need to produce:

31 * [(60*15)/5] = 5,580 rows of data.

Doesn't seem like my higher-ups and clients will be happy with that many rows of data.

So they suggested that I alter my codes to match the database design below (I used paint to demonstrate):



Any ideas in how I could achieve this?



Regards,
Sam


==================
Environment Details

Google Chrome
SmartGwt 6.1p EE
==================

[Isomorphic]

Your pictures did not come through.

The CubeGrid requires one Record per cell shown to the user. You can generate these from any database design you like; they need not correspond to actual database rows. However, in typical database designs that actually make sense to navigate with a CubeGrid (orthogonal dimensions that can be viewed in any order and pivoted), there will be at least one database row per cell.

[samliew94]

Hi, sorry. I'll reupload the two pictures again.

1:


2:


In case the pictures didn't manage to load again (image by url):
1: (url)
2: (url)


From picture no.2, technically it is still a one-record-per-cell, just that I merged 4 columns into 1.

Something like a JSON data format.


Regards,
Sam

[Isomorphic]

Thanks for following up with the pictures. Let us know if you have further questions after reviewing the advice we already gave.

[samliew94]

Hello Isomorphic,

I solved the issue, however it seems like the rows duplicated itself, even though the JSON data seems to be correctly inserted.

The image below shows the outcome I would like to achieve and what is actually being displayed.



The code below shows how I structure my JSON code.

{  
   "response":{  
      "status":0,
      "startRow":0,
      "endRow":31,
      "totalRows":31,
      "data":[
          {
            "cellID": "1",
            "Header": "EVENTS",
            "Header2": "",
            "Date": "18/1/2018",
            "Time": "8:00",
            "Value": ""
          },
          {
            "cellID": "2",
            "Header": "DRUGS",
            "Header2": "",
            "Date": "18/1/2018",
            "Time": "8:00",
            "Value": ""
          },
          {
            "cellID": "3",
            "Header": "GASES",
            "Header2": "O2(%)",
            "Date": "18/1/2018",
            "Time": "8:00",
            "Value": ""
          },
          {
            "cellID": "4",
            "Header": "GASES",
            "Header2": "N2O",
            "Date": "18/1/2018",
            "Time": "8:00",
            "Value": ""
          },
          {
            "cellID": "5",
            "Header": "GASES",
            "Header2": "AIR",
            "Date": "18/1/2018",
            "Time": "8:00",
            "Value": ""
          }
        ]
   }
}

Each cellID should represent 1 row. In this case, I should only get 5 rows as displayed above ("^What I would like to achieve^"), but I get the one next to it ("^what I get^").

Nothing changes much on the cubeGrid object settings:

cubeGrid.setColumnFacets("Date","Time");
cubeGrid.setRowFacets("Header","Header2");
cubeGrid.setValueProperty("Value");
cubeGrid.setCellIdProperty("cellID");

Is this a bug or an error on my side? Please advice, thanks.


Sam

[Isomorphic]

This is way too little information, but if you have not already looked at the docs for hideEmptyFacetValues, this may be the problem.

[samliew94]

Hi,

I added hideEmptyFacetValues() along with setHideEmptyAxis() as mentioned in the doc:

DataSource ds = cubeGrid.getDataSource();         
         ds.fetchData(null, new DSCallback() {

            @Override
            public void execute(DSResponse dsResponse, Object data, DSRequest dsRequest) {
                // TODO Auto-generated method stub
                RecordList rl = dsResponse.getDataAsRecordList();            
                cubeGrid.setData(rl);
                cubeGrid.setColumnFacets("Date","Time");
                cubeGrid.setRowFacets("Header","Header2");
                cubeGrid.setValueProperty("Value");
                cubeGrid.setCellIdProperty("cellID");

                cubeGrid.setHideEmptyAxis(Axis.ROW);
                cubeGrid.setHideEmptyFacetValues(true);

                getThis().addMember(cubeGrid);
                getThis().addMember(createButtons());                
            }
        });

Slightly unrelated, but I added some values to the JSON file. The outcome is still the same:


I used the RestDataSource with JSON example from the wiki (link) as a reference.

Other than that, I couldn't figure out what other information that could be linked this issue.


-Sam

[Isomorphic]

The only sample of your data that you've shown has several records where your valueProperty ("Value") is "". That would cause blank cells. Omit such records entirely instead.

[samliew94]

Slightly unrelated, but I added some values to the JSON file. The outcome is still the same:

Hi,

I've added sample values("1","2","3","4","5") in the JSON file, as indicated in the picture in my previous post.

The only field that has empty values is the field ("Header2");

cubeGrid.getTotalRows()

shows that I have 12 rows,

while a

dsResponse.getDataAsRecordList().getLength()

would show that I have 5 records only, which matches the JSON file.

Upon further inspection, I realized that other than the 5 rows with data, all the other rows/cell are null.

I also realized that the setHideEmptyFacetValues() function only applies to Facets, in this case the fields only. The valueProperty is not a Facet.

I thought of the only solution is to hide the rows where it's null, but I couldn't find the function to do so.

Your advice is highly appreciated.



Regards,
Sam

[Isomorphic]

hideEmptyFacetValues hides facetValues where there are no records that have that combination of facet values in the data (as the docs say).

We show this working in samples...

You should revisit your code and carefully compare it to the sample to figure out what's wrong. If you think it's a framework bug, you should modify the sample to reproduce the issue you're seeing. As long as you continue to not show complete code and data, there's obviously nothing anyone can do for you.