How to Drive Yourself Mad with EPMA: A Personal Story

So I’ve gone back and forth on what my first blog post will be.  In fact, I’ve been going back and forth for over a year now.  After some much needed prodding to get this blog up and started, I figured today’s frustration was the perfect start to my blogging world.  Let me come clean on one thing:

I am a fan of EPMA.

I can hear everyone say in unison, “Shut your mouth!”  But verily, verily it is true!  However, it is the cause of my rogue white hairs.  To those at ECOUG, you will get that reference (Danny B, Kent G, Jeff S).  This fandom has not come without much frustration and time spent on the learning curve.  Having worked with it for a few years now, I consider myself pretty good with EPMA. I’m certainly not a classic fan, having worked with it since 2005.  When it comes time for maintenance on many applications, EPMA is the way to go (yes, yes, I know about DRM but this blog site is about EPMA).

So back to today and my current headache (quite literally…).  A little background…  I have a cost center reporting cube that is just massive.  It shares 10 dimensions with HFM and 1 dimension is special – the cost center dimension.  Because of the unique reporting needs, every exhaustive option for a cost center must be in place creating over 48,000 members in this one dimension.  Yes, you read that correctly.  And since the users would like to see the data rolled up through the hierarchies and not all at level zero (what harsh requirements), I decided to put an ASO cube on top of the BSO cube so that I never had to aggregate the BSO cube and waste time.  For the past week, it’s been working like a charm.  I load the data to the local in the currency dimension and all data is coming in as YTD.  I have a calculation that converts the YTD value to periodic that runs in a very short amount of time.  The data that is loaded to Local can then be translated into whatever currency the user wants by choosing the currency.  The currency translation is driven off the USD value of the local currency.  Perfecto.

To keep my mind straight, I have the calcs – at least in development – being done in the BSO and ASO cubes.  Because of the nature (years) of experience with writing BSO calcs, I am more confident in writing BSO calcs correctly the first time than I am with ASO calcs.  I’ve worked with MDX in the past, so I’m not afraid of it.  I just don’t use it that often!  With that said, I have re-written all the BSO calcs in MDX for ASO in EPMA (EIEIO for another word jumble).  Good to go.

In EPMA, I went to deploy my updated application.  Once I got the deployment successful message, I went to EAS to double-check things (because I tend to be a worrier and perfectionist).  Oops.  I had the outline open during deployment, but didn’t get an error.  Oops.  I close the outline and reopen it.  Under the Currencies dimension name, there were no children.  Oh gosh oh gosh oh gosh.  I decided it was because the outline was open.  I redeploy (and even closed out of EAS altogether because I don’t want any opportunity to make the same mistake twice) and I get the same results.

What the heck?  This app has been working fine for 7 days!

I tried setting the dimension to local and deployed.  No luck.

I tried creating a brand new application (different name) with the same dimensions.  No luck.

I tried creating a brand new dimension with a different dimension name.  No luck.

At this point, I called upon two EPMA’ers that I trust – Amy Del Rosario and Daniel Villani.  Surely they can help a girl out.  Immediately, Amy suggests I try “Clear Property Overrides” within the application’s Dimension Library for Currencies.  So I did and I redeployed.

Voila!  It worked!

I was so excited that I started going back to the currency formulas and reentering them in and I deployed again.  But I didn’t learn the first time.  I had the outline open in EAS and I got the same error.

So I tried the steps as I tried before (insanity, anyone?) and did have any luck.  At this point I am audibly grunting and my coworker is laughing.  I’m about to throw my laptop.

One last thing before I throw my laptop, I thought.  Let me validate my MDX.  Maybe I got cocky with my MDX skillz.  Guess what.  The very first calculation – USD – did not validate.  The 990 line MDX script was bad.  I rewrote it down to 448, but it still didn’t validate.  I decided to remove it and see if that would fix my issue.

It did.  The whole freaking dimension didn’t deploy because a formula was bad.  And I got a blood pressure headache from the whole thing.

The reason that “Clear Property Overrides” worked the first time and not after that was because I added the MDX scripts to the local application outline and not the Shared Library.  The second time, I added them to the Shared Library causing a ripple effect of the errors.  The formula was the issue the whole time.

But I know the reason now.  And how to fix it!

So if you have issues in the future where:
1.  You have a dimension with member formulas,
2.  Deploy but are missing the entire hierarchy less the dimension name,
3.  and the Application Library says the application is in sync with deployment…

…check your formulas.  You may have an error.

Happy first post!