MS CRM 2011 Principal Object Access Table (POA)
Posting publicly but still under edit, links provided are orginal sources.
You may or may not know the POA (principal object access) table is a table for sharing of records. Basically if you try to access a record that you do not own the system will check the POA and if the permission is there then you can access the record.
http://blogs.msdn.com/b/crminthefield/archive/2010/08/16/excessive-principalobjectaccess-poa-table-growth-in-crm-4-0.aspx
(START BLOG)
I often times work with customers that enter a large number of records inside their Microsoft CRM system. When reviewing their CRM table counts, they sometimes find that they have a larger than expected number of records in the PrincipalObjectAccess table (POA). The POA table is used to provide access to specific records for CRM users, and each record in the POA table represents one CRM object that is related to one CRM user. Records created in the POA table come from one of four ways:
• Share reassigned records with original owner: CRM System Settings
o If this is set to Yes, then records would be added to the POA table whenever an assign takes place. These records will have a value in the AccessRightsMask colum of the POA table.
• Direct sharing: Actions – Sharing
o When users explicitly share a record to another user, a record would be created in the POA table. These records will have a value in the AccessRightsMask colum of the POA table.
• Reparent Setting: Relationship Behavior
o Each entity has relationships with other entities (ex. Account to Case). By default, the Reparent option is set to Cascade All. With this setting, sub records would be shared to the owner of the parent record. For example: Let’s say that User1 owns Account1. User2 has access to Account1 and creates a case underneath Account1. With the out of the box Reparent options, a record would be created in the POA table that would give User1 access to the newly created case. These records will have a value in the InheritedAccessRightsMask colum of the POA table.
• Indirect Sharing
o When sharing occurs through a direct share, assignment, or parenting, if the relationship is set up to cascade the share to child records, additional records will be created in the POA table in order to give proper permissions to the new user for the relevant child records. These records will have a value in the InheritedAccessRightsMask colum of the POA table.
The Reparent setting is what seems to come as a surprise to some customers. Many customers will keep the Reparent setting set to Cascade All. This is a perfectly acceptable setting, but if the majority of your users already have access to the records that are being shared to them through their security role, the records in the POA table may not be providing much of a benefit.
It is possible to change the Reparent setting to Cascade None. In this scenario, records would not be auto-created in the POA table for the owner of the parent record. Building off of the example mentioned above: User1 owns Account1. User2 has access to Account1 and creates a case underneath Account1. With the relationship set to Cascade None, a record would not be created in the POA table for User1.
(END BLOG)
So, how do we determine the amount of sharing within the Pulse system. I have found an XML report for 2011 that will outline the sharing that is happening with the system.
http://sharingsummary2011.codeplex.com/
Also, I believe it would be good to run this script in order to see what types of Sharing is happening within the POA table, this may make the report not necessary but I feel that everything should be on the table.
select ObjectTypeCode, COUNT(ObjectTypeCode) as total
from PrincipalObjectAccess
group by ObjectTypeCode
order by total desc
SAMPLE RESULTS
ObjectTypeCode EntityName total
5 Annotation 3.166.458
4200 ActivityPointer 838.042
112 Incident 415.500
1 Account 38.087
4230 UserQuery 135
9100 Report 104
150 UserSettings 88
8 SystemUser 88
9106 MailMergeTemplate 20
2010 Template 3
4.458.525
More information on the POA table and understanding it, I have not fully explored this yet.
So how do we manage the POA, here are some recommendations.
(START BLOG)
One of the topics of discussion that can come up during the planning phase for a customers CRM implementation is Business Unit structure and sharing, which leads to the PrincipalObjectAccess (POA) table. As the POA table grows in size due to the sharing of records, which can be frequent in environments with a complex Business Unit structure, CRM performance can suffer. Below are some general recommendations that we provide to customers that are anticipating their deployment will have a complex Business Unit structure and/or frequent sharing of records.
• Share only what is needed
o By limiting the amount of sharing that takes place, we will reduce the total number of records in the POA table
o For more on sharing and the POA table see Jon’s post
• Minimize the number of Business Units where possible
o Help reduce the need for sharing records
• Ensure users are placed in the appropriate Business Unit
o Can a user be moved further up the Business Unit hierarchy to give them the necessary access to records in another Business Unit
• Modify Security to allow users to see information outside of their Business Unit
o This will also reduce the need for sharing
• Once a record does not need to be shared any longer, stop sharing it
• Enable the EnableRetrieveMultipleOptimization registry key
o http://support.microsoft.com/kb/2535245
o Enabling this will cause the queries to make use of temp tables
Evaluate splitting TempDB out to its own physically separate RAID array
• Ensure frequent queries that involve the POA table have appropriate indexes in place
Not all of these will be applicable to all deployments but the goal of most of these is to provide customers items to consider while they are planning out their Business Unit structure.
(END BLOG)
