> For the complete documentation index, see [llms.txt](https://support.ordereazi.com/central/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://support.ordereazi.com/central/developers/odata-and-power-bi.md).

# OData and Power BI

Connect Microsoft Power BI, Excel, or another reporting tool to your OrderEazi data through OData, a read-only feed of 52 data sets.

The reports in OrderEazi answer the common questions, but you may want your own dashboards, or to combine OrderEazi data with other figures. OData (Open Data Protocol, a standard way to share data over the web) gives reporting tools a read-only feed of your OrderEazi data. You can use Microsoft Power BI, Excel, or another tool, and refresh the reports whenever you like.

### Before you start

You'll need:

* **An API key with the OData permission:** Someone in your organisation with the **API Keys** permission creates a key under **Integrations** > **API Keys**, and ticks **For OData (PowerBI)**. Use a key just for reporting, with no other permissions. See [API Keys](/central/order-management/integrations/api-keys.md).
* **Your OrderEazi address:** This is the address you sign in at, such as `yourcompany.ordereazi.com`. The feed is at `https://yourcompany.ordereazi.com/odata`.
* **Power BI Desktop or Excel:** Either works. Power BI Desktop is free from Microsoft.

> **Warning:** **For OData (PowerBI)** gives read access to all of your organisation's data in the feed, including costs and margins, whatever is hidden on the key's other tabs. Give the key only to people who may see everything, and keep it out of reports you share.

### How to connect Power BI Desktop

The feed needs your key in a header (extra information sent with each request), and the **OData feed** option in Power BI can't send one. Connect with a blank query instead.

1. In Power BI Desktop, select **Get data** > **Blank query**.
2. In the Power Query Editor, select **Advanced Editor**.
3. Replace the text with the query below, using your own address and key:

   ```
   let
       Source = OData.Feed(
           "https://yourcompany.ordereazi.com/odata",
           null,
           [
               Implementation = "2.0",
               Headers = [ #"X-OrderEazi-Key" = "YOUR_API_KEY" ]
           ]
       )
   in
       Source
   ```
4. Select **Done**.
5. If Power BI asks how to connect, choose **Anonymous**. The key in the query does the signing in.
6. Select **Connect**.
7. For each data set you want, right-click **Table** in its **Data** column, and select **Add as New Query**.
8. Select **Close & Apply**.

The data sets you chose appear in the **Data** pane, ready for your report.

`Implementation = "2.0"` lets Power BI send your filters and column choices to OrderEazi, so it downloads less data.

**Example:** You want a dashboard of sales by rep for this year. You connect, and open **Projects** and **ProjectValues**. In the Power Query Editor, you filter **Projects** to this year first, so less data comes over, and keep only the columns you need. Power BI joins the two tables on `ProjectId`, and you chart the totals by sales rep.

### How to connect Excel

Excel uses the same query.

1. In Excel, select **Data** > **Get Data** > **From Other Sources** > **Blank Query**.
2. Select **Advanced Editor**.
3. Paste the query from above, with your address and key.
4. Select **Done**.
5. If Excel asks how to connect, choose **Anonymous**.
6. For each data set you want, right-click **Table** in its **Data** column, and select **Add as New Query**.
7. Select **Close & Load**.

Each data set loads into its own sheet.

### How to refresh in the Power BI service

When you publish a report to the Power BI service, set up its refresh in the semantic model (what the Power BI service calls the data behind a published report):

1. Open the semantic model's settings.
2. Go to **Data source credentials**, and select **Edit credentials**.
3. Choose **Anonymous** as the authentication method.
4. Set a privacy level.
5. If the check fails, tick **Skip test connection**. The test doesn't send the key, but a refresh does.

The feed is on the internet, so you don't need a gateway (software Power BI needs to reach data on a private network).

### The data sets

Each data set's name is the name you see in Power BI. Every row has an ID column, such as `ProjectId`, that other data sets use to link to it.

#### Clients

* **Clients:** Client accounts, with contact details, tax number, balance, credit limit, and terms.
* **ClientAddresses:** Each client's addresses.
* **ManualClients:** The client and contact details captured on each project. Use this to link a project to its client: `Projects.ManualClientId` to `ManualClients`, then `ManualClients.ClientId` to `Clients`.
* **EndUsers**, **EndUserContacts**, and **EndUserAddresses:** End Users, whoever actually receives a client's goods, with their contacts and addresses.

#### Suppliers

* **Suppliers:** Supplier records, with contact details, terms, balance, and addresses.
* **SupplierCategories:** Supplier category names.

#### Products

* **Products:** The product catalogue, with codes, descriptions, brand, and size and colour.
* **Brands** and **Categories:** Brand names, and the category tree.
* **ProductCategories:** Which products are in which categories.
* **ProductDimensions:** Each product's size and weight.
* **ProductKeyValues:** Each product's custom fields.
* **ProductMOQs:** Minimum, maximum, and default order quantities.
* **ProductMarkupMargins:** Each product's markup and margin limits.
* **ProductSuppliers:** Which suppliers sell each product, with their codes, costs, and stock.
* **ProductSupplierPrices:** Each supplier's cost prices, by quantity.
* **ProductBehaviours:** Product behaviour rules.
* **ParcelDefinitions:** Your standard box sizes.

#### Projects (orders and Quotes)

* **Projects:** Each project's number, status, sales rep, dates, client order number, sales channel, team, and currency.
* **ProjectLines:** Each project's lines, with quantities, costs, selling prices, markup, and GP (gross profit).
* **ProjectValues:** Each project's totals: cost, sales, GP, tax, discount, expenses, and shipping.
* **ProjectHistories:** Each project's history.
* **ProjectKeyValues** and **ProjectLineKeyValues:** Custom fields on projects and lines.
* **ProjectRequests:** Requests raised on projects.
* **ProjectTags** and **Tags:** Which tags are on each project, and the tags themselves.
* **ProjectWorkflows:** The workflow each project follows.
* **ProductHistories:** Changes to project lines.

#### Documents

* **Documents:** Every document, such as Quotes, Sales Orders, Tax Invoices, and Purchase Orders, with its type, number, dates, status, and ERP number (the number your accounting system gave it). Documents have no totals: add up their **DocumentProducts**.
* **DocumentProducts:** Each document's lines, with quantities, costs, selling prices, GP, and tax.
* **DocumentProductSerials:** Serial numbers on document lines.
* **DocumentVoids:** Voided documents.
* **Addresses:** The addresses on documents.
* **DeliveryNoteSurveyResponses:** Clients' answers to the delivery survey.

#### Stock

* **SimpleStockLevels:** Stock from Simple Inventory Management for each product in each warehouse and location: on hand, reserved, available, incoming, and average cost.
* **BranchWarehouses:** Which warehouses belong to each branch.

#### Your organisation

* **Organizations**, **OrganizationSettings**, **OrganizationAdditionalAddresses**, and **OrganizationBanks:** Your company details, settings, extra addresses, and bank accounts.
* **Branches**, **BranchUsers**, and **BranchUserAccesses:** Branches, and the users in each.
* **Currencies**, **TaxTypes**, and **Industries:** Currencies, tax rates, and client industries.
* **Teams**, **UserDetails**, and **UserTeams:** Teams, users, and who's in which team.

To see every column, open `https://yourcompany.ordereazi.com/odata/$metadata`.

### Things to know about the data

* **Deleted records:** Clients, products, and suppliers you've deleted are still in the feed. Deleted clients and products show as inactive.
* **Voided documents:** These are included. Filter on `IsVoided` to leave them out.
* **Dates and times:** These are in UTC (Coordinated Universal Time). South African time is 2 hours ahead.
* **Related records:** Columns such as a project's `Currency` come through as a record. Select the expand icon on the column to pick the fields you want.
* **Money:** Amounts are in the project's or document's own currency. Use `CurrencyId` and `ExchangeRate` to convert them.

### Limits

* **100 rows at a time:** The feed sends up to 100 rows per request. Power BI and Excel fetch the rest for you.
* **500 requests a minute:** This is per key, and it's shared with any API calls made with the same key. At 100 rows a request, a refresh of very large tables can reach it.
* **Long queries:** A query that takes longer than 3 minutes stops with an error.

To keep refreshes fast, filter large tables such as **ProjectLines** and **DocumentProducts** by date early in the Power Query Editor. Remove columns you don't need.

### When the feed refuses a request

* **401, "Unauthorized. A valid X-OrderEazi-Key API key is required.":** The key is missing or wrong. Check the query's header.
* **403, "Forbidden. Your API key does not have permission to perform this action.":** The key doesn't have **For OData (PowerBI)**. Ask your administrator to tick it.
* **429, "Too many requests, please try again later.":** The key has made more than 500 requests in a minute. Wait 30 seconds, then refresh again.
* **400:** The request asks for something the feed doesn't support, such as a column that doesn't exist.

### How to query the feed directly

```bash
curl 'https://yourcompany.ordereazi.com/odata/Projects?$top=10' \
  -H "X-OrderEazi-Key: YOUR_API_KEY"
```

The feed is OData version 4, at `/odata`, with your key in the `X-OrderEazi-Key` header. It supports `$select`, `$filter`, `$orderby`, `$top` (up to 1,000), `$skip`, `$count`, and `$expand` (2 levels deep). Larger results come back 100 rows at a time, with an `@odata.nextLink` to the next page.

* **One row:** `GET /odata/Projects(6f1c2a9e-0b7d-4c4f-9a51-1f0b8f6a2d11)`, with the ID unquoted.
* **Related rows:** `GET /odata/Projects(6f1c2a9e-0b7d-4c4f-9a51-1f0b8f6a2d11)/ProjectLines`.
* **Joining tables:** **ProductCategories**, **ProductSuppliers**, **ProjectTags**, **BranchWarehouses**, **BranchUserAccesses**, and **UserTeams** have no single ID, so read them with `$filter`.
* **Not expandable:** `ProductSupplierPrices` (from **Products** or **Suppliers**) and `ChildDocuments` (from **Documents**) can't be used in `$expand`. Read them through the path instead, such as `/odata/Products({id})/ProductSupplierPrices`.

### What the feed does not do

**Changing data:** The feed is read-only. To create or change data, use the API. See [Getting started with the API](/central/developers/getting-started-with-the-api.md).

### Related pages

* [API Keys](/central/order-management/integrations/api-keys.md): create the key
* [Authentication](/central/developers/authentication.md): API key permissions
* [Rate limits](/central/developers/rate-limits.md): the 500 requests a minute limit
* [Reports](/central/order-management/reports.md): the reports built into OrderEazi