Turning hotel front-office data into management reports over a read-only connection

A hotel's most valuable data lives in the database of its front-office software (the PMS): reservations, stays, folios, agency accounts, payments. Management, however, usually sees that data through hand-made Excel files. Every morning someone exports a report, stitches a few sheets together, works out the exchange-rate effect by hand and emails the result to the general manager. That routine is slow and error-prone, which is why two departments so often answer the same question with two different numbers.

In this article we describe the approach behind the reporting layer we built for a resort hotel. The core principle is simple: never write anything to the hotel software, never put load on it during the day, and always be able to explain where each number comes from. The product side is on the Hotel Automation Package page; here we focus on the architectural decisions and on working code.

Why a read-only connection?

The PMS is the operational heart of the hotel. Every row written while reception checks a guest in or the cashier closes a folio is input for another process. If a reporting tool has write access to that database, even a well-meant debugging command can disrupt live operations. On top of that, the PMS vendor's support agreement often explicitly excludes third-party writes to the database.

So we create a separate database user for the reporting layer and give it read access only. In SQL Server the simplest way is to add the user to the db_datareader role. If you want a narrower scope, grant SELECT on a specific schema or on individual tables instead:

CREATE LOGIN report_reader WITH PASSWORD = 'a-long-random-password';
GO
USE HotelPms;
CREATE USER report_reader FOR LOGIN report_reader;
-- Option 1: can read every table in the database
ALTER ROLE db_datareader ADD MEMBER report_reader;
-- Option 2: one schema only
-- GRANT SELECT ON SCHEMA::dbo TO report_reader;
-- Explicit write denial (also blocks rights inherited from other roles)
ALTER ROLE db_denydatawriter ADD MEMBER report_reader;

Membership in db_denydatawriter makes sure the user cannot run INSERT, UPDATE or DELETE on tables even if someone later adds it to a role that carries write permissions by mistake (provided the user is never given sysadmin or db_owner, where denials do not hold). It is a single line, but it backs the words "read-only" with a guarantee at the database level.

What ApplicationIntent=ReadOnly does and does not do

Adding ApplicationIntent=ReadOnly to the connection string is frequently recommended and frequently misunderstood. It does not make the connection read-only. It only matters in SQL Server Always On availability groups: when you connect through the listener and read-only routing is configured, the connection is routed to a readable secondary replica. On a single server it has no effect at all. The real protection is the permission setup above; this setting is only useful for moving load away from the primary when your infrastructure actually has a readable secondary.

Safe, parameterised queries from Python

We write the reporting layer in Python and connect to SQL Server through pyodbc. Two habits matter. Never concatenate values into the query text; always pass them as parameters with the ? placeholder. And always set a query timeout, so that an unexpectedly heavy query cannot lock up the PMS during the day.

import os
import pyodbc

def connect() -> pyodbc.Connection:
    conn_str = (
        "DRIVER={ODBC Driver 18 for SQL Server};"
        f"SERVER={os.environ['PMS_SERVER']};DATABASE={os.environ['PMS_DB']};"
        f"UID={os.environ['PMS_USER']};PWD={os.environ['PMS_PASSWORD']};"
        "Encrypt=yes;TrustServerCertificate=no;"
    )
    con = pyodbc.connect(conn_str, timeout=15)   # login timeout (s)
    con.timeout = 120                             # query timeout (s)
    return con

def stays(con, start: str, end: str) -> list[tuple]:
    sql = (
        "SELECT reservation_no, guest, arrival, departure, room_type, agency_code, amount, currency "
        "FROM dbo.Stay WHERE arrival >= ? AND arrival < ?"
    )
    return con.cursor().execute(sql, start, end).fetchall()

In the connection string, Encrypt=yes encrypts the traffic and TrustServerCertificate=no requires the server certificate to be validated. If the database on the internal network uses a self-signed certificate, switching this to yes temporarily will let you connect, but it turns certificate validation off and leaves the connection open to a man-in-the-middle; the proper fix is to install a certificate on the server that the client trusts.

Table and column names differ in every PMS; the ones above are illustrative. That is also the longest part of any installation: building the hotel's data dictionary and confirming, together with the front office and accounting teams, what each code actually means.

Using the PMS's own stored procedures

For critical figures such as revenue we prefer calling the stored procedures the PMS uses for its own reports rather than writing our own SQL. The reason is consistency. The departmental revenue shown on the PMS screen is calculated with specific rules: cancellations, refunds, splitting package prices, taxes. Trying to reproduce those rules in our own query leads to management seeing two different numbers on two screens. When we call the same procedure, the report and the PMS screen agree; if they ever differ, the cause is in data entry, not in our layer.

There is an important security detail here. To run a procedure, the user needs EXECUTE permission. Because of ownership chaining in SQL Server, a procedure that writes to tables owned by the same owner can perform those writes even when the caller has no write permission of its own. That is why we never grant EXECUTE on a whole schema; we grant it one by one, only on reporting procedures whose code we have read and confirmed to be read-only.

GRANT EXECUTE ON OBJECT::dbo.ReportDepartmentRevenue TO report_reader;

def department_revenue(con, day: str) -> list[tuple]:
    return con.cursor().execute("{CALL dbo.ReportDepartmentRevenue (?)}", day).fetchall()

A nightly snapshot: no load on the PMS during the day

Management reports are opened many times a day. Sending large queries to the PMS every time could slow the system down during reception's busiest hours. Instead, every night, after the hotel's end-of-day routine has finished, we read the data we need once and write it to the reporting layer's own SQLite database. Every report opened during the day is served from that copy; the PMS only sees a single read at night.

import sqlite3

def take_snapshot(pms, local_path: str, start: str, end: str) -> int:
    rows = stays(pms, start, end)
    with sqlite3.connect(local_path) as local:
        local.execute(
            "CREATE TABLE IF NOT EXISTS stay ("
            "reservation_no TEXT, guest TEXT, arrival TEXT, departure TEXT, room_type TEXT, "
            "agency_code TEXT, amount REAL, currency TEXT)"
        )
        local.execute("DELETE FROM stay WHERE arrival >= ? AND arrival < ?", (start, end))
        local.executemany(
            "INSERT INTO stay VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            [(r[0], r[1], str(r[2]), str(r[3]), r[4], r[5], float(r[6]), r[7]) for r in rows],
        )
    return len(rows)

Because the delete and the insert run in the same transaction, an interrupted transfer does not wipe out the old data: the with block rolls the transaction back on error. The snapshot has a price, too: figures during the day reflect yesterday's close. We state that plainly in the report header. The few indicators that genuinely need to be live, such as today's arrivals and departures list, are read directly with small, parameterised queries.

An exchange-rate cache

In tourism, revenue is mostly in foreign currency while a large share of costs is in Turkish lira. To keep reports comparable, it must be clear which day's rate was used to convert each amount. Fetching the rate from an external service every time a report opens is slow and breaks the report whenever that service is unreachable. So we store daily rates in our own database and, if there is no rate for a date, fall back to the nearest earlier business day. Weekends and public holidays have no published rate, so this fallback is necessary, not optional.

from datetime import date, timedelta

def rate(local: sqlite3.Connection, day: date, currency: str) -> float:
    for back in range(10):                       # look back at most 10 days
        d = (day - timedelta(days=back)).isoformat()
        row = local.execute(
            "SELECT buying FROM fx_rate WHERE day = ? AND currency = ?", (d, currency)
        ).fetchone()
        if row:
            return row[0]
    raise LookupError(f"no {currency} rate found for {day}")

The rates themselves are fetched once by the nightly job from the official source and written into that table. If something goes wrong, the report does not silently continue with a wrong rate: a missing rate raises a clear error, and the nightly job's alert picks it up.

Exception robots: find, flag, never write

One step beyond reporting is finding, automatically, the inconsistencies a human eye can miss. We call these "robots", but their job is modest: they run defined rules over the nightly snapshot and write records that break a rule into an exception queue. Typical rules: two reservations for the same guest, arrival date and room type; an agency balance that is past due but still unpaid; a stay that has checked out while its folio is still open.

The key principle is that robots never write to the PMS and never correct a record. They only find and flag. The correction is made by an authorised person, on the PMS's own screen. That way, even a badly written rule produces at worst an unnecessary warning, never a damaged record.

RULES = {
    "duplicate_reservation": """
        SELECT 'duplicate_reservation', MIN(reservation_no) || ',' || MAX(reservation_no)
        FROM stay
        GROUP BY guest, arrival, room_type HAVING COUNT(*) > 1
    """,
    "overdue_receivable": """
        SELECT 'overdue_receivable', agency_code
        FROM agency_balance
        WHERE due_date < DATE('now') AND balance > 0
    """,
}

def run_robots(local: sqlite3.Connection) -> int:
    local.execute(
        "CREATE TABLE IF NOT EXISTS exception (rule TEXT, item_key TEXT, "
        "found TEXT DEFAULT CURRENT_TIMESTAMP, status TEXT DEFAULT 'open', "
        "UNIQUE (rule, item_key))"
    )
    before = local.total_changes
    for sql in RULES.values():
        local.executemany(
            "INSERT OR IGNORE INTO exception (rule, item_key) VALUES (?, ?)",
            local.execute(sql).fetchall(),
        )
    local.commit()
    return local.total_changes - before

The agency_balance table in the example is another snapshot table filled by the nightly job, just like stay. The UNIQUE (rule, item_key) constraint stops the same exception from being reopened every night; until someone closes it, it appears in the queue exactly once. From the queue screen you can drill down from each exception to the underlying reservation, and a daily email summary lists only the newly opened ones.

Excel, PDF and bilingual reports

However good the management screen is, accounting and the board want Excel. So the on-screen table of every report and its Excel export are produced from the same data structure; there is no separate calculation layer between the number on screen and the number in the file. Where formulas are needed (totals, ratios) we write real Excel formulas instead of values, so whoever receives the file can see where a number comes from.

from openpyxl import Workbook

LABELS = {
    "tr": {"agency": "Acenta", "nights": "Geceleme", "revenue": "Gelir (EUR)", "total": "Toplam"},
    "en": {"agency": "Agency", "nights": "Room nights", "revenue": "Revenue (EUR)", "total": "Total"},
}

def agency_excel(rows: list[tuple], lang: str, path: str) -> None:
    lb = LABELS[lang]
    wb = Workbook()
    ws = wb.active
    ws.append([lb["agency"], lb["nights"], lb["revenue"]])
    for row in rows:
        ws.append(list(row))
    last = ws.max_row
    ws.append([lb["total"], f"=SUM(B2:B{last})", f"=SUM(C2:C{last})"])
    wb.save(path)

In hotels, the management team and the group head office often work in different languages. Keeping labels in a single dictionary and passing a language parameter to report generation produces Turkish and English versions of the same report with no extra effort. The PDF output uses the same dictionary, so between the two languages only the labels change, never the numbers.

The order we follow when installing

1. Define the read-only user and network access together with the hotel's IT contact.
2. Build the data dictionary: which table, which code and which status value means what.
3. Put the first report next to the PMS's own screen and close the differences line by line.
4. Switch on the nightly snapshot and the exchange-rate table.
5. Add exception rules one at a time, each approved by the head of the relevant department.

Step three takes the longest and must not be skipped. Management only trusts a new report once it shows the same number as the screen they are used to.

Conclusion

You do not need to replace the PMS, or write to it, to get trustworthy management reports out of hotel data. A reading user restricted at the database level, procedures that carry the PMS's own calculation rules, a snapshot taken at night, stored exchange rates and exception robots that only flag: together they give management numbers that speak the same language every morning, without putting operations at risk. If you would like to discuss what is possible with your own hotel's data, write to us through our contact page.