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 not 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 against 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
Best practice is to avoid accessing fields directly from CRM tables, the database structure, tables and fields could all change in future releases, rollups and service packs.
So what is the alternative to accessing CRM data using SQL if you shouldn’t access the data directly?
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.
- Data Access Platform
- Filtered SQL Views
CRM online doesn’t allow any access to filtered views, the reason (obvious when you think about it) is because 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
In CRM 2013 (optional)/CRM 2015 (mandatory) the two database table structure was changed and merged into 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.
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
The best and first place you should start looking for information is the CRM SDK (the Truth). The link below shows the names of the filtered views in CRM.
|Entity schema name
||Entity type code
||Report view name
||Business that represents a customer or potential customer. The company that is billed in business transactions.
||Represents the intersect table for the accountleads_associationrelationship.
- 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.
- In CRM 2011 the filtered view contained 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.
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 filtered views in future versions, rollups, service patches, upgrades. If you write reports against a filtered view it won’t suddenly break when a rollup or service patch is applied, the report should work if you upgrade the version of CRM (that is a big if). Microsoft can change CRM tables but when they do they update the filtered view to work with the changes.
Security is embedded in the view
Filtered views are created for CRM developers to use in reports. Filtered views use the Microsoft Dynamics CRM security model, so 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.
Adam summarises this excellently in his comment
Filtered views enforce security
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 sensitivity
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
Thanks to CRM MVP Adam Vero for pointing this benefit out, he has an excellent blog which you should read here
CRM has added a few neat tricks and columns to the filtered view which makes them more useful than a view which will be supported in future upgrades/releases of CRM.
Mentioned above they included UTC field dates so we know they are already adding value to filtered view by having the same data but already converted, which saves the developer from doing this.
Adam mentions the filtered view has denormalization the data in the view but what does this mean, here is a wiki definition of denormalization
This means for lookup fields the filtered view contains two fields
In most reports you will often want to display the name or the title of the related entity, this usually means you have to retrieve the extra data but in the filtered views they add the name field.
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.
The security model can have significant impact on general CRM peformance and the filtered views performance will be slower if you have a large and/or complex security model. A complex security model could include
- Lots of Teams in different BU’s
- Lots of sharing
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
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.