DQL Tool

To make it more convenient to query pseudo tables, especially to handle multi-table association queries, esProc offers the DQL tool to query data using DQL (Dimensional Query Language).

Starting DQL tool

DQL tool is now provided only in esProc Enterprise edition. Its startup class is com.scudata.ide.spl.dql.DQL. Execute the file and start to define the metadata and use DQL to query data.

Start the DQL tool and you see the following interface:

Click “New” icon , or click File->New through the menu bar, to generate a new DQL configuration file:

Select “Logical metadata” to define the metadata:

Defining a pseudo table

To define metadata, you need to first define the pseudo table that will be used in DQL. The pseudo table must come from a composite table or a bin file. Click “Add pseudo table” icon , or click Edit->Add pseudo table through the menu bar, to add a pseudo table definition for the metadata:

Click “Select” button to select the data file for generating the pseudo table. You can change the variable name used by the pseudo table in DQL as needed:

Here we use the original composite table file emps.ctx. After data is selected for the pseudo table, the pseudo table name will be automatically changed to one the same as the composite table file name. You can also change it as you like. To define a custom field for the pseudo table, the process is similar to that of defining a pseudo table user-defined field explained in the above section. You just add one under the “User-defined field” and modify the corresponding field of the records in column definition. Click “Add row” icon , or click Edit->Add row through the menu bar, to add a new field. As the following shows, we add enumerated pseudo field Dept and binary dimension pseudo fields IfMarried and IfLady:

Double-click the cell under “Enum dim value sequence” to set up enumerated values for the enumerated pseudo field:

Similarly, Double-click the cell under “Binary dimension pseudo field” to set up values for the binary dimension pseudo field:

The DQL interface offers meticulous guide to configurations of pseudo table custom field in DQL, such as setting up enum field and bits field for defining apply expressions under “Enum dim value sequence” and “Binary dimension pseudo field” respectively.

Defining DQL tables and making DQL queries

Once you define a pseudo table in DQL, you can define tables used by DQL in DQL tab:

Click Tool->Generate table from pseudo table to generate a DQL table based on a defined pseudo table:

Click “OK” button to finish adding new pseudo fields:

With DQL, as table data originates from a certain defined pseudo table, you can click   or select Edit->Add table through the menu bar to add a DQL table. Select a defined pseudo table after “Pseudo table name” to make configurations line by line:

In DQL, a table corresponds to a pseudo table file. Similar to a table sequence and a database table, a DQL table has data structure and is made up of records. When a DQL table is successfully added, its metadata is stored as test.glmd. The primary key is the only field to identify records in a DQL table and inherits from the composite table file that generates the pseudo table. In the above emps table, no foreign key field is set up for it. A non-dimension field is called measure field.

Once a DQL table is defined, you can make queries in DQL with the DQL tool. Click Tool->DQL Query through the menu bar and perform queries via DQL statements. The DQL syntax is similar to the simple SQL, for instance:

Click “Execute” button to execute the DQL query. Query result is displayed on “Browse data” page:

DQL supports SQL-like syntax, such as where, as order by, for instance:

Now we add a pseudo table definition based on a multi-zone composite table. When setting up the multi-zone composite table file, you should not select a certain zone table file, but you need to change and use the overall composite table name:

 

The multi-zone table file’s zone table numbers are set up after “Zone”, and select the zone column after “Zone column”:

After a pseudo table is added, click Tool->Generate table from pseudo table on the DQL tab to add the new pseudo table as a DQL table:

The new DQL table is as follows:

The DQL query works the same as esProc code that queries the composite table via the pseudo table:

Similar to SQL, use * when querying all fields. The data queried from the multi-zone composite table will also be merged according to the first field.

As we use orders table, we now use multi-zone composite table orders2.ctx to generate a pseudo table definition:

In multi-zone composite table orders2.ctx, the first field is City. We need to set user field as the SID. When the pseudo table finishes configurations, create the corresponding DQL table for it:

Then import it to the DQL table definition, make query in “DQL query” window, and get the following result:

The result is the same as that obtained using esProc, which is merged by the first field.

DQL logical table dimension

DQL allows using a logically existed but not physically exited table as a dimension table. Such a dimension table is called logical table, which is often used in date handling.

In the following we will add several date-related logical tables on DQL’s Table tab. Select Edit->Add logical table through the menu bat to add a logical table definition:

Add a date-related logical table named Day, and click  to add the field definition day:

A logical table allows one-field primary key only. It is equivalent to a single-field table without records. Here we choose default data type for the date logical table. Actually, the logical table’s data will be associated with a datetime type field. That is to say, field type of logical table Day is date and such a logical table is called date dimension. In DQL table definition window, a logical table is marked with a different icon from the regular DQL table.

Next, we add two more logical tables YearMonth and Quarter and use them as another two time-related dimension tables – YearMonth dimension table and Quarter dimension table:

The YearMonth table is used to get the year and month part of the date data, and Quarter table to get the quarter that the date belongs to. Data types of both dimension tables are integer. Such a logical table is called integer dimension.

Since the three logical tables stores data according to time, there are associations between them actually. Based on Day table, you can get the year, month and quarter a date belongs to, and from YearMonth table you can find the quarter a month falls in. We can represent these associations with functions. If a dimension table can be associated with another dimension through a function’s computing result, we call it level function. For instance, we add a level function for dimension table Day and set up association relationships between it and another two time-dimension tables:

Set a level function on “Dim and level” tab under “DQL” page by giving a distinct name to it and creating association with the primary key of a certain dimension table. In a level function, we use the quotation mark ? to represent the primary key value of the current dimension table. Through the level function, we can get data on different levels in the YearMonth table or Quarter table from a specified date. A level function does not compute bidirectionally. You can get the quarter a certain date belongs to, but you cannot find out a date according to a certain quarter value. Now, we add a level function for YearMonth table and set up the association between it and the Quarter table through the function:

In orders table’ “Foreign key” configuration tab,  we add an association relationship between it and the date dimension table Day:

Add a new foreign key fk1 for orders table to associate with dimension table Day by matching orders’ OTime field with Day’s primary key day. Through dimension table Day’s level function, orders table can also association with another two time-related dimension tables YearMonth and Quarter. We can use the association relationships in DQL queries, for instance:

Through the level function, the date data stored in OTime field is able to associate with YearMonth table and get ym field data from the latter. In the DQL query, OTime#ym means getting corresponding ym values according to OTime field through the level function.

You can also use a multilevel dimension to filter data. To get orders of the 2^nd quarter, for instance:

Using dimensions in DQL helps to make most use of table data and meet more real-world business needs. One dimension table an associate with foreign keys of multiple tables, and dimension table’s level function is convenient to use. With the above date-related dimension, we can get data from different levels only by creating association between a datetime foreign key and a date dimension table.