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, and sample queries for selected tables.

For information on the query URL components and options, see Use OData and the OData documentation on 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 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.

Data limits

The following limits apply when querying data for various output formats 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.
• 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.
• 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 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:

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

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

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

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

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

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

  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.
• Use the primary fields provided for each table. Primary fields for each table are the granularity fields, listed in the metadata sheet and with the tables. 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:

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:

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:

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:

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

While or is generally discouraged, it is supported in optimized 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:

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:

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:

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 and with the tables.

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:

  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:

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:

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

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:

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

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:

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

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:

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

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

Unoptimized queries:

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

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

UserGroups

Optimized queries:

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

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

Unoptimized queries:

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

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

SeriesCollection

Optimized queries:

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

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

Unoptimized queries:

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

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

SeriesModules

Optimized queries:

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

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

SeriesModuleSections

Optimized queries:

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

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

Unoptimized queries:

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

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

SeriesRating

Optimized queries:

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

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

Unoptimized queries:

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

SeriesUserRating

Optimized queries:

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

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

Unoptimized queries:

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

Modules

Optimized queries:

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

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

Unoptimized queries:

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

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

Users

Optimized queries:

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

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

Unoptimized queries:

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

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

CoachingSessions

Optimized queries:

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

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

Unoptimized queries:

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

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:

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

Alternative filter syntax:

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

Unoptimized queries:

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

Missions

Optimized queries:

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

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

Unoptimized queries:

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

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

MissionParameters

Optimized queries:

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

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

Unoptimized queries:

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

MissionAIInsights

Optimized queries:

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

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

Unoptimized queries:

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

CourseObjects

Optimized queries:

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:

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

QuestObjects

Optimized queries:

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

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

Unoptimized queries:

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

QuickUpdateObjects

Optimized queries:

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:

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

AssessmentObjects

Optimized queries:

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:

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

ChecklistObjects

Optimized queries:

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

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

Unoptimized queries:

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

ILTObjects

Optimized queries:

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

Unoptimized queries:

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

ILTSessions

Optimized queries:

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

Unoptimized queries:

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

ModuleTags

Optimized queries:

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

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

Unoptimized queries:

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

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

LearningObjectTags

Optimized queries:

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

Unoptimized queries:

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

Related

• Use OData: The query URL components and options these guidelines apply to.
• Pagination: Fetch datasets larger than the standard limits.
• Tables: performance and completion: The update frequency and granularity fields each guideline refers to.