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 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.
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.
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.