Release Notes PivotData Microservice Documentation

Version 1.4.3 (latest) 2018 Nov 29

  • ElasticSearch connector: added support of parameter Type=Condition.
  • ElasticSearch connector: include into 'Count' aggregation results documents without dimension field ('missing').
  • SQL connector (all databases): resolve custom SQL expressions for dimensions used in parameter Type=Condition
  • PDF Export: better errors handling when underlying tool (wkhtmltopdf) returns error. Now PDF with error message is returned.
  • PDF Export: added more options that may be configured to prevent server overload with huge exports (execution timeout, process priority, max allowed input HTML size).

Version 1.4.2 2018 Nov 19

  • ElasticSearch connector: added ability to specify SQL-like filter expression (enables ability to use report parameters).
  • Now all 'technical' limits are configurable (max size of lookup for "Cube.Lookup" function, max number of rows for exports etc).
  • Bugfix: custom format is ignored in 'flat table' JSON export.
  • Bugfix: error when 'flat table' report has expression-type column.

Version 1.4.1 2018 Oct 22

  • Now native "oracle" connector is supported in .NET Core build of PivotData microservice
  • More efficient flat table API: async, do not use data buffer for SQL data sources (stream processing)
  • Dynamic cube schema URL provider: more efficent cache invalidation when cube config changes
  • MongoDb connector: add query limit to prevent loading of huge resultset that exceedes "AggregateRowsLimit"
  • ElasticSearch connector: correct infer schema for legacy ElasticSearch (<6.0)
  • ElasticSearch connector: fixed issue with empty grand total when no dimensions selected
  • Pivot table JSON export: fixed incorrect output for expression-based measures

Version 1.4.0 2018 Sep 14

  • added flat table API for reports by non-aggregated rows
  • added max pivot table cells option (helps avoid heavy server load if user selects incorrect dimensions for the report)
  • added examples of canned reports: pivot table, chart, dashboard
  • new heatmap options: HeatmapColor, and new Heatmap modes: RowTotal, ColumnTotal
  • avoid unnecessary data transformations in some cases
  • ElasticSearch connector: bugfixes
  • CSV connector: correct detection and parsing of datetime values with timezone like EST
  • SQL sources: fixed issue with incorrect SQL when measure is configured for column with table alias
  • SQL sources: fixed issues with incorrect SQL for "oracle" connector
  • Pivot table report: fixed 'Index out of range' when several expression-based measures use the same arguments
  • Pivot table report: added ability to combine measures from several cubes (data sources)
  • Pivot table report: added ability to resolve lookups by data from another cube
  • Pivot table export API: added valueFormatting option that can force values formatting for JSON/Excel output
  • Pivot table JSON export: fixed issue with incorrect handling of formula (expression-based) measures
  • Now main build is for .NET Core 2.1 (which is LTS release).
    Alternative build for .NET Framework 4.6.1+ is also available.
    Builds for legacy versions of .NET Core (1.0, 2.0) may be provided upon request.
  • Now official nreco/pivotdataservice docker image is available.

Version 1.3.2 2018 May 14

  • added ElasticSearch connector
  • MongoDb connector: fixed issue with inferred unwinds and "Element '_id' not found" error
  • make front-end code (index.html, webpivotbuilder.js) compatible with latest jQuery 3.3.x and select2 4.0.5
  • jquery.nrecopivottable.js: fixed headers now based on position:sticky for better UX and smooth scroll
  • fixed issue with CSV separator detection in some cases
  • fixed issues with keyword-based filter, now it is possible to specify conditions for dimensions
  • a lot of other fixes of minor bugs

Version 1.3.1 2018 Mar 22

  • added ability to filter by table values (like "count<50", "sum>=100")
  • Front-end (webpivotbuilder.js): now typeahead is used for filter autocomplete
  • alternative way to provide JWT auth in POST forms parameter (useful for report export download with HTML form)
  • MongoDb Driver updated to latest version (supports MongoDb 3.6)
  • added more helper functions to expressions evaluation context (like Date.Week, Date.DayOfWeek)
  • added "Quantile" (median) aggregator (can be used if aggregation is performed by microservice)
  • other minor-issues fixes
  • now .NET Core 1 build is provided only upon request

Version 1.3.0 2018 Feb 20

  • added microservice .NET Core 2 build (with "odbc" connector) in addition to .NET Core 1 and .NET Framework 4.6.1 builds
  • added "odbc" connector options for SQL-dialect
  • drill-down by click (front-end feature)
  • added "FirstValue" measure type for custom database aggregation functions
  • added export to Excel PivotTable
  • added chart rendering in PDF export (template is customizable)
  • added row-level expression-based dimensions in CSV connector
  • upgraded to latest PivotData Toolkit components:
    • CSV files processed 3x times faster with a new parser
    • sub-totals are calculated 10x times faster
    • many other fixes and improvements
  • bugfixes:
    • incorrect infer schema for PostgreSql
    • incorrect detection of CSV separator in some cases
    • prevent out-of-memory exceptions for large pivot tables

Version 1.2.0 2017 Sep 14

  • star-schema support for SQL databases: added "JoinsAfterGroup" option
  • star-schema support for MongoDb + more control over aggregate pipeline: added "CustomStagesBeforeGroup" and "CustomStagesBeforeGroup" options
  • added MongoDb connector options: "AllowDiskUse", "MaxTimeMS"
  • added .NET Framework 4.6.2 build (supports "oracle" and "odbc" connectors)
  • front-end updates: new chart types (stacked area, donut), smooth scroll on mouse wheel
  • SqlDb connector optimization: avoid inner select when possible (when "SelectSql" like "SELECT * FROM ...")
  • trace all SQL/Mongo queries to log when "LogLevel" for "NReco.PivotData.Engine" = "Debug"
  • added integration example (MVC Core): secure API with JWT, provide cubes config dynamically from main app
  • bugfixes:
    • SQL Server connector: incorrect infer schema
    • PostgreSQL connector: column does not exist exception for
    • CSV connector: correct handling of CSV headers with special symbols (like newline)
    • server error (500) returned by export API for some reports
    • ignore incorrect report order parameters instead of throwing an exception

Version 1.1.1 2017 Jun 10

  • added new options for pivot report measure:
    • Heatmap: enables heatmap table styling
    • RunningTotal: calculate running total value
    • Difference: calculate difference value
    • DifferenceAsPercentage: calculate difference value as percentage
  • SQL data sources:
    • implemented InferSchema option
    • added ability to specify custom SQL expression for dimension
    • bugfix: pivot report without dimensions
  • MongoDB data sources:
    • implemented InferSchema option
    • added ability to specify custom aggregation pipeline operators for dimension

Version 1.1 2017 May 22

  • new connector for CSV/TSV files:
    • automatic encoding/delimiter detection (tab-delimited, semicolon-delimited etc)
    • can infer cube schema automatically, no need to specify dimensions/measures
    • supports gzipped and zipped files
    • effectively handles date-part dimensions (year/quarter/month/day)
    • fast: query to 200mb CSV file is processed in seconds
  • new connector for Yandex ClickHouse database
  • added report parameters UI to sample web pivot builder (index.html), + enhanced web API to return parameters metadata
  • added support for dynamic data sources configuration (provided by URL, URL may use JWT context values)
  • SqlDb, MongoDb connectors: added ability to use parameter in the connection string (useful for SaaS-apps)
  • MongoDb connector fixes: throw error when sub-collection is used without unwind, avoid KeyNotFoundException when field is not present in the document
  • Fixed in-memory cache issue for some cube configurations
  • add logging to a file (configurable)
  • switched to .NET Core 1.1 (better performance and stability)

Version 1.0-final 2016 Dec 16

  • 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

Version 1.0-beta3 2016 Oct 29

  • 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

Version 1.0-beta2 2016 Oct 10

  • 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

Version 1.0-beta1 2016 Sep 26

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