In this blog post I'm going to describe my experience building a reporting solution (Inmon's Oper Mart) with Microsoft Dynamics CRM database as a source. First challenge was to find a way to map CRM forms to their tables.
My first approach was to google a lot which didn't help much, then buy a book which gave a better understanding of CRM but still didn't help much. Most critical information was the fact that the Dynamics CRM is Meta Driven so there is interface that allows to map Forms fields to tables and the information is stored in tables that belong to a schema called "MetaDataSchema".
This information helped me a lot and with Entity and Attribute tables I was able to implicitly do the mapping but I was still missing tables that would precisely tell me this is the label on the form and this is the table field and after experiencing an issue where field was renamed (due to data type change) and having several similar fields in table where only one was live I decided it is time to uncover the mistery of CRM MetaDataSchema tables and find a reliable way to do the mapping.
This article will give you a number of queries that helped me with my work however bear in mind they might be wrong as I'm still learning more about the CRM so use it at your own risk.
Core Tables and Design
hmmm coming soon.
So far I have this query to help me with my mapping:
SELECT e.BaseTableName, e.Name, l.Label, a.PhysicalName
FROM MetadataSchema.LocalizedLabel AS l
MetadataSchema.Attribute AS a
ON l.ObjectId = a.AttributeId
MetadataSchema.Entity AS e
ON a.EntityId = e.EntityId
WHERE l.OverwriteTime = 0 AND l.ComponentState = 0
AND l.ObjectColumnName = 'DisplayName'
AND a.OverwriteTime = 0 AND a.ComponentState = 0
AND e.OverwriteTime = 0 AND e.ComponentState = 0
Below is table with partial Join
dbo.StringMapBase AS sb
on [ForeignKeyFromParentTable] = sb.AttributeValue
AND sb.AttributeName = '[Type Field Name]
AND sb.ObjectTypeCode = [Type Table ID]
Important: You may get the same Attribute Name for a different Object Type (Table) so IDs and Value may match however this is nor reliable and it should be treated as separate lookups (especially when you do UNION that I did :)
After this project I found out about Dynamis CRM Filtered Views, not sure why my initial Google search didn't point me to that!