Logical table level function

A function whose parameter is a table’s dimension and that returns a different dimension from the original one is called a hierarchy function.

 

A logical table is a single-field table that does not physically exist within a pseudo table. There are two purposes of introducing the concept of logical tables:

One is to let users observe data on different levels of granularity by defining the hierarchical structure for a dimension.

Using the logical table and the level function together lets users define a date hierarchy like year, month and day and enables them to observe data on different levels of time granularity. We can also define a hierarchy like nation, province and city. Business requirements determine what type of hierarchy we need to create. Once such a hierarchy is defined, it can be used within the Metadata Editor throughout.

Generally, the number of tables (or logical tables) to be defined is the same as that of dimensions during hierarchy definition. Take the date hierarchy as an example, usually we just need to define three tables – year, month and day. If a table’s date field stores day data, associate it with the “day” table; if it contains month data, link it with the “month” table, and so on.

During metadata definition, a dimension definition will be automatically generated according to the related information in foreign key configurations when a foreign key association between tables is established. Then we can define computing relationships between dimensions on metadata’s Dim and level tab.

 

Example:

Define a level function on the date dimension. demo.glmd, for instance, defines the the levels of date data through three logical tables – Day, Year and YearMonth:

 

 

 

Note:

1) Use question mark (?) to represent the table containing the source field in a hierarchy expression. In the following instance, a value of “Year” dimension field is computed based on the month field in “YearMonth” dimension using expression int(?/100).

2) A level functioncomputes from the greater level of granularity to a less level. For instance, we can get years from days based on “Day” dimension but not vice versa.

3) A target dimension can be obtained from different source dimensions. A “Year” dimension, for instance, can be accumulated from days or from months/years. For the “Year” dimension, we need to define two hierarchy expressions to get its values from “Day” table’s date and from “YearMonth” table’s month/year.

4) Once hierarchical structures are defined, we can use one of them directly in DQL queries, as shown below:

 

ReturnedPmt table’s Date field is date type. In the table’s foreign key definition, we establish a foreign key association between ReturnedPmt table’s Date field and Day table’s Day field, as shown below:

This way we can get year and month data from Date field in DQL, like getting year in Date using Date#Year and year and month in Date using Date#YearMonth.

 

DQL: SELECT CustomerID,ID,Date,Date#YearMonth YearMonth,Date#Year Year,Amount,SellerID FROM ReturnedPmt

DQL: SELECT sum(Amount) Sum_Amount ON Year FROM ReturnedPmt by Date#Year

DQL: SELECT sum(Amount) Sum_Amount ON YearMonth FROM ReturnedPmt by Date#YearMonth

Example:

Define a level functionon an integer dimension. demo.glmd, for instance, defines the following hierarchy using Province table, Area table and City table:

 

Here are DQL query statatments and results:

DQL: SELECT CustID,CustName,Contact,ContactTitle,CityCode,CityCode#Province Province,CityCode#Area Area FROM Customer

DQL:

  SELECT count(CustID) CustNumber ON Area FROM Customer BY  CityCode#Area

DQL:

SELECT count(CustID) CustNumber ON Province FROM Customer BY  CityCode#Province

The other is to use a logical table as a database view. In this case logical tables can be treated as ordinary tables, for which we can define a series of properties like primary key and foreign key. The effect is the same as operations on real tables.

Tip:

With concepts of the logical table and the hierarchy function, users are able to observe data on a desired level and manipulate data more flexibly.