Scenario: Client has BCS wired up directly to SQL Server (stored procedure) to ReadList and ReadItem. Search crawls the BCS external content type nightly. The stored procedures return one-to-many data as delimited text in a single field (think Location field, with potentially each record having multiple Cities listed, each City separated by semi-colon character).
We'd like to display each individual city as an individual refiner in a search page, such that, for example, a value of "Miami;New York;Los Angeles;" would appear as 3 separate refinement values. Selecting either refinement value would return this particular entry as a result.
I see 3 possible options to this:
Manipulate the properties using Content Enrichment sort of like this: Refiners for multi-valued managed properties in SharePoint - this is current approach, though there are lots of moving parts and deployment dependencies.
Make use of a custom display template for the refiner. This looks like it may work, but haven't validated (I've done similar approach for customizing multi value selects in refiners before): https://hyankov.wordpress.com/2014/12/15/sharepoint-2013-refiner-multi-value-contains-instead-of-an-equals/
Modify the BCS to call .NET assembly or WCF service instead of going directly against SQL Server stored procedure (flat results). This would more of a bigger change, but might allow for easier future growth.
My questions are:
A. Am I missing an option to solve for this? B. Do you have a preference on approach? C. Can you speak to using any of these with your experience and have any comments/considerations for the approach?
TIA!