PivotData Microservice
Web pivot tables, charts for SQL/MongoDb/ElasticSearch
NET8 backend can be hosted on Linux or Windows
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 Trial pack
v.1.10.0
PivotData Microservice binaries + Web pivot table builder UI (js frontend) + sample configurations DB data sources (CSV file, Sqlite, Mysql, ClickHouse, MongoDB, ElasticSearch, XMLA Client for Analysis Services). |
Download |
PivotData Microservice 1-Server License
Includes: a commercial license for 1-server deployment + key (removes trial limitations), assistance with initial setup & configuration, a 1-year period of email support and free product upgrades. |
$999 - Order Now |
PivotData Microservice Enterprise License
Includes: a commercial license for UNLIMITED deployments + key (removes trial limitations), binaries to disable deployments activation, assistance with initial setup & configuration, a 1-year period of email support and free product upgrades. |
$4990 - Order Now |
Web-based reports builder may be embedded in minutes!
quick purchase process
- 1 Choose a package
- 2 Pay online
- 3 Download the package
how to use → get started
- Download PivotData Microservice binaries.
- Deploy to Linux or Windows (NET8 runtime is required)
- Configure microservice by editing
appsettings.json
file - Review PivotDataService examples:
index.html
(web pivot builder)table-report-builder.html
(table report builder)condition-builder.html
(web pivot with query builder)pivot-table-report-view.html
(simple preconfigured pivot table)pivot-chart-report-view.html
(simple preconfigured chart)web-dashboard.html
(web dashboard with custom filter)
- For other usage scenarios explore microservice web API
online demo
Web pivot tables and charts builder (JS/jQuery) + PivotData microservice backend |
Web Pivot Builder |
BI tool based on PivotData microservice | SeekTable.com |
Web API usage sample (javascript) | Open JSFiddle |
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.
- 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
"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 } } ]
"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" } ] } ]
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").
- 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
.
- 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)
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.