Malam Payroll: Modelling Payroll, Attendance and BI Together

Malam Payroll sells payroll, attendance and HR as one suite on a shared database - the vendor names Malam Payroll, NeoPay, SAP Payroll, the TRM attendance system, Synerion Enterprise and HR-M - and markets that as a single source of truth. Reporting across the suite is straightforward. Reporting outside it is a modelling problem, not a connectivity one.

Three layers, three different grains

The suite covers three layers, and the reason a naive dashboard misleads is that each layer stores time at a different resolution.

Aggregating those three at their own grain and joining on employee and month gives correct answers. Joining raw punches to payslip components does not, because a punch has no money on it and a component has no day on it.

What question are you actually asking?

Almost every payroll dashboard request is one of four questions, and they need different measures:

• What did people cost us? Employer cost by month by cost centre - gross plus employer contributions and provisions, on the period the work belongs to.
• What did we pay out? Net transfers plus withholdings, on the date money moved. This is the one that ties to the bank.
• How many people do we have? Headcount or full-time equivalent at a point in time, which is an HR measure, not a payroll one.
• What did an hour cost on this project? Attendance hours joined to a cost rate, which is a derived number and needs a stated rule.

Mixing the first two in one chart is the single most common cause of a finance team refusing to trust a payroll dashboard. Decide which of the four each tile answers, label it on the tile, and keep the definitions in one document. The general version of that discipline is in connecting CRM and ERP data to BI.

Why do payroll numbers change after the month closes?

Because payroll legitimately restates the past. A retroactive pay-scale change, a late absence certificate, a correction to a previous month's hours or a backdated promotion all produce amounts in the current run that belong to an earlier month.

That behaviour has three consequences for the model, and all three are structural rather than cosmetic:

• A snapshot loaded once per month and never revisited will drift away from payroll's own numbers over the year.
• An append-only fact table is wrong unless every row carries both the payment period and the period the amount belongs to. Two date columns, not one.
• Any comparison to last year must state which version of last year it uses - as originally reported, or as it stands now.

The practical rule is to reload a rolling window - typically the current period plus several closed ones - on every run, and to store the run identifier on every row so a restatement is visible instead of mysterious.

A minimal model that survives contact with reality

Four tables carry almost everything and none of them require an API:

• Employee dimension, one row per employee per validity window, keyed by employing entity plus payroll employee number, with ת"ז held only if a downstream process genuinely requires it.
• Organisation dimension, cost centre and department with their own validity windows, because departments are renamed and merged and last year's report has to keep last year's structure.
• Payroll fact, one row per employee per component per period, with both the payment period and the earning period, plus the run identifier.
• Attendance fact, one row per employee per day per activity, with an approval status so provisional and approved data are distinguishable.

Two operational rules make the difference between a model that lasts and one that is rebuilt every year: never delete and re-insert a period silently, and never let a report read attendance rows that have not been approved without saying so on the report.

Where incentive pay and project hours complicate it

Malam publishes an incentive-pay module described as a monthly process in which departmental activity data is reported and combined with employees' attendance data to calculate a personal incentive premium. That is a useful example of a general pattern, because it means a number on the payslip is derived from a source outside both payroll and attendance.

Whenever that happens, three things need an owner:

• The input data itself - who produces the departmental activity figures and by when.
• The calculation rule, which is a business policy and changes without any system change.
• The audit trail, so an employee question a year later can be answered from stored inputs rather than from someone's memory of a spreadsheet.

Project-hour costing has the same shape. Attendance can carry a project code, but the rate applied to it is a decision, and a BI layer that invents the rate quietly becomes the system of record for something nobody agreed to.

What Malam publishes, and what you have to ask for

Malam's public pages are product pages. They describe capabilities, named systems and the unified-database architecture. They do not publish an API reference, an export record layout, a database schema or a warehouse connector, and none could be found elsewhere.

So the extraction conversation is the same one you would have with any Israeli payroll vendor:

• Ask which of the four routes is on offer - a scheduled report, a file drop, a vendor-built interface, or reporting inside the vendor's own tool.
• Ask for the layout as a versioned document.
• Ask specifically how retro rows are represented, because that answer determines your fact table.
• Ask who may receive the file, since it carries salary and identity data and the recipient list is part of the design, not an afterthought.

It is worth noting the contrast with one payroll output whose structure is public: employer reporting of pension deposits runs on the Capital Market Authority's uniform structure, an XML-based format defined in published circulars. A regulator-defined interface is documented; a vendor-defined one usually is not. The equivalent vendor-side conversation for an integrated bureau is described in getting payroll and attendance data out of Hilan.

Sources

• Malam Payroll - attendance reporting
• Malam Payroll - home
• Uniform structure for transferring information in the pension savings market - Capital Market Authority