Problem in setting Attribute Relation

[suris]

Hi,

As I am novice of MSTR Desktop, I have a basic question.
I have two tables EMP, DEPT. I want a report like no of employees per department . The Dname is available in DEPT table and empno field is in emp table. (I don't want to make use the deptno in employee table, reason being want to know how to make joins between two or more tables)

When I develop a report, for all the departments I am getting employee total as 14.

The metric is grouped properly.
The metric formula is Count(Empno) {Dname}

Any valid suggestions are appreciable.

Regards,
suri

 

[suris]

Hi,

I forgot to add the sql query generated by report.

Report: Dept Emp

select a12.DNAME DNAME,
count(a11.EMPNO) NOOFEMPLOYEE
from EMP a11,
DEPT a12
group by a12.DNAME

Here, there is no join between emp and dept table. No idea, how to make join. If any one has idea about this please share your knowledge across.

Regards,
Suris

 

[Sridharan]

suri,

i think that query will do a cross join and i think u should set relations during the attribute creation wizard... never mind wat u can do is click the dept attribute and set the relations and run the query....

i think that should do for you....

regards,

sridharan

 

[MSTRupesh]

make the attributes parent-child of each other.
dept is parent of employee.

-R

 

[suris]

Hi Sridhar,

I have made relation between emp and dept tables at attribute level. Still the problem exists. If possible, can you explain the steps?

Regards,
suri

 

[Sridharan]

suris,

go to schema objects of your project and click on attributes... the right pane should show all the attributes in your project ... open dept attribute and set the relations........

the relationship should be like this....


Dept_Id One To Many Employee_Id


select dept_id or whatever field you have and click childrens and select employee_id as children and relationship type should be One-Many..... this should do for you....


regards,

sridharan

 

[suris]

Hi Sridhar,

Thankx a lot for your prompt help.

Please see the steps listed below done by me.

Gone to the attributes in "Schema Objects".

The listed attributes are

Deptno
Dname
Loc
EDeptno - Employee table's deptno
Empno
Ename
Job

I selected Deptno. Added Empno as child. The description is
"Each Deptno must have one or many Empno". This is fine.

Created a new metric in "Metrics" of "Public Objects".
metric name is "no of employees"
The formula is

Count(Empno) {Dname }

Dname - Dimensionality

Created a report by selecting DName attribute and the "no of employees" metric. The report sql is as follows

Report: DeptDetails

select a12.DNAME DNAME,
count(a11.EMPNO) NOOFEMPS
from EMP a11,
DEPT a12
group by a12.DNAME

In the above, I am not getting the join and the output is as follows

ACCOUNTS 14
OPERATIONS 14
RESEARCH 14
SALES 14


Even I tried making the relation like

Dept.Deptno Parent Emp.Deptno Child and tried all other possibilities also.

It would be great, if u could resolve this issue.

Regards,
suris

 

[Sridharan]

Suris,

does the column name of department_id in both tables same.... seeing your attributes i feel u've given a different name to employee table department no(edept_no)....
try changing that to wat u have given in department table and create a new project...... i did exactly your project and it worked perfectly even without specifying relationships.......... i used dept_id in both employee as well as department tables....



later i gave different name to employee tables column as edept_no and i got your results in report..... so instead of pulling department no of department table try pulling edept_no and no of employees it will give a correct result coz no joining is required....

but thing is if the column name is same in both the tables its works perfectly for me......


regards,

sridharan

 

[suris]

Hi Sridhar,

In database, both tables dept and emp have same column name for making relation. i.e deptno in both.

During the project creation, it was not listing the deptno of employee table, as both tables were having same column name.

After creating the project, I manually added the 'deptno' column from emp table. I could't save the attribute as 'deptno', as already 'deptno' attribute exists (Which was listed from dept). So I saved it as edeptno.

I am also getting proper results when I don't use a metric.
I tried to create a report with following fields

Deptno,Dname,Job,empno.

the results were perfect.

When I tried to add a metric (instead of empno I tried to put count(empno)) then I am getting the problem.

Regards,
Suris

 

[Sridharan]

Suris,

I did xactly a similar project like yours with same dept_id name in employee and department... it worked even without relationships.... one more thing try using the dept_no of department table and count of employee rather than edept_no...

regds,

sridharan

 

[nlim]

suris, the main problem here is that attributes are supposed to represent classic ER type entities. So for the department attribute, it has IDs(defined by deptno column) and descriptions (defined by dname). It won't work to create a new attribute for each column....

here's my suggestion:

1) delete the attributes called deptno, dname, edeptno.
2) create a new attribute call "department" using the Right mouse click.
- a dialog will prompt you to choose "source table" on the top left: pick dept table, then pick deptno. Before you click ok, make sure "automatic" mapping is chosen. This ensures that the ID for department is deptno. In this case the joins will be automatic since the column names are the same. Double check to see that the 2 tables emp and dept are listed, and that DEPT is Bolded (ie. lookup table and won't use the deptno in emp table)
- create a new attribute FORM (not new attribute) for description attribute category. pick dname from dept table
3) you really shouldn't need to have the employee department relationship, but just in case do it.

your metric should be count(empno) without any other curly brackets.

report is Department and metric.

More information available in the basicsetup.pdf file

good luck, MicroStrategy is harder to setup and does not use the classical simple concepts of joins but rather attribute abstraction. But once you get it, you'll find it much easier to do any type of reporting...

 

[suris]

Hi Nlim,

Thanx a lot and it works! Great Job...

Regards,
Suri

 

[suris]

Hi Nlim,

Thanx a lot and it works! Great Job...

Regards,
Suris

 

[suris]

Hi Sridhar,

Thankx for your kind contribution...

Regards,
Suris