CRM 2015 – Why filtered views are useful

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.

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

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

AccountBase

AccountExtended

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

Why Filtered Views Are Cool

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.

Filtered views in Microsoft Dynamics CRM

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.

• 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.

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 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

Denormalisation

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

In computing, denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.^[1][2] In some cases, denormalization is a means of addressing performance or scalability in relational database software.

This means for lookup fields the filtered view contains two fields

• Guid
• Name

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

CRM 2011/CRM 2013 Filtered View Return No Data

 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.