--- title: OData Connection keywords: odata, odata connection, power bi, excel, tableau, qlik, bi tools, reporting, rest api, lodata, odata v4, odata v4.01, manage_odata, api key, system user, business intelligence, basic auth summary: Explains how to set up and use the OData v4.01 API connection in Manor to connect external BI and reporting tools such as Microsoft Excel, Power BI, and Tableau directly to Manor data. --- # OData Connection ## Overview Manor provides an **OData v4.01** (Open Data Protocol) compliant REST API service. OData is an OASIS standard protocol that defines best practices for building and consuming RESTful APIs, enabling external applications to query and manipulate data using standard HTTP conventions. In Manor, the OData service is strictly **read-only**, ensuring that external reporting and Business Intelligence (BI) platforms can query firm, client, project, financial, and time-tracking data without any risk of inadvertently modifying or deleting records. Key characteristics of Manor's OData service: - **Standard Protocol:** Fully adheres to the OData v4.01 standard specification. - **Read-Only Safety:** Configured exclusively in read-only mode for querying and data retrieval. - **Live Direct Connection:** External BI tools can run live queries or scheduled data refreshes directly against Manor database entities. - **Rich Query Semantics:** Supports URL-based system query options including `$select`, `$filter`, `$orderby`, `$top`, `$skip`, and `$expand`. - **Broad Tool Compatibility:** Compatible natively with Microsoft Excel, Microsoft Power BI, Tableau, Qlik Sense, and any HTTP client or programming language supporting OData v4. --- ## Prerequisites To connect external tools to the Manor OData feed, you must configure three components in Manor Administration: 1. **A Custom Role with the OData Permission:** The user or integration service must hold the **Can access Odata API.** permission. 2. **A Dedicated System User:** A non-human service account created specifically for the integration, assigned the custom role with OData access. 3. **An Active API Key:** An API token generated for the system user, used as the secret password for HTTP Basic Authentication. ### Step 1: Grant OData Permissions via Custom Role Only users with the OData API permission can access the `/odata` service. Administrators hold this permission by default; for custom roles, assign it explicitly: 1. Navigate to **Administration** → **Roles** (`/administration/role`). 2. Click **New role** (or click **Edit** on an existing custom role). 3. Under the **OData** category, select the checkbox: - **Can access Odata API.** — *Allows a system user to use the ODATA API.* 4. Under the other permission categories (such as Projects, Invoices, Billables, or Time), ensure the role has the appropriate visibility permissions for the data you intend to report on. 5. Click **Save**. ![The OData permission group in the role permissions list](../screenshots/odata-role-permission.png) ### Step 2: Create a Dedicated System User System users are specialized accounts intended exclusively for API access and external integrations. They do not consume billable user seat licenses. 1. Navigate to **Administration** → **Users** (`/administration/user`). 2. Click **New system user for integrations** in the upper-right corner. 3. Provide a clear **Name** (e.g., "Power BI Reporting" or "Excel BI Service"). 4. Under **Roles**, select the role you configured with OData access (e.g., your custom OData role or Administrator). 5. Click **Save**. ### Step 3: Generate an API Key API keys provide credentials for external authentication: 1. Navigate to **Administration** → **API keys** (`/administration/api-key`). 2. Click **New key** in the upper-right corner. 3. Enter a descriptive **Name** (e.g., "Power BI Desktop Key"). 4. In the **User** dropdown, choose the system user created in Step 2. 5. Optionally set an **Expiration date** and add an internal description. 6. Click **Save**. 7. **Copy the generated key immediately.** The complete token is shown only once and cannot be retrieved later. The API key token is structured as: ```text [instance].[identifier].[secret] ``` The token has three dot-separated parts: an **instance prefix** identifying your Manor instance, a **key identifier**, and a **secret**. For example: ```text bigcorp.8XhJ1Ts5yDO2MeOL.4e9a8f2c9d1b7e30f6a2c4d5e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2b3c4d5e6f7 ``` --- ## OData Service URL and Metadata Manor hosts the OData feed under the `/odata` prefix of your Manor instance domain. | Endpoint | Description | |---|---| | `https:///odata` | **Service Document (Root):** Lists all exposed entity sets and resources available to query. | | `https:///odata/$metadata` | **Service Metadata (CSDL):** XML/JSON metadata document defining all entity types, properties, relations, and data types. | | `https:///odata/` | **Entity Collection:** Accesses the records for a specific entity set (e.g., `/odata/Matters`). | > **Tip:** You can inspect your schema anytime by loading `https:///odata/$metadata` in your browser or an API development client using your HTTP Basic credentials. --- ## Authentication Manor's OData service uses **HTTP Basic Authentication**: * **Username:** Your API Key Identifier (or any arbitrary placeholder string, such as `api`, `odata`, or the identifier part of the key). * **Password:** The **full API key token** (`[instance].[identifier].[secret]`), exactly as shown when the key is generated. When external BI clients send the request, Manor's authentication middleware extracts the token from the Basic Auth header, matches the identifier, verifies the secret hash, and confirms that the linked account is an active system user possessing the **Can access Odata API.** permission. The username portion of the Basic Auth header is not validated, so any placeholder value works as long as the password contains the correct full token. ### Example HTTP Request Header ```http GET /odata/Matters HTTP/1.1 Host: your-subdomain.manor.app Authorization: Basic Accept: application/json ``` --- ## Available Entities Manor exposes comprehensive business data across multiple entity sets. Common entity sets include: | Entity Set | Underlying Data | Description & Scope | |---|---|---| | **Matters** | `matters` | Core projects and cases. All time entries, billables, invoices, and activities belong to a matter. Includes computed property `excluded_from_billable_ratio`. | | **Clients** | `clients` | Individuals and companies to whom work is billed. | | **Times** | `times` | Detailed time log entries registered by team members against matters and activities. | | **Expenses** | `expenses` | Project out-of-pocket expenses and material costs. | | **Invoices** | `invoices` | Issued invoices, proposal drafts, billing amounts, totals, and invoice statuses. | | **InvoiceLines** | `invoice_lines` | Itemized line items belonging to invoices. | | **Activities** | `activities` | Categorization tasks and phases within matters (also aliased as `Segments`). | | **Users** | `users` | Staff members, professionals, and system users within the firm. | | **Departments** | `departments` | Organizational units within firms. | | **Firms** | `firms` | Legal entities and branches configured in the organization. | | **Rates** | `rates` | Hourly rate definitions and rate models. Includes computed properties `latest_price` and `latest_cost_price`. | | **RateCategories** | `rate_categories` | Categories grouping rate types together. | | **ReportingBillables** | `reporting_billables` | Denormalized reporting database view combining time, travel, expense, per diem, and product entries for unified analytics. | | **PerDiems** | `per_diems` | Daily meal and travel allowances logged on projects. | | **Travels** | `travels` | Mileage and travel records. | | **Products** | `products` | Fixed-fee items or direct disbursements added to matters. | | **Retainers** | `retainers` | Retainer agreements and subscription structures for clients. | | **RetainerPeriods** | `retainer_periods` | Periodic billing cycles and balance tracking for retainers. | | **Labels** | `labels` | Tags and categorization labels attached to matters (aliased as `Subjects`). | | **Labellables** | `labellables` | Matter-to-label associations. | | **Currencies** | `currencies` | Currencies configured for multi-currency billing and rate cards. | | **VatRates** | `vat_rates` | Tax percentages applied across invoice items and billables. | | **PayPeriods** | `pay_periods` | Payroll processing periods. | | **PayPeriodUser** | `pay_period_user` | User allocations within specific pay periods. | | **BillableInvoiceLine** | `billable_invoice_line` | Pivot mapping linking billable entries to specific invoice line items. | > **Note:** Sensitive fields such as passwords, personal secrets, and raw authentication tokens are excluded from OData serialization. --- ## Step-by-Step: Connecting Microsoft Excel Microsoft Excel allows you to import OData feeds directly into modern Power Query worksheets and Power Pivot data models. ### Step 1: Open OData Feed Connector 1. Open Microsoft Excel and create a new or existing workbook. 2. Go to the **Data** tab on the Excel ribbon. 3. Click **Get Data** → **From Other Sources** → **From OData Feed**. ### Step 2: Specify the Feed URL 1. In the **OData feed** dialog: - Select **Basic**. - Enter your OData Service URL: ```text https:///odata ``` 2. Click **OK**. ### Step 3: Enter Authentication Credentials 1. When prompted for credentials, select the **Basic** tab on the left side of the authentication window. 2. Configure the fields: - **User name:** Your API Key Identifier (or `api`). - **Password:** Paste the complete API key token (`[instance].[identifier].[secret]`). - **Select which level to apply these settings to:** Choose `https:///odata`. 3. Click **Connect**. ### Step 4: Select Tables and Load 1. The **Navigator** window opens, listing all available Manor entity sets (`Matters`, `Times`, `Invoices`, `Clients`, etc.). 2. Check **Select multiple items** if you wish to import multiple tables simultaneously. 3. Select the desired entity sets. Excel displays a preview of the records on the right. 4. Click **Load** to import data directly into Excel tables, or click **Transform Data** to filter columns, modify data types, and build custom calculated fields in the Power Query Editor. --- ## Connecting Power BI and Other BI Tools ### Microsoft Power BI Desktop 1. Open **Power BI Desktop**. 2. On the **Home** tab, click **Get Data** → **OData feed** (or click **Get Data** → **More...** → **Other** → **OData Feed**). 3. Enter the URL `https:///odata` and click **OK**. 4. In the authentication prompt: - Click the **Basic** tab on the left. - Enter your **User name** (your API key identifier or `api`). - Enter your **Password** (the complete API key token). - Set the credential level to `https:///odata`. 5. Click **Connect**. 6. In the **Navigator** dialog, select the entities you need (e.g., `Matters`, `Times`, `ReportingBillables`) and click **Load** or **Transform Data**. 7. Establish table relationships using standard key fields (such as `matter_id`, `client_id`, `user_id`) to build interactive dashboards and KPIs. ### Tableau 1. Open Tableau Desktop. 2. Under **Connect** → **To a Server**, select **OData**. 3. In the OData connection dialog: - **Server:** Enter the specific entity collection URL, e.g.: ```text https:///odata/Matters ``` - **Authentication:** Select **Username and Password**. - **Username:** Your API Key Identifier. - **Password:** Complete API key token. 4. Click **Sign In**. 5. Repeat for any additional entity sets required in your Tableau data source, linking them across common ID fields. ### Qlik Sense & Generic REST / OData Clients In Qlik Sense, use the **REST Connector** or native **OData Connector**: 1. Configure a new data connection with the URL `https:///odata/`. 2. Select **Basic Authentication**. 3. Supply your identifier as the username and the secret token as the password. 4. Set the HTTP header `Accept: application/json`. 5. Run the data load script. --- ## Query Options and Pagination Manor supports standard OData v4 query options, allowing external tools to optimize network transfer by filtering, selecting, and sorting records on the server before transferring data. ### System Query Options You can append query options to entity endpoints using standard URL query string formatting: | Query Option | Purpose | Example | |---|---|---| | `$select` | Returns only specified properties, reducing payload size. | `/odata/Matters?$select=id,name,client_id,state` | | `$filter` | Filters entities matching logical expressions. | `/odata/Matters?$filter=state eq 'active'` | | `$orderby` | Sorts results by one or more properties ascending (`asc`) or descending (`desc`). | `/odata/Invoices?$orderby=issue_date desc` | | `$top` | Limits the number of returned records. | `/odata/Times?$top=50` | | `$skip` | Skips a specific number of records (used for offset pagination). | `/odata/Times?$skip=100&$top=50` | | `$expand` | Includes related entity data inline within the response. | `/odata/Matters?$expand=client` | | `$count` | Returns total record count along with results. | `/odata/Matters?$count=true` | ### Common Filter Operators The `$filter` parameter supports standard comparison and logical operators: - **Equality / Inequality:** `eq` (equal), `ne` (not equal) - **Comparison:** `gt` (greater than), `ge` (greater than or equal), `lt` (less than), `le` (less than or equal) - **Logical:** `and`, `or`, `not` - **String Functions:** `contains(name, 'Corporate')`, `startswith(name, 'Project')` - **Date Filtering:** `/odata/Times?$filter=date ge 2026-01-01` ### Pagination (1,000 Items Per Page) To ensure high performance and prevent memory exhaustion during bulk transfers, Manor applies server-side pagination: - **Default Page Size:** Responses return up to **1,000 items per page**. - **Next Page Links (`@odata.nextLink`):** When an entity collection contains more than 1,000 records, the response payload includes an `@odata.nextLink` annotation containing the URL to fetch the subsequent batch. - **Client Handling:** Standard tools like Microsoft Power BI and Excel automatically detect and follow `@odata.nextLink` annotations, seamlessly retrieving full datasets across pages without requiring manual pagination logic. --- ## Security Considerations To maintain robust security when connecting external reporting tools: * **Principle of Least Privilege:** Configure a custom role with only the necessary view permissions. Do not grant broad administrative or invoicing edit rights if the service account only needs to read reporting tables. * **Dedicated System Users:** Never share an API key or system user across multiple tools or departments. Create a separate system user and API key for Excel analysts, Power BI dashboards, and data pipelines. * **HTTPS Encryption:** Always connect using encrypted HTTPS connections to safeguard credentials and organization data in transit. * **Credential Protection:** Store API tokens in enterprise key vaults or secure password managers. Never hardcode API keys into public script repositories or distribute unencrypted workbooks containing embedded plaintext credentials. * **Key Rotation & Expiration:** Set expiration dates for API keys assigned to third-party consultants or external auditors. Regularly inspect active keys under **Administration** → **API keys** and immediately revoke keys that are no longer in active use. * **Instant Revocation:** If a key is compromised, navigate to **Administration** → **API keys**, open the key, and click **Revoke key** to cut off API access immediately. --- ## Related Topics - [API Keys](api-keys.md) — How to generate, inspect, and revoke API credentials - [System Users](system-users.md) — Creating and managing non-human service accounts - [Custom Roles](custom-roles.md) — Configuring custom permission sets - [Custom Role Permissions](custom-role-permissions.md) — Detailed permissions reference guide