By using two-part names in the Dimension Wizard, you can create a single dimension with multiple hierarchies. The OLAP Manager treats these hierarchies as if they were multiple dimensions, but in the MDX Sample program, the structure is reflected as two hierarchies within a single dimension. By running two queries, you can see that the same months are organized into two different sets of quarters and years, but the aggregated values for the months remain the same. The way these multiple hierarchies are organized is exactly what you want.
Now, let's dig a little deeper into how multiple hierarchies affect the way you write MDX queries. As in Screen 4, you use the hierarchy name to further qualify the dimension name (e.g., [NewTime].[Calendar]). You might logically conclude that if you want to reference the current member, the hierarchy name is not required. According to the OLAP Services documentation, CurrentMember "returns the current member along a dimension during an iteration, rather than along a hierarchy." Why is it important to know that CurrentMember works on a hierarchy and not a dimension? This situation is just one aspect of a larger issue. What really happens when OLAP Services executes an MDX query is that it treats multiple hierarchies as if they were different dimensions. The following code demonstrates this point:
SELECT [NewTime].[Calendar].[Year].Members ON columns,
[NewTime].[Fiscal].[Year].Members ON rows
FROM [Time Piece]
The MDX query in Listing 1 demonstrates how you can put two hierarchies of the same dimension on different axes (rows and columns). If you think about listing different time hierarchies on rows and columns, the result doesn't make sense. The only combination of members that returns values in this query is one in which the months are the same. For example, the cell value defined by [Calendar] .[1998] and [Fiscal] .[1997] returns a value that represents the intersection of the two years (January through June).
Now here's the gotcha: Typically, Time dimensions don't have an All Member because summing multiple years isn't meaningful in business analysis. Usually, the root level of a Time hierarchy is Years.
Look at the FoodMart Sales cube as an example. In this case, multiple members are at the root level of the hierarchy and one of the members is the default member. Remember that OLAP Services selects the default member when you don't explicitly call out a member from that dimension in an MDX query. This situation can be a problem if you create multiple hierarchies in the time dimension and one or both of the multiple hierarchies doesn't have an All Member.
Then, imagine you want to list all the fiscal years on the rows and some other dimension on the columns. Because OLAP Services treats the Calendar hierarchy as a dimension, you need to explicitly select some member from the Calendar hierarchy or OLAP Services selects the default member for you. If the fiscal years don't overlap with the Calendar member you selected, OLAP Services returns a null (empty) value.
If you want to explore this idea further, go back to the Dimension Editor for the two hierarchies we created in this column and use the properties panel to turn off the All Member. Then reprocess the Time Piece cube. You might discover that it's difficult to create MDX queries that list time periods on the rows or columns, because OLAP Services always uses the default member from the other Time hierarchy to filter your result.
We came up with two solutions to this problem. Solution one is to use an All Member in the time dimension when you have multiple hierarchies. Solution two is to create a Calculated Member on the Time hierarchy that aggregates the top-level members, and select this member in the WHERE clause. This method requires an expensive performance hit, but it is a viable alternative.
With the help of this article, you can begin experimenting with multiple hierarchies to build flexible OLAP models.
End of Article
Prev. page
1
[2]
next page -->
