Parent/Child Hierarchy ProblemicCube

Feedback/questions about installing/configuring/using icCube products.

Page: 1

TOPIC: Parent/Child Hierarchy Problem

2012/09/04 05:41 #596

Parent/Child Hierarchy Problem

Hi,
I'm trying to make a dimension with a Parent/Child Hierarchy. My data source is SQL Server and my table's structure is following:

Code:



CREATE TABLE [dbo].[tbl_Organ](

	[Organ_ID] [uniqueidentifier] NOT NULL,

	[Organ_Name] [nvarchar](255) NOT NULL,

	[Organ_Parent_ID] [uniqueidentifier] NULL,

 CONSTRAINT [PK_tbl_Organ] PRIMARY KEY CLUSTERED ([Organ_ID] ASC)

While I create a Parent/Child dimension I got the following error:

Code:



The data of the parent/child hierarchy (Hierarchy) must be sorted (child-id:13588CC9-973C-4752-AE7A-0000D3EE9FFC,parent-id:879811E6-73BF-42D5-9A66-84D5067B7730)

Any idea how I can fix this?

Last Edit: 2012/09/04 05:44 By aligholami77.

Offline

The administrator has disabled public write access. Top

aligholami77

Karma: 0

Fresh Boarder

Posts: 2

2012/09/04 20:02 #599

Re:Parent/Child Hierarchy Problem

For the time being, when building a parent/child dimension, the data should be sorted in a hierarchical way; as your datasource is a relational DB you can use the following:

Oracle :.

select id, parent_id, level from the_table
start with parent_id is null
connect by prior id = parent_id;

Others (that's more complicated) :

with R(id, parent_id, lvl) as
(
select id,
parent_id,
1
from YourTable
where parent_id is null
union all
select T.id,
T.parent_id,
R.lvl + 1
from YourTable as T
inner join R
on T.parent_id = R.id
)
select R.id,
R.parent_id
from R
order by lvl

Hope that helps.

Offline

The administrator has disabled public write access. Top

mpolizzi

Karma: 5

Moderator