Find all dimensions with calculated members using AdomdClient

Hi,

I need to find all dimensions in a cube that have calculated members but I can't afford to call GetMembers on every hierarchy level.

Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient

Thanks,

James

Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members.

> Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient

Not possible.

> Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members

Can you please clarify in more details what is the special about calculated members so that you want to know in advance whether hierarchy has them.

|||

Hi Mosha,

Thanks for the reply, I believe our very own Dave Grant showed you some of RSinteract’s MDX at PASS in Seattle.

We decided that placing the filters in the sub-query allowed us to provide more user friendly totalling (i.e. when filtering row or column attributes). However calculated members are not allowed in sub-queries, so we need to know which Dimension Attributes contain Calc Members and place any filters inline or in a where clause. Our current solution is to have an administrator run an AMO application which saves dimensions with calc members to a config file (AMO must be run with admin privileges).

Cheers,

James

|||What I wonder is why do you want to exclude the entire hierarchy just because it may have calculated members. It seems like if you just wrap the sets that you put inside subselects with StripCalculatedMembers function call, both you and your users will be better of.

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

Filtering structure across 2 dimensions

I would appreciate any help with the following problem:

Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?

But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )

This only returns rows in which the two heirarchy members are related.

So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?

Thanks a lot.

When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:

SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

|||

Thanks for the reply.

Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)

I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.

Thanks.

|||

The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.

If you have another one the it should exists as another fact table or anything else.