Hierarchy based on one dimension.

[Former Member]

Hi,

How to create a hierarchy based on one dimension. For example: we have table:

Category

-

---

Category_ID

Parent_Category_ID

......

I want to create hierarchy, which like as:

Category_Level_1

Category_Level_2

....

Is it possible without create new alias of table for each level?

Thanks

[Former Member]

OK, thanks

It's idea.

So far,I inserted a few alias of table (one per level in hierarchy). Idea with derived table has advantages, but also disadvantage.

Comparison "alias of table" and "flat hierarchy"

"Alias of table"

+ generated sql based is more optimal (amount of alias in sql is equal used level in hierarchy)

- you have to insert more table to Universed

"flat hierarchy"

+ one table (derived table)

+ more elastic

- generated sql isn't optimal

Thanks for your answer, my knowledge is greater now.

Slawek

[Former Member]

Thanks for fast answer.

Version: BO XI r2

Sorry, I hadn't writen clearly.

My example table:

Category

-

---

Category_ID

Parent_Category_ID

Name

.....

Object: Category (from Category.Name)

I want hierachy like as:

Category (1)

Category (2)

Category (3)

etc.

Each level of hierarchy is from one object - Category

One category may has parent category, which may has parent category etc

Edited by: Slawomir Druzba on Jun 2, 2010 2:52 PM

[Former Member]

Hi,

You can create a derived table that flatten the hierarchy in multiple levels.

You have to define the maximum number of levels in the derived table beause it cannot be dynamic.

Here is a sample (in attachment) that I did in the past on the Foodmart SQL Server database (it works well).

Didier

[Former Member]

Which version of BO are you using?

Assuming that you are using BO XI R2 or above version.

And Is your question - how to create custom hierarchies in Universe Designer..... for two different fields (columns) taken from a single database table?

If so, yes you can create custom hierachy for above scenario. In Custom Hierarcies option: Add the Parent_Category_ID first and below that add Category_ID.

If you want to rename your hierarchy levels as Category_Level_1 & Category_Level_2, then rename their corresponding objects names in Clasess & Objects pane.

- Anil