Database Reference
In-Depth Information
need to be time balanced and which need to be summed up. This gives the end users a
very simple Pov option in their reports to toggle on and off time functionality.
6.10 tips anD triCks
There are numerous tips and tricks that could be included in this section. The following
pages will review some of my favorite tricks for querying metadata including:
• returning all members of a Dimension
• Querying for Descendants
• Querying for Ancestors
• Querying Level-0 members
• What Level/generation?
• Counts
• Filter Counts
• Filters
• Querying Properties
• Filtering on Attributes
• Filtering on uDAs
• Counts with uDAs and Attributes
• Query for Shared members
• Calculating optimal Sparse Dimension order
6.10.1 Querying Metadata
one of the more useful purposes I have found for mDx is to query the outline to find
out things about my metadata. As an administrator, I often have questions about the
outline that I need to know in order to answer a user request or perhaps leverage for
optimization. Below are a number of examples that I have used. Because I have already
explained the basics of queries, we can just run through these with a brief explanation
and a code example. I encourage readers to work with these examples and tweak them
for your own applications.
note that in most examples I am not looking to return data, I just want a list of
members that meet certain criteria. For these samples, I am going to use an empty set,
defined by curly braces with nothing in them { }, on my CoLumn axis.
6.10.1.1 Returning All Members of a Dimension
using the members() function with any
dimension will return a set with all members of the dimension. It is also possible to
specify a particular layer to limit the members returned to a single layer. A layer is
another way of saying a generation or a Level.
SELECT {} ON AXIS(0),
[Year].Members ON AXIS(1)
FROM [Sample.Basic];
Axis-1
+-------------------
(Year)
(Qtr1)
(Jan)
(Feb)
