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

PivotData Web API can be used with any REST Client

features on-premise web API for pivot tables

  • based on NReco PivotData SDK:
    • fast in-memory OLAP operations (no ETL, real-time reporting)
    • built-in ROLAP engine for SQL/MongoDB data sources
    • aggregate functions: count/count unique, sum, average, min/max, variance/standard deviation, list/list unique, custom formula
    • calculated members: derived dimensions and formula measures
  • advanced pivot table features:
    • labels grouping, sub-totals
    • multiple values in one table
    • sort table by values, totals or labels
    • calculations: percentage, difference, running total
    • formatting options, heatmap table styling
    • server-side pagination and top N limits for large pivot tables
    • keyword-based filter for rows & columns
  • report parameters (data source level filtering):
    • any number of custom parameters
    • multi-value parameters (IN conditions)
    • custom user-defined conditions (and/or)
  • export pivot tables & charts to HTML, JSON, CSV, Excel, PDF
  • embeddable web pivot table & chart builder (front-end js components) for self-service analytics, user-defined reports
  • supported data sources (connectors):
    • MS SQL Server (Azure SQL), MySQL (MariaDB, MemSQL), PostgreSQL (Amazon Redshift), SQLite, Yandex ClickHouse
    • MongoDB (aggregation pipeline), Azure DocumentDB (MongoDb protocol)
    • large CSV/TSV files (up to 200mb)
  • easy integration with any web application: ASP.NET, Java, PHP, Ruby, NodeJS, Python etc
  • PivotData microservice .NET Core app can be hosted on Linux, MacOS or Windows.

download and pricing Verified Quality by financesonline.com

PivotData microservice provides simple way to embed self-service analytics (pivot table, pivot chart reports) into any web application. Data sources are configured with simple JSON config. You can add web pivot tables to your web app 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 server
  3. Configure your data sources with appsettings.json config
  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

Use PivotData microservice to:
  • add self-service web pivot table builder into existing web application
  • replace Excel files with web-based reporting tool
  • real-time analytics and ad-hoc BI reporting by SQL/MongoDB data sources
  • aggregate data, calculating measures for custom BI dashboards on-the-fly
  • automate summary reports generation on the server-side (scheduled reports, exports)
Pivot table builder (javascript front-end) is highly customizable: for example, you can use another UI controls for pivot table configuration, change layout or even replace whole pivot table configuration form with your own variant.
SQL-compatible data sources are configured in "Cubes" section of appsettings.json file; cube "SourceType" should be "SqlDb".

Currently the following SQL databases are supported:
  • MS SQL Server, Azure SQL: "Connector":"mssql"
  • MySQL (MariaDB, MemSQL): "Connector":"mysql"
  • PostgreSQL: "Connector":"postgresql"
  • Amazon Redshift: "Connector":"postgresql" + include "Server Compatibility Mode=Redshift" into connection string
  • SQLite: "Connector":"sqlite"
  • Additional connectors for .NET Framework 4.6.2 build (Windows-only):
    • Oracle: "Connector":"oracle"
    • ODBC: "Connector":"odbc" (will be available in .NET Core build too after netstandard2.0 release)
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
  }
} 
]
"SelectSql" should include all columns referenced from "Dimensions" or "Measures". Don't worry about dataset size or number of columns: PivotData engine will load only data needed for concrete pivot table. Aggregation is performed on SQL level (with GROUP BY) if possible.
For MongoDb data source "SourceType" should be "mongodb". Dimensions and Measures are configured in the same way as for SQL source; note that you can reference sub-document fields like "address.street".

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"
  }
 ]
}
]
Note that unwinds are performed only if report includes dimension or measure that uses field from sub-collection (this is important for "Count" aggregator).
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 (appsettings.json):
"Auth": {
  "Type": "jwt",
  "Jwt": {
    "ValidIssuer": "Test",
    "ValidateIssuer": true,
    "ValidateAudience": false,
    "ValidateLifetime": true,
    "ValidateIssuerSigningKey": true,
    "IssuerSigningKeyString": "some_secure_key_value"
  }
}
The following features may be implemented in the future releases:
Data sources
  • ODBC connector (Oracle, IBM DB2, Vertica, EXASOL, Apache Hadoop Hive, Google BigQuery etc) already available in net462 build
  • Oracle native connector already available in net462 build
  • JSON connector / URL as data source
  • ElasticSearch connector
  • OLAP Server (XMLA/MDX) connector
  • SalesForce connector (SOQL)
  • Support for custom data connector plugins (.net)
Data cubes
  • Better support for star-schema (resolve lookups after data grouping)
  • External dimension lookups
  • Hybrid cubes merged from several data sources (data blending)
API
  • endpoint for basic XMLA/MDX that can be used from Excel, BI tools, 3rd party pivot table controls
  • custom reports defined by HTML templates + export to PDF or image
  • built-in save/load for user-defined reports
Pivot table & UI (front-end components)
  • Web API for pivot chart exports: HTML, PDF, render to image (JPEG/PNG)
  • Drill-down functionality
  • Add ability to filter by pivot table values (like "exclude values <50")
  • search-driven analytics: ability to build a report by simple search query
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.