Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Hierarchy across multiple dimensions

Posted on 2008-10-21
8
Medium Priority
?
862 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

580 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