If you have been a CRM developer for a length of time then you will certainly come across filtered views and they are useful and definitely something you should learn more about, particularly if you are going to be writing reports or accessing the CRM database directly.

The CRM database can be viewed and the data used but never changed directly – Hosk

This quote means you should never change data directly on the SQL table, below are a few key reasons why
  • Direct changes to the SQL table are unsupported, this will result in Microsoft offering no or limited support if your CRM installation goes wrong.  You can read more about why you should create unsupported changes here
  • When you use the CRM SDK to change a value it could update other fields and values, direct changes to the SQL will not
  • There is no validation on the SQL tables,  you could easily put in an incorrect value
  • If you write SQL stored procedure directly agaisn’t the CRM database tables this could stop working when a rollup or service patch is applied to your CRM organisation

You should be of the opinion directly changing values on the Microsoft CRM database is a big no no

 

The truth is you should think long and hard about access fields directly from CRM tables because these might change in future releases, rollups and service packs.

What are you meant to do if you want to read your CRM data using SQL?#

Microsoft have created a filtered view for each database table and Microsoft will make sure these work even after future releases, rollups and services packs.

Start with the CRM database

The database has an odd status for CRM developers, we all know it’s there and in many ways Microsoft Dynamics CRM is at its core just a database for customers to store and use their precious data.
CRM developers know there is an SQL server database there and you may have installed CRM or written some SSRS reports using the database.
CRM developers are taught all interaction with the CRM data should be done using the CRM SDK or OData/Rest queries if using Javascript.
Basically no direct access to the database, this is highlight if you look at this great article on MSDN – Microsoft Dynamics CRM Extensibility Architecture, below I have taken the diagram.
Microsoft avoid tight coupling and unmanaged complexity (one of the deadly sins of complex code – read more The problems with complex code and complex CRM Customizations) by limiting access to the database to
  • Data Access Platform
  • Filtered SQL Views
Interestingly CRM online doesn’t allow any access to filtered views, the reason (obvious when you think about it) isbecause you do not have access to the CRM database, which is quite a limitation to the reporting options for CRM online deployments because they will all have to be fetchXML.
In the (good) old days (warning Hosk you are starting to sound like an OLD CRM man) of CRM 2011 and versions of CRM below the CRM database use to have a base and an extended table for entity.  The base table had the out of the box fields and the extended table had all the new custom fields added.
so for the account entity
AccountBase
AccountExtended
Either in CRM 2013 or CRM 2015 the two database table structure was changed to one database table.  I’m not sure what happens when you upgrade a database but I’m guessing the scripts in the background might merge the database tables, which is another warning why you shouldn’t write SQL directly against the database tables.   I don’t have SQL access to any CRM databases at the moment so I’m not sure what happens, please leave a comment if you do know.

What are filtered views?

Filtered views have been around since the days of CRM 3, I’m happy to say I have never had to use a version of CRM below version 4.  I found this article written in 2006
Whenever you want to learn something about CRM development the best and first place you should start looking is the CRM SDK.  I tried this and got back so many results I couldn’t work out where to start.
Luckily you can also search on-line, this link has a list of all the CRM entities, the name of the CRM table and the name of the Filtered view
Entity schema name Entity type code Report view name Description
Account 1 FilteredAccount Business that represents a customer or potential customer. The company that is billed in business transactions.
AccountLeads 16 FilteredAccountLeads Represents the intersect table for the accountleads_associationrelationship.
The filtered views have the same name as the CRM table but with the word Filtered prefixing it.  Not all Entities have filtered views because some entities are system entities
Filtered views will be automatically created for any custom entities you create.
For CRM 2011 the filtered view was a view containing all the fields in the base and the extended table.
CRM filtered views exist for individual activity types e.g. (email, task, letter, etc)

Why are Filtered views cool

 
You are probably wondering what is so special about filtered views, it sounds just like a view of the table.
Microsoft has created a create article called Use SQL and filtered views to retrieve data for reports, this has many of the benefits of Filtered views listed below

The benefits of Filtered views are

  • Filtered views won’t break after patches, services releases and maybe upgrades
  • Security is embedded in the view
  • All the column names are in lower case
  • Filtered views are automatically created and updated for custom entities
  • Datetime fields are held in datetime and UTC
  • Drop down values are held in filtered views

Filtered views won’t break after patches, services releases and maybe upgrades

Microsoft will support the views and the views won’t change after rollups, service patches, upgrades.  This means if you write reports against a filtered view it won’t suddenly break when a rollup or service patch is applied and the report should work if you upgrade the version of CRM (that is a big if).  Microsoft can change CRM tables, so if you

Security is embedded in the view

Filtered views are purposely created for CRM developers to use in their reports.  One of the main benefits is Filtered views use the Microsoft Dynamics CRM security model.   This means the data shown in a report using a filtered view would only contain the data the users can view in CRM.

Filtered views take uses security levels e.g. organisation, business unit, owner and field level and these only show the levels each individual user can see.

If you created a view created directly on the Microsoft Dynamics CRM tables then it would show all the data to all users.

All the column names are in lower case

All the column names in a filtered view are in lower case, so you do not have any problems with case sentivity

Filtered views are automatically created and updated for custom entities

Microsoft is busy in the background and when you create a new entity or add fields to a custom entity or System entity, Microsoft automatically creates a new filtered view for custom entities and adds the new fields to existing filtered views.

Datetime fields are held in datetime and UTC

Filtered views have two datetime fields for every date time field you add.  One DateTime field and one UTC DateTime field.

The DateTime field holds the date time for the users time zone

DateTime UTC holds the time in UTC

Drop down values are held in filtered views

Option sets have two fields in a filtered view, one holds the value the other holds the string representation

One field holds the string value of the option set

Another value holds the number

If you had an option set field accountrating with an option set value set of Gold – 1.

accountrating = 1

accountratingname = “Gold”

Primary Key Field

The primary key field for each entity has  a field in the filtered view.
e.g.  contact filtered view would have the field contactid
My current dealing/problem
Filtered view has more fields it has a date field and date field UTC

Problems with Filtered Views

One of the main problems with filtered views can be performance and they have been known to run slowly.
One of the reasons for the slow speed of filtered views is you cannot apply indexes to the tables. Another reason for poor performance is the filter view connects a number of tables together.
You will quickly know if speed is going to be an issue with the report but the security advantages are definitely worth using filtered views to initially create the report.
If you cannot get your reports with filtered views working this blog has a potential solution
 This forum discusses how to speed up filtered views (there isn’t really anyway)

Summary

Filtered views are a great way to create reports in a supported fashion which won’t break in future releases.  The advantage of the security being applied to the report means your reports won’t show data the user isn’t mean to see.
The major downside is a common problem to all reports e.g. SPEED.  It’s common for reports to try aggregate lots of data and these reports can run slowly when using Filtered views.
The UTC date can be very useful and save you formatting the date
 My advice is to try the filtered view first because the advantages are great but if the report is too slow you have have to consider your options.

Filed under: CRM 2011, CRM 2013, CRM 2015