Blogs

Implementing ADF Branches and Filters for Employee Training

Inkey, March 22, 2024 450 Views

In one organization, there is one analysis of employee’s performance on different technologies based on the technologies they know and ratings from their manager. If they don’t meet the required criteria, then that employee will get training for those technologies.

Input:

emp_id	MSBI	PowerBI	ratings from the manager
101	null	x	3
101	null	null	1
101	x	null	5
102	x	x	4
102	x	null	3
103	x	null	4
103	x	x	3
104	null	null	0
105	null	x	4
105	x	null	5
105	null	x	2

 

Output:

emp_id	Needs improvement?
101	no
102	yes
103	yes
104	yes
105	no

 

• There are two technologies “MSBI” and “Power BI”.

If an employee has experience in Power BI technology, then the minimum required rating is 4.

If an employee has experience in MSBI technology, then the minimum required rating is 5.

• Null means that employee has not worked in that technology and “x” means that employee has worked in that technology.
• If an employee is not meeting the minimum rating criteria, then that employee needs an improvement and for a particular employee, if that employee needs an improvement in all cases, then that employee needs to be improved and will be trained.
• If Employee does not have worked in any of the technology, then we do not have to consider rating of that employee and that employee needs improvement.

Logical Implementation:

This is our input data.

emp_id	MSBI	PowerBI	ratings from the manager
101	null	x	3
101	null	null	1
101	x	null	5
102	x	x	4
102	x	null	3
103	x	null	4
103	x	x	3
104	null	null	0
105	null	x	4
105	x	null	5
105	null	x	2

 

Here, according to our conditions, for each case which employee needs improvement and which employee does not need is given below:

 

emp_id	MSBI	PowerBI	ratings from the manager	need improvement or not?
101	null	x	3	yes
101	null	null	1	yes
101	x	null	5	no
102	x	x	4	yes
102	x	null	3	yes
103	x	null	4	yes
103	x	x	3	yes
104	null	null	0	yes
105	null	x	4	no
105	x	null	5	no
105	null	x	2	yes

 

Now we do not need [MSBI], [PowerBI], [ratings from the manager] column as of now so we will only focus on [emp_id] and [need_improvement_or_not] column.

 

---

Discover the power of our services:

Microsoft Fabric, Power BI, Microsoft Business Intelligence, SQL Server, and Business Central. By the power of these services, from advanced analytics to seamless business integration, we’ve got the expertise you need to optimize operations and drive growth. Harness the potential of your data infrastructure with our comprehensive suite of solutions.

---

 

Please see the below diagram for the step-by-step logical implementation:

Step 1: Add values to the [Need Improvement or Not] column based on the specified conditions and input data.

Step 2: Filter the records with “Yes” in the [Need Improvement or Not] column and rename it as [Ref_Need_Improvement_or_Not]. Also, rename the [emp_id] column to [emp_id_ref] to avoid confusion between steps.

Step 3: Filter the records with “No” in the [Need Improvement or Not] column.

Step 4: Join the output from Step 2 with the output from Step 3 using the [emp_id] and [emp_id_ref] columns to obtain matching records.

Therefore, only the emp_ids with differing values in the [Need Improvement or Not] and [Ref_Need_Improvement_or_Not] columns will be retrieved.

Step 5: If the [Need Improvement or Not] column contains ‘no’ and the [Ref_Need_Improvement_or_Not] column contains ‘yes’, update the value in the [Need Improvement or Not] column to ‘no’. This update is necessary because an employee will get training only if improvement is needed in all cases.

Step 6: Perform a left join between the output from Step 1 and the output from Step 5. This will result in obtaining all the records from Step 1 and only the matching records from Step 5.

Step 7: Filter only those records where the [emp_id_join] column is null.

Step 6 & 7 is executed to include records that have ‘yes’ in the [Need Improvement or Not] column in all cases.

Step 8: Update the values in the [Need Improvement or Not] column to the corresponding values in the [Ref_Improvement_or_Not] column. This update will always result in ‘yes’ because we have filtered records with a null [emp_id_join] column.

Step 9: Union the output from Step 8 with the output from Step 5. This will yield records with both ‘yes’ and ‘no’ values in the [Need Improvement or Not] column.

Step 10: Remove duplicates from the result to obtain the desired output.

Implementation in ADF:

We will compare logical steps to Azure Data Factory (ADF) steps, step by step.

This is the Data flow in ADF of implementation.

Input data:

Step 1: We will introduce our condition and create a new column named [Need_Improvement_or_Not].

Output:

Step 2 :  Filter the records with “Yes” in the [Need Improvement or Not] column and rename it.

Output:

Step 3: Filter the records with “No” in the [Need Improvement or Not] column.

Output:

Step 4: Join the output from Step 2 with the output from Step 3 using the [emp_id] and [emp_id_ref] columns to obtain matching records.

Therefore, only the emp_ids with differing values in the [Need Improvement or Not] and [Ref_Need_Improvement_or_Not] columns will be retrieved.

Output:

Step 5: If the [Need Improvement or Not] column contains ‘no’ and the [Ref_Need_Improvement_or_Not] column contains ‘yes’, update the value in the [Need Improvement or Not] column to ‘no’ and rename the column. This update is necessary because an employee will get training only if improvement is needed in all cases.

Output:

Step 6: Perform a left join between the output from Step 1 and the output from Step 5 using the [emp_id] and [emp_id_ref] columns. This will result in obtaining all the records from Step 1 and only the matching records from Step 5.

Output:

Step 7: Filter only those records where the [emp_id_join] column is null.

Steps 6 & 7 are executed to include records that have ‘yes’ in the [Need Improvement or Not] column in all cases.

Output:

Step 8: Update the values in the [Need Improvement or Not] column to the corresponding values in the [Ref_Improvement_or_Not] column. This update will always result in ‘yes’ because we have filtered records with a null [emp_id_join] column.

Output:

Step 9: Union the output from Step 8 with the output from Step 5. This will yield records with both ‘yes’ and ‘no’ values in the [Need Improvement or Not] column.

Output:

Step 10: Remove duplicates from the result to obtain the desired output.

Output:

 

---

Know the number of records in any Dataverse entity or table.
https://www.inkeysolutions.com/entity-record-counter

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator