If members in an OLAP hierarchy have children, then the Voyager cross tab will display these members with either a plus sign (+) or minus sign (-) in order to indicate whether those children are selected on the cross tab or not. Clicking on the plus sign will ‘drill down’ and display the child members. Clicking on the minus sign will remove any child members. When you see a plus sign this should indicate that the child members are not present and when you see a minus sign, this should indicate that the child members are not present.
There are a few circumstances when using a Microsoft Analysis Services cube that this sign indicating the drill state will be incorrect. Here is an example workflow using the sample Adventure Works cube that Microsoft supplies for Analysis Services.
First choose a measure ‘Internet Sales Amount’ on columns. Then choose two reasonably sized hierarchies to stack on columns. This example uses Customer stacked on the inside and Product on the outside.
Now expand the ‘All Products’ member by clicking on the plus sign to reveal all of its children.
Notice the ‘All Products’ member now displays a minus sign as expected indicating it is drilled down.
Now expand the ‘All Customers’ member to reveal all its children.
You will notice that ‘All Customers’ displays a minus sign correctly indicating its children are selected. However the ‘All Products’ member displays a plus sign despite the fact that it was drilled down in the previous step. Invoking the member selector from the Product hierarchy confirms that the ‘All Products’ member is indeed drilled down.
So why does this happen?
This type of behavior happens because of an unfortunate intersection of two parts of the Voyager infrastructure that try and make query execution as fast as possible:
- Query Chunking
- Calculating Member Drill State
Query Chunking
Query chunking is an optimization that is implemented to make large queries return faster. The Voyager data access component only ever asks for the amount of data required by the Voyager client to render the visible screen. In practice this is actually a window a bit larger than the data you see in your web browser (60 rows by 40 columns) as the Voyager client caches some data so scrolling around the immediate area is fast.
For example, if you define a large OLAP data view that has 10 000 members selected on rows and 10 000 members selected on columns, the Voyager data access component will generate MDX that only asks for 60 by 40 data cells (rather than the 10 000 by 10 000 or 100 million cells)
If you scroll around in the crosstab outside this 60 by 40 cell area you will notice further MDX queries being executed.
Calculating member drill state
Voyager makes use of the fact that the result set returned from Microsoft Analysis Services contains a flag that describes the drill state of a member. Technically this uses the MDDISPINFO_DRILLED_DOWN bit mask on the DISPLAY_INFO property returned by the axis row set. For more information on this property look at the on the following web page: http://msdn.microsoft.com/en-us/library/ms725398(VS.85).aspx. This means that Voyager doesn’t have to work out the drill state of each member itself.
The Problem
If you have more than one hierarchy stacked on an axis and the hierarchy on the inner axis has a large number of members selected (large enough for query chunking to kick in) then this is where the problem will occur.
When you stack two axes, what happens is that the first member on the outer axis will be repeated for each member on the inner axis until you have cycled through all the members on the inner axis. If the number of members on the inner axis is larger than the chunk size boundary then the second member on the outer axis will not be in the first chunk of the query.
Microsoft Analysis Services sets the drill state of a member by looking to see if any of its children are also present in the current query. In the case of a chunked query generated by Voyager the child member on the outside axis may not be in the current chunk so Analysis Services might not find the child in the current query and therefore return that the member is drilled up. For example, the diagram below illustrates this.
Here we have a hierarchy with two members selected, ‘All’ and its child ‘USA’, on the outside of the row axis and five members A, B, C, D and E selected on the inside of the row axis. The red arrow indicates where the chunk boundary would lie if it were set to four tuples (which is much lower than Voyager’s default setting). If a query were executed to retrieve the first chunk it would just contain the tuples above the red arrow. When Analysis Services is computing the drill state for each member it sees the member ‘All’ and does not find any children of ‘All’ so returns a drilled up status.
Workarounds
In order to determine whether a member is drilled or not, the member selector for the appropriate hierarchy can be invoked and if the member’s children are shown highlighted in blue then the member is indeed drilled down.
In order to drill up the member, the member selector can again be invoked. Right clicking on the member will allow you to deselect all the children.
