Data Model
The connector models Microsoft Planner data like tasks, plans, and buckets as relational tables, allowing you to write SQL to query and modify Microsoft Planner data. Live connectivity to these objects means that any changes to your Microsoft Planner account are immediately reflected in the connection exposed by the connector.
Tables
The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, contain samples of what you might have access to in your Microsoft Planner account.
The following tables and views are shipped with the connector:
| Table | Description |
| Buckets | Defines custom columns for tasks, allowing users to categorize work into phases, task types, or departments for better organization. |
| PlanDetails | Stores metadata and additional settings related to a specific plan, enhancing its customization and management. |
| Plans | Represents individual plans, each associated with a group, to organize and track project-related tasks. |
| TaskDetails | Holds detailed information for tasks, including attributes such as priority, due dates, and custom fields. |
| Tasks | Manages individual tasks, providing functionality for planning, assigning, and tracking initiatives across various projects. |
| Groups | Represents the groups created automatically when a new plan is initiated, serving as the foundational organizational unit for tasks and plans. |
| TaskAssignments | Provides a unified view of all tasks assigned to various users, aiding in task tracking and accountability. |
| TaskComments | Captures discussions, notes, and comments related to tasks within a plan, fostering collaboration and communication. |
| Users | Tracks user-specific task views and preferences, enabling personalized task management experiences. |
Default Filters
The Microsoft Planner API supports several ways of accessing most entities. For example, you can fetch Tasks entries in these ways:
- Personal Tasks: Tasks that are assigned to the current user. The connector uses this when querying Tasks with no filter but only when authenticating as a user. This method is unavailable when authenticating as a service principal.
- Specific Tasks: Providing a WHERE TaskId = '...' filter reads a specific task.
- Plan Tasks: Providing a WHERE PlanId = '...' filter reads all tasks belonging to that plan. This includes assigned and unassigned tasks. When authenticating as a service principal, the connector first queries Groups to find all plans belonging to a group and then uses this API to fetch the tasks for each of those plans.
- User Tasks: Providing a WHERE UserId = '...' filter reads all tasks assigned to that user.
- Bucket Tasks: Providing a WHERE BucketId = '...' filter reads all tasks in a plan bucket. This includes assigned and unassigned tasks.
Each of these options retrieves a different collection of tasks and has different performance characteristics. For example, SELECT * FROM Tasks WHERE BucketId IN (SELECT BucketId FROM Buckets) fetches tasks using buckets, which requires fetching plans (buckets belong to plans), which requires fetching groups. On the other hand, SELECT * FROM Tasks WHERE UserId IN (SELECT UserId FROM Users) requests the tasks assigned to each individual user. Which one is faster depends upon how many users you have versus how complex your group and plan structure is.
If you do not get the data or performance you expect, review the table definitions to ensure you are using the right relationships to read data.