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

PivotData Web API can be used with any REST Client

features headless BI for embedded pivot tables/charts

  • reporting engine built on the top of NReco PivotData Toolkit components:
    • near real-time server-side reports generation: ROLAP approach, no data import, live connections to data sources
    • connectors for SQL / MongoDB / ElasticSearch / SSAS / CSV
    • efficiently works with star-schema data marts
    • suitable for multi-tenant (SaaS) apps
    • aggregate functions: count/count unique, sum, average, min/max, variance/standard deviation, median/quantile, mode, list/list unique, custom DB-specific aggregations
    • calculated members: expressions-based dimensions and formula measures
  • advanced web-based pivot tables:
    • labels grouping, sub-totals, expand/collapse
    • multiple values in one table
    • sort table by values, totals or labels
    • calculations: percentage, difference, running total
    • custom formatting options (numbers, dates), heatmap table styling
    • server-side pagination and top-N limits for large pivot tables
    • easy data exploration with drill-down function
    • user-friendly search-like filter for rows, columns, values
  • report parameters for database query filters:
    • any number of parameters that may affect query generation
    • multi-value parameters (IN conditions)
    • custom user-defined conditions (and/or)
  • exports to PDF, Excel, Excel PivotTable, CSV, Image, HTML, JSON
  • includes web pivot tables builder (JS front-end example) for embedding into any web app
  • search-driven analytics capability new
  • supported data sources:
    • SQL Server, MySQL, PostgreSQL, Oracle, SQLite, ClickHouse, Snowflake, Presto/Trino (any other SQL DB with ODBC driver)
    • MongoDB (Azure Cosmos DB, Amazon DocumentDB)
    • ElasticSearch
    • XMLA OLAP (SSAS, Azure Analysis Services)
    • Custom connector (your web API)
  • easy integration with any web application: ASP.NET (any version), Java, PHP, Ruby, NodeJS, Python etc

download and pricing

PivotData microservice is a simple way to add self-service analytics & BI reporting into your web application. Configure your data sources with JSON config and generate pivot tables and charts reports with web API.
Web-based reports builder may be embedded in minutes!

quick purchase process

  • 1 Choose a package
  • 2 Pay online Online payment methods
  • 3 Download the package

how to use get started

  1. Download PivotData Microservice binaries.
  2. Deploy to Linux or Windows (NET8 runtime is required)
  3. Configure microservice by editing appsettings.json file
  4. Review PivotDataService examples: and use these samples to embed pivot tables and charts into your web app.
  5. For other usage scenarios explore microservice web API

online demo

Don't hesitate to contact us and ask for assistance or schedule a live demo.

frequently asked questions

PivotData microservice is an analytics backend (headless BI) that generates reports defined with JSON in terms of the cube model (dimensions, metrics, parameters). Under the hood the reporting engine generates & executes database queries, manages caching, queuing, calculations, DB connections and exports to various formats. Unique capabilility is a rendering of HTML pivot tables: they simply may be shown on any web page.

PivotData API is useful for:
  • build internal business intelligence tools with minimal effors.
  • web-based reporting: replace Excel PivotTable files with centralized live web-based pivot tables/charts and lightweight ROLAP solution.
  • use unified cube models for SQL databases, MongoDb, ElasticSearch, XMLA OLAP.
  • reports automation: generate reports in various formats on schedule.
Web pivot tables builder (js front-end) is a highly customizable: you can change the layout, use another controls for report configuration, apply custom styles etc.
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 file ("sqlite" connector)
  • Yandex 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.


more components

  • PivotData Toolkit for .NET

    .NET components for manipulating multidimensional dataset (in-memory data cube) without OLAP server, aggregating huge datasets, calculating data for pivot tables / charts, web pivot table control for ASP.NET MVC/Blazor.

  • Javascript Pivot Table

    Advanced features for pivot.js jQuery plugin: sort by values/labels, fixed headers, pivot table data for exports, drill-down event. ASP.NET MVC Core integration (optional): UI builder for report SQL query, exports to CSV/Excel/PDF.