Joins

[mstms]

Hi, I have a problem with joins.person id,person name are stored in one table.industry headid, sector headid... are stored in other tables,which I have to join with the person
id to get the name.Hence this person id is queried 4 times.
a11.Z_PRSN_OPRID = a12.ESA_PERSON_ID and
a12.ESA_PERSON_ID = a13.Z_INDLEAD_OPRID and
a12.ESA_PERSON_ID = a13.Z_SECTLEAD_OPRID and
a12.ESA_PERSON_ID = a13.Z_SEGLEAD_OPRID .
the person name is in a12,indlead,sectlead,seglead all have different values in a row and finally all the rows are dropped instead of displaying the names for indlead, sectlead..in every row.Is there any way I can do this.Thanks for any suggestions.
mstms

 

[iolaper]

Couple of questions:
1)Why do you want to join to so many columns in a13 to get the description?
2) a12 is your Person lookup table and a11 I believe is your fact table. What is a13?

Are INDLEAD, SECTLEAD and SEGLEAD the same as PERSON?

The problem you are having is that you are joining PERSON to the above three. You need to go to the PERSON attribute and modify ID column and delete all those that are NOT related to PERSON.

If I have not answered your question correctly please paste the SQL here so that I can give you a better suggestion.

 

[mstms]

Hi Iolaper,Yes, as you said,a11 is fact table and a12 is the person lookup table,and a13 is the dept dimension table,the fact table stored for each employee(z_prsn_oprid)and to get the name of this employee we use first join,each employee belongs to a department which belongs to a segment,the segment belongs to sector,etc.The segment,sector,industry all have leaders and the ids for them are stored in a13.The seg leader,sect leader,ind leader,employee have different values for ids and to get the names for these ids from a12,(the name column is a12.esa_person_desc) we use joins 4,3, 2, 1 respectively.This sql , what it does is it is trying to equate all the four id columns and finally gives no results.
Since the sql is too lengthy, I hope this explaination would give you an idea,Let me know if you have any suggestions,thanks for your response.
mstms

 

[iolaper]

there is nothing wrong with the 4 ids equating, but they are equating to the same PERSON_ID.
A simple explaination is saying you are trying to match a date with your date id, but you are also matching date with month and year columns in the same table.
I would have wanted person id from the fact table to match up with the look up person table to get person desc and then segment id to match up with the segment lookup table to get segment desc etc.
Open your PERSON attribute and double click on your ID. In the window that opens up tell me what other IDs you see there and what their default lookup tables are.
I feel this problem is with your attribute mapping.

 

[mstms]

Hi Iolaper,i am joining the leader id, segment id etc with person id in person lookup table becos a12.esa_person_id contains ids for all the seg leaders, sect leaders, ind leaders etc.The person(employee) attribute contains only the a11.z_prsn_oprid,a12.esa_person_id in mapping,but I am also mapping the seg leader, sect leader, ind leader from a13 with a12.esa_person_id since this column contains ids of leaders for seg,sect.. and to get the desc from a12.esa_person_desc.Is there any other way to get the desc for all these from the same a12.esa_person_desc,should we have to use the temporary tables option.

 

[mstms]

Hi, I found the solution for this problem, if we have to join the same column more than once, then we can create an alias of the table in the schema in microstrategy and we can join against the same column from the alias,Any way thanks for suggestions from iolaper.
mstms

 

[FLB]

Seems like you need to use table aliases to "separate" logically the different roles that a person can have.

2 cents,
FLB.

 

[mstms]

Hi FLB, Yes, either I could have used attribute roles or create table alias in the tables folder. Since I am new, I couldnt get this solution immediately, Thanks for the suggestions.
mstms