Power BI & Excel
Connect
Power BI Desktop: Get data › OData feed, URL https://odata.loby.io. When asked to authenticate, pick Basic, type any username (it is ignored, e.g. loby) and paste your OData API key as the password. Pick the entity sets you need and load.
Excel: Data › Get Data › From Other Sources › From OData Feed, then the same URL and Basic credentials. On a Mac (Microsoft 365, Excel 16.69 or later): Data › Get Data (Power Query) › OData.
Power BI's navigator shows the ten entity sets; relationships are carried by the id properties (Visits.customerId → Customers.id, Tickets.ticketTypeId → TicketTypes.id, and so on), so model them as regular relationships in Power BI.
Incremental refresh
Reloading years of visits on every refresh is slow. Power BI's incremental refresh splits a table into date ranges and reloads only the recent ones. Base the ranges on each set's own date, which never changes for a row:
| Set | Range column |
|---|---|
| Visits | visitDate |
| Tickets | createdAt |
| Orders | orderDate |
| Transactions | transactionDate |
| Customers | createdAt |
| Memberships | createdAt |
- Define the
RangeStart/RangeEnddatetime parameters in Power Query. - Filter the table on its range column, e.g.
visitDate ge RangeStart and visitDate lt RangeEnd(Power Query folds this into the feed's$filter). - Set the incremental refresh policy on the table (e.g. archive 5 years, refresh the last 30 days).
A row that changes after its range was loaded — a ticket marked used months after it was bought, a customer who moves — is reloaded only with its range. Schedule an occasional full refresh to pick those up, and to drop deleted rows, which incremental refresh never sees.
Don't range on updatedAt, where a set has one: a changed row would move into a new range while the old range still holds it, so it would appear twice.
The dimension sets
TicketTypes, MembershipTypes, Departments and Locations are small. Load them in full on every refresh instead of incrementally.