Settings PivotData Microservice Documentation

The following diagram illustrates how microservice internally processes web API request that produces a pivot table report:

Render pivot table
Prepare pivot data
In-memory cache
Use cached data if possible
Perform in-memory OLAP query
Data source
Compose database aggregate query
Load query results into in-memory OLAP cube
On input API expects pivot table configuration structure. Engine verifies that pivot table config is correct in context of specified cube schema, determines which data is needed to build a report, performs necessary pre- and post- processing. In-memory cache holds data for pivot table reports executed recently; engine uses cached data whenever possible. Database query is composed and executed on-the-fly (in the real-time) and microservice works like ROLAP.

Microservice works in ROLAP style: raw data is not loaded from the data source (there are no ETL process) - instead of that database is accessed directly. This means that web API always return actual data and database should able to execute aggegate queries fast enough (ideally in seconds, up to several minutes max). For really large datasets we recommend to use specialized analytical databases that support distributed usage scenario (like Amazon Redshift or Yandex ClickHouse).

Microservice settings are stored in the appsettings.json file located in the folder with app binaries:

  "Logging": {    /* what to log and where */    },
  "Auth": {    /* web API auth options */    },
  "PivotDataService": {
    "Cubes": [    /* static cubes schema */    ],
    "CubeProviders": [    /* dynamic cubes schema providers */    ],
    "License" : {    /* license key options */    }

Cubes Configuration

Cube config determines what dimensions and measures could be used in reports and how they map to the underlying data source. In general schema looks like:

    "Id": "uniqueCubeId",
    "Name": "Cube Title",
    "SourceType": "SqlDb",  /* connector type, possible values: SqlDb, MongoDb, File */
    "Dimensions": [  /* list of available dimensions */  ],
    "Measures": [  /* list of available metrics (values) */  ],
    /* source type specific options */

The following data source types are supported (follow the links to get more info / usage example):

Source Type Description
SqlDb SQL database:
  • SQL Server, MySql, PostgreSql, ClickHouse, SQLite
  • database that is protocol-compatible with connectors listed above: Azure SQL, Amazon Redshift, MariaDb etc.
  • any DB that has ODBC driver (not supported by .NET Core 1.0 microservice build)
MongoDb Mongo Database (2.4+)
File Local file: CSV/TSV or binary cube file produced by PivotData .NET Toolkit.

Cubes configuration may be specified statically in "Cubes" array, or dynamically provided in the run-time by external URL or HTTP request context (JWT claim value).

Logging options

Default "Logging" section looks like:

"Logging": {
  "LogLevel": {
    "Default": "Debug",
    "System": "Information",
    "Microsoft": "Error",
    "NReco.PivotData.Engine": "Information"	 // set to "Debug" if you want to trace ALL SQL/Mongo queries  
  "File": {"Path":"c:\apps\logs\pivotdataservice.log"}

This is standard setup for .NET Core apps log filtering; mosting interesting options here are:

NReco.PivotData.Engine you can switch it to "Debug" and get detailed trace with ALL SQL or Mongo queries executed by connectors. This might be useful for debugging cube configuration errors; it is not recommended to use "Debug" option in production environment as it might cause unconrolled growth of the log file.
File in addition to console specifies the file for log entries. Remove or comment this entry to disable logging to the file.

Auth Options

By default microservice API can be accessed without authentication. If you need to secure API access you have the following alternatives:

  • pass JSON Web Token (JWT) in "Authentication" HTTP header and configure "Auth" section to verify the token. Also this approach allows you to provide context values that could be used as report parameters (like current user ID or login).
  • use reverse proxy authentication (NGINX or IIS).

Change appsettings.json location

If your appsetting.json file is located outside folder with microservice binaries its path should be provided explicitely, for example:

dotnet NReco.PivotData.Service.dll --appsettings.json=c:\config\pivotdataservice\appsettings.json