Features
New custom view SQL editor
An SQL code editor has been integrated into xMart so that custom SQL views can be created and edited in the application without needing direct database access.
To access the new SQL editor, navigate to Model/Custom Views in your mart and either create a new view using the “+ New” button or open an existing SQL view.
The editor can be opened in full-screen mode using the Maximize button (use the same button to restore the normal view).
- Press Run to test/run the entire query or a selected part of the query.
- Press Format to format the query.
- Use the comment/uncomment button to toggle commenting on selected lines.
- Use the Save button to save the query, even if incomplete or invalid. Saving a query does not create a new version of the query and does not affect the current version used by the data catalog and the OData API.
- Press Publish to apply the query to the database and create a new version. The new version becomes immediately available in the data catalog and OData API.
- As before, use Register View to make the view avaialble on the Data page and via API. If not registered, the view is still created in the database and is available from 3rd party tools like Power BI or Site Finity. Unregistered views can also be used in pipelines using the GetDb command (see below).
- Use Drop View to permanetnly hard delete the view.
For very large views, a miniaturized view of the query appearing on the right can be used to quickly navigate between sections.
SELECT statements can be arbitarily complex but only the mart’s and REFMART’s schemas can be queried.
All of the following are supported: joins, top, order by, group by, aggregations, common table expressions, subqueries, date and math functions, etc.
No “dangerous” or other type of SQL is supported.
The CUSTOM_VIEW_MANAGE permission is required to be able to create and edit custom SQL views. During the 4.28 upgrade process, the CUSTOM_VIEW_MANAGE permission was added to every role which had MART_MANAGE. Those with MART_MANAGE can further assign the CUSTOM_VIEW_MANAGE to those who need it.
Currently, CUSTOM_VIEW_MANAGE permission gives the user the ability to create and edit any custom view in the mart and also to use any table or view inside the custom view. This is the same permissions as currently exists when editing views directly in the database but this all-or-nothing security model may change in future versions.
Tabular view of data catalog
On the Data page, a tabular view of a mart’s tables and views has been added. This is an alternative to the cards view. The system remembers will remember your choice. There is an option to group rows by a selected field, for example, grouping by the type of the object(table or view).
This feature is available on the Data page of every mart and also on the top-level “global” Data page.
Search pipeline source code
You can search the xml content of a pipeline if you check the “Search content” checkbox. Matching source code lines are displayed in the search results.
If you enter multiple words such as “loadtable ref_places” the system will find lines in the pipeline that match both “loadtable” and “ref_places”:
Home page data widget
A new data widget is available on a mart’s home page to faciliate navigation to a user’s favorite tables or views. A table or view can be marked as a favorite on the Data page of a mart. It is also possible to sort the items by last updated, number of rows, etc.
Get mart data using SQL in GetDb command
The GetDb command can now be used to write SQL SELECT statements to retrieve data from a mart. This is an alternative to the GetMart command.
SELECT statements can be arbitarily complex but must conform to the same rules as for the new custom SQL editor released in this version, ie, only SELECT statements are supported and only the mart’s and REFMART’s schemas can be queried.
All of the following are supported: joins, top, order by, group by, aggregations, common table expressions, subqueries, date and math functions, etc.
It is possible to select data from unregistered views! For example, when materializing a slow custom view as a table you probably only want the table and not the slow custom view to appear on the Data page and in the API.
Enhancements
Hide statistics for casual users
The statistics widget on a mart’s home page is now hidden unless you are a mart admin (ie have MART_MANAGE permission).
More user-friendly variable names during upload
Variable names are displayed in a more readable and user-friendly way during data upload. The system puts white space in obvious places like before a new capital letter or number and replaces punctuation like “_” and “.”.
Better support for huge downloads
In this version, it is now possible to download huge tables such as a table with 57 million rows and 23 columns. Previously this would result in an out-of-memory error.
Filter data rows for a data loading issue
When previewing a batch during a data upload, on the issues tab, it is now possible to filter the data rows by clicking on a column like elsewhere in the application.
Other changes
- #5290: DbMigrate - init database issues reported by Alexandru
- #5374: Duplicated system sql from each load balanced server
- #5375: Custom view labels not responding to code/title switch
- #5382: Improve authorization denied message
- #5371: Mart deploy batch preview screen keeps switching to NEW
- #5385: Data download, sync system columns selection
- #5395: Form Download error when user has no MODEL_UPDATE
- #5400: Always show version info in pipeline history
- #5372: Lookup Issue resolution preview does not take LookupInterval into account (due to SkipIntervalInIssues)
- #5403: What is the CRON to run at 8am on the first Sunday of every month?
- #5406: Data Editing “Unable to cast object of type ‘System.DBNull’ to type ‘System.String’” error
- #5050: Custom IsStructure pipeline - fields can be attached to table of other mart
- #5417: Deleted Fields should be made non-Mandatory
- #5410: Error in Form default pipeline if SubForm is empty (edit: required field non mapped)