EPM patch 11.1.2.2.500 Breaks EPMA: com.hyperion.awb.web.common.DimensionServiceException

EPMA can be frustrating at the best of times but this was the first time I've had an issue with patching. The issue only occurs with some environments, in this example the patch worked fine on DEV but broke the PROD environment.

The error occurs when applying the 11.1.2.2.500 super patch to EPM 11.1.2.2.300. The first time you start the EPMA services everything will work as normal. However, when the EPMA dimension service starts it executes some SQL on the database which means that the second time you start the EPMA dimension service it won't start correctly.

You'll get this error message in the EPM Workspace when selecting the EPMA Application or Dimension components:

Requested Service not found 

Code: 
com.hyperion.awb.web.common.DimensionServiceException 
Description: An error occurred processing the result from the server. 

A look at the DimensionServer.log reveals the following:

Failed 11.1.2.2.00 FixInvalidDynamicPropertyReferences task at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy) 
...
An error occurred during initialization of the Dimension Server Engine:  startIndex cannot be larger than length of string.
Parameter name: startIndex.    at Hyperion.DimensionServer.LibraryManager.FixInvalidDynamicPropertyReferences()
   at Hyperion.DimensionServer.Global.Initialize(ISessionManager sessionMgr, Guid systemSessionID, String sqlConnectionString)

The same error can also occur when applying 11.1.2.3.500. Oracle released 11.1.2.3.501 to fix the issue, unfortunately they didn't release a patch for the 11.1.2.2 code line.

Thankfully there is a fix. Run the following SQL on your EPMA repository and your EPMA Dimension Server will start successfully again:

UPDATE DS_Property_Dimension 
SET c_property_value = null 
FROM DS_Property_Dimension pd 
JOIN DS_Library lib 
ON lib.i_library_id = pd.i_library_id 
JOIN DS_Member prop 
ON prop.i_library_id = pd.i_library_id 
AND prop.i_dimension_id = pd.i_prop_def_dimension_id 
AND prop.i_member_id = pd.i_prop_def_member_id 
JOIN DS_Dimension d 
ON d.i_library_id = pd.i_library_id 
AND d.i_dimension_id = pd.i_dimension_id 
WHERE 
prop.c_member_name = 'DynamicProperties' AND 
pd.c_property_value IS NOT NULL AND pd.c_property_value = '';