×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Problem in setting Attribute Relation

Problem in setting Attribute Relation

Problem in setting Attribute Relation

(OP)
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

RE: Problem in setting Attribute Relation

(OP)
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

RE: Problem in setting Attribute Relation

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

RE: Problem in setting Attribute Relation

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

-R

RE: Problem in setting Attribute Relation

(OP)
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

RE: Problem in setting Attribute Relation

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

RE: Problem in setting Attribute Relation

(OP)
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

RE: Problem in setting Attribute Relation

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

RE: Problem in setting Attribute Relation

(OP)
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

RE: Problem in setting Attribute Relation

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

RE: Problem in setting Attribute Relation

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...

RE: Problem in setting Attribute Relation

(OP)
Hi Nlim,

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

Regards,
Suri

RE: Problem in setting Attribute Relation

(OP)
Hi Nlim,

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

Regards,
Suris

RE: Problem in setting Attribute Relation

(OP)
Hi Sridhar,

Thankx for your kind contribution...

Regards,
Suris

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close