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

dataSourceToChoose

Flexmonster Pivot Table & Charts Component allows you to work with a large volume of data. It was designed to render big data 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 and works with OLAP data cubes, which are the unique features distinguishing our component from others.

The most important criteria for choosing the optimal data source setup for Flexmonster is the amount of data you have – your data volume. If you have some hesitations about which combination of the data source and Flexmonster will work the best for you – take a look at useful shortcuts we propose you below:

  • Data loading

The main difference between OLAP and non-OLAP data sources is when the browser loads data. If you use JSON, CSV or SQL databases, you have to load all data before component starts displaying any report. Our pivot table quickly handles data up to 1 million unique rows. However, if your data source is larger, it will take some time to load these records.

If you use OLAP data sources, such as MS Analysis Services or Mondrian, Flexmonster can handle much larger data because not all data is loaded into the browser at once. You have the ability to load just part of the data necessary for the specified report. For example, you have 20 dimensions and 10 measures in your cube. If the report has only two dimensions and one measure to be displayed in the pivot table, the component will only load data to display this particular slice.

  • Browser limitations

Flexmonster Pivot does not have any limitations on the size of the data (except for Pivot Table for SQL/CSV/JSON Basic edition). Being a client-side component, Flexmonster relies on resources available to the browser, and this affects the maximum size of the data that can be handled on every particular computer.

  • Performance

Once component loaded JSON, CSV or SQL database data, it allows you to show it immediately because it already has all necessary information for pivoting. In case of OLAP cubes, 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 better at showing hierarchical data and 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 great with both. If the format of your data can be CSV or JSON, we recommend testing both to see which one works better for a given use case.

Using CSV data source has an additional perk: we have compression features that allow sending compressed data. This decreases data size significantly. Our special algorithm optimizes data and allows compressed CSV files to be loaded and analyzed 3-4 times faster comparing to simple CSV files.

Our recommendations

  • Use JSON, CSV or SQL databases with up to 1 million unique rows.
    Note 1. For large CSV files, we suggest using Compressor to gain better performance.
    Note 2. Connection to SQL databases is always done via Compressor which guarantees high data loading speed.
    Note 3. We always recommend using prefiltering on the server-side when connecting to SQL databases. This article explains the concept in details.
  • Use OLAP cube if the number of unique rows is more than 1 million. Usually, data redundancy is a problem for OLAP because XMLA protocol contains a lot of unnecessary data. 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 better for you. Follow our blog and get the new tips to help you fully estimate our product and achieve all targets.

Feel free to download the newest Flexmonster 2.5 version here.

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