Solved

Hierarchy across multiple dimensions

Posted on 2008-10-21
8
839 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
  • 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now