PivotData OLAP library implements in-memory multidimensional dataset (data cube) for further analytical processing in .NET applications.
Library performs fast real-time data grouping and aggregation, calculates data for pivot tables and charts without OLAP server.
OLAP library NReco.PivotData
- pure C# library for data aggregation and grouping: calculates data for pivot table (pivot grid, pivot chart) from any enumerable data source, SQL or NoSQL database.
- implements fast in-memory data cube (multidimensional dataset) and OLAP query operations (slice/dice/filter/roll-up/pivot).
- aggregate (summary) functions : count, sum, average, min, max, count unique, list unique, list values, variance, standard deviation, median, mode, custom formula. Several measures may be collected at once.
- supports incremental data processing (appending), merge calculation results (parallel aggregation), lazy totals calculation.
- fast data cube serialization (save/load in-memory cube state).
- pivot table data model (2D or n-Dimensional): several dimensions per axis (label grouping), sort by labels, sort by values, totals, sub-totals.
- simplest way to pivot a DataTable or typed list - with one line of C# code.
- OPEN SOURCE and FREE for usage in non-SaaS apps that have only one single-server production deployment (see FAQ section for more details).
download and pricing
PivotData OLAP library Source Code & Examples
|
View on GitHub |
PivotData Toolkit Trial & Examples (NET8)
|
Download
|
PivotData OLAP library commercial license
Removes NReco.PivotData nuget free usage restrictions: allows library redistribution as a part of your product (unlimited number of deployments) and usage in SaaS apps + 1 year of email support. |
$299 - Order Now |
PivotData Toolkit provides .NET advanced components: render pivot tables to HTML, exports to CSV/JSON/Excel/PDF, calculations, filters,
for using DBs as data sources (SQL/MongoDb/ElasticSearch/MDX) etc.
Includes
web pivot table builder example that can be easily integrated into any ASP.NET application.
PivotData Toolkit NReco.PivotData.Extensions
- advanced components for complex pivot tables rendering and exports:
- HTML pivot table renderer that supports labels grouping, multiple measures, totals and groups subtotals, custom HTML formatting and CSS styling
- pivot table exports to CSV, Excel, PDF, JSON or .NET DataTable
- calculate pivot table percentage / difference / running total / heatmap
- javascript pivot table control: sort by labels/values/totals, fixed headers, server-side pagination for large tables, groups expand/collapse
- search-like filter to exclude pivot table rows/columns
- ASP.NET Core MVC pivot table: interactive reports builder (pivot table + chart) for end-users
-
data processing:
- load tabular data from SQL databases, CSV/TSV, JSON
- load pre-aggregated data: SQL SELECT GROUP BY, MongoDb aggregate pipeline results, ElasticSearch aggregate query, OLAP server MDX results
- ETL components: parse numbers/dates, merge/match by regex, combine columns, calculate derived values
- export in-memory cube to DataSet (star schema)
- memory-optimized read-only cube implementation (FixedPivotData)
- read more details about PivotData Toolkit...
PivotData Toolkit Standard License Pack
Includes: license key for PivotData Toolkit components, per-developer commercial license (perpetual, royalty-free redistribution as part of your product), 1 year of email support and free component upgrades Read more about PivotData Toolkit... |
|
PivotData Toolkit SaaS License for 1-deployment
Includes: license key for PivotData Toolkit, SaaS license for 1 production deployment (no limitation on the number of developers), 1 year of email support and free component upgrades |
$499 - Order Now |
PivotData Toolkit Enterprise License
Custom licensing conditions. Possible options: unlimited number of developers for standard license, unlimited number of deployments for SaaS, OEM license, source code for BI Toolkit components, high-priority or live support |
Request Pricing |
quick purchase process
- 1 Choose a package
- 2 Pay online
- 3 Download the package
how to use create pivot table example
- Install NReco.PivotData nuget package
- Aggregate data using PivotData class with one line of C# code:
var pivotData = new PivotData( new string[] {"name","age"}, new AverageAggregatorFactory("salary"), new DataTableReader(t) );
-
Access data cube values:
var grandTotal = pivotData[Key.Empty,Key.Empty].Value; var subTotalFor29 = pivotData[Key.Empty,29].Value; var allDimensionKeys = pivotData.GetDimensionKeys();
-
Create pivot table model:
var pivotTable = new PivotTable( new []{"name"}, // row dimension(s) new []{"age"}, // column dimension(s) pivotData );
- Render pivot table to HTML table:
For this code snippet you need to install NReco.PivotData.Extensions nuget package.var htmlResult = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult); pvtHtmlWr.Write(pivotTable);
- What's next:
- PivotData online documentation and HOW-TOs
- Download PivotData SDK examples
- NReco.PivotData API reference
- Have a question? Feel free to ask on StackOverflow or contact support team.
online c# pivot table demo
see also
ASP.NET example: MVC pivot table builder | interactive pivot tables and pivot charts builder |
ASP.NET Example: BI dashboard with charts | custom BI dashboard with pivot tables and charts |
SeekTable (BI tool) | based PivotData Toolkit components |
frequently asked questions
PivotData OLAP library can do the following:
- perform fast in-memory aggregation of tabular data and collect one or several measures in one pass.
- execute OLAP queries in C# (linq-style): slice/dice, drill up/drill down, roll-up/pivot (without OLAP server and MDX).
- calculate summary data for pivot tables (pivot charts, cohort analysis) in .NET apps.
- use databases as a data source in ROLAP mode (when DB query is generated & executed on-the-fly).
- rendering/exporting a pivot table to HTML table/CSV/Excel/PDF/JSON on the server side.
- web pivot table builder that can be embedded into your ASP.NET application (MVC Core, Blazor, or even into legacy ASP.NET app).
- functionality which is typically needed for embedded web-based BI/analytics, scheduled reports generation, custom BI dashboards, operational reporting.
PivotData can be used with large variety of data sources:
Aggregation of large datasets (>1 mln rows) can be performed on database level (with GROUP BY query) and loaded into PivotData instance for further analytical processing.
- in-memory .NET collections, anything with IEnumerable interface: see DynamicListGrouping example
- large CSV / TSV files:
CsvSource
class - JSON data: array of objects / array of values:
JsonSource
class - any SQL database that has ADO.NET provider (see ToolkitSqlDbSource example +
SqlGroupByCube.cs
in in ToolkitPivotBuilderMvc):- MS SQL Server, Azure SQL
- MySQL, MariaDB, MemSql
- PostgreSQL, Amazon RedShift
- SQLite, Oracle DB, IBM DB2 etc
- Yandex ClickHouse
- any other SQL-compatible database with ODBC driver (say, Google BigQuery)
- MongoDB with support of aggregate pipeline: see ToolkitMongoDbSource example
- ElasticSearch: see ToolkitElasticSearchSource example +
ElasticSearchCube.cs
in ToolkitPivotBuilderMvc - SSAS (Analysis Services) OLAP cube: see ToolkitAdomdSource example +
MdxAdomdCube.cs
in ToolkitPivotBuilderMvc
Aggregation of large datasets (>1 mln rows) can be performed on database level (with GROUP BY query) and loaded into PivotData instance for further analytical processing.
PivotData Toolkit (NReco.PivotData.Extensions.dll) extends OLAP library with advanced components for typical analytics/reporting functionality:
-
Data sources:
- DbCommandSource: use SQL DB (MS SQL Server/MySQL/PostgreSQL etc) as input data source
- JsonSource: use JSON as input data source (supports both arrays of objects and values)
- CsvSource: use CSV file as input data source (can be used for processing data from large >1gb CSV files)
-
MongoDbSource
is implemented in the ToolkitMongoDbSource example -
ElasticSearchSource
is implemented in the ToolkitElasticSearchSource example - GroupedSourceReader: load PivotData from pre-grouped data (for example, when data is aggregated on SQL level with GROUP BY)
- DerivedValueSource: handle derived values: parse input values, apply formatting rules, define derived values (date parts like year/month/quarter, formulas)
- Pivot table renderers (writers):
- HTML: PivotTableHtmlWriter
- CSV: PivotTableCsvWriter
- Excel: PivotTableExcelWriter
- JSON: PivotTableJsonWriter
- DataTable: PivotTableDataTableWriter (not available in netstandard1.5 build)
- Pivot table model wrappers:
- PercentagePivotTable: calculates percentage values (by grand total, row or column totals).
- DifferencePivotTable: calculates difference between values (by row or column).
- RunningValuePivotTable: calculates running value (by row or column). Running total is calculated if aggregator is not specified.
- TopPivotTable: limits table by top N (or last N) rows and/or columns.
- PaginatePivotTable: takes section of the table by specified start-end rows and columns. Used for server-side pagination.
- HeatmapPivotTable: highlights max values in the table.
- PivotDataFactory / PivotTableFactory: create PivotData/PivotTable instances by JSON configuration (used when pivot table is constructed dynamically)
- CubeFileReader / CubeFileWriter: read/write serialized PivotData (configuration and data) from/to local files
PivotData microservice
is a headless BI (self-hosted NET6 app with web API) based on PivotData Toolkit components:
- advanced web API for pivot table HTML rendering and export functions + API for usual tabular reports (datagrids that display dataset's rows)
- implements lightweight ROLAP engine (direct query connections to data sources)
- includes built-in connectors for all popular DBs: SQL Server, MySQL, PostgreSQL, SQLite, ODBC, MongoDb, ElasticSearch, CSV any many others. A special web-API connector can be used to load data for pivot tables from your .NET app!
- uses short-living in-memory cache to eleminating excessive database queries and improve UX
- can be scaled horizontally
- includes pivot table builder front-end example (HTML/JS) that may be easily integrated into any web application (PHP, Ruby, Python, Java etc)
- Microservice NET6 can be hosted either on Linux or Windows server or Docker container.
PivotData Toolkit can be used for creating OLAP
local cubes
(represented by .cub file) with the following steps:
- lets assume that some dataset is already aggregated with PivotData library
- export PivotData instance into relational representation (
DataSet
with star schema) using DataSetStarSchemaWriter component - save DataSet into csv files with schema.ini for OLEDB Provider Text Driver
- use AMO (Analysis Services .NET API) to create a new local cube (.cub file)
- configure dimensions, data source (JET OLE Provider to csv files) and setup cube according to PivotData instance metadata
- Process cube (this action will force OLAP to read all data from csv files and store them in the local cube)
The following code snippet illustrates how to pivot a DataTable:
DataTable tbl; var pvtData = new PivotData(new[] {"product", "year" }, new CountAggregatorFactory() ); pvtData.ProcessData(new DataTableReader(tbl)); var pvtTbl = new PivotTable( new [] {"product"}, //rows new [] {"year"}, //columns pvtData);PivotTable object can be easily exported to DataTable with PivotTableDataTableWriter (Toolkit component):
var dataTableWr = new PivotTableDataTableWriter("Test"); var tbl = dataTableWr.Write(pvtTbl);
See also: how to pivot DataTable to Excel PivotTable.
Depending on situation PivotData can (re)calculate all subtotal/grand total values either as part of
data processing
(PivotData.ProcessData)
or when totals are accessed for the first time (LazyTotals=true).
This difference is essential for data cubes with a lot of dimensions (>3) and/or significant amount of unique dimension keys (>100):
non-lazy calculation of subtotals for all dimensions recombinations may take a lot of time and lead to OutOfMemory exception.
In general LazyTotals=false mode is recommended in the following cases:
In general LazyTotals=false mode is recommended in the following cases:
- you need extremely fast random access to sub-totals many times and sub-totals doesn't cause "out-of-memory" exception; for example, this may be concurrent requests to the same in-memory data cube.
- data cube is used for aggregating real-time data stream (ProcessData is called many times for data chunks) and sub-totals should be kept up-to-date without re-calculating totals when next data chunk is arrived (for example in case of real-time monitoring/alerts).
- all PivotData subtotals assumed to be used: good example is the pivot table report generation
that renders subtotals for all row/column intersections. Since all subtotals should be calculated anyway,
it is more effective to calculate them at once with LazyTotals=false.
The typical scenario for generating pivot report from a big data cube (LazyTotals=true):- slicing by dimensions needed for pivot table with PivotData.Slice method (LazyTotals=false)
- use sliced PivotData with PivotTable view to iterate through pivot table rows/columns
It is possible to render pivot table in WinForms/WPF/UWP apps in one of the following ways:
- export PivotTable model to DataTable (with
PivotTableDataTableWriter
) and bind it to DataGrid control. This approach is useful if you don't need labels grouping and other advanced rendering options. - use
WebBrowser
(WinForms) or
WebView
(WPF/UWP) to show HTML provided by
PivotTableHtmlWriter
PivotData SDK can be integrated with NReco.NLQuery library for reports generation by end-user's search-like queries. You can check how it works here: PivotData microservice example.
If you're interested in this feature and want to get an example of NLQ feature please contact us .
- PivotData OLAP library
-
PivotData library (NReco.PivotData.dll) can be used for
FREE in non-SaaS apps that have only one single-server production deployment.
Commercial license is required for in case of multiple deployments, redistribution as part of your product, or usage in SaaS app. - PivotData Toolkit
advanced components -
PivotData Toolkit (NReco.PivotData.Extensions.dll, web pivot table builder js components, code from "toolkit" examples)
requires a commercial license for production use:
- Standard per-developer license (perpetual, royalty-free).
All developers who are working on the project that uses PivotData Toolkit need to be licensed. It is possible to purchase a license for unlimited developers.
NOTE: this license cannot be used for SaaS applications. - SaaS application license (perpetual, per-deployment).
Anyone who works on the project may use PivotData Toolkit for the purpose of developing SaaS application. Each production deployment should have its own license. If you offer self-hosted (on-premise) version of SaaS application, each installation requires its own SaaS deployment license(s). It is possible to purchase SaaS license for unlimited deployments.
If you’re not sure what licenses are needed in your case, please consult our support team.
All commercial packages include 1 year subscription for email support (assistance in components usage, troubleshooting) and free component upgrades. After first year it is possible to renew the subscription with 50% of the full package price (this is optional). - Standard per-developer license (perpetual, royalty-free).
what's new NReco.PivotData
2023 Feb 02 | v.1.4.2 (latest) |
2022 Aug 26 | v.1.4.1 |
2022 Jun 13 | v.1.4.0 |
2020 May 27 | v.1.3.8 |
2018 Jul 18 | v.1.3.7 |
2018 Apr 06 | v.1.3.6 |
2018 Feb 26 | v.1.3.5 |
2017 Feb 01 | v.1.3.4 |
2016 Oct 18 | v.1.3.3 |
2016 Aug 14 | v.1.3.2 |
2016 Jul 05 | v.1.3.1 |
2016 May 10 | v.1.3.0 |
2016 Feb 04 | v.1.2.6 |
2016 Jan 13 | v.1.2.5 |
2015 Dec 11 | v.1.2.4 |
2015 Sep 30 | v.1.2.3 |
2015 July 27 | v.1.2.2 |
2015 May 26 | v.1.2.0 |
2015 Apr 10 | v.1.1.0 |
2015 Mar 09 | v.1.0.1 |
2015 Feb 11 | v.1.0.0 |
BI Toolkit changes NReco.PivotData.Extensions
2023 Sep 28 | v.1.7.0 (latest) |
2023 Apr 17 | v.1.6.2 |
2022 Jul 28 | v.1.6.1 |
2022 Jul 25 | v.1.6.0 |
2022 Apr 12 | v.1.5.5 |
2021 May 29 | v.1.5.4 |
2020 May 27 | v.1.5.3 |
2019 Dec 30 | v.1.5.2 |
2019 Dec 11 | v.1.5.1 |
2019 Mar 17 | v.1.5.0 |
2018 Aug 23 | v.1.4.2 |
2018 Apr 12 | v.1.4.1 |
2018 Jan 09 | v.1.4.0 |
2017 Sep 25 | v.1.3.8 |
2017 Jul 26 | v.1.3.7 |
2017 Jun 26 | v.1.3.6 |
2017 Mar 15 | v.1.3.5 |
2017 Feb 01 | v.1.3.4 |
2016 Dec 02 | v.1.3.3 |
2016 Oct 18 | v.1.3.2 |
2016 Sep 16 | v.1.3.1 |
2016 Aug 14 | v.1.3.0 |
2016 Jun 03 | v.1.2.0 |
2016 Feb 04 | v.1.1.0 |
2016 Jan 01 | v.1.0.2 |
2015 Sep 30 | v.1.0.1 |
2015 Jul 28 | v.1.0.0 |
2015 Jul 14 | v.0.7 |
2015 Jun 25 | v.0.6 |
2015 Jun 04 | v.0.5 |
more components
-
PivotData Microservice
On-premises NET8 app that provides web API for PivotData Toolkit functionality: create pivot tables & charts by SQL/MongoDb/ElasticSearch databases, export reports to Excel/PDF/JSON/CSV/Image. Can be used as a backend for web pivot table builder.
-
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.