Body
This article serves as a guide for organizations who wish to start querying their private cloud database for extended reporting scenarios and covers useful information to keep in mind for all query situations.
The over-arching design for the majority of tables in the TeamDynamix database includes a tenant ID column. This is almost always a column postfixed with _BEID or _BEIDInt, though a few tables have a dedicated BE_ID column.
- The name BEID stems from the term Business Entity or Billable Entity (as denoted from the BillableEntity table).
- The Business Entity ID started as a SQL uniqueidentifier data type. Later on, TeamDynamix added an integer-based Business Entity ID, hence the BEIDInt nomenclature.
The easiest way to identify your tenant ID is to navigate to the TDAdmin application for your organization.
- You can view the ID number for your tenant on the main tenant details page (below the tenant name header should be an ID: ### section).
- Alternatively, you can access this information from the API by using this URL format: https://your_tenant_domain/TDWebApi/Api/Environment
The API Environment endpoint will return an XML document with a base EnvironmentInformation object. One of the child nodes in that XML object will be named BEIDInt. Use the value inside this node as your integer tenant ID.
You can then plug your integer tenant ID into this query template to store both values as variables for your query.
Declare @BEIDInt int = YOUR_TENANT_ID_HERE;
Declare @BEID uniqueidentifier = (Select Top 1 BE_ID From dbo.BillableEntity Where BE_IDInt = @BEIDInt);
Use these variables as appropriate whenever querying tables that have Business Entity filters available.
- The tables are indexed primarily to filter by this column first, making queries more performant.
- These values are the same across your production, sandbox and release preview environments.
Why should I use Tenant ID filtering?
- First, it ensures that queries only return your tenant data. Each database has a "reserved" TeamDynamix entity.
- While the TeamDynamix entity has little to no data inside of it, its data may get picked up in queries, leading to confusing results.
- Second, most of the tables are indexed in a way to optimize for Tenant ID filtering.
- Query performance can be drastically impacted for the worse by excluding this filtering.
Given the number of tables in the TeamDynamix database, we do not currently produce a schema diagram. We recommend using a dynamic, third-party tool for purposes of illustrating table relationships. The change reports were generated with RedGate SQL Doc.
The most common tables to query directly are typically as follows.
| Table Name |
Description |
| Assets |
Table containing each asset record. |
| Attachments |
Table containing a reference to all the file attachments associated with a work item. |
| Attributes |
Table containing all of the custom fields that have been defined. This would indicate the name of the custom field, whether it was marked as required, its data type (e.g. date/time, dropdown, etc.), etc. |
| AttributeChoices |
Table containing all of the choice values found within custom fields (e.g. drop down menu of choices). For example, if an there was an Attribute named “Eye Color”, this table would contain the choices: Blue, Brown, Green, etc. |
| AttributeValues |
Table containing the custom fields information that is associated with a work item. In other words, this table will indicate that ticket 12345 has “Blue” selected for the Eye Color attribute. This may require joining from AttributeValues to AttributeChoices on choice ID to get the choice name. |
| ClientAcctCodes |
Table containing each account/department record. |
| Expenses |
Table containing all of the individual expense information logged by users against work items. |
| ItemUpdates |
Table containing the top-level Feed information found on work items. |
| ItemUpdateComents |
Table containing the sub-level Feed information found on work items. |
| Surveys |
Table containing survey response data. |
| Teams |
Table containing each project, project request and workspace record. |
| Tickets |
Table containing each ticket record. |
| TicketTasks |
Table containing ticket tasks that are part of a ticket. |
| Time |
Table containing all the individual time/effort entries logged by users against work items. |
| Users |
Table containing information about user records. |