Guided Query Validation in Embedded SAP Analytics Cloud – Part 1
This blog post talks about the Guided Query Validation – Phase1 feature provided to the Lob’s who is using Integration Service for embedding SAP Analytics Cloud.
When a story is imported into an embedded SAP Analytics Cloud tenant, it is possible that there is a mismatch between the metadata between the story and the actual columns/fields available in the Lob’s database. In such cases, a calculation view cannot be created for those story. If a story has a model whose metadata is inconsistent with the database, we term the story as broken. Even though the story can be imported into a SAP Analytics Cloud tenant, it cannot be opened or edited by a user.
A user will come to know that a story is broken only when he opens it. The user may like to fix the inconsistencies in the story by editing the metadata of the embedded queries.
Currently, if number of errors of this nature are more than one, we show only one error. Once the user fixes that, the next one is shown. This is not a useful and effective way to guide and allow users to fix these mismatches.
So as part of Guided Query Validation provides the below feature in the Embedded SAP Analytics Cloud application:
- Indicate that a story is broken, i.e., the metadata in the query/models is not consistent with the database
- A list of all errors for the query/model in a single listing, so that they can be addressed in one-go by the end-user
- Provide detailed information regarding these errors, e.g., the objects, columns, joins, filter etc. where the mismatch is detected
Note: In Phase 1, the scenario where metadata in the model/query is inconsistent in the target system/DB due to removal of Column from system has been handled.
The use case that is addressed here is the problem where the query builder persists old metadata information on which the query was last saved. Because of this, when the user tries to create a query again, it fails as the old metadata information is no more valid now. This happens in scenarios where the user is trying to edit a standard content document that is stored.
The main part of the backend is the Lob’s endpoint and the information that we need to pass to them. Lob endpoint would be /validation
During query call, if some objects are missing in the metadata and the query fails then the Integration Service will communicate to the Lob’s endpoint. The Lob’s endpoint using remote API call returns response containing information about those missing objects. This API response is passed to getValidationError(error) method, where parsing process of response is done.
Steps in UI workflow:
- Open the embedded SAP Analytics Cloud story
- If it is a broken story, then perform Edit -> ‘Edit data source’ from the toolbar option
- User will be taken to Query Builder page
- User Clicks on ‘Preview Query’ /Finish in Query Builder
- Based on the Lob’s response on those metadata missing the Validation errors will be thrown up in Query Builder toolbar as shown in the below screenshots
- User can click on the Validation option and view all the error details
- User has to fix those validation errors manually and then proceed with ‘Preview Query’/ Finish workflow
Only once all the inconsistencies in the query have been resolved, the user can move to the story or user can still go back to story by canceling the operation.
When the user lands to Query Builder page and do Preview Query/Finish -> Validation errors Status 1st pop-up will be shown up as below
When user Clicks on any validation error from above dialog ->Validation errors Details in the 2nd pop-up will be shown up as below
When the embedded SAP Analytics Cloud story is broken due to the metadata in the query/models is inconsistent with Lob’s database then to fix those Validation errors in the query builder UI in a user-friendly manner the guided query validation feature will be helpful to the end-users.
Note: More features are planned to be delivered in phases. In Phase1, when the Column/Objects that was used in the query/model earlier is not available now then “Column not found” or “Datasource not found” errors will be shown.
Thanks for reading the blog post!