Using escape character (\) to retain quotes in Essbase dim builds

Hello fellow Essbasers, I thought I’d pass on a little tip for an issue you may come across when creating an Essbase dimensional build. Occasionally you may need to build outline members that require quotes in one of the dimensional build fields (typically involving member formulas).

Let’s take the following source data as an example.

As you can see, my source metadata has double quotes in the values for the member formula. For this example, assume that we want to build these members into our outline with the double quotes intact. We create our (SQL) dimensional build rule like normal and retrieve sample data into the load rule to insure the data is pulling correctly from the SQL source. What do we find? Ta-da, no quotes!

For reference, the record shown in the top of the load rule editor reflects what is actually in the source table. The record shown at the bottom reflects the (meta)data and its format as it will be loaded to Essbase. Note the quotes missing from the formula column. If we run this build, we get errors as the members referenced within the member formula have spaces in the name and must be enclosed in double quotes.

Just to confirm that we’re losing quotes on the load, let’s run the dim build and check the results.

As we can see, the outer quotes are being truncated by Essbase during the load. As a result, the member formula does not validate and the formula will be disabled on this member.

Relax, it’s a simple fix. For some reason, Essbase likes to remove quotes during dimensional builds, seemingly viewing them as special characters. Luckily, we can invoke the mighty slayer of special characters, yes, yes I’m talking about this fella here -> \, the mighty backslash. If we update our source as shown below and re-run our dim build, the quotes will be retained in the formula as we desire.

Here’s a screenshot of the refreshed outline after “escaping” the outer quotes with backslashes in the SQL table and re-running our dim build.

We can now retain quotes as appropriate when performing dim builds in Essbase. Woohoo! Hope this little tip saves some intrepid Essbase developer a little time and sanity. Cheers!

This post is Ragnar approved.