Virtual Data Model
A virtual data model enables users to integrate data from disparate enterprise sources and view them as a single entity. Using a VDM, any data source can be deployed as a virtual database source. This allows querying the source with AQL syntax to retrieve any kind of information.
Below you can see a virtual data model where two different sources: a database table and an Excel File are deployed as a virtual database. Once deployed on Astera Data Model as a virtual database, these sources can be queried with AQL.
Multi-Table Query Source
After deployment, a Multi-table Query source is used in a dataflow to call the VDM as a virtual database source.
To use a Multi-table Query source, drag it from the Sources tab in the Toolbox.
Now, right-click on the Multi-Table Query source in the dataflow designer to open its properties.
Select Virtual Database in the first window of Multi-Table Query source’s properties.
Open Connection to specify the credentials for the virtual database.
In the Database Connection window, you will see the Data Provider, name of the Server, and the Virtual Database that is deployed on that server. We only support Astera Data Model (ADM) as the data provider in this context, so it is selected by default. You can specify the Server and the Virtual Database in the screen shown below.
Click on Test Connection once you have specified the necessary information.
After the connection is successful, click on Ok.
Now select the table that you want to query as the Root. Both data sources that were deployed as a virtual database can be seen in the drop-down menu of Root.
We will select ‘Orders_Data’ as the root and then click on Ok. You can now see Orders_Data at the topmost node, and Customers_Data on the second node in the Multi-Table Query source object.
To view the ‘Select’ query for both the combined sources, map the Multi-Table Query source to another object in the dataflow designer. Centerprise auto-generates AQL so that the user can view the query simply by mapping the Multi-Table Query source to either a transformation or destination object.
In this example, we are mapping both the objects, Customers_Data and Orders_Data, in the Multi-Table Query source to a Passthru transformation object. To do so, drag the Passthru transformation object from the Transformations tab in the Toolbox onto the dataflow designer.
Next, we will map the top node of the Multi-Table Query source object to the top node of the Passthru transformation object.
Once the mapping is done, right-click on Multi-Table Query source object’s properties and click on Show SQL to view the ‘Select’ AQL of the integrated tables.
In the Query Preview window, you can see the ‘Select’ query for Customers_Data, and Orders_Data, which basically is a ‘Join’ between these two tables.
AQL has child-to-parent query syntax, which means that it queries the tables moving from a child table to its parent table. It enables users to perform any kind of complex joins using just a simple syntax. It uses the alias of the relationship created between the parent and child table to perform the join. Let’s examine this with an example.
In the AQL below, the highlighted part shows that all the columns in the table ‘Orders_Data’ are selected.
The table ‘Orders_Data’ is the child of table ‘Customers_Data’; therefore, the ‘Select’ query for this table is:
|
Syntax |
Query |
|
SELECT table_name.column1, table_name.column2, … FROM table_name
|
SELECT Orders_Data.OrderID, Orders_Data.CustomerID, … FROM Orders_Data
|
In the AQL below, the highlighted part shows all the columns in the table ‘Customers_Data’ are selected.
The table ‘Customers_Data’ is the parent of the table ‘Orders_Data’. To select columns from the parent table, the child table uses the alias of the relationship created between the two tables. Here the alias of the relationship between the table ‘Customers_Data’ and ‘Orders_Data’ is ‘Customers’. Therefore, the ‘Select’ query for this table is:
|
Syntax |
Query |
|
SELECT table_name.alias.column1, table_name.alias.column2, … FROM table_name
|
SELECT Orders_Data.Customers.CompanyName, Orders_Data.Customers.ContactName, … FROM Orders_Data
|
In this simple way, a ‘Join’ is performed between ‘Customers_Data’ and ‘Orders_Data’. You can join multiple tables using this method without having to write any code.
Other Applications of AQL in Centerprise
AQL can be used to query the virtual database sources for many more cases other than ‘Select’ statement or ‘Join’ query. Let’s discuss a few of them below:
‘Where’ clause
You can generate a query with a specific ‘Where’ clause that fulfills your data retrieval requirement by using Filter in the Multi-Table Query source object. Let’s say you want to get data about all the customers and orders shipped in the USA.
To do so, open Multi-Table Query source’s properties and go to the second window, which shows the Query Source Layout.
To add a Filter, click on the Add a filter to the query icon shown in the screenshot below:
Once you click on the Add a filter to the query icon, you will get the option to select the field on which you want to apply the Where Clause. You can also define the criteria on which the clause will be applied.
Now open the drop-down menu of Path and select ‘ShipCity’ from the table ‘Orders_Data’.
After selecting the field, we will specify the criteria by writing ‘USA’ in front of the Equals to sign under Criteria.
Now click on Ok and right-click on Multi-Table Query source’s object and select Show SQL to see the query with the ‘Where’ clause generated by Centerprise.
Value Ranges
AQL also allows you to generate a query only to retrieve data that falls in a particular range. Let’s say you want to get records of all the customers and orders where freight cost falls between 50 and 100.
To do so, you will add a new filter in the Multi-Table Query source object, select ‘Freight’ as the field on which the filter will be applied, and click on Other and select In range from the drop-down list. Here, we are using the criteria In Range, but you can use any criteria from this list and Centerprise will generate the query for you.
Now write 50.0 in the From box and 100.0 in the To box.
Now click on Ok, right-click on the Multi-Table Query source, and select Show SQL to see the AQL generated for the set value range. This is the query that you will see:
Writing AQL Queries in the Query Window
Another way to retrieve data from virtual database sources is by writing custom queries in the Query Editor window.
To do so, click the File in the menu toolbar, hover over New, then Files, and click on Query to open the Query window.
This will open a query window along with a Database Connection screen where you need to connect to the ADM Server and query a Virtual Database.
Once the credentials have been specified, you can write any customized query in the window shown below. Let’s say you want to view the fields ‘CustomerID’, ‘CompanyName’, ‘ContactName’, ‘ContactTitle’, and ‘Address’ from the table ‘Customers_Data’. The query for this is shown in the screenshot below:
To run this query and see the output, click on the Execute Query icon on the top left.
0 Comments