Designing Temporal Analytics for Woo

MariaDB 10.3 provides built-in functionality called System Versioning (1). This has several use-cases, such as supporting sophisticated audit trails. In fact, MariaDB provides a plug-in that can be used to log a server’s activities for exactly this purpose (2).

System Versioning can also be used to produce data analytics that are of interest in a retailing environment.

For example, a WooCommerce end-user may be tasked with producing a graph that tracks, say, the price changes of store products over some unit of time.

Producing such a report using native SQL and the existing WooCommerce database schema is far from straightforward.

This is due to the latter’s underlying complexity – which is a result of WooCommerce reliance on, and conformance to, the semantics of the WordPress data model (3), which is extensible — a feature that WooCommerce uses to its full advantage.

This extensibility reduces the need for plug-ins to overpopulate the WP database with their own custom tables.

When shop owners run WooCommerce’s pre-installed or custom reports to obtain business-related information about their store, there are  several important factors to consider.

Multi-user ad hoc querying of a production WooCommerce platform can result in performance degradation of transactional execution (i.e., the shopping cart experience), slow down shopper catalog browsing, and impact the refresh rate of the somewhat sluggish WP Admin panel.

Moreover, particularly in entry-level hosted environments, running frequent reports may also cause a store owner to exceed a given plan’s CPU time in a shared server configuration

Finally, it can be difficult to produce customized WooCommerce reports without costly programming that requires skilled knowledge of WooCommerce internals. (For an excellent tutorial on the semantics of WP’s core database, here is an excellent multi-part tutorial.).  

A separate DSS environment would be the ideal solution proposed by this experimental project – which I am calling Project SB, for Sandbox.

Theoretically, this environment could be hosted on an end user’s local machine; alternatively, it could be hosted in the cloud on a Saas basis. 

For example, an end user (such as a store owner, or someone who is authorized to examine transactional data) may wish to know how many widgets were sold between time t1 and t2, and group the result of this query by price.

An end-user should be able to formulate such a query as simply as possible – without needing to write extremely complex SQL statements or add mysterious PHP snippets to a child theme that they might be asked to create.

It  is even possible to envision the implementation of a DSS/Analytical database in a SaaS model using, say,  MariaDB’s AX new columnar storage engine (4), that would keep the DSS and transactional envs in sync in quasi real time.

 
  1. see https://mariadb.com/kb/en/library/system-versioned-tables/
  2. see https://mariadb.com/kb/en/library/mariadb-audit-plugin/
  3. see https://codex.wordpress.org/Database_Description
  4. see https://mariadb.com/kb/en/library/mariadb-columnstore