Self Join in MicroStrategy

Hello Guys,

Today will discuss, how to model a schema for the Self Join requirement. I have been asked many times in the interview for self-join and today I thought of sharing the concept for the same.

So, before going to build the schema, first have a look at the basic SQL query which we required to generate from the MSTR report:

SQL:

select e.Name as Emp_Name, m.Name as Manager_Name 

from tblEmployeeDetails e

join tblEmployeeDetails m 

on e.Mng_id = m.Emp_id 

--> Below is a Tables structure of  tblEmployeeDetails 

Now, as we can see in the above SQL the same table (tblEmployeeDetails ) is used to join itself. and to achieve this kind of requirement in MSTR we have to use the "Table Aliasing" and "Attribute Role" Concept.

So, without any further due, let's jump into the Schema creation in MSTR.

Below are the steps which you need to perform to get the output:

1. Firstly create an alias table of tblEmployeeDetails and name it as tblEmployeeDetails_Mng.
   
   
   
   
   
   
   
   
2. Then create Emp attribute (Id & Desc form) using tblEmployeeDetails table like below:
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Then create Mng attribute (Id & Desc form) using tblEmployeeDetails_Mng table like below:
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
4. Now, create one attribute Emp - Mng which will act as a relationship bridge to join both the table to get the desired SQL. i.e (on e.Mng_id = m.Emp_id )
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Once, you have created all the attributes, now it's time to create a Report.
6. Create one report and include Emp & Mng attribute. and below is the output:
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   & Below is the sql generated by report:
   
   select distinct a11.Emp_id  Emp_id,
   a11.Name  Name,
   a12.Mng_id  Mng_id,
   a12.Name  Name0
   from tblEmployeeDetails a11
   join tblEmployeeDetails a12
   on (a11.Mng_id = a12.Emp_id)

So, by this way we can create the self join in MSTR.

Let me know by commenting if you have any queries or any other requirements.