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 Toolkit:
    • 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
    • percentage, difference, formatting options
    • 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
    • MongoDB (aggregation pipeline), Azure DocumentDB (MongoDb protocol)
    • pre-computed binary cube data files
    • it is possible to develop custom connectors (.NET plugins)
  • simple integration with any web application: ASP.NET, Java, PHP, Ruby, NodeJS, Python etc
  • PivotData REST service (.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 trial pack and deploy PivotData Microservice on Linux or Windows server (.NET Core application), see readme.txt for instructions.
  2. Configure your data sources with simple appsettings.json config
  3. Review web pivot builder examples:
    • index.html
    • condition-builder.html
    Use sample code to embed pivot tables and charts into your web app.
  4. For all pivot table related functions: use simple web API

pivot table builder demo

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

frequently asked questions

PivotData microservice is useful in the following cases:
  • integrate advanced pivot tables and charts builder into any web application
  • get embedded analytics and ad-hoc BI reporting for SQL/MongoDB data sources
  • calculate data for web-based BI dashboards with custom widgets (charts/maps etc)
  • automate summary reports on the server-side (scheduled reports, exports)
Pivot table builder UI (javascript fron-end) can be customized: for example, you can use another controls for pivot table configuration, change layout or even write your own pivot table configuration interface.
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"
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
  • Support for custom data connector plugins (.net)
  • ElasticSearch connector
  • SalesForce connector (SOQL)
  • Yandex ClickHouse OLAP connector
  • EXASOL connector (possible only in windows-only build, there are no .NET Core compatible EXASOL provider yet)
  • Google BigQuery connector
  • Oracle DB support (possible only in windows-only build, waiting for officialy announced Oracle ADO.NET provider for .NET Core )
  • CSV/TSV/JSON files (or URLs) as data source
Data cubes
  • Dynamic cube configurations in SQL database + REST API for cubes management
  • Hybrid cubes merged from several data sources (data blending); for example, pre-computed + SQL
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.

roadmap

2016 Dec 16 Version 1.0-final changes:
  • enable CORS by default
  • front-end js code was reorganized to simplify integration
  • added support for multi-value report parameters (used with IN condition)
  • added "Condition" report parameter type for complex user-defined filter conditions (may be combined with and/or). Filter is applied on the data source level (both SQL and MongoDb sources are supported).
  • added javascript Query Builder integration example (condition-builder.html)
  • added ability to specify export file name with API parameter
2016 Oct 29 Version 1.0-beta3 changes:
  • added Json Web Token (JWT) authentication and authorization for microservice web API
  • added custom report parameters (variables) for SQL selects / MongoDb filters (database-level data filtering):
    • variable values are specified with PivotReport model (JSON)
    • claim values are specified with JWT
2016 Oct 10 Version 1.0-beta2 changes:
  • added server-side pagination both for rows and columns (viewing large pivot tables)
  • MongoDB connector: uses aggregation pipeline, can unwind properties
  • Pivot report JSON config now uses dimension/measure names instead of index numbers
  • bugfixes
2016 Sep 26 Version 1.0-beta1 with features:
  • render pivot table to HTML, JSON, CSV, Excel, PDF
  • formatting options: custom formatting for numbers/labels, percentage values, difference values
  • top N limits
  • measures: count, min, max, average, count unique, list unique, variance/std-dev
  • JSON configuration for data cubes (appsettings.json)
  • data sources: MS SQL, MySQL/MariaDB, PostgreSQL, SQLite, pre-computed binary cube files
  • use of database-level aggregation (GROUP BY) if possible
  • Derived dimensions (calculated from parent dimension(s) - for example, date year/quarter/month/day)
  • Custom formula measures (calculated from parent measure(s) with formula expression)
  • keyword-based filter by pivot table row/column labels
  • in-memory caching (can be configured for each cube separately)

PivotData REST API methods

<BaseUrl> = http://localhost:5000/    [Q] = query string parameter    [H] = request header    [BODY] = request body
URL Description Method Parameters Result
<BaseUrl>/api/cube Returns list of available cubes. GET
[ {CubeDescriptor}, ]
<BaseUrl>/api/cube/{cubeId} Returns specified cube metadata. GET
cubeId
Cube unique identifier (from appsettings.json config)
{CubeDescriptor}
<BaseUrl>/api/cube/{cubeId}/pivot/render Calculates pivot table and returns HTML+JSON metadata needed for front-end UI (jquery.pivottable.js plugin) that provides interactive data exploration functions (sorting, pagination). GET
[Q] pvtReportJson
PivotReport model JSON
JSON structure:
{
 "Configuration" : { /* Pivot table config metadata */ },
 "HtmlContent" : "<table> ...",
 "JsonData" : { /* JSON export without values */ }
}
POST
[H] Content-Type
application/json
[BODY]
PivotReport model JSON
<BaseUrl>/api/cube/{cubeId}/pivot/export/{format} Calculate pivot table and export to specified format. GET, POST
[Q] format
supported export formats: csv, excel, pdf, json, html
[Q] fileName (optional)
File name in "Content-Disposition" header
[Q] pvtReportJson
PivotReport model JSON
(export file for download)

PivotReport model API parameter

Property Value Description
Rows array of PivotReport.Dimension models list of dimensions for pivot table rows; empty by default
Columns array of PivotReport.Dimension models list of dimensions for pivot table columns; empty by default
Measures array of PivotReport.Measure models list of measures for pivot table values; "Count" is used by default (or first measure if count is not present in the cube)
SubtotalColumns true|false render sub-totals for grouped column labels; false by default
SubtotalRows true|false render sub-totals for grouped row labels; false by default
GrandTotal true|false render grand total value; true by default
TotalsRow true|false render row with totals; true by default
TotalsColumn true|false render column with totals; true by default
LimitRows integer (N) render only first N rows; when limit is specified, pagination options for rows are ignored
LimitColumns integer (N) render only first N columns. When limit is specified, pagination options for columns are ignored
RowPage
{
  "Limit" : 100,
  "Offset" : 0
}
specifies pagination options for rows; "Limit" = max number of rows, "Offset" = start riw; if not specified all riws are shown
ColumnPage
{
  "Limit" : 100,
  "Offset" : 0
}
specifies pagination options for columns; "Limit" = max number of columns, "Offset" = start column; if not specified all columns are shown
Filter string comma or space separated list of keywords to match table row or column labels (this filter is applied after data aggregation); it is possible to specify dimension hints (like "year:2015") or starts-with/ends-with match type ("start*" or "*end")
Parameters
{
  "paramName" : "val"
}
custom parameters (name-value) for the report; specified parameters should be defined in the cube configuration
OrderBy PivotReport.OrderOptions model table sorting options; by default rows & columns are sorted by labels (A-Z order)
{
 "Columns" : [ 
  {"Name":"Country"}, 
  {"Name":"Category"} 
 ],
 "Rows": [ 
  {"Name": "Order Date (Year)"} 
 ],
 "Measures": [ 
  {"Name":"Count"} 
 ],
 "SubtotalColumns" : true,
 "RowPage": {
  "Limit": 100,
  "Offset": 0
 }
 "Filter": "2015,2016,USA",
 "Parameters": {
  "customer": "somevalue"
 }
}

PivotReport.Dimension model API parameter

Property Value Description
Name string dimension name from cube's config
LabelText string custom label for this dimension; overrides LabelText from the cube's config
Format string custom format string for dimension keys; overrides Format from the cube's config
{
 "Name", "order_date",
 "LabelText" : "Order Date",
 "Format" : "{0:d}"
}

PivotReport.Measure model API parameter

Property Value Description
Name string measure name from cube's config
LabelText string custom label for this measure; overrides LabelText from the cube's config
Format string custom format string for measure values; overrides Format from the cube's config
Percentage GrandTotal | RowTotal | ColumnTotal calculate percentage value for this measure
Difference PreviousRow | NextRow | PreviousColumn | NextColumn calculate difference value for this measure
{
 "Name", "SumOfAmount",
 "LabelText" : "Amount (Sum)",
 "Format" : "{0:#,#.##}",
 "Percentage": "RowTotal"
}

PivotReport.OrderOptions model API parameter

Property Value Description
PreserveGroupOrder true | false preserves labels grouping order when sort by value is specified; false by default
Values
{
 "Axis":A,
 "Direction":D,
 "Index":I,
 "Measure":M
}
order by values:
A
"Rows"|"Columns"
D
"Ascending"|"Descending"
I
row or column index
M
table measure index (0 if not specified)
{
  "Axis", "Rows",
  "Direction" : "Descending",
  "Index" : 0,
  "Measure": 0
}

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, export to CSV/ExcelPDF.