Generated SQL and creating views (reports)

Version: 3.2.0+

SQL definition

For each ScaiPlatform widget that shows some tabular data, you can view the SQL that was used to retrieve the data from the data source. Simply click on the button from the widget menu.

You will notice that the SQL shown in ScaiPlatform is formatted and also quite human-readable. ScaiPlatform tries to avoid generating long query statements when retrieving the data from the SQL data source. The compact SQL query statements generated by the ScaiPlatform:

  1. avoid database vendor specific issues in parsing and optimizing long SQL query statements. Historically this has been an issue between many BI or SQL generating tools and database engines
  2. humans can understand, copy and use the generated SQL query statements. Compact SQL query statements can be easily understood, audited, recorded or used in other applications/tools.

Creating reports

After you have filtered, aggregated, pivoted or added custom fields to some data, you can quickly save the results as a view. Saving reports as database views has the advantage of keeping all logic related to report generation together with the data. Thus you benefit from:

  • being able to re-use the report logic/views in other tools. Thus, ScaiPlatform does not lock in your reports.
  • being able to backup, audit and limit restrictions at database level for all your reports.

To create a new view, simply open the window with the SQL definition and click on the Create view button. Then choose the schema were you would like to place the view in and the new (unique) name for the view. Take a look at the video from the SQL definition for an example.

Make sure that you are connected to the same source you are exploring data from. It may be that you are trying to create a view based on data you are exploring behind a dashboard graph that comes from a different source.

Limitations

  • Some database vendors do not allow creating views as select statements with subqueries. For these database vendors and versions, creating views is not supported: MySQL 5.5, 5.6