Anatomy of the CRM 4.0 Filtered View

Introduction

This post explains the way that filtered views work in a CRM 4 database, and the ways in which this impacts object sharing in CRM, and can have repercussions for the way in which you decide to organise your database hierarchy.
Filtered Views can be used directly in queries on a CRM database SQL Server, and are particularly useful when building customised reports through SQL Server Reporting Services. By querying the Filtered View in the report, and then installing the report in CRM, the user executing the report gets to see only the entities to which they have permission.

Example

Imagine a situation where I have two Team Leaders: A and B, and a number of Agents A1, A2, B1, B2 etc. A Team Leader role is created which has no rights to the Agent entity. A Supervisor then shares the agents A1, A2…An to Team Leader A and B1, B2…Bn to Team Leader B. A custom report is then created which accesses the Agent data by means of the Filtered View, and this is shared with Team Leaders A and B.
The Team Leaders can run the report, and will only see the data for the agents to whom they have been explicitly given access to: the report fully respects the rights granted in CRM, even though the CRM Web Service is bypassed in the generation of the report. This is very powerful, and means reports can be shared to users safe in the knowledge that the database is restricting the data that the report will contain.

Object Access Steps

When CRM decides if a user as rights to see an object in CRM, it makes a number of tests, of which at least one must come back positive:

-              Does the user own the object?
-      Does the role of the user allow access to the object at Business Unit level, and is the user in the same Business Unit?
-          Does the role of the user allow access to the object at Child Business Unit level, and is the user in a Parental Business Unit?
-          Has the object been explicitly share with the user?

To check the privilege granted to the object by the user, the function fn_GetMaxPrivilegeDepthMask is called, passing the Entity Type Id as parameter. This in turn calls the function fn_FindUserGuid to find the GUID of the user querying the filtered view using the SUSER_NAME() SQL function and joins it to the Roles and Privileges table to check access rights.
The column PrivilegeDepthMask in the RolePrivileges table is returned, which contains a bit mask indicating the level of access to the object permitted:

0x88 – Child Business Unit
0x44 – Global Access
0x22 – Business Unit

If none of these permissions are set, then a join is made to the Principal Object Access table. This table contains one row for each object shared with each user, so 10 objects shared with 3 users will have 30 records in this table. Remember also that if the object has a Parental Cascading relationship with child objects, that sharing an instance of an object to a user will also create an entry in the POA table for every child object also.

Filtered View Query

The Filtered View is now ready to be compiled. The pseudo code is:

SELECT {entity attribute list}
WHERE
PrivilegeRight = Child business unit AND Object Owner BU is in child business unit as Curernt User
OR
PrivilegeRight = Global
OR
PrivilegeRight = Business unit AND Object Owner is in same business unit as Current User
OR Id IN (SELECT Objects in POA for Current User)

Conclusions

So what conclusions can we draw from all of this? The main part is limiting the records in the Principal Object Access table. The more records that are in this table, the greater the overhead on the Filtered View query. Sharing too many objects will rapidly become a drain on database performance and will result in system slowdown. The rule of thumb is that on a reasonable system, the number of records in this table should be less than 5 million, but I would suggest an upper limit of no more than 1 million, and to aim for less than 100000 if possible. Use sharing sparingly and be aware that cascading permissions could be adding many records to the POA table.

When querying data via the CRM web service, the Filtered View is not used, but a very similar query is used instead, with very similar characteristics including the join to the POA table. Reducing records in the POA will have benefits here too.

The use of Business Units to partition data can reduce the number of records which require sharing. Use them wherever possible, providing they are sensible to reduce the need to share objects, and your database will perform better as a result.

Another way to reduce the impact of Filtered View queries is to query the list of parameters for your report using the Filtered View, but run the report directly from the entity view, once you are sure that the report input parameters will always restrict the output to those objects the user should be able to see. Default/empty parameters can trip you up here if you’re not careful.

Don’t be scared of sharing objects – it can be the only way to solve certain problems – but know the impact of what will happen to the database from those shares and then you can make the best choice.

Pivoting Columns to Rows in SQL Server

I've found this trick useful, and maybe you will too.

I was given a table with a number of columns which needed to be turned into a vertical table with one row for each column as name-value pairs.

i.e. changing:
Title   FirstName   Surname
------- ----------- --------
Mr      John        Smith

into:
NamePart    NameValue
----------- ---------
Title       Mr
FirstName   John
Surname     Smith

To solve this, I created a table with one column and 3 distinct rows: one for each column I wanted to split out, and included it unjoined in the query. I then used a CASE to return the appropriate column for each value in the temporary table, and sorted the output by the temporary table value to keep the ordering. Of course this only works if all the columns have the same data type, otherwise they will have to be CAST to a common type.

The example makes more sense, so here is the code:


declare @Tab table (Id INT IDENTITY(1,1), Title VARCHAR(30), FirstName VARCHAR(30), Surname VARCHAR(30));

insert into @Tab values ('Mr','John','Smith');
insert into @Tab values ('Dr','David','Jones');
insert into @Tab values ('Mrs','Sally','Brown');


select Id, Title, FirstName, Surname
from @Tab;


with P (Col1)
as
(select Col1=1
union all
select Col1=2
union all
select Col1=3)

select T.Id, 
       case P.Col1
when 1 then 'Title'
when 2 then 'FirstName'
when 3 then 'Surname'
  end as [Name Part],
  case P.Col1
when 1 then T.Title
when 2 then T.FirstName
when 3 then T.Surname
  end as [Name Value]
from @Tab T, P
order by T.Id, P.Col1;

Output:


Id          Title        FirstName     Surname
----------- ------------ ------------- ------------------------------
1           Mr           John          Smith
2           Dr           David         Jones
3           Mrs          Sally         Brown


Id          NamePart  NameValue
----------- --------- ------------------------------
1           Title     Mr
1           FirstName John
1           Surname   Smith
2           Title     Dr
2           FirstName David
2           Surname   Jones
3           Title     Mrs
3           FirstName Sally
3           Surname   Brown