PivotData Microservice Web pivot tables, charts for SQL/MongoDb/ElasticSearch
On-prem NET8 app can be hosted on Linux or Windows

SQL Data source documentation page explains how configure SQL-compatible database as a data source. Currently the following SQL databases are supported:

• MS SQL Server, Azure SQL ("mssql" connector)
• MySQL, MariaDB, MemSQL ("mysql" connector)
• PostgreSQL, Amazon Redshift, PipelineDB ("postgresql" connector)
• Oracle ("oracle" connector)
• SQLite local DB files ("sqlite" connector)
• DuckDB/MotherDuck ("duckdb" connector)
• ClickHouse ("clickhouse" connector)
• Snowflake ("snowflake" connector)
• Google BigQuery (with ODBC driver)
• Presto/Trino ("presto" connector)
• any other SQL-compatible database with ODBC driver

SQL data source configuration example:

"Cubes": [
{
 "Id": "orders",
 "Name": "Orders from MySQL sample DB",
 "SourceType": "SqlDb", // sql data source type
 "SourceDb": {
   "Connector": "mysql", // mssql, postgresql, sqlite 
   "ConnectionString": "Server=db4free.net;Database=nreco_sampledb;Uid=[DB_USER];Pwd=[PASSWORD];",
   "SelectSql": "SELECT o.status, o.orderDate, c.customerName, c.country, c.state, c.city FROM orders o LEFT JOIN customers c ON (c.customerNumber=o.customerNumber)"
 },
 "Dimensions": [
   { "Name": "status" },
   {
    "Name": "OrderDate_year",
     "LabelText": "Order Date (Year)",
     "Type": "Expression", // derived dimension
     "Params": [ "Date.Year(orderDate)", "orderDate" ]
   },
   {
    "Name": "OrderDate_quarter",
    "LabelText": "Order Date (Quarter)",
    "Type": "Expression",
    "Params": [ "Date.Quarter(orderDate)", "orderDate" ]
   },
   {
    "Name": "OrderDate_month",
    "LabelText": "Order Date (Month)",
    "Type": "Expression",
    "Params": [ "Date.Month(orderDate)", "orderDate" ]
   },
   { "Name": "customerName", "LabelText":"Customer" },
   { "Name": "country" },
   { "Name": "state" },
   { "Name": "city" }
 ],
 "Measures": [
  { "Type": "Count" }
 ],
 "Cache": {
  "AbsoluteExpirationMinutes" : 60
  }
} 
]

MongoDb Data source documentation page explains how configure Mongo database as a data source. Microservice uses aggregation pipeline and report generation time mainly depends on your cluser performance. MongoDb data source configuration example:

"Cubes":[
{
 "Id": "restaurants",
 "Name": "Restaurants from MongoDB sample dataset",
 "SourceType": "mongodb",
 "SourceMongoDb": {
  "ConnectionString": "mongodb://USER:PWD@MONGO_SERVER:PORT/DATABASE",
  "Database": "nrecodemo",
  "Collection": "restaurants",
  "FilterJson" : "{ \"cuisine\" : \"American \" }",
  "Unwinds": [  //sub-collections to unwind
    { "Path": "grades", "ApplyOnDemand": true }
  ],
  "UseAggregate": true // enable aggregation pipeline usage
 },
 "Dimensions": [
  { "Name": "cuisine", "LabelText": "Cuisine" },
  { "Name": "borough", "LabelText": "Borough" },
  { "Name": "name", "LabelText": "Name" },
  { "Name": "address.street", "LabelText": "Street" },
  { "Name": "grades.grade", "LabelText": "Grade" }
 ],
 "Measures": [
  { "Type": "Count" },
  {
   "Type": "Max",
   "Params": [ "grades.score" ],
   "LabelText": "Max Score"
  },
  {
   "Type": "Average",
   "Params": [ "grades.score" ],
   "LabelText": "Score Average"
  },
  {
	"Type": "Sum",
	"Params": [ "grades.score" ],
	"LabelText": "Score Sum"
  }
 ]
}
]

Parameters may be used to filter data on a database-level (with SQL WHERE or MongoDb $filter / $match). The following configuration snippets illustrate how to define parameters with appsettings.json:

// for SQL-based cube
"SourceDb": {
  "Connector": "mssql",
  "ConnectionString": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;",
  "SelectSql": "SELECT * FROM [Orders] WHERE 1=1 @country[and Country={0}]"
},
"Parameters": [
  {"Name": "country", "DataType": "string"}
],

// for MongoDb-based cube
"SourceMongoDb": {
    "ConnectionString": "mongodb://mongodbserver",
    "Database": "nrecodemo",
    "Collection": "restaurants",
    "FilterJson": "{ @cuisine[ \"cuisine\" : {0}, ]  }",
    "UseAggregate": true
},
"Parameters": [
    { "Name": "cuisine", "DataType": "string", "Type" : "Variable" }
],

Also it is possible to use "claim" values passed with JWT ("Type":"Claim").

Reports may include sensitive data and you might need to secure microservice API. The following options are available:

• disable all incoming connections to the microservice's port (5000 by default) with server's firewall. This variant is applicable if you access PivotData REST API only from your server application on the same machine.
• use reverse-proxy server for accessing REST API (IIS on windows, nginx on Linux) and use its authentication mechanisms (for instance, "Basic" or "Windows" authentication).
• configure JWT-based authentication in appsettings.json.

The answer is yes: microservice works as ROLAP engine and delegates data aggregation to the undelying data source, and this can be distributed database with billions of records like:

• Amazon Redshift (with PostgreSql connector)
• MongoDb (+ protocol compatible Azure Cosmos DB and Amazon DocumentDB)
• ElasticSearch (native connector)
• Yandex ClickHouse (native connector)
• Snowflake (native connetor)
• Google BigQuery (with ODBC driver)
• Vertica (with ODBC driver)
• Apache Drill (with ODBC driver)

For real-time reporting database should able to execute aggregate queries fast enough (in seconds, max several minutes).

PivotData microservice uses per-deployment licensing model: each production deployment (physical server, VM, docker container) needs to have its own license. It is possible to purchase an enterprise license which allows unlimited number of deployments.

Without a license key application works in trial mode: it renders trial notices and some limitations on the dataset size are applied. PivotData microservice in trial mode can be used only for evaluation/testing purposes.