Whether to extract and make available data or to manage it, SQL queries are omnipresent. Therefore, being able to document them and identify their impacts is of great interest. That's why this question arises "how to represent the different types of queries while fitting into the DataGalaxy meta model?".
Article objective :
- Represent SQL queries in DataGalaxy
- Have a robust documentation solution to understand their impacts
Selection queries
Concept
Selection queries allow extraction and provision of information to technical or functional users. We can represent it as follows
Therefore, we distinguish between a data source and a query result restitution that allows users to access the data.
Representation in DataGalaxy
In DataGalaxy, we can translate this schema as follows
To create the link between the set and the database, most use case will only requires a relation at the table and data set level
Exemple
In this example we retrieve data from the ODS database to make them available in a data set named Query : product analysis
To go further
Three questions may arise:
- The granularity of the link: at what level should it be created: at the level of a table as in the example and at the level of the data set as in the example? at a finer level? it depends on 3 elements: your use cases, your automation and maintenance capacities
- Query identification: in order to facilitate the identification of queries, we can of course play on the notion of functional name vs. technical name, but also position a specific attribute in order to highlight them via filtered views.
- Classification: in order not to overload the tree structure of the usage module, it can be interesting to classify the requests under one or several applications.
Management queries
Concept
Insert, update and delete queries concern the management of data. We could schematize it as follows:
Représentation in DataGalaxy
Unlike the extraction query, this type of query is not intended to publish data. It is rather a technical transformation. To represent them, it is better to take an object from the processing module. This will allow you to explain the transformation that has taken place.
Exemple
In this example we delete data present in the table range_product of the ODS. We can see the difference with the extraction request which is in green, that is to say functional.
To go further
As for the data extraction request, the question of the granularity of the information arises. Here again, the balance must be struck between the needs of the use case and the maintenance capabilities.
Noted that an industrialization of the information feedback can be done thanks to APIs.