Semantic layer

There are three types of files on the semantic layer – metadata (.glmd), dictionary(.gdct) and lexicon (.glxc). The metadata file contains definitions of all pseudo tables and relationships between DQL tables. A dictionary file defines aliases for DQL tables or fields. A lexicon file defines search words.

Based on the metadata file, this subsection explains how to configure DQL tables in the metadata file, and how to create and configure dictionary files and lexicon files.

 

Note: There is a complete set of example metadata (demo.glmd)/dictionary (demo.gdct)/ lexicon (demo.glxc) files in【esProc installation directory】\esProc\esproc-services\datalogic\conf. They will be used in the following illustrations.

Basic metadata design

We edit a metadata file using Metadata Editor. A metadata file’s default extension is .glmd. We can deploy the file at the server side together with DQL Server’s drivers and connect to and access the client side through the standard JDBC.

Open the example file demo.glmd (where pseudo tables and DQL tables are already defined) and switch to “DQL” tab:

Following is the detailed illustration about DQL table configurations.

Ø  Table

There are two types of DQL tables – ordinary tables and logical tables. First let’s look at ordinary tables.

The Table source name for an ordinary table should be the same as that for the corresponding pseudo table. Table name can be modified as needed.

On Normal tab, we set fields for an ordinary table; and Data source should be existing fields in the pseudo table. The values under Field name are the same as those under Data source, but they can be modified when needed.

Besides clicking  to create a new ordinary table, we can also generate one based on one or more pseudo tables according to the following direction:

Click Tool>Generate table from pseudo table through the menu bar:

Select a table to be created on the following pop-up window:

Choose a way to handle the original table and the new table in the “When the table name already exists” drop-down list.

Keep both tables (add a suffix to the new table): When the new table takes the name of the original table, add extension _bak to the new table name.

Keep original table: Keep the original table and ignore the new table.

Replace the original table with the new table: Delete the original table and use the new table.

Ø  Primary key

The primary key identifies records uniquely. It inherits key of the composite table file for generating the pseudo table or primary key of the table sequence/in-memory table’s selected fields.

The primary key set for a DQL table is mainly used to define the foreign key and dimension. Just check the check box corresponding to the desired field to set it as the key. You can select one or both properties for the primary key: “Primary key contains time key” and “Primary key is ordinal number key”.

 

Similar to a database table, a DQL table can have a single-field primary key or a composite primary key. Values of the single-field key or value combinations of multiple key fields should be unique in the table.

The primary key of a DQL table in the metadata can be custom. It is important that the key field/fields is/are suitable and feasible as DQL Server will not check its legitimacy when defining multiple key fields. If the primary key values are not unique of if you select wrong property for the primary key, errors could happen during queries or aggregations.

See Advanced metadata design to learn about defining advanced metadata including logical tables and subtables.

 

Dictionary file design

We can use Metadata Editor to edit the dictionary file and save it as .gdct format. The dictionary file can be deployed on the server together with the engine. From the client side, users can access the metadata-defined data according to the alias of a defined table or field, field display format, displayed code value and or other configurations. The alias settings in the dictionary need to be analyzed and parsed on the server. They are unrelated to DQL and cannot be used in DQL statements.

Open Metadata Editor, click File>New through the menu and create the dictionary file:

Click “OK” button to get into the dictional file design interface.

Take sample file demo.gdct as an example. Its content is as follows when opened:

Let’s look at configurations in the dictionary file in detail.

Ø  Table item

The configurations of “Table item” include “Title” and various items under “Field item” for every table, Aggregate item.

Under “Field item”, there are table field names, measure names, foreign key name and other configurations, during which we can define aliases for Title, Field item and Aggregate item.

Ø  Dim item

In metadata, if a table’s primary key field is unique and not the foreign key field, the table is called dimension table, simply called dimension. In the “Dime item” configuration, we can define an alias for the dimension in the metadata.

 

Besides, we can also configure code field and display field under “Dim item”.

Ø  Class item

We can classify all tables in the current metadata file by configuring “Class item”.

Ø  Load metadata

Besides adding items to the dictionary file one by one through the menu, we can also directly load the metadata to add tables, field names, measure names, foreign key names and dim items to the dictionary.

Metadata is imported in the following way:

Execute Tool>Load logical metadata through the menu and select the metadata file to be loaded. Once the file is loaded, names table items, dim items and class items are by default namesake with those defined in the metadata:

If the option “Delete tables and fields that are not in metadata” is selected, tables, field items and dimensions that already exists in the dictionary but does not exist in the metadata will be deleted.

For example, we import sample file demo.glmd and the get the dictionary file that has the following structure: