Querying Dimension Members In Analysis Services
Many Analysis Services applications query members to populate a dropdown or find those that match some criteria – starting or ending with a string, existing with other members, having a member property with a certain value, whatever. For example, say I have a bird hierarchy and I want to find all birds with the expression “eagle” somewhere in the member name. I could do something like this:
Select filter(birds.members, instr(1, Birds.currentmember.name, "eagle") > 0) on columns from [birds]
But this can lead to poor performance in some circumstances – particularly in ROLAP cubes or cubes with expensive calculations.
Why? It is because this query asks for and returns cell data as well. A query that asks for members on columns also returns data in the cells at each column position. This might be expensive. (And I’ve run into situations where it just destroys performance). So two suggestions:
a) For you application builders querying only for members either use the schema rowsets (see the member schema) or send queries that explicitly request an empty set on the first axis; ie, something like this:
Select {] on columns, filter(birds.members, instr(1, Birds.currentmember.name, "eagle") > 0) on rows from [birds]
This way, no cell data is evaluated or returned.
b) For those you building cubes relying on applications that aren’t following the advice above and you’ve determined that performance is a problem because of unnecessary cell evaluation, create an invisible calculated member with a null expression and make it the default measure. (And be sure to test!)
