The Relationship Between Data Entities in One Model

This article explains how the data joins together in your One Model instance. 

A One Model implementation will create fact and dimension tables from the data you are sending via API and/or flat files. These tables form the basis on which metrics and dimensions are created and can be found in the Additional Columns section of Explore. If any of these terms are unfamiliar, please check out the Cheat Sheet. 

Fact

prd_employee

evt_employee

evt_requisition

Dimension

dim_org_unit

dim_employment_status

dim_application_stage

You will have a much longer list of tables in your Additional Columns section of Explore - even if you have only implemented your core workforce data. And then, as you bring new data sources into One Model, the number of fact and dimension tables grows to align with the logical groupings of data, e.g. performance table and performance-related dimensions, learning table and learning dimensions, recruitment, surveys and so on. 

A key part of any new data source implementation is determining the relationship between the new and existing data. 

Let’s take Learning for example. 

• Does it make sense to join Learning and Employee data? Typically, yes. 
• Is there a common employee ID in your Learning system and HRIS that will allow a clean link between the datasets? Often a common Employee ID field is stored in your different HR systems, if not we can work with you to find a good way to join the data together.
• In what direction can we join the data? Because a single employee can have multiple, concurrent learning records we would join FROM Learning TO Employee (to avoid misreporting employee metric results due to multiplication). This join means you can split your learning data by all the existing employee dimensions, such as org unit, job level, org tenure, etc. 

If you weren’t involved in the data source implementation, it can be tricky to know if and how your fact and dimension tables are joined. So here’s a good place to start!

From Admin > Admin Reports, you can download an Entity Relationship Information report. This gives a full list of all From and To joins that exist in your data, including the field/s that are used to make the join work, and the full entity relationship as it is described in the processing script. 

For example, this company has over 200 joins, including these four:

This demonstrates:

• Join FROM Learning TO Employee
• Join FROM Employee TO Time Periods
• Join FROM Employee Events TO Event Reason dimension 
• Join FROM Employee Events TO Employee

Points to note:

• Every fact table will join to ‘timeperiods’ and other time specific dimensions. 
• The single join from the Employee Events table to the Employee table gives access to all employee related dimensions. This is demonstrated visually in the below data map. Notice the only “DIM” tables joined to the Employee Event table are specific event dimensions. We don’t need to make joins to all the Employee “DIM” tables. 

Why do Entity Relationships matter?

Have you ever created a query in Explore, clicked on 'Run Query', and no dimension results show up? Something like the image below. There is no error, but why is it just showing my aggregate headcount?

In this case, you are trying to join a metric FROM the Employee table TO an Employee Event dimension. It won’t calculate because the join is not in that direction. The headcount figure would count all the events for all employees in the year - which is not what you want as your end of period Headcount value.

Now compare to either of the below examples.

A metric and dimension from the same table are naturally going to work. 

And a metric FROM the Employee Event table TO an Employee dimension will also work because that is aligned with the direction of the join. 

In the case where you think a join should exist between data tables to get to the data you need, but it’s not listed in your Entity Relationship Information, it’s time to chat with your Customer Success Lead to explore your options.