Demystifying OBIEE Essbase Date Measure – The Solution

About the mystery

One of my readers, Saptarshi, asked an excellent question. Original post here: http://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!

OBIEE_Essbase_Date_Google_Results

 

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!

Essbase_11.1.2.4

 

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”.

Essbase_Date_Setup_1

 

Now add a member named “InvoiceDate”

Essbase_Date_Setup_2

Set this member Type to Date.

Essbase_Date_Setup_3

Next step is to use SmartView to input some dates.

SmartView_Date_Input

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.

OBIEE_Returned_Date

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_Data_1

 

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

OBIEE_Essbase_Date_Step1

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

OBIEE_Essbase_Date_Step2

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’)

OBIEE_Essbase_Date_Step3

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

OBIEE_Essbase_Date_Data_2

 

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

Advertisements

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist

One thought on “Demystifying OBIEE Essbase Date Measure – The Solution”

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