If you’re like me, you may find cardinality settings in UKG Cognos BI aka People Analytics less than intuitive. Even if you can do a SQL left outer join in your sleep. This post should help. It will help answer key reporting challenges, including:

Show me all active employees and their medical plans.

Show me all active employees and their highest level of education.

Show me all active employees and any bonus they received in March.

In each case, an active employee may not have any of the related data. An employee may not have a degree, a benefit plan or a bonus just because they’re getting a paycheck.

But, from a reporting perspective, you want to include all employees in each report. In SQL, these are all cases of “Left Outer Joins”. Many of the UKG People Analytics reports need this technique.

I’ll go through this step by step. The example combines employee and education data.

The first example will only include the highest level of education obtained by each employee.

Next, we’ll change the example to show all education levels. The number of records will change but the number of distinct employees won’t change. We’ll show you how to use Excel to prove that the number of employees doesn’t change. (There are ways of checking this within People Analytics/Cognos BI but this post was already quite long. I’ll be updating this with a link to another post.

Please note: This post is for you if you’re a report writer looking for results. If you want to dive into the deep meaning of cardinality, this post is not for you.

The Example – Employees and Their Highest Education Level.

My sample report has two base queries and a third query which joins those two together.

The base queries are:

10 – Employee with Job.

20 – Education – Highest Level

I then join these two queries in a third query:

40 – Employee, Job and Highest Level of Education.

I’ve numbered the queries for ease of reference in the output examples.

Each query only has one record per employee.

Let’s review the two queries

There are two things to note:

1. I filter for active employees.
2. I include EEID or Employee ID in the query.
   Employee Numbers are unique WITHIN companies but not across multiple companies. EEIDs are always unique across all your companies. This will allow us to create our link on a single column.

When I run this query, we receive 2047 records back.

I’ve filtered this query to deliver one record per employee by selecting the highest level. (As far as I’ve seen, there can only be one highest level. Of course, if someone had two PhDs, I’m not sure which we would be the highest. It’s nice to know that after all that education, the individual found employment. But I digress.)

This query delivers 4069 records back.

You may ask yourself: How did I get here? –Why are there more records in this result than in the first query?

The answer: We didn’t filter this for active employees only. Because of our cardinality settings,this won’t matter.

Your mileage may vary depending on your data.

Now, I combine the two queries into the final query.

Let’s look at the join:

Let’s review the cardinality settings.

Let’s start with the Left side – 10 – Employees with Job. The 1:1 Cardinality setting means that we expect every record in the 10 – Employee with Job query to appear in the final, joined query. For most purposes, including the cases mentioned above, you will ALWAYS set this value to 1..1.

Now, let’s review the right side – 20 – Education – Highest Level. The 0..1 setting means that the join query may or may not include any education information. And, for each employee there can be no more than 1 education record.

For reference, here are the fields in the joined query:

Let’s run the query to excel. You’ll see that the number of records matches the number of records in the 10 – Employees with Jobs. Not all employees have a highest level of education specified. You’ll also see that not all employees have education information.

And you’ll see that not all employees have education information.

Let’s now adjust our query so we can see all education levels.

Employees – All Levels of Education

Here are the queries we’ll be using:

I’ll still be using 10 – Employees with Job as our base query so I won’t review that again.

I’ve made slight adjustments to the other queries.

For this query, we’ve removed the filter on “highest level”. We can expect to receive multiple education records for some, but not all employees.

We are still joining on EEID.

The cardinality under 10 – Employees with Job is still 1..1. Again, this is almost always the setting you want.

On the right side, I’ve adjusted the cardinality to 0..n. This tells us that for each 10 – Employees with Job record the joined query may have 0, 1 or multiple records from 30 – Education – All Levels query.

As expected, we get more records—in this case, 2578.

As a simple check that we are still getting the same number of employees, we can use Excel.

We copy the column of employees to another sheet and remove duplicates. We get 2407.

 

Let us know if you have questions below. If you’d like the sample report, please contact us.