HR Analytics Data Modeling in Infor Birst
Accurate HR analytics in Infor Birst depends on modeling decisions made before the first dashboard: the grain of each workforce fact, how effectively-dated history is captured, how organization and reporting hierarchies are built, and how row-level security follows the org structure. Get those four right and headcount, turnover and FTE stay consistent across every report. Get them wrong and every dashboard needs a footnote.
Infor Birst is Infor’s cloud analytics platform, which extracts, models and stores application data in a user data store for reporting. This guide is for human resources information system (HRIS) leads, people analytics managers and business intelligence (BI) architects running Infor HR Talent, Infor HR Payroll or Infor Workforce Management. It draws on what our Infor Birst implementation practice sees when HR models are built, audited and rebuilt.
Each section covers one decision, the Infor capability behind it and the trade-offs we weigh. Full-time equivalent (FTE), headcount and turnover are used in their standard HR sense throughout.
Why Workforce Data Is Harder to Model Than Finance or Sales Data
HR data is harder to model because one employee record changes meaning over time, often retroactively, and is sensitive at both row and column level. A sales order is created once and rarely rewritten. An employee’s assignment, department, pay rate and status are effective-dated, so the same person looks different depending on the date you ask about.
Five traits cause most of the trouble. Records are effective-dated, so history lives in date ranges rather than transaction timestamps. Changes are often retroactive, such as a termination entered on the 10th but effective on the 1st. Employees can hold concurrent assignments or positions, organization hierarchies are ragged, and compensation and personal data must be hidden from most users.
The business cost shows up at board level. A headcount figure in the board pack does not reconcile with payroll, and nobody can explain the gap quickly. A second-quarter turnover rate changes between the July run and the October run because late terminations landed in between. Once that happens twice, leaders go back to spreadsheets.
How HR Data Moves From Infor HR Talent Into Birst
In the Infor multi-tenant cloud, HR data reaches Birst through Infor Data Lake rather than through direct database extracts. For most customers, HR Talent and HR Payroll sit inside a wider Infor CloudSuite application estate that also feeds finance and supply chain analytics. Infor Data Lake is the component of Infor OS that stores replicated application data, and Birst is integrated with it.
Infor describes HR Talent Analytics as the content of the Infor Birst package used to collect data from Infor HR Talent and HR Payroll. Replication sets, a Landmark tool, pull HR Talent data into Infor Data Lake, where Birst can use it.
Per Infor’s guide to replicating and loading HR Talent data into Birst, registering updates to the Data Catalog and Compass object definitions sends replication sets to the Data Lake. A full load replicates everything and reloads it into Birst, used at the initial load or for a purge and reload. An incremental load replicates only updates after that initial full load and can be scheduled.
Infor Process Automation (IPA) is Infor’s workflow engine for Landmark applications. Starting with the 2023.10 feature release, HR Talent includes the Data Flow to Analytics Full Load and Incremental Load IPA workflows, which run replication and Birst orchestration together. Before running them, IPA setup must be complete in the tenant. Any scheduled Birst orchestration must also be turned off, a check we put in every go-live runbook.
Infor’s IPA workflows also update ION schemas, so the Infor ION layer is part of the HR pipeline, not a side concern. When replication delivers fewer records than HR expects, we review the ION connection points and data flows before touching the Birst model.
Inside Birst Modeler, the Infor Data Lake connector extracts Data Lake objects using Compass, the Data Lake query capability. It requires an Administrator account in Birst. Infor notes that Compass is subject to Amazon Web Services (AWS) service limits and that a query may time out after 60 minutes. Infor recommends incremental or highly optimized queries.
Not every HR source lives in Infor. Birst Connectivity Framework tools can bring in legacy HRIS, applicant tracking or survey data: Birst Connect uploads data from behind your firewall, and Live Access queries local data marts without uploading them first.
Figure: Pipeline from Infor HR Talent and HR Payroll through replication sets, Data Catalog registration and Infor Data Lake, then the Data Flow to Analytics IPA workflows and Birst orchestration into snapshot, event and assignment facts, with non-Infor sources joining through Birst Connect and Live Access. Alt text: Left-to-right architecture diagram showing Infor HR Talent data replicated to Infor Data Lake, loaded into Infor Birst by IPA workflows, and modeled into workforce facts alongside external HR sources.
Choosing the Grain for Each Workforce Fact
Grain is the most important HR modeling decision, because it defines exactly what one fact row represents and what every measure can be summed against. As Infor’s documentation on defining the grain of a data source explains, Birst declares grain as a combination of hierarchy levels. The combined level keys must form a unique key. If they do not, Birst still loads the data but logs an error in the load log, and a non-unique grain key can cause a Cartesian join.
Birst also adds an implicit time level to the grain of any source that contains measures. For a snapshot fact, that makes the effective grain employee or assignment plus snapshot date. Design the date key deliberately rather than leaving it to whatever date column happens to be on the source.
| Fact type | Grain | Typical measures | Strengths | When to use it |
|---|---|---|---|---|
| Workforce snapshot fact | One row per employee or assignment per snapshot date | Headcount, FTE, active flag, tenure days | Fast point-in-time reporting and simple trend lines | Month-end or daily headcount, FTE and workforce composition |
| Workforce event fact | One row per hire, termination, transfer, promotion or pay change | Hires, terminations, transfers, promotions, pay change amount | Additive counts with reason codes and a clear audit of movement | Turnover, internal mobility and hiring volume |
| Effective-dated assignment fact | One row per assignment per effective date range | Assignment FTE, standard hours, pay rate | Reconstructs status on any date and stays closest to the source | History backbone that feeds snapshots and as-of queries |
Most HR models need the snapshot fact and the event fact together, with the effective-dated assignment table kept as a history layer behind them. Snapshots answer how many people were in a state on a date; events answer what moved during a period. Turnover needs both, terminations from one and average headcount from the other. Forcing both questions onto one fact usually breaks one of them.
Capturing Workforce History With Snapshots and Refresh Policies
History in a Birst HR model is governed by each source’s snapshot and transaction policy, so that setting is a history decision, not a performance tweak. Birst lets you set snapshot and transaction policies per source in Modeler Relate under Source Properties. There are four options:
- Snapshot every time the source is published, which keeps a copy of the measure table on each publish.
- Schedule snapshot, which captures data on a daily, weekly or monthly schedule.
- Full refresh, which replaces all rows with the last upload.
- Incremental refresh, which keeps existing rows and adds new ones, optionally with delete keys so Birst can replace older versions of the same record.
Month-end headcount suits a monthly scheduled snapshot, giving one frozen picture per period. Daily active workforce suits a daily snapshot, with a retention rule agreed early because row counts grow quickly. Event facts with retroactive corrections suit incremental refresh with delete keys on the event identifier, so a corrected transfer replaces the original rather than duplicating it. Reference data such as job codes can use full refresh.
Headcount and FTE are semi-additive: they sum across departments and locations but not across time. Twelve monthly snapshots added together report twelve times the real workforce. Report them at the period end, use an average of period-end values for turnover denominators, and apply that rule to every time-based visual.
If the HR system stores only current records, snapshots begin the day you switch them on. Earlier periods have to be rebuilt from effective-dated history or loaded from legacy reports, which is a historical data migration with its own reconciliation rules.
Building Organization, Manager, Position and Time Hierarchies
HR hierarchies decide how every measure rolls up, and the key choice is between the structure as it is today and as it was at each event. In Birst, hierarchies are built from levels, and each level is assigned level keys that identify its records.
The organization unit hierarchy is usually ragged and stored parent-child in the source. We flatten it into fixed levels in a scripted source, padding short branches with the lowest real unit so every level key is populated. The reporting relationship, or manager, hierarchy is also parent-child and changes more often than the org structure; key it on assignment where managers hold more than one role.
Position and job hierarchies typically run from job family to job code to position, and location runs from country to state to site. Fiscal reporting needs a custom time hierarchy, which Birst supports in Advanced spaces, so periods match the payroll and finance calendar rather than calendar months.
The as-is view attaches all history to today’s structure, which answers how the teams a leader runs now have performed. The as-was view stores the org unit on each fact row at the event date, so last year’s turnover by department stays fixed after a reorganization. A department created this year shows full history as-is and none as-was. Most clients need both, exposed as two clearly named attributes.
Defining HR Measures That Reconcile Across Reports
HR measures reconcile only when each has one agreed definition, a known source fact and the correct aggregation rule.
| Measure | Source fact | Aggregation | Common calculation mistake |
|---|---|---|---|
| Headcount | Snapshot | Semi-additive at period end | Summing snapshots across months |
| FTE | Snapshot | Semi-additive at period end | Counting per person instead of per assignment |
| Hires | Event | Additive | Counting rehires or internal transfers as hires |
| Terminations | Event | Additive | Using entry date instead of effective date |
| Voluntary and involuntary turnover rate | Event and snapshot | Derived | Averaging monthly rates instead of dividing totals |
| Average tenure | Snapshot | Derived at a point in time | Including terminated employees in active tenure |
| Time to fill | Requisition event | Derived | Starting the clock at creation instead of approval without agreement |
| Absence rate | Workforce Management time | Derived | Using calendar days instead of scheduled hours as the denominator |
| Overtime hours | Workforce Management time | Additive | Double counting hours that also appear in payroll data |
Turnover rate definitions must be agreed with HR and finance before the build, including which termination reasons count as voluntary. The model should store the components, terminations and average headcount, rather than a precalculated rate. Rates then calculate correctly at any level of any hierarchy.
Absence and overtime usually come from Infor Workforce Management rather than HR Talent. When time and attendance data needs a different cadence or grain than the delivered content provides, Workforce Management API development is often cleaner than reshaping it inside Birst.
Securing People Data at the Row and Column Level
People data needs row-level filters that follow the organization structure and column-level separation that keeps compensation away from most users. Birst handles row-level control through security filters in the Space Management module. A Visibility Control table must exist before any filter is created.
Filters are either Variable type, based on an existing variable, or Custom type, based on a Birst Query Language (BQL) query returning a list of values. A Custom filter can match a mapping table to the logged-in user, for example SELECT [Mapping.Org_Unit_Code] FROM [ALL] WHERE [Mapping.Username]=GetVariable(‘USER’). Filters can apply to all sources or selected sources, and space groups such as HR administrators can be exempted.
Two limits shape the design. Filters created this way do not support networked BI scenarios, and the feature only works on spaces using the new metadata infrastructure. Settle both before designing a multi-space architecture.
Infor Workforce Management Analytics for Birst is a useful reference pattern. Access is based on Space Group membership, and custom subject areas expose focused sets of measures and attributes to permitted groups. Data-level filters then restrict rows by HR Organization, HR Organization Unit, Reporting Relationship and Recruiter.
Apply the same split in custom HR spaces. Keep compensation, performance ratings and personal attributes in a restricted subject area granted only to HR and finance groups. Row filters then control which people a user sees, and subject areas control what they see about them.
Pitfalls We See on HR Analytics Projects
Most HR models we are asked to repair fail on one of six recurring design gaps, each with a recognizable symptom.
Headcount Inflated Across Periods
The symptom is quarterly or annual headcount roughly three or twelve times what HR expects. The cause is snapshot rows summed across periods instead of read at the period end. The fix is to treat headcount and FTE as semi-additive, return the last snapshot in each period, and test every time aggregation.
Duplicated Rows From Concurrent Assignments
The symptom is employees appearing twice and inflated measures after joins. The cause is a grain keyed on employee when some people hold more than one concurrent assignment or position. The fix is to add assignment to the grain, choose a primary assignment flag for person counts, and clear grain errors from the load log.
Retroactive Changes That Never Reach the Model
The symptom is terminations, transfers or pay changes visible in HR Talent but missing or duplicated in Birst. The cause is incremental refresh without delete keys, so corrected rows are added beside originals or never replace them. The fix is delete keys on the business key, plus periodic full reloads of event facts.
Reorganizations That Rewrite History
The symptom is last year’s turnover by department changing after a reorganization. The cause is a model holding only the current hierarchy. The fix is to store the org unit on each event and snapshot row at the time it happened, alongside the current hierarchy.
Security Filters That Stop Working After a New Source
The symptom is users suddenly seeing rows outside their scope after a release. The cause is a new source targeting a dimension column already filtered, without the existing filter being re-saved, which Infor says is required. The fix is a release checklist step to re-save affected filters and retest by role.
Compass Timeouts on Full History Loads
The symptom is initial loads failing on large employment history tables. The cause is single Compass queries running past the 60-minute limit. The fix is to split the first extraction by date range or object, load history in batches, then switch to incremental queries.
How the Model Changes by Company Profile
The core design stays the same across company sizes, but the number of hierarchies, history layers and security tiers changes considerably.
A global multi-entity enterprise needs legal entity and HR Organization in the grain, both as-is and as-was hierarchies, and multi-currency compensation in a restricted subject area. Security usually starts at HR Organization and narrows to org unit. If several spaces are planned, the networked BI limit on security filters must be settled first.
A mid-market company with one HR system and a few sites can run a single space with snapshot and event facts, monthly snapshots and a manager-based filter. A simpler model is enough here, and as-was hierarchies can wait until the first real reorganization.
A growth-stage company in its first one to three years on Infor has little history in the source. Switch on scheduled snapshots from day one, start from the delivered HR Talent Analytics content, and load legacy history once rather than maintaining two reporting stacks.
Measuring Whether the Model Is Working
A working HR model passes the same small set of checks every period, and those checks should be owned by named people in HR and BI.
- Headcount at each period end reconciles to the payroll register within an agreed tolerance.
- The load log shows no grain errors after each processing run.
- Turnover rates for a closed period stay the same when that period is rerun.
- Security filters are tested by role after every release, using test users for each space group.
- HR business partners use the dashboards weekly, which depends on Birst visualizations designed around HR decisions rather than generic charts.
When a check fails, fix the model before building more dashboards on top of it. Each failed check maps back to one of the four decisions in the lead: grain, history, hierarchy or security.
If your current Birst space fails two or more of these checks, a model review is usually faster than another round of dashboard fixes. You can talk to our Birst team about an HR model review and get a scoped view of what to change first.
Frequently Asked Questions
What is the best grain for headcount reporting in Infor Birst?
Use one row per employee assignment per snapshot date in a dedicated snapshot fact. Birst adds the time level to the grain automatically, so the combination of assignment key and date must be unique. Read headcount at the period end rather than summing it.
How do I track historical headcount in Birst if the HR system only stores current records?
Enable a scheduled snapshot policy on the workforce source so Birst stores a copy on a daily, weekly or monthly schedule. History builds from the day snapshots begin. Earlier periods must be rebuilt from effective-dated records or loaded from legacy reports.
How do retroactive HR changes affect Birst reports?
Retroactive changes alter closed periods, so turnover and headcount can shift between runs. Use incremental refresh with delete keys on event facts so corrections replace original rows. Agree a policy on whether closed months are restated or frozen.
Can Birst restrict HR data so managers only see their own teams?
Yes. A Custom security filter can match a mapping of users to org units or reporting relationships against the logged-in user with GetVariable(‘USER’). Sensitive measures such as pay are best kept in a restricted subject area.
Does Infor provide prebuilt HR analytics content for Birst?
Yes. Infor describes HR Talent Analytics as the content of the Infor Birst package for collecting data from Infor HR Talent and HR Payroll. Most organizations start from that content and extend it with their own facts, hierarchies and security.