Release Notes PivotData Microservice Documentation

Version 1.6.0 2019 Dec 12

  • XMLA Client connectornew: now PivotData microservice can connect to OLAP cubes that are accessible via XMLA-over-HTTP(S) endpoints like SQL Server Analysis Services and Azure Analysis Services.
    • custom MDX SELECT template, you can define custom sets using WITH
    • drill-down filter can be translated into MDX slicers (any-level drill-downs are possible)
    • report parameters are supported (only 'equals' comparison for dimension keys)
  • Added tabular report builder example (usual data tables that displays DB rows, no aggregation)
  • When Expression-type dimension uses Html.Raw function with 2 arguments, pivot table is ordered by 2-n argument (non-HTML value)
  • Additional specifiers for cube members "Format": format month number to short or full month name, format large numbers with k/M/B suffix
  • Difference calculation: now sub-totals are calculated if possible
  • CSV connector: fixed issue in flat-table API when both offset and sort are specified
  • CSV connector: added option to specify a separator explicitely
  • MongoDB.Driver upgraded to 2.9

Version 1.5.5 2019 Sep 30

  • SQL connector (any DB): now it is possible to calculate totals/sub-totals of custom-SQL measures with GROUP BY CUBE syntax. This feature is disabled by default and can be enabled with cube config "UseGroupByCube" option
  • Added ability to translate pivot table user-defined filter (textbox, also used by drill-down) into database query conditions when possible. This feature is controlled by cube configuration "PivotFilter" options
  • Added "FormatCulture" option (both global & cube-specific) to configure culture-specific formatting for numbers/dates
  • Added "Properties" option for cube members definitions. In this way it is possible to add your own metadata and use it in front-end code.
  • MongoDb connector: more memory-efficient handling of large aggregate query result

Version 1.5.4 2019 Aug 12

  • ElasticSearch connector: correct sorting of dimension number values calculated with 'painless' script (like year/month/day date parts)
  • ElasticSearch connector: added ability to define custom bucket aggregation type (JSON) for dimensions
  • ElasticSearch connector: added ability to define custom metric aggregation (JSON) for measures with "FirstValue" type
  • MongoDb connector: set HeartbeatTimeout=10 (seconds) if not specified in the connection string to prevent usage of 'zombie' connections from pool
  • Expressions: added Date.TimezoneUtcOffsetHours function that unlocks ability to use datetime 'range' filter for concrete timezone
  • Added global "AggregateRowsLimit" option (appsettings.json) to override default limit (1,000,000) for max number of aggregated rows

Version 1.5.3 2019 Jul 20

  • Pivot table rendering: now if cell value is 'object' or 'array' it is rendered in JSON format.
  • MongoDb connector: fixed issue with dimensions that use custom projection definition in flat table reports.
  • MongoDb connector: correct unwinds generation by InferSchema for fields with arrays of simple values.
  • MongoDb connector: inlcude incorrect JSON into exception in case of incorrect config.
  • ElasticSearch connector: fixed ES query generation for "in" condition + parse dimensions values as numbers even if ES returns them as strings (if data type is specified or get-mapping API is allowed).
  • jquery.nrecopivotchart.js: display only integer values for axes if all values are integers
  • jquery.nrecopivottable.js: added 'fixedHeadersDisableByAreaFactor' option that disables fixed headers if they take too much space
  • Expressions: now String.Join accepts any array/list or even single value, added Math.Round, Math.Sqrt, Math.Pow functions.
  • Connector driver update: MongoDB.Driver 2.8.1, Oracle.ManagedDataAccess.Core 2.19.3

Version 1.5.2 2019 Jun 17

  • ElasticSearch connector: now flat tables use 'scroll API' if rows limit > 10k.
  • ElasticSearch connector: fixed issue with pivot table without dimensions (grand total only).
  • MongoDb connector: fixed 'Duplicate element name' error (can occur when dimension is resolved with $lookup stage)
  • Expressions: now Html.Raw accepts (optional) 2nd argument for value that is used in non-HTML exports (Excel, CSV).
  • Expressions: now Parameter["param_name"] can be used in calculated cube members.
  • Connector drivers update: MySqlConnector 0.55, Npgsql 4.0.7
  • Better errors handling and other minor improvements

Version 1.5.1 2019 Apr 02

  • CSV cube: added ability to specify filtering expression for rows which unlocks usage of report parameters for this kind of data sources.
  • CSV cube: fixed parse issue of timestamps that end with "GMT+X:YZ"
  • CSV cube: added detection of multivalue CSV columns. These values may be deconstructed and processed separately when report uses CSV column dimension with suffix " (Split)".
  • Report parameters: added ability to specify an expression that is evaluated to resolve parameter value for the data query.
  • Export to image: now it is possible to render pivot table or flat table to PNG image.
  • Expressions: added Regex.Replace and Regex.IsMatch functions.

Version 1.5.0 2019 Feb 22

  • Expand/collapse mode for pivot tables with 2 or more dimensions for rows and/or columns
  • Expressions: now functions Html.Raw and Html.Link may be used for custom HTML in pivot table cells
  • New pivot report options: custom 'emtpy' label text, default value for empty cells, '...' text, "Totals" header text, sub-totals labels suffix
  • Fix: SQL errors in some cases when "Select Query" starts with "select * from"
  • Fix: correct handling of column names with spaces
  • Fix: correct handling of sub-objects in ElasticSearch results
  • Fix: row headers are not rendered if no columns and totals column is disabled
  • Fix: correct handling of expression-type measure when this is only measure in the pivot table
  • Fix: type-cast error in case of CSV source + flat table + filter by number-type column

Version 1.4.4 2019 Jan 22

  • MVC Core updated to 2.2, removed excessive dependencies (-9mb in distribution size)
  • Export to PDF: ability to define custom 'tokens' and use them in the template
  • ElasticSearch connector: better error handling of some strange responses
  • SQL connector: correct handling of select query with hardcoded 'ORDER BY'
  • export to Excel PivotTable fixes: when several measures are used, when label (either dimension or measure) has special symbols (like ampersand).

Version 1.4.3 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)