Logical table

A logical table is a virtual, single-field table without records. Being treated as an ordinary table, it is mainly used for defining the dimension hierarchy in scenarios where the corresponding physical tables are absent. For example, usually there isn’t a physical table corresponding to the time dimension, and the logical table can be created to do the same work.

For example, we can define the date hierarchy in demo.glmd through three logical tables – date, year and month:

Note:

1.  Here are system pre-defined level functions for date dimension:

2. A inter-level expression can be regarded as performing a computation from low to high in terms of granularity. For example, you can obtain the year from year-month-day in a date dimension, but cannot obtain year-month-day from the day.

3. A target dimension can be obtained from different source dimensions. The “year” dimension, for instance, can be obtained through aggregation either from year-month-day or from e “year-month”. Therefore, two inter-level expressions need to be defined for obtaining the “year” dimension from date table’s date value or from month table’s yearmonth value.

Next, you can directly use a defined date level function in the DQL query, as shown below:

ReturnedPmt table’s RDate field is date type. In the table’s foreign key definition, a foreign key association is established between the field and date table’s date field, as shown below:

This way you obtain year and month information of RDate through the DQL query. For instance, RDate#year obtains the year of RDate and RDate#ymonth gets year and month of RDate.

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

DQL: SELECT sum(Amount) Sum_Amount ON year FROM ReturnedPmt by RDate#year

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