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.

SQL Source
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!

Dim Build

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.

Invalid member formula missing quotes

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.

This pains Captain Picard

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.

SQL source utilizing escape charater "/"
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.

Success! Quotes are retain and all is well.

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.

Ragnar

Pete Strayer

About Pete Strayer

Setting aside aspirations to become a professional bass fisherman or race car driver, Pete Strayer instead opted for the glorious career as an EPM consultant. When he’s not making his dad’s secret sloppy joe recipe, you’ll likely find him creating some MDX calculations or exploring new possibilities with Oracle Data Integrator. An Arizona native, Pete’s hobbies include eating (especially pizza or Mexican food), fast cars, HPDE events (google it) and spending time with his family.

3 Comments

  1. Avatar
    Praveen Padarthi

    Hi Pete, This is helpful info. thank you.

    • Hi Praveen,

      Glad you found it useful. If you have any topics you would like covered in a post, please let us know and we’ll do our best to cover them.

      Pete

  2. I am trying to upload account formulas in EPBCS using Application Management in SmartView and I can’t seem to get this to work. For example, I am trying a simple formula for Merchandise Margin:

    “Net Sales”-“Merch COGS”; and I’ve tried \”Net Sales”-“Merch COGS\”; but it uploads \Net Sales-Merch COGS\;

    I’ve tried a few different combinations but it won’t work for me, any thoughts? Is there a formula I can use without quotes?

Leave a Reply

Your email address will not be published. Required fields are marked *