difficulty to write correct schema (level pb)

[peppena]

Hello ,
i have this table :
|-------------------|
employee
|-------------------|
| emp_id |
| emp_name |
| supervisor |
|-------------------|
supervisor is the id of supervisor it's foreign key from the same table because supervisor is an employee
i cant whrite a correct dimention to observe in interface supervisor 1
|---employee2
supervisor 2
|----employee2
.......
i whrite this dimension :
<Dimension name="Employee" >
<hierarchy hasAll="true" primaryKey="emp_id">
<table name="employee" />
<Level name="Supervisor" column="supervisor" uniqueMembers="false"/>
<level name="Employee" column="emp_name" uniqueMembers="false"/>
</hierarchy>
</Dimension>
but i can see only supervisor_id
any one can hlp me

[sgwood]

There is an example of this in FoodMart, with exactly this scenario ie. employees/supervisor.
<Dimension name="Employees" foreignKey="employee_id">
<Hierarchy hasAll="true" allMemberName="All Employees"
primaryKey="employee_id">
<Table name="employee"/>
<Level name="Employee Id" type="Numeric" uniqueMembers="true"
column="employee_id" parentColumn="supervisor_id"
nameColumn="full_name" nullParentValue="0">
<Closure parentColumn="supervisor_id" childColumn="employee_id">
<Table name="employee_closure"/>
</Closure>
<!-- Explicit Closure of [Employees] (just for unit testing):
== [Employees] is a parent/child hierarchy (along the relationship
== supervisor_id/employee_id). The table employee_closure expresses the
== closure of the parent/child relation, ie it represents
== ancestor/descendant, having a row for each ancestor/descendant pair.
==
-->
The closure table is there to speed things up.
Sherman

[peppena]

thanks for help sgWood
this is sql presented my table :
CREATE TABLE employee (
emp_id character varying(30) NOT NULL,
emp_name character varying(255) NOT NULL,
supervisor character varying(30) NOT NULL,
);
ALTER TABLE ONLY employee
ADD CONSTRAINT "$2" FOREIGN KEY (supervisor) REFERENCES employee(emp_id) ;
I tested to write this mdx to found supervisor and his employees but i get not correct response
<Dimension name="Employee" primaryKey="emp_id">
<Hierarchy hasAll="true" allMemberName="All Employees" primaryKey="emp_id">
<Table name="employee"/>
<Level name="Employee" uniqueMembers="true" column="emp_id" parentColumn="supervisor" nameColumn="emp_name" nullParentValue="0">
</Level>
</Hierarchy>
</Dimension>
i just see one row : All Employees and can't see details (supervisor and his employees ) , i don't undrestand !

[sgwood]

Can you show the MDX you were using?
Sherman

[jhyde]

I'm intrigued how you managed to enable the foreign key constraint, because at least one employee (the root) must have supervisor=0. Yet if there is no employee with emp_id=0, the foreign key constraint will be breached.
This may be related to the problem you are seeing. The children of [All Employees] will be the employees with supervisor. Are there any such employees in your database?

[peppena]

thanks very well by correct a nullParentValue = '-' and not '0' oups !!
but in the details (Drill Through Table) i see tow column for this dimension Employee Id and Employee Id(key) ,
i preffred to suppress Employee Id(key) from interface
any idea ?
thanks

[jhyde]

This is the way it is supposed to work. When you drill through and one of the dimensions is a parent-child, you get two columns. We print the key because the other column might not be unique (for example, there might be employees called John Smith in two departments).