How to choose the best data source to use with Flexmonster
The variety of data sources for Flexmonster makes it easier to find the perfect option for your project. At the same time, choosing the right data source is very important and can influence your experience with Flexmonster. So this article is here to help you make the right choice.
The selection of the data source depends on different factors:
- Size and format of the data.
- Complexity of data manipulations.
- Need for advanced features like drill-down.
- Performance requirements of the project.
- Technology stack of the project.
When choosing a data source that will suit your business needs, you should determine the main requirements and prioritize them in advance. We collected a set of questions to help you define your main points:
By answering these questions, you can clearly outline the key criteria for your project’s data source. The next step is to determine which approach is better in your case: to process all data on the client or on the server.
Client-side data sources
Client-side data sources load the whole dataset over the network at once.
The data is parsed and aggregated on the client's machine, and then the component is ready to serve different slices of data on the run.
Being a client-side component, Flexmonster relies on resources available to the browser. Because of that, the method isn't ideal for handling extremely large datasets. The amount of data a client machine can handle depends on its RAM, while the speed of data aggregation depends on the CPU's performance.
But, if a large dataset is just your case, we recommend you consider the server-side approach.
Server-side data sources
Flexmonster can delegate data aggregation to the server side to manage large data amounts and use fewer browser resources.
With server-side approaches, a new query is formed on every change in the report, like expanding or filtering data, and data is loaded by parts. As a result, the component receives only the data it needs to visualize, and everything else remains on the server side. This helps to optimize both memory usage and component speed.
Of course, the amount of RAM and processor power of the client machine also matters. However, in server-side approaches, a fast and stable network connection is what will maintain communication between the server and the client.
Flexmonster provides several ready-to-use server-side tools for some data sources. Further in this article, we will tell more about each of them. It is also possible to create your own solution, giving you more control over data processing.
Now that you know the difference between client-side and server-side approaches, let's have a look at specific options that Flexmonster offers.
CSV or JSON
If you usually work with small datasets, up to 100 MB, then our CSV/JSON approach will be ideal for you. It supports the largest subset of the component's features and, as a result, is the most flexible in implementation into the project.
The big advantage of this approach is that the data from any data source can be transformed into a CSV or JSON file and then be given to Flexmonster.
You can:
- Display JSON data that is already on your page.
- Load JSON or CSV data from your local file system.
- Refer to a remote file.
- Add data generated by a script.
- Fetch data from the API.
When to use:
- You work with small datasets, up to 100 MB.
- You need raw, non-aggregated data in a flat table view.
- You need an easy-to-implement and easy-to-start solution.
The approach might not fit you if:
- You have a data source bigger than 100 MB. With such an amount of data, you can experience delays in loading and much slower work of the pivot table.
- You need custom aggregation logic.
In such cases, we recommend switching to Flexmonster Data Server — our server-side tool, which can process files of more than 1 GB. It has built-in support for JSON and CSV data sources and can help browsers handle much bigger files.
What is Flexmonster Data Server?
Flexmonster Data Server is a server-side tool developed by our team. It optimizes the work with SQL databases and JSON/CSV files, as it does all the data processing.
While the Data Server is fetching and aggregating the data, Flexmonster Pivot gets only to visualize the ready-to-show data. Thus, the data is visualized faster, and the load on the browser is reduced.
For the .NET stack, you can reference the Data Server as a DLL – this will allow you to extend the Data Server to suit your needs. For example, you can implement your own parser, making it possible to connect to any data source of your choice.
Flexmonster Data Server is a good option when:
- You need to quickly implement solutions for visualizing large files with minimal investment.
- You need a good solution for pivoting the data larger than 100 MB.
- You would like to have a UI tool for managing data source connections. Data Server provides Admin Panel — a cross-platform desktop application that helps to manage indexes, security, and other configs.
On the other hand, this approach has some limitations:
- To achieve greater performance, the Data Server stores indexes in the RAM. From our experience, the Flexmonster Data Server tends to use 200%-300% of memory compared with the original file size. Accordingly, if you have a lot of data, you will need a lot of RAM. So, if the RAM requirements become too high for you, it is a good idea to consider other options where all the data is not kept in the RAM.
- Flexmonster Data Server does not support dynamic queries, as it relies on a set of predefined indexes that cannot be changed during runtime. So, if you need a more customizable data source manager - move on to the next options.
SQL Databases
Flexmonster Pivot supports out-of-the-box connection to the most popular SQL databases via Flexmonster Data Server. The list of supported databases contains MySQL, Microsoft SQL Server, PostgreSQL, Oracle, MariaDB, and Microsoft Azure SQL. The speed and capacity of the Data Server make it the best way to load data from a database.
The main pros and cons of this approach remain the same as for using Flexmonster Data Server with CSV/JSON files. So, reference the FDS block in this article to learn more.
Except for the mentioned above cases, it is recommended to use this approach when:
- You need to visualize large datasets from SQL databases.
- Your data is better to be stored in a tabular format.
You can also connect to any database using a server-side script that returns data in a JSON or CSV format. However, note that you will face the same limitations as with client-side JSON/CSV data sources.
There is also a third option to connect to SQL databases. It is more flexible in feature support and provides more control over data processing. At the same time, this approach requires more development to implement. To learn more, reference the custom data source API block in this article.
MongoDB
To work with MongoDB data, Flexmonster provides Flexmonster MongoDB Connector. It’s a server-side tool that is responsible for sending requests to your MongoDB database and then fetching, aggregating, and passing the data to Flexmonster.
The Connector has to be embedded into a middle-layer server, which will accept requests from Flexmonster and pass them to the Connector. MongoDB handles all the heavy tasks like data aggregation and filtering, and you can work flawlessly with bigger datasets.
On top of that, MongoDB Connector is open-source. You can download it on our GitHub and make changes to support your custom logic.
When to use:
- You already have a Mongo database.
- The structure of your database is more suitable for a document-oriented approach. If you are not acquainted with such kinds of databases, you can read more on why and when to use MongoDB.
The approach might not fit you if:
- You need a value filter for your report. If this feature is crucial for you, check out the list of data sources with filtering by values.
- You need product, median, or distinct count aggregations. See the list of supported aggregations for MongoDB in technical specifications.
Elasticsearch
Elasticsearch is a free and open-source technology for exploring structured and unstructured data. Being a fast search engine, it is a popular choice in cases when you have large datasets and need to execute complex queries quickly. The tool can scale out to hundreds of servers and, as a result, process vast amounts of data.
We provide built-in support for connecting to Elasticsearch, allowing you to get started with just a quick configuration of the pivot table. You only need to specify the Elasticsearch host URL and the index name.
Combining the tool with Flexmonster, you receive a quick large dataset visualizer without extra load on the browser. Flexmonster sends a sequence of queries to get the summarized data from Elasticsearch. And you need no complex server-side configurations with Elasticsearch’s built-in RESTful API.
When to use:
- You already set up Elasticsearch indexes.
- You are looking for a free, easy-to-implement server-side solution.
- You need an approach with server-side filtering. Elasticsearch supports subqueries to optimize the data loading.
The approach might not fit you if:
- You need a flat table view with completely raw data, as Flexmonster does not support it for Elasticsearch.
- You need a value filter for your report.
- You need to use complex aggregations like the percent or the difference of rows. See the list of supported aggregations for Elasticsearch in technical specifications.
MS Analysis Services
A combination of Microsoft Analysis Services and Flexmonster creates a robust enterprise-level data analytical tool. Analysis Services does all about data: structures and processes it, builds hierarchies, and defines sorting and formatting. Flexmonster just visualizes the data received from Analysis Services.
Flexmonster supports Microsoft Analysis Services with both tabular and multidimensional model types.
There are two ways to connect Flexmonster Pivot to Microsoft Analysis Services:
- Via XMLA — works for multidimensional models only and is a better choice if you already have XMLA configured.
- Via Flexmonster Accelerator — works for both multidimensional and tabular models. It’s our server-side utility that supports on-premises installations of SSAS and provides more advanced features like the use of credentials, etc.
When to use:
- You want a stable enterprise-level solution to visualize your big datasets.
- You need server-side filtering. Analysis Services also support subqueries, which help Flexmonster take less time for loading and rendering.
- You already have a Microsoft Analysis Services cube.
The approach might not fit you if:
- If you need a flat table view with completely raw data.
- If you need a free cross-platform solution. Microsoft Analysis Services is not free and can be hosted only on Windows servers.
- If you need to change the structure of your data often. The tool requires reprocessing of the cube when certain changes are made, which is a time-consuming and resource-intensive operation.
What if nothing above fits me?
If none of the above data sources fits, or you didn’t find the one you’re using, there is a flexible, versatile approach to use with any data source.
This approach requires your own server, which will prepare the data and provide it to Flexmonster. To make communication between your server and Flexmonster possible, we created a custom data source API.
It is a custom protocol developed by the Flexmonster team to help you build your own data source that will work with Flexmonster Pivot. While you will need a bit more time and effort, you will be able to support the custom logic you want, be it a flat table feature or dynamic queries.
The custom data source API makes it possible to connect to any data source, like NoSQL databases or Amazon Redshift, using your server and pass the data in the right format to Flexmonster. In our documentation, you can find a guide on how to create your own server. You can also refer to our Node.js and .NET sample servers when considering implementing this approach in your project. Besides that, MongoDB Connector and Flexmonster Data Server are also created based on the custom data source API.
Here are some common cases when custom data source API will be your best choice:
- You have a large dataset consisting of millions of rows that might scale even more over time.
- The data come from a mixed data source.
- You need to add custom aggregations or customize the way existing ones work.
- You already have a powerful backend that knows how to fetch, filter, and aggregate data, and you would like to feed Flexmonster with the data from it.
But it's better to consider other options when:
- If you don’t have enough server-side capabilities to perform CPU-intensive data processing on the app's back end.
- If your architecture does not involve the use of a server.
After all, you can always write a server-side script that returns data in a JSON or CSV format and gives it directly to Flexmonster. This approach is equivalent to using plain CSV or JSON data sources and has the same drawbacks and advantages.
Final recommendations
This article was created to simplify the choice of an optimal data source. All data sources described above can be effectively used with Flexmonster. However, all approaches have their advantages and disadvantages. On the technical specifications page, you can find a list of all available features for each data source.
For details on the connection to each data source, visit our documentation. If you have any further questions, you can contact our Tech team via the Help Center.