Is it possible to create a View in OQL query ?

0
Hi There,   I want to execute a SQL statement where it will create a view in the backend to render some of the data quickly into the insights page!   There is an alternate mechanism where we can integrate DatabaseConnector module and make use of Execute Query or Execute Statment actions in the microflow to execute SQL statements. But the DatabaseConnector does not work when we use the Mendix based cloud database since Mx does not provide database credentials in the procurement of private license node.   Any ideas would be appreciated. Thanks. :)
asked
2 answers
0

Abdul,

No, OQL cannot create views (or tables).  You can use a saved OQL query as a data set in Mendix.  A data set is similar to a view, but its only accessible from within Mendix and only using the built in reporting widgets.

What’s your use case?  Could be some members of the community have built a solution for a similar use case.

Mike

answered
0

If you want the Insights page to appear quickly, then you want to prepare the data as much as possible. Prevent the complex and/or data-heavy query to start when the user has requested it and is actually tapping his fingers waiting for it.

It helps if you set up a materialized view in the database. The view combines the data from the other tables and the gathering of the data in the materialized view takes place at an earlier stage. Serve that view to Mendix as if it is a regular table.

Refresh the view at smart times. If it is a sales-report about all the sales up to yesterday, refresh it during the night. If the user wants an end-of-day report, then agree on the time that the report becomes available, maybe shop-closing-hour. Or any other smart trigger suitable to your user’s needs.

It would be nice if the MBS has a caching-mechanism which would serve the data directly to the client-side, instead of doing the retrieve to the database, but as far as I know, the MBS does not have that (please correct me if I am wrong).

NB. You do need access to the database to do this. No problem for an on-premise setup. A Mendix Cloud application does not allow that by default. Maybe getting in contact with you Customer Succes Manager will help.

answered