Hierarchy across multiple dimensions

I have a cube with a Project and an Offer dimension.  There is a one to many relationship from projects to offers.  The client's reporting application is excel and it appears that you can only have drilldown capability if you create a hierarchy (Unlike the SSAS Viewer where this works fine).  I don't know how to create a hierarchy between attributes that exist in different dimensions (Project and Offer).  I'm trying to either
1.  Learn how to drill down on dimenion attributes in excel that aren't part of a hierarchy or...
2.  Learn how to create a hierarchy between attributes of two different dimensions or...
3.  Last choice is have an attribute exist in two different dimensions by modifying the data source view and adding the attribute to both dimensions.  (I don't know if this is a good idea).
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't know why you have 2 dimensions when there is a strict relation 1:N as you write. You can have one dimension with multiple hierarchies in AS2005+ if there is a business need. You are wasting resources when processing and querying also.

Is there any option Drill Across in Excel? Did you try ProClarity?

Of course you can implement Offers and Projecs both like one dimension and separate dimensions also.
bminetwork2277Author Commented:
I've done some more testing.  On my system when I create the cube using Visual Studio 2005 and consume it in excel 2007 I have drilldown capability (I may be using bad terminology.  I mean plus/minus expand/collapse) no matter which dimension attributes I pull in to the row labels.  Attributes can have no hierarchy and can even exist in different dimensions and I can still expand/collapse.

When I try to do exactly the same thing on the same versions of visual studio 2005 and excel 2007 on my clients system I don't have the ability to expand/collapse unless a hierarchy is set up.  I've tried creating another simple cube from scratch on my clients system and get the same behavior.

This leads me to believe there is some setting in either visual studio or excel that is not enabling expanding and collapsing unless hierarchy's are created.

Since I know I can get this to work on my system, I'd like help determining why this other cube is behaving differently.
bminetwork2277Author Commented:
I have other requirements that weren't relevent to this question that necessitate having multiple dimensions.  I don't know what drill across is, but I do need to clarify that I think I should have been saying expand/collapse and not drill down.  ProClarity is not an option.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Expand/collapse is equivalent to drill down. You are correct.

You write "this other cube is behaving differently" means that the cube is set different. You have an option when creating attributes in Studio 2005 to have them:
- in a hierarchy (attribute has AttributeHierarchyVisible set to False) - attributes can be still accessed via Property method in MDX - I belive this is option of the other cube
- in a hierarchy and as attributes (not recommended)
- only like attributes - this is default, case of your cube
bminetwork2277Author Commented:

I thought you had it, but I checked the AttributeHierarchyVisible(True), AttributeHierarchyEnabled(True), and AttributeHierarchyOptimized(FullyOptimized) on both cubes and they both have the same values for all attributes.  It must be something else.
It is not easy to say the source of the differences. To other issues may belog a perspective - there can be other rules in a dimension publication - or a security rule where members of an attribute that is creating a level can be disallowed.
bminetwork2277Author Commented:
I never did find a way to get drilldown across multiple dimensions.  I ended up creating a dimension using views just to get this drilldown capability.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Funny I ran into similar problem. An attribute was visible via BI Studio but outside even in Management studio was not accessible. I had to drop that attribute from its dimension and again recreate it. You know - event the best software can have its weeknesses.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.