When PMS reports are not enough: exports, dashboards and BI tools for hotels

Hotel business intelligence is the layer that turns data from several systems into one consistent set of numbers. You need it when built-in PMS reports cannot answer questions that cross properties, systems or periods. The usual path runs from scheduled exports into one spreadsheet model, to a dashboard tool, to a data warehouse, and each step depends on shared codes and agreed definitions.

Month end arrives and someone opens four spreadsheets, two PMS exports and a hand-kept file for the restaurant. The totals disagree, and nobody in the room can say which one is right. This article walks through the steps between that room and a reliable set of numbers: how to recognise the limit of built-in reports, how to build a scheduled export into one spreadsheet model, what a dashboard tool adds, what a data warehouse is, and what every one of these steps needs underneath it. After reading, you should be able to judge which step your business actually needs, and what to fix before spending money on any of them.

How do you know you have outgrown your PMS reports?#

You have outgrown them when the questions you ask most often need data the PMS does not hold in one place: a second property, the restaurant system, the channel manager, or three years of history.

Built-in reports are built for a defined job: night audit, the housekeeping list, the city ledger, the arrival list. They are repeatable and correct for that job, and that is exactly their limit. They are not wrong; they are finished.

The clearest sign of the limit is re-keying. Someone copies PMS figures into a spreadsheet every week or every month, and over time the copy becomes the number the owner actually reads. The second sign is disagreement: two people produce different values for the same figure, usually because one includes a segment the other leaves out. The third sign is scope: you cannot put two properties on one page, or you need last year's detail that the system keeps only for the current period.

Before concluding anything, separate a reporting gap from a definition gap. If two reports give different RevPAR figures, a new tool will not fix it; a written definition will. (If the formulas themselves are unclear, start with RevPAR, ADR and occupancy rate: formulas and common mistakes.) Also separate a daily need from an analytical need: a general manager's morning page and an owner's monthly trend across properties are different products with different owners, as covered in what a GM's morning page should contain.

The spreadsheet route is legitimate and cheap, but it breaks silently. When the person who built it leaves, nobody knows which formula was load-bearing.

Step 1: scheduled exports and one spreadsheet model#

A scheduled export writes the same report, at the same time, in the same format, into a folder, so the spreadsheet reads a file instead of a person copying numbers.

Ask the PMS for a saved report or export with fixed columns, a fixed date rule, for example the business day closed by night audit, and a delivery time after night audit has run. The point is that the file arrives on its own; nobody has to remember to pull it.

Use one workbook, not many. Keep a raw tab that nobody edits, a mapping tab that translates codes into readable names, and a report tab that only reads the other two. Write the definitions into a tab: what counts as a room night, which deductions turn gross into net revenue, which segments roll into which group. Date and version every file, and keep the export files themselves, not only the workbook, so a past month can be rebuilt.

Expect the known failure modes: a column added to the export, a renamed segment code, a property that changes its business-day cut-off, a person on leave. The effort is low and the payoff is real, because the exercise forces the definitions conversation early, before any tool is bought.

Step 2: a dashboard tool on top of exports#

A dashboard tool connects to the same exports and draws the numbers as charts and filters, so a manager can look without opening a spreadsheet.

What changes is refresh and access, not meaning. The tool inherits every mapping error in the export, and it will show the error faster and to more people. Check what the tool actually reads: the export files or a database. A tool pointed at a folder of files needs a person to notice when a file is missing.

Ask whether one figure can be shown two ways, per property and per group, without duplicating the calculation in two places. Permissions are a question, not an assumption: an owner of one property should not see another owner's numbers unless the group has decided otherwise, and row-level access must be tested with a real account, not a demonstration. A mobile view matters too; a chart that needs a laptop is opened once and then abandoned.

Where it breaks: dashboards multiply, each with its own definition, nobody owns the refresh, and one stale number in one meeting ends the project's credibility. When it is enough: if all your data still comes from one PMS and one export, a dashboard on exports is a reasonable end state, not a stepping stone. If you want to go further toward asking plain-language questions of your own hotel data, the same definitions still apply underneath.

What is a data warehouse, in plain words?#

A data warehouse is a separate database that copies data from your PMS, restaurant system, channel manager and accounting, cleans it into shared definitions, and keeps the history, so reporting tools read from one place instead of five.

It is a copy, not a live view. Data is loaded on a schedule, nightly is common, and the warehouse is never the system of record and never edited by hand. In plain words it has three layers: the raw copy as received, the cleaned layer where codes are mapped and duplicates removed, and the reporting layer that holds the numbers a manager sees.

History is the real gain. The warehouse keeps what source systems overwrite or drop, which is what makes multi-year trend questions possible at all. What it needs: a stable key for each property, room type, rate code and segment; a load schedule; a person who checks the load actually ran; definitions written down. What it costs: real money and real maintenance. It earns its place when several systems and several properties must agree, not for one hotel with one PMS.

Data separation between owners is a design decision: separate schemas or separate warehouses per ownership group, with permissions tested rather than assumed.

What every step needs: consistent codes, agreed definitions and one owner#

Every step up, from a spreadsheet to a warehouse, depends on the same three things: the same code means the same thing everywhere, one written definition per figure, and one named person responsible for the refresh.

Codes that must agree: market segment, rate code, revenue code, outlet, department and property. If the PMS says one thing and the channel manager says another, the join fails and the total is quietly wrong. Definitions belong on one page: room nights, the occupancy denominator and what counts as an available room, ADR, RevPAR, net versus gross revenue, and when a cancellation leaves the numbers.

An owner is a named person with a schedule and a fallback, not a department. When the refresh fails, that person is called. Change control matters as much: when a new segment or outlet is opened, the mapping table is updated the same week, by the same owner. Reconciliation is the safety net: each month, the dashboard or warehouse total must equal the PMS trial balance or night audit total. An unexplained difference is a stop signal, not a rounding note.

Group reporting respects ownership: each owner sees their own property, and the combined view is a separate permission granted deliberately. For the wider governance questions around multi-property groups, see the multi-property governance checklist for owners.

Should you fix the source systems before buying a reporting tool?#

Yes. Clean the codes in the systems that produce the data first, because every layer above copies them faithfully, and a warehouse will copy the mistakes faster.

Audit the segment list: how many segments exist, how many are actually used, how many are duplicates or a catch-all that hides the answer. Do the same for rate codes and revenue codes. A rate code should describe what the booking is, such as board, channel or package, not who typed it that day. Map outlets and departments, restaurant, bar, spa, minibar, laundry, to the revenue lines you want to read, and delete the ones nobody uses.

Remember that financial records are voided and reversed, never deleted, so historic data will always contain corrections. The reporting layer must handle them rather than average them away. Give one person ownership of the code list, with a written rule for adding a code and a reason required.

What should you ask a BI provider before you sign?#

Ask how the numbers are defined, who owns the refresh, and what happens when a source system changes, because those three answers decide whether the project is still trusted a year later.

What to do next#

Before you compare tools, write two pages: one page of definitions for the figures you report, and one list of the codes your systems use today. Then run the cheapest step for a month, a scheduled export into one three-tab workbook, and see which questions it still cannot answer. Those remaining questions, not a vendor demonstration, tell you whether you need a dashboard or a warehouse.