Demystifying OBIEE Essbase Date Measure – The Solution

About the mystery

One of my readers, Saptarshi, asked an excellent question. Original post here: https://mondaybi.com/2014/08/07/an-essbase-obiee-integration-walk-through-with-tips-and-tricks/

He asked:

I am integrating Essbase and OBIEE. I have few measures (accounts) as date in Essbase, such as Receipt Date etc.

Now when I fetch this in OBIEE, they appear as numbers.

How to get date measures from Essbase to OBIEE exactly the way they are sitting in Essbase?

And he’s right! They do appear as numbers. So I Googled and see how frequent this question is asked and if there were already answers I can refer him to. This is what I got!

 

Opps, just questions, no answers! Questions like this comes in every few years. Saptarshi actually also posted in Oracle Community too, 16 hours ago! Good job Google! As promised last night, I’m going to demystify and give you a solution to this! In return, just like my Facebook page which was just launched yesterday! Click here!

Essbase Date Measure Knowledge

So that you all should know, Essbase stores Data Type Dates as numbers. So, inputting 1-Jan-2015 returns, 1420070400. And yeah, what the heck is that?

OK, so if we input 1-Jan-1970, it actually returns 0.

Many of us may not know what the number means. It actually represents seconds. So, 1420070400 actually means 1420070400 seconds since 1-Jan-1970.

When OBIEE reads this, it reads the seconds and not converted into the dates! Doesn’t mean OBIEE is wrong. It actually returned the CORRECT results but displaying in a way you can’t read.

Setting Up Essbase Environment

Just to flash my newest installation with OBIEE 11.1.1.9, it comes with Essbase 11.1.2.4.0! Hooray!

 

I’m going to use Sample Basic for demonstrating the solution. I’m not going to go through in details on how this is setup, but the following should be enough to guide readers with a bit of Essbase background.

First thing is to setup the properties to allow date measures.

Go to outline properties and set “Typed Measures enabled” to “true”.

 

Now add a member named “InvoiceDate”

Set this member Type to Date.

Next step is to use SmartView to input some dates.

These are the dates I’ve submitted to Essbase using SmartView.

Demystifying Using Excel

With my OBIEE already setup for Essbase, this is what OBIEE is returning for the above results.

Like I said previously, it’s doing everything correctly, returning results in seconds since 1st of Jan 1970. On 1st of Jan 1970 00:00:00 time, it is recognized as 0.

Let’s do some Excel calculations.

First, you must acknowledge the below formulas as described in Column G and H.

Hours in a day = 24

Minutes in a day = 24 * 60 = 1440

Seconds in a day = 1440 * 60 = 86400

Once you understand this, We will divide 1-Jan-2015 (C4) with 86400 to return number of days since 1-Jan-1970 (C17). The result is 16436 (E4) days since day 1 of 1970. If we add this with 1-Jan-1970, we actually do get the correct results as demonstrated in cell F4.

 

OBIEE Essbase Date Measure Solution

The solution is already stated above. All we need to do is turn this into OBIEE language.

To do this, we will need to divide InvoiceDate by 86400 to get number of days since 1970.

We will also know how to create a date in OBIEE as 1/1/1970.

And we’ll be using Timestampadd function.

Step 1: If we look at the below, we have “Measure / 86400” which is read in OBIEE as “Basic#1”.”Measure”/86400

Step 2: For 1970 column, the formula would be Date ‘1970-01-01’

Step 3: For Essbase Date Converted Column, we will be using OBIEE Timestampadd function. The formula would be TIMESTAMPADD(SQL_TSI_DAY, CAST((“Basic#1”.”Measure”/86400)AS INT),Date ‘1970-01-01’)

Yup, it’s giving the correct dates now. So basically, all you need to learn is step 3.

 

Returns all expected results! Hope you enjoyed the solution!

You might also like the following:

Click here to learn about using SmartView VBA with OBIEE.

Click here to learn about must use features in OBIEE 11.1.1.9.

Click below links to learn more about OBIEE and Essbase Integrations.

OBIEE Essbase Integration Part 1

OBIEE Essbase Integration Part 2