Optimizing Database Storage: A Case Study on Cleaning the POA Table for Significant Cost Savings

Introduction

Microsoft Dynamics 365 is a powerful platform that enhances operational efficiency by managing and optimizing field service activities. Central to its functionality is the intricate relationship between various database tables, which govern how data is accessed and secured. Each time a user creates an activity within the system, specific access level permissions and unique identifiers (IDs) are generated. While these permissions are crucial for maintaining security and ensuring appropriate access rights, they often accumulate over time, leading to potential inefficiencies.?

One of the challenges faced by organizations utilizing Microsoft Dynamics 365 is the failure of standard cleanup jobs to reset or remove these access level permissions and IDs. This oversight can result in bloated databases, which not only consume valuable storage space but also drive up operational costs. Therefore, understanding how to effectively manage and reset these permissions is essential for maximizing the platform's efficiency and minimizing unnecessary expenditures. This article explores strategies for optimizing Dynamics 365 by addressing the cleanup of access level permissions, ultimately leading to significant cost savings for organizations.?

Why Microsoft Dynamics 365 Development Can Save Money for Customers

In-house development of Microsoft Dynamics 365 can significantly save money for customers by offering tailored solutions to optimize database management. Specifically, focusing on the cleanup of unwanted or obsolete log data within critical database tables—such as the Principal Object Access (POA), Activity Pointer, and Activity Party tables—allows organizations to reclaim valuable storage space.

Currently, many businesses incur unnecessary costs by purchasing additional storage to accommodate the growing volume of these database tables. However, with a dedicated development effort, companies can implement unique strategies to reset or clear out these excess data points. This not only frees up storage space but also enhances system performance, reducing the need for constant expansions.

By investing in small-scale, targeted development initiatives, businesses can address the inefficiencies associated with data management in Dynamics 365. This proactive approach not only leads to immediate cost savings but also fosters a more efficient and responsive database environment, ultimately benefiting the organization's bottom line.

Features of MDFS That Contribute to Cost Savings?

One of the standout features of Dynamics 365 is its ability to manage complex relationships between entities effectively. However, many organizations create custom entities without fully understanding the implications of cascading relationships and access configurations. This oversight can lead to significant inefficiencies that not only bloat the database but also incur unnecessary costs.?

When activities trigger in Microsoft Dynamics 365, they often generate a multitude of access IDs and Principal Access Object IDs. These identifiers accumulate in the database, creating a backlog of stored data linked to user interactions with specific records. If users have direct access to a record, the inherited access from these cascading relationships becomes redundant. This redundancy can lead to excessive database storage requirements and increased operational costs, as the system continues to log and maintain these unnecessary access permissions.

To mitigate these issues, it is essential to implement thoughtful entity relationships and access configurations from the outset. By ensuring that custom entities are correctly configured to prevent the creation of superfluous access IDs, organizations can significantly reduce the amount of data stored in their databases.?

Moreover, adopting alternative arrangements—such as simplifying access structures or using more efficient permission settings—can further minimize the need for expensive database resources. This proactive approach enhances system performance and translates into substantial cost savings for businesses, allowing them to allocate resources more effectively and focus on their core operations.

In summary, by leveraging MDFS's relationship management capabilities while paying careful attention to the configuration of custom entities, organizations can avoid unnecessary data accumulation and associated costs, ultimately fostering a more efficient and cost-effective operational environment.

Case Studies: Financial Benefits from MDFS Implementation

One notable case study involves a business that utilized Microsoft Dynamics 365 with Customer Engagement, managing a substantial customer base with multiple campaigns and gift-related initiatives. This organization faced significant challenges due to its expansive database, which had accumulated over 600 GB of storage consumption and approximately 53 million rows in the Principal Object Access table.

Recognizing the inefficiencies and costs associated with such a large database, we implemented targeted changes to optimize their MDFS instance. The focus was on refining the relationships between entities and addressing the configuration of access permissions. By cleaning up redundant data and eliminating unnecessary access IDs, we aimed to reduce the overall database size and improve performance.

The results were remarkable. After making the necessary modifications, we projected a reduction of the Principal Object Access table from 123 GB to between 38 and 53 GB. This reduction not only lessened the storage burden but also translated into substantial financial savings for the organization. By decreasing the amount of data stored, the company reduced its costs associated with database storage, allowing them to allocate resources more effectively.

This case highlights how careful attention to database management within Dynamics 365 can lead to significant financial benefits. By optimizing entity relationships and minimizing unnecessary data accumulation, organizations can achieve operational efficiencies that directly impact their bottom line, demonstrating the value of investing in MDFS development and management.

Comparison with Traditional Methods or Other Solutions?

Traditional methods of data management often rely on generic cleanup jobs that do not adequately address the specific needs of organizations using Microsoft Dynamics 365. These methods can overlook the complexities of entity relationships and the implications of cascading permissions, leading to continued data bloat and increased storage costs.

In contrast, a tailored approach that involves in-depth analysis and targeted cleanup strategies can significantly enhance database performance. By focusing on specific areas, such as the POA table and other critical data structures, organizations can streamline their operations and reduce reliance on expensive storage solutions.

This article is aimed primarily at users of Microsoft Dynamics 365, particularly those engaged with Customer Engagement and CRM functionalities. The target audience includes:

- Business Owners: Individuals looking to optimize their operational costs and enhance the efficiency of their systems. They are interested in understanding how to leverage MDFS to improve their bottom line.

- IT Professionals: Technical experts responsible for managing and maintaining Dynamics 365 implementations. They seek insights into best practices for database management and optimization, as well as strategies to streamline workflows.

- Database Managers: Professionals tasked with overseeing data integrity, storage optimization, and performance within the Dynamics 365 environment. They are keen on learning how to effectively manage database relationships and reduce unnecessary data accumulation.

By addressing the unique needs and challenges faced by these groups, the article aims to provide actionable insights that can lead to significant cost reductions and improved operational efficiency within their organizations.

Findings and Technical Analysis on Reducing Principal Object Table Size in Dynamics 365?

This practice was conducted on a live Microsoft Dynamics 365 instance to evaluate potential methods for reducing the size of the Principal Object Access (POA) table. The objective was to identify strategies for optimizing database performance and minimizing associated costs.

Initial Analysis

The research commenced with a detailed analysis of the POA table and its relationships with custom entities. Utilizing the Advanced Find feature, we were able to identify which entities had POA ID rows created and the respective timestamps of those creations.

Upon extracting the total records from the POA table using Advanced Find, we discovered an overwhelming volume of over 53 million rows. Unfortunately, Advanced Find lacked the capabilities to effectively filter and analyse these records for targeted cleanup.

Transition to FetchXML

To improve our data filtering capabilities, we transitioned to using FetchXML. We developed scripts designed to filter out irrelevant or unwanted data. Below is an example of a FetchXML script that can be executed to initiate the filtering process. It is important to run these scripts in lower environments first to ensure accuracy and prevent potential issues.

```xml

<fetch returntotalrecordcount="true">

? <entity name="principalobjectaccess">

??? <attribute name="accessrightsmask" />

??? <attribute name="changedon" />

??? <attribute name="inheritedaccessrightsmask" />

??? <attribute name="objectid" />

??? <attribute name="objecttypecode" />

??? <attribute name="principalid" />

??? <attribute name="principalobjectaccessid" />

??? <attribute name="principaltypecode" />

??? <filter>

????? <condition attribute="changedon" operator="lt" value="20xx-xx-xx xx:xx:xx" />

??? </filter>

? </entity>

</fetch>

```

Focus on System and Custom Entities?

Following the initial filtering with FetchXML, we directed our attention to both system and custom entities. By examining these entities, we identified that the accessrightsmask attribute contains ID rows that grant direct access to users. This was reflected in the conditions applied in the script.

To further refine the data, we modified the FetchXML script to include conditions based on the accessrightsmask. This allowed us to specifically target rows where access rights had been granted or denied:

```xml

<fetch returntotalrecordcount="true">

? <entity name="principalobjectaccess">

??? <attribute name="accessrightsmask" />

??? <attribute name="changedon" />

??? <attribute name="inheritedaccessrightsmask" />

??? <attribute name="objectid" />

??? <attribute name="objecttypecode" />

??? <attribute name="principalid" />

??? <attribute name="principalobjectaccessid" />

??? <attribute name="principaltypecode" />

??? <filter>

????? <condition attribute="accessrightsmask" operator="eq" value="1" />

??? </filter>

??? <filter>

????? <condition attribute="changedon" operator="lt" value="20xx-xx-xx xx:xx:xx" />

??? </filter>

? </entity>

</fetch>

```

Identifying Targets for Cleanup

At this point, it was crucial to determine which records to target for deletion or reset. The key focus was on rows associated with the inheritedaccessrightsmask, as these IDs maintain relationships with accessrightsmask IDs that store access permissions linked to specific entity records.

The relationship can be summarized as follows:

- POA ID Unique → Object ID Unique → Principal ID Common (for the same object level) → Activity Party → Inherited Access Right Mask → Entities

This hierarchy illustrates how access permissions are structured and how they can be targeted for deletion.

Limitations and Cleanup Strategy

It is important to note that there are limitations in identifying and deleting specific POA IDs. The system does not allow direct deletion or resetting of POA ID rows; such actions can only be performed via Microsoft’s automated garbage collection tools.

Thus, our strategy focused on filtering records by custom entities. Since system entities cannot be reset or modified due to their integration with parent relationships, we concentrated on custom entities created for specific business requirements.

To analyse relationships for custom entities, we accessed the Power Apps Admin Portal under the Tables section. If cascading relationships were identified, they should be set to Cascading None, which prevents the system from generating unnecessary access masks.

Data Analysis with Power BI

To facilitate deeper analysis, we utilized Power BI. An OData call was constructed to fetch records from the POA table, allowing for a more detailed examination of the data:

```plaintext

OData query: = OData.Feed("https://<yourorganisationURL>.crm6.dynamics.com/api/data/v9.2/principalobjectaccessset?$count=true&$filter=inheritedaccessrightsmask+ne+0+and+accessrightsmask+eq+0&$top=500000", null, [Implementation="2.0", MoreColumns=true])

```

Given the high volume of records, we limited the fetch to 500,000 to ensure timely retrieval and analysis.

Targeting Activity Pointer Entity

To focus our efforts, we initially targeted the Activity Pointer entity, which has the activity type code 4200. By filtering the POA table for this entity, we identified thousands of inherited access mask IDs, which were prime candidates for resetting.?

The FetchXML for filtering Activity Pointer records is provided below:

```xml

<fetch>

? <entity name="principalobjectaccess">

??? <attribute name="principalobjectaccessid" />

??? <attribute name="changedon" />

??? <filter>

????? <condition attribute="objecttypecode" operator="eq" value="4200" />

????? <condition attribute="principalid" operator="eq" valueof="principalid" />

????? <condition attribute="changedon" operator="lt" value="2019-01-01 11:31:16" />

??? </filter>

? </entity>

</fetch>

```?

Conclusion: Resetting Inherited Access Rights?

To effectively reset inherited access rights, we recommended utilizing an API call to create a system job that revokes inherited accesses from activity pointer permissions. The following script can be employed for this purpose:

```plaintext

https://Yourorganisation.crm6.dynamics.com/api/data/v9.0/ResetInheritedAccess(FetchXml=@fetchXml)?@fetchXml='<fetch> <entity name="principalobjectaccess">

<attribute name="principalobjectaccessid" />

<filter>

? <condition attribute="objecttypecode" operator="eq" value="4200" />

? <condition attribute="principalobjectaccessid" operator="eq" value="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" />

</filter>

</entity>

</fetch>'

```

Backup Recommendation: Prior to executing any deletion or reset operations, it is critical to ensure a full backup of the data to safeguard against unintended deletions and to maintain a record of all previous states for verification.

By following this structured approach, organizations can effectively manage their POA table sizes, optimize performance, and achieve significant cost savings in their Dynamics 365 environments.