Need some help on Joins

[sekar211]

I have a userprofile table with following columns: ID,supervisor(ID),Manager(ID).There are separate lookup tables for supervisors and managers to get the respective names.I will be joining fact table on the ID column of the userprofile which can be either supervisor or manager and the supervisor reports to some manager.

Now when I place only manager and the metric on the report i get the amount for all the managers.But when i place both managers and supervisors on the report i loose the amount that is directly assocaited with managers.I mean to say that I will only get the supervior names and the corresponding managers but not the records for the managers who don't have any supervisors and the amount is directly assocaited with them.

I hope i am clear with my question.Any help on this will be greatly apprecaited.

Thank You.

 

[iolaper]

Go to your metric property and change the JOIN to be OUTER. It will be set by default to inner. I believe that will show up all managers and supervisors who have no relationship between them.

Plan B:
If for example a Supervisor S does not have a Manager M, what you could do is define the ID, Supervisor(ID),Manager(ID) table as a view. Then coalesce the Supervisor(ID) and/or Manager(ID) to, say, '0' or just a blank string.
It will show up on the report.

Hope this helps!

 

[sekar211]

All supervisors reports to a manager.

well,i have only one metric so i think metric inner or outer joins don't come into picture here.I think it has to do with attribute inner or outer joins.

the issue here is when i drill down to the Supervisor level from manager level,i get only those amount which are at supervisor level and the corresponding manager to whom the supervisor reports to.But i loose the result at manager level because not all fact are at supervisor level.There are some at manager level.I want to see both the levels at the same time.

 

[entaroadun]

MSTR doesn't allow for outer joins on attributes. From an academic standpoint, they aren't necessary. An entity (user profile) should have a value for every attribute (i.e. supervisor). The fact that a user profile has NO supervisor must be represented by a value that isn't dropped when joined to the supervisor lookup table. Most data modelers miss this.

In other words, the user profile table needs to have a value in the supervisor column for every row. If the user profile has no supervisor, you should populate it with a value (usually zero) that denotes this. Your supervisor lookup table should have an entry (zero) with a description of "No Supervisor".

The same goes for manager.

Once you do this, the report will be correct. To exclude user profiles that have no managers, just put a filter of Manager(ID)=0 on the report, or use the equivalent attribute element filter.


BTW, you shouldn't use NULL to represent a "No Supervisor" relationship. NULL has a specific meaning: "I Don't Know". In other words, if an user profile has NULL for supervisor ID, then you are saying that you don't know who the supervisor is for this user profile. This is different than saying that the user profile has no supervisor. In the first case, you DON'T KNOW who the supervisor is. In the second, you KNOW FOR CERTAIN that there is no supervisor. The distinction is very important, and most data modelers don't use NULL properly.

In any case, it's safer to not allow NULL, and use a token value to represent "I Don't Know" simply because NULLs get dropped in joins.

 

[sekar211]

Thank you very much entaroadun.Now i totally understand what the problem is.I will discuss this with the Modelling team and see if we can make the necessary changes.
Once again thank you.