Clearing the Web Platform Installer’s installer cache

My laptop has two hard drives — a 128GB SSD and a 1TB old school hard drive (taking the place of the DVD drive, which I never used). I love the SSD, but it’s size limits it to the operating system, some important program files, and a handful of documents.

Every now and then, it fills up. I recently found a silent disk space usage offender: Microsoft’s Web Platform Installer. Apparently, the Web Platform Installer (WebPI) caches all installer files to a local folder, and never deletes them. Which isn’t a problem if you have plenty of disk space, but for me, it’s a problem.

The path to the WebPI installer cache folder is “%LOCALAPPDATA%\Microsoft\Web Platform Installer\installers” – where the variable LOCALAPPDATA points to a folder in your user profile directory. For example, on my machine, the full path is C:\Users\demarzo\AppData\Local\Microsoft\Web Platform Installer\installers.

Deleting all the files in this folder is safe, as the WebPI will simply re-download what it needs when it needs it, which should be perfectly fine for most people who don’t make a habit of repeatedly reinstalling the same version of software over and over again. For me, it saved over 1GB of disk space. Which tells me, among other things, I install a lot of software!

Getting Dynamics CRM ObjectTypeCode values via SQL

Lots of tables in Dynamics CRM use the ObjectTypeCode to identify the entity. Sure, we all remember that Account is ObjecTypeCode 1 and Contact is ObjectTypeCode 2… But what about your custom entities, which start at 10,000 and are not guaranteed to be the same in different organizations, even if you have the same solutions installed — how will we remember their ObjecTypeCode values?

You don’t remember them, you query them when they need you. It’s quick and easy if you are on premises and have access to the SQL database. (If you don’t, ask your DBA to create a view with this query and give you rights to it.)

select coalesce(OriginalLocalizedName,name) as DisplayName, Name as SchemaName, ObjectTypeCode
from EntityLogicalView
order by ObjectTypeCode

Much easier than memorizing, and avoids the problems of remembering the wrong number for the wrong organization or deployment. Oops!

How entity relationships can kill performance in Dynamics CRM 2011

Microsoft Dynamics CRM 2011 allows us to specify relationships between entities, and gives us some configuration as to how those relationships work. One of those relationship types is “parental” — which effectively means, “anything that happens to the parent happens to the child.” Therein lies a lot of power — and a lot of risk.

In the environment where I work, we have a lot of entities. Two of those entities are a “Contract” entity (not the out-of-the-box Contract entity, which we renamed to a more appropriate “Service Contract”) and a “Contract Volume” entity. Intuitively, since you can not have Contract Volume without a Contract, we set the relationship type to parental, with Contract being the parent to Contract Volume.

We have thousands of Contract records, and hundreds of thousands of Contract Volume records. The data for these comes from a separate proprietary application. Data is inserted/updated into CRM from this other system using Scribe Insight. The entities are read-only in CRM, so we don’t have to bi-directional synchronization of data. All was well, and we moved data for a couple weeks without issue.

In a recent release, we expanded the functionality of CRM and found the need to customize the owner of the Contract entity to one of three different CRM teams. So, we did what seemed sensible: update the Scribe package to set the owner based on the criteria we came up with. Unfortunately, running this package took down our CRM system within a minute. The database server was overloaded, and SQL blocks were everywhere.

The problem was our relationship. In a parental relationship, any deleting, sharing, and assigning of the parent will affect all of its children, The effect of this is:

  • Deleting a parent will delete all child records.
  • Sharing or un-sharing a parent will perform an identical share or un-share on all child records.
  • Assigning (changing the owner of) a parent will assign (change the owner of) all child records.

Think about the potential impact of this. If a single record has 10,000 child records, and you assign this record to another user, CRM must perform the same operation against all 10,000 child records — as one operation. Add on top of that CRM’s built-in overhead (auditing, principal object access, logging, etc.), and you’ve effectively killed your system, which likely can not keep up with the scope of your request while dealing with other people’s requests.

A better solution for us was to do the following:

  • Change the Parental relationship to a Referential relationships, where there is no cascading, and a delete will simply remove the parent reference, leaving the child an orphan with no parent.
  • Create a cleanup job to delete all the orphaned child records. This can be done via a a Scribe job, a recurring bulk delete job, or manually.

Dynamics CRM has a lot of power, but you have to think about the effect of the choices made, because they very easily can cause unintended consequences — as they did for us recently!