Introduction to Metrics in One Model

Metrics are at the core of everything in One Model,  but what are Metrics, and why are they fundamental to People Analytics? This introduction explains what metrics are and where to find them. 

If you're already familiar with metrics in One Model and are looking to create your own metrics, then you should the review the article - How to Create Metrics. 

You may have heard the One Model platform being referred to as the “People Data Cloud”.  In the People Data Cloud, each customer has their own data model (in another context this might be referred to as a data warehouse, data lake or similar). 

The One Model People Data Cloud is purpose built for modeling data about people and creating an analytical data structure. During your One Model implementation, your data will undergo many processes, and one of those processes involves creating fact tables that form the basis of your metrics and dimension tables that are the foundation of dimensions.  You can read more about data processing in our article - Overview of Data Processing in One Model. 

Metrics are quantitative measurements such as the number of hires, percentage of retirement eligible workers, termination rate while Dimensions are attributes of your data or ways of describing your data such as work location, age, job family. Dimensions are primarily used for grouping and filtering metrics. 

For example - you can analyze a metric like “Termination Rate” by a dimension like Job Family to see which jobs have the highest level of turnover. 

Metrics are grouped into categories and subcategories such as;

• Compensation
• Diversity
• Headcount
• Recruiting

Categories and subcategories are customizable so it’s a good idea to have a basic understanding of the different types and kinds of metrics, their application, security restrictions / implications, and their relationship to dimensions and time. There is no limit to the number of Metrics you can create in One Model and, typically, the more data sources you connect, the more Metrics and Dimensions you will have. 

What are metrics?

In One Model, there are two primary types of metrics;

1. Table-based, and;
2. Calculated. 

There is a third type called Time-Based that is very similar to Table-based metrics. Time-based metrics use the dynamic time columns and work with all of the Time / Time periods dimensions. Time-Based metrics can be complex so they are the subject of their own dedicated article - Introduction to Time-Based Metrics. 

1. Table-based Metrics

Table-based metrics, also known as base metrics, are created from fact tables. Fact tables collate, where possible, data from different but related sources and splice (split out) this data onto a timeline. 

Each row in the table is a fact (data) relevant to the subject of the fact table, and each column is an attribute which may be used for:

• Metric creation, 
• Joining different tables (i.e. where the column is an id), 
• The basis for a dimension; or,  
• General reporting purposes. 

For Example:

The subject of the Employee table is workers, and each record in this table represents a fact about the workers. The values which aggregate into metrics are typically found in fact tables. 

To illustrate - as at 31 December 2023, in the above fact table, we can see 4 employees with a Full-time Equivalent (FTE) of 1 in their facts while one employee (13217)  has an FTE of 0.425. These values would aggregate into the FTE metric result of 4.425. 

Fact tables

In One Model, fact tables typically fall into two categories: 

1. Period-based; and,  

2. Event driven. 

1. Period-based fact tables (prefixed prd)

Period-based tables contain facts that span time. This can range from facts that span longer periods of time without a realized end date, eg. Employee A’s hire date is 1st January 2020 and they have not yet terminated to facts spanning shorter periods, eg. Employee B’s position was Store Manager from April 15th 2022 to October 14th 2022. 

As well as the prd prefix on these tables, they can be identified by the presence of an effective date (effdt) representing the start date for the fact and an end date (enddt) representing the end date for the fact. The date ‘9999-12-31’ is used where an end date is not yet known. 

These dates are used to determine where the fact appears on a timeline, beginning on the effective date and stopping on the end date. When an attribute changes, a new record (row) is created with the relevant effective date and end date which, in the above example of Workers, will result in multiple rows recording the different events. 

Metrics created from period-based fact tables should take into account how the metric rolls up over time since the metric value will apply across a time span and simply summing the metric can have unintended results. Summing the metric AND filtering by time, such as with end of period (EOP) or start of period (SOP) will avoid this.

Consider the following example:

Employee A with 1 FTE will have 365 ‘facts’ per year from their hire date 1st January 2020 for the FTE column in the prd_employee fact table. A One Model query for the year 2022 using an FTE metric with no time filter will sum every employees’ facts for the year 2022 (= up to 365 x number of employees’ FTE value). That’s going to give you an incorrect FTE. For the correct FTE, run a One Model query using an FTE metric with an End of Period time filter which will sum every employee’s facts for 31 December 2022 (= 1 x number of employees’ FTE value). 

Here's a sneak peek at the metric setup for a table-based metric from a period-based fact table. 

You can read more information about creating a metric in this article - How to create metrics.   

2. Event Driven Fact Tables (prefixed evt)

Event driven tables contain facts for specific occurrences (events) such as employee start date, end date, requisition or position changes. Events occur on a specific date, such as;

• Hires
• Terminations
• Promotions
• Transfers

Event facts will also include data relevant to the event, eg: the type of event, the reason for the event. Event driven tables can be identified via the use of (evt_ ) at the beginning of the fact table name and will contain a single effective date (effdt) with no end date. This date determines where on the timeline the event will appear. 

Metrics created from event driven tables can often use simple aggregations (eg: sum, count) since the event fact does not span time. 

Going back to the example above- our Employee A with 365 ‘facts’ per year for FTE in the prd_employee table, will have perhaps only a few ‘facts’ per year in the evt_event table, e.g. a hire on 1 January 2020, a promotion on 1 March 2022, a transfer on 15 August 2022, then a termination on 19 October 2022. 

Consider a query for the year 2022 using an “Events” metric with no time filter will sum every employees’ facts for the year 2022 (= 3 for Employee A and similar for other employees x number of employees). This query will give the intended results for an aggregate count of all event types.

Now compare that result with a query for the year 2022 using an “Events” metric with an End of Period time filter will sum every employee’s facts for 31 December 2022 (= 0 for Employee A and only events dated 31 December 2022 x number of employees). This is likely not the result you were aiming for. 

Note that as we bring in more and more events related to our employees, such as badging data, we will start seeing daily events and therefore daily facts in our events tables. This is where metric design and knowing your data will assist in delivering the right metric results. Similarly, applying a filter by “Event Type” in the “Events” example above, is likely to give you more commonly used metrics like “Hires” and “Voluntary Terminations”.

Here's a sneak peek at the metric setup for a table-based metric from an event driven fact table. 

A note regarding special period-based metrics

Job Tenure (EOP Average) and any other date and time related metric

This metric is an example of the DATEDIFF function being used to create a table-based metric. DATEDIFF is used to calculate, for example, an employee’s job tenure, age, or anything that is a quantification of time between two dates. This type of metric is considered a table-based metric as the DATEDIFF function can only be used at the fact table level, using data from within the same fact table.

For example, you can calculate the difference between an employee’s hire date and “today” (i.e. their job tenure), but you couldn’t calculate the difference between an employee’s hire date and first performance review date (unless that performance review date is incorporated into the employee fact table).

Finally, a sneak peek at a time-based metric setup. Learn more in our How to Create Metrics article.

2. Calculated metrics

Calculated metrics are where other metrics are used in the metric definition as part of a calculation, or a single table-based metric or column is used and then filtered upon.

Some examples include:

• % Female Headcount (EOP) = Female Headcount (EOP) / Headcount (EOP)
• Termination Rate = Terminations / Average Headcount
• Voluntary Terminations = Terminations with the filter Event Reason = Voluntary 
• Headcount (EOP) - Contingent = headcount (column from employee table) with the filters Worker Type = Contingent and Status = Active applied

The dimensions you can apply as filters to your metrics will need to be sourced from the same fact table, or have an existing join. There are also considerations on whether to use a column or metric as the basis for your calculation. 

Why would I use a column instead of a metric in my calculation?

Let’s take a look at the metric Headcount (eop) - Contingent. There are two main ways this metric could be created and represented to your audience. Outlined below are the main considerations per option.

Of the two options presented in this table above;

Option 1 Column + Metric Filter will give the user a better drill through experience while option 2 Metric + Metric Filter is quicker and easier during metric creation. Option 2 will also necessitate an additional click for your end user as well as additional metric permissions.

Watch this short 10 minute video which explains the difference: 

Where to find your metrics?

There are a few places to find your metrics, and the metrics available to you are subject to your role and permissions;

1. Explore tab,
2. The Define panel in Storyboards; and the,
3. Admin Reports.

Explore

All of your metrics are listed under the Explore tab then scroll down to the left-hand menu to view all of your metrics, dimensions, and columns (labelled as Additional Columns in Explore). You can also use the search bar to locate specific Metrics quickly without having to scroll through your lists. This view will respect your security settings and only provide access to metrics that your roles have been permissioned to see. Note, Admins or users with the permission to create/edit metrics will be able to view all Metrics in the Create/ Edit mode.

Define Panel in Storyboards

The Define panel in Storyboards is a quick and easy way to access your queries and metrics without leaving your Storyboard. The Define panel is the perfect go-to tool when you need to build a quick query or make a simple adjustment. If you need to create or edit the metric, then the Explore tab is where you will still need to go. 

Admin Reports

You can download a CSV file that lists all of the metrics in your instance from the Admin tab > Admin Reports > Metrics. 

How to name and identify your metrics?

As a new customer, you will be provided with a suite of metrics for your core data set using the standard One Model metric names, categories and sub-categories. However, we encourage our customers to implement their own naming conventions to align with their language for reporting and analytics. For example, “Terminations” can be updated to “Separations” or “Exits” or another name (by an Admin with the appropriate permissions).  

Each metric has an ID which is the unique combination of 5 numbers at the end of your browser’s URL (web address), e.g. company.onemodel.us/Metrics/Edit/16990. Whenever you create a metric, it will be issued with this unique ID that you can use as a way to navigate quickly to the details of a metric (e.g. formula, definition, filters and other details), or you should provide the unique URL as a helpful detail when troubleshooting within your own team or with the One Model team. 

Go to the next part - Introduction to Time Concepts in Metrics