Embedded BI settings PivotData Microservice Documentation

Get PivotData Microservice

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

Web API Render pivot table	→	Engine 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:

"Cubes": [
  {
    "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, Oracle, SQLite, ClickHouse, Snowflake database that is protocol-compatible with connectors listed above: Azure SQL, Amazon Redshift, MariaDb, MemSQL etc. any other SQL-compatible DB that has ODBC driver (like Google BigQuery)
MongoDb	Mongo Database (2.4+), Azure Cosmos DB and other Mongo-protocol compatible DBs
ElasticSearch	ElasticSearch (self-hosted or cloud service)
XMLA	Analysis Services via HTTP(S) XMLA endpoint
File	Local file: CSV/TSV or a binary cube file produced by PivotData .NET Toolkit.
Web API endpoint	Custom connector implemented as a web API endpoint that can handle PivotDataService JSON queries.

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/Elasic 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).

API Usage Limit Options

Reports generation might be a resource consuming, and it is possible to limit the number of concurrent executions to avoid many DB queries at once or prevent shortage of server resources where PivotDataService is hosted (RAM/CPU).

When API limits are configured all requests that match the same criteria are organized in the queue. If a request waits too much (exceeds the specified timeout) API returns HTTP/429 error.

Requests may be grouped on:

Cube ID: useful to limit number of queries to the same data source)
ReportConfig: useful if your report may be loaded at once by many users)
HeaderName: you may pass a custom value in the HTTP header and use it as a criteria. For example this could be user ID or tenant ID, and in this way you can limit the number of parallel executions for the particular user or tentant.

{
  "PivotDataService": {
    "ApiUsageLimit" : {
      "MaxConcurrentRequests":1,
      "MaxWaitTimeoutMins":2,
      "Criteria": {
        "CubeId":true,
        "ReportConfig":true,
        "HeaderName":null
      }
    }
  }
}

Other Options

{
  "PivotDataService": {
    "EnableResponseCompression": false,   // enable HTTP response compression (in case if reverse proxy is not used)
    "EnableTableApi" : true,   // enable/disable flat table API, disabled by default
    "EnableSearchApi": true,   // enable search queries API, disabled by default
    "ExposeException": false,  // include .NET exception with stacktrace into the error JSON 
    "PivotTableCellsLimit": 1000000,  // max cells in the pivot table; 0 by default which means 'no limit'
    "AggregateRowsLimit": 1000000,  // max number of rows for aggregate query
    "FlatTableRowsLimit": 50000,  // max number of rows loaded for flat-table reports
    "FlatTableNonStreamExportLimit": 50000, // max number of rows in flat-table PDF/Excel exports
    "QueryTimeout" : 120,  // db query timeout in seconds, 120 by default
    "CubeLookupDictionarySizeLimit": 100000, // max dictionary size for the Cube.Lookup function
    "FormatCulture" : null,  // global setting for the culture, for example: "en-US"
    "Cache" : {  // global in-memory cache options (applied if cube has no own "Cache" options)
      "Enabled" : true,  // enabled by default
      "SlidingExpirationMinutes" : 2,  // max life if cached data is not used
      "AbsoluteExpirationMinutes" : 10, // max life of cached data
    },
    "Export": {
      "EnableJsonPivotSubtotals": false,  // affects pivot report export to JSON
      "ExcelChart" : true,  // affects pivot report export to Excel
      "ExcelChartMaxShiftColumn": 5,
    }
    "ConnectionSecrets": {
      "db_user_pwd" : "PASSWORD_VALUE"  // now token @db_user_pwd may be used in cube's connection string/URL
    }
  }
}

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