Ho to filter dimension in BIDS

Hi,
I have 2 cubes, Cube_A and Cube_B, and a common dimension, Dim_C.
When I browse Cube_A I don't want to see dimension members that have some values (for example some name members that begin with caracter "A" or "Z").

How can I do this using calculation tab of "Business Intelligence Development Studion" (BIDS) ?

(I don't want to define role on dimension)
thanks.

We often limit sets on the axes as part of the query (MDX) statement, but I've never heard of making specific members of a set non-visible for all users and all browsers like this. I realize you said you don't want to create a role, but that may be the only way to accomplish this.

If someone else out there knows a way to pull this off, I'd be interested in hearing the solution.

Good luck,
Bryan

Hierarchy Members

Hi

Is it possible to hide an Hierarchy member?

Regards

I am not quite sure what you are looking for here, but it is possible to hide an "attribute" that is part of your dimension definition. Click on the "attribute" you want to hide and then go to properties for that attribute. There is a property called AttributeHierarchyVisible which can be set to "False" to hide the attribute. If you are trying to hide individual members from certain groups of users you can use security roles. Create a security role and then go to the "Dimenension Data" tab. Here you can choose which members to allow or deny from members of the role.

HTH,

Steve

Hierarchy is used more than once in the Crossjoin function

When I do this in Adventure Works:

SELECT NON EMPTY {
[Customer].[Customer Geography].[State-Province].Members *
[Customer].[Customer Geography].[City].Members
}
ON AXIS(0)
FROM [Adventure Works]

I get this: "The Customer Geography hierarchy is used more than once in the Crossjoin function."

How do I do the equivalent in an MDX statement that works? I want users to be able to drag multiple hierarchy levels onto the same axis. I have spyed on the MDX that BIDS is generating, and it seems to be cheating by not getting the states and citys back in the same MDX statement. It looks like its combining the results of two queries at run-time as the user expands nodes in the tree. Isn't there a better way?

Thanks,

Terry

Hi Terry,

Any reason why cross-joining the attribute hierarchies instead won't work for you, like:

SELECT NON EMPTY {

[Customer].[State-Province].[State-Province].Members *

[Customer].[City].[City].Members

}

ON AXIS(0)

FROM [Adventure Works]

|||

Hi Deepak,

Thanks for your reply. Our application allows users to drag-and-drop whatever they want, so in this instance I don't want to prevent the user from putting two hierarchy levels on the same axis. Maybe the solution is somehow use metadata to find the attribute names that built the hierarchy levels and use the attribute names behind the scenes in the crossjoin function? Is that possible?

|||You can use AMO to retrieve the necessary metadata to identify which attribute underlies a hierarchy level and its corresponding attribute hierarchy.

Hiding "ALL" level in a dimension

Hi,

When I browse the data of any dimension then I have the "ALL" level and then the members of the dimension.

How can I remove / hide the "ALL" level in Analysis Services 2005.

Thanks in Advance

You can set the IsAggregatable property of the attributes to false. But this has other implications and might not be exactly what you want as the All member is the default member. The easiest way to explain this is with an example.

If you were to display Sales Amount by customer, without specifying a member of the product dimension then the "All Products" member would be used. If you were to change the Product dimension to remove the all member then you would either have to specify a default member or specify a particular member as default or SSAS will pick a member (usually the first one in the dimension) to use as the default. Thus when you look at Sales Amount by Customer it would now only be for one particular product.

Personally I only remove the all member in very specific circumstances. When doing budgeting cubes, where you have a dimension with budget/actual/forecast members, then it does not make sense to aggregate these together so I usually remove the all member. And for some cubes I take it out of the time dimension as the concept of aggregating measures across all time members does not always make sense.