Deconstructing Essbase to a Relational Database, Part 1

Yep, you read the title of this blog post correctly. I have a series of articles I’m working with OTN and Oracle Magazine for next year regarding an EPMer (me) learning APEX and getting back to my SQL, relational roots (it’s been toooooooo long). I’m not going into all the details here – you’ll have to read Oracle Magazine for those!

But in a bipartisan (ha!) effort to have each side learn about the other’s details, I’m starting a new series where I’ll be relating these two dichotomous databases to each other. What has been interesting to me is that I routinely take relational databases and build OLAP (Essbase) databases from them. However, I have never broken down an Essbase cube into a relational database. Honestly, it has required me to think different from I have in years! …And I’m loving the challenge.

Add to that an application component – an APEX application – that twists my “previous life as an application developer’s” brain. I have developed many (VB, .Net) applications from scratch – the database and the application code. So moving to a tool that will write the code for me and allows applications to be created in record time is nothing short of fascinating. I’m loving learning this tool and LOVE that it’s free from Oracle and on the cloud. Yep, for real.

Back to the real meat of this post…

Jorge Rimblas has been my go-to guy for all things APEX. Kent Graziano taught me so much about architecting a relational database in a short time at OOW. Put the two together, and it’s like having George Washington teach you about the Revolutionary War. While working through some APEX concepts this past weekend, Jorge introduced me to the idea of virtual columns in a table. These columns (members for the EPMers) are not actually real (stored), but calculated upon time of need or reporting.

Sound familiar? Sound a bit like a Dynamic Calc member in Essbase? How about…exactly.

Yep, exact same concept. I’d like to think that Essbase gave the Oracle database developers the idea. While researching the concept and executing my {best laid} plans, I learned that you cannot reference a virtual member in another virtual member (Essbase wins that round!) and you cannot reference a column (real or virtual) in another table. This reduces the size of the table, reduces the code needed to calculate these columns on a regular row insertion and makes database administration more streamlined.

To example, if we were to calcuate simple interest, the equation is A = P(1 + rt), where A = total accrued amount, P = principal amount, r = rate of interest and t = time

Essbase DC member example:

Accrued_Interest = Principal * (1 + Rate * Time);

Oracle Virtual Column example in SQL for column creation:

ACCRUED_INTEREST      NUMBER GENERATED ALWAYS AS (PRINCIPAL * (1 + RATE * TIME)) VIRTUAL

where PRINCIPAL, RATE and TIME would all be real (stored) columns (members).

So there…you learned something about Oracle databases today! 🙂 There will be more posts like this coming. It’s always good to know what the other hand is doing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s