Share via

SQL Server: Best way to add additional parent child attribute

  • Article

Requirement 1

In some requirement most of the user want to see data by name i.e customer Name employee name etc. but in few case user want to see data by id as well as by name.  If user want to see data by user and by id in Parent child dimension we need to do following changes

Open adventure works project in BIDS, open employee dimension select employee attribute and select Name Column property s we can see this property set with Employee.FullName.  

But Employee attribute contain data as id. After this setting process the dimension now when user browse data they can view employee name. See below image

For user who want see user id we need to add same employee dimension same as how we are adding employee dimension earlier, but we give  a different name, for our example we give name here EmployeeId see in below image

For EmployeeId we can add same attributes like Employee dimension and same relationship as Employee dimension see below image.

Requirement 2

If user want to see bot user id and user name at the same time. First we need open DSV and convert Employee dimension as named query

Query

Past this code in named query

 

SELECT
 
EmployeeKey, ParentEmployeeKey,
 
EmployeeNationalIDAlternateKey, ParentEmployeeNationalIDAlternateKey,
 
SalesTerritoryKey, FirstName,
 
LastName, MiddleName,
 
NameStyle, Title,
 
HireDate, BirthDate,
 
LoginID, EmailAddress,
 
Phone, MaritalStatus,
 
EmergencyContactName,
 
EmergencyContactPhone, SalariedFlag,
 
 Gender, PayFrequency,
 
BaseRate, VacationHours,
 
SickLeaveHours, CurrentFlag,
 
SalesPersonFlag, DepartmentName,
 
StartDate, EndDate, Status, EmployeePhoto,
 
 CASE WHEN  MiddleName IS  NULL
 
 THEN convert(varchar,EmployeeKey)+ ' ' + FirstName  + ' ' + LastName
 
ELSE convert(varchar,EmployeeKey) +' '  + FirstName + ' ' + MiddleName + '.' + ' '  + LastName END  AS FullName,
 
  
 
 CASE WHEN  Gender = 'M'
 
THEN 'Male'
 
ELSE 'Female'  END AS  GenderDesc,
 
  
 
CASE WHEN  MaritalStatus = 'S'
 
THEN 'Single'
 
ELSE 'Married'  END AS  MaritalStatusDesc,
 
  
 
CASE WHEN  PayFrequency = '1'
 
THEN 'Monthly'
 
ELSE 'Bi-Monthly'  END AS  PayFrequencyDesc,
 
  
 
CASE WHEN  Status = 'Current'
 
THEN 'Active'
 
ELSE 'Inactive'  END AS  EmployeeStatus,
 
  
 
CASE WHEN  EndDate IS  NULL
 
THEN 'Active'
 
ELSE
 
DATENAME(mm, EndDate) + ' ' + DATENAME(dd, EndDate) + ', ' + DATENAME(yy, EndDate) END  AS SimpleEndDate, DATENAME(mm, StartDate) +  ' ' + DATENAME(dd, StartDate)
 
  + ', ' + DATENAME(yy, StartDate) AS  SimpleStartDate, DATENAME(mm, HireDate) + ' ' + DATENAME(dd, HireDate) + ', ' + DATENAME(yy, HireDate) AS SimpleHireDate,
 
  DATEPART(yy, HireDate) AS  HireYear, CASE  WHEN SalariedFlag = 0 THEN 'False'  ELSE 'True'  END AS  SalariedFlagDesc,
 
  
 
CASE WHEN  SalesPersonFlag = 0
 
THEN 'False'
 
ELSE 'True'  END AS  SalesPersonFlagDesc
 
  
 
FROM  DimEmployee

 

In this query we add id in full name

Output

Please see below image

After process of employee dimension  we can  browse dimension we can see id and name together.

Please see below image