MySql connector for pivot tables, charts

SeekTable can be used as reporting tool for MySql: this database is fully supported by SQL data source. Protocol-compatible databases like MariaDB or SingleStore can be used with this connector as well.

Tabular data schema can be determined by the table name or custom SQL query automatically, or you can configure dimensions/measures manually. Once configured, MySql-based cube can be used for live reporting even by non-IT users with simple UI, without need to write an SQL.

• MySql is used as a live data source: no need to refresh reports as users always get actual data (direct query mode).
  This means that your MySql server should be able to execute aggregate queries fast enough - ideally in seconds. Usually this is not a problem if configured MySql query returns a reasonable number of rows; otherwise, you may try to configure report parameters to reduce data that needs to be aggregated or use pre-aggregated data views. For near real-time reporting on big tables MySql data can be mirrored to specialized analytical columnar databases like SingleStore, ClickHouse or DuckDB.
• Reports may be shared with the team or published for embedding (or sharing via public link). Users can subscribe to reports in a self-service manner.
• The cube itself may be shared too: users can create own reports without having direct access to MySql database.
• Web API can be used for automated reports generation (export to Excel, multipage PDFs).

How to configure MySql cube

1. Click the "Connect to Database" to open a new data cube configuration form.
2. Choose Data Source Type: SQL-compatible database (this opens SQL Settings section).
3. Choose Database Connector: MySql, MariaDB, SingleStore
   
4. Connection String should be a valid connection string for MySqlConnector driver. For example:

   Server=hostName;Port=3306;Database=db;Uid=user;Pwd=password;ConvertZeroDateTime=True;

   Server	The host name or network address of the MySQL Server to which to connect. Multiple hosts can be specified in a comma-delimited list. Do not use "localhost" or LAN server name; use public IP address or server's domain name.
   Database	The case-sensitive name of the database to use.
   Uid	The MySQL user ID.
   Pwd	The password for the MySQL user.
   ConvertZeroDateTime	if true ignore empty or zero date time values.
   UseCompression	if true compresses packets sent between SeekTable and MySql server (if the server supports compression).

5. Configure other required settings as described in SQL-compatible data source. As a minimum you need to specify Select Query (which determine's the dataset) and keep Infer dimensions and measures by dataset checked.

Date-part dimensions calculated with SQL expressions

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:

1. add new dimension with Type=Field
2. fill Name with some unique value: say, "date_column_year"
3. add one Parameter which should be a date-part MySql SQL expression:
   • For year: YEAR(date_column)
   • For month: MONTH(date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
   • For day: DAYOFMONTH(date_column)
   • For day-of-week: DAYOFWEEK(date_column)-1 + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)
   • For quarter: QUARTER(date_column)
   • For week-of-year: WEEKOFYEAR(date_column)

Troubleshooting

Connect Timeout expired

firewall on the server where MySql is hosted blocks incoming connections from SeekTable server on MySql port. To fix this add 34.210.10.23 to the firewall's white list.

Access denied for user

your MySql doesn't allow incoming connections from SeekTable server (IP: 34.210.10.23) for the concrete DB user credentials specified in the connection string; this may be fixed with GRANT command.