Microsoft CRM 4.0 database structure (to find your data and report on it)

Having become so familiar with the CRM database structure I almost take it for granted.  There is great value in how Microsoft designed and built the database and how it handles creation of new entities.  I found myself explaining the benefits of how the database is structured twice this week so I figured that warrants a post.
 
The CRM database is structured with views that prevent the need to query the tables directly.  Each entity in CRM consists of two tables in the database.  For example, the account entity consists of the AccountBase and AccountExtensionBase tables.  The AccountBase table contains the out of the box attributes and any custom attributes are in the AccountExtensionBase table.  The database has a view named Account that automatically joins those two tables so when querying for data you would query the view and use a query such as ‘select * from account’.  This view also has joins to related tables based on foreign key ids to retrieve the name text value of the related entity.  For instance, it contains the ownerid but also contains the owneridname which returns the name of the owner.  This can prove handy when creating reports.
Another view also exists named Filtered<entity name> (i.e. FilteredIncident).  This view has the same structure as the Incident view yet also applies the system security to that view.  This allows someone to write a report using the FilteredIncident view named ‘My Cases’ and distribute it to the entire organization.  When executed, the report is filtered to only show Cases that the executing user has access to.
Most tables and views in CRM bear the same name as it is labeled in CRM yet a system customizer can rename an entity.  Case is one object out of the box that has underlying tables with a different name.  The Incident tables and views represent the Case entity in CRM.  Any custom entites you create contain the customization prefix in the table and view name as defined in the CRM System Settings.  Once the new entity is created, the supporting tables and views as I defined above are automatically created for you and no additional work is needed on the database end.
If ever you are unable to locate the tables and views that support an entity, you can locate that in the Microsoft Dynamics CRM interface.  In CRM, click “Settings” in the Wunderbar, click “Customization” in the navigation links on the top left of the screen, and then select “Customize Entities”.  Find the entity you are interested in by viewing the “Display Name” column as that represents the name used in the user interface.  The “Name” column will show you what the underlying table/view name is in the database.
When you open an entity in the CRM customization area, you can locate the 1:N, N:1, and N:N relationships for that entity in the left navigation area.  It will display the name of the foreign key id field in the related entity if you are viewing a 1:N relationship for instance.
This post should give you an idea of the database structure in Microsoft Dynamics CRM and the value it provides for reporting or other purposes.

Comments

[Robert_Engage2day]

Thanks for the post Mark.. SRS is slowly becomming a new badge for me to wear at work so this should be a good reference point for me.

[Jonas]

You promised to speak about the advantages of the CRM database model. Apologies, but i could not find one single advantage in the model you described.

[Mark Weilandt]

The advantages of the CRM data model outlined in the post are:
1. Automatic creation of views and tables when creating a new entity.
2. The entity view joins the two underlying tables and retrieves the names of the foreign key id objects into the view so you don't have to create as many joins when querying.
3. The entity filtered view enforces user security so reports built using these views will not show records they should not see.

[Rex Wood]

Mark, you mentioned in your post about CRM creating views automatically. As a novice in DB construction, is calling data from several tables possible, in order to merge complex documentation using wide ranging data from the breadth of CRM.

I am not satisfied with the response I have from consultants that are wanting to add all of the data to the Contact table [extended table].

There must be a more elegant approach that will allow me to create a view with data from across the board [contact, financial, activity, relationships, employment, etc] in order to use that data in a Word merge document.

I have used Excel/Word merging very effectively for a long time, it's fast accurate and the data very easily manipulated in Word, to generate complex emails, letters, documents etc.

But it has limitations and rather than use another 'hand made' solution, it makes sense to use a more comprehensive tool - CRM.

However Outlook, and it appears CRM, are very clunky and cumbersome when it comes to anything outside the vanilla, especially providing data to Word to complete merges. And I am not talking about simple name and address, etc.

I cannot find a 3rd part add-in that will do the job, and to be honest wonedr why it would be neccessary to use a 3rd party developed product for a fundemental function of any CRM.

So it seems the only way is to export/dump the data to Excel and manipulate from there.

Surely I cannot be the only one with this issue?

Anyway i am trying to get an understanding of the DB construction so I can apply some lateral thinking to it, because the answers I am getting thus far are inneffective and insufficient.

I welcome your thoughts