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

PivotData Web API can be used with any REST Client

features on-premise (self-hosted) web API for pivot tables

  • based on NReco PivotData SDK:
    • real-time OLAP queries (no ETL, live connection to data source)
    • built-in ROLAP engine for SQL / MongoDB / ElasticSearch / CSV
    • efficiently works with star-schema data marts
    • aggregate functions: count/count unique, sum, average, min/max, variance/standard deviation, list/list unique
    • calculated members: expression-based dimensions and formula measures
  • advanced HTML pivot table features:
    • labels grouping, sub-totals
    • 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 keyword-based filter for rows, columns, values
  • report parameters for database query filters:
    • any number of custom parameters
    • multi-value parameters (IN conditions)
    • custom user-defined conditions (and/or)
  • export web pivot table report to HTML, PDF, CSV, Excel, Excel PivotTable, JSON
  • includes web pivot tables builder JS front-end for self-service analytics and user-defined reports
  • supported data sources (connectors):
    • MS SQL Server (Azure SQL), MySQL (MariaDB, MemSQL), PostgreSQL (Amazon Redshift), SQLite, Yandex ClickHouse
    • any other SQL-compatible database with ODBC driver
    • MongoDB (aggregation pipeline), Azure DocumentDB (with MongoDb protocol)
    • ElasticSearch
    • large CSV/TSV files
  • easy integration with any web application (ASP.NET, Java, PHP, Ruby, NodeJS, Python etc)
  • PivotData microservice .NET Core app can be hosted on Linux, Windows or MacOS

download and pricing

PivotData microservice adds self-service analytics & BI reporting to your web application. Configure data sources with simple JSON config and use simple web API for pivot tables and charts generation. Interactive web reports builder may be embedded in minutes!

quick purchase process

  • 1 Choose a package
  • 2 Pay online 2Checkout.com is a worldwide leader in online payment services
  • 3 Download the package

how to use

  1. Download PivotData Microservice binaries.
  2. Deploy to Linux or Windows (.NET Core runtime is required)
  3. Configure microservice by editing appsettings.json file
  4. Review web pivot builder examples:
    • index.html
    • condition-builder.html
    Use sample code 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 useful for:
  • self-service analytics: embed end-user web pivot table builder into any web application (OLAP server not needed).
  • web reporting: replace Excel PivotTable files and/or SSAS with centralized live web-based pivot tables/charts and lightweight ROLAP solution.
  • real-time BI: build reports on-the-fly by SQL databases, MongoDb, ElasticSearch.
  • rapid development of custom BI dashboards and canned reports
  • reports automation: server-side reports generation in various formats on schedule
Web pivot table builder (js front-end) is 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)
  • Yandex ClickHouse ("clickhouse" connector)
  • SQLite local file ("sqlite" connector)
  • Oracle (native connector in .NET Framework build; ODBC in .NET Core 2 build)
  • Google BigQuery (with ODBC driver)
  • any 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 (native connector)
  • ElasticSearch (native connector)
  • Yandex ClickHouse (native connector)
  • Google BigQuery (with ODBC driver)
  • Vertica (with ODBC driver)
For real-time reporting database should able to execute aggregate queries fast enough (in seconds, max several minutes).
The following features may be implemented in the future releases:
Data sources
  • SSAS OLAP Server (MDX) connector (only for Windows platform)
  • Allow URL in CSV file data source
  • JSON file connector
  • Druid connector
  • IBM DB2 native connector. Note: it is possible to connect to DB2 with existing ODBC connector.
  • SalesForce connector (SOQL)
Data cubes
  • External lookups for dimensions (in-memory dictionaries)
  • Hybrid cubes when values are taken from several data sources (data blending)
API
  • search-driven analytics: build reports with natural language queries (already available as experimental feature for CSV sources)
  • custom reports defined by HTML templates + export to PDF or image
Pivot table & UI (front-end components)
  • Collapse/expand label groups
  • Web API for pivot chart export to image (JPEG/PNG)
If you are interested in one of these features please let us know.

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 table / chart reports, ASP.NET pivot table builder control.

  • 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 integration (optional): UI builder for report SQL query, exports to CSV/Excel/PDF.