1.Connecting to CSV using the custom data source API

In the previous article, we described how to connect the pivot table to the CSV data source. To gain even better performance while working with CSV datasets, use the custom data source API – our custom communication protocol allowing you to retrieve already aggregated data from a server to Flexmonster Pivot.

With our sample .NET Core server implementing the custom data source API, it is possible to work with the data from CSV files. This approach has the following advantages:

There’s no limit on the dataset volume. Since all the data processing and aggregating is made on the back end, Flexmonster can visualize reports on larger data volumes. Thus, the maximum size of a file to process depends only on your server’s power.
You can implement your own security logic. With the custom data source API, you have full control over the data access and you can manage the data security the way you want.
Reduced browser’s memory usage. Flexmonster loads only the data needed for the report, and that data is already summarized, so the client side is less loaded.

To connect to CSV using the custom data source API, follow these steps:

To run the sample project, you will need .NET Core. Get it here if it’s not already installed on your machine.
Note that you need at least .NET Core 3.0. To check which versions of .NET Core are installed on your computer, run the following commands in the console:
```
dotnet --list-sdks
dotnet --list-runtimes
```
Download the .zip archive with the sample project or clone it from GitHub with the following command:
```
git clone https://github.com/flexmonster/api-data-source my-proj
cd my-proj
```
By default, the server uses the data stored in JSON format. To make the server use a CSV data, the "DataSourceName" configuration in server-dotnetcore/appsettings.json should be the following:
```
"DataSource": {
    "DataSourceName": "csv",
    ...
}
```
In the server-dotnetcore/appsettings.json file, create an index for the file with your data. It should be done as follows:
```
"DataSource": {
    "DataSourceName": "csv",
    "Indexes": {
        "fm-product-sales": "./data/fm-product-sales.csv",
        "data-types": "./data/data-types.csv",
        "your-index": "./data/data.csv"
    }
},
```
In this key:value pair, key is your index’s name, and value is a path to your file (e.g. ./data/data.csv). You can choose any name for your index.
As soon as you start the sample .NET Core server, it automatically preloads the data specified in the "Indexes" property. Thus, when Flexmonster requests for the data, the server responses with the already preloaded data.

Note 1: the preloaded data is kept in the server’s RAM, so the number of indexes you can specify is limited by the amount of RAM available to the server.

Note 2: data is stored in the server-dotnetcore/data/ folder in this sample. You can store your data either in this folder or in any other folder. In case the folder with your data isn’t on the server, an absolute path to your file must be defined when creating a new index. In addition, you should also make sure that the server has permission to access the file.
(optional) By default, data from a database is reloaded every 60 minutes. You can change the refresh time in the "DataRefreshTime" configuration in server-dotnetcore/appsettings.json. For example:
```
"DataStorageOptions": {
    "DataRefreshTime": "1800000"
}
```
Now the data will be reloaded every 30 minutes.
Note that refresh time is set in milliseconds.
Configure the report on the client side (see client/index.html):
```
new Flexmonster({
  container: "#pivot",
  report: {
    dataSource: {
      type: "api",
      url: "http://localhost:3400/api/cube",
      index: "your-index"
    }
  }
});
```
Note 1: index must match the name of the index defined in step 3 (e.g. "your-index").
Note 2: when Flexmonster requests for the data, the sample .NET Core server sends the response and then caches it. In case the component sends the same request once again, the server responses with the data from its cache. The cache is cleared only when the server is restarted, though the cache has a memory limit: when the limit is reached, and the new response can’t be cached, the .NET Core server deletes one of the previously cached responses from the cache.
To start the .NET Core server, run the following commands in a console:
```
cd server-dotnetcore
dotnet restore
dotnet run
```

What’s next?

You may be interested in the following articles: