---
title: "Best practices"
description: "Guidelines on data limits and on the OData query URL used to query data from the Reporting API tables, with optimized and unoptimized sample queries for selected tables."
contentType: "guide"
url: "https://developer.mindtickle.com/docs/reporting/best-practices/"
---

These guidelines cover data limits and the OData query URL used to query data from the Reporting API tables. They help you retrieve relevant data efficiently. This page covers general best practices, data limits, [query URL guidelines](#query-url-guidelines), and [sample queries](#sample-queries) for selected tables.

For information on the query URL components and options, see [Use OData](/docs/reporting/use-odata/) and the OData documentation on [URL conventions](https://www.odata.org/documentation/odata-version-3-0/url-conventions/).

## General best practices

The following recommendations help you decide when to use the Reporting API and when to use the Mindtickle Snowflake integration:

- **Use the Snowflake integration if** you have a Snowflake data warehouse. It does not require the Reporting API and provides direct and secure access to your Mindtickle data. This integration is based on the Snowflake [Secure Data Sharing](https://docs.snowflake.com/en/user-guide/data-sharing-intro) feature, which lets you fetch and store the data directly through Snowflake into your data warehouse. For more information, see Use Snowflake to directly access platform data.
- **Use the Reporting API if** you do not have a Snowflake data warehouse. Use the OData-based Reporting API to import data into your BI tool, such as Microsoft Excel, Google Sheets, Power BI, and Tableau. For more information, see [Reporting](/docs/reporting/).

## Data limits

The following limits apply when querying data for various [output formats](#format-option) and querying methods, that is scripts and BI tools. The limits are calculated based on 20 fields or columns. If you have more than 20 fields, the limits change accordingly.

| Data query method | Format | Row limit |
| --- | --- | --- |
| Python or script | XML | 400,000 |
| Python or script | CSV | 1 million |
| Python or script | JSON | 400,000 |
| Python or script | JSON compact | 500,000 |
| BI tools \* and Excel | XML | 200,000 |

\* Tested with Power BI only.

For datasets larger than the standard limits, use one of the following:

- **Client-side pagination:** If your dataset is larger than the standard limits but less than 5 million, implement client-side pagination to fetch up to 5 million records of data. For more information, see [Pagination](/docs/reporting/pagination/).
- **LMPI pagination:** The LMPI pagination guide requires pagination above 100,000 records per request. Use that more specific threshold instead of the general 1 million record recommendation. For more information, see [Pagination for the LMPI table](/docs/reporting/pagination/#pagination-for-the-lmpi-table).
- **Incremental tables:** For the `LearnerModulePerformances`, `CourseObjects`, `QuickUpdateObjects`, and `AssessmentObjects` tables, if the data size is greater than 1 million, use their respective incremental tables: `LearnerModulePerformancesIncremental`, `CourseObjectsIncremental`, `QuickUpdateObjectsIncremental`, and `AssessmentObjectsIncremental`.
- **Download data:** If you are using a BI tool and cannot use pagination or incremental tables, download the data in the CSV format to your local space and then connect through the BI tool.

## Query URL guidelines

The following guidelines for the query URL apply to all tables. Ensure that your OData query makes use of the following options to fetch the data faster and to fetch only relevant data:

- `Format` option: recommended
- `Filter` option: recommended
- `Select` option: recommended
- `Count` option: recommended
- `Top` option: recommended
- `Expand` option
- `Orderby` option

Also note:

- All the table and column names are case-sensitive and must be provided as shown.
- Maintain a gap of at least 30 seconds between queries when performing consecutive queries for different tables.
- The Reporting API has two [service root URLs](/docs/reporting/use-odata/#url-components) to query and extract data. Ensure that you are using the correct, applicable URL.

### Format option

The OData default output format is XML, but Mindtickle recommends the CSV format for all warehouse and non-BI tool users.

Avoid the XML and JSON formats. If you need the response in JSON, use the JSON compact format instead, because JSON fetches extra metadata information.

- **CSV:** Smallest data size and quicker to transfer over the network. For example:

  ```text
  https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=csv
  ```

- **XML:** Default output format, but largest data size. For example:

  ```text
  https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=xml
  ```

- **JSON:** Small data size and the most standard format in development. For example:

  ```text
  https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=json
  ```

- **JSON compact:** JSON with less metadata than the standard JSON output. For example:

  ```text
  https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=json_compact
  ```

### Filter option

Apply filters to limit the amount of data and get only the required data. This also reduces the data size and the time required to fetch the data.

- Use IDs instead of names in filters. Avoid filters on the name fields, such as `SeriesName`, `ModuleName`, and `ReviewerName`, on live routes. You can use them on dump routes. Live routes are the tables with near real-time update frequency, while dump routes are the tables with daily update frequency. Each table's update frequency is listed with the [tables](/docs/reporting/tables-performance-and-completion/).
- Use the primary fields provided for each table. Primary fields for each table are the granularity fields, listed in the [metadata sheet](https://docs.google.com/spreadsheets/d/1mghEtkASz0OO9YUE2lJjjMfnZ4ilsGrmyFXqNpkM-Qo/edit#gid=0) and with the [tables](/docs/reporting/tables-performance-and-completion/). As these fields are indexed, the query is much faster. For example, for the `SeriesModules` table, the primary fields are `SeriesId` and `ModuleId`.
- You do not need to apply any filters for the `Modules`, `SeriesCollection`, `SeriesModules`, and `SeriesModuleSections` tables. Always fetch the full data for them, because these tables contain a small amount of data. You can still apply filters if you are looking for specific records, such as a particular series or module. For those 4 metadata tables, avoid timestamp filters on repeat fetches. This advice does not apply to the dedicated incremental tables, which require date filters.
- Apply state-related filters to filter out learners, modules, and series by their current status on the platform. For example, you can filter out deactivated learners and archived or deleted modules. Apply state filters for all applicable tables except `LearnerModulePerformancesIncremental`. For `LearnerModulePerformancesIncremental`, the data is fetched incrementally, and filtering out may lead to data discrepancies.
- Avoid the `or`, `startswith`, and `endswith` functions in filter options, because they result in an unoptimized query. For example, if you query that the series ID is X or the module ID is Y, the query searches for that series and then the modules too, increasing the data size and the time to fetch the data. If you are searching for a few modules or series, it is fine to use. But if you search across parameters, it may fail in fetching the data.

Use `LearnerState` to filter out learners by their current status on the platform. Example values: `active`, `added`, `deactivated`. For example:

```text
https://admin.mindtickle.com/Odata.svc/Users?$format=csv&$filter=LearnerState ne 'deactivated'
```

Use `ModuleState` to filter out modules by their status on the platform. Example values: `archived`, `deleted`, `draft`, `published`. For example:

```text
https://admin.mindtickle.com/Odata.svc/Modules?$format=csv&$filter=ModuleState eq 'published'
```

Use `LearnerModuleState` to filter out learners and modules by status in combination. Example values: `deactivated`, `timedout`, `added`, `submitted`, `active`, `completed`, `challenge_added`. For example:

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessions?$format=csv&$filter=LearnerModuleState ne 'deactivated'
```

Use `SeriesState` to filter out series by their current status on the platform. Example values: `deactivated`, `published`. For example:

```text
https://admin.mindtickle.com/Odata.svc/SeriesCollection?$format=csv&$filter=SeriesState ne 'deactivated'
```

While `or` is generally discouraged, it is supported in [optimized queries](#sample-queries) for filtering specific IDs. You can include up to 30 `eq` conditions on indexed fields, such as `UserId` or `ModuleId`. For example, filter by multiple users:

```text
https://admin.mindtickle.com/Odata.svc/Users?$format=csv&$filter=UserId eq '123456789012345101' or UserId eq '123456789012345102' or UserId eq '123456789012345103'
```

Filter by multiple modules:

```text
https://admin.mindtickle.com/Odata.svc/Modules?$format=csv&$filter=ModuleId eq '123456789012345101' or ModuleId eq '123456789012345102' or ModuleId eq '123456789012345103'
```

### Select option

As with filters, use the `Select` option to limit the amount of data and get only relevant and required data. This option fetches only the selected columns and reduces the data size and the time required to fetch the data. For example:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=csv&$select=ModuleId,UserId,Version,LearnerModuleState,EntityType,HasCompleted,ModuleName,CompletionStatus,LearnerName,LearnerEmailId,Score,IsCertified
```

- For all tables, use the `Select` option if you are not interested in fetching data for all columns. `Select` limits the number of columns that are queried.
- The primary fields are selected by default. Primary fields for each table are the granularity fields, listed in the [metadata sheet](https://docs.google.com/spreadsheets/d/1mghEtkASz0OO9YUE2lJjjMfnZ4ilsGrmyFXqNpkM-Qo/edit#gid=0) and with the [tables](/docs/reporting/tables-performance-and-completion/).

### Count option

Use `$inlinecount=allpages` to request the total number of matching records. It does not reduce the returned records; `$top=1` limits the sample query below to 1 result.

- For the `LearnerModulePerformances` table, avoid querying without applying any filters if the records are greater than 100,000, which is the benchmark limit. If the record count is greater than 100,000, apply filters before running the query. If you do not apply any filters, the query is likely to fail in fetching data. At the very least, apply the `LearnerModuleState` filter to filter out deactivated records.
- For all tables, the limit of 100,000 records can go up if you narrow down the query with the `Select` option.
- If you face a 504 timeout or any other issue due to data size, run the following query to check the total number of records that your query will fetch:

  ```text
  https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=json_compact&$inlinecount=allpages&$top=1
  ```

### Top option

Use this option to fetch only the top records. For example:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=csv&$top=10
```

For the `LearnerModulePerformances` table, avoid unfiltered calls if the record count is greater than 100,000. Use the `Top` option to fetch only the top records, rather than all records.

### Expand option

Avoid the `Expand` option to join multiple tables. It makes the querying heavy and the data transfer much slower on the network, because queries with `Expand` are unoptimized. Fetch the tables separately and join their data in your client.

For example, if you want to join the `LearnerModulePerformances` and `Users` tables (`https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$expand=User&$format=csv`), fetch both tables separately as follows, and then join them on `UserId` in your script:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=csv
```

```text
https://admin.mindtickle.com/Odata.svc/Users?$format=csv
```

### Orderby option

Avoid the `Orderby` and similar options on the OData query to perform ordering and sorting of data.

When you implement client-side pagination, it is fine to use the `Orderby` option. Otherwise, sort the returned data in your client. On the client side, you can use native script functions to sort, group, and perform other aggregate functions on the dataset once the raw data is fetched through the Reporting API.

## Sample queries

Use these sample queries as a reference while creating your own queries. Optimized queries are the queries to use. Unoptimized queries are listed only for your reference; these are the kind of queries to avoid. All the table and column names are case-sensitive and must be provided as shown.

### LearnerModulePerformances

Ensure that the record count for your query is less than 100,000.

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=csv&$filter=LearnerModuleState ne 'deactivated'
```

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$format=csv&$filter=SeriesId eq '123456789012345101'&$select=SeriesId,ModuleId,UserId,InvitedOn,ReattemptNo,Score,HasCompleted,ModuleName,SeriesName,LearnerName,LearnerModuleState,EntityType,ModuleRelevance,MaxScore
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances?$expand=User&$format=csv
```

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformances
```

### LearnerModulePerformancesIncremental

Use overlapping `LastUpdatedTime` ranges between consecutive fetches, then reconcile overlapping records by the table's documented keys. Replace the historical dates and timestamp in these examples with values for your own export, within the supported 30-day window. Confirm the timestamp field name against your table metadata before building the query; do not treat `LastUpdatedTime` and `LastUpdatedTimestamp` as interchangeable.

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformancesIncremental?$format=csv&$filter=Date eq '2022-01-01' and LastUpdatedTime ge 1640995200
```

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformancesIncremental?$format=csv&$filter=Date ge '2022-01-01' and Date le '2022-01-02'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformancesIncremental?$format=csv&$filter=LastUpdatedTime ge 1640995200
```

```text
https://admin.mindtickle.com/Odata.svc/LearnerModulePerformancesIncremental?$format=csv&$filter=SeriesId eq '123456789012345101' or SeriesId eq '123456789012345102'
```

### UserGroups

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/UserGroups?$format=csv&$filter=GroupId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/UserGroups?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/UserGroups?$format=csv&$filter=GroupName eq 'group name 1' or GroupName eq 'group name 2'
```

```text
https://admin.mindtickle.com/Odata.svc/UserGroups?&$filter=AddedOn ge '2022-01-01 00:00:00'
```

### SeriesCollection

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesCollection?$format=csv&$filter=SeriesId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesCollection?$format=csv&$filter=SeriesState ne 'deactivated'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesCollection?$format=csv&$filter=SeriesName eq 'series name 1' or SeriesName eq 'series name 2'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesCollection?&$filter=CreationTime ge '2022-01-01 00:00:00'
```

### SeriesModules

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesModules?$format=csv&$filter=SeriesId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesModules?$format=csv
```

### SeriesModuleSections

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesModuleSections?$format=csv&$filter=SeriesId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesModuleSections?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesModuleSections?$format=csv&$filter=SectionName eq 'section name 1' or SectionName eq 'section name 2'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesModuleSections?&$filter=CreatedOn ge '2022-01-01 00:00:00'
```

### SeriesRating

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesRating?$format=csv&$filter=SeriesId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesRating?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesRating?$format=csv&$filter=SeriesName eq 'series name 1' or SeriesName eq 'series name 2'
```

### SeriesUserRating

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesUserRating?$format=csv&$filter=SeriesId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/SeriesUserRating?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/SeriesUserRating?$expand=Series,User
```

### Modules

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/Modules?$format=csv&$filter=ModuleId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/Modules?$format=csv&$filter=ModuleState eq 'published'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/Modules?$format=csv&$filter=ModuleName eq 'module name 1' or ModuleName eq 'module name 2'
```

```text
https://admin.mindtickle.com/Odata.svc/Modules?&$filter=LastModified ge '2022-01-01 00:00:00'
```

### Users

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/Users?$format=csv&$filter=UserId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/Users?$format=csv&$filter=LearnerState ne 'deactivated'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/Users?&$filter=AddedOn ge '2022-01-01 00:00:00'
```

```text
https://admin.mindtickle.com/Odata.svc/Users?&$filter=LearnerEmailId eq 'abc.xyz@example.com' or LearnerEmailId eq 'pqr.xyz@example.com'
```

### CoachingSessions

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessions?$format=csv&$filter=ModuleId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessions?$format=csv&$filter=LearnerModuleState ne 'deactivated'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessions?$format=csv&$filter=ReviewerName eq 'a' or ReviewerName eq 'b'
```

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessions?$expand=Module,User
```

### CoachingSessionsParameters

Prefer the `ModuleId` query on live routes. The reviewer-name query below illustrates the alternative filter syntax.

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessionsParameters?$format=csv&$filter=ModuleId eq '123456789012345101'
```

Alternative filter syntax:

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessionsParameters?$format=csv&$filter=ReviewerName eq 'a' or ReviewerName eq 'b'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/CoachingSessionsParameters?$expand=Module,User
```

### Missions

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/Missions?$format=csv&$filter=ModuleId eq '123456789012345101' and UserId eq '123456789012345104' and ReattemptNo eq '0'
```

```text
https://admin.mindtickle.com/Odata.svc/Missions?$format=csv&$filter=LearnerModuleState ne 'deactivated'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/Missions?$format=csv&$filter=ReviewerName eq 'a' or ReviewerName eq 'b'
```

```text
https://admin.mindtickle.com/Odata.svc/Missions?$expand=Module,User
```

### MissionParameters

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/MissionParameters?$format=csv&$filter=ModuleId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/MissionParameters?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/MissionParameters?$expand=Module,User
```

### MissionAIInsights

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/MissionAIInsights?$format=csv&$filter=ModuleId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/MissionAIInsights?$format=csv&$filter=LearnerModuleState ne 'deactivated'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/MissionAIInsights?$expand=Module,User
```

### CourseObjects

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/CourseObjects?$format=csv&$select=UserId,ModuleId,LearningObjectId,Title,Type,TopicName,ModuleName,CorrectResponses,WrongResponses,Responses,CompletionState,LikeDislikeState,Score,MaxScore,StartTime,EndTime,LifelinesUsed,Version&$filter=StartTime ge '2022-01-01 00:00:00' and StartTime le '2022-03-31 23:59:59'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/CourseObjects?$expand=Module,User
```

### QuestObjects

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/QuestObjects?$format=csv&$filter=ModuleId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/QuestObjects?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/QuestObjects?$expand=Module,User
```

### QuickUpdateObjects

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/QuickUpdateObjects?$format=csv&$filter=StartTime ge '2022-01-01 00:00:00' and StartTime le '2022-03-31 23:59:59'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/QuickUpdateObjects?$expand=Module,User
```

### AssessmentObjects

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/AssessmentObjects?$format=csv&$select=UserId,ModuleId,LearningObjectId,Title,Type,ModuleName,CorrectResponses,WrongResponses,CompletionState,LikeDislikeState,Score,MaxScore,StartTime,EndTime,LifelinesUsed,Version&$filter=StartTime ge '2022-01-01 00:00:00' and StartTime le '2022-03-31 23:59:59'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/AssessmentObjects?$expand=Module,User
```

### ChecklistObjects

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ChecklistObjects?$format=csv&$filter=ModuleId eq '123456789012345101'
```

```text
https://admin.mindtickle.com/Odata.svc/ChecklistObjects?$format=csv
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ChecklistObjects?$expand=Module,User
```

### ILTObjects

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ILTObjects?$format=csv&$filter=SessionId eq '123456789012345101'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ILTObjects?$expand=Module,User
```

### ILTSessions

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ILTSessions?$format=csv&$filter=SessionId eq '123456789012345101'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ILTSessions?$expand=Module
```

### ModuleTags

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ModuleTags?$format=csv&$select=CategoryName,TagName
```

```text
https://admin.mindtickle.com/Odata.svc/ModuleTags?$format=csv&$filter=TagId eq '123456789012345101'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/ModuleTags?$format=csv&$filter=TagName eq 'Tag 1' or TagName eq 'Tag 2'
```

```text
https://admin.mindtickle.com/Odata.svc/ModuleTags?$expand=Module
```

### LearningObjectTags

Optimized queries:

```text
https://admin.mindtickle.com/Odata.svc/LearningObjectTags?$format=csv&$filter=ModuleId eq '123456789012345101'
```

Unoptimized queries:

```text
https://admin.mindtickle.com/Odata.svc/LearningObjectTags?$expand=Module
```

## Related

- [Use OData](/docs/reporting/use-odata/): The query URL components and options these guidelines apply to.
- [Pagination](/docs/reporting/pagination/): Fetch datasets larger than the standard limits.
- [Tables: performance and completion](/docs/reporting/tables-performance-and-completion/): The update frequency and granularity fields each guideline refers to.