We would like to be able to pre-define queries for use with Power BI centrally on Invantive Cloud.
This makes it easier to re-use logic across all reports.
This would actually be a view.
Currently Invantive SQL provides views within the context of a driver and also views across all data containers. However, there is no way to define them currently using a SQL-statement. Also, there is currently no way to persistently store such a manually defined view.
For a next release we will add the create or replace [force] view statement to Invantive SQL to define views that can operate on tables from all data containers.
Persistent storage is currently not planned, but the view can be defined in the On Startup SQL. Defining the view will not trigger data retrieval, so it is a cheap operation in terms of resource use and runtime.
This idea will be updated when the change is available.
Custom (database)views will be available on across all Invantive SQL-products starting release 22.0.14. This release will be available within 14 days for all products.
For more information see Using custom database views for SQL and BI tools.