Web pivot tables by SQL DB Data Source Setup
appsettings.json
file PivotDataService:Cubes
section.Cube config for SQL database should have
"SourceType": "sqldb"
and an additional "SourceDb"
section:
{ "Id": "orders", "Name": "Orders from MySQL sample DB", "SourceType": "SqlDb", "SourceDb": { "Connector": "mysql", "ConnectionString": "Server=db4free.net;Database=nreco_sampledb;Uid=nreco;Pwd=HRt5UbVD;", "SelectSql": "SELECT o.* FROM orders o LEFT JOIN customers c ON (c.customerNumber=o.customerNumber)" }, "InferSchema": true, /* dimensions and measures are determined automatically by SELECT query resultset */ "Dimensions": [], "Measures": [], "Parameters": [] }
"SelectSql" determines tabular data that can be used in a pivot table report.
If you don't need to perform any row-level calculations in SQL it is recommended to use SELECT * FROM ...
or SELECT mainTablePrefix.* FROM ...
instead of explicit list of columns - in this case microservice replaces *
with columns list needed for concrete report.
Also it is much more effective to resolve lookups after grouping stage; "Star-schema support" section explains how to configure conditional post-group JOINs.
List of supported connectors:
Connector | Description |
---|---|
mssql |
MS SQL Server, Azure SQL. ConnectionString sample:
Data Source=hostName;Database=db;User=user;Password=password;Complete list of connection string options: System.Data.SqlClient documentation. |
mysql |
MySql, MariaDB, MemSQL. ConnectionString sample:
Server=hostName;Database=db;Uid=user;Pwd=password;ConvertZeroDateTime=True;Complete list of connection string options: MySqlConnector Driver documentation. |
postgresql |
PostgreSql, Amazon Redshift. ConnectionString sample:
Host=hostName;Port=port;Database=db;User ID=user;Password=password;For Redshift in some cases you'll need to add: Server Compatibility Mode=Redshift;
Complete list of connection string options: NpgSql .NET Driver documentation. |
clickhouse |
Yandex ClickHouse. ConnectionString for native TCP/IP protocol .NET driver (ClickHouse.Ado):
Compress=True;Compressor=lz4;SocketTimeout=15000;Host=hostName;Port=portNumber;Database=default;User=default;Password=ConnectionString for alternative HTTP(S) interface .NET driver (ClickHouse.Client): Driver=ClickHouse.Client;Compression=True;Host=hostName;Port=8123;Protocol=http;Database=default;Username=default;Password= |
sqlite |
Local SQLite database file. ConnectionString sample:
Data Source=@CurrentDirectory/App_Data/northwind.db;(you can use @CurrentDirectory token to specify working directory of the microservice.) |
oracle |
Oracle DB. ConnectionString sample:
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostName)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=serviceName))); User Id=user;Password=password; |
snowflake |
Snowflake DB. ConnectionString sample:
account=test;user=test;password=userpwd;warehouse=wh;db=snowflake_sample_data;host=test.snowflakecomputing.com; |
odbc |
Database that has ODBC Driver. ConnectionString sample:
Driver={any odbc driver's name};OdbcKey=someValue;(Check concrete ODBC driver's documentation on available connection string options) Normally you'll not use "odbc" connector directly because most databases have own SQL dialect and ODBC connector should be configured as custom connector. |
<custom connector> |
Custom connector defined in "PivotDataService.DbConnectors" configuration entry. |
NOTE: "InferSchema" is useful for one-time or for development purposes; for production use it is better to specify list of dimensions and measures explicitely to avoid some overhead and get full control over cube member options.
Dimensions
In case of SQL data source dimension name should refer to the column of the specified SQL SELECT:
"Dimensions": [ { "Name": "status", /* or 'o.status' */ "LabelText": "Status" } ]
It is possible to specify SQL expression to calculate dimension values on database level:
{ "Name": "orderDate_year", "LabelText": "Order Year", "Params": [ "YEAR(orderDate)" ] }
You can use report parameters in this custom SQL (starting from v.1.8.5).
.Measures
Measure parameter (if needed by the aggregator type) should refer to the column returned by "SelectSql" query:
"Measures": [ { "Type": "Count", /* other types: "Sum", "Average", "Min", "Max", "FirstValue" */ }, { "Type": "Sum", "Params": [ "orderNumber" ] /* refers to a column from SQL query. This can be an SQL expression */ } ]
Like dimensions each measure should have an unique name; if property "Name" is not specified measure name is generated automatically by its type and list of parameters. For the sample config from above suggested names will be "Count and "SumOforderNumber".
It is possible to use non-standard SQL aggregate function with "FirstValue" measure type:
"Measures": [ { "Name": "usersCountUnique "Type": "FirstValue", "Params": [ "COUNT(DISTINCT user_id)" ] /* custom SQL */ } ]
You can use report parameters in this custom SQL (starting from v.1.8.5).
Parameters
In SQL data sources report parameters could be used in SELECT query as ADO.NET command parameters, for example:
"Parameters": [ { "Name": "country", "DataType": "string", "Multivalue": true } ]
"SelectSql": "SELECT o.* FROM orders o LEFT JOIN customers c ON (c.customerNumber=o.customerNumber) WHERE 1=1 @country[ AND c.country IN ({0}) ]"
Token @country[ AND c.country IN ({0}) ]
refers to the parameter with name "country".
It works in the following way: if report parameter is empty, nothing is added to WHERE; otherwise "AND c.country IN ('Austria')" condition is added.
NOTE: Multivalue parameters should be used only with "IN" operator.
Star-schema support
If database has star schema with main facts table and dimension tables it is possible to resolve lookup values without JOINs in the main query ("SelectSql"). Instead of that JOINs could be applied after data grouping only if they are needed for the concrete pivot table report; this is possible with "JoinsAfterGroup" config entry.
Lets assume that 'facts' table has 'company_id' column that refers to 'companies' dimension table with 'id' and 'title' columns. The following config illustrates how to configure conditional JOIN to resolve 'Company Title' dimension:
"SourceDb": { "Connector": "...", "ConnectionString": "...", "SelectSql": "SELECT * FROM facts", "JoinsAfterGroup": [ { "JoinSql": "LEFT JOIN companies c ON (c.id=t.company_id)", /* "SelectSql" has an alias 't' */ "ApplyOnFields": [ "c.title" ] /* join is applied only for specfied dimension names */ } ] }, "Dimensions": [ { "Name": "c.title", /* refers to join specified in "JoinsAfterGroup" */ "LabelText": "Company Title", "Params": ["company_id"] /* FK column in facts table for group-by */ } ],
You can use report parameter tokens (@ParamName
) in "JoinSql" template in the same manner as in "SelectSql".
Define custom ODBC-based connector
It is possible to customize default database connector setup by defining custom connector type in appsettings.json
config (in "PivotDataService" section):
"DbConnectors" : [ { "BaseConnector": "odbc", "Connector": "odbc-access", "IdentifierFormat": "[{0}]", "ConnectionStringTpl": "Driver={{Microsoft Access Driver (*.mdb)}}; DBQ={0}", "SelectWithLimitTpl": "SELECT TOP {1} * FROM ( {0} ) as t", "SqlFuncTpl": { "Year": "YEAR({0})", "Month": "MONTH({0})" "DayOfMonth": "DAY({0})" } } ]
In this case custom connector is based on "odbc" and it defines database-specific options for Access ODBC driver.
Option | Description |
---|---|
BaseConnector |
Base SQL connector identifier (listed above). For ODBC driver use "odbc". |
Connector |
New customized connector identifier. |
IdentifierFormat |
Format string for database identifiers (table and column names). In most cases this will be [{0}] (SQL Server-like), "{0}" (SQL-standard), `{0}` (MySql-like) |
ConnectionStringTpl |
Template for the connection string that allows you to incapsulate part of the connection string (say, Driver=Some ODBC driver name;{0} ). Specify {0} to use connection string from cube config without modifications. |
SelectWithLimitTpl |
Template for SQL command that applies database-level LIMIT syntax. |
SqlFuncTpl |
Define SQL expressions for derived dimensions like date parts for dates. They are used by "InferSchema" option and you don't need to specify these templates if you don't use infer schema feature. |
How-to for ODBC-based connectors: