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 18.104.22.168.500 super patch to EPM 22.214.171.124.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
Description: An error occurred processing the result from the server.
A look at the DimensionServer.log reveals the following:
Failed 126.96.36.199.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 188.8.131.52.500. Oracle released 184.108.40.206.501 to fix the issue, unfortunately they didn't release a patch for the 220.127.116.11 code line.
Thankfully there is a fix. Run the following SQL on your EPMA repository and your EPMA Dimension Server will start successfully again:
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
prop.c_member_name = 'DynamicProperties' AND
pd.c_property_value IS NOT NULL AND pd.c_property_value = '';