Why Filtered Views Are Cool
In this article I want to describe my favorite feature from Microsoft Dynamics CRM 3.0: Filtered Views.
Background
Dynamics CRM is built on top of a complex SQL Server database. The CRM Platform component was designed to manage access to the underlying SQL Server database, providing an API layer, security services (permissions, roles, etc), and some (but not all) of the CRM business logic.
It has always been possible to access the raw SQL Server database directly, but not as an ordinary user. In CRM 1.0 and 1.2 you had to be the SQL Server administrator, and as we all know, administrators can do as much harm as good. But there was a lot of value to be had from direct access to the raw database, especially if you were a CRM 1.0 or 1.2 user frustrated by the scalability of the reporting solution we had at the time. If you wanted a fast, simple report on 100,000 contacts, the only really reliable way was to query the SQL Server database directly. We even went so far at one point as to write up a draft set of instructions for desperate users, but looking at all the problems in black and white just made it clearer that direct access to the database was a bad idea.
Here’s a sample of some of the problems:
– The biggest problem was security. Accessing the CRM 1.0 or 1.2 databases directly circumvented all the access controls managed by the CRM Platform. Applications had to run in an elevated privilege mode, and provide all the logic for making sure that end-users did not gain access to data that they should not have been able to access.
– Dynamics CRM is a metadata-driven system, and direct access to database views and tables without reference to the appropriate metadata could cause nasty problems. For example, the visibility of data attributes is controlled by a metadata value called ValidForReadAPI. Attributes with ValidForReadAPI=0 in the metadata are not intended to be used by applications, and are never returned through CRM Platform APIs.
– The CRM 1.0 and 1.2 database views were littered with “magic numbers”. Picklist values are saved as numbers; people using direct access to the CRM database needed to query the StringMap table to figure out how the number saved as the picklist attribute value mapped to the string that the user selected through the CRM UI. The same “magic numbers” problem applied to State and Status values on objects, and ActivityTypeCode values on Activity objects. (The CRM 1.0 and 1.2 database only had one Activity view. Application authors had to use the ActivityTypeCode value to distinguish between Activity types: 142 for Appointments, 136 for Faxes, etc.)
– A particularly important “magic number” that people had to address when they used direct database access in CRM 1.0 and 1.2 was the DeletionStateCode value. Records with DeletionStateCode=2 were marked for deletion by the now-defunct Deletion Service, and had to be handled appropriately.
– It is common, and good, practice to store date/time data as UTC, and the CRM system follows this convention. Unfortunately, this adds an extra level of complexity for application writers using direct access to the CRM SQL Server database, forcing them to struggle with implementing timezone conversions, accounting for daylight saving time, etc.
– The CRM metadata and the CRM Platform both provide insulation between the CRM database and applications, shielding application developers from the impact of implementation changes under the covers. But application authors who accessed the CRM 1.0 and 1.2 databases directly faced the full force of the many hugely beneficial changes that went into CRM 3.0 database.
Filtered Views
All these problems are addressed in Dynamics CRM 3.0 through Filtered Views, opening up a world of new application possibilities.
In a nutshell, Filtered Views are SQL Server views that do wonderful things for the application developer needing to get access to data from the CRM system:
- Filtered Views know about your CRM security permissions – which objects you are able to access and what you are allowed to do with them.
- Filtered Views encapsulate all the standard table joins necessary to make sense of a CRM object: base tables to extension tables (for custom attributes), picklist values to strings, etc.
- Filtered Views exist for all the user-visible CRM objects, including separate views for each activity type.
- Filtered Views work for custom entities.
Let’s look at some examples from real life. These examples are taken from the application that the CRM development team in Redmond uses to manage its development projects. The application uses lots of data that we store in our internal CRM system, which has been extended with custom entities for project tracking.
Here’s what you see if you take the SQL Query Analyzer (SQL Server 2000 version) and point it at a Dynamics CRM 3.0 database. You’ll notice that there are a lot of views with names like “dbo.FilteredAccount” and “dbo.FilteredContact”. These are Filtered Views.
If we look a bit further down the list of views we come across a Filtered View for the Area custom entity. The default prefix for CRM custom entities and attributes is “new_” (the prefix can be customized), so the Filtered View for the custom Area entity is called “dbo.Filterednew_Area”, and custom attributes (which show up as columns in the SQL Server view) are called “new_areaid”, “new_name”, “new_subproductid”, etc.
So if you wanted to list all the Accounts that have given permission for contact by phone, your SQL query would look like this:
select name, telephone1
from dbo.FilteredAccount
where donotphonename = ‘Allow’ and telephone1 is not null
To understand this query, we need to look a bit deeper at how picklists are handled in Filtered Views. Here are the attributes that record whether to send emails to the account, make phone calls, etc.
These attributes come in pairs, one ending with the suffix “name”: “donotphone” and “donotphonename”, for example. Boolean values are treated in Dynamics CRM like two-value picklists, so every Boolean attribute is represented by both a SQL bit attribute, which can have the values 0 and 1, and a SQL string attribute that holds the string representation of the picklist value. When donotphone=1, donotphonename=”Do Not Allow”, and when donotphone=0, donotphonename=”Allow”.
So we could have written the query like this instead, but the original version using donotphonename is easier to understand.
select name, telephone1
from dbo.FilteredAccount
where donotphone = 0 and telephone1 is not null
Creating queries that get data from related entities is also straightforward. Here’s a query that joins two custom entities, Workitem and WorkitemRemaining. The WorkitemRemaining entity is used to record a history of remaining efforts for development workitems. The query returns the workitems and work estimates for the M1 milestone, along with the owners and types of the workitems.
select
wir.new_name as [Workitem Name],
wi.owneridname as [Owner],
wi.new_typename as [Workitem Type],
wir.new_estimate as [Work Remaining Estimate]
from Filterednew_WorkitemRemaining as wir
inner join Filterednew_Workitem as wi
on (wir.new_parentworkitemid = wi.new_workitemid)
where
wir.new_milestoneidname = ‘M1’
order by wi.owneridname
A final cool nuance of Filtered Views. Take a look at the columns for the Area custom entity, and notice the “createdonutc” and “createdon” columns. The first column is the date/time that the Area record was created, saved as a UTC (Universal Time Coordinated) value, and the second column is the same date/time converted to the local timezone.
Excel Queries
The ability to make well-behaved queries against the CRM database unlocked lots of new functionality in Dynamics CRM 3.0.
The following Advanced Find query returns the same results as the first SQL sample we looked at above.
Click on Find, then on the Export to Excel button on the results pane taskbar, and elect to create a Dynamic Workbook. The results of the query are displayed in an Excel spreadsheet that can be refreshed on demand. If you go into Excel and examine the underlying SQL query (Data à Import External Data à Edit Query to bring up the Microsoft Query tool, then press the SQL button), you’ll get something this:
select account.name ,account.telephone1 ,account.accountid from FilteredAccount as account where (account.donotemail = 0 and account.telephone1 is not null ) order by account.name asc
Yes, it’s just a slightly more verbose version of our first sample!
If you are familiar with creating ODBC data sources, you can create a connection to your CRM SQL Server database through the ODBC Data Source Administrator applet (accessed via the Windows Control Panel à Administrative Tools). You’ll need to know the name of your CRM SQL Server, and the name of your root Business Unit. The CRM database on the SQL Server will have a name derived from the root Business Unit: if your root Business Unit is “Microsoft CRM Business Unit”, your CRM database will be Microsoft_CRM_Business_Unit_MSCRM.
Once you have successfully created an ODBC data source for your CRM SQL Server database, the world is your oyster! Try the following:
Start up Excel with a blank worksheet. Click on Data à Get External Data à New Database Query. Select the CRM data source you just created. The Filtered Views will appear in the list of available tables.
Select the columns that you wish to display, and the filters you want to apply:
In a few more clicks you’ll be importing data into Excel directly from the CRM SQL Server database, and if you’re familiar with how to edit queries using the Microsoft Query tool, you can start building complex multi-entity queries.
Look out for future articles that show how to use similar techniques to get CRM data into Microsoft Office Access.
– Charles Eliot
