?
Solved

Hierarchy across multiple dimensions

Posted on 2008-10-21
8
Medium Priority
?
858 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

762 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