1. Sorting and Level Display
Note: In the preceding example, the ordinalcolumn and namecolumn attributes of the <level> element affect how the level is displayed in the result. The ordinalcolumn attribute specifies a column in the hierarchy table. It provides the order of Members for a given level, and namecolumn specifies the column to be displayed.
For example, in the monthly level in the preceding example, the datehierarchy table has two columns: Month (1 .. 12) and month_name (January, February... December. In MDX, valid member descriptions are generally in the following format:
[Time]. [2005]. [Q1]. [1]
Members of the [month] level are displayed in the order of January, February, and so on.
In the parent-child hierarchy, members are often sorted in a hierarchical order. The ordinalcolumn attribute controls the order in which siblings appear in their parents. The sorting column can be any data type used in the order by statement. The sorting range is for each parent, so in the preceding example, the day_in_month column loops every month. The instances of Java. Lang. Comparable objects whose values returned by the JDBC driver are not empty are compared using the comparable. compareto method.
The level that contains the type attribute. The value can be set to string, integer, numeric, Boolean, date, time, and timestamp. The default value is numeric, because the primary key column is generally of the numerical type. If the types are different, Mondrian needs to know the correct type to generate an SQL statement. For example, a string value will generate a statement that contains single quotes:
Where productsku = '2017-456-aaa'
2. Multi-level structure
A dimension can contain more than one level:
<Dimension name = "time" foreignkey = "time_id"> <Hierarchy hasall = "false" primarykey = "time_id"> <Table name = "time_by_day"/> <Level name = "year" column = "the_year" type = "numeric" uniquembers = "true"/> <Level name = "quarter" column = "quarter" uniquemembers = "false"/> <Level name = "month" column = "month_of_year" type = "numeric" uniquemembers "=" false "/> </Hierarchy> <Hierarchy name = "Time Weekly" hasall = "false" primarykey = "time_id"> <Table name = "time_by_week"/> <Level name = "year" column = "the_year" type = "numeric" uniquembers = "true"/> <Level name = "Week" column = "Week" uniquemember = "false"/> <Level name = "day" column = "day_of_week" type = "string" uniquemembers = "false"/> </Hierarchy> </Dimension> |
Note that the first level has no name. By default, a level has the same name as its dimension. Therefore, the first level is called time.
Do these hierarchies have nothing in common? They even use the same table! The main reason for putting two layers into the same dimension is that this is more meaningful to end users, the end user knows whether the time level placed on one axis or the time weekly Level on the other axis makes sense. If the two layers are in the same dimension, the MDX statement cannot use both layers in the same query.