DevExtreme HTML5 Pivot Grid Remote Data Processing

This post covers the DevExtreme Pivot Grid widget, which is available for jQuery, as an Angular component, ASP.NET MVC and ASP.NET Core MVC controls, and as a Knockout binding.

In the past (prior to v16.2), a connection to an OLAP cube (an XMLA store) was the only way to use the Pivot Grid with large data sources. Configuration and use of OLAP cubes are complicated, and client-side data processing can’t easily be applied because of the limited memory and performance available to browser runtimes as well as the simple fact that large data volumes would need to be transferred to the client in the first place. Server side data aggregation without an OLAP cube is the technique of choice for this scenario.

Starting with DevExtreme v16.2, you can connect the Pivot Grid to a relational data source in server mode to perform filtering, grouping and summary calculation operations on the server without using OLAP or Analytic services.

If you enable server mode by assigning true to the remoteOperations option, the Pivot Grid sends filtering, grouping and summary calculation options to the server and waits for the processed data.

Comparing performance

We compared loading time and network traffic volume when processing data locally and remotely. Here’s what we did, in case you would like to reproduce our tests.

We created a database with the following table:

CREATE TABLE [dbo].[Categories] (
  [CategoryID] INT IDENTITY (1, 1) NOT NULL,
  [CategoryName] VARCHAR (10) NULL,
  [RegisterDate] DATETIME NOT NULL,
  [Count] INT NOT NULL,
  [Price] DECIMAL (18, 2) NOT NULL,
  CONSTRAINT [PK_dbo.Categories] PRIMARY KEY CLUSTERED ([CategoryID] ASC)
);

We added the following indices to the table to optimize server-side grouping:

CREATE INDEX CategoryIndex [dbo].[Categories] (CategoryName);
CREATE INDEX DateIndex [dbo].[Categories] (RegisterDate);

We fill the table with random data, including 100 unique CategoryName values and a RegisterDate range of three years.

We configured the Pivot Grid like this:

$("#container").dxPivotGrid({
  ...
  dataSource: {
    ...
    fields: [
      { dataField: "RegisterDate", dataType: "date", area: "column" },
      { dataField: "CategoryName", area: "row" },
      { dataField: "Price", summaryType: "sum", area: "data", 
        format: { type: "currency" } }
    ]
  }
});

When measuring performance, we used the DevExtreme.AspNet.Data library to process data on the server. Below are a comparison table and charts showing the results.

As you can see, client-side processing slows down dramatically with increasing record count, and becomes quite unusable with data sets of one million records. Remote data processing works fine even if the record count is 3 million. Note also how the data transfer volume remains almost constant for remote operations, while it obviously increases quickly for local operations. Of course, the Pivot Grid in server mode can process data sets containing many more than 3 million records, with loading time depending only on database configuration and server performance.

Try it!

We have published a sample that demonstrates how to configure the Pivot Grid widget to work with a remote WebAPI data service. Additionally, there is the DevExtreme PHP Data Library, which allows you to use the Pivot Grid Remote Data Processing feature with a mysql database and a PHP server. Finally, it is possible to run a JavaScript based server accessing a MongoDB database using this library.

We maintain a list of links to samples in the devextreme-examples GitHub repository.

Please let us know your thoughts about this feature and our results!

Free DevExpress Products - Get Your Copy Today