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.

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.

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.

<Schema> <Cube name="Sales"> <Table name="sales_fact_1997"/> <Dimension name="Gender" foreignKey="customer_id"> <Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="customer_id"> <Table name="customer"/> <Level name="Gender" column="gender" uniqueMembers="true"/> </Hierarchy> </Dimension> <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" uniqueMembers="true"/> <Level name="Quarter" column="quarter" uniqueMembers="false"/> <Level name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/> </Hierarchy> </Dimension> <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]"> <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/> </CalculatedMember> </Cube> </Schema>

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.

<Cube name="Sales"> <Table name="sales_fact_1997"/> ... </Cube>

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"> <MeasureExpression> <SQL dialect="generic"> (case when sales_fact_1997.promotion_id = 0 then 0 else sales_fact_1997.store_sales end) </SQL> </MeasureExpression> </Measure>

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"> <Table name="customer"/> <Level name="Gender" column="gender" uniqueMembers="true"/> </Hierarchy> </Dimension>

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 fom 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 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.

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
TimeDays	Level represents days

Here is an example of a time dimension:

<Dimension name="Time" type="TimeDimension"> <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"/> </Hierarchy> </Dimension>

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.

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:

<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" uniqueMembers="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" uniqueMembers="true"/> <Level name="Week" column="week" uniqueMembers="false"/> <Level name="Day" column="day_of_week" type="String" uniqueMembers="false"/> </Hierarchy> </Dimension>

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.

<Cube name="Checkout">  <Table name="checkout"> <Dimension name="Payment method"> <Hierarchy hasAll="true">  <Level name="Payment method" column="payment_method" uniqueMembers="true" /> </Hierarchy> </Dimension>  </Cube>

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:

<Dimension name="Severity"> <Hierarchy hasAll="true" primaryKey="severity_id"> <InlineTable alias="severity"> <ColumnDefs> <ColumnDef name="id" type="Numeric"/> <ColumnDef name="desc" type="String"/> </ColumnDefs> <Rows> <Row> <Value column="id">1</Value> <Value column="desc">High</Value> </Row> <Row> <Value column="id">2</Value> <Value column="desc">Medium</Value> </Row> <Row> <Value column="id">3</Value> <Value column="desc">Low</Value> </Row> </Rows> </InlineTable> <Level name="Severity" column="id" nameColumn="desc" uniqueMembers="true"/> </Hierarchy> </Dimension>

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

<Dimension name="Severity"> <Hierarchy hasAll="true" primaryKey="severity_id"> <Table name="severity"/> <Level name="Severity" column="id" nameColumn="desc" uniqueMembers="true"/> </Hierarchy> </Dimension>

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"> ... <CalculatedMember name="Profit" dimension="Measures"> <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula> ... </CalculatedMember> </Cube> <VirtualCube name="Warehouse and Sales" defaultMeasure="Profit" > ... <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit]"/> </VirtualCube>

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:

<Cube name="Sales"> ... <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"> <Table name="product"/> <Join leftKey="product_type_id" rightKey="product_type_id"> <Table name="product_class"/> <Table name="product_type"/> </Join> </Join> <!-- Level declarations ... -> </Hierarchy> </Dimension> </Cube>

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:

<Dimension name="Store Type"> <Hierarchy hasAll="true" primaryKey="store_id"> <Table name="store"/> <Level name="Store Type" column="store_type" uniqueMembers="true"/> </Hierarchy> </Dimension> <Cube name="Sales"> <Table name="sales_fact_1997"/> ... <DimensionUsage name="Store Type" source="Store Type" foreignKey="store_id"/> </Cube> <Cube name="Warehouse"> <Table name="warehouse"/> ... <DimensionUsage name="Store Type" source="Store Type" foreignKey="warehouse_store_id"/> </Cube>

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:

If a dimension has a small number of members, Mondrian reads it into a cache on first use. See the mondrian.rolap.LargeDimensionThreshold property.
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:

<VirtualCube name="Warehouse and Sales"> <CubeUsages> <CubeUsage cubeName="Sales" ignoreUnrelatedDimensions="true"/> <CubeUsage cubeName="Warehouse"/> <CubeUsages/> <VirtualCubeDimension cubeName="Sales" name="Customers"/> <VirtualCubeDimension cubeName="Sales" name="Education Level"/> <VirtualCubeDimension cubeName="Sales" name="Gender"/> <VirtualCubeDimension cubeName="Sales" name="Marital Status"/> <VirtualCubeDimension name="Product"/> <VirtualCubeDimension cubeName="Sales" name="Promotion Media"/> <VirtualCubeDimension cubeName="Sales" name="Promotions"/> <VirtualCubeDimension name="Store"/> <VirtualCubeDimension name="Time"/> <VirtualCubeDimension cubeName="Sales" name="Yearly Income"/> <VirtualCubeDimension cubeName="Warehouse" name="Warehouse"/> <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Sales Count]"/> <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Cost]"/> <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Sales]"/> <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Unit Sales]"/> <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit Growth]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Store Invoice]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Supply Time]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Units Ordered]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Units Shipped]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse Cost]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse Profit]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse Sales]"/> <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Average Warehouse Sale]"/> <CalculatedMember name="Profit Per Unit Shipped" dimension="Measures"> <Formula>[Measures].[Profit] / [Measures].[Units Shipped]</Formula> </CalculatedMember> </VirtualCube>

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"> <Table name="employee"/> <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="p