Use Aliases, Joins, Aggregate Functions in Database Lookup Query

The database lookup query supports the use of aliases, joins and aggregate functions etc. through the use of views. Following is a scenario to demonstrate the use of views to handle aliases, joins and aggregate functions.

Scenario

The administrator wishes to see the list of all the departments in the organization and also the number of employees in each department. Following is a snapshot of the Employee details and Department details of the organization.

Employee

Department

Achieving this scenario requires the use of joins, aliases and aggregate functions in the query as shown below:

SELECT DepartmentGroupKey AS DepartmentId, DepartmentGroupName AS DepartmentName, COUNT(EmployeeKey) AS [Number of Employees]

FROM DimDepartmentGroup INNER JOIN DimEmployee 

ON DepartmentName=DepartmentGroupName

GROUP BY DepartmentGroupKey,DepartmentGroupName;

Steps to achieve the scenario

Joins and aliases are supported in the database lookup query through the use of views. Follow the steps give below to achieve the above scenario:

1. Create a view in the database, say DepartmentEmpCount as shown below:

   CREATE VIEW DepartmentEmpCount

   AS

   SELECT DepartmentGroupKey AS DepartmentId, DepartmentGroupName AS DepartmentName, COUNT(EmployeeKey) AS [NumberOfEmployees]

   FROM DimDepartmentGroup INNER JOIN DimEmployee 

   ON DepartmentName=DepartmentGroupName

   GROUP BY DepartmentGroupKey,DepartmentGroupName;

2. Create a database lookup, DeptEmployeeCount as shown below. Enter the query in the Query field as "Select * From DepartmentEmpCount", where DepartmentEmpCount is the view name.

3. Click Validate & Preview. The count of employees for each department is displayed.