This article clarifies the distinctions between three types of "Unknown" values that appear when analyzing data in One Model, particularly with hierarchical dimensions like the Supervisory Chain: “Genuine Unknowns" stem from a missing ID for an employee and can be filtered out; "Orphans" are unfilterable unknowns caused by IDs in employee records that haven’t been mapped in the Dimension.
You may notice some "Unknown" values can't be filtered out - we're here to help you understand why.
Like many other customers, you likely have a need to examine data by organizations, layers, or other dimensions within your company. For instance, you may want to examine Headcount (EOP) by Supervisory Chain to better understand how your headcount is distributed across levels of management.
One Model can successfully build dimensions to help you slice the data in such a way. However, when you filter your view, you may find some "Unknown" values aren't able to be filtered out. On the surface, it's easy to be confused about why this is happening, as genuine "Unknown" values can be filtered out. The values which can't be filtered out, while labelled as "Unknown", actually represent true blanks, or blank values created when individual employee records are pivoted by multiple levels of the supervisory chain and similar dimensions. In this article, we'll focus on the Supervisory Chain to help explain the concept.
Genuine Unknowns
The first step is understanding what “Genuine Unknown" values are, or how data fields traditionally get mapped to "Unknown". For example, below you'll see a table with employee location data:
As you can see, Employee 3 is missing location data. In this particular case, Employee 3 has an Unknown location, and so they fall into the “Unknown” node at the top level, which can then be filtered out by excluding the Unknown node in the query.. If a proper "City/Town" value is inputted for Employee 3, the employee will map to the dimension normally and you will no longer see "Unknown" values.
Unfilterable Unknowns - “Orphans”
The primary cause for the Unfilterable Unknown is what are known in Data Warehousing as “Orphans”. These are cases where there is an ID associated with an employee that doesn’t currently exist in the Dimension Table. For example, if the wrong ID is given for an employee’s location, that doesn’t exist in the Location Dimension:
In this case, Employee3 has their Job Title put into the Location ID, likely as a result of a Data Entry error. This ID doesn’t exist in the Location dimension, so it defaults to returning an Unknown value.
The solution in this case is:
- Change the Dimension sourcing so that unexpected codes are automatically placed under “Unmapped”. This can be done by a Data Engineer working on the Processing Script
- Either correct the code given to the employee to a genuine location, or add the location to the data or mapping file that’s used to build the Dimension Structure. This can be done by either the Data Owner, or (in the case of a mapping file), a Data Engineer.
These orphans can be proactively found and addressed using the Datawarehouse Relationships section of the Site Validation page (found under Admin -> Site Validation.
If you have any questions on the content in this article, please reach out to the Customer Support team.
Comments
0 comments
Please sign in to leave a comment.