|
How to Design a Mondrian Schema
- What is a schema?
- Schema files
- Logical model
- Cube
- Measures
- Dimensions, Hierarchies, Levels
- Mapping dimensions and hierarchies onto tables
- The 'All' member
- Time dimensions
- Order and Display of Levels
- Multiple hierarchies
- Degenerate dimensions
- Inline tables
- Member properties and formatters
- Approximate level cardinality
- Default Measure Attribute
- Star and snowflake schemas
- Shared dimensions
- Join optimization
- Advanced logical constructs
- Virtual cubes
- Parent-child hierarchies
- Tuning parent-child
hierarchies
- Closure tables
- Populating closure tables
- Member properties
- Calculated members
- Named sets
- Plug-ins
- User-defined function
- Member reader
- Cell reader
- Cell formatter
- Member formatter
- Property formatter
- Schema processor
- Data source change listener
- Dynamic datasource xmla servlet
- Internationalization
- Aggregate tables
- Access-control
- Defining a role
- Rollup policy
- Union roles
- Setting a connection's role
- Appendix A: XML elements
1. What is a schema?
A schema defines a multi-dimensional database. It contains a logical model, consisting of cubes, hierarchies, and members, and a mapping of this model onto a physical model.
The logical model consists of the constructs used to write queries in MDX language: cubes, dimensions, hierarchies, levels, and members.
The physical model is the source of the data which is presented through the logical model. It is typically a star schema, which is a set of tables in a relational database; later, we shall see examples of other kinds of mappings.
2. Schema files
Mondrian schemas are represented in an XML file. An example schema, containing almost all of the
constructs we discuss here, is supplied as demo/FoodMart.xml in the mondrian distribution.
The dataset to populate this schema is also in the distribution.
Currently, the only way to create a schema is to edit a schema XML file in a text editor. The XML
syntax is not too complicated, so this is not as difficult as it sounds, particularly if you use the FoodMart
schema as a guiding example.
The structure of the XML document is as follows:
aggElements
aggElements
relation
relation ::=
relation
aggElement ::=
NOTE: The order of XML elements is important. For example,
<UserDefinedFunction>
element has to occur inside the <Schema>
element after all collections of <Cube>,
<VirtualCube>,
<NamedSet>
and <Role> elements. If you include it before the first
<Cube> element,
the rest of the schema will be ignored.
The content of each XML element is described in
Appendix A and in the XML schema.
3. Logical model
The most important components of a schema are cubes, measures, and dimensions:
- A cube is a collection of dimensions and measures in a particular subject area.
- A measure is a quantity that you are interested in measuring, for example, unit sales of a
product, or cost price of inventory items.
- A dimension is an attribute, or set of attributes, by which you can divide measures into
sub-categories. For example, you might wish to break down product sales by their color, the gender of the
customer, and the store in which the product was sold; color, gender, and store are all dimensions.
Let's look at the XML definition of a simple schema.
< Table name="sales_fact_1997"/> < Dimension name="Gender" foreignKey="customer_id"> < Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="customer_id"> < Level name="Gender" column="gender" uniqueMembers="true"/> < Hierarchy hasAll="false" primaryKey="time_id"> < Table name="time_by_day"/> < Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/> < Level name="Quarter" column="quarter" uniqueMembers="false"/> < Level name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/> < Measure name="Unit Sales" column="unit_sales" aggregator="sum" formatString="#,###"/> < Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.##"/> < Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00"/> < CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]">
This schema contains a single cube, called "Sales". The Sales cube has two dimensions,
"Time", and "Gender", and two measures, "Unit Sales" and "Store Sales".
We can write an MDX query on this schema:
SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
{descendants([Time].[1997].[Q1])} ON ROWS
FROM [Sales]
WHERE [Gender].[F]
This query refers to the Sales cube ([Sales]), each of the dimensions
[Measures], [Time], [Gender], and various members
of those dimensions. The results are as follows:
[Time] |
[Measures].[Unit Sales] |
[Measures].[Store Sales] |
[1997].[Q1] |
0 |
0 |
[1997].[Q1].[Jan] |
0 |
0 |
[1997].[Q1].[Feb] |
0 |
0 |
[1997].[Q1].[Mar] |
0 |
0 |
Now let's look at the schema definition in more detail.
3.1 Cube
A cube (see <Cube>) is a named collection of measures
and dimensions. The one thing the measures and dimensions have in common is the fact table, here
"sales_fact_1997". As we shall see, the fact table holds the columns
from which measures are calculated, and contains references to the tables which hold the dimensions.
< Table name="sales_fact_1997"/> ...
The fact table is defined using the <Table> element. If the fact table is not in
the default schema, you can provide an explicit schema using the "schema" attribute, for example
< Table schema=" dmart" name="sales_fact_1997"/>
You can also use the <View> and
<Join> constructs to build more complicated SQL statements.
3.2 Measures
The Sales cube defines several measures, including "Unit Sales" and "Store Sales".
< Measure name="Unit Sales" column="unit_sales" aggregator="sum" datatype="Integer" formatString="#,###"/> < Measure name="Store Sales" column="store_sales" aggregator="sum" datatype="Numeric" formatString="#,###.00"/>
Each measure (see <Measure>) has a name, a column in the fact table, and an
aggregator. The aggregator is usually "sum", but "count", "min", "max", "avg", and
"distinct-count" are also allowed; "distinct-count" has some limitations if your cube contains a
parent-child hierarchy.
The optional datatype attribute specifies how cell values are represented in Mondrian's
cache, and how they are returned via XML for Analysis. The datatype attribute can have
values "String", "Integer", "Numeric", "Boolean",
"Date", "Time", and "Timestamp".
The default is "Numeric", except for "count" and
"distinct-count" measures, which are "Integer".
An optional formatString attribute specifies how the value is to be printed.
Here, we have chosen to output unit sales with no decimal places (since it is an integer), and store sales
with two decimal places (since it is a currency value). The ',' and '.' symbols are locale-sensitive, so if
you were running in Italian, store sales might appear as "48.123,45". You can achieve even more wild effects
using advanced format strings.
A measure can have a caption attribute to be returned by the
Member.getCaption()
method instead of the name. Defining a specific caption does make sense if special letters (e.g. Σ or Π)
are to be displayed:
< Measure name="Sum X" column="sum_x" aggregator="sum" caption="Σ X"/>
Rather than coming from a column, a measure can use a cell reader,
or a measure can use a SQL expression to calculate its value. The measure "Promotion Sales" is
an example of this.
< Measure name="Promotion Sales" aggregator="sum" formatString="#,###.00"> (case when sales_fact_1997.promotion_id =
0 then 0 else sales_fact_1997.store_sales end)
In this case, sales are only included in the summation if they correspond to a promotion sales.
Arbitrary SQL expressions can be used, including subqueries. However, the underlying database
must be able to support that SQL expression in the context of an aggregate. Variations in syntax
between different databases is handled by specifying the dialect in the SQL tag.
In order to provide a specific formatting of the cell values, a measure can use a
cell formatter.
3.3 Dimensions, Hierarchies, Levels
Some more definitions:
- A member is a point within a dimension determined by a particular set of attribute
values. The gender hierarchy has the two members 'M' and 'F'. 'San Francisco', 'California' and
'USA' are all members of the store hierarchy.
- A hierarchy is a set of members organized into a structure for convenient analysis.
For example, the store hierarchy consists of the store name, city, state, and nation. The
hierarchy allows you form intermediate sub-totals: the sub-total for a state is the sum of the
sub-totals of all of the cities in that state, each of which is the sum of the sub-totals of
the stores in that city.
- A level is a collection of members which have the
same distance from the root of the hierarchy.
- A dimension is a collection of hierarchies which discriminate on the same fact table attribute (say, the day that a sale occurred).
For reasons of uniformity, measures are treated as members of a special dimension, called 'Measures'.
An example
Let's look at a simple dimension.
< Dimension name="Gender" foreignKey="customer_id"> < Hierarchy hasAll="true" primaryKey="customer_id"> < Level name="Gender" column="gender" uniqueMembers="true"/>
This dimension consists of a single hierarchy, which consists of a single level called Gender.
(As we shall see later, there is also a special level called
[(All)] containing a grand total.)
The values for the dimension come from the gender column in the customer
table. The "gender" column contains two values, 'F' and 'M', so the Gender dimension contains the members
[Gender].[F] and [Gender].[M].
For any given sale, the gender dimension is the gender of the customer who made that purchase. This is expressed by
joining from the fact table "sales_fact_1997.customer_id" to the dimension table "customer.customer_id".
3.3.1 Mapping dimensions and hierarchies onto tables
A dimension is joined to a cube by means of a pair of columns, one in the fact table, the other in the dimension table.
The <Dimension> element has a foreignKey attribute,
which is the name of a column in the fact table; the <Hierarchy> element has
primaryKey attribute.
If the hierarchy has more than one table, you can disambiguate using the primaryKeyTable
attribute.
The column attribute defines the key of the level. It must be
the name of a column in the level's table. If the key is an expression, you can
instead use the <KeyExpression> element inside the Level. The following is
equivalent to the above example:
< Dimension name="Gender" foreignKey="customer_id"> < Hierarchy hasAll="true" primaryKey="customer_id"> < Table name="customer" /> < Level name="Gender" column="gender" uniqueMembers="true"> < SQL dialect="generic">customer.gender</ SQL>
Other attributes of <Level>, <Measure>
and <Property> have corresponding nested elements:
| Parent element |
Attribute |
Equivalent nested element |
Description |
<Level> |
column |
<KeyExpression> |
Key of level. |
<Level> |
nameColumn |
<NameExpression> |
Expression which defines the name of members of this level. If
not specified, the level key is used. |
<Level> |
ordinalColumn |
<OrdinalExpression> |
Expression which defines the order of members. If not specified,
the level key is used. |
<Level> |
captionColumn |
<CaptionExpression> |
Expression which forms the caption of members. If not specified,
the level name is used. |
<Level> |
parentColumn |
<ParentExpression> |
Expression by which child members reference their parent member
in a parent-child hierarchy. Not specified in a regular hierarchy. |
<Measure> |
column |
<MeasureExpression> |
SQL expression to calculate the value of the measure (the
argument to the SQL aggregate function). |
<Property> |
column |
<PropertyExpression> |
SQL expression to calculate the value of the property. |
The uniqueMembers attribute is used to optimize SQL generation. If you know that the
values of a given level column in the dimension table are unique across all the other values in that column across
the parent levels, then set uniqueMembers="true", otherwise, set to
"false". For example, a time dimension like [Year].[Month]
will have uniqueMembers="false" at the Month level, as the same month appears in different
years. On the other hand, if you had a [Product Class].[Product Name] hierarchy, and you
were sure that [Product Name] was unique, then you can set
uniqueMembers="true". If you are not sure, then always set
uniqueMembers="false". At the top level, this will always be
uniqueMembers="true", as there is no parent level.
The highCardinality attribute is used to notify Mondrian
there are undefined and very high number of elements for this dimension.
Acceptable values are true or false (last one
is default value). Actions performed over the whole set of dimension elements
cannot be performed when using highCardinality="true".
3.3.2 The 'all' member
By default, every hierarchy contains a top level called '(All)', which contains a single
member called '(All {hierarchyName})'. This member is parent of all other members
of the hierarchy, and thus represents a grand total. It is also the default member of the hierarchy; that is, the member
which is used for calculating cell values when the hierarchy is not included on an axis or in the slicer. The
allMemberName and allLevelName attributes override the default
names of the all level and all member.
If the <Hierarchy> element has hasAll="false", the 'all'
level is suppressed. The default member of that dimension will now be the first member of the first level; for example,
in a Time hierarchy, it will be the first year in the hierarchy. Changing the default member can be confusing, so you
should generally use hasAll="true".
The <Hierarchy> element also has a defaultMember
attribute, to override the default member of the hierarchy:
< Dimension name="Time" type="TimeDimension" foreignKey="time_id"> < Hierarchy hasAll="false" primaryKey="time_id" defaultMember="[Time].[1997].[Q1].[1]"/> ...
3.3.3 Time dimensions
Time dimensions based on year/month/week/day are coded differently in the Mondrian schema
due to the MDX time related functions such as:
ParallelPeriod([level[, index[, member]]])PeriodsToDate([level[, member]])WTD([member])MTD([member])QTD([member])YTD([member])LastPeriod(index[, member])
Time dimensions have type="TimeDimension". The role of a level in a time dimension is
indicated by the level's levelType attribute, whose allowable values are as follows:
levelType value |
Meaning |
| TimeYears |
Level is a year |
| TimeQuarters |
Level is a quarter |
| TimeMonths |
Level is a month |
| TimeWeeks |
Level is a week |
| TimeDays |
Level represents days |
Here is an example of a time dimension:
< Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="dateid"> < Table name="datehierarchy"/> < Level name="Year" column="year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/> < Level name="Quarter" column="quarter" uniqueMembers="false" levelType="TimeQuarters" /> < Level name="Month" column="month" uniqueMembers="false" ordinalColumn="month" nameColumn="month_name" levelType="TimeMonths" type="Numeric"/> < Level name="Week" column="week_in_month" uniqueMembers="false" levelType="TimeWeeks" /> < Level name="Day" column="day_in_month" uniqueMembers="false" ordinalColumn="day_in_month" nameColumn="day_name" levelType="TimeDays" type="Numeric"/>
3.3.4 Order and display of levels
Notice that in the time hierarchy example above the ordinalColumn and
nameColumn attributes on the <Level> element. These
effect how levels are displayed in a result. The ordinalColumn attribute specifies a
column in the Hierarchy table that provides the order of the members in a given Level, while the
nameColumn specifies a column that will be displayed.
For example, in the Month Level above, the datehierarchy table has month (1 .. 12)
and month_name (January, February, ...) columns. The column value that will be used internally within MDX is the
month column, so valid member specifications will be of the form:
[Time].[2005].[Q1].[1]. Members of the [Month]
level will displayed in the order January, February, etc.
In a parent-child hierarchy, members are always sorted in hierarchical
order. The ordinalColumn attribute controls the order that
siblings appear within their parent.
Ordinal columns may be of any datatype which can legally be used in
an ORDER BY clause. Scope of ordering is per-parent, so in the
example above, the day_in_month column should cycle for each month.
Values returned by the JDBC driver should be non-null instances of
java.lang.Comparable
which yield the desired ordering when their
Comparable.compareTo method is called.
Levels contain a type attribute, which can have values "String", "Integer", "Numeric", "Boolean",
"Date", "Time", and "Timestamp".
The default value is "Numeric" because key columns generally have a numeric type. If it is a
different type, Mondrian needs to know this so it can generate SQL statements
correctly; for example, string values will be generated enclosed in single
quotes:
WHERE productSku = '123-455-AA'
3.3.5 Multiple hierarchies
A dimension can contain more than one hierarchy:
< Hierarchy hasAll="false" primaryKey="time_id"> < Table name="time_by_day"/> < Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/> < Level name="Quarter" column="quarter" uniqueMembers="false"/> < Level name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/> < Hierarchy name="Time Weekly" hasAll="false" primaryKey="time_id"> < Table name="time_by_week"/> < Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/> < Level name="Week" column="week" uniqueMembers="false"/> < Level name="Day" column="day_of_week" type="String" uniqueMembers="false"/>
Notice that the first hierarchy doesn't have a name. By default, a hierarchy has the
same name as its dimension, so the first hierarchy is called "Time".
These hierarchies don't have much in common ? they don't even have the same table! ? except
that they are joined from the same column in the fact table, "time_id".
The main reason to put two hierarchies in the same dimension is because it makes more sense to
the end-user: end-users know that it makes no sense to have the "Time" hierarchy on one axis
and the "Time Weekly" hierarchy on another axis. If two hierarchies are the same dimension, the
MDX language enforces common sense, and does not allow you to use them both in the same query.
3.3.6 Degenerate dimensions
A degenerate dimension is a dimension which is so simple that it isn't worth
creating its own dimension table. For example, consider following the fact table:
| product_id |
time_id |
payment_method |
customer_id |
store_id |
item_count |
dollars |
| 55 |
20040106 |
Credit |
123 |
22 |
3 |
$3.54 |
| 78 |
20040106 |
Cash |
89 |
22 |
1 |
$20.00 |
| 199 |
20040107 |
ATM |
3 |
22 |
2 |
$2.99 |
| 55 |
20040106 |
Cash |
122 |
22 |
1 |
$1.18 |
and suppose we created a dimension table for the values in the payment_method column:
| payment_method |
| Credit |
| Cash |
| ATM |
This dimension table is fairly pointless. It only has 3 values, adds no additional information,
and incurs the cost of an extra join.
Instead, you can create a degenerate dimension. To do this, declare a dimension without a
table, and Mondrian will assume that the columns come from the fact table.
<!-- The fact table is always necessary. -->
<!-- No table element here. Fact table is assumed. -->
< Level name="Payment method" column="payment_method" uniqueMembers="true" /> <!-- other dimensions and measures -->
Note that because there is no join, the foreignKey attribute of
Dimension is not necessary, and the Hierarchy
element has no <Table> child element or
primaryKey attribute.
3.3.7 Inline tables
The <InlineTable> construct allows
you to define a dataset in the schema file. You must declare the names of the columns, the column types
("String" or "Numeric"), and a set of rows. As for
<Table> and
<View>, you must provide a unique alias with which
to refer to the dataset.
Here is an example:
< Hierarchy hasAll="true" primaryKey="severity_id"> < Level name="Severity" column="id" nameColumn="desc" uniqueMembers="true"/>
This has the same effect as if you had a table called 'severity' in your database:
| id |
desc |
| 1 |
High |
| 2 |
Medium |
| 3 |
Low |
and the declaration
< Hierarchy hasAll="true" primaryKey="severity_id"> < Level name="Severity" column="id" nameColumn="desc" uniqueMembers="true"/>
To specify a NULL value for a column, omit the <Value>
for that column, and the column's value will default to NULL.
3.3.8 Member properties and formatters
As we shall see later, a level definition can also define member properties
and a member formatter.
3.3.9 Approximate level cardinality
The <Level> element allows specifying the optional attribute "approxRowCount". Specifying
approxRowCount can improve performance by reducing the need to determine level, hierarchy, and dimension cardinality.
This can have a significant impact when connecting to Mondrian via XMLA.
3.3.10 Default Measure Attribute
The <Cube> and <VirtualCube>
elements allows specifying the optional attribute "defaultMeasure".
Specifying defaultMeasure in <Cube> element allows users
to explicitly specify any base measure as default Measure.
Specifying defaultMeasure in <VirtualCube>
element allows users to explicitly specify any VirtualCube Measure as a default Measure.
Note that if default measure is not specified it takes the first measure defined in the cube as the default measure. In the case of virtual cube,
it would pick up the first base measure of the first cube defined within it as the default.
Specifying the defaultMeasure explicitly would be useful in cases where you would want a calculated member to be picked up as the default measure.
To facilitate this, the calculated member could be defined in one of the base cubes and specified as the defaultMeasure in the virtual cube.
< Cube name="Sales" defaultMeasure="Unit Sales"> ...
...
< VirtualCube name="Warehouse and Sales" defaultMeasure="Profit" > ...
4. Star and snowflake schemas
We saw earlier how to build a cube based upon a fact table, and dimensions in the fact
table ("Payment method") and in a table joined to the fact table ("Gender"). This is the
most common kind of mapping, and is known as a star schema.
But a dimension can be based upon more than one table, provided that there is a well-defined
path to join these tables to the fact table. This kind of dimension is known as a snowflake,
and is defined using the <Join> operator. For example:
...
Dimension name="Product" foreignKey="product_id">
< Hierarchy hasAll="true" primaryKey="product_id" primaryKeyTable="product"> < Join leftKey="product_class_key" rightAlias="product_class" rightKey="product_class_id"> < Join leftKey="product_type_id" rightKey="product_type_id"> < Table name="product_class"/> < Table name="product_type"/> <!-- Level declarations ... -->
This defines a "Product" dimension consisting of three tables. The
fact table joins to "product" (via the foreign key
"product_id"), which joins to "product_class" (via the foreign
key "product_class_id"), which joins to "
product_type" (via the foreign key "product_type_id"). We require
a <Join> element nested within a <Join>
element because <Join> takes two operands; the operands
can be tables, joins, or even queries.
The arrangement of the tables seems complex, the simple rule of thumb is to order the tables
by the number of rows they contain. The "product" table has the most
rows, so it joins to the fact table and appears first; "product_class"
has fewer rows, and "product_type", at the tip of the snowflake, has
least of all.
Note that the outer <Join> element has a
rightAlias attribute. This is necessary because the right component of the join (the inner
<Join> element) consists of more than one table. No
leftAlias attribute is necessary in this case, because the leftKey
column unambiguously comes from the "product" table.
4.1 Shared dimensions
When generating the SQL for a join, mondrian needs to know which column to join to. If you are
joining to a join, then you need to tell it which of the tables in the join that column belongs
to (usually it will be the first table in the join).
Because shared dimensions don't belong to a cube, you have to give them an explicit table
(or other data source). When you use them in a particular cube, you specify the foreign key. This
example shows the Store Type dimension being joined to the
Sales cube using the sales_fact_1997.store_id
foreign key, and to the Warehouse cube using the
warehouse.warehouse_store_id foreign key:
< Hierarchy hasAll="true" primaryKey="store_id"> < Level name="Store Type" column="store_type" uniqueMembers="true"/>
< Table name="sales_fact_1997"/> ...
< DimensionUsage name="Store Type" source="Store Type" foreignKey="store_id"/>
< Table name="warehouse"/> ...
< DimensionUsage name="Store Type" source="Store Type" foreignKey="warehouse_store_id"/>
4.2 Join optimization
The table mapping in the schema tells Mondrian how to get the data, but Mondrian is smart
enough not to read the schema literally. It applies a number of optimizations when generating
queries:
- TODO: describe large dimension support
- If a dimension (or, more precisely, the level of the dimension being accessed) is in the fact table, Mondrian does
not perform a join.
- If two dimensions access the same table via the same join path, Mondrian only joins them once. For example,
[Gender] and [Age] might both be columns in the
customers table, joined via sales_1997.cust_id = customers.cust_id.
5. Advanced logical constructs
5.1 Virtual cubes
A virtual cube combines two or more regular cubes. It is defined by the <VirtualCube>
element:
< CubeUsage cubeName="Sales" ignoreUnrelatedDimensions="true"/>
The <CubeUsages>
element is optional. It specifies the cubes that are imported into the virtual cube.
Holds CubeUsage elements.
The <CubeUsage>
element is optional. It specifies the base cube that is imported into the
virtual cube. Currently it is possible to define a VirtualCubeMeasure and
similar imports from base cube without defining CubeUsage for the cube.
The cubeName attribute specifies the base cube being imported.
The ignoreUnrelatedDimensions attribute specifies that the measures
from this base cube will have non joining dimension members pushed to the
top level member. This behaviour is currently supported for aggregation.
This attribute is by default false.
ignoreUnrelatedDimensions is an experimental feature similar to
the similarly named feature in SSAS 2005.
MSDN documentation
mentions "When IgnoreUnrelatedDimensions is true, unrelated dimensions are forced
to their top level; when the value is false, dimensions are not forced to their
top level. This property is similar to the Multidimensional Expressions
(MDX) ValidMeasure function". Current mondrian implementation of
ignoreUnrelatedDimensions depends on use of ValidMeasure. E.g. If we
want to apply this behaviour to "Unit Sales" measure in the "Warehouse and Sales"
virtual cube then we need to define a CubeUsage entry for "Sales" cube as shown
in the example above and also wrap this measure with ValidMeasure.
The <VirtualCubeDimension>
element imports a dimension from one of the constituent cubes. If you do not
specify the cubeName attribute, this means you
are importing a shared dimension. (If a shared dimension is used more than once
in a cube, there is no way, at present, to disambiguate which usage of the
shared dimension you intend to import.)
The <VirtualCubeMeasure>
element imports a measure from one of the constituent cubes. It is imported with
the same name. If you want to create a formula, or just to rename a measure as
you import it, use the <CalculatedMember>
element.
Virtual cubes occur surprisingly frequently in real-world applications. They
occur when you have fact tables of different granularities (say one measured at
the day level, another at the month level), or fact tables of different
dimensionalities (say one on Product, Time and Customer, another on Product,
Time and Warehouse), and want to present the results to an end-user who doesn't
know or care how the data is structured.
Any common dimensions -- shared dimensions which are used by both constituent
cubes -- are automatically synchronized. In this example, [Time]
and [Product] are common dimensions. So if the context is ([Time].[1997].[Q2],
[Product].[Beer].[Miller Lite]), measures from either cube will
relate to this context.
Dimensions which only belong to one cube are called non-conforming
dimensions. The [Gender] dimension is an example of this: it exists
in the Sales cube but not Warehouse. If the context is
([Gender].[F], [Time].[1997].[Q1]), it makes sense to
ask the value of the [Unit Sales] measure (which comes from the
[Sales] cube) but not the [Units Ordered] measure (from
[Warehouse]). In the context of [Gender].[F], [Units
Ordered] has value NULL.
5.2 Parent-child hierarchies
A conventional hierarchy has a rigid set of levels, and members which adhere to those
levels. For example, in the Product hierarchy, any member of the Product Name
level has a parent in the Brand Name level, which has a parent in the
Product Subcategory level, and so forth. This structure is sometimes too rigid
to model real-world data.
A parent-child hierarchy has only one level (not counting the special 'all' level),
but any member can have parents in the same level. A classic example is the reporting structure
in the Employees hierarchy:
< Dimension name="Employees" foreignKey="employee_id"> < Hierarchy hasAll="true" allMemberName="All Employees" primaryKey="employee_id"> < Level name="Employee Id" uniqueMembers="true" type="Numeric" column="employee_id" nameColumn="full_name" parentColumn="supervisor_id" nullParentValue="0"> < Property name="Marital Status" column="marital_status"/> < Property name="Position Title" column="position_title"/> < Property name="Gender" column="gender"/> < Property name="Salary" column="salary"/> < Property name="Education Level" column="education_level"/> < Property name="Management Role" column="management_role"/>
The important attributes here are parentColumn and nullParentValue:
- The
parentColumn attribute is the name of the
column which links a member to its parent member; in this case, it is
the foreign key column which points to an employee's supervisor. The <ParentExpression>
child element of <Level> is equivalent to the parentColumn
attribute, but allows you to define an arbitrary SQL expression, just
like the <Expression> element. The parentColumn
attribute (or <ParentExpression> element) is the
only indication to Mondrian that a hierarchy has a parent-child
structure.
- The
nullParentValue attribute is the value which
indicates that a member has no parent. The default is nullParentValue="null",
but since many database don't index null values, schema designers
sometimes use values as the empty string, 0, and -1 instead.
5.2.1 Tuning parent-child hierarchies
There's one serious problem with the parent-child hierarchy defined above, and that is the
amount of work Mondrian has to do in order to compute cell-totals. Let's suppose that the
employee table contains the following data:
| employee |
| supervisor_id |
employee_id |
full_name |
| null |
1 |
Frank |
| 1 |
2 |
Bill |
| 2 |
3 |
Eric |
| 1 |
4 |
Jane |
| 3 |
5 |
Mark |
| 2 |
6 |
Carla |
If we want to compute the total salary budget for Bill, we need to add in the salaries of Eric
and Carla (who report to Bill) and Mark (who reports to Eric). Usually Mondrian generates a
SQL GROUP BY statement to compute these totals, but there is no
(generally available) SQL construct which can traverse hierarchies. So by default,
Mondrian generates one SQL statement per supervisor, to retrieve and total all of that
supervisor's direct reports.
This approach has a couple of drawbacks. First, the performance is not very good if a
hierarchy contains more than a hundred members. Second, because Mondrian implements the
distinct-count aggregator by generating SQL, you cannot define a distinct-count
measure in
any cube which contains a parent-child hierarchy.
How can we solve these problems? The answer is to enhance the data so that Mondrian is
able to retrieve the information it needs using standard SQL. Mondrian supports a mechanism
called a closure table for this purpose.
5.2.2 Closure tables
A closure table is a SQL table which contains a record for every employee/supervisor
relationship, regardless of depth. (In mathematical terms, this is called the 'reflexive
transitive closure' of the employee/supervisor relationship. The distance
column is not strictly required, but it makes it easier to populate the table.)
| employee_closure |
| supervisor_id |
employee_id |
distance |
| 1 |
1 |
0 |
| 1 |
2 |
1 |
| 1 |
3 |
2 |
| 1 |
4 |
1 |
| 1 |
5 |
3 |
| 1 |
6 |
2 |
| 2 |
2 |
0 |
| 2 |
3 |
1 |
| 2 |
5 |
2 |
| 2 |
6 |
1 |
| 3 |
3 |
0 |
| 3 |
5 |
1 |
| 4 |
4 |
0 |
| 5 |
5 |
0 |
| 6 |
6 |
0 |
In the catalog XML, the <Closure> element maps
the level onto a <Table>:
< Dimension name="Employees" foreignKey="employee_id"> < Hierarchy hasAll="true" allMemberName="All Employees" primaryKey="employee_id"> < Level name="Employee Id" uniqueMembers="true" type="Numeric" column="employee_id" nameColumn="full_name" parentColumn="supervisor_id" nullParentValue="0"> < Closure parentColumn="supervisor_id" childColumn="employee_id"> < Table name="employee_closure"/> < Property name="Marital Status" column="marital_status"/> < Property name="Position Title" column="position_title"/> < Property name="Gender" column="gender"/> < Property name="Salary" column="salary"/> < Property name="Education Level" column="education_level"/> < Property name="Management Role" column="management_role"/>
This table allows totals to be evaluated in pure SQL. Even though this introduces an extra
table into the query, database optimizers are very good at handling joins. I recommend that
you declare both supervisor_id and employee_id NOT NULL, and index
them as follows:
CREATE UNIQUE INDEX employee_closure_pk ON employee_closure (
supervisor_id,
employee_id);
CREATE INDEX employee_closure_emp ON employee_closure (
employee_id);
5.2.3 Populating closure tables
The table needs to be re-populated whenever the hierarchy changes, and it is
the application's responsibility to do so — Mondrian does not do this!
If you are using Pentaho Data Integration (Kettle), there is a special step
to populate closure tables as part of the ETL process. Further details in the
Pentaho Data
Integration wiki.
Closure Generator step in Pentaho Data Integration
|
If you are not using Pentaho Data Integration, you can populate the table
yourself using SQL. Here is an example of a stored procedure that populates a closure table.
CREATE PROCEDURE close_employee()
BEGIN
DECLARE distance int;
TRUNCATE TABLE employee_closure;
SET distance = 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO employee_closure (supervisor_id, employee_id, distance)
SELECT employee_id, employee_id, distance
FROM employee;
-- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
REPEAT
SET distance = distance + 1;
INSERT INTO employee_closure (supervisor_id, employee_id, distance)
SELECT employee_closure.supervisor_id, employee.employee_id, distance
FROM employee_closure, employee
WHERE employee_closure.employee_id = employee.supervisor_id
AND employee_closure.distance = distance - 1;
UNTIL (ROW_COUNT() == 0))
END REPEAT
END
5.3 Member properties
Member properties are defined by the <Property>
element within a <Level>, like this:
< Level name="MyLevel" column="LevelColumn" uniqueMembers="true"> < Property name="MyProp" column="PropColumn" formatter="com.acme.MyPropertyFormatter"/>
The formatter attribute defines a property formatter
, which is explained later.
Once properties have been defined in the schema, you can use them in MDX statements via the
member.Properties("propertyName") function, for example:
SELECT {[Store Sales]} ON COLUMNS,
TopCount(Filter([Store].[Store Name].Members,
[Store].CurrentMember.Properties("Store Type") = "Supermarket"),
10,
[Store
Sales]) ON ROWS
FROM [Sales]
Mondrian deduces the type of the property expression, if it can. If the property name is a
constant string, the type is based upon the type attribute ("String", "Numeric" or "Boolean")
of the property definition. If the property name is an expression (for example
CurrentMember.Properties("Store " + "Type")), Mondrian will return an untyped
value.
5.4 Calculated members
Suppose you want to create a measure whose value comes not from a column of the fact table,
but from an MDX formula. One way to do this is to use a WITH MEMBER clause, like
this:
WITH MEMBER [Measures].[Profit] AS '[Measures].[Store
Sales]-[Measures].[Store Cost]',
FORMAT_STRING = '$#,###'
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
{[Product].Children} ON ROWS
FROM [Sales]
WHERE [Time].[1997]
But rather than including this clause in every MDX query of your application, you can define
the member in your schema, as part of your cube definition:
You can also declare the formula as an XML attribute, if you prefer. The effect is just the same.
< CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales]-[Measures].[Store Cost]">
Note that the <CalculatedMemberProperty>
(not <Property>) element corresponds
to the FORMAT_STRING = '$#,###' fragment of the MDX statement. You can define
other properties here too, but FORMAT_STRING is by far the most useful in practice.
The FORMAT_STRING property value can also be evaluated using an expression.
When formatting a particular cell, first the expression is evaluated to yield a format string,
then the format string is applied to the cell value. Here is the same property with a conditional
format string:
< CalculatedMemberProperty name="FORMAT_STRING" expression="Iif(Value < 0, '|($#,##0.00)|style=red', '|$#,##0.00|style=green')"/>
For more details about format strings, see the
MDX specification.
One additional calculated member property that is worth mentioning is DATATYPE.
As with measures,
setting datatype specifies how the calculated member is returned via XML for Analysis.
The DATATYPE property of a calculated member can have values "String", "Integer", or
"Numeric":
You can specify SOLVE_ORDER for the calculated member property. Solve order
determines the priority of calculation in the event of competing expressions
You can make a calculated member or a measure invisible. If you specify visible="false"
(the default is "true") in the <Measure> or <
CalculatedMember> element, user-interfaces such as
JPivot will notice this property and hide the member. This is useful if you want to perform
calculations in a number of steps, and hide intermediate steps from end-users. For example,
here only "Margin per Sqft" is visible, and its factors "Store Cost", "Margin" and "Store Sqft"
are hidden:
< Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00" visible="false"/> < Formula>([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]</ Formula>
5.5 Named sets
The WITH SET clause of an MDX statement allows you to declare a set expression
which can be used throughout that query. For example,
WITH SET [Top Sellers] AS
'TopCount([Warehouse].[Warehouse Name].MEMBERS, 5,
[Measures].[Warehouse Sales])'
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{[Top Sellers]} ON ROWS
FROM [Warehouse]
WHERE [Time].[Year].[1997]
The WITH SET clause is very similar to the WITH MEMBER clause,
and as you might expect, it has a construct in schema analogous to <
CalculatedMember>. The
<NamedSet> element allows you to define a
named set in your schema as part of a cube definition. It is implicitly available for
any query against that cube:
...
< Formula>TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])</ Formula>
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{[Top Sellers]} ON ROWS
FROM [Warehouse]
WHERE [Time].[Year].[1997]
|
Warehouse
|
Warehouse Sales |
| Treehouse Distribution |
31,116.37 |
| Jorge Garcia, Inc. |
30,743.77 |
| Artesia Warehousing, Inc. |
29,207.96 |
| Jorgensen Service Storage |
22,869.79 |
| Destination, Inc. |
22,187.42 |
A named set defined against a cube is not inherited by a virtual cubes defined against
that cube. (But you can define a named set against a virtual cube.)
You can also define a named set as global to a schema:
< Cube name="Sales" ... /> < Cube name="Warehouse" ... /> < NamedSet name="CA Cities" formula="{[Store].[USA].[CA].Children}"/>
A named set defined against a schema is available in all cubes and virtual cubes in that
schema. However, it is only valid if the cube contains dimensions with the names required to
make the formula valid. For example, it would be valid to use [CA Cities] in
queries against the [Sales] and [Warehouse and Sales] cubes, but
if you used it in a query against the [Warehouse] cube you would get an error,
because [Warehouse] does not have a [Store] dimension.
6. Plug-ins
Sometimes Mondrian's schema language isn't flexible enough, or the MDX language isn't
powerful enough, to solve the problem at hand. What you want to do is add a little of your
own Java code into the Mondrian application, and a plug-in is a way to do this.
Each of Mondrian's extensions is technically a Service Provider Interface (SPI); in short,
a Java interface which you write code to implement, and which Mondrian will call at runtime.
You also need to register an extension (usually somewhere in your schema.xml file) and to
ensure that it appears on the classpath.
Plug-ins include
user-defined functions;
cell, member and
property formatters;
dynamic schema processors and
data source change listeners.
There is incomplete support for member
readers and cell readers,
and in future we may support pluggable
SQL dialects.
Other extensions include Dynamic datasource xmla servlet
6.1 User-defined function
A user-defined function must have a public constructor and implement the
mondrian.spi.UserDefinedFunction interface. For example,
package com.acme;
import mondrian.olap.*;
import mondrian.olap.type.*;
import mondrian.spi.UserDefinedFunction;
/**
* A simple user-defined function which adds one to its argument.
*/
public class PlusOneUdf implements UserDefinedFunction {
// public constructor
public PlusOneUdf() {
}
public String getName() {
return "PlusOne";
}
public String getDescription() {
return "Returns its argument
plus one";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
return new NumericType();
}
public Type[] getParameterTypes() {
return new Type[] {new
NumericType()};
}
public Object execute(Evaluator evaluator, Exp[]
arguments) {
final Object argValue =
arguments[0].evaluateScalar(evaluator);
if (argValue instanceof
Number) {
return new Double(((Number) argValue).doubleValue() + 1);
} else {
//
Argument might be a RuntimeException indicating that
//
the cache does not yet have the required cell value. The
//
function will be called again when the cache is loaded.
return null;
}
}
public String[] getReservedWords() {
return null;
}
}
Declare it in your schema:
...
And use it in any MDX statement:
WITH MEMBER [Measures].[Unit Sales Plus One]
AS 'PlusOne([Measures].[Unit
Sales])'
SELECT
{[Measures].[Unit Sales]} ON COLUMNS,
{[Gender].MEMBERS} ON ROWS
FROM [Sales]
If a user-defined function has a public constructor with one string argument, Mondrian
will pass in the function's name. Why? This allows you to define two or more user-defined
functions using the same class:
package com.acme;
import mondrian.olap.*;
import mondrian.olap.type.*;
import mondrian.spi.UserDefinedFunction;
/**
* A user-defined function which either adds one to or
* subtracts one from its argument.
*/
public class PlusOrMinusOneUdf implements UserDefinedFunction {
private final name;
private final isPlus;
// public constructor with one argument
public PlusOneUdf(String
name) {
this.name = name;
if (name.equals("PlusOne")) {
isPlus = true;
} else if
(name.equals("MinusOne")) {
isPlus = false;
} else {
throw new IllegalArgumentException("Unexpected name " + name);
}
}
public String getName() {
return name;
}
public String getDescription() {
return "Returns its argument
plus or minus one";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
return new NumericType();
}
public Type[] getParameterTypes() {
return new Type[] {new
NumericType()};
}
public Object execute(Evaluator evaluator, Exp[]
arguments) {
final Object argValue =
arguments[0].evaluateScalar(evaluator);
if (argValue instanceof
Number) {
if (isPlus) {
return new Double(((Number) argValue).doubleValue() + 1);
}
else {
return new Double(((Number) argValue).doubleValue() - 1);
}
} else {
//
Argument might be a RuntimeException indicating that
//
the cache does not yet have the required cell value. The
//
function will be called again when the cache is loaded.
return null;
}
}
public String[] getReservedWords() {
return null;
}
}
and register two the functions in your schema:
...
If you're tired of writing duplicated User-defined Function declarations in schema files,
you can pack your User-defined Function implemention classes into a jar file with a embedded
resource file META-INF/services/mondrian.spi.UserDefinedFunction. This resource file contains
class names of implementations of interface mondrian.spi.UserDefinedFunction, one name per line.
For more details, you may look into src/main/META-INF/services/mondrian.spi.UserDefinedFunction
in source ball and
Service Provider. User-defined Functions declared by this means are available to all
mondrian schema in one JVM.
Caution: you can't define more than one User-defined Function implementations in one class
when you declare User-defined Functions in this way.
6.2 Member reader
A member reader is a means of accessing members. Hierarchies are usually based
upon a dimension table (an 'arm' of a star schema), and are therefore populated using SQL.
But even if your data doesn't reside in an RDBMS, you can make it appear as a hierarchy
by writing a Java class called a custom member reader.
Here are a couple of examples:
DateSource (to be written) generates a time
hierarchy. Conventionally, data warehouse implementors generate a table
containing a row for every date their system is ever likely to deal
with. But the problem is that this table needs to be loaded, and as
time goes by, they will have to remember to add more rows. DateSource
generates date members in memory, and on demand.FileSystemSource (to be written) presents the file
system as a hierarchy of directories and files. Since a directory can
have a parent which is itself a directory, it is a parent-child
hierarchy. Like the time hierarchy created by DateSource, this is a
virtual hierarchy: the member for a particular file is only created
when, and if, that file's parent directory is expanded.ExpressionMemberReader (to be written) creates a
hierarchy based upon an expression.
A custom member reader must implement the
mondrian.rolap.MemberSource interface. If you need to implement a larger set of member
operations for fine-grained control, implement the derived
mondrian.rolap.MemberReader interface; otherwise, Mondrian wrap your reader in a
mondrian.rolap.CacheMemberReader object. Your member reader must have a public constructor
which takes (
RolapHierarchy,
Properties) parameters, and throws no checked exceptions.
Member readers are declared using the <Hierarchy>
element's memberReaderClass attribute; any
<Parameter> child elements are passed via the properties
constructor parameter. Here is an example:
< Hierarchy hasAll="true" memberReaderClass="mondrian.rolap.HasBoughtDairySource"> | 
