SeekTable
This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

MySql reporting with web pivot tables

SeekTable can be used as reporting tool for MySql with built-in MySql connector of SQL data source. Protocol-compatible databases like MariaDB or SingleStore (former MemSQL) can use this connector as well.

Tabular data schema can be determined by the table name or custom SQL query (or defined manually), and then you can build real-time reports (pivot tables, charts, tabular reports) and perform ad-hoc queries to your MySql database with simple UI, without need to write an SQL.

There are no any limitations on the dataset size; your MySql should be able to execute aggregate queries fast enough (in seconds; 2 minutes max). This is not a problem in case of small tables; for large datasets database filters may be applied to reduce data for the aggregation, or you may use pre-aggregated tables, or mirror your data to specialized analytical database like SingleStore (former MemSql).

MySql connection setup

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).

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.