Release Notes PivotData Microservice Documentation
Version 1.7.0 2021 Mar 16
- Added an alternative Clickhouse driver (ClickHouse.Client) that connects with HTTP(S) interface.
To use this driver it is enough to specify
Driver=ClickHouse.Clientin the connection string.
- Default ClickHouse driver (that connects with native TCP/IP protocol) was updated to fix 2 issues: now it supports LowCardinality data types + handles correctly high-scale decimals that cannot be represented with .NET System.Decimal type (now such values are converted to System.Double with a possible precision loss).
Version 1.6.9 2021 Feb 22
- Oracle: fixed issue "Cannot compose 'XYZ' aggregator state from field 'AAA': Specified cast is not valid" related to decimals handling in ODP.NET
- Connection secrets: now it is possible to define name-value pairs in appsettings.json (or ENV variables) and use @name placeholders in the connection strings/URLs. This is useful when cubes JSON config is loaded dynamically.
- Flat table: fixed issue with "Filter" condition generation: with exact-match hint is specified do not generate OR for partially-matched column names
- SQL connector: fixed issue with Expression-type measure uses "FirstValue" measure as an argument + "GROUP BY CUBE" option is enabled
- SQL connector: fixed SQL generation issues with "FirstValue" measure type (BigQuery: "Fields must contain only letters, numbers, and underscores", SQL Server: "the identifier is too long").
- Driver update: Oracle.ManagedDataAccess.Core (2.19.101).
Version 1.6.8 2021 Jan 17
- Expression-type measures: added ability to use pivot table totals in the formula:
- Expression-type members: added ability to specify a separate value for sorting purposes with
- Drivers updates: System.Data.SqlClient (4.8.2), Npgsql (4.1.7), ClickHouse.Ado (1.2.6), Snowflake.Data (1.1.4), MongoDB.Driver (2.11.5).
Version 1.6.7 2020 Nov 24
- JWT auth: now encrypted JWTs are supported (for decoding
TokenDecryptionKeyStringshould be set in the "Auth":"Jwt" section)
- JWT auth: alternative header name may be used instead of "Authorization" with
AltHeaderName(in the "Auth":"Jwt" section)
Version 1.6.6 2020 Oct 20
- SQL connector: fixed SQL generation issue when dimension name contains '-' (Type=Field)
- SQL connector: fixed SQL generation issue when flat table is ordered by column that is NOT included into the report
- CSV connector: better heuristics for multi-value columns detection + now it is possible to add a dimension with " (Split)" suffix manually (even if column is not detected as multi-value)
- ElasticSearch connector: added option to skip SSL certificate validation (useful if self-signed certificate is used)
- Pivot table: fixed high CPU load issue when report has multiple measures (>2) with 'running total' calculation
Version 1.6.5 2020 Aug 17
- SQL connector: now Snowflake is supported as a data source (with official ADO.NET provider is used)
- Flat table: now numbers/dates in Excel export are shown as typed values
- Report parameters: added
Sql.Rawfunction that can be used in parameter's expression to insert 'raw' (unescaped) string value into the SQL query
Version 1.6.4 2020 July 08
- now search API works for all supported data sources (not only for CSV cubes)
- added cube config "Search" section (affects search API)
- SQL connector: fixed issue with measure Type=FirstValue (incorrect SQL syntax when custom SQL expression contains escaped column name)
- Report parameters: now it is possible to check what dimensions/measures are used in the report (in "Expression")
- ElasticSearch: fix for 'Unexpected character while parsing path' when field name with a space used in a flat-table report
Version 1.6.3 2020 May 05
- Added support for IIS in-process hosting model in .NET Core 3.1 build
- Now PivotReport options
Labels.TotalsRowTextare applied for all export formats (not only for HTML output)
- Fix for 'Unknown dimension' error message
Version 1.6.2 2020 Mar 24
- Now official .NET Core build targets ASP.NET Core Runtime 3.1 LTS which offers better performace and decreases RAM usage.
Build for .NET Core 2.1 LTS is considered legacy but still provided until 2.1 LTS EOL (2021-Aug-21).
Build for .NET Framework 4.6.1+ now provided only upon request.
- Cube member format: added simple way to specify custom text if value is empty or null with
- Report parameters: fixed an issue with
@param[ tpl_if_param_defined ; tpl_if_not ]placeholders - now it is possible to provide alternative template for case if parameter is not defined.
- Excel export: preserve new-lines in text content cells
- SQL cube: now query parameters are included into console log output.
- Flat table: better SQL errors handling in certain cases (affects HTML/CSV/JSON exports only).
- Flat table: more relevant error message if report configuration doesn't contain columns at all.
- Pivot table: added special
nulloutput format that executes data source query and populates in-memory cache but don't render anything.
- Pivot table: fixed issue with incorrect table order by values in case of "Last N" limits.
- Cube members with Type=Expression: added
CubePfunction that is similar to
Cubebut accepts 2nd argument for parameters.
- Front-end demo / widgets changes:
- jQuery updated to latest 3.4.1
- legacy jQueryUI sortable replaced with sortableJS (supports touch screens)
- compatibility with latest select2 4.0.12+
Version 1.6.1 2020 Jan 31
- XMLA Client connector: now "InferSchema" is supported (dimensios and measures may be determined automatically)
- XMLA Client connector: added support of 'range' filters
- SQL Connector: now it is possible to use more than one 'parent' field in the "Conditional JOIN"
- SQL Connector: fixed issue with pivot report without dimensions when "Use GROUP BY CUBE" is enabled
- CSV connector: now when date-part dimensions are inferred their names have format like
- CSV connector: fixed issue with 'unknown measure type Median' error
- Pivot Report: now validation of max table size takes into account expand/collapse mode
- Pivot Report: improved performance of rendering in expand/collapse mode
- Pivot Report: new options
- Pivot Report: negative values in
LimitColumnshandled as 'show last-N'
- jquery.nrecopivotchart.js: added support of legends and animations
- Added example: flat-table report builder
- Updated database drivers: MongoDB.Driver (2.9.3), MySqlConnector (0.61.0), NpgSql (4.1.2), Oracle (2.19.60)
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)