PivotData Microservice Web pivot tables and pivot charts for 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/CSV data sources
    • efficiently works with star-schema data marts
    • 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
    • custom formatting options (numbers, dates), 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, PDF, CSV, Excel (sheet with tabular data), Excel PivotTable, JSON
  • 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 existing web applications: ASP.NET, Java, PHP, Ruby, NodeJS, Python etc
  • PivotData microservice .NET Core app can be hosted on Linux, Windows or MacOS.

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 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

Typical PivotData microservice usage:
  • add end-user web pivot table builder into existing web application (without OLAP server)
  • replace Excel files with PivotTables and/or SSAS with web pivot tables and lightweight ROLAP solution
  • real-time analytics and ad-hoc BI reporting for SQL-compatible and MongoDB data sources (including big data)
  • aggregate data, calculate metrics for custom BI dashboards on-the-fly
  • automate summary reports generation on the server-side (scheduled reports, exports)
Web pivot table builder (js front-end) can be easily customized: 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 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)
  • Extra connectors supported in microservice build for .NET Framework 4.6.2 (Windows-only):
    • Oracle
    • ODBC
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 (appsettings.json):
"Auth": {
  "Type": "jwt",
  "Jwt": {
    "ValidIssuer": "Test",
    "ValidateIssuer": true,
    "ValidateAudience": false,
    "ValidateLifetime": true,
    "ValidateIssuerSigningKey": true,
    "IssuerSigningKeyString": "some_secure_key_value"
  }
}
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
  • MongoDb
  • Yandex ClickHouse
For real-time reporting database should able to execute aggregate queries fast enough (in seconds - up to minute), and no matter how large is your dataset.
The following features may be implemented in the future releases:
Data sources
  • ODBC connector already available in net462 build; no official .NET Core ODBC provider yet
  • Oracle native connector already available in net462 build; no official .NET Core Oracle provider yet
  • IBM DB2 native connector: .NET Core provider is already available (can be added in all builds)
  • Google BigQuery native connector (can be added in all builds)
  • JSON connector / URL as data source
  • ElasticSearch connector
  • Druid connector
  • OLAP Server (XMLA/MDX) connector
  • SalesForce connector (SOQL)
  • Support for custom data connector plugins (.net)
Data cubes
  • External dimension lookups (in-memory dictionaries)
  • 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
Pivot table & UI (front-end components)
  • Drill-down functionality coming soon
  • Collapse/expand label groups
  • Web API for pivot chart exports: HTML, PDF, render to image (JPEG/PNG)
  • 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.