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 CUBEsyntax. 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.TimezoneUtcOffsetHoursfunction 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.Joinaccepts any array/list or even single value, added
- 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.Rawaccepts (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.Linkmay 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
valueFormattingoption 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
- 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
- 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:
- added ability to specify custom SQL expression for dimension
- bugfix: pivot report without dimensions
- MongoDB data sources:
- 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 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
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)