Solved

Hierarchy across multiple dimensions

Posted on 2008-10-21
8
853 Views
Last Modified: 2013-11-16
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).
0
Comment
Question by:bminetwork2277
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 8

Expert Comment

by:srnar
ID: 22790091
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.
0
 
LVL 1

Author Comment

by:bminetwork2277
ID: 22792643
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.
0
 
LVL 1

Author Comment

by:bminetwork2277
ID: 22792659
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 8

Expert Comment

by:srnar
ID: 22794301
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
0
 
LVL 1

Author Comment

by:bminetwork2277
ID: 22797948
srnar,

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.
0
 
LVL 8

Assisted Solution

by:srnar
srnar earned 500 total points
ID: 22810973
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.
0
 
LVL 1

Accepted Solution

by:
bminetwork2277 earned 0 total points
ID: 22892220
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.
0
 
LVL 8

Expert Comment

by:srnar
ID: 22893054
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.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question