What is the likelihood of PMF truly supporting a ragged hierarchy as a dimension?

Obviously I could log a request for this, but thought I'd throw the topic up here first.

We've got a couple of scenarios where a dimension that we're loading is a ragged hierarchy -- specifically where it may only be 2 levels deep on one "branch" and it may be 4 levels deep on another "branch". We can model this and load it into PMF but to do it, the dimension has to be set up at the deepest possible level and then dummy values are used to indicate that you're beyond the deepest level of the current branch. Some of this is a result of the limitation of the number of dimensions, so we're thinking of our dimensions as generically as possible -- so it really ends up that we have multiple types of values in a single dimension whereas we'd normally split those into separate dimensions. But even beyond that -- we have a situation where a single dimension is hierarchical but has different levels depending on the branch. My dream would be that PMF would recognize the hierarchy and would know that particular branch of a dimension only goes 2 levels deep and so it wouldn't present the user with the ability to further drill into that dimension.

To come at this at a higher level, our analyst is becoming somewhat frustrated by the number of clicks that are necessary to navigate but also by the fact that it's difficult to tell where she is going next and when she will/should/can stop drilling down a particular path.

This may be a bit "pie-in-the-sky" -- but I figured it wouldn't hurt to ask!

We've batted this around for a while. We have even come up with a standard way to handle middle values on ragged hierarchies (using a *** to indicate early terminus of a particular hierarchy branch, as a technique in the loaders for now and eventually as a feature). I believe you are already using that technique? If not, it's a good one.

We don't have plans to address short-path navigation on ragged hierarchy levels in the upcoming 5.0 release. Our feature plate is already full for that. I know it's sometimes a pain to have to make those extra clicks. We will eventually address this, but probably not in 2008.

Yes, I don't want to take undue credit, but I think we might have originated that *** idea.

But yes, it is a good technique and I think it makes it FAIRLY obvious to the end user that they've passed the last node of the branch. Thanks for the update though -- if I see you at Summit, I might pick your brain a little more on this topic and the clicks/usability because I'd be interested in how others have mitigated some of the frustration. It may simply be that there are better ways to use the dashboards/gadgets that we're simply not thinking about.

Regardless, thanks for the quick responses and we'll continue to experiment!

Yes, we admit it - we have now officially stolen your great idea.
Credit where credit is due.
I'll be there at Summit; look forward to chatting.

We also have the ragged hierarchy issue here.

Question: When using the *** technique, do you populate the lower level data with ***, or do you populate the duplicate hierarchy names with ***?

Answering my own question:

You populate the duplicate dimension level names with *** when you do the dimension load.

This message has been edited. Last edited by: SethW,

As far as the *** technique. Is there an easy way to get the *** at the bottom of the tree? Right now the *** shows up as the first branch in the hierarchy.

Any suggestions are appreciated.

-Seth

Asterick is ASCII 42, so that's going to sort before any likely alpha-numerics. So you'll need to pick some character that sorts after your standard alpha-numerics.

Funny, I would assume that you'd want the higher level to appear first. In this hyper-simple example, I assume you'd want the President's Office to appear before the VP's Office:

Level01               Level02
------------------    ----------------------
President's Office    *****
President's Office    VP Office

  

But if your business needs are different, then you'll have to find a different character to use. Do a web search on "ascii collating sequence", you'll get hundreds of hits.

EricH

Dear All,
Any new developments pertaining to the handling of a ragged/staggered hierarchy in PMF rel 5.2.3?
Our higher Ed customer is attempting to define an overall Organizational Structure which includes:
1 a Academic Org Structure: Institution(Vice Chancellor)/College/School/Department/Course
2 a Qualification Org Structure: Institution(Vice Chancellor)/College/Qualification/Course
3 a Support Org Structure: Institution(Vice Chancellor)/Pro Vice Chancellor/Vice Principal/Executive Directors/Directors/Department Heads/(Unit or Sections)
In combining the above it creates various branches that terminate at higher levels compared to others.
It also creates “holes” in some branches where dimension values only changes at lower levels.
My personal suggestion is to split into 3 distinct dimensions as listed above.
What is best practice?

We generally advise people to read Kimball on Dimension design. We follow it fairly closely.

Your three Dimensions seem quite similar to each other. I think what's happening perhaps is that the three departments are in disagreement as to how the information should be aggregated and organized. The thing to do is to get to the bottom of the WHY they are so similar and yet so split... in other words what are they trying to see and what purposes this vision has for them. This is more of a process than a technical approach. You can build any DImensions you want with PMF, but you must practice a certain economy as each Dimension incurs some increase of load time for the Metrics warehouse, and of course there is an absolute limit of 15 Dimensions in the system besides Time.

In our practice of helping customers, we find that a brief conversation with the principals from the three departments with three different views of the world can profit you greatly. Now, we're not saying that you haven't met with all these principals. But a meeting to specifically rationalize their Dimensions to allow them all to get what they want - perhaps with an eye to finding a compromise - might profit everyone.

As to ragged hierarchies, PMF requires you to "rectangularize" your hierarchies. I think if you search through FocalPoint you'll find references to this approach. Let's look at a simple example.

COUNTRY > STATE > CITY

US > NY > NYC
US > NY > SCHENECTADY
US > MA > BOSTON
US >    > DC

Note the last. There is a city in the US called WASHINGTON DC that lacks a "state." This is a classic and simple ragged hierarchy. THe problem it presents is that when you want to add up your data for the entire country, if you were to add up the sums for all states, and the sums for the country, the state level aggregate would be missing the DC numbers. Thus your results would not conform.

To allow for the conformity of your aggregates, you would simply have it conform to a rectangular shape to allow all aggregation to be proper:

US > NY > NYC
US > NY > SCHENECTADY
US > MA > BOSTON
US > DC > WASHINGTON DC

This leaves you a "skippable" level for DC, and also allows us a proper "state" level aggregate that matches the national aggregate at the country level.

Some people prefer to use a * character to represent the skippable level. That is an acceptable solution:

US > NY > NYC
US > NY > SCHENECTADY
US > MA > BOSTON
US > *  > WASHINGTON DC

Hope this makes everything clear. Dimensional design is a fascinating subject. We recommend the following book on the subject:

The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence

thanks

Bob,
Thank you for your comprehensive response.
All the above is taken into considertion.
Will shout again if we don't make headway, however you have answered my questions more than sufficiently.