Get Free Trial
Get Free Trial

Loading Data, Large Datasets, and Formatting Fields

Steve Pietrek asked on February 23, 2019

Hello,
I am trialing Flexmonster for my client who needs a rich pivot table. So far I have been impressed with performance and functionality. I did have a few questions.

  1. I would like to format fields to include a caption and format; however, I do not want to show them initially in the grid. In the code below, I am showing Age and Gender in the rows array; however, I also have id, g_id, state, etc fields. I would like to specify caption for those other fields but not show them initially. The user could select the fields if needed. With a measure, I can set active to false. It does not look like rows/columns has that capability? Is this possible?
  2. I will be retrieving content from an API and loading into Redux in the React application. For my testing, I am using Axios to retrieve content from a json file in my public folder. It works the majority of the time; however, for very quick requests, although I am loading a report and defaulting an empty dataset, the grid sometimes displays one row field and 1 measure (see attached) rather than 2 rows and 10 measures. Usually the problem happens when I update my code when I am on a separate browser tab and then switch back. Refreshing the page solves the issue or updating the code when the app is in the current browser tab generally always renders correctly.
  3. The loading dialog uses spaces for thousand separator instead of comma. How do I change it to use the user’s location’s preference?
  4. The application could be using tens of millions of rows. What recommendations do you have for handling large datasets? Loading a smaller amount of records and appending data? Is there a way to specify a contract based on the current report layout that can be passed to the api, loaded, and retrieved? Any other hints?
  5. Is there a way to turn off row sorting indicators?

 
import React, { Component } from ‘react’;
import * as FlexmonsterReact from ‘react-flexmonster’;
import axios from ‘axios’;

interface IGridFlexProps {}

interface IGridFlexState {
quickFilterText: string;
sideBar: boolean;
}

class GridFlex extends Component<IGridFlexProps, IGridFlexState> {
private pivot: any;

constructor(props: IGridFlexProps) {
super(props);

this.state = {
quickFilterText: ”,
sideBar: true,
};

this.pivot = React.createRef();
}

public componentDidMount() {
const requestUrl = ‘/data/100k.json’;
const request = {
url: requestUrl,
timeout: 200000,
method: ‘get’,
headers: {
accept: ‘application/json;odata=verbose’,
‘content-type’: ‘application/json;odata=verbose’,
},
};

axios(request)
.then(response => {
const { data } = response;
const formattedData = this.getData(data);
this.pivot.current.flexmonster.updateData({ data: formattedData });
// this.pivot.current.flexmonster.setReport(this.reportJson);
})
.catch(error => {});
}

private getDefaultData = () => {
return [
{
id: 0,
g_id: 0,
gen: ”,
eth: ”,
race: ”,
age: ”,
state: ”,
etg: 0,
a1c: 0,
met: 0,
inf: 0,
onc: 0,
etgCalc: 0,
a1cCalc: 0,
metCalc: 0,
infCalc: 0,
oncCalc: 0,
},
];
};

private getData = (data: any) => {
if (data instanceof Array) {
// const concatData = data
// .concat(data)
// .concat(data)
// .concat(data)
// .concat(data)
// .concat(data)
// .concat(data)
// .concat(data)
// .concat(data)
// .concat(data);
const concatData = data.splice(0, 1000);
return concatData.map(item => {
return {
…item,
etgCalc: item.etg > 0 ? 1 : 0,
a1cCalc: item.a1c > 0 ? 1 : 0,
metCalc: item.met > 0 ? 1 : 0,
infCalc: item.inf > 0 ? 1 : 0,
oncCalc: item.onc > 0 ? 1 : 0,
};
});
}
return [];
};

private reportJson = {
dataSource: {
dataSourceType: ‘json’,
data: this.getDefaultData(),
},
slice: {
rows: [
{
caption: ‘Age’,
uniqueName: ‘age’,
sort: ‘asc’,
},
{
caption: ‘Gender’,
uniqueName: ‘gen’,
sort: ‘asc’,
},
],
columns: [],
measures: [
{
uniqueName: ‘etgCalc’,
caption: ‘etg #’,
aggregation: ‘sum’,
active: true,
format: ‘count’,
},
{
uniqueName: ‘etgPercent’,
formula: ‘percentofcolumn(“etgCalc”)’,
individual: false,
caption: ‘etg %’,
active: true,
},
{
uniqueName: ‘a1cCalc’,
caption: ‘a1c #’,
aggregation: ‘sum’,
active: true,
format: ‘count’,
},
{
uniqueName: ‘a1cPercent’,
formula: ‘percentofcolumn(“a1cCalc”)’,
individual: false,
caption: ‘a1c %’,
active: true,
},
{
uniqueName: ‘metCalc’,
caption: ‘metformin #’,
aggregation: ‘sum’,
active: true,
format: ‘count’,
},
{
uniqueName: ‘metPercent’,
formula: ‘percentofcolumn(“metCalc”)’,
individual: false,
caption: ‘met %’,
active: true,
},
],
drillThrough: [
‘id’,
‘g_id’,
‘age’,
‘gen’,
‘race’,
‘eth’,
‘etg’,
‘a1c’,
‘met’,
‘inf’,
‘onc’,
],
},
options: {
viewType: ‘grid’,
grid: {
type: ‘compact’,
title: ”,
showFilter: true,
showHeaders: true,
fitGridlines: false,
showTotals: true,
showGrandTotals: ‘on’,
showExtraTotalLabels: false,
showHierarchies: true,
showHierarchyCaptions: true,
showReportFiltersArea: true,
pagesFilterLayout: ‘horizontal’,
},
chart: {
type: ‘line’,
title: ”,
showFilter: true,
labelsHierarchy: ”,
multipleMeasures: false,
oneLevel: false,
autoRange: false,
reversedAxes: false,
showLegendButton: false,
showAllLabels: false,
showMeasures: true,
showOneMeasureSelection: true,
showWarning: true,
activeMeasure: ”,
},
configuratorActive: false,
configuratorButton: false,
configuratorMatchHeight: false,
showAggregations: true,
showAggregationLabels: false,
showCalculatedValuesButton: true,
editing: false,
drillThrough: true,
showDrillThroughConfigurator: true,
sorting: ‘on’,
datePattern: ‘MM/dd/yyyy’,
dateTimePattern: ‘MM/dd/yyyy HH:mm:ss’,
saveAllFormats: false,
showDefaultSlice: true,
showEmptyData: true,
defaultHierarchySortName: ‘asc’,
selectEmptyCells: true,
showOutdatedDataAlert: false,
},
formats: [
{
name: ”,
thousandsSeparator: ‘,’,
decimalSeparator: ‘.’,
decimalPlaces: 0,
maxSymbols: 20,
currencySymbol: ”,
currencySymbolAlign: ‘left’,
nullValue: ‘ ‘,
infinityValue: ‘Infinity’,
divideByZeroValue: ‘Infinity’,
},
{
name: ‘count’,
thousandsSeparator: ‘,’,
decimalSeparator: ‘.’,
decimalPlaces: 0,
},
],
};

private onCustomizeToolbar = (toolbar: any) => {
const tabs = toolbar.getTabs();
toolbar.getTabs = () => {
delete tabs[0];
delete tabs[1];
delete tabs[2];
delete tabs[3];
delete tabs[4];
delete tabs[4];
delete tabs[7];
return tabs;
};
};

private onCustomizeCellFunction = (cell: any, data: any) => {
if (data.hierarchy && data.hierarchy.uniqueName === ‘id’ && data.member) {
const el = document.createElement(‘div’);
el.innerHTML = cell.text;
const parts = cell.text.split(el.innerText);
if (parts.length === 2) {
cell.text =
parts[0] +
“<a href=’https://codepen.io/spietrek-the-animator/pen/exjwdw’ ” +
“target=’_blank’ class=’link’ onclick=’preventExpand(event)’>” +
el.innerText +
‘</a>’ +
parts[1];
} else {
cell.text =
“<a href=’https://codepen.io/spietrek-the-animator/pen/exjwdw’ ” +
“target=’_blank’ class=’link’ onclick=’preventExpand(event)’>” +
el.innerText +
‘</a>’;
}
} else if (
data.isDrillThrough &&
data.type !== ‘header’ &&
data.hierarchy &&
data.hierarchy.uniqueName === ‘id’
) {
cell.text =
“<a href=’https://codepen.io/spietrek-the-animator/pen/exjwdw’ ” +
“target=’_blank’ class=’link’ onclick=’preventExpand(event)’>” +
data.label +
‘</a>’;
}
};

public render() {
return (
<div>
<FlexmonsterReact.Pivot
ref={this.pivot}
toolbar={true}
beforetoolbarcreated={this.onCustomizeToolbar}
componentFolder=”https://cdn.flexmonster.com/”
width=”100%”
report={this.reportJson}
customizeCell={this.onCustomizeCellFunction}
/>
</div>
);
}
}

export default GridFlex;

Attachments:
emptygrid.png

3 answers

Public
Dmytro Zvazhii Flexmonster February 25, 2019

Hello Steve,
Thank you for your questions. We are grateful for your kind words. Please find our answers here:
 
1. The formatting is only applicable to the measures. Therefore, as you have already mentioned, you will need to define the formatting and set isActive: false. As for the other fields, you can define the captions as part of the JSON metadata object. Please refer to our docs for more information: https://www.flexmonster.com/doc/data-types-in-json/.
 
2. As for the second paragraph, such behavior appears when you are trying to run updateData() API call but Flexmonster’s instance is not created yet. Flexmonster runs different tasks during its initialization process and some of them are asynchronous. In your case, componentDidMount is performed before Flexmonster’s instance is actually loaded. Therefore, we recommend using Flexmonster events to avoid such situations. It is better to use reportcomplete event for your case. For more information please refer to the following article: https://www.flexmonster.com/api/reportcomplete/. The reportcomplete will be triggered when the pivot with the default data is loaded. It means that the component is ready to listen to your requests. After that, you can run the update data API call.
 
3. Currently, Flexmonster does not allow applying the formatting to the numbers in the pop-up window when loading the data. Could you please specify how critical such option is for you?
 
4. Tens of millions of data is a huge amount of data. Probably, even half of such data set can crush clients browser. For such cases, we do not recommend using simple data sources such as JSON and CSV since Flexmonster uses browsers memory to store the data. That will lead to a consistent browser’s crashing. When it comes to such huge data sources it is necessary to delegate as many calculations as possible to the server side. For such cases, we recommend choosing between SSAS and Elasticsearch data sources. Such an approach allows working with massive data sets and allows performing all the data analysis operations such as drilling, expanding, sorting and filtering in the runtime. You are welcome to try our demos: http://www.flexmonster.com/demos/connect-msas/http://www.flexmonster.com/demos/connect-elasticsearch/.
 
5. In case you need to disable sorting indicators, please set options.sort: false. Please find more information about our options properties here: https://www.flexmonster.com/api/options-object/.
 
Feel free to contact us in case you need more guidance from us.
Regards,
Dmytro

Public
Steve Pietrek February 26, 2019

Thanks Dmytro. 

  1. Thanks. I missed this capability.
  2. Thanks. I updated my code and it is working as expected.
  3. Not a show stopper.
  4. Sorry for my confusing question. I agree we would not display 10’s of millions of records in the grid. I was curious if you had infinite scrolling like ag-grid (https://www.ag-grid.com/javascript-grid-infinite-scrolling/) or server-side row model like ag-grid (https://www.ag-grid.com/javascript-grid-server-side-model/)? The plan was to have a NodeJS API which would return JSON. We would have an in-memory dataset which the grid would hit.
  5. Thanks. Is it possible to turn off row sort but leave column sort? 
  6. Is there the ability to filter the grid across all columns/rows? Or would you rather filter the in-memory dataset and then update the grid’s data?
Public
Dmytro Zvazhii Flexmonster February 27, 2019

Hello Steve,
Thank you for your feedback.

4. The infinite scrolling is not supported by Flexmonster. When the JSON data is loaded into the component Flexmonster builds its own cube. Therefore, no other external requests are needed since the component operates with the whole data set. That is the benefit of such an approach since all the operations are performed instantly without the need to send requests to the server-side. At the same time, such an approach limits the maximum JSON sizes as the browser storage is also limited. Flexmonster does not support server-side row model the way ag-grid does. Our component uses the server-side model in its own way. When you use either SSAS or Elasticsearch data source Flexmonster does not load the entire data set into the browser. It requests from the data storage the exact data slice which is necessary for displaying the current state. The data comes already aggregated and it saves a lot of space in the browser’s storage. For instance, when you open drill-through view a new request for data is performed. Flexmonster composes the query itself and maintains the state in its own way. Such a distributed approach allows working with larger data sets then the JSON approach. Still, it needs making a data request when the new data is required.

5. You can set the “sorting” property with “columns” value and the sorting will only be available for the columns.

6. When you use JSON data set the entire data set is processed with the browser and loaded into the browser’s memory. Therefore, it can definitely be filtered across all rows and columns since all the data is available. Also, it is possible to change the sequences of actions and apply the report with the filter and a small data set first. After that, you can extend the data source by updateData() approach. That will lead to pretty much the same result as in the first approach. Also, please note that when the data set exceeds the browser’s memory limitations we recommend making some pre-filtering operations on the server side.
 
Please let us know in case of any other question.
Regards,
Dmytro

Please login or Register to Submit Answer