# Python Microsoft Fabric - Keeping lookup tables up to date

A scheduled service that copies small tables, such as a list of locations or products, from your systems into Fabric every night.

Most reports in Fabric show names, not IDs. A chart of orders per location needs the name of each location, and a chart of costs per employee needs the name of each employee. Those names live in your own systems, e.g. an HR database holds the list of employees and an ERP holds the list of products, and Fabric needs its own copy of each list to build the charts. Such a copy is called a lookup table, and it is small, from a few rows to a few thousand.

When a location is renamed or an employee leaves, the change happens in your system, and the copy in Fabric is out of date until someone updates it. This page writes one service that runs every night, reads each list from the system that has it and writes it to Fabric, replacing the old copy. The example copies three lists, locations, insurers and staff, and the same service works for any list your reports need.

> **What you need from your Fabric admin**
>
> The workspace ID and the lakehouse ID, both from the address bar when the lakehouse is open in Fabric. A workspace is the folder in Fabric where the tables live, and a lakehouse is the item in it that holds them. The [tutorial](https://zato.io/docs/dev/examples/cloud/fabric/tutorial.md) shows where to find both IDs.

## The tables before the refresh {#the-tables-before-the-refresh}

The three tables are in the lakehouse `Operations`, and each one is a copy of a list from a different system. Yesterday, employee `STF-104` left, and the HR system marks that person as inactive, but the `staff` table in Fabric still lists `STF-104` as active, because the copy was made before the change:

## The service {#the-service}

One service refreshes all three tables. For each one, it reads the current list from the system that has it and calls `write_table`, which replaces the table in Fabric with the rows given. In a real service, the three lists come from a database query or an API call. Below, they are written out in full so that the page shows what the rows look like.

```python
# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class RefreshLookupTables(Service):

    name = 'lookups.refresh-tables'

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

        conn = self.microsoft.fabric['My Fabric']

        # The list of locations, from the system that maintains it ..
        locations = [
            {'location_id': 'LOC-01', 'name': 'Riverside',   'city': 'Portland', 'state': 'OR', 'beds': 120},
            {'location_id': 'LOC-02', 'name': 'Oak Hill',    'city': 'Salem',    'state': 'OR', 'beds': 80},
            {'location_id': 'LOC-03', 'name': 'Maple Grove', 'city': 'Eugene',   'state': 'OR', 'beds': 60},
        ]

        # .. the list of insurers, from the billing system ..
        insurers = [
            {'insurer_id': 'INS-01', 'name': 'Cascade Health Plan'},
            {'insurer_id': 'INS-02', 'name': 'Pacific Mutual'},
            {'insurer_id': 'INS-03', 'name': 'Evergreen Assurance'},
        ]

        # .. and the list of staff, from the HR system, where STF-104 is now inactive ..
        staff = [
            {'staff_id': 'STF-101', 'location': 'Riverside',   'role': 'Scheduling',
                'cost_center': 'CC-100', 'active': True},
            {'staff_id': 'STF-102', 'location': 'Riverside',   'role': 'Front desk',
                'cost_center': 'CC-200', 'active': True},
            {'staff_id': 'STF-103', 'location': 'Oak Hill',    'role': 'Scheduling',
                'cost_center': 'CC-100', 'active': True},
            {'staff_id': 'STF-104', 'location': 'Oak Hill',    'role': 'Billing',
                'cost_center': 'CC-300', 'active': False},
            {'staff_id': 'STF-105', 'location': 'Maple Grove', 'role': 'Scheduling',
                'cost_center': 'CC-100', 'active': True},
        ]

        # .. replace each table in Fabric with its list, write_table replaces by default ..
        tables = {
            'locations': locations,
            'insurers': insurers,
            'staff': staff,
        }

        counts = {}

        for table_name, rows in tables.items():
            conn.write_table(workspace_id, lakehouse_id, table_name, rows)
            counts[table_name] = len(rows)

        # .. and report how many rows each one has now.
        self.response.payload = counts
```

The response after a run is the number of rows in each table:

```json
{"locations": 3, "insurers": 3, "staff": 5}
```

After the run, the `staff` table in Fabric lists `STF-104` as inactive, the same as the HR system does.

## Scheduling it {#scheduling-it}

The service has to run every night without anyone invoking it, which is what the scheduler is for. In the Dashboard, go to `Scheduler → Config`, click "Create a new job", pick the cron-style type and enter `0 2 * * *`, which means two in the morning every day, and `lookups.refresh-tables` as the service. Two in the morning is after the day's other loads have finished and before anyone opens a report. The [scheduler](https://zato.io/docs/dev/examples/scheduler.html) page shows the form and how to create the same job from code.

## What the reports show {#what-the-reports-show}

A Power BI report is how Fabric turns tables into charts. A chart grouped by location takes its labels from the `name` column of the `locations` table, so when the service writes a renamed location, the chart shows the new name. There is one more step, because a report does not read the tables each time it is opened, it reads a copy of its own that has to be refreshed. The [pipelines and reports](https://zato.io/docs/dev/examples/cloud/fabric/pipelines-and-reports.html) page refreshes it from a service right after the tables are written.

## See also {#see-also}

- [Loading data into tables](https://zato.io/docs/dev/examples/cloud/fabric/loading-tables.html) - The difference between appending rows to a table and replacing it
- [Running a pipeline and refreshing a report](https://zato.io/docs/dev/examples/cloud/fabric/pipelines-and-reports.html) - Refreshing a report's data after the tables are written
- [Tables](https://zato.io/docs/dev/examples/cloud/fabric/api/tables.html) - write\_table and its mode parameter in detail

## Learn more {#learn-more}

- [Development documentation](https://zato.io/docs/dev/) - Everything about writing services, in one place
- [Requests and responses](https://zato.io/docs/dev/request-response/) - What a service receives, what it returns and how to shape both
- [Integration examples](https://zato.io/docs/dev/examples/) - Ready-made code for the systems you are likely to connect to
- [IDE and debugging](https://zato.io/docs/dev/ide/) - Write services in the Dashboard or in your own editor
- [Data models](https://zato.io/docs/dev/model/) - Declare inputs and outputs and have them validated for you
- [In-depth API tutorial](https://zato.io/tutorials/main/01.html) - The full platform tutorial, from installation to production patterns