I am writing an MDX query and I want to include dimension attributes as part of the DIMENSION PROPERTIES section of the query.
SELECT
{ Measures.SalesAmount, Measures.ShipQuantity } on columns,
{ (Items.ItemNumber.Allmembers,
Time.Month.AllMembers) }
DIMENSION PROPERTIES
Items.Description, Items.Category,
MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
From Sales
My problem is that while I can include Items.Description in the dimension properties list, I cannot include Items.Category in the list. The query parser gives an error,
The [Items].[Category] dimension attribute was not found.
I look at the definition of the dimension, and I see no difference between the two attributes in the schema.
What should I be looking at to see if there are differences between these two attributes? Or am I doing something else wrong?
Mike
I'm guessing that Items.ItemNumber is an attribute hierarchy and so Items.ItemNumber.AllMembers includes the All member plus the members of the attribute. Since the server will only return dimension properties that apply to all the members returned for that dimension, having members from different levels can unintentionnally restrict the dimension properties. Try changing the mdx to remove the all member (probably something like Items.ItemNumber.ItemNumer.AllMembers).
Another possibility is that Items.Category is a valid member property name, but not one associated with the level you are querying. Check for a similarly named member property as its easy to confuse member properties for attribute hierarchies and user defined hierarchies. In this case the name you want might be something like "ItemNumber.Category" since it probably only applies to the ItemNumber level.
|||I'm not sure I am understanding your answer.
Items.ItemNumber is an attribute heirarchy, and so (it appears) are Series, Family, Category, and a bunch of others.
There are two user-defined hierarchies.
I don't understand when to use Items.Description and Items.Description.Description - both work in the DIMENSION properties statement - the first is the hierarchy, the second is the level. Another items attribute hierarchy I have is [Product Life Cycle], and neither Items.[Product Life Cycle] nor Items.[Product Life Cycle].[Product Life Cycle] works in the dimension properties statement, tho they appear identical in the dimension editor.
Here is the Item Dimension in the BIDS dimension editor:
|||Now I know why Items.Description works in my DIMENSION PROPERTIES statement.
It is referring to the built-in property of the dimension, the Description property that I see in the Dimension editor property window when I select the top node in the tree, the Items dimension node itself. It also works for Items.ID and Items.Name built-in properties.
Still, now the question remains, why can't I use Items.Category or Items.Whatever in my dimension properties statement?
|||If Items.ItemNumber.Allmembers returns an all member, then that is likely your problem. Try the MDX satement with a single specific member.
Here's a re-wording of basically what I wrote in my first reply that might help.
When including member properties in MDX queries using the DIMENSION PROPERTIES syntax, here are two common causes for confusion.
First, the server will only return member properties that apply to all members requested in a hierarchy. This means that if you request members from multiple levels, you will only get member properties which you have requested and which exist for all of the levels from which you have requested members.So if you request “[MyDim].[MyHier].Members” you will only get member properties applying to all levels.Instead, you should specifically request just members of a single level using something like “[MyDim].[MyHier].[MyLevel].Members”.Note that most attributes hierarchies contain two levels – the all level and the attribute level.This means you can get different results from [MyDim].[MyAttribute].Members and [MyDim].[MyAttribute].[MyAttribute].Members.
A second source of confusion is the fact that you can have similarly named member property for attribute hierarchy levels and user defined hierarchy levels based on the same attributes.This is true even if the attribute hierarchy is not visible.As a result, you may be unintentionally using a perfectly valid member property name that doesn’t correspond to the level of the members you are requesting.This results in no error, but you don’t get back any member properties.
