Extensive Microsoft Dynamics Database Guide

Dynamics CRM data is stored in a Microsoft Dynamics database. Reports access this data through several filtered views included in the Dynamics CRM. The data is filtered by these views based on the user’s Dynamics CRM security role.

Extensive Microsoft Dynamics Database Guide

Object Schemas

To locate schema information on any filtered view, attribute, or entity in the Dynamics CRM database, any of the following methods can be applied:

  1. In the Visual Studio.NET, Server Explorer helps you get connected to the server where Dynamics CRM is installed. Then expand the SQL Servers node, as well as the target SQL server instance code. Access the filtered views under the Views node: <organization>_MSCRM database. By right-clicking the filtered view, you can be able to view a shortcut menu that enables you to go through the design of the filtered view, including the data it returns
  2. You can log into an account with the System Admin security role and then clicking Settings>>Customization>>Customize Entities, and then double-clicking an entity. You may view Attributes and Relationships under Details.

In Attributes you’ll find all the attributes, with their respective names and a brief description of each attribute.
N:1 Relationships, 1:N Relationships, and N:N Relationships indicate the current entity and its relationship with other entities. To view mapping between these attributes, just open a relationship form and link Mappings under Details.

  • Open the Metadata Browser at http: //<crmserver>/sdk/list.aspx
  • Directly view the database contents by opening the SQL Server Management Studio and SQL Server 2000

These methods allow you to access schema info for custom entities and attributes.

Naming Conventions

The field naming conventions found within the Dynamics CRM database include:
Attributes from Filtered Views: While field names in Dynamics CRM are both case-sensitive and even in mixed cases, all attribute names from filtered views are lowercase.

Drop-Down List Attributes

For every string in a list, drop-down lists have two associated fields and each string has a value or code field and a name or label field, for instance, listcode and listcodename. The label fields are displayed in the reports, which also use the value field for various numeric comparisons.

Entity Attributes

In the Microsoft Dynamics database, each entity table have all their primary key fields in the name format entityid, e.g. accounted, with a associated entityname filed with the value that needs to be shown in the reports.

Status, Status Reason Filtered Views and Drop-Down Lists

One filtered view is used for drop-down lists and the other for the data of status and status reason. Dynamics CRM applies an entity’s status and even status reason in seamless business logic. These attributes frequently filter the results. For instance, you may have a result that displays open cases/ opportunities already closed due to a loss to a particular competitor.

  • FilteredStringMap: has valid numeric values for all items in the drop-down list, including equivalent labels for such values, as well as the order of display for items in that list
  • FieldStatusMap: has valid status reason, including the default status reason. But only specific status reasons are valid for every status value

The names in Dynamics CRM used for status and status reason include:

  •  Statecodename for status
  •  Statuscodename for status reason

You can easily confuse the schema and logical name, as the Web App customization forms utilize the label schema name for logical name. Note that after creating the entity or attribute, the SDK utilizes the name in lowercase.

For instance, the Account entity’s logical name is “account” and that of a customized entity known as Bank Account can be “new_bankaccount”. Below are various names for a typical entity:

  • Display Name: the entity name shown to the user in the Web App and the Dynamics CRM for Office Outlook
  • Collection Name: this name belongs to the class that is created in the Web service representing the entity, which is all lowercase
  • Schema Name: used in creating the logical name, as well as the secured view. The name needs to use Pascal case

Metadata Web Service: Name Used

Attributes, entities, and relationships possess several different names

  • Display Name: this is the name displayed to the user
  • Display Collection Name: Display name’s plural version, which only applies to entities. In addition, the unique class or property name created in the (WSDL) Web Services Description Language is all in lowercase
  • Logical Name: mostly used with the SDK. A custom attribute, relationships, or entity has a prefix, with the unique schema name specified on creation and used to create the logical name, in Pascal case.
  • Schema Name: For a custom entity, relationship, or attribute, this must have a prefix

Prefix_SchemaName

The schema and logical name for customized attributes, relationships and entities and for many-to-many relationship intersect tables contain a total length of 50 (max), with the following format:

The name and the prefix must be constructed to set the name values that will be used in metadata (particularly create methods). The prefix or characters that come before the underscore, you may use the prefix that is defined in the organization.schemanameprefix property, which must contain 2-8 characters.

However, the schema name needs to be in Pascal case. While this may not be enforced, it is the convention that Dynamics CRM follows and so will be much easier to differentiate the multiple name types located in the metadata. Moreover, the schema name is then converted to uppercase just for the logical name.

Conclusion

A Microsoft Dynamics database stores the Dynamics CRM data, where reports can easily access this data via a number of filtered views included in the Dynamics CRM. These views filter data based on the user’s Dynamics CRM security role. Using the Dynamics CRM SDK means you are building on top of the system, whereby the Dynamics CRM platform can support smaller deployments, as well as scale for app service provider models.

The security mode will protect the platform from any unauthorized access over the Web. The key platform components are: SQL Server database, System services (metadata, workflow and integration), Web services, a query processor to support the entity model, plug-ins for the extensibility of business logic, reporting services, and secured ad hoc queries using an XML fetch statement in protecting the physical Microsoft Dynamics database.

Michael Taylor
Michael is the Lead Author & Editor of DynaMe. DynaMe is a blog focused on cloud based Microsoft Dynamics.
Michael Taylor on sabtwitterMichael Taylor on sablinkedinMichael Taylor on sabgoogleMichael Taylor on sabfacebook