Which Data Source to Choose to Boost Your Pivot’s Performance

Being designed to process and render large volumes of data, Flexmonster Pivot Table & Charts allows you to work with a dataset of any size in a fast and robust way. All companies from small businesses up to worldwide enterprises can benefit greatly from using Flexmonster. Our component easily renders 1 million unique rows, works with OLAP data cubes and Elasticsearch. These are the unique features that distinguish our component from the rest.

We know that one of the most important criteria for choosing the optimal data source for Flexmonster is the amount of data you have. If you have any hesitations about which combination of the data source and Flexmonster will work best for you, take a look at helpful shortcuts we propose to you below:

• Data loading

The main difference between the OLAP and non-OLAP data sources lies in the way the browser loads data. If you use JSON, CSV or SQL databases, you have to load all the data before the component starts displaying any report. Though our pivot table can swiftly handle rendering up to 1 million unique rows, if your data source is too large, it will take some time to load these records due to the browser's memory limitations.

If you use data sources that perform calculations on the server-side, such as MS Analysis Services, Elasticsearch or Mondrian, Flexmonster can handle considerably larger amounts of the data because not all the data is loaded into the browser at once. In this case, you can load only the part of the data required for the specified report. Every time you make a change in the report (e.g, filter, expand, drag and drop hierarchies), a new query to the server is formed. Let’s say you have 20 dimensions and 10 measures in your cube or index. If you choose to display only two dimensions and one measure in the pivot table's report, the component will load only the data that is defined by this particular slice.

• Browser limitations

Flexmonster Pivot has no limitations on the data size (except for Pivot Table for SQL/CSV/JSON Basic edition). Being a client-side component, Flexmonster completely relies on resources available to the browser. This factor affects the maximum size of the data that can be processed on every particular machine.

• Performance

Once component loaded JSON, CSV or SQL database data, it allows you to show it immediately because it already has all the necessary information for pivoting. In the case of OLAP cubes or Elasticsearch indexes, however, Flexmonster has to load chunks of data every time you want to see a new report.

JSON vs CSV

When comparing JSON and CSV data sources, JSON is considered more redundant. However, JSON is easier to work with at scale. If your data comes in one particular format, it’s more convenient to stick with it, as Flexmonster performs excellent with both. If the format of your data can either CSV or JSON, we recommend testing both to see which one works better for a given use case.

Our recommendations

• Use JSON, CSV or SQL databases as a data source if the total number of the unique rows you want to display does not exceed one million.
  Important note: We always recommend using prefiltering on the server-side when connecting to SQL databases.
• Use the OLAP cube or Elasticsearch index if the number of unique rows exceeds one million. As a rule, data redundancy is a problem inherent to OLAP because the XMLA protocol contains a lot of redundant data. To overcome this, our developers created Accelerator – a special server-side proxy to work with OLAP cubes. We recommend using Accelerator when connecting to MS Analysis Services cubes or Mondrian cubes.

We hope you will enjoy our component as much as we do. We are constantly working on making it the best solution for your projects in the market. Follow our blog and get new fresh tips to help you fully evaluate our product and achieve all your defined goals.

Feel free to download the newest version of Flexmonster Pivot Table & Charts here.

And, please don’t hesitate to get in touch with our support team - we are always glad to answer all the questions you have.